CODEWITHSUNDEEP
sqlsql-server
Gabriel H
Gabriel H

Reputation: 329

How to index and partition a table

I need to index and partition a huge but simple table (127 million rows) to speed up querying, using SQL Server Management Studio. It has 99 blocks of monthly data, each month having an integer 1 to 99. I want to partition each month into each partition, and index over the [ID] and [Month] columns, as I need to query the table doing a comparison of each month to its preceding [Month] and each individual's [ID]. How do I do this? I have created the partition function

CREATE PARTITION FUNCTION PF_Monthly(int)
AS RANGE RIGHT FOR VALUES (
1,2,3,4,5,6,7,8,9,10
,11,12,13,14,15,16,17,18,19,20
,21,22,23,24,25,26,27,28,29,30
,31,32,33,34,35,36,37,38,39,40
,41,42,43,44,45,46,47,48,49,50
,51,52,53,54,55,56,57,58,59,60
,61,62,63,64,65,66,67,68,69,70
,71,72,73,74,75,76,77,78,79,80
,81,82,83,84,85,86,87,88,89,90
,91,92,93,94,95,96,97,98,99
)
GO

And scheme:

CREATE PARTITION SCHEME PS_Monthly
AS PARTITION PF_Monthly
ALL TO ( [PRIMARY] );
GO

How do I apply this to a table called [Table] and its column called [Month]? Can I do this after indexing or do I have to re-create the table, then partition and then index? Thanks

Upvotes: 0

Views: 285

Answers (1)

David Browne - Microsoft
David Browne - Microsoft

Reputation: 89371

I need to index and partition a huge but simple table (127 million rows) to speed up querying,

That's not what partitioning is for. It might be faster, but partitioning is more of a managability feature, and a feature that allows your workload to be no slower as the amount of data grows. Note no slower is not the same as faster.

Paritioning is a useful feature for performance, but it's far from a silver bullet. columnstores are generally much better at improving query performance on large tables, and can be combined with partitioning.

How do I apply this to a table called [Table] and its column called [Month]?

The process is documented here: Move an Existing Index to a Different Filegroup

Moving a table to a Partition Scheme is the same as moving it to a different Filegroup.

Upvotes: 3

Related Questions