title: Is there any way to mark existing clustered index as primary key in SQL Server?
body: <p>I have table with about 60 000 000 rows in it and having size near 60 GB.
  It has 2 indexes: clustered index and primary key on same id identity column.
  Primary key index has size near 1GB. It looks excessive. I have several such tables.</p>
  <p>Question is, is there any way to effectively mark existing clustered index as primary key also without dropping both indexes and creating one single new index?</p>
  <p>Sub question is does it worth it to do such operations, maybe only drop primary keys? What's the real practical advantage of having primary key except descriptive usage for 3rd party tools? maybe sql server optimizer uses this metadata to optimize queries or any other advantages that I am missing?</p>
  <p>Small sample of what I want to achieve, but other way (if it exists), without dropping and creating indexes.
  I assume that it looks like there's no other way really, but who knows maybe there's some trick.</p>
  <pre><code>create table a
  (
      id int identity,
      col1 varchar(50)
  )
  
  create unique clustered index cix_id on a(id)
  alter table a add constraint pk_a primary key nonclustered(id)
  
  select t.name,i.name,i.is_primary_key,i.type_desc from 
  sys.tables t
  inner join sys.indexes i on i.object_id=t.object_id
  where t.name='a'
  
  drop index cix_id on a
  alter table a drop constraint pk_a
  alter table a add constraint pk_a primary key clustered(id)
  
  select t.name,i.name,i.is_primary_key,i.type_desc from 
  sys.tables t
  inner join sys.indexes i on i.object_id=t.object_id
  where t.name='a'
  </code></pre>