Provide Explicit Column Lists
When writing SELECT or INSERT statements in application code or stored procedures, you should always provide the full column lists for the SELECT or INSERTSELECT * in your code or in a stored procedure, the column list is resolved each time the SELECT statement is executed. If the table is altered to add or remove columns, the SELECT
statement returns a different set of columns. This can cause your
application or SQL code to generate an error if the number of columns
returned is different than expected. For example, consider the following
sample table: statement. If you use
create table dbo.explicit_cols (a int, b int)
insert explicit_cols (a, b) values (10, 20)
Now, suppose there is a stored procedure with a cursor that references the explicit_cols table, using SELECT * in the cursor declaration, similar to the following:
create proc dbo.p_fetch_explicit_cols
as
declare
@a int, @b int
declare c1 cursor for select * from explicit_cols
open c1
fetch c1 into @a, @b
while
@@fetch_Status = 0
begin
print 'the proc works!!'
fetch c1 into @a, @b
end
close c1
deallocate c1
return
If you run the p_fetch_explicit_cols procedure, it runs successfully:
exec dbo.p_fetch_explicit_cols
go
the proc works!!
Now, if you add a column to the explicit_cols table and rerun the procedure, it fails:
alter table explicit_cols add c int null
go
exec dbo.p_fetch_explicit_cols
go
Msg 16924, Level 16, State 1, Procedure p_fetch_explicit_cols, Line 7
Cursorfetch: The number of variables declared in the INTO list must match that of
selected columns.
The p_fetch_explicit_cols procedure fails this time because the cursor is now returning three columns of data, and the FETCH statement is set up to handle only two columns. If the cursor in the p_fetch_explicit_cols procedure were declared with an explicit list of columns a and b instead of SELECT *, this error would not occur.
Not providing an explicit column list for INSERT statements can lead to similar problems. Consider the following stored procedure:
create proc p_insert_explicit_cols (@a int, @b int, @c int)
as
insert explicit_cols
output inserted.*
values (@a, @b, @c)
return
go
exec dbo.p_insert_explicit_cols 10, 20, 30
go
a b c
----------- ----------- -----------
10 20 30
With three columns currently on the explicit_cols table, this procedure works fine. However, if you alter the explicit_cols table to add another column, the procedure fails:
alter table explicit_cols add d int null
go
exec dbo.p_insert_explicit_cols 10, 20, 30
go
Msg 213, Level 16, State 1, Procedure p_insert_explicit_cols, Line 4
Insert Error: Column name or number of supplied values does not match table
definition.
If the procedure were defined with an explicit column list for the INSERT statement, it would still execute successfully:
alter proc p_insert_explicit_cols (@a int, @b int, @c int)
as
insert explicit_cols (a, b, c)
output inserted.*
values (@a, @b, @c)
return
go
exec dbo.p_insert_explicit_cols 11, 12, 13
go
a b c d
----------- ----------- ----------- -----------
11 12 13 NULL
Note
If a procedure specifies fewer columns for an INSERT statement than exist in the table, the INSERT statement succeeds only if the columns not specified allow NULL values or have default values associated with them. Notice in the previous example that column d allows nulls, and the OUTPUT clause used in the procedure shows that the INSERT statement inserted a NULL value into that column.
Qualify Object Names with a Schema Name
In SQL Server 2005, the
behavior of schemas was changed from earlier versions of SQL Server. SQL
Server 2005 introduced definable schemas, which means schemas are no
longer limited to database usernames only. Each schema is now a distinct
namespace that exists independently of the database user who created
it. Essentially, a schema is now simply a container for objects. A
schema can be owned by any user, and the ownership of a schema is
transferable to another user. This new feature provides greater
flexibility in creating schemas and assigning objects to schemas that
are not simply related to a specific database user. At the same time, it
can create confusion if objects with the same name exist in multiple
schemas.
By default, if a user has CREATE permission (or has the db_ddladmin
role) in a database and that user creates an object without explicitly
qualifying it with a schema name, the object is created in that user’s
default schema. If a user is added to a database with the CREATE USER command and a specific default schema is not specified, the default schema is the dbo schema.
Note
To further complicate matters, if you use the old sp_adduser system procedure to add a user to a database, sp_adduser also creates a schema that has the name of the user and makes that the user’s default schema. However, sp_adduser is a deprecated feature that will be dropped in a future release of SQL Server. You should therefore instead use the CREATE USER command.
For example, consider the following SQL commands that create a user called testuser43 in the bigpubs2008 database:
use bigpubs2008
go
sp_addlogin testuser43, 'TestUser#43', bigpubs2008
go
create user testuser43
go
exec sp_addrolemember 'db_ddladmin', testuser43
exec sp_addrolemember 'db_datareader', testuser43
exec sp_addrolemember 'db_datawriter', testuser43
go
If you then log in and create a table under the testuser43 account without qualifying the table with a schema name, it is created in the default dbo schema:
--Verify name of current schema
select schema_name()
go
---------------------
dbo
create table test43(a varchar(10) default schema_name() null)
go
insert test43 (a) values (DEFAULT)
go
select * from test43
go
a
----------
dbo
From these commands, you can see that the default schema for the testuser43 user is dbo, and the test43 table gets created in the dbo schema.
Now, if you create a schema43 schema and want to create the test43 table in the schema43 schema, you need to fully qualify it or change the default schema for testuser43 to schema43. To demonstrate this, you run the following commands while logged in as the testuser43 user:
create schema schema43
go
alter user testuser43 with default_schema = schema43
go
create table test43(a varchar(10) default schema_name() null)
go
insert test43 (a) values (DEFAULT)
go
select * from test43
go
a
----------
schema43
As you can see from this example, now the same CREATE TABLE and INSERT commands as entered before create and insert into a table in the schema43 schema.
When the user testuser43 runs a query against the test43 table, which table the SELECT statement runs against depends on the current default schema for testuser43. The first query runs in the schema43 schema:
alter user testuser43 with default_schema = schema43
go
select * from test43
go
a
----------
schema43
The next query runs from the dbo schema:
alter user testuser43 with default_schema = dbo
go
select * from test43
go
a
----------
dbo
You can see that the current
schema determines which table is queried when the table name is not
fully qualified in the query. There are two ways to avoid this
ambiguity. The first is to create objects only in the dbo
schema and not to have additional schemas defined in the database. If
you are working with a database that has multiple schemas, the only
other way to avoid ambiguity is to always fully qualify your object
names with the explicit schema name. In the following example, because
you fully qualify the table name, it doesn’t matter what the current
schema is for user testuser43; the query always retrieves from the dbo.test43 table:
alter user testuser43 with default_schema = dbo
go
select * from dbo.test43
go
a
----------
dbo
alter user testuser43 with default_schema = schema43
go
select * from dbo.test43
go
a
----------
dbo
Along these same lines, when you are creating objects in a database, it is recommended that you specify the schema name in the CREATE statement to ensure that the object is created in the desired schema, regardless of the user’s current schema.
