sql server programming: I have a table (Say TblA) with composite Clustered PK key ID1+ID2+ID3 plus
other columns.
There are high levels of inserts on this table.

Question

1. Is using Insert (With Nolock) a good idea? What are the
advantages/disadvantages.
2. Changing PK to a new identity column (clustered) and creating a unique
nonclustered index on ID1+ID2+ID3 be a better idea.

If you INSERT with NOLOCK, you still get a lock.

Clustering on an identity gives you the advantage that inserts will not
cause data page fragmentation. Also, all nonclustered indexes include the
clustering key. Thus, if your clustering key is, say, an int, that's more
advantageous than a composite key of, say, 3 ints, because all of your
nonclustered indexes will have smaller bookmarks. It's less
resource-intensive to rebuild a nonclustered index than a clustered one -
given the same data size. If the combination of the 3 columns still has to
be unique, you can create a UNIQUE constraint to satisfy that requirement.
Just be sure to set the fill factor < 100 and defrag as required to prevent
page splits.

HTH

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau


[quoted text, click to view]
I have a table (Say TblA) with composite Clustered PK key ID1+ID2+ID3 plus
other columns.
There are high levels of inserts on this table.

Question

1. Is using Insert (With Nolock) a good idea? What are the
advantages/disadvantages.
2. Changing PK to a new identity column (clustered) and creating a unique
nonclustered index on ID1+ID2+ID3 be a better idea.

Sorry I made a mistake in my post. I typed Nolock instead of ROWLOCK. It's
been a busy week.

So the post will read as following:

I have a table (Say TblA) with composite Clustered PK key ID1+ID2+ID3 plus
other columns. There are high levels of inserts on this table.

Questions

1. Is using Insert (With ROWLOCK) a good idea? What are the
advantages/disadvantages.
2. Changing PK to a new identity column (clustered) and creating a unique
nonclustered index on ID1+ID2+ID3 be a better idea.

Thanks all.

[quoted text, click to view]

Generally, the optimizer gets it right. You would use ROWLOCK only in very
specific instances where you do reap a benefit - after you test it
thoroughly to prove that it was necessary.

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau


[quoted text, click to view]
Sorry I made a mistake in my post. I typed Nolock instead of ROWLOCK. It's
been a busy week.

So the post will read as following:

I have a table (Say TblA) with composite Clustered PK key ID1+ID2+ID3 plus
other columns. There are high levels of inserts on this table.

Questions

1. Is using Insert (With ROWLOCK) a good idea? What are the
advantages/disadvantages.
2. Changing PK to a new identity column (clustered) and creating a unique
nonclustered index on ID1+ID2+ID3 be a better idea.

Thanks all.

[quoted text, click to view]

Subhash Agarwal (subhasha31@msn.com) writes:
[quoted text, click to view]

A distinct disadvantage is that NOLOCK is not permitted with INSERT.

[quoted text, click to view]

Impossible to say without more detailed knowledge about how it is used etc.

One situation it could make sense is that this is a table with a high
insertion rate, and the data inserted is not aligned with the current
clustered index, leading to page splits galore. In this case adding an
IDENTITY column and clustering on it, can be a life-saver.

But I can think of many more scenarios where it would be a bad idea. In
most cases when I have both a surrogate key and a natural key in a table,
I cluster on the natural key, and not on the IDENTITY column.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at

[quoted text, click to view]

Hypothetical: would it make sense instead to leave the three-column
primary key, but make it NONclustered? Does there absolutely have to
be a clustered index?

rpresser (rpresser@gmail.com) writes:
[quoted text, click to view]

There is nothing that says that the PK has to be clustered. Sometimes
clustering on the PK is bad idea, sometimes it's a good idea. It all
depends on how the table is used.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at