When I first heard about SQL Server 2012\u2019s SEQUENCE object \u2013 I thought it was an interesting feature to be added and one that I have been asked about by customers in the past (from those who had worked on different database platforms).\u00a0 <\/span>But when I looked at the CYCLE argument of SEQUENCE, that\u2019s when I really<\/em> got interested.<\/span><\/span><\/p>\n

I wondered if it could be used in the service of implementing hash partitioning (of sorts) \u2013 allowing me to evenly distribute rows across a set number of partitions based on a hash key.\u00a0 <\/span>In this scenario I want the distribution to be evenly spread out, but NOT partition based on other business keys (like a datetime column or other attribute that has business or application meaning).<\/span><\/span><\/p>\n

So will a column with a sequence default also work as a partition key?<\/span>\u00a0 <\/span><\/span><\/span><\/p>\n

I started off by creating a new table based on AdventureWorkDWDenali\u2019s FactInternetSales table:<\/span><\/span><\/p>\n

— Create demonstration Fact table, no constraints, indexes, keys
\n— Tested on version 11.0.1750 (SQL Server 2012 RC0)
\nUSE [SequenceDemo];
\nGO
\nCREATE TABLE [dbo].[FactInternetSales](
\n[ProductKey] [int] NOT NULL,
\n[OrderDateKey] [int] NOT NULL,
\n[DueDateKey] [int] NOT NULL,
\n[ShipDateKey] [int] NOT NULL,
\n[CustomerKey] [int] NOT NULL,
\n[PromotionKey] [int] NOT NULL,
\n[CurrencyKey] [int] NOT NULL,
\n[SalesTerritoryKey] [int] NOT NULL,
\n[SalesOrderNumber] [nvarchar](20) NOT NULL,
\n[SalesOrderLineNumber] [tinyint] NOT NULL,
\n[RevisionNumber] [tinyint] NOT NULL,
\n[OrderQuantity] [smallint] NOT NULL,
\n[UnitPrice] [money] NOT NULL,
\n[ExtendedAmount] [money] NOT NULL,
\n[UnitPriceDiscountPct] [float] NOT NULL,
\n[DiscountAmount] [float] NOT NULL,
\n[ProductStandardCost] [money] NOT NULL,
\n[TotalProductCost] [money] NOT NULL,
\n[SalesAmount] [money] NOT NULL,
\n[TaxAmt] [money] NOT NULL,
\n[Freight] [money] NOT NULL,
\n[CarrierTrackingNumber] [nvarchar](25) NULL,
\n[CustomerPONumber] [nvarchar](25) NULL,
\n[OrderDate] datetime NULL,
\n[DueDate] datetime NULL,
\n[ShipDate] datetime NULL
\n)ON [PRIMARY];
\nGO<\/p>\n

Next I created the sequence object (increment by 1, with a min of 1, max of 10, caching of 10 at a time and a cycling of values):<\/span><\/span><\/p>\n

CREATE SEQUENCE dbo.Seq_FactInternetSales
\nAS int
\nSTART WITH 1
\nINCREMENT BY 1
\nMINVALUE 1
\nMAXVALUE 10
\nCYCLE
\nCACHE 10;<\/p>\n

After that, I added a new column to the Fact table called PartitionBucketKey and associated it with the new sequence object:<\/span><\/span><\/p>\n

ALTER TABLE [dbo].[FactInternetSales]
\nADD PartitionBucketKey int DEFAULT
\n(NEXT VALUE FOR dbo.Seq_FactInternetSales);<\/p>\n

Next, I created a partition function and scheme:<\/span><\/span><\/p>\n

— Create a new partition function
\nCREATE PARTITION FUNCTION pfFactInternetSales(int)
\nAS RANGE LEFT FOR VALUES (1,2,3,4,5,6,7,8,9);
\n— Create a new partition scheme
\n— And yes, being lazy about the FGs, as I just want to see whether the
\n— individual partitions fan-out the way I want…
\nCREATE PARTITION SCHEME psFactInternetSales
\nAS PARTITION pfFactInternetSales
\nALL TO ( [PRIMARY] );<\/p>\n

