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

Specify schema on table name Expand / Collapse
Author
Message
Posted Tuesday, October 2, 2012 4:05 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, October 1, 2014 2:30 PM
Points: 515, Visits: 1,138
Hi,

I can across this link on the net http://www.sommarskog.se/dyn-search-2005.html.
Somewhere there's this:

On lines 25-28 there is something very important:

FROM dbo.Orders o
JOIN dbo.[Order Details] od ON o.OrderID = od.OrderID
JOIN dbo.Customers c ON o.CustomerID = c.CustomerID
JOIN dbo.Products p ON p.ProductID = od.ProductID
As you can see, I refer to all tables in two-part notation. That is, I also specify the schema (which in SQL 7/2000 parlance normally is referred to as owner.) If I would leave out the schema, each user would get his own his own private version of the query plan, so if there are ten users searching for orders there will be ten instances of the plan in cache. (As discussed in Curse and Blessings... it may work without specifying the schema on SQL 2005, but you should not rely on that.)


Since this article is written over SQL 2005 my question is: is this still true on SQL 2008?
For each users that executes a SP, if the tables' names are not fully qualified, there's a execution plan for each user?
This can be not so bad if each user has a different work mode and makes queries that have nothing to do with other users, I guess...

Thanks,
Pedro




If you need to work better, try working less...
Post #1366932
Posted Tuesday, October 2, 2012 4:54 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, November 27, 2012 9:02 AM
Points: 114, Visits: 140
The dbo prefixing thing seemed to be a problem after changeover from SQL 2000 to SQL 2005, though what it was, I've forgotten.

I've always thought it is generally good practise to prefix tables with the schema, and can remember companies that insisted all stored procedures were gone through to make sure all tables were prefixed with 'dbo.'

But if you haven't upgraded from SQL2000 - then on 2005 or 2008, if the users in question have a different default schemas, then yes, Id' guess they would generate different plans, however usually the default schema is dbo - so if all users have the default schema it would not matter.

Not sure I want to do this myself, but if you create two users in your northwind DB, make sure they have the same default schema, then execute the same statement without the dbo. a few times for each user, looking at the results of this query, to see what plans get cached.

SELECT TOP 10
plan_handle, sql_handle,
ObjectName = OBJECT_SCHEMA_NAME(qt.objectid,dbid) + '.' + OBJECT_NAME(qt.objectid, qt.dbid)
,TextData = qt.text
,DateCached = qs.creation_time
,LastExecutionTime = qs.last_execution_time
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
WHERE qt.text like '%Customers%' -- or something in your query
ORDER BY plan_handle;

I would guess you would see the plan reused.

Then create another user with a different schema, and see if when that user executes the query you get a new plan created.

I think you would see a different plan. But I'll leave this up to someone else to test out and confirm/refute.




Post #1366953
Posted Tuesday, October 2, 2012 4:59 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 8:55 AM
Points: 2,873, Visits: 5,185
You will not get different query execution plan per user if you don't specify schema.
Unless your system is designed in a such way (it's very rare case actually) that one group of users have a dedicated default schema and all relevant objects are duplicated in every such schema, it is always good idea to specify schema name.
1. It explicitly suggest which schema the object belongs to
2. Because of #1 you will get some performance benefits as compiler doesn't need to check which schema the object is from.


_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1366960
Posted Wednesday, October 3, 2012 2:10 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 4:48 PM
Points: 436, Visits: 2,295
(If SQL2008 is the same as SQL2005 in this regard, and I expect it is,...)
Don't forget that even if my default schema is DBO-- if I execute the following 2 queries, they will NOT use the same cached execution plan.
...They may come up with identical EPs, but they will both be generated.

SELECT (some columns) FROM SomeTable WHERE (some condition)
SELECT (some columns) FROM DBO.SomeTable WHERE (some condition)

... and the following query won't reuse the EP from the first either...

SELECT (some columns) FROM                 SomeTable WHERE (some condition)




Cursors are useful if you don't know SQL
Post #1368021
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse