execution plan

• niranjankumar_k

  Ten Centuries

  Points: 1075

  More actions

  May 24, 2008 at 7:42 am

  #182771

  Created non clustered index on this column (key) of 2 table a1, a2.. made a join with index column only .. and select non index column date in select list .. but execution plan shows table scan ....

  same query i choose index column in select list only no other changes in join .. however it shows index scan .. how it is possible

  ---->>>>>TABLE SCAN

  SELECT STG2.DT , STG1.DT

  FROM A1 STG1 (NOLOCK)

  JOIN A2 STG2 (NOLOCK)

  ON STG1.KEY = STG2.KEY

  |--Parallelism(Gather Streams)

  |--Hash Match(Inner Join, HASH:([STG1].)=([STG2].), RESIDUAL:([STG2].=[STG1].))

  |--Parallelism(Repartition Streams, PARTITION COLUMNS:([STG1].))

  | |--Table Scan(OBJECT:([QIW].[dbo].[A1] AS [STG1]))

  |--Parallelism(Repartition Streams, PARTITION COLUMNS:([STG2].))

  |--Table Scan(OBJECT:([QIW].[dbo].[A2] AS [STG2]))

  ---->>>>> INDEX SCAN

  SELECT STG1.KEY , STG2.KEY

  FROM A1 STG1 (NOLOCK)

  JOIN A2 STG2 (NOLOCK)

  ON STG1.KEY = STG2.KEY

  |--Parallelism(Gather Streams)

  |--Hash Match(Inner Join, HASH:([STG2].)=([STG1].), RESIDUAL:([STG2].=[STG1].))

  |--Parallelism(Repartition Streams, PARTITION COLUMNS:([STG2].))

  | |--Index Scan(OBJECT:([QIW].[dbo].[A2].[NC_INDEX1_A2] AS [STG2]))

  |--Parallelism(Repartition Streams, PARTITION COLUMNS:([STG1].))

  |--Index Scan(OBJECT:([QIW].[dbo].[A1].[NC_Index1_A1] AS [STG1]))

• Carl Federl

  One Orange Chip

  Points: 25384

  More actions

  May 24, 2008 at 9:05 am

  #819998

  Please provide DDL and sample data.

  For details, please read http://www.aspfaq.com/etiquette.asp?id=5006

  SQL = Scarcely Qualifies as a Language

• niranjankumar_k

  Ten Centuries

  Points: 1075

  More actions

  May 24, 2008 at 9:32 am

  #820001

  this both table has 1020 columns ...

  there is no relation ship .. constraint .. only this 2 column is having non cluster index .. nothing else ..

  and 4.5 lak data having ..

  if i include the index column in select i can see index scan .. but this is not possible .. in another place i have choosen 350 columns ...

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  May 24, 2008 at 2:03 pm

  #820010

  Table and index definitions please.

  How many rows do the two tables have? How many does this query return?

  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