Regarding create index and lock word in SQL statement used for SQL Server 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
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 &gt; (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).