March 12, 2013 at 7:51 am
Hi
A random question, if you write queries and put the schema before objects will it affect performance in any way and if so what type of metrics are we looking at? even if they are tiny 😀
Regards
Gordon Beeming
The Fastest Methods aren't always the Quickest Methods
March 12, 2013 at 12:19 pm
I have a SQL server instructor (Bell) who told me it does require some CPU cycles to resolve a name if it is not qualified with a schema name. Also, if you do NOT qualify the schema, what schema will be used? (I think, by default, the schema that contains the stored procedure will be the default schema rather than the default schema of the current user or the default schema of the database).
For these reasons, as a general practice, I always qualify database objects in stored procedures as two-part names.
March 12, 2013 at 12:24 pm
To clarify, say we have three tables:
schemaA.MyTable
schemaB.MyTable
dbo.MyTable
If my current-logged in user's default schema is schemaA and the default schema for the database is schemaB. And we create a stored procedure named dbo.SomeStoredProcedure that does this:
SELECT * FROM MyTable
It will actually query from dbo.MyTable, not schemaA.MyTable or schemaB.MyTable.
If someone could explain this behavior, I would appreciate it.
March 12, 2013 at 12:39 pm
Every user in a database has a default schema whether they like it or not. This is at the database user level, not the login level. If you don't specify a schema in your query, you'll be querying the table in your default schema. As far as I know, if you don't specify a schema, it won't look in any other schema for the table you query.
March 12, 2013 at 12:40 pm
ryan.mcatee (3/12/2013)
To clarify, say we have three tables:schemaA.MyTable
schemaB.MyTable
dbo.MyTable
If my current-logged in user's default schema is schemaA and the default schema for the database is schemaB. And we create a stored procedure named dbo.SomeStoredProcedure that does this:
SELECT * FROM MyTable
It will actually query from dbo.MyTable, not schemaA.MyTable or schemaB.MyTable.
If someone could explain this behavior, I would appreciate it.
for objects not qualified with the schema name,SQL Server looks for the object in the default schema your user belongs to first, then in dbo schema. if my default schema is SchemaC, SchemaA and SchemaB are not even checked to see if the object exists... only SchemaC then
In your procedure, you would simply see that behaviour being applied: objects not qualified with the schema name will assume dbo, which is the schema of the containing object and if it doesn't exist, would fail with object not found.
edit: corrected because my assumptions were way way wrong! Thanks Lynn Pettis!
Lowell
March 12, 2013 at 12:46 pm
The dbo schema first, then default. Thank you, Lowell. I just learned something.
March 12, 2013 at 2:30 pm
Ed Wagner (3/12/2013)
The dbo schema first, then default. Thank you, Lowell. I just learned something.
Actually, if you do not provide the schema for the object it will look first in the users default schema, then it will look in the dbo schema.
I just tested this myself.
March 12, 2013 at 2:33 pm
The behavior is different within a stored procedure.
March 12, 2013 at 2:38 pm
ryan.mcatee (3/12/2013)
The behavior is different within a stored procedure.
Will have to research further later.
It is interesting behaviour.
March 12, 2013 at 2:40 pm
Lynn Pettis (3/12/2013)
ryan.mcatee (3/12/2013)
The behavior is different within a stored procedure.Will have to research further later.
It is interesting behaviour.
Especially when you deal with ownership chaining.
March 13, 2013 at 6:16 am
To demonstrate this behavior:
CREATE SCHEMA testA
GO
CREATE SCHEMA testB
GO
CREATE TABLE dbo.Cities (city varchar(50))
GO
CREATE TABLE testA.Cities (city varchar(50))
GO
CREATE TABLE testB.Cities (city varchar(50))
GO
INSERT INTO dbo.Cities VALUES ('Chicago')
INSERT INTO testA.Cities VALUES ('Cairo')
INSERT INTO testB.Cities VALUES ('Atlanta')
GO
CREATE PROCEDURE testB.GetCities
AS
SELECT * FROM Cities
GO
EXEC testB.GetCities
GO
Output is:
Atlanta
...CREATE PROCEDURE testB.GetCities
WITH EXECUTE AS CALLER
...CREATE PROCEDURE testB.GetCities
WITH EXECUTE AS OWNER
These all yielded the same result.
March 13, 2013 at 7:03 am
ryan.mcatee (3/13/2013)
To demonstrate this behavior:
CREATE SCHEMA testAGO
CREATE SCHEMA testB
GO
CREATE TABLE dbo.Cities (city varchar(50))
GO
CREATE TABLE testA.Cities (city varchar(50))
GO
CREATE TABLE testB.Cities (city varchar(50))
GO
INSERT INTO dbo.Cities VALUES ('Chicago')
INSERT INTO testA.Cities VALUES ('Cairo')
INSERT INTO testB.Cities VALUES ('Atlanta')
GO
CREATE PROCEDURE testB.GetCities
AS
SELECT * FROM Cities
GO
EXEC testB.GetCities
GO
Output is:
Atlanta
...CREATE PROCEDURE testB.GetCities
WITH EXECUTE AS CALLER
...CREATE PROCEDURE testB.GetCities
WITH EXECUTE AS OWNER
These all yielded the same result.
Which means if you call a stored procedure in a specific schema and the table in that stored procedure is not accessed with a schema, it first looks in the schema of the stored proc (not dbo) to see if the table exists there. Hmm, "default" (schema the stored proc resides) then dbo, not dbo, then schema.
March 13, 2013 at 7:04 am
Well, at least that makes sense. 🙂
March 14, 2013 at 1:08 am
BinaryDigit (3/12/2013)
HiA random question, if you write queries and put the schema before objects will it affect performance in any way and if so what type of metrics are we looking at? even if they are tiny 😀
Regards
Gordon Beeming
Cumulatively, yes, there can be a performance gain by schema-qualifying all your objects. It avoids the overhead of the engine having to find the object you meant to refernece either in the default schema (depends on context as shown above) or in the dbo schema. I like to see all objects schema-qualified. In my opinion it makes the code more readable and avoids potential for latent bugs becoming actual bugs.
Consider the code example above. If someone were to drop the table testB.Cities for some reason the stored procedure would suddenly start returning Chicago...probably not an acceptable side-effect of dropping a table. Similarly, if when the proc had gone live at a time when there was no such table as testB.Cities and later someone added that table to the database the proc would suddenly stop returning Chicago and would start returning Atlanta. These are not things I would like to see happening when a table is added or removed from a database.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 14, 2013 at 2:40 am
Thank you everyone for the replies 😀
I have made a C# application that looks at a database and then goes through Triggers, Views and Stored procedures and looks for any references to other objects in the database that don't contain a schema before the object :). Hopefully from now it always returns no results as I have updated some systems with schemas before all objects.
The Fastest Methods aren't always the Quickest Methods
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply