Prev: select statement ,sum with calculation
Next: Return a specific string based on positions of Y/N data
From: Ron on 3 Dec 2009 13:15
Hi there

Can someone help me with an understanding with respect to how a clustered
index is being used in a query I have?

I have a table with 3 columns, COL1,COL2,COl3. There is a clustered index
IX_FRED on COL1 ASC, COL2 ASC

I then run a query, displaying execution plan...

SELECT TOP 5000 [COL1], [COL2] FROM MyTable AS D
WITH WHERE D.COL3 LIKE '%X%'

What I cant understand is that the execution plan shows a 'Clustered Index
Seek' using IX_FRED. I can't understand why this is the case. As COL3 is not
in the index, surely it would have to scan all rows to match the contents of
COL3 against '%X%'. It may seem like a trivial question, but it would really
help my understanding of how the plan is constructed.

Any help greatly appreciated.

From: Tibor Karaszi on 3 Dec 2009 13:57
Can you post the real query? The one you posted shouldn't compile (WITH
WHERE) so I suspect here's more to it than we see. Also, is it a table or a
view you are accessing? Also, are you certain it is an index seek and not
index scan?

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi



"Ron" <Ron(a)discussions.microsoft.com> wrote in message
news:051CD0CE-5FB3-4A40-A7A4-EC909ABA06C7(a)microsoft.com...
> Hi there
>
> Can someone help me with an understanding with respect to how a clustered
> index is being used in a query I have?
>
> I have a table with 3 columns, COL1,COL2,COl3. There is a clustered index
> IX_FRED on COL1 ASC, COL2 ASC
>
> I then run a query, displaying execution plan...
>
> SELECT TOP 5000 [COL1], [COL2] FROM MyTable AS D
> WITH WHERE D.COL3 LIKE '%X%'
>
> What I cant understand is that the execution plan shows a 'Clustered Index
> Seek' using IX_FRED. I can't understand why this is the case. As COL3 is
> not
> in the index, surely it would have to scan all rows to match the contents
> of
> COL3 against '%X%'. It may seem like a trivial question, but it would
> really
> help my understanding of how the plan is constructed.
>
> Any help greatly appreciated.
>
From: Jay on 3 Dec 2009 18:59
Can you also post the create index statement for the actual index?

"Ron" <Ron(a)discussions.microsoft.com> wrote in message
news:051CD0CE-5FB3-4A40-A7A4-EC909ABA06C7(a)microsoft.com...
> Hi there
>
> Can someone help me with an understanding with respect to how a clustered
> index is being used in a query I have?
>
> I have a table with 3 columns, COL1,COL2,COl3. There is a clustered index
> IX_FRED on COL1 ASC, COL2 ASC
>
> I then run a query, displaying execution plan...
>
> SELECT TOP 5000 [COL1], [COL2] FROM MyTable AS D
> WITH WHERE D.COL3 LIKE '%X%'
>
> What I cant understand is that the execution plan shows a 'Clustered Index
> Seek' using IX_FRED. I can't understand why this is the case. As COL3 is
> not
> in the index, surely it would have to scan all rows to match the contents
> of
> COL3 against '%X%'. It may seem like a trivial question, but it would
> really
> help my understanding of how the plan is constructed.
>
> Any help greatly appreciated.
>


From: Ron on 4 Dec 2009 12:15
Hi, yes, here is the full query, exec plan (SHOWPLAN_TEXT), table and Index.
(I tried to reduce it down to an example for simplicity before, and fell into
the trap of providing less than useful info). I've tried to lay this out as
best as possible....
Just to repeat, my question is why is there a clustered index seek, when
index6 isn't in the clustered index (and is not SARGable?). Any thoughts
greatly appreciated.

----Execution Plan----

|--Top(TOP EXPRESSION:((5000)))
|--Clustered Index Seek(OBJECT:([_archive].[dbo].[Docs].[IX_DOCUMENTS]
AS [D]), SEEK:([D].[ApplicationID]=(99)),
WHERE:([dbtest].[dbo].[Docs].[INDEX6] as [D].[INDEX6] like '%W%') ORDERED
FORWARD)

----Query

SELECT TOP 5000 [DocumentID], [ImportDate] FROM Docs AS D
WHERE APPLICATIONID=99
AND D.INDEX6 LIKE '%W%'

----Index

