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 |
|
madlo
Starting Member
41 Posts |
 Posted - 2011-05-06 : 09:58:14
|
| I want to start refactoring the database names in the databases I am working on as the number of stored procs and tables are too many and not grouped together.1. As I understand in sql server 2005 schemas are now not linked to a user ie. if you delete the user you won't delete all the tables and procs link to that schema. I have sql 2005 on my server with a database in sql 2000 mode. Is schemas still linked to the user in this scenario?2. Would using schemas as namespaces be recommended for sql 2000 database?3. So as I see it if you have an existing database it would make sense to never use the dbo schema but rather a proper namespace describing the object.4. If you have a MIS report stored proc that is a join on schemaA.Table and schemaB.table I assume you should call this stored proc not part of either schema but instead its own schema i.e. MIS.storedproc5. How do you hande lookup tables ie. geographic region. It belongs neither to e.g Sales nor Purchasing schema. Should I put this table in it own schema and since it is common should I call the schema General.Region or Common.Region or perhaps keep it dbo.Region. |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2011-05-06 : 10:10:44
|
| 1. Correct, in 2005 and higher users and schema are completely separate, you can drop one without affecting the other.2. Schemas do not exist in SQL 2000 or earlier, the user account is used as the owner of an object. You can certainly use multiple users to simulate schemas but it's pretty clunky.3. It depends. It's not inherently better or worse to avoid using dbo, unless you have very strict security guidelines and can't have any admin-level access.4. (both entries) That also depends. Too many schemas can make organizing your database harder.I'd recommend looking at the AdventureWorks sample database and see how they used schemas. If you have a process or data that should be isolated from others, those objects should probably have their own schema. The rest can go in the Common/General schema you described. |
 |
|
|
|
|
|
Some questions on Database Schema naming