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 |
|
X002548
Not Just a Number
15586 Posts |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2011-12-16 : 11:52:36
|
| Depends on how things are set up and such. Like you said, you could create a new database instead of a schema. I tend to not like lots of schemas, but I also don't like a lot of cross-database dependencies. Sometimes I think that creating lots of single purpose databases is akin to buying a new car when your current one needs an oil change. Othertimes, I like the separation. (again it depends). A couple of examples where you may or may not what separate databses (I've sen both ways):1. Extracting data from various source systems - You could create a database for each one or one database and a separate schema for each source. 2. Reporting on transactional data - You can create a new reporting database and put the stored procedures in that database or you can create a "reporting" schema in the transactional database and create stored procedures under that schema to separate them out.There are, I'm sure, nearly unlimited examples of stuff like this. It just comes down to how you want to handle things. As for the first example, extracting from multiple source systems, assuming I cannot do what I need to do as far as transforms and such in the pipeline or I need a very specific audit, I tend to lean towards multiple schemas as all the extracted data is located in one place and you can tell where it came from via the schema. Plus you don't have to do cross-database joins (not that that is a big deal).However, if you are talking about more of single purpose database then I tend to lean towards just using dbo. I'm not a fan of the way Northwind breaks things out by schema. But, that might have a place depending on what you are trying to do or how security fits into things.Not sure if the helps, just kinda spit-balling. |
 |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2011-12-16 : 18:30:16
|
quote:
1. Extracting data from various source systems - You could create a database for each one or one database and a separate schema for each source.
We do something similar. We have to refresh an entire table twice a day, but not impact selects on current data. Partitioning isn't available due to editions, so we import into the same table name under a different schema, then do ALTER SCHEMA TRANSFER to swap the old and new tables. Works as fast as partition switching with minimal locks. And it's easier than renaming tables because all the constraints can have the same name.quote:
2. Reporting on transactional data - You can create a new reporting database and put the stored procedures in that database or you can create a "reporting" schema in the transactional database and create stored procedures under that schema to separate them out.
You can also use separate schemas to do code deployments, using ALTER SCHEMA TRANSFER to roll out the new code. And since the whole thing can go in a transaction, if you find an error you can roll back without losing anything. |
|
|
|
|
|
|
|
|
TABLE_SCHEMA