Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

supplying a schema in queries, performance? Expand / Collapse
Author
Message
Posted Wednesday, March 13, 2013 6:16 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, October 07, 2013 6:39 AM
Points: 47, Visits: 133
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

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 7:26 AM
Points: 22,472, Visits: 30,141
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.



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
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 1:12 PM
Points: 3,297, Visits: 2,340
Well, at least that makes sense.


Tally Tables - Performance Personified
Best practices on how to ask questions
Post #1430359
Posted Thursday, March 14, 2013 1:08 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, April 10, 2014 10:17 AM
Points: 7,070, Visits: 12,522
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

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

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, July 12, 2013 1:35 AM
Points: 89, Visits: 193
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
Post #1430816
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse