Weird pattern of behaviour

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  February 12, 2007 at 11:10 pm

  #689373

  Sorry about that. I read in something that wasn't there.

  I am interested in your experience with 2005, as mine has been exactly the opposite. I did a little experiment this morning. SQL version 9.00.1399.06, database in compat mode 90

  I have a table as follows with 5000 rows in (randomly generated data). There's a NC index on the Status column and the cluster is on the date. I ran 2 queries, as detailed below and on the first got an index seek, and the second got an index scan.

  CREATE

  TABLE dbo.IndexTest(

  ID uniqueidentifier NOT NULL DEFAULT (newid()),

  CreationDate datetime NOT NULL DEFAULT (getdate()),

  Description varchar(250) NOT NULL,

  Status varchar(7)  NOT NULL,

  CONSTRAINT pk_IndexTest PRIMARY KEY NONCLUSTERED (ID ASC)

  )

  The two test queries were as follows: Total records returned, 833

  select Status from IndexTest where status = '9NNQ1'

  select Status from IndexTest where status = N'9NNQ1'

  According to the exec plan, the first took 22% of the batch and did an index seek on the index on Status, the second took 78% and did an index scan on the index on Status. In the plan you can see the implicit conversion of the Status column in the second. Partial exec plan below.

  select Status from IndexTest where status = '9NNQ1'

  |--Index Seek(OBJECT: ([Testing].[dbo].[IndexTest].[idx_IndexTest_Status] ) , SEEK: ([Testing].[dbo].[IndexTest].[Status] = [@1] ) ORDERED FORWARD)

  select Status from IndexTest where status = N'9NNQ1'

  |--Index Scan(OBJECT: ([Testing].[dbo].[IndexTest].[idx_IndexTest_Status] ) , WHERE: (CONVERT_IMPLICIT(nvarchar(50),[Testing].[dbo].[IndexTest].[Status],0 ) =[@1] ) )

  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

• colin.Leversuch-Roberts

  SSC Guru

  Points: 52643

  More actions

  February 13, 2007 at 2:43 am

  #689390

  Hmm . I'll have to get back to you, I used a production database on a test server. my selects used a sarg of   " where col1 = @variable "

  I was testing a secondary index on a company name column returning 4 rows.

  [font="Comic Sans MS"]The GrumpyOldDBA[/font]
  www.grumpyolddba.co.uk
  http://sqlblogcasts.com/blogs/grumpyolddba/