supplying a schema in queries, performance?

Click here to monitor SSC

<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>

SQLServerCentral is supported by Red Gate Software Ltd.

Log in :: Register :: Not logged in

<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>

Recent Posts

Popular Topics

Home

Home » SQL Server 2008 » SQL Server Newbies » supplying a schema in queries, performance?

Add to briefcase

15 posts, Page 2 of 2

««1 2

supplying a schema in queries, performance?

Expand / Collapse

Author

Message

Posted Wednesday, March 13, 2013 6:16 AM

SSC Rookie

Group: General Forum Members
Last Login: Today @ 9:35 AM
Points: 43, Visits: 119

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.

Post #1430341

Posted Wednesday, March 13, 2013 7:03 AM

SSC-Insane

Group: General Forum Members
Last Login: Today @ 5:20 PM
Points: 21,602, Visits: 27,428

ryan.mcatee (3/13/2013)

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.

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.

Cool

Cool

Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)

Post #1430356

Posted Wednesday, March 13, 2013 7:04 AM

SSCommitted

Group: General Forum Members
Last Login: Thursday, May 16, 2013 6:44 AM
Points: 1,856, Visits: 528

Well, at least that makes sense. Smile

Smile

Post #1430359

Posted Thursday, March 14, 2013 1:08 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 5:44 PM
Points: 6,703, Visits: 11,734

BinaryDigit (3/12/2013)

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 BigGrin

BigGrin

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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy

Post #1430788

Posted Thursday, March 14, 2013 2:40 AM

SSC Journeyman

Group: General Forum Members
Last Login: Friday, April 12, 2013 4:21 AM
Points: 89, Visits: 189

Thank you everyone for the replies BigGrin

BigGrin

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

Post #1430816

« Prev Topic | Next Topic »

Add to briefcase

15 posts, Page 2 of 2

««1 2

Permissions

Expand / Collapse