SQL slow datetime record search

• leetrueman

  SSC-Addicted

  Points: 439

  More actions

  June 23, 2015 at 8:46 am

  #1807411

  Thanks guys, the difference in speed is amazing 🙂

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  June 23, 2015 at 1:13 pm

  #1807530

  Alvin Ramard (6/23/2015)

  ---

  leetrueman (6/23/2015)

  ---

  The first one

  CREATE NONCLUSTERED INDEX idx_Transactions_TransTime

  ON dbo.transactions (trans_time, SiteID)

  INCLUDE (transactionID,siteID,areacode,gamename,sitename)

  GO

  is reporting

  Msg 1909, Level 16, State 2, Line 1

  Cannot use duplicate column names in index. Column name 'SiteID' listed more than once.

  ?

  Try removing siteID from the INCLUDE clause:

  CREATE NONCLUSTERED INDEX idx_Transactions_TransTime

  ON dbo.transactions (trans_time, SiteID)

  INCLUDE (transactionID, areacode, gamename, sitename)

  GO

  Sorry about that. Copy-paste error.

  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