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

partitioningsql server

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.

Best Answer

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.

Related Solutions

Sql-server – Altering the existing partition which is partitioned by date range in SQL Server 2012

Yes, but I wouldn't recommend it. Splitting partitions with lots of data takes an unpredictable amount of time and typically eats a ton of transaction log. I'd insert the data into a new table with the correct partitioning. It's more work, but you are in control of the move. Best practice (hindsight's 20/20) is to always have an empty left and right partition for these types of scenarios.

Best Practices

Always keep empty partitions at both ends of the partition range to guarantee that the partition split (before loading new data) and partition merge (after unloading old data) do not incur any data movement. Avoid splitting or merging populated partitions. This can be extremely inefficient, as this may cause as much as four times more log generation, and may also cause severe locking.

https://msdn.microsoft.com/en-us/library/ms186307.aspx

Sql-server – how to align data with an specific partition? plus 2 other questions related to the same table partition

how is this that my records of 2010 are not on partition 1?

With a RANGE RIGHT function, values greater than or equal to the first boundary ('20100101') and less than the second boundary ('20110101') are stored in partition 2. Only rows less than first '20100101' boundary are stored in partition 1. No rows in your test data meet this criteria.

can I find out exactly on which partition a record is?

Invoke the partition function using $PARTITION along with the partitioning column value:

SELECT 
      visit
    , $PARTITION.PF_Year(visit) AS PartitionNumber
FROM dbo.countries;

how do I add another partition, let's say next year (2018) I will add the partition for 2017, without having to rebuild the clustered index?

Set the partition scheme NEXT USED filegroup to the desired filegroup and SPLIT the function with the new boundary. This should be done before 2018 data exists, ideally with the existing partition (2017) empty, to avoid costly data movement.

ALTER PARTITION SCHEME PSC_YEAR
    NEXT USED [F8];
ALTER PARTITION FUNCTION PF_Year()
    SPLIT RANGE('20180101');

last but not least, let's say in a few year time down the line, I will not be interested anymore in the rows of 2010. how can I get rid of all that partition?

SWITCH the partition into an aligned staging table of the same schema and remove the partition boundary with MERGE. I suggest partitioning the staging table using the same scheme as the source to ensure alignment and truncating the staging table immediately after the SWITCH.

CREATE TABLE dbo.countries_staging(
      name nvarchar(100) not null
    , visit date not null
);

CREATE UNIQUE CLUSTERED INDEX pk__countries_staging   
ON dbo.countries_staging (visit desc ,name asc)
WITH(DATA_COMPRESSION=PAGE)
ON PSC_YEAR (visit);
GO

ALTER TABLE dbo.countries
    SWITCH PARTITION $PARTITION.PF_Year('20100101')
    TO dbo.countries_staging PARTITION $PARTITION.PF_Year('20100101');
TRUNCATE TABLE dbo.countries_staging;
ALTER PARTITION FUNCTION PF_Year()
    MERGE RANGE('20100101');
GO

Best Answer

Related Solutions

Sql-server – Altering the existing partition which is partitioned by date range in SQL Server 2012

Sql-server – how to align data with an specific partition? plus 2 other questions related to the same table partition

Related Question