sql primary key and index

Question

Say I have an ID row  int  in a database set as the primary key  If I query off the ID often do I also need to index it  Or does it being a primary key mean it s already indexed   Reason I ask is because in MS SQL Server I can create an index on this ID  which as I stated is my primary key   Edit  an additional question - will it do any harm to additionally index the primary key

User · Answer

I have a huge database with no  separate  index   Any time I query by the primary key the results are  for all intensive purposes  instant

User · Answer

Here the passage from the MSDN      When you specify a PRIMARY KEY constraint for a table  the Database Engine enforces data uniqueness by creating a unique index for the primary key columns  This index also permits fast access to data when the primary key is used in queries  Therefore  the primary keys that are chosen must follow the rules for creating unique indexes

User · Answer

Primary keys are always indexed by default       You can define a primary key in SQL Server 2012 by using SQL Server Management Studio or Transact-SQL  Creating a primary key automatically creates a corresponding unique  clustered or nonclustered index    http   technet microsoft com en-us library ms189039 aspx

User · Answer

You are right  it s confusing that SQL Server allows you to create duplicate indexes on the same field s   But the fact that you can create another doesn t indicate that the PK index doesn t also already exist   The additional index does no good  but the only harm  very small  is the additional file size and row-creation overhead

User · Answer

Declaring a PRIMARY KEY or UNIQUE constraint causes SQL Server to automatically create an index     An unique index can be created without matching a constraint  but a constraint  either primary key or unique  cannot exist without having a unique index   From here  the creation of a constraint will    cause an index with the same name to be created deny dropping the created index as constraint is not allowed to exists without it   and at the same time dropping the constraint will drop the associated index   So  is there actual difference between a PRIMARY KEY or UNIQUE INDEX    NULL values are not allowed in PRIMARY KEY  but allowed in UNIQUE index  and like in set operators  UNION  EXCEPT  INTERSECT   here NULL   NULL which means that you can have only one value as two NULLs are find as duplicates of each other  only one PRIMARY KEY may exists per table while 999 unique indexes can be created when PRIMARY KEY constraint is created  it is created as clustered unless there is already a clustered index on the table or NONCLUSTERED is used in its definition  when UNIQUE index is created  it is created as NONCLUSTERED unless it is not specific to be CLUSTERED and such already does not exist

User · Answer

NOTE   This answer addresses enterprise-class development in-the-large   This is an RDBMS issue  not just SQL Server  and the behavior can be very interesting   For one  while it is common for primary keys to be automatically  uniquely  indexed  it is NOT absolute   There are times when it is essential that a primary key NOT be uniquely indexed   In most RDBMSs  a unique index will automatically be created on a primary key if one does not already exist   Therefore  you can create your own index on the primary key column before declaring it as a primary key  then that index will be used  if acceptable  by the database engine when you apply the primary key declaration   Often  you can create the primary key and allow its default unique index to be created  then create your own alternate index on that column  then drop the default index   Now for the fun part--when do you NOT want a unique primary key index   You don t want one  and can t tolerate one  when your table acquires enough data  rows  to make the maintenance of the index too expensive   This varies based on the hardware  the RDBMS engine  characteristics of the table and the database  and the system load   However  it typically begins to manifest once a table reaches a few million rows   The essential issue is that each insert of a row or update of the primary key column results in an index scan to ensure uniqueness   That unique index scan  or its equivalent in whichever RDBMS  becomes much more expensive as the table grows  until it dominates the performance of the table   I have dealt with this issue many times with tables as large as two billion rows  8 TBs of storage  and forty million row inserts per day   I was tasked to redesign the system involved  which included dropping the unique primary key index practically as step one   Indeed  dropping that index was necessary in production simply to recover from an outage  before we even got close to a redesign   That redesign included finding other ways to ensure the uniqueness of the primary key and to provide quick access to the data

User · Answer

As everyone else have already said  primary keys are automatically indexed   Creating more indexes on the primary key column only makes sense when you need to optimize a query that uses the primary key and some other specific columns  By creating another index on the primary key column and including some other columns with it  you may reach the desired optimization for a query   For example you have a table with many columns but you are only querying ID  Name and Address columns  Taking ID as the primary key  we can create the following index that is built on ID but includes Name and Address columns   CREATE NONCLUSTERED INDEX MyIndex ON MyTable ID  INCLUDE  Name  Address    So  when you use this query   SELECT ID  Name  Address FROM MyTable WHERE ID  gt  1000   SQL Server will give you the result only using the index you ve created and it ll not read anything from the actual table

