Regarding create index and lock word in SQL statement used for SQL Server

Question

This way someone creates an index.

CREATE TABLE [dbo].[QueueSlow](
[QueueID] [int] IDENTITY(1,1) NOT NULL,
[QueueDateTime] [datetime] NOT NULL,
[Title] [nvarchar](255) NOT NULL,
[Status] [int] NOT NULL,
[TextData] [nvarchar](max) NOT NULL
) ON [PRIMARY]
GO
CREATE UNIQUE CLUSTERED INDEX [PK_QueueSlow] ON [dbo].[QueueSlow]
(
[QueueID] ASC
)
GO
CREATE NONCLUSTERED INDEX [IX_QuerySlow] ON [dbo].[QueueSlow]
(
[QueueDateTime] ASC,
[Status] ASC
)
INCLUDE ( [Title])
GO

I have question that when creating non-clustered index like

CREATE NONCLUSTERED INDEX [IX_QuerySlow] ON [dbo].[QueueSlow]
(
[QueueDateTime] ASC,
[Status] ASC
)
INCLUDE ( [Title])
GO

so what is the meaning of INCLUDE([Title]) - does it mean a non-clustered index will be created on Title column?

Here is a SQL statement with lock keyword.

declare @Batch table (QueueID int, QueueDateTime datetime, _
Title nvarchar(255), TextData nvarchar(max) )

insert into @Batch
select Top (@BatchSize) QueueID, QueueDateTime, Title, TextData from QueueSlow
WITH (UPDLOCK, HOLDLOCK)
where Status = 0
order by QueueDateTime ASC

When inserting data into a table variable then why would anyone use lock like

WITH (UPDLOCK, HOLDLOCK)

What is the meaning of WITH (UPDLOCK, HOLDLOCK) ??

Is this statement WITH (UPDLOCK, HOLDLOCK) compatible with SQL Server 2000/2005 ?

Looking for a discussion. thanks

Answer 1

Three questions in one ....

OK, here are some answers:

The INCLUDE(Title) option is new in SQL Server 2005. It does NOT create an index on that column - the index is created on (QueueDateTime, Status) only. But in the leaf level of that non-clustered index, the Title column's value is included - so if you have a query something like:

SELECT QueueDateTime, Title
FROM dbo.YourTable
WHERE QueueDateTime > (some value) AND Status = 1

then this query can be satisfied by using just the index leaf level pages - the index covers the query - you don't need to go back to the actual data tables to get your answer to your query.

This can very significantly improve performance, and increase scalability, too.

See a good explanation of the benefits of including columns in non-clustered indexes here.

As for the WITH (UPDLOCK, HOLDLOCK) - you should check out this other SO question on the topic and the excellent answer provided by Andrew. And here's yet another question on the same topic with an excellent answer by Remus Rusanu.

Both table hints were available at least since SQL Server 2000 (or possibly even earlier - I can't remember versions earlier than that, though).

Answer 2

First, read Using Tables as Queues.

So what is the meaning of INCLUDE([Title]) - does it mean a non-clustered index will be created on Title column?

No. It means the index will include the Title as extra info. A query can lookup rows by the indexed fields ([Status], [QueueDateTime]) (hint: this is the correct order of your keys) and retrieve also the value of the Title w/o looking up the base table.

When inserting data into a table variable then why would anyone use lock like WITH (UPDLOCK, HOLDLOCK)

The hints (which are incidentally incorrect) apply to the SELECT, not to the INSERT.

Is this statement WITH (UPDLOCK, HOLDLOCK) compatible with SQL Server 2000/2005 ?

Yes. Here is the SQL Server 2000 spec and here is the SQL Server 2005 spec. But is, as I said, incorrect. Forcing serialization isolation level (which the HOLDLOCK hint is) is a sure-shot way to tank your throughput.

To do what you want you should make the [QueueId] a non-clustered primary key constraint and the clustered index should be ([Status], [QueueDateTime]). The SELECT hint should be (UPDLOCK, READPAST). A much better approach, based on OUTPUT clause is presented in the article I first linked.