Help with query...

• PiMané

  SSChampion

  Points: 13965

  More actions

  November 30, 2012 at 4:00 am

  #393711

  Hi,

  I have two tables that have the clustered index columns and the key columns from non clustered indexes.

  I want to get the columns existing on the leaf levels of the non clustered indexes.

  I have to get all the columns from the NC indexes plus the CL index columns that don't exist on the NC index.

  -- small example

  CREATE TABLE CI (o1 INT, c1 INT) -- clustered index columns (object_id, column_id)

  CREATE TABLE KI (o1 INT, i1 INT, c1 INT) -- NC index columns (object_id, index_id, column_id)

  -- some sample data

  INSERT INTO CI VALUES (1,1), (1,2), (2,1),(3,1)

  INSERT INTO KI VALUES (1, 1, 1), (1, 1, 2),(1, 2, 2),(2, 1, 2),(2, 1, 3),(3, 1, 1)

  With the following query I get the desired result but is there a simple way of doing it, with CROSS APPLY or something like that?!

  -- gets all the columns from the NC indexes

  SELECT k.o1, k.i1, k.c1, 0 type FROM KI k

  UNION ALL

  -- gets all the columns from the CL index joined with NC indexes (to get index_id) and where the columns doesn't exist on the NC index

  SELECT c.o1, k.i1, c.c1, 1 type FROM CI c INNER JOIN KI k ON c.o1 = k.o1 AND NOT EXISTS (SELECT 1 FROM KI k0 WHERE k0.o1 = c.o1 AND k0.c1 = c.c1 AND k0.i1 = k.i1)

  [/code="sql"]

  Thanks,

  Pedro

  ---

  
  If you need to work better, try working less...

• Gail Shaw

  SSC Guru

  Points: 1004514

  More actions

  November 30, 2012 at 4:02 am

  #1564253

  http://www.sqlskills.com/blogs/kimberly/post/A-new-and-improved-sp_helpindex-(jokingly-sp_helpindex8).aspx

  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