Show specific data from a partitioned table in SQL Server. Data Archiving using Table Partitions

Question

I have created partitions on a table i.e. tblUserDetails having the following record details based on the partitions:

ObjectName       PartitionNumber    RangeValue  RowCnt
tblUserDetails      1               01-01-2014  500
tblUserDetails      2               01-01-2015  347
tblUserDetails      3               01-01-2016  1948
tblUserDetails      4               01-01-2017  4077
tblUserDetails      5               01-01-2018  478

Now I want to hide the data of year 2014 i.e. display the data from year 2015 to 2018 and it should be available in the same table so that if the user wants to see the data it can be seen.

eg: Whenever I fire a query like like Select * from tblUserDetails. It should list all the records from year 2015 to 2018 and not the records of 2014. But if user still wants to see the records of 2014 then the user will have to use some key like where or some thing explicitly to show all the records.

My requirement is to archive the data before a specific date. But still if user wants to see old records then on click of a button 'Show Old Records' every thing should be displayed.

I dont want to use the Swtich Command, because in that i will have to create another table.

I want to achieve this without making any changes in the existing code. And this is just a one table. I want to do this on many tables.

So, if there is any option to hide or disable a partition of a table then that will also be helpful.

Answer 1

It doesn't matter whether the table is partitioned or not. You will need a WHERE clause to exclude unwanted data. To do this transparently without application code changes, you could create a view with the desired WHERE clause and name the view as the original table name, or create a synonym with the original table name that maps to the view. Synonym example:

EXEC sp_rename N'dbo.tblUserDetails', 'tblUserDetails_Partitioned';
GO
CREATE VIEW dbo.vw_tblUserDetails
AS
SELECT <column-list>
FROM tblUserDetails_Partitioned
WHERE datecreated >= '20150101';
GO
CREATE SYNONYM dbo.tblUserDetails FOR dbo.vw_tblUserDetails;
GO

Queries that need historical data can query the table directly.

Answer 2

If you want to archive it and do not want it to be returned in default queries, then remove it from the table and add the filegroup for this partition to another table tblUserDetailsArchive. Then any user could query

select * From tblUserDetailsArchive  

or

select * From tblUserDetailsArchive where datecreated < '20140101'      

to get the older rows if required.