Composite Clustered Index and NULL values

• Clive Strong

  SSChampion

  Points: 11222

  More actions

  March 17, 2005 at 6:01 am

  #102440

  Hey All,

  Quick quiestion.  Can a composite clustered index contain null values?

  The clustered index has 3 columns.  Say, columns A, B & C.  Column C contains non null values.

  When you run a DBCC DBREINDEX against it or (in test) drop the clustered index and rebuild it I get a SQLDumpExceptionHandler error.  I created a composite clustered index using columns A & B and this works.  When I try to add in column C it fails, which leads me to believe that it doesn't like the NULL values (of which there are some 1800 out of 90,000 rows).

  I just need some clarification so I can discuss this with the software vendor.

  Cheers

• Clive Strong

  SSChampion

  Points: 11222

  More actions

  March 17, 2005 at 6:18 am

  #547510

  Sorry, typo in there.

  Columns A & B contain no NULLS.  Only column C contains NULL values.

• Frank Kalis

  SSC Guru

  Points: 111183

  More actions

  March 17, 2005 at 6:37 am

  #547512

  Not sure if I understand you here, but this works for me without errors

  use tempdb

  create table #test

  (

   c1 int null

   , c2 int null

   , c3 int not null default 1

  )

  create clustered index ci_test on #test(c1, c2, c3)

  insert into #test(c3) values(2)

  dbcc dbreindex('tempdb..#test')

  select * from #test

  drop table #test

  Any entries in the log files?

  --
  Frank Kalis
  Microsoft SQL Server MVP
  Webmaster: http://www.insidesql.org/blogs
  My blog: http://www.insidesql.org/blogs/frankkalis/[/url]