Sql-server – Covering index in a partitioned table

nonclustered-indexpartitioningsql serversql-server-2012

I have a table with 20 millions of rows called [transaction].

That table is partitioned by transaction_date.

Can I create a non clustered index in that table even if it is partitioned?

Example:

CREATE NONCLUSTERED INDEX [IX_TRANSACTION_ID_TRANSACTION_DATE]
ON [dbo].[transaction] ([id])
INCLUDE ([transaction_date])
GO

Here is the table schema:

CREATE TABLE [dbo].[TRANSACTION](

    [id] [numeric](19, 0) IDENTITY(1,1) NOT NULL,
    [transaction_date] [datetime] NULL,
    [transaction_datetime] [datetime] NULL
PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, 
       ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

Are there any considerations to take into account? Or what should be the best practices on it.

Best Answer

The create table DDL in your question is for a non-partitioned table. It cannot be partitioned without schema changes since the partitioning column must be part of the primary key (and other unique indexes/unique constraints) and primary key columns cannot allow NULLs.

That said, you can create non-clustered indexes on a partitioned table. The index will be partitioned using the same scheme as the table by default. You can also specify a filegroup for the non-clustered index, which would create a non-partitioned index. It is generally advisable to partition non-clustered indexes similarly to the table (align) in order to allow switching partitions between tables.

BTW, I strongly recommend you avoid using reserved keywords line TRANSACTION as object and column names.

Related Question