CREATE CLUSTERED INDEX [IX_DOCUMENTS] ON [dbo].[Docs]
(
[ApplicationID] ASC,
[IsTest] ASC,
[BatchID] ASC,
[ImportDate] DESC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB =
OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS
= ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

----Table

CREATE TABLE [dbo].[Docs](
[ID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[DocumentID] [uniqueidentifier] NOT NULL,
[ApplicationID] [smallint] NOT NULL,
[BatchID] [int] NOT NULL,
[ImportDate] [datetime] NULL,
[ValidFrom] [datetime] NOT NULL,
[ValidTo] [datetime] NULL,
[IsTest] [bit] NOT NULL,
[DocumentName] [varchar](255) NOT NULL,
[Extension] [varchar](50) NOT NULL,
[IsCompressed] [bit] NOT NULL,
[IsAttachmentOnly] [bit] NOT NULL,
[Document] [image] NULL,
[INDEX1] [varchar](255) NULL,
[INDEX2] [varchar](255) NULL,
[INDEX3] [varchar](255) NULL,
[INDEX4] [varchar](255) NULL,
[INDEX5] [varchar](255) NULL,
[INDEX6] [varchar](255) NULL,
[INDEX7] [varchar](255) NULL,
[INDEX8] [varchar](255) NULL,
[INDEX9] [varchar](255) NULL,
[INDEX10] [varchar](255) NULL,
[INDEX11] [varchar](255) NULL,
[INDEX12] [varchar](255) NULL,
[INDEX13] [varchar](255) NULL,
[INDEX14] [varchar](255) NULL,
[INDEX15] [varchar](255) NULL,
[UsageCount] [int] NOT NULL,
[PageJump] [int] NULL,
[DocumentSize] [int] NULL,
[IsApproved] [bit] NOT NULL,
[CustomProperties] [xml] NOT NULL,
[IsEnabled] [bit] NOT NULL,
[msrepl_tran_version] [uniqueidentifier] NOT NULL,
CONSTRAINT [PK_Docs] PRIMARY KEY NONCLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY =
OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
CONSTRAINT [DocumentID] UNIQUE NONCLUSTERED
(
[DocumentID] 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]





From: Jay on 4 Dec 2009 22:28
Because "ApplicationID" heads the index. I would expect the optimizer to do
a seek even if this was a non-clustered, or non-unique index. The operative
item is that ApplicationID is heading the index.

From there I would expect it to just filter the rows according to the LIKE.

Double check your original post, you left that part out.

"Ron" <Ron(a)discussions.microsoft.com> wrote in message
news:159C6E55-1DC4-4295-B6AB-D4C9D5491722(a)microsoft.com...
> Hi, yes, here is the full query, exec plan (SHOWPLAN_TEXT), table and
> Index.
> (I tried to reduce it down to an example for simplicity before, and fell
> into
> the trap of providing less than useful info). I've tried to lay this out
> as
> best as possible....
> Just to repeat, my question is why is there a clustered index seek, when
> index6 isn't in the clustered index (and is not SARGable?). Any thoughts
> greatly appreciated.
>
> ----Execution Plan----
>
> |--Top(TOP EXPRESSION:((5000)))
> |--Clustered Index Seek(OBJECT:([_archive].[dbo].[Docs].[IX_DOCUMENTS]
> AS [D]), SEEK:([D].[ApplicationID]=(99)),
> WHERE:([dbtest].[dbo].[Docs].[INDEX6] as [D].[INDEX6] like '%W%') ORDERED
> FORWARD)
>
> ----Query
>
> SELECT TOP 5000 [DocumentID], [ImportDate] FROM Docs AS D
> WHERE APPLICATIONID=99
> AND D.INDEX6 LIKE '%W%'
>
> ----Index
>
> CREATE CLUSTERED INDEX [IX_DOCUMENTS] ON [dbo].[Docs]
> (
> [ApplicationID] ASC,
> [IsTest] ASC,
> [BatchID] ASC,
> [ImportDate] DESC
> )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB =
> OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF,
> ALLOW_ROW_LOCKS
> = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
> GO
>
> ----Table
>
> CREATE TABLE [dbo].[Docs](
> [ID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
> [DocumentID] [uniqueidentifier] NOT NULL,
> [ApplicationID] [smallint] NOT NULL,
> [BatchID] [int] NOT NULL,
> [ImportDate] [datetime] NULL,
> [ValidFrom] [datetime] NOT NULL,
> [ValidTo] [datetime] NULL,
> [IsTest] [bit] NOT NULL,
> [DocumentName] [varchar](255) NOT NULL,
> [Extension] [varchar](50) NOT NULL,
> [IsCompressed] [bit] NOT NULL,
> [IsAttachmentOnly] [bit] NOT NULL,
> [Document] [image] NULL,
> [INDEX1] [varchar](255) NULL,
> [INDEX2] [varchar](255) NULL,
> [INDEX3] [varchar](255) NULL,
> [INDEX4] [varchar](255) NULL,
> [INDEX5] [varchar](255) NULL,
> [INDEX6] [varchar](255) NULL,
> [INDEX7] [varchar](255) NULL,
> [INDEX8] [varchar](255) NULL,
> [INDEX9] [varchar](255) NULL,
> [INDEX10] [varchar](255) NULL,
> [INDEX11] [varchar](255) NULL,
> [INDEX12] [varchar](255) NULL,
> [INDEX13] [varchar](255) NULL,
> [INDEX14] [varchar](255) NULL,
> [INDEX15] [varchar](255) NULL,
> [UsageCount] [int] NOT NULL,
> [PageJump] [int] NULL,
> [DocumentSize] [int] NULL,
> [IsApproved] [bit] NOT NULL,
> [CustomProperties] [xml] NOT NULL,
> [IsEnabled] [bit] NOT NULL,
> [msrepl_tran_version] [uniqueidentifier] NOT NULL,
> CONSTRAINT [PK_Docs] PRIMARY KEY NONCLUSTERED
> (
> [ID] ASC
> )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY =
> OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
> CONSTRAINT [DocumentID] UNIQUE NONCLUSTERED
> (
> [DocumentID] 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]
>
>
>
>
>


 |  Next  |  Last
Pages: 1 2 3 4 5
Prev: select statement ,sum with calculation
Next: Return a specific string based on positions of Y/N data