Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
svierregger
Starting Member
2 Posts |
 Posted - 2010-03-19 : 13:22:06
|
| It's been a while since I've used SQL Server, and I notice that 2008 adds a new Schema layer between the user login and the database.Our applications typically use a single db account, and we normally have all our tables, procs, etc. within a single database. Is it still best practice to let all database objects be owned by dbo? Or is it now recommended to put app-related objects into their own Schema, instead?If I create all my objects in the dbo Schema, am I at any risk of naming collisions if I move to a shared server where lots of databases are using the same dbo Schema as I am? And is there any real performance penalty for not prefixing the "dbo." on front of my objects when I'm accessing them? For example, "select * from user", vs "select * from dbo.user". The login we use will have dbo as the default Schema (unless you experts convince me otherwise). Also, I assume SQL Server implicitly adds the database name in front of the schema, based on my ADO.Net connection info. Thanks for the info. |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-03-19 : 13:46:12
|
" Is it still best practice to let all database objects be owned by dbo? "Its rare, in my experience, to have tables that are NOT owned by DBO.There is no conflict with other databases. dbo.MYTABLE (in your database) has no conflict with OtherDatabase.dbo.SameTableName" And is there any real performance penalty for not prefixing the "dbo." on front of my objects when I'm accessing them?"Good question. I was brought up to believe that, and we religiously prefix all Table / Function etc/ names with DBO in the belief that it improves the chances of getting an exact hit on the cached query plan. (but there is nothing to alert me if I forget to put the "dbo." prefix in)But MS have put a lot of effort into dumbing everything down so even Muppets can write "loose" SQL that will be guaranteed to be found in the query plan cache. So misspellings in the table and columns names are probably now allowed, AND cached!, too  Still "good practice" though IMHO." Also, I assume SQL Server implicitly adds the database name in front of the schema, based on my ADO.Net connection info."I don't think it works like that. I would describe it as:You do: USE MyDatabase (or your connection string in effect does that, or SQL sets your current database according to the Default Database for your Server Login ID)and then you are "in" that database (the "current database") and all queries apply to the current database (unless you explicitly say OtherDatabase.dbo.SomeTable - or even OtherServer.OtherDatabase.dbo.SomeTable) |
 |
|
|
svierregger
Starting Member
2 Posts |
Posted - 2010-03-19 : 13:58:36
|
quote:
Originally posted by Kristen
" Is it still best practice to let all database objects be owned by dbo? "Its rare, in my experience, to have tables that are NOT owned by DBO.There is no conflict with other databases. dbo.MYTABLE (in your database) has no conflict with OtherDatabase.dbo.SameTableName
Ok thanks. I saw some articles where they were splitting up the tables by functionality and putting them in different Schemas. While I typically do that in my ER diagrams, I've never done that in my db. Occasionally we add customer-specific tables when we integrate, and those might make sense to put in another schema, if only to keep them logically separated from our product db objects.Thanks for the help. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-03-19 : 14:32:10
|
| " customer-specific tables"Yes, I can see that.The Schema is assigned to the Login. So if you login as XXX you will, by default, get XXX.mytable (if it exists, otherwise you will get dbo.MYTABLE).I have seen systems where there is a VIEW owned by XXX (say XXX.PRODUCTS)with the same name as a table - i.e. dbo.PRODUCTS - such that the view provides restricted recordset.If clients are allowed to log in that would allow them to see "their tables"So having kristen.PRODUCTS and OtherClient.PRODUCTS (where you also have a standard dbo.PRODUCTS table for everyone else) might help with configuration per se.But you could program the application to have dbo.PRODUCT_KRISTEN and dbo.PRODUCTS_OtherClient as well I expect? horses-for-courses ... |
|
|
|
jeffw8713
Aged Yak Warrior
819 Posts |
Posted - 2010-03-21 : 12:42:17
|
| It is now recommended best practice on 2005/2008 to schema qualify all object access. And yes, there is a performance penalty for not doing so. If you do not schema qualify the object, and each users does not have the same default schema defined - you will get a separate execution plan for each user that executes the code.For example:UserA executes the code SELECT col1, col2 FROM Users - UserA has default schema of UserA, you get an execution plan for UserA.UserB executes the same exact code, you now get a separate execution plan for UserB.If you schema qualify the code and both users execute SELECT col1, col2 FROM dbo.Users - only one execution plan is generated for dbo.You can avoid some of these issues by making sure every user in that database has the same default schema. However, as soon as you find that you want to use schemas to separate out functionality and security, you're going to assign different default schemas for those users and that is going to cause the above problems.And seriously, is it really that difficult to type the few extra characters needed to do this in the first place? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-03-21 : 13:21:01
|
" is it really that difficult to type the few extra characters needed to do this in the first place"First you have to know to do that - I don't think many/most ordinary DEVs know that.Second, and even if you do know that there is nothing that will tell you about the ones that you forget. No "warnings mode" no "strict mode"  I'd like to try two different schema logins to see about the caching. MS seems to have put a huge amount of effort into getting "any old junk" to use cached query plans in recent SQL versions. |
|
|
|
|
|
|
|
|
Database setup vs Schema vs User