April 8, 2011 at 1:01 pm
Hi,
Using SQL Server 2008, I have a database that has over 250 tables. Each table represents a electrical model with a different schema. There are about six different types of models, and when I want to display my data, I join the model with a "mapping table" of its type. In this case, should I be creating a view or sp for each table (via a trigger?), or should I be using dynamic SQL? Right now, I am using inline sql statements in my c# code since I recently ported from SQLCe. I try to use parameters as much as possible, but I'm also passing some column names or table names which cant be parametized. In this case, with so many differing tables, should I just stick to inline sql? Also, how difficult would it be to Insert/Update if I move away from inline Sql, as right now I can just pass in an array of columns I want to update.
Any thoughts appreciated.
Also, I am not too concerned with how long it would take to switch, if it is clearly better.
April 8, 2011 at 1:35 pm
Hi Jim welcome to SSC. The question you pose is pretty vague but I can try to give my version of an answer on a few items.
jimw923 (4/8/2011)
Hi,Using SQL Server 2008, I have a database that has over 250 tables. Each table represents a electrical model with a different schema. There are about six different types of models, and when I want to display my data, I join the model with a "mapping table" of its type. In this case, should I be creating a view or sp for each table (via a trigger?), or should I be using dynamic SQL? Right now, I am using inline sql statements in my c# code since I recently ported from SQLCe. I try to use parameters as much as possible, but I'm also passing some column names or table names which cant be parametized. In this case, with so many differing tables, should I just stick to inline sql? Also, how difficult would it be to Insert/Update if I move away from inline Sql, as right now I can just pass in an array of columns I want to update.
Any thoughts appreciated.
Also, I am not too concerned with how long it would take to switch, if it is clearly better.
As a general rule it is much better to have ALL of your data manipulation in the data layer (sql server). selects, inserts updates etc. Then you can track data changes and fix your code before the user has a chance to find the pieces you might have missed. This also lets you correct logical errors in the data side without being forced to deploy your application. You don't need a view for each table unless you need the data from each table. Probably more appropriate if you create views based on what the application needs. Not really sure what you mean about the trigger comment or column/table names that can't be parameterized. Without any details I can't give you much more. 🙂
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 8, 2011 at 2:24 pm
Sean Lange (4/8/2011)
Hi Jim welcome to SSC. The question you pose is pretty vague but I can try to give my version of an answer on a few items.jimw923 (4/8/2011)
Hi,Using SQL Server 2008, I have a database that has over 250 tables. Each table represents a electrical model with a different schema. There are about six different types of models, and when I want to display my data, I join the model with a "mapping table" of its type. In this case, should I be creating a view or sp for each table (via a trigger?), or should I be using dynamic SQL? Right now, I am using inline sql statements in my c# code since I recently ported from SQLCe. I try to use parameters as much as possible, but I'm also passing some column names or table names which cant be parametized. In this case, with so many differing tables, should I just stick to inline sql? Also, how difficult would it be to Insert/Update if I move away from inline Sql, as right now I can just pass in an array of columns I want to update.
Any thoughts appreciated.
Also, I am not too concerned with how long it would take to switch, if it is clearly better.
As a general rule it is much better to have ALL of your data manipulation in the data layer (sql server). selects, inserts updates etc. Then you can track data changes and fix your code before the user has a chance to find the pieces you might have missed. This also lets you correct logical errors in the data side without being forced to deploy your application. You don't need a view for each table unless you need the data from each table. Probably more appropriate if you create views based on what the application needs. Not really sure what you mean about the trigger comment or column/table names that can't be parameterized. Without any details I can't give you much more. 🙂
Hello, the comment about the trigger meant creating a trigger that would create a view for each table when it is made. That way I dont have to manually make all the views. The parameter comment meant I can't have a parameter such as Select * From @tableName. With so many tables, would I have to create a select sp for each, or is dynamic sql the way to go?
April 8, 2011 at 2:34 pm
Ahh I see. I don't know that you want a view for every table in the system. Most queries need to be more complex than that so the view would be mostly not used, unless of course you join the view to other views. At that point you haven't gained anything since you are back to dynamic logic. More often than not I find that I build a view or proc based on the needs of the application and not just grab a table but that will vary depending on your implementation.
There is no absolute in sql other than that aren't any. 😉 You of course could pass table names as parameters if you want, you would just have to execute dynamic sql (not saying that is a great idea but it can be done). Hope I helped a little and didn't just make things more confusing.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply