August 7, 2013 at 10:41 am
Why do some do this Database..Table instead of Database.dbo.Table. Which one is a good practice?
What is dbo?
Thanks
August 7, 2013 at 11:07 am
dbo is the schema name. Those two are identical, the .. is a shortcut for .dbo.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 7, 2013 at 12:02 pm
GilaMonster (8/7/2013)
dbo is the schema name. Those two are identical, the .. is a shortcut for .dbo.
Gail doesn't this actually shortcut to the current user's default schema? Of course that is most often dbo but it doesn't have to be.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 7, 2013 at 12:36 pm
Sean Lange (8/7/2013)
GilaMonster (8/7/2013)
dbo is the schema name. Those two are identical, the .. is a shortcut for .dbo.Gail doesn't this actually shortcut to the current user's default schema? Of course that is most often dbo but it doesn't have to be.
Err.... I think it's dbo. Easy to test if you want.
It's not something I'd recommend putting in production code, it's not that clear what's going on. For quick demos or ad-hoc queries, sire.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 7, 2013 at 12:39 pm
GilaMonster (8/7/2013)
Sean Lange (8/7/2013)
GilaMonster (8/7/2013)
dbo is the schema name. Those two are identical, the .. is a shortcut for .dbo.Gail doesn't this actually shortcut to the current user's default schema? Of course that is most often dbo but it doesn't have to be.
Err.... I think it's dbo. Easy to test if you want.
It's not something I'd recommend putting in production code, it's not that clear what's going on. For quick demos or ad-hoc queries, sire.
Yeah I figured I would check it out. It does in fact map to the default schema for the current user.
I totally agree that it is not something I would use in production.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 7, 2013 at 12:51 pm
Sean Lange (8/7/2013)
GilaMonster (8/7/2013)
Sean Lange (8/7/2013)
GilaMonster (8/7/2013)
dbo is the schema name. Those two are identical, the .. is a shortcut for .dbo.Gail doesn't this actually shortcut to the current user's default schema? Of course that is most often dbo but it doesn't have to be.
Err.... I think it's dbo. Easy to test if you want.
It's not something I'd recommend putting in production code, it's not that clear what's going on. For quick demos or ad-hoc queries, sire.
Yeah I figured I would check it out. It does in fact map to the default schema for the current user.
Which makes it even more risky to use in production code.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 7, 2013 at 1:32 pm
Thank you all.
August 7, 2013 at 11:14 pm
That would also make for a bit slower code, IIRC. Without 2 part naming, it searchs for the object using the current user schema. If it doesn't find one, (again, IIRC), it searches Master. When it doesn't find one there, then it finally give "dbo" a shot in the current databasse. Those excursions don't mean much on the start of a long running batch proc (for exammple) but they could represent some unneed resource usage if the proc (or other object) is referenced/called by front end code that executes, say, 10,000 times a day.
--Jeff Moden
August 8, 2013 at 12:20 pm
Jeff Moden (8/7/2013)
That would also make for a bit slower code, IIRC. Without 2 part naming, it searchs for the object using the current user schema. If it doesn't find one, (again, IIRC), it searches Master. When it doesn't find one there, then it finally give "dbo" a shot in the current databasse. Those excursions don't mean much on the start of a long running batch proc (for exammple) but they could represent some unneed resource usage if the proc (or other object) is referenced/called by front end code that executes, say, 10,000 times a day.
There is another issue to consider - that is the plan cache. When you either don't specify the schema - or use the .. syntax, if each user has a different default schema from dbo you will get multiple plans. You would end up with a plan for each schema...
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
August 8, 2013 at 12:34 pm
Jeffrey Williams 3188 (8/8/2013)
Jeff Moden (8/7/2013)
That would also make for a bit slower code, IIRC. Without 2 part naming, it searchs for the object using the current user schema. If it doesn't find one, (again, IIRC), it searches Master. When it doesn't find one there, then it finally give "dbo" a shot in the current databasse. Those excursions don't mean much on the start of a long running batch proc (for exammple) but they could represent some unneed resource usage if the proc (or other object) is referenced/called by front end code that executes, say, 10,000 times a day.There is another issue to consider - that is the plan cache. When you either don't specify the schema - or use the .. syntax, if each user has a different default schema from dbo you will get multiple plans. You would end up with a plan for each schema...
Not sure I follow what you mean about getting multiple plans. I guess if objects with the same name resided in multiple schemas that would be the case but in that case it is an entirely different query that would require a new plan anyway.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply