Composite Primary key?

• Mallikarjuna

  SSC Veteran

  Points: 221

  More actions

  February 2, 2004 at 3:01 am

  #107614

  What type of index is created on a table if I create Composite Primary key?

• Frank Kalis

  SSC Guru

  Points: 111183

  More actions

  February 2, 2004 at 3:30 am

  #492458

  if object_id(N'[dbo].[MyIndex]') is not null

  drop table [dbo].[MyIndex]

  GO

  create table [dbo].[MyIndex] (

   [id] [char] (10) not null,

   [col1] [char] (20) not null

  ) on [PRIMARY]

  go

  alter table [dbo].[MyIndex] with nocheck add

   constraint [PK_MyIndex] primary key 

   (

    [id],

    [col1]

  &nbsp   on [PRIMARY]

  GO

  select indexproperty(object_id('MyIndex'),'PK_MyIndex', 'IsClustered')

             

  -----------

  1

  (1 row(s) affected)

  Unless you donot explicitely state NONCLUSTERED like this

  if object_id(N'[dbo].[MyIndex]') is not null

  drop table [dbo].[MyIndex]

  GO

  create table [dbo].[MyIndex] (

   [id] [char] (10) not null,

   [col1] [char] (20) not null

  ) on [PRIMARY]

  go

  alter table [dbo].[MyIndex] with nocheck add

   constraint [PK_MyIndex] primary key nonclustered

   (

    [id],

    [col1]

  &nbsp  on [PRIMARY]

  GO

  select indexproperty(object_id('MyIndex'),'PK_MyIndex', 'IsClustered')

             

  -----------

  0

  (1 row(s) affected)

  SQL Server creates a clustered index

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

• Frank Kalis

  SSC Guru

  Points: 111183

  More actions

  February 2, 2004 at 3:33 am

  #492459

  Hm,  change the smilie to a closing bracket

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