1

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.

Improve this question

3 Answers 3

Reset to default
3

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.

Improve this answer
4
  • 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. Commented Apr 25, 2011 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? Commented Apr 25, 2011 at 19:38
  • clustered index is on data column but not all queries will use a date predicate. Commented Apr 25, 2011 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. Commented Apr 25, 2011 at 20:00
3

You could try a filtered index.

CREATE INDEX IX_SomeIndex ON SomeTable ( SomeColumn, AnotherColumn ) WHERE DateColumn >= '1/1/2011'

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

Improve this answer
0
2

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.

Improve this answer
6
  • i was just going to edit my question. I would like to not use a view if I can. Commented Apr 25, 2011 at 19:21
  • if i'm splitting the tables by date, what would I join the tables on? Commented Apr 25, 2011 at 19:25
  • @Titan2782: SELECT * FROM table1 UNION SELECT * FROM table2. (Or UNION ALL; choose amongst the two as only you can.) Commented Apr 25, 2011 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. Commented Apr 25, 2011 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. Commented Apr 25, 2011 at 19:47

You must log in to answer this question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.