Skip to main content
4 of 4
deleted 18 characters in body; edited tags; edited title
Paul White
  • 90.3k
  • 30
  • 424
  • 663

Covering index in a partitioned table

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.

user141153