January 8, 2013 at 4:42 am
Hi,
Is it best to have a table with 400 columns or if the application has several screens to update/insert the information should the table "reflect" those screens?
For example a customers table with bank information but the information is shown on another form, not the customers "base" form. But on the customers lists the bank information is shown...
Thanks,
Pedro
January 8, 2013 at 7:42 am
PiMané (1/8/2013)
Hi,Is it best to have a table with 400 columns or if the application has several screens to update/insert the information should the table "reflect" those screens?
For example a customers table with bank information but the information is shown on another form, not the customers "base" form. But on the customers lists the bank information is shown...
Thanks,
Pedro
There are really few absolutes in sql server but this is one of them in my opinion. DO NOT EVER model your data after the UI. If you change the UI does that mean you have to go back and redesign the columns in all your tables. What happens if a particular piece of data moves from one screen to another? Does that mean you should move the column? Keep your data and your UI separated.
_______________________________________________________________
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/
January 8, 2013 at 7:45 am
Thanks,
but if the table has 400 columns and only 20 are used in lists? The number of reads will be huge since 400 columns take a lot of space per row.. is the solution create a schemabind view with the possible columns for lists?
Thanks,
Pedro
January 8, 2013 at 8:09 am
Creating views sounds like a better approach. If you have data that is related to the customer but is not as important then splitting to an extension type table is probably ok too. Just don't make those types of decision based on the current UI, base those decisions on how the data is consumed.
_______________________________________________________________
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/
January 8, 2013 at 8:30 am
Sean Lange (1/8/2013)
Creating views sounds like a better approach. If you have data that is related to the customer but is not as important then splitting to an extension type table is probably ok too. Just don't make those types of decision based on the current UI, base those decisions on how the data is consumed.
So if the table with 400 cols only has 20 of them used in 90% of the database operations (lists) should we consider creating a table with only those 20 cols for faster IO and the other columns on a "big data" table?
Thnks,
Pedro
January 8, 2013 at 9:37 am
PiMané (1/8/2013)
Sean Lange (1/8/2013)
Creating views sounds like a better approach. If you have data that is related to the customer but is not as important then splitting to an extension type table is probably ok too. Just don't make those types of decision based on the current UI, base those decisions on how the data is consumed.So if the table with 400 cols only has 20 of them used in 90% of the database operations (lists) should we consider creating a table with only those 20 cols for faster IO and the other columns on a "big data" table?
Thnks,
Pedro
Sounds like that would be a reasonable thing to explore. You will likely gain some performance improvements with that. As with anything...do it on a dev instance and test, test, test.
_______________________________________________________________
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/
January 8, 2013 at 9:49 am
Sean Lange (1/8/2013)
Sounds like that would be a reasonable thing to explore. You will likely gain some performance improvements with that. As with anything...do it on a dev instance and test, test, test.
Right 🙂
Just like "forcing" the NVARCHAR(MAX) and VARCHAR(MAX) "move" to LOB data page if it isn't used on select queries, just for "full" data... less reads.
Thanks,
Pedro
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply