InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 23 May 2013 22:06:52 GMT20http://www.sqlservercentral.com/Forums/Topic1429821-1292-1.aspxThank you everyone for the replies :-DI 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.Thu, 14 Mar 2013 02:40:18 GMTBinaryDigithttp://www.sqlservercentral.com/Forums/Topic1429821-1292-1.aspx[quote][b]BinaryDigit (3/12/2013)[/b][hr]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 :-DRegardsGordon Beeming[/quote]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.Thu, 14 Mar 2013 01:08:14 GMTopc.threehttp://www.sqlservercentral.com/Forums/Topic1429821-1292-1.aspxWell, at least that makes sense. :-)Wed, 13 Mar 2013 07:04:27 GMTEd Wagnerhttp://www.sqlservercentral.com/Forums/Topic1429821-1292-1.aspx[quote][b]ryan.mcatee (3/13/2013)[/b][hr]To demonstrate this behavior:[code="sql"]CREATE SCHEMA testAGOCREATE SCHEMA testBGOCREATE TABLE dbo.Cities (city varchar(50))GOCREATE TABLE testA.Cities (city varchar(50))GOCREATE TABLE testB.Cities (city varchar(50))GOINSERT INTO dbo.Cities VALUES ('Chicago')INSERT INTO testA.Cities VALUES ('Cairo')INSERT INTO testB.Cities VALUES ('Atlanta')GOCREATE PROCEDURE testB.GetCitiesAS SELECT * FROM CitiesGO EXEC testB.GetCitiesGO[/code]Output is:Atlanta...CREATE PROCEDURE testB.GetCitiesWITH EXECUTE AS CALLER...CREATE PROCEDURE testB.GetCitiesWITH EXECUTE AS OWNERThese all yielded the same result.[/quote]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.Wed, 13 Mar 2013 07:03:01 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1429821-1292-1.aspxTo demonstrate this behavior:[code="sql"]CREATE SCHEMA testAGOCREATE SCHEMA testBGOCREATE TABLE dbo.Cities (city varchar(50))GOCREATE TABLE testA.Cities (city varchar(50))GOCREATE TABLE testB.Cities (city varchar(50))GOINSERT INTO dbo.Cities VALUES ('Chicago')INSERT INTO testA.Cities VALUES ('Cairo')INSERT INTO testB.Cities VALUES ('Atlanta')GOCREATE PROCEDURE testB.GetCitiesAS SELECT * FROM CitiesGO EXEC testB.GetCitiesGO[/code]Output is:Atlanta...CREATE PROCEDURE testB.GetCitiesWITH EXECUTE AS CALLER...CREATE PROCEDURE testB.GetCitiesWITH EXECUTE AS OWNERThese all yielded the same result.Wed, 13 Mar 2013 06:16:22 GMTryan.mcateehttp://www.sqlservercentral.com/Forums/Topic1429821-1292-1.aspx[quote][b]Lynn Pettis (3/12/2013)[/b][hr][quote][b]ryan.mcatee (3/12/2013)[/b][hr]The behavior is different within a stored procedure.[/quote]Will have to research further later.It is interesting behaviour.[/quote]Especially when you deal with ownership chaining.Tue, 12 Mar 2013 14:40:03 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1429821-1292-1.aspx[quote][b]ryan.mcatee (3/12/2013)[/b][hr]The behavior is different within a stored procedure.[/quote]Will have to research further later.It is interesting behaviour.Tue, 12 Mar 2013 14:38:34 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1429821-1292-1.aspxThe behavior is different within a stored procedure.Tue, 12 Mar 2013 14:33:55 GMTryan.mcateehttp://www.sqlservercentral.com/Forums/Topic1429821-1292-1.aspx[quote][b]Ed Wagner (3/12/2013)[/b][hr]The dbo schema first, then default. Thank you, Lowell. I just learned something.[/quote]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.Tue, 12 Mar 2013 14:30:49 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1429821-1292-1.aspxThe dbo schema first, then default. Thank you, Lowell. I just learned something.Tue, 12 Mar 2013 12:46:00 GMTEd Wagnerhttp://www.sqlservercentral.com/Forums/Topic1429821-1292-1.aspx[quote][b]ryan.mcatee (3/12/2013)[/b][hr]To clarify, say we have three tables:schemaA.MyTableschemaB.MyTabledbo.MyTableIf 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 MyTableIt will actually query from dbo.MyTable, not schemaA.MyTable or schemaB.MyTable.If someone could explain this behavior, I would appreciate it.[/quote] 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 [b]dbo [/b]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!Tue, 12 Mar 2013 12:40:09 GMTLowellhttp://www.sqlservercentral.com/Forums/Topic1429821-1292-1.aspxEvery 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.Tue, 12 Mar 2013 12:39:15 GMTEd Wagnerhttp://www.sqlservercentral.com/Forums/Topic1429821-1292-1.aspxTo clarify, say we have three tables:schemaA.MyTableschemaB.MyTabledbo.MyTableIf 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 MyTableIt will actually query from dbo.MyTable, not schemaA.MyTable or schemaB.MyTable.If someone could explain this behavior, I would appreciate it.Tue, 12 Mar 2013 12:24:00 GMTryan.mcateehttp://www.sqlservercentral.com/Forums/Topic1429821-1292-1.aspxI 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.Tue, 12 Mar 2013 12:19:35 GMTryan.mcateehttp://www.sqlservercentral.com/Forums/Topic1429821-1292-1.aspxHiA 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 :-DRegardsGordon BeemingTue, 12 Mar 2013 07:51:51 GMTBinaryDigit