Is it possible to split data from one table into 2 or more tables and have SQL do a partition on it? The goal is to have historical data in mytable_old while current data is in mytable. When queries are ran, sql can get data from both/either depending on query predicates.

For example all data older than 2011 goes into mytable_old while 2011 and newer stays in mytable.

select * from mytable where date between 12/1/2010 and 12/1/2011

will pull from both tables. Is this possible?

I would rather not use a view if possible. I want to avoid having any indexes on the old data.


To do it that way, you would need to setup a view.

It would be better to use SQL Server Enterprise edition and simply partition the table using table partitioning. You could then have two or more partitions and only the needed partition or partitions would be queried.

  • already using sql ent. and partitioning but that onlyhelps when the queries use predicates based on partition function, it doesn't help the other queries. – Titan2782 Apr 25 '11 at 19:23
  • As long are you are partitioned by date, then partition elimination should make it so you only need to query that partition. Are you searching on the clustered index or the non-clustered index? Are you searching by the partitioned column? – mrdenny Apr 25 '11 at 19:38
  • clustered index is on data column but not all queries will use a date predicate. – Titan2782 Apr 25 '11 at 19:40
  • Then using a view wouldn't help either as SQL wouldn't be able to use partition elimination against the view unless you query based on the partitioned column. You should essentially have the exact same problem. If you are querying against a non-clustered index you can partition the non-clustered index against a different column if you know the query in advance. – mrdenny Apr 25 '11 at 20:00

You could try a filtered index.

CREATE INDEX IX_SomeIndex ON SomeTable (
WHERE DateColumn >= '1/1/2011'

Then it will only index stuff newer than the date specified.


One way to accomplish this would be with a VIEW. The view in this case would be across both tables, so that you could shuffle records between them in whatever way you wanted.

  • i was just going to edit my question. I would like to not use a view if I can. – Titan2782 Apr 25 '11 at 19:21
  • if i'm splitting the tables by date, what would I join the tables on? – Titan2782 Apr 25 '11 at 19:25
  • @Titan2782: SELECT * FROM table1 UNION SELECT * FROM table2. (Or UNION ALL; choose amongst the two as only you can.) – BMDan Apr 25 '11 at 19:30
  • @BMDan doing a union would leave it up to sql optimizer to determine where to get data but it would still need to use indexes which I was hoping to avoid having on the old data. – Titan2782 Apr 25 '11 at 19:38
  • @Titan2782: As you continue to add requirements to this project, I cannot help but suspect that you are much better off doing this in application logic than at the SQL Server level. – BMDan Apr 25 '11 at 19:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.