patternMinor
what is the first schema checked?
Viewed 0 times
thewhatfirstcheckedschema
Problem
I am a little confused right now. Can someone answer me with a simple answer to this question.
I have a user [user_test] and has [db_owner] role assigned to him for one database [database_test].
The user's default schema is [user_test] and almost all objects from the [database_test] are created under schema [user_test]. In the database exist 3 more objects with schema [dbo]
The user has rights to both schemas: [dbo] and [user_test] without explicitly specifying them in select statements in front of objects reference.
What is the first schema checked by SQL when an object is called by user [user_test] ?
I mean, If [user_test] issues a select for an object from dbo schema (without schema in front), will SQL first check in it's default schema specified and then in dbo?
thanks,
I have a user [user_test] and has [db_owner] role assigned to him for one database [database_test].
The user's default schema is [user_test] and almost all objects from the [database_test] are created under schema [user_test]. In the database exist 3 more objects with schema [dbo]
The user has rights to both schemas: [dbo] and [user_test] without explicitly specifying them in select statements in front of objects reference.
What is the first schema checked by SQL when an object is called by user [user_test] ?
I mean, If [user_test] issues a select for an object from dbo schema (without schema in front), will SQL first check in it's default schema specified and then in dbo?
thanks,
Solution
With SQL Server 2005 & 2008 the first schema that is searched is the users default schema.
from SQL Server Best Practices – Implementation of Database Object Schemas
Default Schema
Users can be defined with a default
schema. The default schema is the
first schema that is searched when it
resolves the names of objects it
references.
and
creating the base tables in
the dbo schema will enable table
objects to be referred to without an
explicit schema name. This is because
an object will be located by searching
the default schema first, followed by
the dbo schema. So:
Will assess the following statement first:
If it cannot find the object, the server will assess the following statement:
The assessment process can be improved by using either the fully qualified name
or the DEFAULT_SCHEMA option described earlier. By setting a value for
DEFAULT_SCHEMA for the user, the server will check the DEFAULT_SCHEMA
first, removing an unnecessary ownership checking process.
This can improve performance considerably on heavily utilized systems.
Default Schema was introduced in SQL Server 2005 Upgrading to SQL Server 2005 and DEFAULT_SCHEMA setting
from SQL Server Best Practices – Implementation of Database Object Schemas
Default Schema
Users can be defined with a default
schema. The default schema is the
first schema that is searched when it
resolves the names of objects it
references.
and
creating the base tables in
the dbo schema will enable table
objects to be referred to without an
explicit schema name. This is because
an object will be located by searching
the default schema first, followed by
the dbo schema. So:
SELECT * FROM Table1Will assess the following statement first:
SELECT * FROM .Table1If it cannot find the object, the server will assess the following statement:
SELECT * FROM dbo.Table1The assessment process can be improved by using either the fully qualified name
or the DEFAULT_SCHEMA option described earlier. By setting a value for
DEFAULT_SCHEMA for the user, the server will check the DEFAULT_SCHEMA
first, removing an unnecessary ownership checking process.
This can improve performance considerably on heavily utilized systems.
Default Schema was introduced in SQL Server 2005 Upgrading to SQL Server 2005 and DEFAULT_SCHEMA setting
Context
StackExchange Database Administrators Q#1003, answer score: 7
Revisions (0)
No revisions yet.