Next up, I created a clustered index on the table referencing the PK columns used in the original version of this table but then referencing the PartitionBucketKey in partition scheme:<\/span><\/span><\/p>\n

— Create it on the new column referencing the sequence
\nCREATE CLUSTERED INDEX IX_FactInternetSales
\nON\u00a0 dbo.FactInternetSales(SalesOrderNumber, SalesOrderLineNumber)
\nON psFactInternetSales(PartitionBucketKey);<\/p>\n

It\u2019s show time.\u00a0 <\/span>Now I went ahead and populated 60,398 rows from the original table.\u00a0 <\/span>Not much for this test I realize, but this was just an initial proof-of-concept:<\/span><\/span><\/p>\n

INSERT dbo.FactInternetSales
\n(ProductKey, OrderDateKey, DueDateKey, ShipDateKey, CustomerKey,
\nPromotionKey, CurrencyKey, SalesTerritoryKey, SalesOrderNumber,
\nSalesOrderLineNumber, RevisionNumber, OrderQuantity, UnitPrice,
\nExtendedAmount, UnitPriceDiscountPct, DiscountAmount, ProductStandardCost,
\nTotalProductCost, SalesAmount, TaxAmt, Freight, CarrierTrackingNumber,
\nCustomerPONumber, OrderDate, DueDate, ShipDate)
\nSELECT ProductKey, OrderDateKey, DueDateKey, ShipDateKey, CustomerKey,
\nPromotionKey, CurrencyKey, SalesTerritoryKey, SalesOrderNumber,
\nSalesOrderLineNumber, RevisionNumber, OrderQuantity, UnitPrice,
\nExtendedAmount, UnitPriceDiscountPct, DiscountAmount, ProductStandardCost,
\nTotalProductCost, SalesAmount, TaxAmt, Freight, CarrierTrackingNumber,
\nCustomerPONumber, OrderDate, DueDate, ShipDate
\nFROM [AdventureWorksDWDenali].[dbo].[FactInternetSales];<\/p>\n

Now I\u2019ll check if the 60,398 rows were divided up evenly over the 10 partitions:<\/span><\/span><\/p>\n

SELECT partition_number, row_count
\nFROM sys.dm_db_partition_stats
\nWHERE object_id = object_id(‘[dbo].[FactInternetSales]’);<\/p>\n

<\/a><\/span><\/p>\n

It worked.\u00a0 And i<\/span><\/span>f you look at the individual rows, you\u2019ll see the cycle of sequence values were defined based on the PK composite key (SalesOrderNumber, SalesOrderLineNumber):<\/span><\/span><\/p>\n

SELECT SalesOrderNumber, SalesOrderLineNumber, PartitionBucketKey
\nFROM [dbo].[FactInternetSales]
\nORDER BY SalesOrderNumber, SalesOrderLineNumber;<\/p>\n

<\/a><\/span><\/p>\n

Okay, so it works.\u00a0 <\/span>But is this a wise thing to do?<\/span>\u00a0 <\/span><\/span><\/span><\/p>\n

I don\u2019t know yet.\u00a0 <\/span>I have other questions about this technique and I\u2019d like to do more testing on various scenarios.\u00a0 <\/span>But I do<\/em> like the fact that I\u2019m able to leverage a native engine feature in service of another native engine feature.\u00a0 <\/span>Time will tell if this is a viable pattern or a known anti-pattern.<\/span><\/span><\/p>\n","protected":false},"excerpt":{"rendered":"

When I first heard about SQL Server 2012\u2019s SEQUENCE object \u2013 I thought it was an interesting feature to be added and one that I have been asked about by customers in the past (from those who had worked on different database platforms).\u00a0 But when I looked at the CYCLE argument of SEQUENCE, that\u2019s when […]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[28,34],"tags":[],"class_list":["post-511","post","type-post","status-publish","format-standard","hentry","category-performance","category-t-sql"],"yoast_head":"\n