User · Answer

Primary keys are always indexed by default       You can define a primary key in SQL Server 2012 by using SQL Server Management Studio or Transact-SQL  Creating a primary key automatically creates a corresponding unique  clustered or nonclustered index    http   technet microsoft com en-us library ms189039 aspx

User · Answer

Making it a primary key should also automatically create an index for it

User · Answer

Making it a primary key should also automatically create an index for it

User · Answer

Making it a primary key should also automatically create an index for it

User · Answer

a PK will become a clustered index unless you specify non clustered

User · Answer

primary keys are automatically indexed  you can create additional indices using the pk depending on your usage   index zip code  id may be helpful if you often select by zip code and id

User · Answer

I have a huge database with no  separate  index   Any time I query by the primary key the results are  for all intensive purposes  instant

User · Answer

Here the passage from the MSDN      When you specify a PRIMARY KEY constraint for a table  the Database Engine enforces data uniqueness by creating a unique index for the primary key columns  This index also permits fast access to data when the primary key is used in queries  Therefore  the primary keys that are chosen must follow the rules for creating unique indexes

User · Answer

Making it a primary key should also automatically create an index for it

User · Answer

Here the passage from the MSDN      When you specify a PRIMARY KEY constraint for a table  the Database Engine enforces data uniqueness by creating a unique index for the primary key columns  This index also permits fast access to data when the primary key is used in queries  Therefore  the primary keys that are chosen must follow the rules for creating unique indexes

User · Answer

You are right  it s confusing that SQL Server allows you to create duplicate indexes on the same field s   But the fact that you can create another doesn t indicate that the PK index doesn t also already exist   The additional index does no good  but the only harm  very small  is the additional file size and row-creation overhead

User · Answer

NOTE   This answer addresses enterprise-class development in-the-large   This is an RDBMS issue  not just SQL Server  and the behavior can be very interesting   For one  while it is common for primary keys to be automatically  uniquely  indexed  it is NOT absolute   There are times when it is essential that a primary key NOT be uniquely indexed   In most RDBMSs  a unique index will automatically be created on a primary key if one does not already exist   Therefore  you can create your own index on the primary key column before declaring it as a primary key  then that index will be used  if acceptable  by the database engine when you apply the primary key declaration   Often  you can create the primary key and allow its default unique index to be created  then create your own alternate index on that column  then drop the default index   Now for the fun part--when do you NOT want a unique primary key index   You don t want one  and can t tolerate one  when your table acquires enough data  rows  to make the maintenance of the index too expensive   This varies based on the hardware  the RDBMS engine  characteristics of the table and the database  and the system load   However  it typically begins to manifest once a table reaches a few million rows   The essential issue is that each insert of a row or update of the primary key column results in an index scan to ensure uniqueness   That unique index scan  or its equivalent in whichever RDBMS  becomes much more expensive as the table grows  until it dominates the performance of the table   I have dealt with this issue many times with tables as large as two billion rows  8 TBs of storage  and forty million row inserts per day   I was tasked to redesign the system involved  which included dropping the unique primary key index practically as step one   Indeed  dropping that index was necessary in production simply to recover from an outage  before we even got close to a redesign   That redesign included finding other ways to ensure the uniqueness of the primary key and to provide quick access to the data

User · Answer

Primary keys are always indexed by default       You can define a primary key in SQL Server 2012 by using SQL Server Management Studio or Transact-SQL  Creating a primary key automatically creates a corresponding unique  clustered or nonclustered index    http   technet microsoft com en-us library ms189039 aspx

User · Answer

primary keys are automatically indexed  you can create additional indices using the pk depending on your usage   index zip code  id may be helpful if you often select by zip code and id

User · Answer

Declaring a PRIMARY KEY or UNIQUE constraint causes SQL Server to automatically create an index     An unique index can be created without matching a constraint  but a constraint  either primary key or unique  cannot exist without having a unique index   From here  the creation of a constraint will    cause an index with the same name to be created deny dropping the created index as constraint is not allowed to exists without it   and at the same time dropping the constraint will drop the associated index   So  is there actual difference between a PRIMARY KEY or UNIQUE INDEX    NULL values are not allowed in PRIMARY KEY  but allowed in UNIQUE index  and like in set operators  UNION  EXCEPT  INTERSECT   here NULL   NULL which means that you can have only one value as two NULLs are find as duplicates of each other  only one PRIMARY KEY may exists per table while 999 unique indexes can be created when PRIMARY KEY constraint is created  it is created as clustered unless there is already a clustered index on the table or NONCLUSTERED is used in its definition  when UNIQUE index is created  it is created as NONCLUSTERED unless it is not specific to be CLUSTERED and such already does not exist

