I have a table with 5 primary keys. I have clustered index defined on
all 5 columns. However, I can see that this index is only using three
columns and not five. Why is that?
Also,
I for some reason when I run queries I get index scan as opposed to
index seek.
Thanks,
T.It sounds like the indexes you have are not working for you.
For us to assist you, please post the table DDL, along with a query or two
that you believe do not properly use indexing.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"tolcis" <a.liberchuk@.verizon.net> wrote in message
news:1164740732.714927.282500@.j44g2000cwa.googlegroups.com...
> Hi!
> I have a table with 5 primary keys. I have clustered index defined on
> all 5 columns. However, I can see that this index is only using three
> columns and not five. Why is that?
> Also,
> I for some reason when I run queries I get index scan as opposed to
> index seek.
> Thanks,
> T.
>|||> I have a table with 5 primary keys.
You can only have one PK per table. I assume you mean you have one PK based
on 5 columns.
> I have clustered index defined on
> all 5 columns.
You mean that the index that goes with the PK is the clustered index.
> However, I can see that this index is only using three
> columns and not five.
Where do you see this?
> I for some reason when I run queries I get index scan as opposed to
> index seek.
You never get a table scan when a table has a clustered index, so I assume y
ou mean "clustered index
scan". We need to know the table structure, indexes and queries to determine
whether the index can
support your queries.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"tolcis" <a.liberchuk@.verizon.net> wrote in message
news:1164740732.714927.282500@.j44g2000cwa.googlegroups.com...
> Hi!
> I have a table with 5 primary keys. I have clustered index defined on
> all 5 columns. However, I can see that this index is only using three
> columns and not five. Why is that?
> Also,
> I for some reason when I run queries I get index scan as opposed to
> index seek.
> Thanks,
> T.
>|||This is the table DDL:
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[SERVICEW]') and OBJECTPROPERTY(id, N'IsUserTable'
) =
1)
drop table [dbo].[SERVICEW]
GO
CREATE TABLE [dbo].[SERVICEW] (
[AppCode] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NUL
L
,
[IdNumber] [int] NOT NULL ,
[Family] [int] NOT NULL ,
[Sequence] [bigint] NOT NULL ,
[Source] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NUL
L
,
[ApplicantType] [int] NULL ,
[Status] [int] NULL ,
[Type] [int] NULL ,
[UniqueExpansionId] [varchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[UniqueIdNumber] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[Price] [real] NULL ,
[PriceOption] [int] NULL ,
[Quantity] [int] NULL ,
[Description] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[ProductCode] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NU
LL
,
[exported] [bit] NULL ,
[UPTODATE] [bit] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[SERVICEW] WITH NOCHECK ADD
CONSTRAINT [PK_SERVICEW] PRIMARY KEY CLUSTERED
(
[IdNumber],
[Family],
[AppCode],
[Sequence],
[Source]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[SERVICEW] ADD
CONSTRAINT [DF__SERVICEW__Sequen__11FF8BD8] DEFAULT (0) FOR
[Sequence],
CONSTRAINT [DF__SERVICEW__Applic__12F3B011] DEFAULT (0) FOR
[ApplicantType],
CONSTRAINT [DF__SERVICEW__Status__13E7D44A] DEFAULT (0) FOR [Status]
,
CONSTRAINT [DF__SERVICEW__Type__14DBF883] DEFAULT (0) FOR [Type],
CONSTRAINT [DF__SERVICEW__Unique__15D01CBC] DEFAULT (' ') FOR
[UniqueExpansionId],
CONSTRAINT [DF__SERVICEW__Unique__16C440F5] DEFAULT (' ') FOR
[UniqueIdNumber],
CONSTRAINT [DF__SERVICEW__Price__7DE38492] DEFAULT (0.0) FOR [Price]
,
CONSTRAINT [DF__SERVICEW__PriceO__6F2B50E7] DEFAULT (0) FOR
[PriceOption],
CONSTRAINT [DF__SERVICEW__Quanti__396371BC] DEFAULT (0) FOR
[Quantity],
CONSTRAINT [DF__SERVICEW__Descri__03C67B1A] DEFAULT ('') FOR
[Description],
CONSTRAINT [DF__servicew__Produc__1452B3F5] DEFAULT ('') FOR
[ProductCode],
CONSTRAINT [DF__SERVICEW__UPTODA__08211BE3] DEFAULT (1) FOR [UPTODAT
E]
GO
My query is pretty large and it doesn't only use this table it use
multiple but in the execution plan I see it uses clustered index scan
on this table. From what I remember it should only be table seek and
not scan.
Thanks,
T.
Arnie Rowland wrote:[vbcol=seagreen]
> It sounds like the indexes you have are not working for you.
> For us to assist you, please post the table DDL, along with a query or two
> that you believe do not properly use indexing.
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
> You can't help someone get up a hill without getting a little closer to th
e
> top yourself.
> - H. Norman Schwarzkopf
>
> "tolcis" <a.liberchuk@.verizon.net> wrote in message
> news:1164740732.714927.282500@.j44g2000cwa.googlegroups.com...|||tolcis wrote:
> ALTER TABLE [dbo].[SERVICEW] WITH NOCHECK ADD
> CONSTRAINT [PK_SERVICEW] PRIMARY KEY CLUSTERED
> (
> [IdNumber],
> [Family],
> [AppCode],
> [Sequence],
> [Source]
> ) ON [PRIMARY]
> GO
>
If this is the only index available, then only queries that include
IDNumber in the WHERE clause will seek against this index. For example:
This will "seek":
SELECT * FROM ServiceW WHERE IDNumber = 10
This will "scan":
SELECT * FROM ServiceW WHERE AppCode = 'X'
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||On 28.11.2006 22:05, Tracy McKibben wrote:
> tolcis wrote:
> If this is the only index available, then only queries that include
> IDNumber in the WHERE clause will seek against this index. For example:
> This will "seek":
> SELECT * FROM ServiceW WHERE IDNumber = 10
> This will "scan":
> SELECT * FROM ServiceW WHERE AppCode = 'X'
I beg to differ: /all/ queries containing filters on any set of
/leading/ columns of the index should be doing an index seek - unless
the optimizer decides that a full scan is more efficient (for example
because criteria will return 90% of the rows anyway).
Kind regards
robert|||Robert Klemme wrote:
> I beg to differ: /all/ queries containing filters on any set of
> /leading/ columns of the index should be doing an index seek - unless
> the optimizer decides that a full scan is more efficient (for example
> because criteria will return 90% of the rows anyway).
> Kind regards
> robert
Use my example below. Compare the execution plans of the two SELECT
statements. Illustrates exactly the point I was trying to make:
CREATE TABLE #IndexTest
(
Col1 INT,
Col2 INT,
Col3 CHAR(1),
Col4 CHAR(1),
Col5 DATETIME,
PRIMARY KEY CLUSTERED (Col1, Col2, Col3, Col4, Col5)
)
INSERT INTO #IndexTest
(Col1, Col2, Col3, Col4, Col5)
VALUES
(1, 10, 'A', 'Z', DATEADD(dd, -1, GETDATE()))
INSERT INTO #IndexTest
(Col1, Col2, Col3, Col4, Col5)
VALUES
(2, 20, 'B', 'Y', DATEADD(dd, -2, GETDATE()))
INSERT INTO #IndexTest
(Col1, Col2, Col3, Col4, Col5)
VALUES
(3, 30, 'C', 'X', DATEADD(dd, -3, GETDATE()))
INSERT INTO #IndexTest
(Col1, Col2, Col3, Col4, Col5)
VALUES
(4, 40, 'D', 'W', DATEADD(dd, -4, GETDATE()))
INSERT INTO #IndexTest
(Col1, Col2, Col3, Col4, Col5)
VALUES
(5, 50, 'E', 'V', DATEADD(dd, -5, GETDATE()))
INSERT INTO #IndexTest
(Col1, Col2, Col3, Col4, Col5)
VALUES
(6, 60, 'F', 'U', DATEADD(dd, -6, GETDATE()))
INSERT INTO #IndexTest
(Col1, Col2, Col3, Col4, Col5)
VALUES
(7, 70, 'G', 'T', DATEADD(dd, -7, GETDATE()))
INSERT INTO #IndexTest
(Col1, Col2, Col3, Col4, Col5)
VALUES
(8, 80, 'H', 'S', DATEADD(dd, -8, GETDATE()))
INSERT INTO #IndexTest
(Col1, Col2, Col3, Col4, Col5)
VALUES
(9, 90, 'I', 'R', DATEADD(dd, -9, GETDATE()))
INSERT INTO #IndexTest
(Col1, Col2, Col3, Col4, Col5)
VALUES
(10, 100, 'J', 'Q', DATEADD(dd, -10, GETDATE()))
SELECT * FROM #IndexTest WHERE Col1 = 1
SELECT * FROM #IndexTest WHERE Col3 = 'C'
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||On 29.11.2006 14:24, Tracy McKibben wrote:
You said:
> If this is the only index available, then only queries that
> include IDNumber in the WHERE clause will seek against this index.
Then I wrote:
> Robert Klemme wrote:
> Use my example below. Compare the execution plans of the two SELECT
> statements. Illustrates exactly the point I was trying to make:
> CREATE TABLE #IndexTest
> (
> Col1 INT,
> Col2 INT,
> Col3 CHAR(1),
> Col4 CHAR(1),
> Col5 DATETIME,
> PRIMARY KEY CLUSTERED (Col1, Col2, Col3, Col4, Col5)
> )
> INSERT INTO #IndexTest
> (Col1, Col2, Col3, Col4, Col5)
> VALUES
> (1, 10, 'A', 'Z', DATEADD(dd, -1, GETDATE()))
> INSERT INTO #IndexTest
> (Col1, Col2, Col3, Col4, Col5)
> VALUES
> (2, 20, 'B', 'Y', DATEADD(dd, -2, GETDATE()))
> INSERT INTO #IndexTest
> (Col1, Col2, Col3, Col4, Col5)
> VALUES
> (3, 30, 'C', 'X', DATEADD(dd, -3, GETDATE()))
> INSERT INTO #IndexTest
> (Col1, Col2, Col3, Col4, Col5)
> VALUES
> (4, 40, 'D', 'W', DATEADD(dd, -4, GETDATE()))
> INSERT INTO #IndexTest
> (Col1, Col2, Col3, Col4, Col5)
> VALUES
> (5, 50, 'E', 'V', DATEADD(dd, -5, GETDATE()))
> INSERT INTO #IndexTest
> (Col1, Col2, Col3, Col4, Col5)
> VALUES
> (6, 60, 'F', 'U', DATEADD(dd, -6, GETDATE()))
> INSERT INTO #IndexTest
> (Col1, Col2, Col3, Col4, Col5)
> VALUES
> (7, 70, 'G', 'T', DATEADD(dd, -7, GETDATE()))
> INSERT INTO #IndexTest
> (Col1, Col2, Col3, Col4, Col5)
> VALUES
> (8, 80, 'H', 'S', DATEADD(dd, -8, GETDATE()))
> INSERT INTO #IndexTest
> (Col1, Col2, Col3, Col4, Col5)
> VALUES
> (9, 90, 'I', 'R', DATEADD(dd, -9, GETDATE()))
> INSERT INTO #IndexTest
> (Col1, Col2, Col3, Col4, Col5)
> VALUES
> (10, 100, 'J', 'Q', DATEADD(dd, -10, GETDATE()))
> SELECT * FROM #IndexTest WHERE Col1 = 1
> SELECT * FROM #IndexTest WHERE Col3 = 'C'
The second SELECT does not use a set of /leading/ columns of the index!
StmtText
---
SELECT * FROM #IndexTest WHERE Col1 = 1
(1 row(s) affected)
StmtText
----
----
|--Clustered Index Seek(OBJECT
[tempdb].[dbo].[#IndexTest]),SEEK
[tempdb].[dbo].[#IndexTest].[Col1]=(1)) ORDERED FORWARD)
(1 row(s) affected)
StmtText
---
SELECT * FROM #IndexTest WHERE Col3 = 'C'
(1 row(s) affected)
StmtText
----
--
|--Clustered Index Scan(OBJECT
[tempdb].[dbo].[#IndexTest]),WHERE
[tempdb].[dbo].[#IndexTest].[Col3]='C'))(1 row(s) affected)
StmtText
----
SELECT * FROM #IndexTest WHERE Col1 = 1 AND Col2 = 20 AND Col3 = 'C'
(1 row(s) affected)
StmtText
----
----
---
|--Clustered Index Seek(OBJECT
[tempdb].[dbo].[#IndexTest]),SEEK
[tempdb].[dbo].[#IndexTest].[Col1]=(1) AND[tempdb].[dbo].[#IndexTest].[Col2]=(20) AND
[tempdb].[dbo].[#IndexTest].[Col3]='C') ORDERED FORWARD)
(1 row(s) affected)
Q.E.D.
Regards
robert|||Robert Klemme wrote:
> On 29.11.2006 14:24, Tracy McKibben wrote:
> You said:
>
> Then I wrote:
>
> The second SELECT does not use a set of /leading/ columns of the index!
> StmtText
> ---
> SELECT * FROM #IndexTest WHERE Col1 = 1
> (1 row(s) affected)
> StmtText
> ----
----
> |--Clustered Index Seek(OBJECT
[tempdb].[dbo].[#IndexTest]),
> SEEK
[tempdb].[dbo].[#IndexTest].[Col1]=(1)) ORDERED FORWARD)
> (1 row(s) affected)
> StmtText
> ---
> SELECT * FROM #IndexTest WHERE Col3 = 'C'
> (1 row(s) affected)
> StmtText
> ----
---
> |--Clustered Index Scan(OBJECT
[tempdb].[dbo].[#IndexTest]),
> WHERE
[tempdb].[dbo].[#IndexTest].[Col3]='C'))> (1 row(s) affected)
> StmtText
> ----
> SELECT * FROM #IndexTest WHERE Col1 = 1 AND Col2 = 20 AND Col3 = 'C'
> (1 row(s) affected)
> StmtText
> ----
----
---
> |--Clustered Index Seek(OBJECT
[tempdb].[dbo].[#IndexTest]),
> SEEK
[tempdb].[dbo].[#IndexTest].[Col1]=(1) AND> [tempdb].[dbo].[#IndexTest].[Col2]=(20) AND
> [tempdb].[dbo].[#IndexTest].[Col3]='C') ORDERED FORWARD)
> (1 row(s) affected)
> Q.E.D.
> Regards
> robert
?
I don't really know what you're debating here. I said that if IDNumber
(the leading column) wasn't used in the WHERE clause, an index seek
wouldn't happen. You disagreed with me, but then posted an example that
proves my point exactly. What am I missing?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||On 29.11.2006 18:54, Tracy McKibben wrote:
> Robert Klemme wrote:
> ?
> I don't really know what you're debating here. I said that if IDNumber
> (the leading column) wasn't used in the WHERE clause, an index seek
> wouldn't happen. You disagreed with me, but then posted an example that
> proves my point exactly. What am I missing?
You said "If this is the only index available, then /only/ queries that
include IDNumber in the WHERE clause will seek against this index."
(accentuation by me). I objected that because /also/ queries that
contain /more leading columns/ from the index do a seek which is nicely
demonstrated by the plans I posted.
robert
No comments:
Post a Comment