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 creating. Show all posts
Showing posts with label creating. 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.
cluster index and identity
According to Brian Knight in "SQL Server 2000 for Experienced DBAs",
"Avoid creating clustered indexes on identity columns.
Clustered indexes perform better on range queries,
such as a date. When you have a clustered index on
an identity column, you risk your data receiving hot
spots, which are caused by many people updating the
same data page."
I would like to know what is the consenus in this forum on this.
--It really depends on your usage; there is no single silver bullet answer.
"rkusenet" <rkusenet@.sympatico.ca> wrote in message
news:2unifgF2c9ng0U1@.uni-berlin.de...
> According to Brian Knight in "SQL Server 2000 for Experienced DBAs",
> "Avoid creating clustered indexes on identity columns.
> Clustered indexes perform better on range queries,
> such as a date. When you have a clustered index on
> an identity column, you risk your data receiving hot
> spots, which are caused by many people updating the
> same data page."
> I would like to know what is the consenus in this forum on this.
> --
>
>|||I prefer the exact opposite. I think hot spots (up to a point) are a good
thing. Hot spotting data gives the cache manager something to grab hold of.
Clustering on an Identity column also guarantees inserts are at the end of
the table, thus avoiding the dreaded page split. Hot spotting was very bad
under older versions of SQL, but SQL 2000 copes with it pretty well.
Clustering on a narrow unique key also has benefits, especially if you have
a lot of non-clustered indexes on a table.
Having said that, I do not use the identity column as my Primary Key. I
create a PK constraint using a non-clustered index on a natural key.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"rkusenet" <rkusenet@.sympatico.ca> wrote in message
news:2unifgF2c9ng0U1@.uni-berlin.de...
> According to Brian Knight in "SQL Server 2000 for Experienced DBAs",
> "Avoid creating clustered indexes on identity columns.
> Clustered indexes perform better on range queries,
> such as a date. When you have a clustered index on
> an identity column, you risk your data receiving hot
> spots, which are caused by many people updating the
> same data page."
> I would like to know what is the consenus in this forum on this.
> --
>
>|||And I fall exactly opposite of Geof... Here are my reasons... When the
table performance is targeted for quick queries, as opposed to fast inserts
( which is most of the time in my experiecnce)... Then don't clustere on the
identity column, cluster on a Where clause key becuase..
clustered indexes are useful when
you are returning many rows in your queries
there are many duplicate clustered keys ( since they are stored together
they will be returned together)
you are doing many range searches
you are doing tons or order by on the clustered key...
Are any of these true when you use an identity column? How many times will
you do select * from emp where empid > 50 ,,, or empid between 100 and 500
(rarely if ever)... You might be ordering by empid... But for the most part,
if you cluster on the identity column you will NOT be able to benefit from
the nice things that clustering can bring to a select.. So most of the time
I cluster on a WHERE clause column which gives the biggest bang ( deciding
which one is sometimes tough tho.)
Here is the exception:
IF the primary performance item for this table is INSERT SPEED, then the
best way to get that to be fast is to cluster on the identity column. This
cuts page allocation in half, because of a special algorythm used in the
Engine... THe inserts will be fast, but the cost will be select speed...
Reasonable people differ here... You should choose you method ( in my
opinion) based on the performance priorities for the table..
hope this helps..
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:e5NK88EwEHA.2804@.TK2MSFTNGP14.phx.gbl...
> I prefer the exact opposite. I think hot spots (up to a point) are a good
> thing. Hot spotting data gives the cache manager something to grab hold
of.
> Clustering on an Identity column also guarantees inserts are at the end of
> the table, thus avoiding the dreaded page split. Hot spotting was very
bad
> under older versions of SQL, but SQL 2000 copes with it pretty well.
> Clustering on a narrow unique key also has benefits, especially if you
have
> a lot of non-clustered indexes on a table.
> Having said that, I do not use the identity column as my Primary Key. I
> create a PK constraint using a non-clustered index on a natural key.
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "rkusenet" <rkusenet@.sympatico.ca> wrote in message
> news:2unifgF2c9ng0U1@.uni-berlin.de...
> > According to Brian Knight in "SQL Server 2000 for Experienced DBAs",
> >
> > "Avoid creating clustered indexes on identity columns.
> > Clustered indexes perform better on range queries,
> > such as a date. When you have a clustered index on
> > an identity column, you risk your data receiving hot
> > spots, which are caused by many people updating the
> > same data page."
> >
> > I would like to know what is the consenus in this forum on this.
> >
> > --
> >
> >
> >
>|||Good point. Range queries on the clustered index are going to be faster if
you cluster on a natural key. I have one severely de-normalized
transactional table that I had to cluster on the most used foreign key to
get any decent performance. On a properly normalized schema, I find that
more complex queries run faster clustering on an Identity column. It allows
very fast index comparisons so you can leverage multiple indexes to create a
'virtual' covered index. It also increases index density so you get more
effective use of cache memory.
As always, your application will determine your specific needs. Test, test,
and test again so you know what your application is doing to the system. Be
ready to make changes and measure again so you can make real comparisons and
recommendations.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:%23FdcEtNwEHA.3908@.TK2MSFTNGP12.phx.gbl...
> And I fall exactly opposite of Geof... Here are my reasons... When the
> table performance is targeted for quick queries, as opposed to fast
inserts
> ( which is most of the time in my experiecnce)... Then don't clustere on
the
> identity column, cluster on a Where clause key becuase..
> clustered indexes are useful when
> you are returning many rows in your queries
> there are many duplicate clustered keys ( since they are stored together
> they will be returned together)
> you are doing many range searches
> you are doing tons or order by on the clustered key...
> Are any of these true when you use an identity column? How many times will
> you do select * from emp where empid > 50 ,,, or empid between 100 and 500
> (rarely if ever)... You might be ordering by empid... But for the most
part,
> if you cluster on the identity column you will NOT be able to benefit from
> the nice things that clustering can bring to a select.. So most of the
time
> I cluster on a WHERE clause column which gives the biggest bang (
deciding
> which one is sometimes tough tho.)
> Here is the exception:
> IF the primary performance item for this table is INSERT SPEED, then the
> best way to get that to be fast is to cluster on the identity column. This
> cuts page allocation in half, because of a special algorythm used in the
> Engine... THe inserts will be fast, but the cost will be select speed...
> Reasonable people differ here... You should choose you method ( in my
> opinion) based on the performance priorities for the table..
> hope this helps..
>
>
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
> news:e5NK88EwEHA.2804@.TK2MSFTNGP14.phx.gbl...
> > I prefer the exact opposite. I think hot spots (up to a point) are a
good
> > thing. Hot spotting data gives the cache manager something to grab hold
> of.
> > Clustering on an Identity column also guarantees inserts are at the end
of
> > the table, thus avoiding the dreaded page split. Hot spotting was very
> bad
> > under older versions of SQL, but SQL 2000 copes with it pretty well.
> > Clustering on a narrow unique key also has benefits, especially if you
> have
> > a lot of non-clustered indexes on a table.
> >
> > Having said that, I do not use the identity column as my Primary Key. I
> > create a PK constraint using a non-clustered index on a natural key.
> >
> > --
> > Geoff N. Hiten
> > Microsoft SQL Server MVP
> > Senior Database Administrator
> > Careerbuilder.com
> >
> > I support the Professional Association for SQL Server
> > www.sqlpass.org
> >
> > "rkusenet" <rkusenet@.sympatico.ca> wrote in message
> > news:2unifgF2c9ng0U1@.uni-berlin.de...
> > > According to Brian Knight in "SQL Server 2000 for Experienced DBAs",
> > >
> > > "Avoid creating clustered indexes on identity columns.
> > > Clustered indexes perform better on range queries,
> > > such as a date. When you have a clustered index on
> > > an identity column, you risk your data receiving hot
> > > spots, which are caused by many people updating the
> > > same data page."
> > >
> > > I would like to know what is the consenus in this forum on this.
> > >
> > > --
> > >
> > >
> > >
> >
> >
>|||Geoff N. Hiten wrote:
> Good point. Range queries on the clustered index are going to be
> faster if you cluster on a natural key. I have one severely
> de-normalized transactional table that I had to cluster on the most
> used foreign key to get any decent performance. On a properly
> normalized schema, I find that more complex queries run faster
> clustering on an Identity column. It allows very fast index
> comparisons so you can leverage multiple indexes to create a
> 'virtual' covered index. It also increases index density so you get
> more effective use of cache memory.
> As always, your application will determine your specific needs.
> Test, test, and test again so you know what your application is doing
> to the system. Be ready to make changes and measure again so you can
> make real comparisons and recommendations.
>
Just to add my 2 cents. Clustering on any column that changes causes
sever fragmentation of the table. So if the OP chooses to cluster on a
column(s) used to filter queries, make sure that those columns are not
likely to change over time.
The OP should also realize that the clustered key is incorporated into
all non-clustered indexes, so it should be as short as possible.
David Gugick
Imceda Software
www.imceda.com|||In message <2unifgF2c9ng0U1@.uni-berlin.de>, rkusenet
<rkusenet@.sympatico.ca> writes
>According to Brian Knight in "SQL Server 2000 for Experienced DBAs",
>"Avoid creating clustered indexes on identity columns.
> Clustered indexes perform better on range queries,
> such as a date. When you have a clustered index on
> an identity column, you risk your data receiving hot
> spots, which are caused by many people updating the
> same data page."
>I would like to know what is the consenus in this forum on this.
>
It really depends on your requirements and usage.
In an OLAP environment I would agree with his comment however in an OLTP
environment this would cause performance issues on large tables. What is
worse is you increase the risk of index fragmentation and therefore
require more frequent rebuilds.
If the table is used for linking purposes in an OLTP environment with
few inserts then again his statement would hold true.
It all depends ...
Kind Regards,
--
Andrew D. Newbould E-Mail: newsgroups@.NOSPAMzadsoft.com
ZAD Software Systems Web : www.zadsoft.com
"Avoid creating clustered indexes on identity columns.
Clustered indexes perform better on range queries,
such as a date. When you have a clustered index on
an identity column, you risk your data receiving hot
spots, which are caused by many people updating the
same data page."
I would like to know what is the consenus in this forum on this.
--It really depends on your usage; there is no single silver bullet answer.
"rkusenet" <rkusenet@.sympatico.ca> wrote in message
news:2unifgF2c9ng0U1@.uni-berlin.de...
> According to Brian Knight in "SQL Server 2000 for Experienced DBAs",
> "Avoid creating clustered indexes on identity columns.
> Clustered indexes perform better on range queries,
> such as a date. When you have a clustered index on
> an identity column, you risk your data receiving hot
> spots, which are caused by many people updating the
> same data page."
> I would like to know what is the consenus in this forum on this.
> --
>
>|||I prefer the exact opposite. I think hot spots (up to a point) are a good
thing. Hot spotting data gives the cache manager something to grab hold of.
Clustering on an Identity column also guarantees inserts are at the end of
the table, thus avoiding the dreaded page split. Hot spotting was very bad
under older versions of SQL, but SQL 2000 copes with it pretty well.
Clustering on a narrow unique key also has benefits, especially if you have
a lot of non-clustered indexes on a table.
Having said that, I do not use the identity column as my Primary Key. I
create a PK constraint using a non-clustered index on a natural key.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"rkusenet" <rkusenet@.sympatico.ca> wrote in message
news:2unifgF2c9ng0U1@.uni-berlin.de...
> According to Brian Knight in "SQL Server 2000 for Experienced DBAs",
> "Avoid creating clustered indexes on identity columns.
> Clustered indexes perform better on range queries,
> such as a date. When you have a clustered index on
> an identity column, you risk your data receiving hot
> spots, which are caused by many people updating the
> same data page."
> I would like to know what is the consenus in this forum on this.
> --
>
>|||And I fall exactly opposite of Geof... Here are my reasons... When the
table performance is targeted for quick queries, as opposed to fast inserts
( which is most of the time in my experiecnce)... Then don't clustere on the
identity column, cluster on a Where clause key becuase..
clustered indexes are useful when
you are returning many rows in your queries
there are many duplicate clustered keys ( since they are stored together
they will be returned together)
you are doing many range searches
you are doing tons or order by on the clustered key...
Are any of these true when you use an identity column? How many times will
you do select * from emp where empid > 50 ,,, or empid between 100 and 500
(rarely if ever)... You might be ordering by empid... But for the most part,
if you cluster on the identity column you will NOT be able to benefit from
the nice things that clustering can bring to a select.. So most of the time
I cluster on a WHERE clause column which gives the biggest bang ( deciding
which one is sometimes tough tho.)
Here is the exception:
IF the primary performance item for this table is INSERT SPEED, then the
best way to get that to be fast is to cluster on the identity column. This
cuts page allocation in half, because of a special algorythm used in the
Engine... THe inserts will be fast, but the cost will be select speed...
Reasonable people differ here... You should choose you method ( in my
opinion) based on the performance priorities for the table..
hope this helps..
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:e5NK88EwEHA.2804@.TK2MSFTNGP14.phx.gbl...
> I prefer the exact opposite. I think hot spots (up to a point) are a good
> thing. Hot spotting data gives the cache manager something to grab hold
of.
> Clustering on an Identity column also guarantees inserts are at the end of
> the table, thus avoiding the dreaded page split. Hot spotting was very
bad
> under older versions of SQL, but SQL 2000 copes with it pretty well.
> Clustering on a narrow unique key also has benefits, especially if you
have
> a lot of non-clustered indexes on a table.
> Having said that, I do not use the identity column as my Primary Key. I
> create a PK constraint using a non-clustered index on a natural key.
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "rkusenet" <rkusenet@.sympatico.ca> wrote in message
> news:2unifgF2c9ng0U1@.uni-berlin.de...
> > According to Brian Knight in "SQL Server 2000 for Experienced DBAs",
> >
> > "Avoid creating clustered indexes on identity columns.
> > Clustered indexes perform better on range queries,
> > such as a date. When you have a clustered index on
> > an identity column, you risk your data receiving hot
> > spots, which are caused by many people updating the
> > same data page."
> >
> > I would like to know what is the consenus in this forum on this.
> >
> > --
> >
> >
> >
>|||Good point. Range queries on the clustered index are going to be faster if
you cluster on a natural key. I have one severely de-normalized
transactional table that I had to cluster on the most used foreign key to
get any decent performance. On a properly normalized schema, I find that
more complex queries run faster clustering on an Identity column. It allows
very fast index comparisons so you can leverage multiple indexes to create a
'virtual' covered index. It also increases index density so you get more
effective use of cache memory.
As always, your application will determine your specific needs. Test, test,
and test again so you know what your application is doing to the system. Be
ready to make changes and measure again so you can make real comparisons and
recommendations.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:%23FdcEtNwEHA.3908@.TK2MSFTNGP12.phx.gbl...
> And I fall exactly opposite of Geof... Here are my reasons... When the
> table performance is targeted for quick queries, as opposed to fast
inserts
> ( which is most of the time in my experiecnce)... Then don't clustere on
the
> identity column, cluster on a Where clause key becuase..
> clustered indexes are useful when
> you are returning many rows in your queries
> there are many duplicate clustered keys ( since they are stored together
> they will be returned together)
> you are doing many range searches
> you are doing tons or order by on the clustered key...
> Are any of these true when you use an identity column? How many times will
> you do select * from emp where empid > 50 ,,, or empid between 100 and 500
> (rarely if ever)... You might be ordering by empid... But for the most
part,
> if you cluster on the identity column you will NOT be able to benefit from
> the nice things that clustering can bring to a select.. So most of the
time
> I cluster on a WHERE clause column which gives the biggest bang (
deciding
> which one is sometimes tough tho.)
> Here is the exception:
> IF the primary performance item for this table is INSERT SPEED, then the
> best way to get that to be fast is to cluster on the identity column. This
> cuts page allocation in half, because of a special algorythm used in the
> Engine... THe inserts will be fast, but the cost will be select speed...
> Reasonable people differ here... You should choose you method ( in my
> opinion) based on the performance priorities for the table..
> hope this helps..
>
>
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
> news:e5NK88EwEHA.2804@.TK2MSFTNGP14.phx.gbl...
> > I prefer the exact opposite. I think hot spots (up to a point) are a
good
> > thing. Hot spotting data gives the cache manager something to grab hold
> of.
> > Clustering on an Identity column also guarantees inserts are at the end
of
> > the table, thus avoiding the dreaded page split. Hot spotting was very
> bad
> > under older versions of SQL, but SQL 2000 copes with it pretty well.
> > Clustering on a narrow unique key also has benefits, especially if you
> have
> > a lot of non-clustered indexes on a table.
> >
> > Having said that, I do not use the identity column as my Primary Key. I
> > create a PK constraint using a non-clustered index on a natural key.
> >
> > --
> > Geoff N. Hiten
> > Microsoft SQL Server MVP
> > Senior Database Administrator
> > Careerbuilder.com
> >
> > I support the Professional Association for SQL Server
> > www.sqlpass.org
> >
> > "rkusenet" <rkusenet@.sympatico.ca> wrote in message
> > news:2unifgF2c9ng0U1@.uni-berlin.de...
> > > According to Brian Knight in "SQL Server 2000 for Experienced DBAs",
> > >
> > > "Avoid creating clustered indexes on identity columns.
> > > Clustered indexes perform better on range queries,
> > > such as a date. When you have a clustered index on
> > > an identity column, you risk your data receiving hot
> > > spots, which are caused by many people updating the
> > > same data page."
> > >
> > > I would like to know what is the consenus in this forum on this.
> > >
> > > --
> > >
> > >
> > >
> >
> >
>|||Geoff N. Hiten wrote:
> Good point. Range queries on the clustered index are going to be
> faster if you cluster on a natural key. I have one severely
> de-normalized transactional table that I had to cluster on the most
> used foreign key to get any decent performance. On a properly
> normalized schema, I find that more complex queries run faster
> clustering on an Identity column. It allows very fast index
> comparisons so you can leverage multiple indexes to create a
> 'virtual' covered index. It also increases index density so you get
> more effective use of cache memory.
> As always, your application will determine your specific needs.
> Test, test, and test again so you know what your application is doing
> to the system. Be ready to make changes and measure again so you can
> make real comparisons and recommendations.
>
Just to add my 2 cents. Clustering on any column that changes causes
sever fragmentation of the table. So if the OP chooses to cluster on a
column(s) used to filter queries, make sure that those columns are not
likely to change over time.
The OP should also realize that the clustered key is incorporated into
all non-clustered indexes, so it should be as short as possible.
David Gugick
Imceda Software
www.imceda.com|||In message <2unifgF2c9ng0U1@.uni-berlin.de>, rkusenet
<rkusenet@.sympatico.ca> writes
>According to Brian Knight in "SQL Server 2000 for Experienced DBAs",
>"Avoid creating clustered indexes on identity columns.
> Clustered indexes perform better on range queries,
> such as a date. When you have a clustered index on
> an identity column, you risk your data receiving hot
> spots, which are caused by many people updating the
> same data page."
>I would like to know what is the consenus in this forum on this.
>
It really depends on your requirements and usage.
In an OLAP environment I would agree with his comment however in an OLTP
environment this would cause performance issues on large tables. What is
worse is you increase the risk of index fragmentation and therefore
require more frequent rebuilds.
If the table is used for linking purposes in an OLTP environment with
few inserts then again his statement would hold true.
It all depends ...
Kind Regards,
--
Andrew D. Newbould E-Mail: newsgroups@.NOSPAMzadsoft.com
ZAD Software Systems Web : www.zadsoft.com
cluster index and identity
According to Brian Knight in "SQL Server 2000 for Experienced DBAs",
"Avoid creating clustered indexes on identity columns.
Clustered indexes perform better on range queries,
such as a date. When you have a clustered index on
an identity column, you risk your data receiving hot
spots, which are caused by many people updating the
same data page."
I would like to know what is the consenus in this forum on this.It really depends on your usage; there is no single silver bullet answer.
"rkusenet" <rkusenet@.sympatico.ca> wrote in message
news:2unifgF2c9ng0U1@.uni-berlin.de...
> According to Brian Knight in "SQL Server 2000 for Experienced DBAs",
> "Avoid creating clustered indexes on identity columns.
> Clustered indexes perform better on range queries,
> such as a date. When you have a clustered index on
> an identity column, you risk your data receiving hot
> spots, which are caused by many people updating the
> same data page."
> I would like to know what is the consenus in this forum on this.
> --
>
>|||I prefer the exact opposite. I think hot spots (up to a point) are a good
thing. Hot spotting data gives the cache manager something to grab hold of.
Clustering on an Identity column also guarantees inserts are at the end of
the table, thus avoiding the dreaded page split. Hot spotting was very bad
under older versions of SQL, but SQL 2000 copes with it pretty well.
Clustering on a narrow unique key also has benefits, especially if you have
a lot of non-clustered indexes on a table.
Having said that, I do not use the identity column as my Primary Key. I
create a PK constraint using a non-clustered index on a natural key.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"rkusenet" <rkusenet@.sympatico.ca> wrote in message
news:2unifgF2c9ng0U1@.uni-berlin.de...
> According to Brian Knight in "SQL Server 2000 for Experienced DBAs",
> "Avoid creating clustered indexes on identity columns.
> Clustered indexes perform better on range queries,
> such as a date. When you have a clustered index on
> an identity column, you risk your data receiving hot
> spots, which are caused by many people updating the
> same data page."
> I would like to know what is the consenus in this forum on this.
> --
>
>|||And I fall exactly opposite of Geof... Here are my reasons... When the
table performance is targeted for quick queries, as opposed to fast inserts
( which is most of the time in my experiecnce)... Then don't clustere on the
identity column, cluster on a Where clause key becuase..
clustered indexes are useful when
you are returning many rows in your queries
there are many duplicate clustered keys ( since they are stored together
they will be returned together)
you are doing many range searches
you are doing tons or order by on the clustered key...
Are any of these true when you use an identity column? How many times will
you do select * from emp where empid > 50 ,,, or empid between 100 and 500
(rarely if ever)... You might be ordering by empid... But for the most part,
if you cluster on the identity column you will NOT be able to benefit from
the nice things that clustering can bring to a select.. So most of the time
I cluster on a WHERE clause column which gives the biggest bang ( deciding
which one is sometimes tough tho.)
Here is the exception:
IF the primary performance item for this table is INSERT SPEED, then the
best way to get that to be fast is to cluster on the identity column. This
cuts page allocation in half, because of a special algorythm used in the
Engine... THe inserts will be fast, but the cost will be select speed...
Reasonable people differ here... You should choose you method ( in my
opinion) based on the performance priorities for the table..
hope this helps..
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:e5NK88EwEHA.2804@.TK2MSFTNGP14.phx.gbl...
> I prefer the exact opposite. I think hot spots (up to a point) are a good
> thing. Hot spotting data gives the cache manager something to grab hold
of.
> Clustering on an Identity column also guarantees inserts are at the end of
> the table, thus avoiding the dreaded page split. Hot spotting was very
bad
> under older versions of SQL, but SQL 2000 copes with it pretty well.
> Clustering on a narrow unique key also has benefits, especially if you
have
> a lot of non-clustered indexes on a table.
> Having said that, I do not use the identity column as my Primary Key. I
> create a PK constraint using a non-clustered index on a natural key.
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "rkusenet" <rkusenet@.sympatico.ca> wrote in message
> news:2unifgF2c9ng0U1@.uni-berlin.de...
>|||Good point. Range queries on the clustered index are going to be faster if
you cluster on a natural key. I have one severely de-normalized
transactional table that I had to cluster on the most used foreign key to
get any decent performance. On a properly normalized schema, I find that
more complex queries run faster clustering on an Identity column. It allows
very fast index comparisons so you can leverage multiple indexes to create a
'virtual' covered index. It also increases index density so you get more
effective use of cache memory.
As always, your application will determine your specific needs. Test, test,
and test again so you know what your application is doing to the system. Be
ready to make changes and measure again so you can make real comparisons and
recommendations.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:%23FdcEtNwEHA.3908@.TK2MSFTNGP12.phx.gbl...
> And I fall exactly opposite of Geof... Here are my reasons... When the
> table performance is targeted for quick queries, as opposed to fast
inserts
> ( which is most of the time in my experiecnce)... Then don't clustere on
the
> identity column, cluster on a Where clause key becuase..
> clustered indexes are useful when
> you are returning many rows in your queries
> there are many duplicate clustered keys ( since they are stored together
> they will be returned together)
> you are doing many range searches
> you are doing tons or order by on the clustered key...
> Are any of these true when you use an identity column? How many times will
> you do select * from emp where empid > 50 ,,, or empid between 100 and 500
> (rarely if ever)... You might be ordering by empid... But for the most
part,
> if you cluster on the identity column you will NOT be able to benefit from
> the nice things that clustering can bring to a select.. So most of the
time
> I cluster on a WHERE clause column which gives the biggest bang (
deciding
> which one is sometimes tough tho.)
> Here is the exception:
> IF the primary performance item for this table is INSERT SPEED, then the
> best way to get that to be fast is to cluster on the identity column. This
> cuts page allocation in half, because of a special algorythm used in the
> Engine... THe inserts will be fast, but the cost will be select speed...
> Reasonable people differ here... You should choose you method ( in my
> opinion) based on the performance priorities for the table..
> hope this helps..
>
>
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
> news:e5NK88EwEHA.2804@.TK2MSFTNGP14.phx.gbl...
good[vbcol=seagreen]
> of.
of[vbcol=seagreen]
> bad
> have
>|||Geoff N. Hiten wrote:
> Good point. Range queries on the clustered index are going to be
> faster if you cluster on a natural key. I have one severely
> de-normalized transactional table that I had to cluster on the most
> used foreign key to get any decent performance. On a properly
> normalized schema, I find that more complex queries run faster
> clustering on an Identity column. It allows very fast index
> comparisons so you can leverage multiple indexes to create a
> 'virtual' covered index. It also increases index density so you get
> more effective use of cache memory.
> As always, your application will determine your specific needs.
> Test, test, and test again so you know what your application is doing
> to the system. Be ready to make changes and measure again so you can
> make real comparisons and recommendations.
>
Just to add my 2 cents. Clustering on any column that changes causes
sever fragmentation of the table. So if the OP chooses to cluster on a
column(s) used to filter queries, make sure that those columns are not
likely to change over time.
The OP should also realize that the clustered key is incorporated into
all non-clustered indexes, so it should be as short as possible.
David Gugick
Imceda Software
www.imceda.com|||In message <2unifgF2c9ng0U1@.uni-berlin.de>, rkusenet
<rkusenet@.sympatico.ca> writes
>According to Brian Knight in "SQL Server 2000 for Experienced DBAs",
>"Avoid creating clustered indexes on identity columns.
> Clustered indexes perform better on range queries,
> such as a date. When you have a clustered index on
> an identity column, you risk your data receiving hot
> spots, which are caused by many people updating the
> same data page."
>I would like to know what is the consenus in this forum on this.
>
It really depends on your requirements and usage.
In an OLAP environment I would agree with his comment however in an OLTP
environment this would cause performance issues on large tables. What is
worse is you increase the risk of index fragmentation and therefore
require more frequent rebuilds.
If the table is used for linking purposes in an OLTP environment with
few inserts then again his statement would hold true.
It all depends ...
Kind Regards,
--
Andrew D. Newbould E-Mail: newsgroups@.NOSPAMzadsoft.com
ZAD Software Systems Web : www.zadsoft.com
"Avoid creating clustered indexes on identity columns.
Clustered indexes perform better on range queries,
such as a date. When you have a clustered index on
an identity column, you risk your data receiving hot
spots, which are caused by many people updating the
same data page."
I would like to know what is the consenus in this forum on this.It really depends on your usage; there is no single silver bullet answer.
"rkusenet" <rkusenet@.sympatico.ca> wrote in message
news:2unifgF2c9ng0U1@.uni-berlin.de...
> According to Brian Knight in "SQL Server 2000 for Experienced DBAs",
> "Avoid creating clustered indexes on identity columns.
> Clustered indexes perform better on range queries,
> such as a date. When you have a clustered index on
> an identity column, you risk your data receiving hot
> spots, which are caused by many people updating the
> same data page."
> I would like to know what is the consenus in this forum on this.
> --
>
>|||I prefer the exact opposite. I think hot spots (up to a point) are a good
thing. Hot spotting data gives the cache manager something to grab hold of.
Clustering on an Identity column also guarantees inserts are at the end of
the table, thus avoiding the dreaded page split. Hot spotting was very bad
under older versions of SQL, but SQL 2000 copes with it pretty well.
Clustering on a narrow unique key also has benefits, especially if you have
a lot of non-clustered indexes on a table.
Having said that, I do not use the identity column as my Primary Key. I
create a PK constraint using a non-clustered index on a natural key.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"rkusenet" <rkusenet@.sympatico.ca> wrote in message
news:2unifgF2c9ng0U1@.uni-berlin.de...
> According to Brian Knight in "SQL Server 2000 for Experienced DBAs",
> "Avoid creating clustered indexes on identity columns.
> Clustered indexes perform better on range queries,
> such as a date. When you have a clustered index on
> an identity column, you risk your data receiving hot
> spots, which are caused by many people updating the
> same data page."
> I would like to know what is the consenus in this forum on this.
> --
>
>|||And I fall exactly opposite of Geof... Here are my reasons... When the
table performance is targeted for quick queries, as opposed to fast inserts
( which is most of the time in my experiecnce)... Then don't clustere on the
identity column, cluster on a Where clause key becuase..
clustered indexes are useful when
you are returning many rows in your queries
there are many duplicate clustered keys ( since they are stored together
they will be returned together)
you are doing many range searches
you are doing tons or order by on the clustered key...
Are any of these true when you use an identity column? How many times will
you do select * from emp where empid > 50 ,,, or empid between 100 and 500
(rarely if ever)... You might be ordering by empid... But for the most part,
if you cluster on the identity column you will NOT be able to benefit from
the nice things that clustering can bring to a select.. So most of the time
I cluster on a WHERE clause column which gives the biggest bang ( deciding
which one is sometimes tough tho.)
Here is the exception:
IF the primary performance item for this table is INSERT SPEED, then the
best way to get that to be fast is to cluster on the identity column. This
cuts page allocation in half, because of a special algorythm used in the
Engine... THe inserts will be fast, but the cost will be select speed...
Reasonable people differ here... You should choose you method ( in my
opinion) based on the performance priorities for the table..
hope this helps..
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:e5NK88EwEHA.2804@.TK2MSFTNGP14.phx.gbl...
> I prefer the exact opposite. I think hot spots (up to a point) are a good
> thing. Hot spotting data gives the cache manager something to grab hold
of.
> Clustering on an Identity column also guarantees inserts are at the end of
> the table, thus avoiding the dreaded page split. Hot spotting was very
bad
> under older versions of SQL, but SQL 2000 copes with it pretty well.
> Clustering on a narrow unique key also has benefits, especially if you
have
> a lot of non-clustered indexes on a table.
> Having said that, I do not use the identity column as my Primary Key. I
> create a PK constraint using a non-clustered index on a natural key.
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "rkusenet" <rkusenet@.sympatico.ca> wrote in message
> news:2unifgF2c9ng0U1@.uni-berlin.de...
>|||Good point. Range queries on the clustered index are going to be faster if
you cluster on a natural key. I have one severely de-normalized
transactional table that I had to cluster on the most used foreign key to
get any decent performance. On a properly normalized schema, I find that
more complex queries run faster clustering on an Identity column. It allows
very fast index comparisons so you can leverage multiple indexes to create a
'virtual' covered index. It also increases index density so you get more
effective use of cache memory.
As always, your application will determine your specific needs. Test, test,
and test again so you know what your application is doing to the system. Be
ready to make changes and measure again so you can make real comparisons and
recommendations.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:%23FdcEtNwEHA.3908@.TK2MSFTNGP12.phx.gbl...
> And I fall exactly opposite of Geof... Here are my reasons... When the
> table performance is targeted for quick queries, as opposed to fast
inserts
> ( which is most of the time in my experiecnce)... Then don't clustere on
the
> identity column, cluster on a Where clause key becuase..
> clustered indexes are useful when
> you are returning many rows in your queries
> there are many duplicate clustered keys ( since they are stored together
> they will be returned together)
> you are doing many range searches
> you are doing tons or order by on the clustered key...
> Are any of these true when you use an identity column? How many times will
> you do select * from emp where empid > 50 ,,, or empid between 100 and 500
> (rarely if ever)... You might be ordering by empid... But for the most
part,
> if you cluster on the identity column you will NOT be able to benefit from
> the nice things that clustering can bring to a select.. So most of the
time
> I cluster on a WHERE clause column which gives the biggest bang (
deciding
> which one is sometimes tough tho.)
> Here is the exception:
> IF the primary performance item for this table is INSERT SPEED, then the
> best way to get that to be fast is to cluster on the identity column. This
> cuts page allocation in half, because of a special algorythm used in the
> Engine... THe inserts will be fast, but the cost will be select speed...
> Reasonable people differ here... You should choose you method ( in my
> opinion) based on the performance priorities for the table..
> hope this helps..
>
>
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
> news:e5NK88EwEHA.2804@.TK2MSFTNGP14.phx.gbl...
good[vbcol=seagreen]
> of.
of[vbcol=seagreen]
> bad
> have
>|||Geoff N. Hiten wrote:
> Good point. Range queries on the clustered index are going to be
> faster if you cluster on a natural key. I have one severely
> de-normalized transactional table that I had to cluster on the most
> used foreign key to get any decent performance. On a properly
> normalized schema, I find that more complex queries run faster
> clustering on an Identity column. It allows very fast index
> comparisons so you can leverage multiple indexes to create a
> 'virtual' covered index. It also increases index density so you get
> more effective use of cache memory.
> As always, your application will determine your specific needs.
> Test, test, and test again so you know what your application is doing
> to the system. Be ready to make changes and measure again so you can
> make real comparisons and recommendations.
>
Just to add my 2 cents. Clustering on any column that changes causes
sever fragmentation of the table. So if the OP chooses to cluster on a
column(s) used to filter queries, make sure that those columns are not
likely to change over time.
The OP should also realize that the clustered key is incorporated into
all non-clustered indexes, so it should be as short as possible.
David Gugick
Imceda Software
www.imceda.com|||In message <2unifgF2c9ng0U1@.uni-berlin.de>, rkusenet
<rkusenet@.sympatico.ca> writes
>According to Brian Knight in "SQL Server 2000 for Experienced DBAs",
>"Avoid creating clustered indexes on identity columns.
> Clustered indexes perform better on range queries,
> such as a date. When you have a clustered index on
> an identity column, you risk your data receiving hot
> spots, which are caused by many people updating the
> same data page."
>I would like to know what is the consenus in this forum on this.
>
It really depends on your requirements and usage.
In an OLAP environment I would agree with his comment however in an OLTP
environment this would cause performance issues on large tables. What is
worse is you increase the risk of index fragmentation and therefore
require more frequent rebuilds.
If the table is used for linking purposes in an OLTP environment with
few inserts then again his statement would hold true.
It all depends ...
Kind Regards,
--
Andrew D. Newbould E-Mail: newsgroups@.NOSPAMzadsoft.com
ZAD Software Systems Web : www.zadsoft.com
cluster index and identity
According to Brian Knight in "SQL Server 2000 for Experienced DBAs",
"Avoid creating clustered indexes on identity columns.
Clustered indexes perform better on range queries,
such as a date. When you have a clustered index on
an identity column, you risk your data receiving hot
spots, which are caused by many people updating the
same data page."
I would like to know what is the consenus in this forum on this.
It really depends on your usage; there is no single silver bullet answer.
"rkusenet" <rkusenet@.sympatico.ca> wrote in message
news:2unifgF2c9ng0U1@.uni-berlin.de...
> According to Brian Knight in "SQL Server 2000 for Experienced DBAs",
> "Avoid creating clustered indexes on identity columns.
> Clustered indexes perform better on range queries,
> such as a date. When you have a clustered index on
> an identity column, you risk your data receiving hot
> spots, which are caused by many people updating the
> same data page."
> I would like to know what is the consenus in this forum on this.
> --
>
>
|||I prefer the exact opposite. I think hot spots (up to a point) are a good
thing. Hot spotting data gives the cache manager something to grab hold of.
Clustering on an Identity column also guarantees inserts are at the end of
the table, thus avoiding the dreaded page split. Hot spotting was very bad
under older versions of SQL, but SQL 2000 copes with it pretty well.
Clustering on a narrow unique key also has benefits, especially if you have
a lot of non-clustered indexes on a table.
Having said that, I do not use the identity column as my Primary Key. I
create a PK constraint using a non-clustered index on a natural key.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"rkusenet" <rkusenet@.sympatico.ca> wrote in message
news:2unifgF2c9ng0U1@.uni-berlin.de...
> According to Brian Knight in "SQL Server 2000 for Experienced DBAs",
> "Avoid creating clustered indexes on identity columns.
> Clustered indexes perform better on range queries,
> such as a date. When you have a clustered index on
> an identity column, you risk your data receiving hot
> spots, which are caused by many people updating the
> same data page."
> I would like to know what is the consenus in this forum on this.
> --
>
>
|||And I fall exactly opposite of Geof... Here are my reasons... When the
table performance is targeted for quick queries, as opposed to fast inserts
( which is most of the time in my experiecnce)... Then don't clustere on the
identity column, cluster on a Where clause key becuase..
clustered indexes are useful when
you are returning many rows in your queries
there are many duplicate clustered keys ( since they are stored together
they will be returned together)
you are doing many range searches
you are doing tons or order by on the clustered key...
Are any of these true when you use an identity column? How many times will
you do select * from emp where empid > 50 ,,, or empid between 100 and 500
(rarely if ever)... You might be ordering by empid... But for the most part,
if you cluster on the identity column you will NOT be able to benefit from
the nice things that clustering can bring to a select.. So most of the time
I cluster on a WHERE clause column which gives the biggest bang ( deciding
which one is sometimes tough tho.)
Here is the exception:
IF the primary performance item for this table is INSERT SPEED, then the
best way to get that to be fast is to cluster on the identity column. This
cuts page allocation in half, because of a special algorythm used in the
Engine... THe inserts will be fast, but the cost will be select speed...
Reasonable people differ here... You should choose you method ( in my
opinion) based on the performance priorities for the table..
hope this helps..
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:e5NK88EwEHA.2804@.TK2MSFTNGP14.phx.gbl...
> I prefer the exact opposite. I think hot spots (up to a point) are a good
> thing. Hot spotting data gives the cache manager something to grab hold
of.
> Clustering on an Identity column also guarantees inserts are at the end of
> the table, thus avoiding the dreaded page split. Hot spotting was very
bad
> under older versions of SQL, but SQL 2000 copes with it pretty well.
> Clustering on a narrow unique key also has benefits, especially if you
have
> a lot of non-clustered indexes on a table.
> Having said that, I do not use the identity column as my Primary Key. I
> create a PK constraint using a non-clustered index on a natural key.
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "rkusenet" <rkusenet@.sympatico.ca> wrote in message
> news:2unifgF2c9ng0U1@.uni-berlin.de...
>
|||Good point. Range queries on the clustered index are going to be faster if
you cluster on a natural key. I have one severely de-normalized
transactional table that I had to cluster on the most used foreign key to
get any decent performance. On a properly normalized schema, I find that
more complex queries run faster clustering on an Identity column. It allows
very fast index comparisons so you can leverage multiple indexes to create a
'virtual' covered index. It also increases index density so you get more
effective use of cache memory.
As always, your application will determine your specific needs. Test, test,
and test again so you know what your application is doing to the system. Be
ready to make changes and measure again so you can make real comparisons and
recommendations.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:%23FdcEtNwEHA.3908@.TK2MSFTNGP12.phx.gbl...
> And I fall exactly opposite of Geof... Here are my reasons... When the
> table performance is targeted for quick queries, as opposed to fast
inserts
> ( which is most of the time in my experiecnce)... Then don't clustere on
the
> identity column, cluster on a Where clause key becuase..
> clustered indexes are useful when
> you are returning many rows in your queries
> there are many duplicate clustered keys ( since they are stored together
> they will be returned together)
> you are doing many range searches
> you are doing tons or order by on the clustered key...
> Are any of these true when you use an identity column? How many times will
> you do select * from emp where empid > 50 ,,, or empid between 100 and 500
> (rarely if ever)... You might be ordering by empid... But for the most
part,
> if you cluster on the identity column you will NOT be able to benefit from
> the nice things that clustering can bring to a select.. So most of the
time
> I cluster on a WHERE clause column which gives the biggest bang (
deciding[vbcol=seagreen]
> which one is sometimes tough tho.)
> Here is the exception:
> IF the primary performance item for this table is INSERT SPEED, then the
> best way to get that to be fast is to cluster on the identity column. This
> cuts page allocation in half, because of a special algorythm used in the
> Engine... THe inserts will be fast, but the cost will be select speed...
> Reasonable people differ here... You should choose you method ( in my
> opinion) based on the performance priorities for the table..
> hope this helps..
>
>
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
> news:e5NK88EwEHA.2804@.TK2MSFTNGP14.phx.gbl...
good[vbcol=seagreen]
> of.
of
> bad
> have
>
|||Geoff N. Hiten wrote:
> Good point. Range queries on the clustered index are going to be
> faster if you cluster on a natural key. I have one severely
> de-normalized transactional table that I had to cluster on the most
> used foreign key to get any decent performance. On a properly
> normalized schema, I find that more complex queries run faster
> clustering on an Identity column. It allows very fast index
> comparisons so you can leverage multiple indexes to create a
> 'virtual' covered index. It also increases index density so you get
> more effective use of cache memory.
> As always, your application will determine your specific needs.
> Test, test, and test again so you know what your application is doing
> to the system. Be ready to make changes and measure again so you can
> make real comparisons and recommendations.
>
Just to add my 2 cents. Clustering on any column that changes causes
sever fragmentation of the table. So if the OP chooses to cluster on a
column(s) used to filter queries, make sure that those columns are not
likely to change over time.
The OP should also realize that the clustered key is incorporated into
all non-clustered indexes, so it should be as short as possible.
David Gugick
Imceda Software
www.imceda.com
|||In message <2unifgF2c9ng0U1@.uni-berlin.de>, rkusenet
<rkusenet@.sympatico.ca> writes
>According to Brian Knight in "SQL Server 2000 for Experienced DBAs",
>"Avoid creating clustered indexes on identity columns.
> Clustered indexes perform better on range queries,
> such as a date. When you have a clustered index on
> an identity column, you risk your data receiving hot
> spots, which are caused by many people updating the
> same data page."
>I would like to know what is the consenus in this forum on this.
>
It really depends on your requirements and usage.
In an OLAP environment I would agree with his comment however in an OLTP
environment this would cause performance issues on large tables. What is
worse is you increase the risk of index fragmentation and therefore
require more frequent rebuilds.
If the table is used for linking purposes in an OLTP environment with
few inserts then again his statement would hold true.
It all depends ...
Kind Regards,
Andrew D. Newbould E-Mail: newsgroups@.NOSPAMzadsoft.com
ZAD Software Systems Web : www.zadsoft.com
"Avoid creating clustered indexes on identity columns.
Clustered indexes perform better on range queries,
such as a date. When you have a clustered index on
an identity column, you risk your data receiving hot
spots, which are caused by many people updating the
same data page."
I would like to know what is the consenus in this forum on this.
It really depends on your usage; there is no single silver bullet answer.
"rkusenet" <rkusenet@.sympatico.ca> wrote in message
news:2unifgF2c9ng0U1@.uni-berlin.de...
> According to Brian Knight in "SQL Server 2000 for Experienced DBAs",
> "Avoid creating clustered indexes on identity columns.
> Clustered indexes perform better on range queries,
> such as a date. When you have a clustered index on
> an identity column, you risk your data receiving hot
> spots, which are caused by many people updating the
> same data page."
> I would like to know what is the consenus in this forum on this.
> --
>
>
|||I prefer the exact opposite. I think hot spots (up to a point) are a good
thing. Hot spotting data gives the cache manager something to grab hold of.
Clustering on an Identity column also guarantees inserts are at the end of
the table, thus avoiding the dreaded page split. Hot spotting was very bad
under older versions of SQL, but SQL 2000 copes with it pretty well.
Clustering on a narrow unique key also has benefits, especially if you have
a lot of non-clustered indexes on a table.
Having said that, I do not use the identity column as my Primary Key. I
create a PK constraint using a non-clustered index on a natural key.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"rkusenet" <rkusenet@.sympatico.ca> wrote in message
news:2unifgF2c9ng0U1@.uni-berlin.de...
> According to Brian Knight in "SQL Server 2000 for Experienced DBAs",
> "Avoid creating clustered indexes on identity columns.
> Clustered indexes perform better on range queries,
> such as a date. When you have a clustered index on
> an identity column, you risk your data receiving hot
> spots, which are caused by many people updating the
> same data page."
> I would like to know what is the consenus in this forum on this.
> --
>
>
|||And I fall exactly opposite of Geof... Here are my reasons... When the
table performance is targeted for quick queries, as opposed to fast inserts
( which is most of the time in my experiecnce)... Then don't clustere on the
identity column, cluster on a Where clause key becuase..
clustered indexes are useful when
you are returning many rows in your queries
there are many duplicate clustered keys ( since they are stored together
they will be returned together)
you are doing many range searches
you are doing tons or order by on the clustered key...
Are any of these true when you use an identity column? How many times will
you do select * from emp where empid > 50 ,,, or empid between 100 and 500
(rarely if ever)... You might be ordering by empid... But for the most part,
if you cluster on the identity column you will NOT be able to benefit from
the nice things that clustering can bring to a select.. So most of the time
I cluster on a WHERE clause column which gives the biggest bang ( deciding
which one is sometimes tough tho.)
Here is the exception:
IF the primary performance item for this table is INSERT SPEED, then the
best way to get that to be fast is to cluster on the identity column. This
cuts page allocation in half, because of a special algorythm used in the
Engine... THe inserts will be fast, but the cost will be select speed...
Reasonable people differ here... You should choose you method ( in my
opinion) based on the performance priorities for the table..
hope this helps..
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:e5NK88EwEHA.2804@.TK2MSFTNGP14.phx.gbl...
> I prefer the exact opposite. I think hot spots (up to a point) are a good
> thing. Hot spotting data gives the cache manager something to grab hold
of.
> Clustering on an Identity column also guarantees inserts are at the end of
> the table, thus avoiding the dreaded page split. Hot spotting was very
bad
> under older versions of SQL, but SQL 2000 copes with it pretty well.
> Clustering on a narrow unique key also has benefits, especially if you
have
> a lot of non-clustered indexes on a table.
> Having said that, I do not use the identity column as my Primary Key. I
> create a PK constraint using a non-clustered index on a natural key.
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "rkusenet" <rkusenet@.sympatico.ca> wrote in message
> news:2unifgF2c9ng0U1@.uni-berlin.de...
>
|||Good point. Range queries on the clustered index are going to be faster if
you cluster on a natural key. I have one severely de-normalized
transactional table that I had to cluster on the most used foreign key to
get any decent performance. On a properly normalized schema, I find that
more complex queries run faster clustering on an Identity column. It allows
very fast index comparisons so you can leverage multiple indexes to create a
'virtual' covered index. It also increases index density so you get more
effective use of cache memory.
As always, your application will determine your specific needs. Test, test,
and test again so you know what your application is doing to the system. Be
ready to make changes and measure again so you can make real comparisons and
recommendations.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:%23FdcEtNwEHA.3908@.TK2MSFTNGP12.phx.gbl...
> And I fall exactly opposite of Geof... Here are my reasons... When the
> table performance is targeted for quick queries, as opposed to fast
inserts
> ( which is most of the time in my experiecnce)... Then don't clustere on
the
> identity column, cluster on a Where clause key becuase..
> clustered indexes are useful when
> you are returning many rows in your queries
> there are many duplicate clustered keys ( since they are stored together
> they will be returned together)
> you are doing many range searches
> you are doing tons or order by on the clustered key...
> Are any of these true when you use an identity column? How many times will
> you do select * from emp where empid > 50 ,,, or empid between 100 and 500
> (rarely if ever)... You might be ordering by empid... But for the most
part,
> if you cluster on the identity column you will NOT be able to benefit from
> the nice things that clustering can bring to a select.. So most of the
time
> I cluster on a WHERE clause column which gives the biggest bang (
deciding[vbcol=seagreen]
> which one is sometimes tough tho.)
> Here is the exception:
> IF the primary performance item for this table is INSERT SPEED, then the
> best way to get that to be fast is to cluster on the identity column. This
> cuts page allocation in half, because of a special algorythm used in the
> Engine... THe inserts will be fast, but the cost will be select speed...
> Reasonable people differ here... You should choose you method ( in my
> opinion) based on the performance priorities for the table..
> hope this helps..
>
>
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
> news:e5NK88EwEHA.2804@.TK2MSFTNGP14.phx.gbl...
good[vbcol=seagreen]
> of.
of
> bad
> have
>
|||Geoff N. Hiten wrote:
> Good point. Range queries on the clustered index are going to be
> faster if you cluster on a natural key. I have one severely
> de-normalized transactional table that I had to cluster on the most
> used foreign key to get any decent performance. On a properly
> normalized schema, I find that more complex queries run faster
> clustering on an Identity column. It allows very fast index
> comparisons so you can leverage multiple indexes to create a
> 'virtual' covered index. It also increases index density so you get
> more effective use of cache memory.
> As always, your application will determine your specific needs.
> Test, test, and test again so you know what your application is doing
> to the system. Be ready to make changes and measure again so you can
> make real comparisons and recommendations.
>
Just to add my 2 cents. Clustering on any column that changes causes
sever fragmentation of the table. So if the OP chooses to cluster on a
column(s) used to filter queries, make sure that those columns are not
likely to change over time.
The OP should also realize that the clustered key is incorporated into
all non-clustered indexes, so it should be as short as possible.
David Gugick
Imceda Software
www.imceda.com
|||In message <2unifgF2c9ng0U1@.uni-berlin.de>, rkusenet
<rkusenet@.sympatico.ca> writes
>According to Brian Knight in "SQL Server 2000 for Experienced DBAs",
>"Avoid creating clustered indexes on identity columns.
> Clustered indexes perform better on range queries,
> such as a date. When you have a clustered index on
> an identity column, you risk your data receiving hot
> spots, which are caused by many people updating the
> same data page."
>I would like to know what is the consenus in this forum on this.
>
It really depends on your requirements and usage.
In an OLAP environment I would agree with his comment however in an OLTP
environment this would cause performance issues on large tables. What is
worse is you increase the risk of index fragmentation and therefore
require more frequent rebuilds.
If the table is used for linking purposes in an OLTP environment with
few inserts then again his statement would hold true.
It all depends ...
Kind Regards,
Andrew D. Newbould E-Mail: newsgroups@.NOSPAMzadsoft.com
ZAD Software Systems Web : www.zadsoft.com
Saturday, February 25, 2012
cloning a db under another name
I have a database called Paris. I want to clone it but I want the new name
to be Milan. I tried creating a new database named Milan and restoring
Paris over it but Management Studio would not allow this. I had overwrite
checked. How can I do this?
Regards,
Gary Blakely
"GaryDean" <GaryDean@.newsgroups.nospam> wrote in message
news:ewnkrPDlHHA.4772@.TK2MSFTNGP05.phx.gbl...
>I have a database called Paris. I want to clone it but I want the new name
>to be Milan. I tried creating a new database named Milan and restoring
>Paris over it but Management Studio would not allow this. I had overwrite
>checked. How can I do this?
Don't use the GUI.
Personally I wouldn't bother creating Milan but would simply backup Paris
and
RESTORE DATABASE MILAN from disk='c:\paris.bak' etc etc.
> --
> Regards,
> Gary Blakely
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
|||> RESTORE DATABASE MILAN from disk='c:\paris.bak' etc etc.
... and read up on the MOVE option of the RESTORE command.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:uJ3iLHElHHA.4552@.TK2MSFTNGP04.phx.gbl...
> "GaryDean" <GaryDean@.newsgroups.nospam> wrote in message
> news:ewnkrPDlHHA.4772@.TK2MSFTNGP05.phx.gbl...
> Don't use the GUI.
> Personally I wouldn't bother creating Milan but would simply backup Paris and
> RESTORE DATABASE MILAN from disk='c:\paris.bak' etc etc.
>
>
> --
> Greg Moore
> SQL Server DBA Consulting Remote and Onsite available!
> Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
>
|||Hello Gary,
I agree Tibor that you may want to use Move option since you may want to
use a different path for the database files. You could refer to the
following article for some related information
314546 HOW TO: Move Databases Between Computers That Are Running SQL Server
http://support.microsoft.com/?id=314546
Please let's know if you have any further questions on this issue. Thanks.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
|||On May 12, 9:24 am, "GaryDean" <GaryD...@.newsgroups.nospam> wrote:
> I have a database called Paris. I want to clone it but I want the new name
> to be Milan. I tried creating a new database named Milan and restoring
> Paris over it but Management Studio would not allow this. I had overwrite
> checked. How can I do this?
> --
> Regards,
> Gary Blakely
Hi. I suggest you copy DB, that's quite straight forward, you can
choose any name from it.
|||Well, I don't INSIST on using the gui but...
Thanks, that worked great. Now I see what the badly worded error message
was trying to tell me.
Regards,
Gary Blakely
"Daniel Crichton" <msnews@.worldofspack.com> wrote in message
news:OMc3IRjlHHA.4120@.TK2MSFTNGP06.phx.gbl...
> GaryDean wrote on Fri, 11 May 2007 18:24:07 -0700:
>
> If you insist on using the GUI to do this, you need to change the
> filenames in the Restore As column on the Options page of the restore
> task. MS is preventing you from restoring using the previous filenames as
> this would overwrite the files for the Paris database.
> Dan
>
to be Milan. I tried creating a new database named Milan and restoring
Paris over it but Management Studio would not allow this. I had overwrite
checked. How can I do this?
Regards,
Gary Blakely
"GaryDean" <GaryDean@.newsgroups.nospam> wrote in message
news:ewnkrPDlHHA.4772@.TK2MSFTNGP05.phx.gbl...
>I have a database called Paris. I want to clone it but I want the new name
>to be Milan. I tried creating a new database named Milan and restoring
>Paris over it but Management Studio would not allow this. I had overwrite
>checked. How can I do this?
Don't use the GUI.
Personally I wouldn't bother creating Milan but would simply backup Paris
and
RESTORE DATABASE MILAN from disk='c:\paris.bak' etc etc.
> --
> Regards,
> Gary Blakely
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
|||> RESTORE DATABASE MILAN from disk='c:\paris.bak' etc etc.
... and read up on the MOVE option of the RESTORE command.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:uJ3iLHElHHA.4552@.TK2MSFTNGP04.phx.gbl...
> "GaryDean" <GaryDean@.newsgroups.nospam> wrote in message
> news:ewnkrPDlHHA.4772@.TK2MSFTNGP05.phx.gbl...
> Don't use the GUI.
> Personally I wouldn't bother creating Milan but would simply backup Paris and
> RESTORE DATABASE MILAN from disk='c:\paris.bak' etc etc.
>
>
> --
> Greg Moore
> SQL Server DBA Consulting Remote and Onsite available!
> Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
>
|||Hello Gary,
I agree Tibor that you may want to use Move option since you may want to
use a different path for the database files. You could refer to the
following article for some related information
314546 HOW TO: Move Databases Between Computers That Are Running SQL Server
http://support.microsoft.com/?id=314546
Please let's know if you have any further questions on this issue. Thanks.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
|||On May 12, 9:24 am, "GaryDean" <GaryD...@.newsgroups.nospam> wrote:
> I have a database called Paris. I want to clone it but I want the new name
> to be Milan. I tried creating a new database named Milan and restoring
> Paris over it but Management Studio would not allow this. I had overwrite
> checked. How can I do this?
> --
> Regards,
> Gary Blakely
Hi. I suggest you copy DB, that's quite straight forward, you can
choose any name from it.
|||Well, I don't INSIST on using the gui but...
Thanks, that worked great. Now I see what the badly worded error message
was trying to tell me.
Regards,
Gary Blakely
"Daniel Crichton" <msnews@.worldofspack.com> wrote in message
news:OMc3IRjlHHA.4120@.TK2MSFTNGP06.phx.gbl...
> GaryDean wrote on Fri, 11 May 2007 18:24:07 -0700:
>
> If you insist on using the GUI to do this, you need to change the
> filenames in the Restore As column on the Options page of the restore
> task. MS is preventing you from restoring using the previous filenames as
> this would overwrite the files for the Paris database.
> Dan
>
cloning a db under another name
I have a database called Paris. I want to clone it but I want the new name
to be Milan. I tried creating a new database named Milan and restoring
Paris over it but Management Studio would not allow this. I had overwrite
checked. How can I do this?
--
Regards,
Gary Blakely"GaryDean" <GaryDean@.newsgroups.nospam> wrote in message
news:ewnkrPDlHHA.4772@.TK2MSFTNGP05.phx.gbl...
>I have a database called Paris. I want to clone it but I want the new name
>to be Milan. I tried creating a new database named Milan and restoring
>Paris over it but Management Studio would not allow this. I had overwrite
>checked. How can I do this?
Don't use the GUI.
Personally I wouldn't bother creating Milan but would simply backup Paris
and
RESTORE DATABASE MILAN from disk='c:\paris.bak' etc etc.
> --
> Regards,
> Gary Blakely
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||> RESTORE DATABASE MILAN from disk='c:\paris.bak' etc etc.
... and read up on the MOVE option of the RESTORE command.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:uJ3iLHElHHA.4552@.TK2MSFTNGP04.phx.gbl...
> "GaryDean" <GaryDean@.newsgroups.nospam> wrote in message
> news:ewnkrPDlHHA.4772@.TK2MSFTNGP05.phx.gbl...
>>I have a database called Paris. I want to clone it but I want the new name to be Milan. I tried
>>creating a new database named Milan and restoring Paris over it but Management Studio would not
>>allow this. I had overwrite checked. How can I do this?
> Don't use the GUI.
> Personally I wouldn't bother creating Milan but would simply backup Paris and
> RESTORE DATABASE MILAN from disk='c:\paris.bak' etc etc.
>
>> --
>> Regards,
>> Gary Blakely
>>
>
> --
> Greg Moore
> SQL Server DBA Consulting Remote and Onsite available!
> Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
>|||Hello Gary,
I agree Tibor that you may want to use Move option since you may want to
use a different path for the database files. You could refer to the
following article for some related information
314546 HOW TO: Move Databases Between Computers That Are Running SQL Server
http://support.microsoft.com/?id=314546
Please let's know if you have any further questions on this issue. Thanks.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.|||On May 12, 9:24 am, "GaryDean" <GaryD...@.newsgroups.nospam> wrote:
> I have a database called Paris. I want to clone it but I want the new name
> to be Milan. I tried creating a new database named Milan and restoring
> Paris over it but Management Studio would not allow this. I had overwrite
> checked. How can I do this?
> --
> Regards,
> Gary Blakely
Hi. I suggest you copy DB, that's quite straight forward, you can
choose any name from it.|||GaryDean wrote on Fri, 11 May 2007 18:24:07 -0700:
> I have a database called Paris. I want to clone it but I want the new
> name to be Milan. I tried creating a new database named Milan and
> restoring Paris over it but Management Studio would not allow this. I had
> overwrite checked. How can I do this?
>
If you insist on using the GUI to do this, you need to change the filenames
in the Restore As column on the Options page of the restore task. MS is
preventing you from restoring using the previous filenames as this would
overwrite the files for the Paris database.
Dan|||Well, I don't INSIST on using the gui but...
Thanks, that worked great. Now I see what the badly worded error message
was trying to tell me.
--
Regards,
Gary Blakely
"Daniel Crichton" <msnews@.worldofspack.com> wrote in message
news:OMc3IRjlHHA.4120@.TK2MSFTNGP06.phx.gbl...
> GaryDean wrote on Fri, 11 May 2007 18:24:07 -0700:
>> I have a database called Paris. I want to clone it but I want the new
>> name to be Milan. I tried creating a new database named Milan and
>> restoring Paris over it but Management Studio would not allow this. I
>> had
>> overwrite checked. How can I do this?
>
> If you insist on using the GUI to do this, you need to change the
> filenames in the Restore As column on the Options page of the restore
> task. MS is preventing you from restoring using the previous filenames as
> this would overwrite the files for the Paris database.
> Dan
>|||GaryDean wrote on Mon, 14 May 2007 16:11:25 -0700:
> Well, I don't INSIST on using the gui but...
I guess maybe I should have worded it differently. How about "use the GUI as
an alternative"? :)
> Thanks, that worked great. Now I see what the badly worded error message
> was trying to tell me.
Yeah, it's not that great a message really.
Dan
to be Milan. I tried creating a new database named Milan and restoring
Paris over it but Management Studio would not allow this. I had overwrite
checked. How can I do this?
--
Regards,
Gary Blakely"GaryDean" <GaryDean@.newsgroups.nospam> wrote in message
news:ewnkrPDlHHA.4772@.TK2MSFTNGP05.phx.gbl...
>I have a database called Paris. I want to clone it but I want the new name
>to be Milan. I tried creating a new database named Milan and restoring
>Paris over it but Management Studio would not allow this. I had overwrite
>checked. How can I do this?
Don't use the GUI.
Personally I wouldn't bother creating Milan but would simply backup Paris
and
RESTORE DATABASE MILAN from disk='c:\paris.bak' etc etc.
> --
> Regards,
> Gary Blakely
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||> RESTORE DATABASE MILAN from disk='c:\paris.bak' etc etc.
... and read up on the MOVE option of the RESTORE command.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:uJ3iLHElHHA.4552@.TK2MSFTNGP04.phx.gbl...
> "GaryDean" <GaryDean@.newsgroups.nospam> wrote in message
> news:ewnkrPDlHHA.4772@.TK2MSFTNGP05.phx.gbl...
>>I have a database called Paris. I want to clone it but I want the new name to be Milan. I tried
>>creating a new database named Milan and restoring Paris over it but Management Studio would not
>>allow this. I had overwrite checked. How can I do this?
> Don't use the GUI.
> Personally I wouldn't bother creating Milan but would simply backup Paris and
> RESTORE DATABASE MILAN from disk='c:\paris.bak' etc etc.
>
>> --
>> Regards,
>> Gary Blakely
>>
>
> --
> Greg Moore
> SQL Server DBA Consulting Remote and Onsite available!
> Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
>|||Hello Gary,
I agree Tibor that you may want to use Move option since you may want to
use a different path for the database files. You could refer to the
following article for some related information
314546 HOW TO: Move Databases Between Computers That Are Running SQL Server
http://support.microsoft.com/?id=314546
Please let's know if you have any further questions on this issue. Thanks.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.|||On May 12, 9:24 am, "GaryDean" <GaryD...@.newsgroups.nospam> wrote:
> I have a database called Paris. I want to clone it but I want the new name
> to be Milan. I tried creating a new database named Milan and restoring
> Paris over it but Management Studio would not allow this. I had overwrite
> checked. How can I do this?
> --
> Regards,
> Gary Blakely
Hi. I suggest you copy DB, that's quite straight forward, you can
choose any name from it.|||GaryDean wrote on Fri, 11 May 2007 18:24:07 -0700:
> I have a database called Paris. I want to clone it but I want the new
> name to be Milan. I tried creating a new database named Milan and
> restoring Paris over it but Management Studio would not allow this. I had
> overwrite checked. How can I do this?
>
If you insist on using the GUI to do this, you need to change the filenames
in the Restore As column on the Options page of the restore task. MS is
preventing you from restoring using the previous filenames as this would
overwrite the files for the Paris database.
Dan|||Well, I don't INSIST on using the gui but...
Thanks, that worked great. Now I see what the badly worded error message
was trying to tell me.
--
Regards,
Gary Blakely
"Daniel Crichton" <msnews@.worldofspack.com> wrote in message
news:OMc3IRjlHHA.4120@.TK2MSFTNGP06.phx.gbl...
> GaryDean wrote on Fri, 11 May 2007 18:24:07 -0700:
>> I have a database called Paris. I want to clone it but I want the new
>> name to be Milan. I tried creating a new database named Milan and
>> restoring Paris over it but Management Studio would not allow this. I
>> had
>> overwrite checked. How can I do this?
>
> If you insist on using the GUI to do this, you need to change the
> filenames in the Restore As column on the Options page of the restore
> task. MS is preventing you from restoring using the previous filenames as
> this would overwrite the files for the Paris database.
> Dan
>|||GaryDean wrote on Mon, 14 May 2007 16:11:25 -0700:
> Well, I don't INSIST on using the gui but...
I guess maybe I should have worded it differently. How about "use the GUI as
an alternative"? :)
> Thanks, that worked great. Now I see what the badly worded error message
> was trying to tell me.
Yeah, it's not that great a message really.
Dan
cloning a db under another name
I have a database called Paris. I want to clone it but I want the new name
to be Milan. I tried creating a new database named Milan and restoring
Paris over it but Management Studio would not allow this. I had overwrite
checked. How can I do this?
Regards,
Gary Blakely"GaryDean" <GaryDean@.newsgroups.nospam> wrote in message
news:ewnkrPDlHHA.4772@.TK2MSFTNGP05.phx.gbl...
>I have a database called Paris. I want to clone it but I want the new name
>to be Milan. I tried creating a new database named Milan and restoring
>Paris over it but Management Studio would not allow this. I had overwrite
>checked. How can I do this?
Don't use the GUI.
Personally I wouldn't bother creating Milan but would simply backup Paris
and
RESTORE DATABASE MILAN from disk='c:\paris.bak' etc etc.
> --
> Regards,
> Gary Blakely
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||> RESTORE DATABASE MILAN from disk='c:\paris.bak' etc etc.
... and read up on the MOVE option of the RESTORE command.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:uJ3iLHElHHA.4552@.TK2MSFTNGP04.phx.gbl...
> "GaryDean" <GaryDean@.newsgroups.nospam> wrote in message
> news:ewnkrPDlHHA.4772@.TK2MSFTNGP05.phx.gbl...
> Don't use the GUI.
> Personally I wouldn't bother creating Milan but would simply backup Paris
and
> RESTORE DATABASE MILAN from disk='c:\paris.bak' etc etc.
>
>
> --
> Greg Moore
> SQL Server DBA Consulting Remote and Onsite available!
> Email: sql (at) greenms.com [url]http://www.greenms.com/sqlserver.html[/ur
l]
>|||Hello Gary,
I agree Tibor that you may want to use Move option since you may want to
use a different path for the database files. You could refer to the
following article for some related information
314546 HOW TO: Move Databases Between Computers That Are Running SQL Server
http://support.microsoft.com/?id=314546
Please let's know if you have any further questions on this issue. Thanks.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.|||On May 12, 9:24 am, "GaryDean" <GaryD...@.newsgroups.nospam> wrote:
> I have a database called Paris. I want to clone it but I want the new nam
e
> to be Milan. I tried creating a new database named Milan and restoring
> Paris over it but Management Studio would not allow this. I had overwrite
> checked. How can I do this?
> --
> Regards,
> Gary Blakely
Hi. I suggest you copy DB, that's quite straight forward, you can
choose any name from it.|||GaryDean wrote on Fri, 11 May 2007 18:24:07 -0700:
> I have a database called Paris. I want to clone it but I want the new
> name to be Milan. I tried creating a new database named Milan and
> restoring Paris over it but Management Studio would not allow this. I had
> overwrite checked. How can I do this?
>
If you insist on using the GUI to do this, you need to change the filenames
in the Restore As column on the Options page of the restore task. MS is
preventing you from restoring using the previous filenames as this would
overwrite the files for the Paris database.
Dan|||Well, I don't INSIST on using the gui but...
Thanks, that worked great. Now I see what the badly worded error message
was trying to tell me.
Regards,
Gary Blakely
"Daniel Crichton" <msnews@.worldofspack.com> wrote in message
news:OMc3IRjlHHA.4120@.TK2MSFTNGP06.phx.gbl...
> GaryDean wrote on Fri, 11 May 2007 18:24:07 -0700:
>
>
> If you insist on using the GUI to do this, you need to change the
> filenames in the Restore As column on the Options page of the restore
> task. MS is preventing you from restoring using the previous filenames as
> this would overwrite the files for the Paris database.
> Dan
>|||GaryDean wrote on Mon, 14 May 2007 16:11:25 -0700:
> Well, I don't INSIST on using the gui but...
I guess maybe I should have worded it differently. How about "use the GUI as
an alternative"?
> Thanks, that worked great. Now I see what the badly worded error message
> was trying to tell me.
Yeah, it's not that great a message really.
Dan
to be Milan. I tried creating a new database named Milan and restoring
Paris over it but Management Studio would not allow this. I had overwrite
checked. How can I do this?
Regards,
Gary Blakely"GaryDean" <GaryDean@.newsgroups.nospam> wrote in message
news:ewnkrPDlHHA.4772@.TK2MSFTNGP05.phx.gbl...
>I have a database called Paris. I want to clone it but I want the new name
>to be Milan. I tried creating a new database named Milan and restoring
>Paris over it but Management Studio would not allow this. I had overwrite
>checked. How can I do this?
Don't use the GUI.
Personally I wouldn't bother creating Milan but would simply backup Paris
and
RESTORE DATABASE MILAN from disk='c:\paris.bak' etc etc.
> --
> Regards,
> Gary Blakely
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||> RESTORE DATABASE MILAN from disk='c:\paris.bak' etc etc.
... and read up on the MOVE option of the RESTORE command.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:uJ3iLHElHHA.4552@.TK2MSFTNGP04.phx.gbl...
> "GaryDean" <GaryDean@.newsgroups.nospam> wrote in message
> news:ewnkrPDlHHA.4772@.TK2MSFTNGP05.phx.gbl...
> Don't use the GUI.
> Personally I wouldn't bother creating Milan but would simply backup Paris
and
> RESTORE DATABASE MILAN from disk='c:\paris.bak' etc etc.
>
>
> --
> Greg Moore
> SQL Server DBA Consulting Remote and Onsite available!
> Email: sql (at) greenms.com [url]http://www.greenms.com/sqlserver.html[/ur
l]
>|||Hello Gary,
I agree Tibor that you may want to use Move option since you may want to
use a different path for the database files. You could refer to the
following article for some related information
314546 HOW TO: Move Databases Between Computers That Are Running SQL Server
http://support.microsoft.com/?id=314546
Please let's know if you have any further questions on this issue. Thanks.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.|||On May 12, 9:24 am, "GaryDean" <GaryD...@.newsgroups.nospam> wrote:
> I have a database called Paris. I want to clone it but I want the new nam
e
> to be Milan. I tried creating a new database named Milan and restoring
> Paris over it but Management Studio would not allow this. I had overwrite
> checked. How can I do this?
> --
> Regards,
> Gary Blakely
Hi. I suggest you copy DB, that's quite straight forward, you can
choose any name from it.|||GaryDean wrote on Fri, 11 May 2007 18:24:07 -0700:
> I have a database called Paris. I want to clone it but I want the new
> name to be Milan. I tried creating a new database named Milan and
> restoring Paris over it but Management Studio would not allow this. I had
> overwrite checked. How can I do this?
>
If you insist on using the GUI to do this, you need to change the filenames
in the Restore As column on the Options page of the restore task. MS is
preventing you from restoring using the previous filenames as this would
overwrite the files for the Paris database.
Dan|||Well, I don't INSIST on using the gui but...
Thanks, that worked great. Now I see what the badly worded error message
was trying to tell me.
Regards,
Gary Blakely
"Daniel Crichton" <msnews@.worldofspack.com> wrote in message
news:OMc3IRjlHHA.4120@.TK2MSFTNGP06.phx.gbl...
> GaryDean wrote on Fri, 11 May 2007 18:24:07 -0700:
>
>
> If you insist on using the GUI to do this, you need to change the
> filenames in the Restore As column on the Options page of the restore
> task. MS is preventing you from restoring using the previous filenames as
> this would overwrite the files for the Paris database.
> Dan
>|||GaryDean wrote on Mon, 14 May 2007 16:11:25 -0700:
> Well, I don't INSIST on using the gui but...
I guess maybe I should have worded it differently. How about "use the GUI as
an alternative"?
> Thanks, that worked great. Now I see what the badly worded error message
> was trying to tell me.
Yeah, it's not that great a message really.
Dan
Clone new database from existing
How do I go about creating a cloned copy of an existing database on the same server? I basically want to set up a test copy of a production database, but let it reside on the same server with a slightly different name, prefixed with 'Testing_".
I typically just use Studio Express Manager and not scripts to do admin tasks. Here's what I tried that didn't work so well:
* Had full backups of production db
* Detached production database I wanted to clone
* From within Win Explorer, copy the .mdf and .ldf files within same data folder and gave them a new name, prefixing with "Testing_"
* Re-attached prodution db
* Attached "Testing_..." database
Sql Express seems confused at this point with new database I attached. It references the full path of the database I copied from (c:\program files\microsoft sql server\mssql.1\data\...". I rename the datbase, removing full path and prefixing with "Testing_".
* Using Sql authentication, changed the default database for the Login account to the new cloned copy of database.
* Did some query level data sanitizing to the cloned copy of the db. I am positive I performed this on the correct db.
* Was able to connect/authenticate to the cloned copy through a custom VB app on my dev machine, and all looked well.
* Tried to connect to the cloned db from another machine and couldn't. Went back to my dev machine and noticed the original production db looked hosed -- there were no tree nodes available to it! Was still able to connect to the cloned "Testing_" db copy, but not able to connect/authenticate anymore to the production copy.
* Detached cloned copy of "Testing_" db and moved it out of the data folder.
* Detached production db. Got an error msg indicating operation failure of some kind, but the database was gone from the tree.
* Reattached the production db but couldn't hit it from my custom vb app. Re-did the login and user accounts used by the app and was able to hit it. However, the data sanitizing I had done earlier in the cloned copy clearly was visibile in the production db!
* Had to restore production db from backup. Had problems authenticating from app, and recreated User account before the app was to connect.
Q1: What I am doing wrong? Sql Express was obviously as confused as me somewhere along the line.
Q2: Is specifying a default database used by a Login account necessary for a VB app like mine. I think it was something I discovered in the past, however, this would seemingly present a problem whenever I want my app to hit a different database, established through a connection string.
TIA...
Rick
hi Rick,
when doing this kind of "sorcery", please be carefull when "re-registering" the database..
just to cross check, usually you should:
-detach the original db;
-copy it elsewhere or rename destination files (mdf, ldf and eventual ndf) say, with your prefix.. (orig.mdf becomes clone_of_orig.mdf and the like);
-reattach the original db via
USE [master]GO
CREATE DATABASE [myDB] ON
( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.3\MSSQL\Data\myDB.mdf' ),
( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.3\MSSQL\Data\myDB_log.ldf' )
FOR ATTACH
GO
-the original logical db name is correct, as the original physical file(s) pointers..
-attach the cloned db via
USE [master]GO
CREATE DATABASE [CLONE_of_myDB] ON
( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.3\MSSQL\Data\clone_of_myDB.mdf' ),
( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.3\MSSQL\Data\clone_of_myDB_log.ldf' )
FOR ATTACH
GO
as you can see, the logical name of the db is different, as are the referenced phisical file names..
-the internal logical names of each physical file are not considered to collide with other database's logical names, so you are not in trouble here..
regards
|||Hmmh... what you described is exactly what I did, with pecuilar results, except use Sql Express Manager.- I detached original db
- Copied to new set and renamed (cloned)
- Reattached original db
- Attached cloned db
Is Sql Express Manager not up to this task?
Rick|||
Do you get an error message or what are you experiencing ? If you can′t get attach / detach to run for you, you can also use the backup / restore functionality for this.
HTH, jens K. Suessmeyer.
http://www.sqlserver2005.de
I did try restoring the source db and specifying clone db as target, but was prevented from doing so with a message to the effect of "target database not in dataset so not allowable."
If somebody wouldn't mind trying to clone a db from the same server through Sql Express Manager, and let me know the results, I'd feel better about trying it again. When I tried it, I discovered I was simultaneously sanitizing both databases.
Rick|||The 'safest' (and easiest) method to do this is to do a backup, and then restore to a different name and/or location. Read up on RESTORE in Books Online.|||Alright, I was able to do that through Express Manager this time. Much easier and safer. Thanks, both of you.
Rick
Subscribe to:
Posts (Atom)