0

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.

2 Answers 2

0

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.

4
  • Thanks for the answer, but I have two questions: 1. If i will create a view and rename the existing table then I will not be able to do Insert operations on that and then again i will have to make the changes in the code. 2. And if I have created the view and hard coded the date then how will i see the older records...
    – Rishi
    Jan 23, 2018 at 12:14
  • @Rishi, you can insert into the table via this view because only one table is affected. It will not prevent insertion of pre-2015 rows but those will not be visible via the view.
    – Dan Guzman
    Jan 23, 2018 at 12:19
  • ok thanks to clarify that, but any option or suggestion to view old (pre-2015) records ?
    – Rishi
    Jan 23, 2018 at 12:23
  • @Rishi, query the table directly when the user clicks "Show Old Records".
    – Dan Guzman
    Jan 23, 2018 at 12:26
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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