Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question

migrated from stackoverflow.com Oct 15 '12 at 22:57

This question came from our site for professional and enthusiast programmers.

2 Answers 2

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).

share|improve this answer
1  
And this (outside SO) very long and detailed article (and not only about UPDLOCK hint): Developing Modifications that Survive Concurrency –  ypercube Oct 15 '12 at 17:20
    
please tell me briefly what is the use of (UPDLOCK, HOLDLOCK). when one should use it? what is the advantage of (UPDLOCK, HOLDLOCK)? –  Thomas Oct 15 '12 at 18:44
1  
@Thomas: see those questions and answers I linked to - no point in repeating those statements here again.... –  marc_s Oct 15 '12 at 18:52

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.

share|improve this answer
    
please tell me briefly what is the use of (UPDLOCK, HOLDLOCK). when one should use it? what is the advantage of (UPDLOCK, HOLDLOCK)? –  Thomas Oct 15 '12 at 18:18

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.