User · Answer

NOTE   This answer addresses enterprise-class development in-the-large   This is an RDBMS issue  not just SQL Server  and the behavior can be very interesting   For one  while it is common for primary keys to be automatically  uniquely  indexed  it is NOT absolute   There are times when it is essential that a primary key NOT be uniquely indexed   In most RDBMSs  a unique index will automatically be created on a primary key if one does not already exist   Therefore  you can create your own index on the primary key column before declaring it as a primary key  then that index will be used  if acceptable  by the database engine when you apply the primary key declaration   Often  you can create the primary key and allow its default unique index to be created  then create your own alternate index on that column  then drop the default index   Now for the fun part--when do you NOT want a unique primary key index   You don t want one  and can t tolerate one  when your table acquires enough data  rows  to make the maintenance of the index too expensive   This varies based on the hardware  the RDBMS engine  characteristics of the table and the database  and the system load   However  it typically begins to manifest once a table reaches a few million rows   The essential issue is that each insert of a row or update of the primary key column results in an index scan to ensure uniqueness   That unique index scan  or its equivalent in whichever RDBMS  becomes much more expensive as the table grows  until it dominates the performance of the table   I have dealt with this issue many times with tables as large as two billion rows  8 TBs of storage  and forty million row inserts per day   I was tasked to redesign the system involved  which included dropping the unique primary key index practically as step one   Indeed  dropping that index was necessary in production simply to recover from an outage  before we even got close to a redesign   That redesign included finding other ways to ensure the uniqueness of the primary key and to provide quick access to the data

User · Answer

primary keys are automatically indexed  you can create additional indices using the pk depending on your usage   index zip code  id may be helpful if you often select by zip code and id

User · Answer

Well in SQL Server  generally  primary key is automatically indexed  This is true  but it not guaranteed of faster query  The primary key will give you excellent performance when there is only 1 field as primary key  But  when there are multiple field as primary key  then the index is based on those fields    For example  Field A  B  C are the primary key  thus when you do query based on those 3 fields in your WHERE CLAUSE  the performance is good   BUT when you want to query with Only C field in the WHERE CLAUSE  you wont get good performance  Thus  to get your performance up and running  you will need to index C field manually   Most of the time  you wont see the issue till you hits more than 1 million records

User · Answer

a PK will become a clustered index unless you specify non clustered

User · Answer

I have a huge database with no  separate  index   Any time I query by the primary key the results are  for all intensive purposes  instant

User · Answer

a PK will become a clustered index unless you specify non clustered

User · Answer

Primary keys are always indexed by default       You can define a primary key in SQL Server 2012 by using SQL Server Management Studio or Transact-SQL  Creating a primary key automatically creates a corresponding unique  clustered or nonclustered index    http   technet microsoft com en-us library ms189039 aspx

User · Answer

As everyone else have already said  primary keys are automatically indexed   Creating more indexes on the primary key column only makes sense when you need to optimize a query that uses the primary key and some other specific columns  By creating another index on the primary key column and including some other columns with it  you may reach the desired optimization for a query   For example you have a table with many columns but you are only querying ID  Name and Address columns  Taking ID as the primary key  we can create the following index that is built on ID but includes Name and Address columns   CREATE NONCLUSTERED INDEX MyIndex ON MyTable ID  INCLUDE  Name  Address    So  when you use this query   SELECT ID  Name  Address FROM MyTable WHERE ID  gt  1000   SQL Server will give you the result only using the index you ve created and it ll not read anything from the actual table

User · Answer

