Skip to main content
Database Administrators

Return to Question

deleted 18 characters in body; edited tags; edited title
Source Link
Paul White ♦
Paul White ♦
  • 90.3k
  • 30
  • 424
  • 663

Covering Indexindex in a partiotionedpartitioned table

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

That table is partitioned by transcation_datetransaction_date.

My question is... 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.

Covering Index in a partiotioned table

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

That table is partitioned by transcation_date.

My question is... 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.

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.

added 14 characters in body; edited tags; edited title
Source Link
ypercubeᵀᴹ
ypercubeᵀᴹ
  • 98.7k
  • 13
  • 215
  • 305

Covering Index in a partiotioned table in SQL Server

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

That table is partitioned by transcation_date.

My question is... Can I create a non clustedclustered 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  ? orOr what should be the best practices on it.

Covering Index in a partiotioned table in SQL Server

I have a table with 20 millions of records called transactions.

That table is partitioned by transcation_date.

My question is... Can I create a non clusted 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.

Covering Index in a partiotioned table

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

That table is partitioned by transcation_date.

My question is... 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.

added 463 characters in body
Source Link
user141153
user141153

I have a table with 20 millions of records called transactions.

That table is partitioned by transcation_date.

My question is... Can I create a non clusted 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.

I have a table with 20 millions of records called transactions.

That table is partitioned by transcation_date.

My question is... Can I create a non clusted 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

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

I have a table with 20 millions of records called transactions.

That table is partitioned by transcation_date.

My question is... Can I create a non clusted 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.

Source Link
user141153
user141153