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 |
|
rubs_65
Posting Yak Master
144 Posts |
 Posted - 2004-03-17 : 11:30:33
|
| Hi,We are evaluating the benefits of partition view in our environment (as Yukon is not supposed to release until next year).....We are splitting our big table that contains dates in 12 tables with each table holds 1 month of data and recreating a big table as a view on these tables. We are storing dates as numbers in the database.Now when I create a check constraint on underlying table like this:alter table tab1_jan add constraint chk_tab1_jan check (date_col in ( 814678032,815792158))In above case these 2 numbers represent 2 separate days of month.And run a query like :Select * from view where date_col = 814678032Optimizer is smart enough just to go to this partition tab1_janBut since we don't want to hard code the dates values and want to specify like:alter table tab1_jan add constraint chk_tab1_jan check (date_col between 8014678032 and 825792158)Since both the above values are still in range and we expect optimizer to still go to 1 partition for above query but is going to all the 12 partitions.So it works with IN clause and not work with between clause.What can be the possible problem and wondering if someone else also seen these kind of issues with Partition Views?Environment is sql server 2000 enterprise edition on win 2kStatistics are updated with fullscan on all the 12 partition tablesThanks--Harvinder |
|
|
|
|
|
Partition View and Optimizer