NOTE   This answer addresses enterprise-class development in-the-large   This is an RDBMS issue  not just SQL Server  and the behavior can be very interesting   For one  while it is common for primary keys to be automatically  uniquely  indexed  it is NOT absolute   There are times when it is essential that a primary key NOT be uniquely indexed   In most RDBMSs  a unique index will automatically be created on a primary key if one does not already exist   Therefore  you can create your own index on the primary key column before declaring it as a primary key  then that index will be used  if acceptable  by the database engine when you apply the primary key declaration   Often  you can create the primary key and allow its default unique index to be created  then create your own alternate index on that column  then drop the default index   Now for the fun part--when do you NOT want a unique primary key index   You don t want one  and can t tolerate one  when your table acquires enough data  rows  to make the maintenance of the index too expensive   This varies based on the hardware  the RDBMS engine  characteristics of the table and the database  and the system load   However  it typically begins to manifest once a table reaches a few million rows   The essential issue is that each insert of a row or update of the primary key column results in an index scan to ensure uniqueness   That unique index scan  or its equivalent in whichever RDBMS  becomes much more expensive as the table grows  until it dominates the performance of the table   I have dealt with this issue many times with tables as large as two billion rows  8 TBs of storage  and forty million row inserts per day   I was tasked to redesign the system involved  which included dropping the unique primary key index practically as step one   Indeed  dropping that index was necessary in production simply to recover from an outage  before we even got close to a redesign   That redesign included finding other ways to ensure the uniqueness of the primary key and to provide quick access to the data

User · Answer

Well in SQL Server  generally  primary key is automatically indexed  This is true  but it not guaranteed of faster query  The primary key will give you excellent performance when there is only 1 field as primary key  But  when there are multiple field as primary key  then the index is based on those fields    For example  Field A  B  C are the primary key  thus when you do query based on those 3 fields in your WHERE CLAUSE  the performance is good   BUT when you want to query with Only C field in the WHERE CLAUSE  you wont get good performance  Thus  to get your performance up and running  you will need to index C field manually   Most of the time  you wont see the issue till you hits more than 1 million records

User · Answer

a PK will become a clustered index unless you specify non clustered

User · Answer

As everyone else have already said  primary keys are automatically indexed   Creating more indexes on the primary key column only makes sense when you need to optimize a query that uses the primary key and some other specific columns  By creating another index on the primary key column and including some other columns with it  you may reach the desired optimization for a query   For example you have a table with many columns but you are only querying ID  Name and Address columns  Taking ID as the primary key  we can create the following index that is built on ID but includes Name and Address columns   CREATE NONCLUSTERED INDEX MyIndex ON MyTable ID  INCLUDE  Name  Address    So  when you use this query   SELECT ID  Name  Address FROM MyTable WHERE ID  gt  1000   SQL Server will give you the result only using the index you ve created and it ll not read anything from the actual table

User · Answer

Here the passage from the MSDN      When you specify a PRIMARY KEY constraint for a table  the Database Engine enforces data uniqueness by creating a unique index for the primary key columns  This index also permits fast access to data when the primary key is used in queries  Therefore  the primary keys that are chosen must follow the rules for creating unique indexes

User · Answer

I have a huge database with no  separate  index   Any time I query by the primary key the results are  for all intensive purposes  instant

User · Answer

You are right  it s confusing that SQL Server allows you to create duplicate indexes on the same field s   But the fact that you can create another doesn t indicate that the PK index doesn t also already exist   The additional index does no good  but the only harm  very small  is the additional file size and row-creation overhead

User · Answer

You are right  it s confusing that SQL Server allows you to create duplicate indexes on the same field s   But the fact that you can create another doesn t indicate that the PK index doesn t also already exist   The additional index does no good  but the only harm  very small  is the additional file size and row-creation overhead

User · Answer

primary keys are automatically indexed  you can create additional indices using the pk depending on your usage   index zip code  id may be helpful if you often select by zip code and id

User · Answer

As everyone else have already said  primary keys are automatically indexed   Creating more indexes on the primary key column only makes sense when you need to optimize a query that uses the primary key and some other specific columns  By creating another index on the primary key column and including some other columns with it  you may reach the desired optimization for a query   For example you have a table with many columns but you are only querying ID  Name and Address columns  Taking ID as the primary key  we can create the following index that is built on ID but includes Name and Address columns   CREATE NONCLUSTERED INDEX MyIndex ON MyTable ID  INCLUDE  Name  Address    So  when you use this query   SELECT ID  Name  Address FROM MyTable WHERE ID  gt  1000   SQL Server will give you the result only using the index you ve created and it ll not read anything from the actual table

[sql] sql primary key and index

Examples related to sql

Examples related to sql-server

Examples related to tsql

Examples related to indexing

Examples related to primary-key