shamshad.ali (9/3/2015)
We have to move daily transactions on archive (daily bases we move day end data on archive DB) and I see that table partitioning can do this in better way. Managing (backup, indexing) and querying on archived data would be easily available. I need some help on this how can I create Partition(s) for daily bases, i saw we can create 15K partitions if we are on SQL Server 2012 or higher. Please let me know what would be the script for Create Partition for daily bases?Sample on MSDN for creating partition of 15K is:
--Create integer partition function for 15,000 partitions.DECLARE @IntegerPartitionFunction nvarchar(max) = N'CREATE PARTITION FUNCTION IntegerPartitionFunction (int) AS RANGE RIGHT FOR VALUES (';
DECLARE @i int = 1;
WHILE @i < 14999
BEGIN
SET @IntegerPartitionFunction += CAST(@i as nvarchar(10)) + N', ';
SET @i += 1;
END
SET @IntegerPartitionFunction += CAST(@i as nvarchar(10)) + N');';
EXEC sp_executesql @IntegerPartitionFunction;
GO
Shamshad Ali
You're looking for a script and I have to tell you that's the wrong thing to do when it comes to partitioning especially for 15,000 partitions.
If you're going to partition tables and have to support it for when things do go wrong (or even when they go right), you need to have a deep understanding of what partitioning is all about. There are many caveats especially when it comes to things like unique indexes/FKs/PKs, aligned indexes, and the nature of SPLIT itself.
And if you're partitioning for performance, forget about it. Partitioning is not a panacea for performance and will, many times, actually provide less performance because of the nature of the underlying B-Trees of the table (clustered index) and related non-clustered indexes.
Please do yourself a favor. If you don't already have it, get a copy of Books Online and read every single word on all the links in every article in Books Online and then sit down and create a detailed plan for partitioning so that you and your company don't end up shooting yourselves in the mouth. Make sure that you consider how big the table will actually get, how to "compress" the free space out of static files/filegroups, whether or not to make them Read-Only for savings on backups, what you need to do for index maintenance, how it will affect DRs and restores, and much, much more.
The bottom line is that if you setup partitioning and thought is was easy, then you've probably done something seriously wrong.
--Jeff Moden
Change is inevitable... Change for the better is not.