CODEWITHSUNDEEP
sqlsql-serverindexingdatabase-partitioning
JJ.
JJ.

Reputation: 9960

What difference does it make to have or not to have the partition on a NONCLUSTERED index creation in SQL Server?

I have a table that is partitioned by a column, let's call it column "Col1" This column can have the following values: 1, 2, or 3.

All of our queries looking at this table need to have WHERE Col1 = 1.

When we create NONCLUSTERED indexes on this table, what exactly is the difference between this:

CREATE NONCLUSTERED INDEX IX_Table1_City
  ON dbo.table1 (Col1, City)
  INCLUDE (County, Street)

and this:

CREATE NONCLUSTERED INDEX IX_Table1_City
  ON dbo.table1 (Col1, City)
  INCLUDE (County, Street)
ON [Partition_Name] (Col1)

Both indexes are used normally so I'm trying to understand why I would need the second option.

Thanks!

Upvotes: 0

Views: 307

Answers (1)

David Browne - Microsoft
David Browne - Microsoft

Reputation: 89331

Normally those both would result in a partitioned index, as non-clustered indexes are created on the table's partition scheme by default.

However if your table was partitioned by some other column, on some other partition scheme, or not partitioned at all, the second syntax would allow you to create a partitioned non-clustered index that wasn't aligned with the main table.

Upvotes: 3

Related Questions