March 16, 2012 at 11:21 am
Hello,
For my development I need all columns all the columns of the unique indexes of all tables of all schemas in a SQL Server database .
I created only one unique index (IDX_SID) on my a_test1_pk table.
But when I want to see all my unique indexes on the database, I see the names of the primarykey column,too(even I didn't create a unique index on it) .
So is my query false or on SQL Server: PK column, is unique index defined automatically ?
Here is my query:
SELECT s.name as [Schema],t.name as [Table],c.name [Column],i.name as [Index] FROM sys.index_columns ic
INNER JOIN
sys.tables t
on
t.object_id=ic.object_id
INNER JOIN
sys.columns c
ON
c.column_id = ic.column_id
AND
c.object_id=t.object_id
INNER JOIN
sys.schemas s
ON
t.schema_id=s.schema_id
INNER JOIN
sys.indexes i
on
i.index_id=ic.index_id
AND
i.object_id=t.object_id
where t.type='U'
and i.is_unique=1
March 16, 2012 at 11:30 am
Each primary key and each unique constraints are implemented with unique index. This is done for performance. Imagine that you have a table that has more then 10000000 rows and now you insert a new one. At that point the server has to know if there is a primary key violation or not. If the PK won't have an index, the server will have to scan the whole table. If it has an index, then it has to do only few read operation in order to decide if there is a primary key violation.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
March 16, 2012 at 11:34 am
aslhanhanc (3/16/2012)
So is my query false or on SQL Server: PK column, is unique index defined automatically ?
Yes.. Try this
use tempdb
go
if OBJECT_ID('a') is not null
drop table a
create table a ( i int identity primary key)
select OBJECT_ID , OBJECT_name(OBJECT_ID) tablename , name indexname , is_unique
from sys.indexes
where OBJECT_ID = OBJECT_ID('a')
if OBJECT_ID('a') is not null
drop table a
March 16, 2012 at 11:40 am
The Primary Key uniquely identifies each row in a table, so it kinda has to be unique 🙂
March 19, 2012 at 12:29 am
Thank you very much all
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply