December 29, 2009 at 5:45 am
hi
below is my
table with its index defintion
CREATE TABLE [dbo].[INVITEE](
[acct_id] [dbo].[ut_synthetic_key] NOT NULL,
[invitee_stub] [dbo].[ut_stub] NOT NULL,
[evt_stub] [dbo].[ut_stub] NOT NULL,
[invitee_status_id] [smallint] NOT NULL,
[cont_stub] [dbo].[ut_stub] NOT NULL,
[cont_type_stub] [dbo].[ut_stub] NOT NULL,
[responded_through_id] [dbo].[ut_synthetic_key] NOT NULL,
[invited_by_id] [smallint] NOT NULL,
[invitee_invited_by_cont_stub] [dbo].[ut_stub] NOT NULL,
[invitee_credit] [decimal](9, 2) NOT NULL,
[invitee_participant_flag] [dbo].[ut_flag] NOT NULL,
[invitee_original_response_date] [datetime] NOT NULL,
[invitee_reg_confirm_num] [dbo].[ut_friendly_code] NOT NULL,
[tl_stub] [dbo].[ut_stub] NOT NULL,
[invitee_last_registration_date] [datetime] NOT NULL CONSTRAINT [DF_INVITEE_invitee_last_registration_date] DEFAULT (''),
[invitee_last_cancellation_date] [datetime] NOT NULL CONSTRAINT [DF_INVITEE_invitee_last_cancellation_date] DEFAULT (''),
[invitee_last_waitlist_date] [datetime] NOT NULL CONSTRAINT [DF_INVITEE_invitee_last_waitlist_date] DEFAULT (''),
[reg_opt_stub] [dbo].[ut_stub] NOT NULL,
[invitee_comment1] [dbo].[ut_medium_long_description] NOT NULL,
[reference_id] [dbo].[ut_medium_long_description] NOT NULL,
[invitee_note] [dbo].[ut_long_description] NOT NULL,
[registration_last_modified_date] [datetime] NOT NULL CONSTRAINT [DF_INVITEE_registration_last_modified_date] DEFAULT (''),
[registration_last_modified_by] [dbo].[ut_medium_description] NOT NULL,
[created_date] [datetime] NOT NULL CONSTRAINT [DF_INVITEE_created_date] DEFAULT (getdate()),
[created_by] [dbo].[ut_medium_description] NOT NULL,
[last_modified_date] [datetime] NOT NULL,
[last_modified_by] [dbo].[ut_medium_description] NOT NULL
) ON [SECONDARYDATA]
------Here are indexes--------------------------------------------
clustered located on SECONDARYDATAacct_id, evt_stub, cont_stub
nonclustered located on PRIMARYINDEXacct_id, cont_stub
nonclustered located on PRIMARYINDEXacct_id, invitee_invited_by_cont_stub
nonclustered located on SECONDARYINDEXacct_id, invitee_reg_confirm_num
nonclustered located on SECONDARYINDEXacct_id, tl_stub, cont_stub
nonclustered located on SECONDARYINDEXinvitee_reg_confirm_num, acct_id
nonclustered, unique located on PRIMARYINDEX invitee_stub, acct_id
----------------------------------------------------------------
select acct_id , evt_stub from invitee
group by acct_id , evt_stub , cont_stub
having count(1) = 1
With above query , there is index scan but there is clustered index on ( acct_id, evt_stub, cont_stub)
why ??
And
select top 4 acct_id from invitee
where acct_id = 1173
this query using index seek but we have clustered index on ( acct_id, evt_stub, cont_stub)
we are not using other columns like cont_stub and evt_stub btu still it uses Clus index WHY ???
please help , i am confussed
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
December 29, 2009 at 6:14 am
Drop the nonclustered located on PRIMARYINDEX acct_id, cont_stub
and check.
December 29, 2009 at 6:14 am
The group by has to read all the values in the specified columns so it’s doing a scan over the index while your second select is looking up a single value. I don't understand what is confusing you with the above example its working as intended.
December 29, 2009 at 6:16 am
Never mind...
--Jeff Moden
December 29, 2009 at 6:36 am
my question is how it can go for
seek without cont_stub and evt_stub ?
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
December 29, 2009 at 6:46 am
yes vyas , i dropped the index from acct_id and cont_stub
and now query used "clustered index scan"
but i couldnt understand why it went for clustered index scan.?
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
December 29, 2009 at 6:56 am
Because acct_id is the first column in the index it is ordered by the acct_id and can do a seek on it. If you want a seek instead of a scan in the group by you will have to use a filter on a column.
December 29, 2009 at 7:16 am
Bhuvnesh (12/29/2009)
select acct_id , evt_stub from inviteegroup by acct_id , evt_stub , cont_stub
having count(1) = 1
With above query , there is index scan but there is clustered index on ( acct_id, evt_stub, cont_stub)
why ??
It's very simple. It did a scan because there is no SARGable predicate to seek on. To evaluate that query requires reading all the rows, doing the group by and then filtering on an aggregated expression.
To do a seek, there must be a SARGable predicate in the query and an index that matches that predicate. This means a predicate that is a comparison of a column (with no function specified) to a value or expression. The only predicate you have in your query is COUNT(*) = 1. That is not SARGable as it is a function, in fact an aggregate function.
select top 4 acct_id from invitee
where acct_id = 1173
this query using index seek but we have clustered index on ( acct_id, evt_stub, cont_stub)
we are not using other columns like cont_stub and evt_stub btu still it uses Clus index WHY ???
In this case, the predicate is SARGable. There is also a matching index, since the leading column of the clustered index is the column that the predicate is based on, the clustered index can be used for a seek. This is only possible because the acct_id column is the left-most column of the index. If it was anywhere else in the index, a seek would not be possible.
For info on indexes and seeks, see this series on indexes: http://qa.sqlservercentral.com/articles/Indexing/68439/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply