SQL Server Performance Forum – Threads Archive

Critique my idea, please…

I’m in charge of creating a reporting application against a sql server database. The database is going to hold data from several other different databases that each hold the exact same schema. In some of these tables the row count can easily exceed 1 million. So our first idea was to duplicate the schema in our database adding one table that would hold a value to represent each of the other databases and then add a field to hold this value based on which database the data came from. A thought I had was to create separate schemas for each of the replicated databases. So if I had a table called part in each database I would then having something to this respect: Database1.Part
Database2.Part
Database3.Part
etc. Then for each table in each of the schemas create a partitioned view to merge them into one object that we could use for most of our procedures and queries. Would this create more headaches than its worth? Rob Mills

I don’t really understand the question. however if you want a detailed report about a database, you might check out the link in my sig below. www.elsasoft.org

I like the first idea better (WAG <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />

1 Million rows isn’t really that much. Properly designed this isn’t much of a problem for SQL Server. So, given only your few information, I would go for your first idea. —
Frank Kalis
Moderator
Microsoft SQL Server MVP
Webmaster:http://www.insidesql.de

Thanks for everyone’s input. We did in fact decide to go with the first idea.

quote:1 Million rows isn’t really that much. Properly designed this isn’t much of a problem for SQL Server. So, given only your few information, I would go for your first idea.

Unfortunately, I am a bit limited in designing this properly. What I mean is the the databases the data’s coming from are 25 year old progress databases whose schema leaves a lot to be desired. We’ve modified some of it to try to get it more normalized but have decided that some changes would cause too many problems for applications that are already running against this poor schema. Very frustrating because it goes against many best practices that I’m used to implementing. Rob Mills

Sorry to hear that! [<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />] – Frank Kalis Moderator Microsoft SQL Server MVP Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>

]]>