Indexing: How will you index this query??

Question

Indexing: How will you index this query??

aditya-457589

SSC Enthusiast

Points: 156
More actions
June 21, 2011 at 12:47 am

#252221

create table TB (StartDate datetime, StartTime datetime, EndDate datetime, EndTime datetime)
declare @dt datetime , @time datetime
select * from TB
where StartDate < @dt
AND StartTime< @time
AND (EndDate>=@dt OR EndTime >=@time)
In the above query how will you apply indexes???
--Whether it will be clustered on StartDate only? or Nounclustered on all four columns? or Nonclustered on EndDate and EndTime ? or anything else???
--In these type of queries how do we decide indexing???
--Can we rewrite this query in some more efficient way???
I came across this question recently and not finding proper answer to this, even I tried with dummy data and execution plan. But I am not able to come to any final conclusion
Plzzzzzzzzzzzzzz help.

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Non clustered on all four columns. Go read my blog (indexing category) for a post explaining why.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

aditya-457589 SSC Enthusiast Points: 156 More actions · Answer 2

aditya-457589

SSC Enthusiast

Points: 156

June 21, 2011 at 3:34 am

#1341697

Hey GilaMonster , thanks for the info.

aditya-457589 SSC Enthusiast Points: 156 More actions · Answer 3

I went through your "Indexing with ORs" http://sqlinthewild.co.za/index.php/2011/05/03/indexing-for-ors/comment-page-1/#comment-1703 article.

I tried few things at my end, in the arcticle you mentioned that first select query should result in 2 seeks with a merge join and second select query should result in indesx scan. For me both the queries are resulting in index scan.

CREATE TABLE Customers

(CustomerID INT IDENTITY PRIMARY KEY,

Surname VARCHAR(30) NOT NULL,

FirstName VARCHAR(30),

Title VARCHAR(5),

CustomerType CHAR(1) NOT NULL,

IsActive BIT DEFAULT 1 NOT NULL,

RegistrationDate DATETIME NOT NULL DEFAULT GETDATE() )

CREATE INDEX idx_Customers_IsActive ON dbo.Customers (IsActive) INCLUDE (FirstName, Surname)

CREATE INDEX idx_Customers_RegistrationDate ON dbo.Customers (RegistrationDate)

SELECT CustomerID FROM dbo.Customers WHERE IsActive = 1 OR RegistrationDate = '2010-01-24'

SELECT CustomerID,FirstName, Surname FROM dbo.Customers WHERE IsActive = 1 OR RegistrationDate = '2010-01-24'

Plz explain.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

Post Execution plans please.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

aditya-457589 SSC Enthusiast Points: 156 More actions · Answer 5

Hi,

Please find the attached bmp file for execution plan.

Orlando Colamatteo SSC Guru Points: 182276 More actions · Answer 6

Not a picture...please provide the actual execution plan saved as a .sqlplan file.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

Both of them.

Edit: As in, I need to see the .sqlplan file for both the queries (not both picture and .sqlplan)

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Orlando Colamatteo SSC Guru Points: 182276 More actions · Answer 8

GilaMonster (6/22/2011)
Both of them.

🙂

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Chris Howarth-536003 SSCommitted Points: 1775 More actions · Answer 9

aditya-457589 (6/21/2011)
create table TB (StartDate datetime, StartTime datetime, EndDate datetime, EndTime datetime)
declare @dt datetime , @time datetime
select * from TB
where StartDate < @dt
AND StartTime< @time
AND (EndDate>=@dt OR EndTime >=@time)
In the above query how will you apply indexes???
--Whether it will be clustered on StartDate only? or Nounclustered on all four columns? or Nonclustered on EndDate and EndTime ? or anything else???
--In these type of queries how do we decide indexing???
--Can we rewrite this query in some more efficient way???
I came across this question recently and not finding proper answer to this, even I tried with dummy data and execution plan. But I am not able to come to any final conclusion
Plzzzzzzzzzzzzzz help.

Is this a homework question where you're supposed to think for yourself?

Chris

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 10

Don't store date and time in separate fields. What is your reasoning for doing this. Also, I think your query will not return the results you expect with the given WHERE clause.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service