I have a SQL Server 2000 database with cluster and non-cluster indexes.
What advantages do you gain from creating a primary key as a cluster index.
If you were a cluster index without primary key what overhead goes along
with this process?
Thanks,the clustered index creates an object where the physical order of rows
is the same as the indexed order of the rows.
a primary key creates a unique index on that column. generally
speaking a clustered unique index provides much faster query times than
a nonclustered index.
overhead produced by any index will be filesize creating the new page
set for that particular index.|||Joe K. wrote:
> I have a SQL Server 2000 database with cluster and non-cluster
> indexes.
> What advantages do you gain from creating a primary key as a cluster
> index. If you were a cluster index without primary key what overhead
> goes along with this process?
> Thanks,
Keys are logical concepts. Indexes are physical constructs. That fact that
primary keys and unique constraints use indexes for enforcement should not
confuse the issue. Design you keys as the business requires in your logical
data model.
Add indexes where the addition of the index helps query performance. The
choice of whether a particular index should be clustered (only 1 per table)
or non-clustered should be clearly examined on a table by table basis.
Here's a good start:
http://www.sql-server-performance.com/clustered_indexes.asp
David Gugick
Quest Software|||that is a great article that david has given you.sqlsql
Showing posts with label primary. Show all posts
Showing posts with label primary. Show all posts
Thursday, March 22, 2012
Cluster Index Without Primary Key
I have a SQL Server 2000 database with cluster and non-cluster indexes.
What advantages do you gain from creating a primary key as a cluster index.
If you were a cluster index without primary key what overhead goes along
with this process?
Thanks,the clustered index creates an object where the physical order of rows
is the same as the indexed order of the rows.
a primary key creates a unique index on that column. generally
speaking a clustered unique index provides much faster query times than
a nonclustered index.
overhead produced by any index will be filesize creating the new page
set for that particular index.|||Joe K. wrote:
> I have a SQL Server 2000 database with cluster and non-cluster
> indexes.
> What advantages do you gain from creating a primary key as a cluster
> index. If you were a cluster index without primary key what overhead
> goes along with this process?
> Thanks,
Keys are logical concepts. Indexes are physical constructs. That fact that
primary keys and unique constraints use indexes for enforcement should not
confuse the issue. Design you keys as the business requires in your logical
data model.
Add indexes where the addition of the index helps query performance. The
choice of whether a particular index should be clustered (only 1 per table)
or non-clustered should be clearly examined on a table by table basis.
Here's a good start:
http://www.sql-server-performance.c...red_indexes.asp
David Gugick
Quest Software|||that is a great article that david has given you.
What advantages do you gain from creating a primary key as a cluster index.
If you were a cluster index without primary key what overhead goes along
with this process?
Thanks,the clustered index creates an object where the physical order of rows
is the same as the indexed order of the rows.
a primary key creates a unique index on that column. generally
speaking a clustered unique index provides much faster query times than
a nonclustered index.
overhead produced by any index will be filesize creating the new page
set for that particular index.|||Joe K. wrote:
> I have a SQL Server 2000 database with cluster and non-cluster
> indexes.
> What advantages do you gain from creating a primary key as a cluster
> index. If you were a cluster index without primary key what overhead
> goes along with this process?
> Thanks,
Keys are logical concepts. Indexes are physical constructs. That fact that
primary keys and unique constraints use indexes for enforcement should not
confuse the issue. Design you keys as the business requires in your logical
data model.
Add indexes where the addition of the index helps query performance. The
choice of whether a particular index should be clustered (only 1 per table)
or non-clustered should be clearly examined on a table by table basis.
Here's a good start:
http://www.sql-server-performance.c...red_indexes.asp
David Gugick
Quest Software|||that is a great article that david has given you.
Cluster Index Without Primary Key
I have a SQL Server 2000 database with cluster and non-cluster indexes.
What advantages do you gain from creating a primary key as a cluster index.
If you were a cluster index without primary key what overhead goes along
with this process?
Thanks,
the clustered index creates an object where the physical order of rows
is the same as the indexed order of the rows.
a primary key creates a unique index on that column. generally
speaking a clustered unique index provides much faster query times than
a nonclustered index.
overhead produced by any index will be filesize creating the new page
set for that particular index.
|||Joe K. wrote:
> I have a SQL Server 2000 database with cluster and non-cluster
> indexes.
> What advantages do you gain from creating a primary key as a cluster
> index. If you were a cluster index without primary key what overhead
> goes along with this process?
> Thanks,
Keys are logical concepts. Indexes are physical constructs. That fact that
primary keys and unique constraints use indexes for enforcement should not
confuse the issue. Design you keys as the business requires in your logical
data model.
Add indexes where the addition of the index helps query performance. The
choice of whether a particular index should be clustered (only 1 per table)
or non-clustered should be clearly examined on a table by table basis.
Here's a good start:
http://www.sql-server-performance.co...ed_indexes.asp
David Gugick
Quest Software
|||that is a great article that david has given you.
What advantages do you gain from creating a primary key as a cluster index.
If you were a cluster index without primary key what overhead goes along
with this process?
Thanks,
the clustered index creates an object where the physical order of rows
is the same as the indexed order of the rows.
a primary key creates a unique index on that column. generally
speaking a clustered unique index provides much faster query times than
a nonclustered index.
overhead produced by any index will be filesize creating the new page
set for that particular index.
|||Joe K. wrote:
> I have a SQL Server 2000 database with cluster and non-cluster
> indexes.
> What advantages do you gain from creating a primary key as a cluster
> index. If you were a cluster index without primary key what overhead
> goes along with this process?
> Thanks,
Keys are logical concepts. Indexes are physical constructs. That fact that
primary keys and unique constraints use indexes for enforcement should not
confuse the issue. Design you keys as the business requires in your logical
data model.
Add indexes where the addition of the index helps query performance. The
choice of whether a particular index should be clustered (only 1 per table)
or non-clustered should be clearly examined on a table by table basis.
Here's a good start:
http://www.sql-server-performance.co...ed_indexes.asp
David Gugick
Quest Software
|||that is a great article that david has given you.
Saturday, February 25, 2012
CLM
Can someone send me the link to the primary Sql Server (2000 preferably) MS forum? Or is this it?You can certainly post your SQL Server 2000 questions here. You might also take a look at this site http://www.microsoft.com/technet/community/newsgroups/server/sql.mspx for additional newsgroup resources.
Subscribe to:
Posts (Atom)