When I first heard about SQL Server 2012’s SEQUENCE object – 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).But when I looked at the CYCLE argument of SEQUENCE, that’s when I really got interested.

I wondered if it could be used in the service of implementing hash partitioning (of sorts) – allowing me to evenly distribute rows across a set number of partitions based on a hash key.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).

So will a column with a sequence default also work as a partition key?

I started off by creating a new table based on AdventureWorkDWDenali’s FactInternetSales table:

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

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:

— Create it on the new column referencing the sequence
CREATE CLUSTERED INDEX IX_FactInternetSales
ON dbo.FactInternetSales(SalesOrderNumber, SalesOrderLineNumber)
ON psFactInternetSales(PartitionBucketKey);

It’s show time.Now I went ahead and populated 60,398 rows from the original table.Not much for this test I realize, but this was just an initial proof-of-concept:

It worked. And if you look at the individual rows, you’ll see the cycle of sequence values were defined based on the PK composite key (SalesOrderNumber, SalesOrderLineNumber):

SELECT SalesOrderNumber, SalesOrderLineNumber, PartitionBucketKey
FROM [dbo].[FactInternetSales]
ORDER BY SalesOrderNumber, SalesOrderLineNumber;

Okay, so it works.But is this a wise thing to do?

I don’t know yet.I have other questions about this technique and I’d like to do more testing on various scenarios.But I do like the fact that I’m able to leverage a native engine feature in service of another native engine feature.Time will tell if this is a viable pattern or a known anti-pattern.

Good questions Eric! I haven’t tried that technique before. I’d be interested in the CPU overhead of that versus this technique (and also would like to compare execution plans).

One thing I’m also wondering is if the "cache" option of the sequence will provide a benefit for large data loads. It would definitely be interesting to test several options. I was also curious to compare it to a UDF (T-SQL and CLR). We’ll see if I can get to that.