PK Index in SQL Server

I'm a little confused on the details of how a PK index is implemented in SQL
Server.
It looks like when you say "ADD CONSTRAINT _name_ PRIMARY KEY (_col_) ON
_filegroup_" you are specifying the index the PK will use in its entirety.
What I'm accustomed to (under Informix) is to create the table, create the
index the constraint will use and then create the constraint, which will
detect the appropriate index and use it (Informix has much more robust
storage options, which are available in create index, but not add
constraint).
However, under SQL Server when I do this, I seem to get two unique indexes
on the same column(s). Also, SQL Server seems to limit indexes to a single
filegroup negating anything fancy in the first place (this is still true
with DPV's).
So, I'm guessing that the ADD CONSTRAINT clause in T-SQL will create its own
index, period. Also, except for the entry in sysconstraints, a constraint
and an index are implemented completely identically in SQL Server.
Am I correct?
Thanks,
JayHi Jay,
In SQL Server you create a primary key, adding a constraint. The way SS
implement such constraint, is creating a unique index, behind the scene, that
could be clustered or nonclustered. If you do not specify what kind of index
to use, SS will use clustered by default. It will be the same with UNIQUE
constraints.
You can not disable a primary key or unique constraint using "alter table
... nocheck constraint_name", but you can disable the index associated to the
costraint. Notice that if the index is a clustered one, then disabling it
will stop you from inserting into the table.
CREATE TABLE dbo.t(
c1 INT NOT NULL CONSTRAINT pk_t PRIMARY KEY NONCLUSTERED,
c2 INT NOT NULL CONSTRAINT uq_c2 UNIQUE NONCLUSTERED
)
GO
ALTER TABLE dbo.t
NOCHECK constraint pk_t
GO
ALTER TABLE dbo.t
NOCHECK constraint uq_c2
GO
SELECT
OBJECTPROPERTY(OBJECT_ID('pk_t'), 'CnstIsDisabled '),
OBJECTPROPERTY(OBJECT_ID('uq_c2'), 'CnstIsDisabled ')
GO
ALTER INDEX pk_t ON dbo.t DISABLE
go
ALTER INDEX uq_c2 ON dbo.t DISABLE
go
SELECT
OBJECTPROPERTY(OBJECT_ID('pk_t'), 'CnstIsDisabled '),
OBJECTPROPERTY(OBJECT_ID('uq_c2'), 'CnstIsDisabled ')
GO
INSERT INTO dbo.t VALUES(1, 1)
GO
INSERT INTO dbo.t VALUES(2, 1)
GO
INSERT INTO dbo.t VALUES(2, 1)
GO
SELECT * FROM dbo.[t]
GO
DROP TABLE dbo.[t]
GO
AMB
"Jay" wrote:
> I'm a little confused on the details of how a PK index is implemented in SQL
> Server.
> It looks like when you say "ADD CONSTRAINT _name_ PRIMARY KEY (_col_) ON
> _filegroup_" you are specifying the index the PK will use in its entirety.
> What I'm accustomed to (under Informix) is to create the table, create the
> index the constraint will use and then create the constraint, which will
> detect the appropriate index and use it (Informix has much more robust
> storage options, which are available in create index, but not add
> constraint).
> However, under SQL Server when I do this, I seem to get two unique indexes
> on the same column(s). Also, SQL Server seems to limit indexes to a single
> filegroup negating anything fancy in the first place (this is still true
> with DPV's).
> So, I'm guessing that the ADD CONSTRAINT clause in T-SQL will create its own
> index, period. Also, except for the entry in sysconstraints, a constraint
> and an index are implemented completely identically in SQL Server.
> Am I correct?
> Thanks,
> Jay
>
>|||Thank Alejandro,
So, if I understand you correctly, with the exceptions of focusing on
clustering and disabling indexes, adding a primary key constraing is the
same as adding an index, except that you get the extra stuff that comes with
a primary key.
Thanks,
Jay
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:2B48C256-79BC-45C6-92A4-13D940FD6980@.microsoft.com...
> Hi Jay,
> In SQL Server you create a primary key, adding a constraint. The way SS
> implement such constraint, is creating a unique index, behind the scene,
> that
> could be clustered or nonclustered. If you do not specify what kind of
> index
> to use, SS will use clustered by default. It will be the same with UNIQUE
> constraints.
> You can not disable a primary key or unique constraint using "alter table
> ... nocheck constraint_name", but you can disable the index associated to
> the
> costraint. Notice that if the index is a clustered one, then disabling it
> will stop you from inserting into the table.
>
> CREATE TABLE dbo.t(
> c1 INT NOT NULL CONSTRAINT pk_t PRIMARY KEY NONCLUSTERED,
> c2 INT NOT NULL CONSTRAINT uq_c2 UNIQUE NONCLUSTERED
> )
> GO
> ALTER TABLE dbo.t
> NOCHECK constraint pk_t
> GO
> ALTER TABLE dbo.t
> NOCHECK constraint uq_c2
> GO
> SELECT
> OBJECTPROPERTY(OBJECT_ID('pk_t'), 'CnstIsDisabled '),
> OBJECTPROPERTY(OBJECT_ID('uq_c2'), 'CnstIsDisabled ')
> GO
> ALTER INDEX pk_t ON dbo.t DISABLE
> go
> ALTER INDEX uq_c2 ON dbo.t DISABLE
> go
> SELECT
> OBJECTPROPERTY(OBJECT_ID('pk_t'), 'CnstIsDisabled '),
> OBJECTPROPERTY(OBJECT_ID('uq_c2'), 'CnstIsDisabled ')
> GO
> INSERT INTO dbo.t VALUES(1, 1)
> GO
> INSERT INTO dbo.t VALUES(2, 1)
> GO
> INSERT INTO dbo.t VALUES(2, 1)
> GO
> SELECT * FROM dbo.[t]
> GO
> DROP TABLE dbo.[t]
> GO
>
> AMB
>
> "Jay" wrote:
>> I'm a little confused on the details of how a PK index is implemented in
>> SQL
>> Server.
>> It looks like when you say "ADD CONSTRAINT _name_ PRIMARY KEY (_col_) ON
>> _filegroup_" you are specifying the index the PK will use in its
>> entirety.
>> What I'm accustomed to (under Informix) is to create the table, create
>> the
>> index the constraint will use and then create the constraint, which will
>> detect the appropriate index and use it (Informix has much more robust
>> storage options, which are available in create index, but not add
>> constraint).
>> However, under SQL Server when I do this, I seem to get two unique
>> indexes
>> on the same column(s). Also, SQL Server seems to limit indexes to a
>> single
>> filegroup negating anything fancy in the first place (this is still true
>> with DPV's).
>> So, I'm guessing that the ADD CONSTRAINT clause in T-SQL will create its
>> own
>> index, period. Also, except for the entry in sysconstraints, a constraint
>> and an index are implemented completely identically in SQL Server.
>> Am I correct?
>> Thanks,
>> Jay
>>|||> So, if I understand you correctly, with the exceptions of focusing on clustering and disabling
> indexes, adding a primary key constraing is the same as adding an index, except that you get the
> extra stuff that comes with a primary key.
Yes. A PK and UQ constraint is "implemented" though a unique index, which is created for you when
you defined (add) your constraint. The index has the same name as the constraint. This is also why
the constraint definition is "littered" with physical attributes like clustered/nonclustered and
filegroup information. It is just a matter of getting used to that this is the way they've done it.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Jay" <spam@.nospam.org> wrote in message news:ejzlYVICIHA.3916@.TK2MSFTNGP02.phx.gbl...
> Thank Alejandro,
> So, if I understand you correctly, with the exceptions of focusing on clustering and disabling
> indexes, adding a primary key constraing is the same as adding an index, except that you get the
> extra stuff that comes with a primary key.
> Thanks,
> Jay
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
> news:2B48C256-79BC-45C6-92A4-13D940FD6980@.microsoft.com...
>> Hi Jay,
>> In SQL Server you create a primary key, adding a constraint. The way SS
>> implement such constraint, is creating a unique index, behind the scene, that
>> could be clustered or nonclustered. If you do not specify what kind of index
>> to use, SS will use clustered by default. It will be the same with UNIQUE
>> constraints.
>> You can not disable a primary key or unique constraint using "alter table
>> ... nocheck constraint_name", but you can disable the index associated to the
>> costraint. Notice that if the index is a clustered one, then disabling it
>> will stop you from inserting into the table.
>>
>> CREATE TABLE dbo.t(
>> c1 INT NOT NULL CONSTRAINT pk_t PRIMARY KEY NONCLUSTERED,
>> c2 INT NOT NULL CONSTRAINT uq_c2 UNIQUE NONCLUSTERED
>> )
>> GO
>> ALTER TABLE dbo.t
>> NOCHECK constraint pk_t
>> GO
>> ALTER TABLE dbo.t
>> NOCHECK constraint uq_c2
>> GO
>> SELECT
>> OBJECTPROPERTY(OBJECT_ID('pk_t'), 'CnstIsDisabled '),
>> OBJECTPROPERTY(OBJECT_ID('uq_c2'), 'CnstIsDisabled ')
>> GO
>> ALTER INDEX pk_t ON dbo.t DISABLE
>> go
>> ALTER INDEX uq_c2 ON dbo.t DISABLE
>> go
>> SELECT
>> OBJECTPROPERTY(OBJECT_ID('pk_t'), 'CnstIsDisabled '),
>> OBJECTPROPERTY(OBJECT_ID('uq_c2'), 'CnstIsDisabled ')
>> GO
>> INSERT INTO dbo.t VALUES(1, 1)
>> GO
>> INSERT INTO dbo.t VALUES(2, 1)
>> GO
>> INSERT INTO dbo.t VALUES(2, 1)
>> GO
>> SELECT * FROM dbo.[t]
>> GO
>> DROP TABLE dbo.[t]
>> GO
>>
>> AMB
>>
>> "Jay" wrote:
>> I'm a little confused on the details of how a PK index is implemented in SQL
>> Server.
>> It looks like when you say "ADD CONSTRAINT _name_ PRIMARY KEY (_col_) ON
>> _filegroup_" you are specifying the index the PK will use in its entirety.
>> What I'm accustomed to (under Informix) is to create the table, create the
>> index the constraint will use and then create the constraint, which will
>> detect the appropriate index and use it (Informix has much more robust
>> storage options, which are available in create index, but not add
>> constraint).
>> However, under SQL Server when I do this, I seem to get two unique indexes
>> on the same column(s). Also, SQL Server seems to limit indexes to a single
>> filegroup negating anything fancy in the first place (this is still true
>> with DPV's).
>> So, I'm guessing that the ADD CONSTRAINT clause in T-SQL will create its own
>> index, period. Also, except for the entry in sysconstraints, a constraint
>> and an index are implemented completely identically in SQL Server.
>> Am I correct?
>> Thanks,
>> Jay
>>
>|||Technically, yes.
However, the point of having constraints and indexes is because
logically, they have different purposes.
If you are adding a constraint (such as a Primary Key), you are saying
something about your data, you are determining "business rules". With a
Primary Key you are saying: this is the key that identifies the row, and
it should be unique. Then, it is up to the RDBMS how it enforces this
constraint. If you have a compound primary key, then from this point of
view, the order of the columns does not matter.
The current and all past versions of SQL Server enforce a Primary Key
and Unique Constraint with a unique index. This could be changed in the
future. Maybe one day SQL Server might enforce it using hashing or some
other technique.
If you add an index, you are optimizing your data access. When you add a
compound index, the column order matters a lot, because of the nature of
B-tree indexes. It also matters because a join between two tables with a
compound key in different order is very inefficient.
I consider it a good practice to always use constraints to determine
keys and uniqueness, and to use indexes for additional tuning. This way,
the person optimizing the data access knows which indexes can be removed
without destroying integrity, and the person (re)modelling the schema
does not have to look at indexes.
--
Gert-Jan
Jay wrote:
> Thank Alejandro,
> So, if I understand you correctly, with the exceptions of focusing on
> clustering and disabling indexes, adding a primary key constraing is the
> same as adding an index, except that you get the extra stuff that comes with
> a primary key.
> Thanks,
> Jay
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
> news:2B48C256-79BC-45C6-92A4-13D940FD6980@.microsoft.com...
> > Hi Jay,
> >
> > In SQL Server you create a primary key, adding a constraint. The way SS
> > implement such constraint, is creating a unique index, behind the scene,
> > that
> > could be clustered or nonclustered. If you do not specify what kind of
> > index
> > to use, SS will use clustered by default. It will be the same with UNIQUE
> > constraints.
> >
> > You can not disable a primary key or unique constraint using "alter table
> > ... nocheck constraint_name", but you can disable the index associated to
> > the
> > costraint. Notice that if the index is a clustered one, then disabling it
> > will stop you from inserting into the table.
> >
> >
> > CREATE TABLE dbo.t(
> > c1 INT NOT NULL CONSTRAINT pk_t PRIMARY KEY NONCLUSTERED,
> > c2 INT NOT NULL CONSTRAINT uq_c2 UNIQUE NONCLUSTERED
> > )
> > GO
> >
> > ALTER TABLE dbo.t
> > NOCHECK constraint pk_t
> > GO
> >
> > ALTER TABLE dbo.t
> > NOCHECK constraint uq_c2
> > GO
> >
> > SELECT
> > OBJECTPROPERTY(OBJECT_ID('pk_t'), 'CnstIsDisabled '),
> > OBJECTPROPERTY(OBJECT_ID('uq_c2'), 'CnstIsDisabled ')
> > GO
> >
> > ALTER INDEX pk_t ON dbo.t DISABLE
> > go
> >
> > ALTER INDEX uq_c2 ON dbo.t DISABLE
> > go
> >
> > SELECT
> > OBJECTPROPERTY(OBJECT_ID('pk_t'), 'CnstIsDisabled '),
> > OBJECTPROPERTY(OBJECT_ID('uq_c2'), 'CnstIsDisabled ')
> > GO
> >
> > INSERT INTO dbo.t VALUES(1, 1)
> > GO
> >
> > INSERT INTO dbo.t VALUES(2, 1)
> > GO
> >
> > INSERT INTO dbo.t VALUES(2, 1)
> > GO
> >
> > SELECT * FROM dbo.[t]
> > GO
> >
> > DROP TABLE dbo.[t]
> > GO
> >
> >
> > AMB
> >
> >
> > "Jay" wrote:
> >
> >> I'm a little confused on the details of how a PK index is implemented in
> >> SQL
> >> Server.
> >>
> >> It looks like when you say "ADD CONSTRAINT _name_ PRIMARY KEY (_col_) ON
> >> _filegroup_" you are specifying the index the PK will use in its
> >> entirety.
> >>
> >> What I'm accustomed to (under Informix) is to create the table, create
> >> the
> >> index the constraint will use and then create the constraint, which will
> >> detect the appropriate index and use it (Informix has much more robust
> >> storage options, which are available in create index, but not add
> >> constraint).
> >>
> >> However, under SQL Server when I do this, I seem to get two unique
> >> indexes
> >> on the same column(s). Also, SQL Server seems to limit indexes to a
> >> single
> >> filegroup negating anything fancy in the first place (this is still true
> >> with DPV's).
> >>
> >> So, I'm guessing that the ADD CONSTRAINT clause in T-SQL will create its
> >> own
> >> index, period. Also, except for the entry in sysconstraints, a constraint
> >> and an index are implemented completely identically in SQL Server.
> >>
> >> Am I correct?
> >>
> >> Thanks,
> >> Jay
> >>
> >>
> >>|||Jay,
You got very good answers from Tibor and Gert-Jan. It is important, at least
for me, to point what Gert-Jan already mentioned, that primary key is part of
the models earlier than the physical. We do not talk about indexes, during
logical and conceptual models, to enforce a constraint.
AMB
"Jay" wrote:
> Thank Alejandro,
> So, if I understand you correctly, with the exceptions of focusing on
> clustering and disabling indexes, adding a primary key constraing is the
> same as adding an index, except that you get the extra stuff that comes with
> a primary key.
> Thanks,
> Jay
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
> news:2B48C256-79BC-45C6-92A4-13D940FD6980@.microsoft.com...
> > Hi Jay,
> >
> > In SQL Server you create a primary key, adding a constraint. The way SS
> > implement such constraint, is creating a unique index, behind the scene,
> > that
> > could be clustered or nonclustered. If you do not specify what kind of
> > index
> > to use, SS will use clustered by default. It will be the same with UNIQUE
> > constraints.
> >
> > You can not disable a primary key or unique constraint using "alter table
> > ... nocheck constraint_name", but you can disable the index associated to
> > the
> > costraint. Notice that if the index is a clustered one, then disabling it
> > will stop you from inserting into the table.
> >
> >
> > CREATE TABLE dbo.t(
> > c1 INT NOT NULL CONSTRAINT pk_t PRIMARY KEY NONCLUSTERED,
> > c2 INT NOT NULL CONSTRAINT uq_c2 UNIQUE NONCLUSTERED
> > )
> > GO
> >
> > ALTER TABLE dbo.t
> > NOCHECK constraint pk_t
> > GO
> >
> > ALTER TABLE dbo.t
> > NOCHECK constraint uq_c2
> > GO
> >
> > SELECT
> > OBJECTPROPERTY(OBJECT_ID('pk_t'), 'CnstIsDisabled '),
> > OBJECTPROPERTY(OBJECT_ID('uq_c2'), 'CnstIsDisabled ')
> > GO
> >
> > ALTER INDEX pk_t ON dbo.t DISABLE
> > go
> >
> > ALTER INDEX uq_c2 ON dbo.t DISABLE
> > go
> >
> > SELECT
> > OBJECTPROPERTY(OBJECT_ID('pk_t'), 'CnstIsDisabled '),
> > OBJECTPROPERTY(OBJECT_ID('uq_c2'), 'CnstIsDisabled ')
> > GO
> >
> > INSERT INTO dbo.t VALUES(1, 1)
> > GO
> >
> > INSERT INTO dbo.t VALUES(2, 1)
> > GO
> >
> > INSERT INTO dbo.t VALUES(2, 1)
> > GO
> >
> > SELECT * FROM dbo.[t]
> > GO
> >
> > DROP TABLE dbo.[t]
> > GO
> >
> >
> > AMB
> >
> >
> > "Jay" wrote:
> >
> >> I'm a little confused on the details of how a PK index is implemented in
> >> SQL
> >> Server.
> >>
> >> It looks like when you say "ADD CONSTRAINT _name_ PRIMARY KEY (_col_) ON
> >> _filegroup_" you are specifying the index the PK will use in its
> >> entirety.
> >>
> >> What I'm accustomed to (under Informix) is to create the table, create
> >> the
> >> index the constraint will use and then create the constraint, which will
> >> detect the appropriate index and use it (Informix has much more robust
> >> storage options, which are available in create index, but not add
> >> constraint).
> >>
> >> However, under SQL Server when I do this, I seem to get two unique
> >> indexes
> >> on the same column(s). Also, SQL Server seems to limit indexes to a
> >> single
> >> filegroup negating anything fancy in the first place (this is still true
> >> with DPV's).
> >>
> >> So, I'm guessing that the ADD CONSTRAINT clause in T-SQL will create its
> >> own
> >> index, period. Also, except for the entry in sysconstraints, a constraint
> >> and an index are implemented completely identically in SQL Server.
> >>
> >> Am I correct?
> >>
> >> Thanks,
> >> Jay
> >>
> >>
> >>
>
>|||Thanks Alejandro,
But I have no issues with the logical constructs as I've been working with
relational databases for just under 20 years, it was just the physical
implementation I needed to grasp.
Jay
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:2700F07B-B429-4829-BD6A-83DDDB464703@.microsoft.com...
> Jay,
> You got very good answers from Tibor and Gert-Jan. It is important, at
> least
> for me, to point what Gert-Jan already mentioned, that primary key is part
> of
> the models earlier than the physical. We do not talk about indexes, during
> logical and conceptual models, to enforce a constraint.
>
> AMB
> "Jay" wrote:
>> Thank Alejandro,
>> So, if I understand you correctly, with the exceptions of focusing on
>> clustering and disabling indexes, adding a primary key constraing is the
>> same as adding an index, except that you get the extra stuff that comes
>> with
>> a primary key.
>> Thanks,
>> Jay
>> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in
>> message
>> news:2B48C256-79BC-45C6-92A4-13D940FD6980@.microsoft.com...
>> > Hi Jay,
>> >
>> > In SQL Server you create a primary key, adding a constraint. The way SS
>> > implement such constraint, is creating a unique index, behind the
>> > scene,
>> > that
>> > could be clustered or nonclustered. If you do not specify what kind of
>> > index
>> > to use, SS will use clustered by default. It will be the same with
>> > UNIQUE
>> > constraints.
>> >
>> > You can not disable a primary key or unique constraint using "alter
>> > table
>> > ... nocheck constraint_name", but you can disable the index associated
>> > to
>> > the
>> > costraint. Notice that if the index is a clustered one, then disabling
>> > it
>> > will stop you from inserting into the table.
>> >
>> >
>> > CREATE TABLE dbo.t(
>> > c1 INT NOT NULL CONSTRAINT pk_t PRIMARY KEY NONCLUSTERED,
>> > c2 INT NOT NULL CONSTRAINT uq_c2 UNIQUE NONCLUSTERED
>> > )
>> > GO
>> >
>> > ALTER TABLE dbo.t
>> > NOCHECK constraint pk_t
>> > GO
>> >
>> > ALTER TABLE dbo.t
>> > NOCHECK constraint uq_c2
>> > GO
>> >
>> > SELECT
>> > OBJECTPROPERTY(OBJECT_ID('pk_t'), 'CnstIsDisabled '),
>> > OBJECTPROPERTY(OBJECT_ID('uq_c2'), 'CnstIsDisabled ')
>> > GO
>> >
>> > ALTER INDEX pk_t ON dbo.t DISABLE
>> > go
>> >
>> > ALTER INDEX uq_c2 ON dbo.t DISABLE
>> > go
>> >
>> > SELECT
>> > OBJECTPROPERTY(OBJECT_ID('pk_t'), 'CnstIsDisabled '),
>> > OBJECTPROPERTY(OBJECT_ID('uq_c2'), 'CnstIsDisabled ')
>> > GO
>> >
>> > INSERT INTO dbo.t VALUES(1, 1)
>> > GO
>> >
>> > INSERT INTO dbo.t VALUES(2, 1)
>> > GO
>> >
>> > INSERT INTO dbo.t VALUES(2, 1)
>> > GO
>> >
>> > SELECT * FROM dbo.[t]
>> > GO
>> >
>> > DROP TABLE dbo.[t]
>> > GO
>> >
>> >
>> > AMB
>> >
>> >
>> > "Jay" wrote:
>> >
>> >> I'm a little confused on the details of how a PK index is implemented
>> >> in
>> >> SQL
>> >> Server.
>> >>
>> >> It looks like when you say "ADD CONSTRAINT _name_ PRIMARY KEY (_col_)
>> >> ON
>> >> _filegroup_" you are specifying the index the PK will use in its
>> >> entirety.
>> >>
>> >> What I'm accustomed to (under Informix) is to create the table, create
>> >> the
>> >> index the constraint will use and then create the constraint, which
>> >> will
>> >> detect the appropriate index and use it (Informix has much more robust
>> >> storage options, which are available in create index, but not add
>> >> constraint).
>> >>
>> >> However, under SQL Server when I do this, I seem to get two unique
>> >> indexes
>> >> on the same column(s). Also, SQL Server seems to limit indexes to a
>> >> single
>> >> filegroup negating anything fancy in the first place (this is still
>> >> true
>> >> with DPV's).
>> >>
>> >> So, I'm guessing that the ADD CONSTRAINT clause in T-SQL will create
>> >> its
>> >> own
>> >> index, period. Also, except for the entry in sysconstraints, a
>> >> constraint
>> >> and an index are implemented completely identically in SQL Server.
>> >>
>> >> Am I correct?
>> >>
>> >> Thanks,
>> >> Jay
>> >>
>> >>
>> >>
>>