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 |
|
gypo
Starting Member
9 Posts |
 Posted - 2008-04-11 : 13:18:06
|
Hi all  I have recently become involved in a database that is due to grow to a large size and have been looking at ways to ensure efficiency. One of things I have come across is using partionned views.Are partionned views the way to go when dealing with large amounts of data?Is there a minimum number of rows before they should be considered?There are 2 potential tables that it would be used on and they are currently primary keyed on an identity field. I understand that the check constraint can't be used on an identity field, so I would put it on an appropriate date field. When the data is called it is, more often than not, called using the identity field. In this case is it still better to go partionned or keep in a single table? I'm guessing that the answer to that depends on the amount of data???...The only other question is related to data types. At the moment I am using an nvarchar(max) on one of the fields. This may change type to become a binary at some point in the future. Will the partionned view be OK with using the binary data type?I think that's it for now  . Any opinions and advice are welcome! |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-04-11 : 16:20:46
|
| Yes partitioned views is one such solution for large databases. As a good indicator on how well they work know that MySpace.com implemented them across federated servers. Their databases are massive.There is no guideline for when to implement them, but I'd recommend proactively monitoring your system to determine approximately when performance will be unacceptable. Make sure to have a new plan prior to customers complaining how slow the system is. Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
 |
|
|
gypo
Starting Member
9 Posts |
Posted - 2008-04-12 : 04:15:38
|
| Thanks Tara :) Clearly they are the way to go...I suppose I would need to performane test the views to see at which point the partitionned option becomes more efficient. Would it be detremental to implement it on a data set of around 500,000 rows (split into 2 tables - obvisouly data is due to grow substantially in the near future)?Does anybody have an answer/opinion on the identity and data type change question? |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-04-12 : 19:13:18
|
| 1. Don't need split table with around 500000 rows. 2. If tables use identity column, they should have different set of values in case you want to union them in a view.3. Data type change is fine as long as you change all involved tables. |
|
|
|
|
|
|
Partionned Views: Efficiency and Implementing