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 |
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
 Posted - 2004-03-04 : 14:33:00
|
| Over in the thread at http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=33022 Shafiqm has an accounting system that creates a new database every year (Payroll (curren year), Payroll_2203, Payroll_2002, etc.).To me this seems like a totally whacked way of doing things (no offense, I don't think Shafiqm got to choose). Can anyone provide a good reason to use this type of design? It seems to me that it just causes more trouble, especially in this case where they want to do a comparison between years (a.k.a. databases).Your thoughts?--------------------------------------------------------------Find more words of wisdom at [url]http://weblogs.sqlteam.com/markc[/url] |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-03-04 : 14:39:30
|
| I think it would be fine for an archival database where you hardly ever need to point to it. So when you do, you just change the connection string to point to it. But you don't have to use dynamic sql to get to it or do comparisons. I think an alternative to that design would be to use partitioned views with tables that have dates in the name.Tara |
 |
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2004-03-04 : 16:13:28
|
| I totally agree with Tara. We've got some partitioned views looking at several months worth of data, and it works great. If we keep adding tables, we'll get into years worth of data. The only time this causes problems is when the users try to report on the ENTIRE set of data at once (millions of rows). If you can limit the date span in the client application, you'll be fine.Michael<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
|
|
|
Frank Kalis
Constraint Violating Yak Guru
413 Posts |
Posted - 2004-03-04 : 16:21:15
|
I don't think there is even only one good reason for this approach. But if you want to hear several very good reasons against this, post it to the MS newsgroups and with a little bit of luck you'll receive a reply from Joe Celko (yes, I know, I have seen him here in the member list) Frank |
|
|
|
|
|
|
|
|
Design Issue: One DB Per Year