Code inside! --> How to return the @@identity parameter without using stored procedures

Hi.
here is my code with my problem described in the syntax.
I am using asp.net 1.1 and VB.NET
Thanks in advance for your help.
I am still a beginner and I know that your time is precious. I would really appreciate it if you could "fill" my example function with the right code that returns the new ID of the newly inserted row.

PublicFunction howToReturnID(ByVal aCompanyAsString,ByVal aNameAsString)AsInteger

'that is the variable for the new id.
Dim intNewIDAsInteger

Dim strSQLAsString ="INSERT INTO tblAnfragen(aCompany, aName)" & _
"VALUES (@.aCompany, @.aName); SELECT @.NewID = @.@.identity"

Dim dbConnectionAs SqlConnection =New SqlConnection(connectionString)
Dim dbCommandAs SqlCommand =New SqlCommand()
dbCommand.CommandText = strSQL

'Here is my problem.
'What do I have to do in order to add the parameter @.NewID and
'how do I read and return the value of @.NewID within that function howToReturnID
'any help is greatly appreciated!
'I cannot use SPs in this application - have to do it this way! :-(

dbCommand.Parameters.Add("@.aFirma", aCompany.Trim)
dbCommand.Parameters.Add("@.aAnsprAnrede", aName.Trim)

dbCommand.Connection = dbConnection

Try
dbConnection.Open()
dbCommand.ExecuteNonQuery()

'here i want to return the new ID!
Return intNewID

Catch exAs Exception

ThrowNew System.Exception("Error: " & ex.Message.ToString())

Finally

dbCommand.Dispose()
dbConnection.Close()
dbConnection.Dispose()

EndTry

EndFunction

Why don't you put your SQL statement something like this;
Insert Into table (col1, col2) Values (1, 2); Select @.@.IDENTITY
And from there, to retrieve the @.@.IDENTITY, you will execute a scalar return. For example:
Dim identity As Integer = Convert.ToInt32(command.ExecuteScalar())
|||

Hi,

thank you very much for your help.
I tried your suggestion but got always two rows inserted.

Obviously the command object executed the insert statement two times?

first here: db.command.ExecuteNonQuery()
and here:Dim identity As Integer = Convert.ToInt32(command.ExecuteScalar())
this is what I did:
Is this correct ? -at least it works ;-) - but is it the "right way" to do it?
PublicFunction howToReturnID(ByVal aCompanyAsString,ByVal aNameAsString)AsInteger

'that is the variable for the new id.
Dim intNewIDAsInteger

Dim strSQLAsString ="INSERT INTO tblAnfragen(aCompany, aName)" & _
"VALUES (@.aCompany, @.aName);"

'I separated the SQL query string
Dim strSQL2AsString ="Select @.@.IDENTITY;"

Dim dbConnectionAs SqlConnection =New SqlConnection(connectionString)
Dim dbCommandAs SqlCommand =New SqlCommand()
dbCommand.CommandText = strSQL

dbCommand.Parameters.Add("@.aFirma", aCompany.Trim)
dbCommand.Parameters.Add("@.aAnsprAnrede", aName.Trim)

dbCommand.Connection = dbConnection

Try
dbConnection.Open()
'execute first query
dbCommand.ExecuteNonQuery()

'execute second query - this actually returns the id of the currently inserted row.
'But is this the CORRECT WAY to do it?? Any objections?
'this solution only inserts one row not two - as it did when the SQL-query was in one string
dbCommand.CommandText = strSQL2
newID = Convert.ToInt32(dbCommand.ExecuteScalar)

Return intNewID

Catch exAs Exception

ThrowNew System.Exception("Error: " & ex.Message.ToString())

Finally

dbCommand.Dispose()
dbConnection.Close()
dbConnection.Dispose()

EndTry

EndFunction

|||Don't call the ExecuteNonQuery() method. Every Execute*() method that you call runs it as a completely new query, if you know what I mean.
Regards,
Justin|||Well, not really
You mean the .ExecuteScalar method of the command object does also run the insert query?
If so why is there a ExecuteNonQuery method at all?
Is the way I did it in my second source code example not recommendable?
But it works for me - or is there a good reason not to do it that way (performance issues, etc.) ?|||Well, with your second code example, you have described what is wrong with it - it inserts the same data twice into the table. So this breaks logics reason. Besides calling the Execute*() twice, I don't see anything else wrong with the second code posting.
The reason why we have the three Execute() methods (ExecuteNonQuery, ExecuteReader, and ExecuteScalar) on the commands are that each one tells the 'executer' (loosely saying it here) on what type of result to expect. With ExecuteNonQuery(), it returns how many rows were affected. With ExecuteScalar() method, you are expect a simple value type to be returned. And with the ExecuteReader(), it returns a data reader...
They all 'execute' but you have to decide on what information you need to retrieve from execution of that command. Am I making sense now?|||Hi,
yes, everything works now the way it is supposed to :)
Thank you very much for your help!
But in my second code example that i have posted it does not insert the row twice ;-)
Just take a closer look at it - I have provided the command object with a second query that only selects @.@.identity .
But I did that "workaround" because I didn't know that executeScalar also "executes" insert queries ...!
My problem is solved now!|||You are right. I did overlook that. Silly me. Anyway, it was a pleasure.

clustered vs. non clustered

I've been doing a bit of reading and have read in quite a few places
that an identity column is a good clustered index and that all or at
least most tables should have a clustered index. The tool I used to
generate tables made them all with non clustered indexes so I would
like to drop all of them and generate clustered indexes. So my
questions is a) good idea? and b) how? There are foreign key references
to most of them so those would need to be dropped first and then
re-created after the clustered one was created and that could cascade
(I think?)

Any existing scripts out there that might do this? I found something
similar and modified it, the sql is included below. This gives me the
list of all the columns I need, I just need to get the foreign keys for
each from here before each one and generate all the create/drop
scripts.

All the columns I am looking to do this for are called "Id" making this
somewhat simpler. I'm just looking to incrementally make the SQL side
better and don't want to rewrite a bunch of application level code to
make the column names ISO compliant, etc.

/*
-- Returns whether the column is ASC or DESC
CREATE FUNCTION dbo.GetIndexColumnOrder
(
@.object_id INT,
@.index_id TINYINT,
@.column_id TINYINT
)
RETURNS NVARCHAR(5)
AS
BEGIN
DECLARE @.r NVARCHAR(5)
SELECT @.r = CASE INDEXKEY_PROPERTY
(
@.object_id,
@.index_id,
@.column_id,
'IsDescending'
)
WHEN 1 THEN N' DESC'
ELSE N''
END
RETURN @.r
END

-- Returns the list of columns in the index
CREATE FUNCTION dbo.GetIndexColumns
(
@.table_name SYSNAME,
@.object_id INT,
@.index_id TINYINT
)
RETURNS NVARCHAR(4000)
AS
BEGIN
DECLARE
@.colnames NVARCHAR(4000),
@.thisColID INT,
@.thisColName SYSNAME

SET @.colnames = INDEX_COL(@.table_name, @.index_id, 1)
+ dbo.GetIndexColumnOrder(@.object_id, @.index_id, 1)

SET @.thisColID = 2
SET @.thisColName = INDEX_COL(@.table_name, @.index_id, @.thisColID)
+ dbo.GetIndexColumnOrder(@.object_id, @.index_id, @.thisColID)

WHILE (@.thisColName IS NOT NULL)
BEGIN
SET @.thisColID = @.thisColID + 1
SET @.colnames = @.colnames + ', ' + @.thisColName

SET @.thisColName = INDEX_COL(@.table_name, @.index_id,
@.thisColID)
+ dbo.GetIndexColumnOrder(@.object_id, @.index_id,
@.thisColID)
END
RETURN @.colNames
END

CREATE VIEW dbo.vAllIndexes
AS
begin
SELECT
TABLE_NAME = OBJECT_NAME(i.id),
INDEX_NAME = i.name,
COLUMN_LIST = dbo.GetIndexColumns(OBJECT_NAME(i.id), i.id,
i.indid),
IS_CLUSTERED = INDEXPROPERTY(i.id, i.name, 'IsClustered'),
IS_UNIQUE = INDEXPROPERTY(i.id, i.name, 'IsUnique'),
FILE_GROUP = g.GroupName
FROM
sysindexes i
INNER JOIN
sysfilegroups g
ON
i.groupid = g.groupid
WHERE
(i.indid BETWEEN 1 AND 254)
-- leave out AUTO_STATISTICS:
AND (i.Status & 64)=0
-- leave out system tables:
AND OBJECTPROPERTY(i.id, 'IsMsShipped') = 0
end
*/

SELECT
v.*
FROM
dbo.vAllIndexes v
INNER JOIN
INFORMATION_SCHEMA.TABLE_CONSTRAINTS T
ON
T.CONSTRAINT_NAME = v.INDEX_NAME
AND T.TABLE_NAME = v.TABLE_NAME
AND T.CONSTRAINT_TYPE = 'PRIMARY KEY'
AND v.COLUMN_LIST = 'Id'
AND v.IS_CLUSTERED = 0
ORDER BY v.TABLE_NAMEIt's OK to have a clustered index that is seperate from your
nonclustered primary key, even if the two indexes cover the same
columns. In fact, I usually build my indexes in this way in case I
ever have to move the clustered index to a different column and I don't
want to mess with my established foreign key constraints.

That being said, I would simply add the clustered index to each table
and not worry about dropping the pre-existing primary key constraint.
It'll take a while, but it will work.

Stu

pb648174 wrote:

Quote:

Originally Posted by

I've been doing a bit of reading and have read in quite a few places
that an identity column is a good clustered index and that all or at
least most tables should have a clustered index. The tool I used to
generate tables made them all with non clustered indexes so I would
like to drop all of them and generate clustered indexes. So my
questions is a) good idea? and b) how? There are foreign key references
to most of them so those would need to be dropped first and then
re-created after the clustered one was created and that could cascade
(I think?)
>
Any existing scripts out there that might do this? I found something
similar and modified it, the sql is included below. This gives me the
list of all the columns I need, I just need to get the foreign keys for
each from here before each one and generate all the create/drop
scripts.
>
All the columns I am looking to do this for are called "Id" making this
somewhat simpler. I'm just looking to incrementally make the SQL side
better and don't want to rewrite a bunch of application level code to
make the column names ISO compliant, etc.
>
/*
-- Returns whether the column is ASC or DESC
CREATE FUNCTION dbo.GetIndexColumnOrder
(
@.object_id INT,
@.index_id TINYINT,
@.column_id TINYINT
)
RETURNS NVARCHAR(5)
AS
BEGIN
DECLARE @.r NVARCHAR(5)
SELECT @.r = CASE INDEXKEY_PROPERTY
(
@.object_id,
@.index_id,
@.column_id,
'IsDescending'
)
WHEN 1 THEN N' DESC'
ELSE N''
END
RETURN @.r
END
>
-- Returns the list of columns in the index
CREATE FUNCTION dbo.GetIndexColumns
(
@.table_name SYSNAME,
@.object_id INT,
@.index_id TINYINT
)
RETURNS NVARCHAR(4000)
AS
BEGIN
DECLARE
@.colnames NVARCHAR(4000),
@.thisColID INT,
@.thisColName SYSNAME
>
SET @.colnames = INDEX_COL(@.table_name, @.index_id, 1)
+ dbo.GetIndexColumnOrder(@.object_id, @.index_id, 1)
>
SET @.thisColID = 2
SET @.thisColName = INDEX_COL(@.table_name, @.index_id, @.thisColID)
+ dbo.GetIndexColumnOrder(@.object_id, @.index_id, @.thisColID)
>
WHILE (@.thisColName IS NOT NULL)
BEGIN
SET @.thisColID = @.thisColID + 1
SET @.colnames = @.colnames + ', ' + @.thisColName
>
SET @.thisColName = INDEX_COL(@.table_name, @.index_id,
@.thisColID)
+ dbo.GetIndexColumnOrder(@.object_id, @.index_id,
@.thisColID)
END
RETURN @.colNames
END
>
CREATE VIEW dbo.vAllIndexes
AS
begin
SELECT
TABLE_NAME = OBJECT_NAME(i.id),
INDEX_NAME = i.name,
COLUMN_LIST = dbo.GetIndexColumns(OBJECT_NAME(i.id), i.id,
i.indid),
IS_CLUSTERED = INDEXPROPERTY(i.id, i.name, 'IsClustered'),
IS_UNIQUE = INDEXPROPERTY(i.id, i.name, 'IsUnique'),
FILE_GROUP = g.GroupName
FROM
sysindexes i
INNER JOIN
sysfilegroups g
ON
i.groupid = g.groupid
WHERE
(i.indid BETWEEN 1 AND 254)
-- leave out AUTO_STATISTICS:
AND (i.Status & 64)=0
-- leave out system tables:
AND OBJECTPROPERTY(i.id, 'IsMsShipped') = 0
end
*/
>
SELECT
v.*
FROM
dbo.vAllIndexes v
INNER JOIN
INFORMATION_SCHEMA.TABLE_CONSTRAINTS T
ON
T.CONSTRAINT_NAME = v.INDEX_NAME
AND T.TABLE_NAME = v.TABLE_NAME
AND T.CONSTRAINT_TYPE = 'PRIMARY KEY'
AND v.COLUMN_LIST = 'Id'
AND v.IS_CLUSTERED = 0
ORDER BY v.TABLE_NAME

|||pb648174 (google@.webpaul.net) writes:

Quote:

Originally Posted by

I've been doing a bit of reading and have read in quite a few places
that an identity column is a good clustered index and that all or at
least most tables should have a clustered index. The tool I used to
generate tables made them all with non clustered indexes so I would
like to drop all of them and generate clustered indexes.

Yes, having clustered indexes on all tables is a good idea, but the
IDENTITY column is not always the best choice. It's a good choice if
you have a high transaction rate, and you want to avoid fragmentation
and page splits.

But for SELECT queries it is likely that in most tables that there
are better candidates for the clustered index, as you don't do
range queries on ids that often. So I would suggest that you review
your tables and look for better columns to cluster on.

Here I had single-column PKs in mind. Clustering on a multi-column PK,
or part of it is another matter. Take an OrderDetails table for instance.
"SELECT ... FROM OrderDetails WHERE OrderID = @.id" is a very likely
query and a clustred index may be great here.

Stu's suggestion of keeping the PK non-clustered, and adding a clustered
index as well is not that bad. If you have a multi-column key that is 4
30 bytes long, but the first key column is four bytes, the clustering on
the first columns means that the key size for the clustered index is
only 8 bytes. (key col + uniquifier). Since cluster-key colunms appear
in non-clustered index, this matters quite a bit.

As for looking up the foreign keys, the tables are sysreferences in
SQL 2000 and sys.forein_keys in SQL 2005.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Well that makes things simpler then.. I'll try adding the clustered
columns to one area of the app and see if it makes a positive or
negative performance impact. Thanks for the info guys.

Erland Sommarskog wrote:

Quote:

Originally Posted by

pb648174 (google@.webpaul.net) writes:

Quote:

Originally Posted by

I've been doing a bit of reading and have read in quite a few places
that an identity column is a good clustered index and that all or at
least most tables should have a clustered index. The tool I used to
generate tables made them all with non clustered indexes so I would
like to drop all of them and generate clustered indexes.

>
Yes, having clustered indexes on all tables is a good idea, but the
IDENTITY column is not always the best choice. It's a good choice if
you have a high transaction rate, and you want to avoid fragmentation
and page splits.
>
But for SELECT queries it is likely that in most tables that there
are better candidates for the clustered index, as you don't do
range queries on ids that often. So I would suggest that you review
your tables and look for better columns to cluster on.
>
Here I had single-column PKs in mind. Clustering on a multi-column PK,
or part of it is another matter. Take an OrderDetails table for instance.
"SELECT ... FROM OrderDetails WHERE OrderID = @.id" is a very likely
query and a clustred index may be great here.
>
Stu's suggestion of keeping the PK non-clustered, and adding a clustered
index as well is not that bad. If you have a multi-column key that is 4
30 bytes long, but the first key column is four bytes, the clustering on
the first columns means that the key size for the clustered index is
only 8 bytes. (key col + uniquifier). Since cluster-key colunms appear
in non-clustered index, this matters quite a bit.
>
As for looking up the foreign keys, the tables are sysreferences in
SQL 2000 and sys.forein_keys in SQL 2005.
>
>
>
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

|||Performance was actually worse once I added the clustered index. A
query that takes 4 seconds took 5 seconds after adding clustered
indexes to all the tables for a particular module. I turned the actual
execution plan display on and saw that it was using the clustered index
instead of the non clustered. So without the clustered index the
largest time used is an "index seek" and a "table spool/lazy spool" and
with the clustered index the index seek just becomes a clustered index
seek... No big difference except it takes longer!

pb648174 wrote:

Quote:

Originally Posted by

Well that makes things simpler then.. I'll try adding the clustered
columns to one area of the app and see if it makes a positive or
negative performance impact. Thanks for the info guys.
>
Erland Sommarskog wrote:

Quote:

Originally Posted by

pb648174 (google@.webpaul.net) writes:

Quote:

Originally Posted by

I've been doing a bit of reading and have read in quite a few places
that an identity column is a good clustered index and that all or at
least most tables should have a clustered index. The tool I used to
generate tables made them all with non clustered indexes so I would
like to drop all of them and generate clustered indexes.

Yes, having clustered indexes on all tables is a good idea, but the
IDENTITY column is not always the best choice. It's a good choice if
you have a high transaction rate, and you want to avoid fragmentation
and page splits.

But for SELECT queries it is likely that in most tables that there
are better candidates for the clustered index, as you don't do
range queries on ids that often. So I would suggest that you review
your tables and look for better columns to cluster on.

Here I had single-column PKs in mind. Clustering on a multi-column PK,
or part of it is another matter. Take an OrderDetails table for instance.
"SELECT ... FROM OrderDetails WHERE OrderID = @.id" is a very likely
query and a clustred index may be great here.

Stu's suggestion of keeping the PK non-clustered, and adding a clustered
index as well is not that bad. If you have a multi-column key that is 4
30 bytes long, but the first key column is four bytes, the clustering on
the first columns means that the key size for the clustered index is
only 8 bytes. (key col + uniquifier). Since cluster-key colunms appear
in non-clustered index, this matters quite a bit.

As for looking up the foreign keys, the tables are sysreferences in
SQL 2000 and sys.forein_keys in SQL 2005.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

|||Index tuning is not black-and-white, especially when it comes to the
clustered index decision. It is likely than some queries will benefit by
the PK clustered index while others will not. You'll need run a mix of
queries that is representative of the actual workload mix to ascertain
overall performance impact. IMHO, an all-or-nothing clustered index
decision is naive.

It is also possible that some tables will benefit with the clustered PK and
others will not. I know that this adds a wrinkle to automated schema
generation but this is reality. You might consider using the Index Tuning
Wizard (SQL 2000) or Database Engine Tuning Advisor (SQL 2005) for index
recommendations based on workload.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"pb648174" <google@.webpaul.netwrote in message
news:1155727057.245342.208480@.74g2000cwt.googlegro ups.com...

Quote:

Originally Posted by

Performance was actually worse once I added the clustered index. A
query that takes 4 seconds took 5 seconds after adding clustered
indexes to all the tables for a particular module. I turned the actual
execution plan display on and saw that it was using the clustered index
instead of the non clustered. So without the clustered index the
largest time used is an "index seek" and a "table spool/lazy spool" and
with the clustered index the index seek just becomes a clustered index
seek... No big difference except it takes longer!
>
pb648174 wrote:

Quote:

Originally Posted by

>Well that makes things simpler then.. I'll try adding the clustered
>columns to one area of the app and see if it makes a positive or
>negative performance impact. Thanks for the info guys.
>>
>Erland Sommarskog wrote:

Quote:

Originally Posted by

pb648174 (google@.webpaul.net) writes:
I've been doing a bit of reading and have read in quite a few places
that an identity column is a good clustered index and that all or at
least most tables should have a clustered index. The tool I used to
generate tables made them all with non clustered indexes so I would
like to drop all of them and generate clustered indexes.
>
Yes, having clustered indexes on all tables is a good idea, but the
IDENTITY column is not always the best choice. It's a good choice if
you have a high transaction rate, and you want to avoid fragmentation
and page splits.
>
But for SELECT queries it is likely that in most tables that there
are better candidates for the clustered index, as you don't do
range queries on ids that often. So I would suggest that you review
your tables and look for better columns to cluster on.
>
Here I had single-column PKs in mind. Clustering on a multi-column PK,
or part of it is another matter. Take an OrderDetails table for
instance.
"SELECT ... FROM OrderDetails WHERE OrderID = @.id" is a very likely
query and a clustred index may be great here.
>
Stu's suggestion of keeping the PK non-clustered, and adding a
clustered
index as well is not that bad. If you have a multi-column key that is 4
30 bytes long, but the first key column is four bytes, the clustering
on
the first columns means that the key size for the clustered index is
only 8 bytes. (key col + uniquifier). Since cluster-key colunms appear
in non-clustered index, this matters quite a bit.
>
As for looking up the foreign keys, the tables are sysreferences in
SQL 2000 and sys.forein_keys in SQL 2005.
>
>
>
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

>

Clustered Index Question

sql2k sp3
If my Clustered Index is an Identity Coulmn, should
Inserts still be slowed down. In other words, since a
newly Inserted row should be stored at the end, why would
it be any slower than when theres no Clustered Index? It
shouldnt need to reorganize anything. Aslo, should
Updates be any slower since you cant Update an Identity
field anyways?
TIA, ChrisR
Actually it should be slightly faster than if it were just a heap. A heap
has to do some lookups to determine where to place the next row that have a
slight bit of overhead. A CI insert with a monotonically incrementing value
is a no brainer per say for SQL Server. As you stated it goes to the end of
the last page.
Andrew J. Kelly SQL MVP
"ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
news:31d601c4a4d4$5d7139e0$a301280a@.phx.gbl...
> sql2k sp3
> If my Clustered Index is an Identity Coulmn, should
> Inserts still be slowed down. In other words, since a
> newly Inserted row should be stored at the end, why would
> it be any slower than when theres no Clustered Index? It
> shouldnt need to reorganize anything. Aslo, should
> Updates be any slower since you cant Update an Identity
> field anyways?
> TIA, ChrisR
|||Would Updates be the same?

>--Original Message--
>Actually it should be slightly faster than if it were
just a heap. A heap
>has to do some lookups to determine where to place the
next row that have a
>slight bit of overhead. A CI insert with a
monotonically incrementing value
>is a no brainer per say for SQL Server. As you stated
it goes to the end of
>the last page.
>--
>Andrew J. Kelly SQL MVP
>
>"ChrisR" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:31d601c4a4d4$5d7139e0$a301280a@.phx.gbl...
would[vbcol=seagreen]
It
>
>.
>
|||It depends on which columns you modify. If you modify a column which is part of the clustered index,
the row need to be moved. If you modify a column which is part of a non-clustered index, then the
non-clustered index need to be modified accordingly.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
news:254001c4a4d8$6d1a0710$a501280a@.phx.gbl...[vbcol=seagreen]
> Would Updates be the same?
>
> just a heap. A heap
> next row that have a
> monotonically incrementing value
> it goes to the end of
> message
> would
> It
|||Did Karen mention something about "hot spots" if using clustered identity
column in her book? I have to look it up...
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:#tM$WXNpEHA.1668@.TK2MSFTNGP14.phx.gbl...
> Actually it should be slightly faster than if it were just a heap. A heap
> has to do some lookups to determine where to place the next row that have
a
> slight bit of overhead. A CI insert with a monotonically incrementing
value
> is a no brainer per say for SQL Server. As you stated it goes to the end
of
> the last page.
> --
> Andrew J. Kelly SQL MVP
>
> "ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
> news:31d601c4a4d4$5d7139e0$a301280a@.phx.gbl...
>
|||Who's Karen? What book?

>--Original Message--
>Did Karen mention something about "hot spots" if using
clustered identity
>column in her book? I have to look it up...
>
>"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in
message[vbcol=seagreen]
>news:#tM$WXNpEHA.1668@.TK2MSFTNGP14.phx.gbl...
just a heap. A heap[vbcol=seagreen]
next row that have[vbcol=seagreen]
>a
monotonically incrementing[vbcol=seagreen]
>value
it goes to the end[vbcol=seagreen]
>of
in message[vbcol=seagreen]
would[vbcol=seagreen]
Index? It[vbcol=seagreen]
Identity
>
>.
>
|||It's Kalen Delaney and her book is "Inside SQL Server 2000" which every good
dba should have a copy of...
Andrew J. Kelly SQL MVP
"ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
news:03ec01c4a4ea$a9707ac0$a601280a@.phx.gbl...[vbcol=seagreen]
> Who's Karen? What book?
>
> clustered identity
> message
> just a heap. A heap
> next row that have
> monotonically incrementing
> it goes to the end
> in message
> would
> Index? It
> Identity
|||In addition to Tibor's comments if you update a variable column it may make
the row too large to fit everything on the page and cause a split. But this
will happen regardless of the column the CI is on if the data won't fit.
Andrew J. Kelly SQL MVP
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:e1EmQnNpEHA.1712@.tk2msftngp13.phx.gbl...
> It depends on which columns you modify. If you modify a column which is
part of the clustered index,
> the row need to be moved. If you modify a column which is part of a
non-clustered index, then the
> non-clustered index need to be modified accordingly.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
> news:254001c4a4d8$6d1a0710$a501280a@.phx.gbl...
>
|||Hi
Hot spotting used to be a bigger problem in pre SQL 2000 SP 2.
The storage engine team have done a lot of improvements in their quest for
better performance. The actual limitation is now the Page Allocation Map.
Unless yopu are pushing 1'000's of inserts per second, you won't have an
issue.
Standard rules apply, keep the transactions short and don't have excessive
indexes.
Regards
Mike
"Raymond Fang" wrote:

> Did Karen mention something about "hot spots" if using clustered identity
> column in her book? I have to look it up...
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:#tM$WXNpEHA.1668@.TK2MSFTNGP14.phx.gbl...
> a
> value
> of
>
>
|||Mike, Thanks!
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:6DC591BA-8EC3-4F9F-BF43-993CB1BA534E@.microsoft.com...[vbcol=seagreen]
> Hi
> Hot spotting used to be a bigger problem in pre SQL 2000 SP 2.
> The storage engine team have done a lot of improvements in their quest for
> better performance. The actual limitation is now the Page Allocation Map.
> Unless yopu are pushing 1'000's of inserts per second, you won't have an
> issue.
> Standard rules apply, keep the transactions short and don't have excessive
> indexes.
> Regards
> Mike
> "Raymond Fang" wrote:
identity[vbcol=seagreen]
heap[vbcol=seagreen]
have[vbcol=seagreen]
end[vbcol=seagreen]

Clustered Index Question

sql2k sp3
If my Clustered Index is an Identity Coulmn, should
Inserts still be slowed down. In other words, since a
newly Inserted row should be stored at the end, why would
it be any slower than when theres no Clustered Index? It
shouldnt need to reorganize anything. Aslo, should
Updates be any slower since you cant Update an Identity
field anyways?
TIA, ChrisRActually it should be slightly faster than if it were just a heap. A heap
has to do some lookups to determine where to place the next row that have a
slight bit of overhead. A CI insert with a monotonically incrementing value
is a no brainer per say for SQL Server. As you stated it goes to the end of
the last page.
--
Andrew J. Kelly SQL MVP
"ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
news:31d601c4a4d4$5d7139e0$a301280a@.phx.gbl...
> sql2k sp3
> If my Clustered Index is an Identity Coulmn, should
> Inserts still be slowed down. In other words, since a
> newly Inserted row should be stored at the end, why would
> it be any slower than when theres no Clustered Index? It
> shouldnt need to reorganize anything. Aslo, should
> Updates be any slower since you cant Update an Identity
> field anyways?
> TIA, ChrisR|||Would Updates be the same?
>--Original Message--
>Actually it should be slightly faster than if it were
just a heap. A heap
>has to do some lookups to determine where to place the
next row that have a
>slight bit of overhead. A CI insert with a
monotonically incrementing value
>is a no brainer per say for SQL Server. As you stated
it goes to the end of
>the last page.
>--
>Andrew J. Kelly SQL MVP
>
>"ChrisR" <anonymous@.discussions.microsoft.com> wrote in
message
>news:31d601c4a4d4$5d7139e0$a301280a@.phx.gbl...
>> sql2k sp3
>> If my Clustered Index is an Identity Coulmn, should
>> Inserts still be slowed down. In other words, since a
>> newly Inserted row should be stored at the end, why
would
>> it be any slower than when theres no Clustered Index?
It
>> shouldnt need to reorganize anything. Aslo, should
>> Updates be any slower since you cant Update an Identity
>> field anyways?
>> TIA, ChrisR
>
>.
>|||It depends on which columns you modify. If you modify a column which is part of the clustered index,
the row need to be moved. If you modify a column which is part of a non-clustered index, then the
non-clustered index need to be modified accordingly.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
news:254001c4a4d8$6d1a0710$a501280a@.phx.gbl...
> Would Updates be the same?
>
>>--Original Message--
>>Actually it should be slightly faster than if it were
> just a heap. A heap
>>has to do some lookups to determine where to place the
> next row that have a
>>slight bit of overhead. A CI insert with a
> monotonically incrementing value
>>is a no brainer per say for SQL Server. As you stated
> it goes to the end of
>>the last page.
>>--
>>Andrew J. Kelly SQL MVP
>>
>>"ChrisR" <anonymous@.discussions.microsoft.com> wrote in
> message
>>news:31d601c4a4d4$5d7139e0$a301280a@.phx.gbl...
>> sql2k sp3
>> If my Clustered Index is an Identity Coulmn, should
>> Inserts still be slowed down. In other words, since a
>> newly Inserted row should be stored at the end, why
> would
>> it be any slower than when theres no Clustered Index?
> It
>> shouldnt need to reorganize anything. Aslo, should
>> Updates be any slower since you cant Update an Identity
>> field anyways?
>> TIA, ChrisR
>>
>>.|||Did Karen mention something about "hot spots" if using clustered identity
column in her book? I have to look it up...
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:#tM$WXNpEHA.1668@.TK2MSFTNGP14.phx.gbl...
> Actually it should be slightly faster than if it were just a heap. A heap
> has to do some lookups to determine where to place the next row that have
a
> slight bit of overhead. A CI insert with a monotonically incrementing
value
> is a no brainer per say for SQL Server. As you stated it goes to the end
of
> the last page.
> --
> Andrew J. Kelly SQL MVP
>
> "ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
> news:31d601c4a4d4$5d7139e0$a301280a@.phx.gbl...
> > sql2k sp3
> >
> > If my Clustered Index is an Identity Coulmn, should
> > Inserts still be slowed down. In other words, since a
> > newly Inserted row should be stored at the end, why would
> > it be any slower than when theres no Clustered Index? It
> > shouldnt need to reorganize anything. Aslo, should
> > Updates be any slower since you cant Update an Identity
> > field anyways?
> >
> > TIA, ChrisR
>|||Who's Karen? What book?
>--Original Message--
>Did Karen mention something about "hot spots" if using
clustered identity
>column in her book? I have to look it up...
>
>"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in
message
>news:#tM$WXNpEHA.1668@.TK2MSFTNGP14.phx.gbl...
>> Actually it should be slightly faster than if it were
just a heap. A heap
>> has to do some lookups to determine where to place the
next row that have
>a
>> slight bit of overhead. A CI insert with a
monotonically incrementing
>value
>> is a no brainer per say for SQL Server. As you stated
it goes to the end
>of
>> the last page.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "ChrisR" <anonymous@.discussions.microsoft.com> wrote
in message
>> news:31d601c4a4d4$5d7139e0$a301280a@.phx.gbl...
>> > sql2k sp3
>> >
>> > If my Clustered Index is an Identity Coulmn, should
>> > Inserts still be slowed down. In other words, since a
>> > newly Inserted row should be stored at the end, why
would
>> > it be any slower than when theres no Clustered
Index? It
>> > shouldnt need to reorganize anything. Aslo, should
>> > Updates be any slower since you cant Update an
Identity
>> > field anyways?
>> >
>> > TIA, ChrisR
>>
>
>.
>|||It's Kalen Delaney and her book is "Inside SQL Server 2000" which every good
dba should have a copy of...
--
Andrew J. Kelly SQL MVP
"ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
news:03ec01c4a4ea$a9707ac0$a601280a@.phx.gbl...
> Who's Karen? What book?
>
> >--Original Message--
> >Did Karen mention something about "hot spots" if using
> clustered identity
> >column in her book? I have to look it up...
> >
> >
> >
> >"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in
> message
> >news:#tM$WXNpEHA.1668@.TK2MSFTNGP14.phx.gbl...
> >> Actually it should be slightly faster than if it were
> just a heap. A heap
> >> has to do some lookups to determine where to place the
> next row that have
> >a
> >> slight bit of overhead. A CI insert with a
> monotonically incrementing
> >value
> >> is a no brainer per say for SQL Server. As you stated
> it goes to the end
> >of
> >> the last page.
> >>
> >> --
> >> Andrew J. Kelly SQL MVP
> >>
> >>
> >> "ChrisR" <anonymous@.discussions.microsoft.com> wrote
> in message
> >> news:31d601c4a4d4$5d7139e0$a301280a@.phx.gbl...
> >> > sql2k sp3
> >> >
> >> > If my Clustered Index is an Identity Coulmn, should
> >> > Inserts still be slowed down. In other words, since a
> >> > newly Inserted row should be stored at the end, why
> would
> >> > it be any slower than when theres no Clustered
> Index? It
> >> > shouldnt need to reorganize anything. Aslo, should
> >> > Updates be any slower since you cant Update an
> Identity
> >> > field anyways?
> >> >
> >> > TIA, ChrisR
> >>
> >>
> >
> >
> >.
> >|||In addition to Tibor's comments if you update a variable column it may make
the row too large to fit everything on the page and cause a split. But this
will happen regardless of the column the CI is on if the data won't fit.
--
Andrew J. Kelly SQL MVP
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:e1EmQnNpEHA.1712@.tk2msftngp13.phx.gbl...
> It depends on which columns you modify. If you modify a column which is
part of the clustered index,
> the row need to be moved. If you modify a column which is part of a
non-clustered index, then the
> non-clustered index need to be modified accordingly.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
> news:254001c4a4d8$6d1a0710$a501280a@.phx.gbl...
> > Would Updates be the same?
> >
> >
> >>--Original Message--
> >>Actually it should be slightly faster than if it were
> > just a heap. A heap
> >>has to do some lookups to determine where to place the
> > next row that have a
> >>slight bit of overhead. A CI insert with a
> > monotonically incrementing value
> >>is a no brainer per say for SQL Server. As you stated
> > it goes to the end of
> >>the last page.
> >>
> >>--
> >>Andrew J. Kelly SQL MVP
> >>
> >>
> >>"ChrisR" <anonymous@.discussions.microsoft.com> wrote in
> > message
> >>news:31d601c4a4d4$5d7139e0$a301280a@.phx.gbl...
> >> sql2k sp3
> >>
> >> If my Clustered Index is an Identity Coulmn, should
> >> Inserts still be slowed down. In other words, since a
> >> newly Inserted row should be stored at the end, why
> > would
> >> it be any slower than when theres no Clustered Index?
> > It
> >> shouldnt need to reorganize anything. Aslo, should
> >> Updates be any slower since you cant Update an Identity
> >> field anyways?
> >>
> >> TIA, ChrisR
> >>
> >>
> >>.
> >>
>|||Hi
Hot spotting used to be a bigger problem in pre SQL 2000 SP 2.
The storage engine team have done a lot of improvements in their quest for
better performance. The actual limitation is now the Page Allocation Map.
Unless yopu are pushing 1'000's of inserts per second, you won't have an
issue.
Standard rules apply, keep the transactions short and don't have excessive
indexes.
Regards
Mike
"Raymond Fang" wrote:
> Did Karen mention something about "hot spots" if using clustered identity
> column in her book? I have to look it up...
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:#tM$WXNpEHA.1668@.TK2MSFTNGP14.phx.gbl...
> > Actually it should be slightly faster than if it were just a heap. A heap
> > has to do some lookups to determine where to place the next row that have
> a
> > slight bit of overhead. A CI insert with a monotonically incrementing
> value
> > is a no brainer per say for SQL Server. As you stated it goes to the end
> of
> > the last page.
> >
> > --
> > Andrew J. Kelly SQL MVP
> >
> >
> > "ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
> > news:31d601c4a4d4$5d7139e0$a301280a@.phx.gbl...
> > > sql2k sp3
> > >
> > > If my Clustered Index is an Identity Coulmn, should
> > > Inserts still be slowed down. In other words, since a
> > > newly Inserted row should be stored at the end, why would
> > > it be any slower than when theres no Clustered Index? It
> > > shouldnt need to reorganize anything. Aslo, should
> > > Updates be any slower since you cant Update an Identity
> > > field anyways?
> > >
> > > TIA, ChrisR
> >
> >
>
>|||Mike, Thanks!
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:6DC591BA-8EC3-4F9F-BF43-993CB1BA534E@.microsoft.com...
> Hi
> Hot spotting used to be a bigger problem in pre SQL 2000 SP 2.
> The storage engine team have done a lot of improvements in their quest for
> better performance. The actual limitation is now the Page Allocation Map.
> Unless yopu are pushing 1'000's of inserts per second, you won't have an
> issue.
> Standard rules apply, keep the transactions short and don't have excessive
> indexes.
> Regards
> Mike
> "Raymond Fang" wrote:
> > Did Karen mention something about "hot spots" if using clustered
identity
> > column in her book? I have to look it up...
> >
> >
> >
> > "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> > news:#tM$WXNpEHA.1668@.TK2MSFTNGP14.phx.gbl...
> > > Actually it should be slightly faster than if it were just a heap. A
heap
> > > has to do some lookups to determine where to place the next row that
have
> > a
> > > slight bit of overhead. A CI insert with a monotonically incrementing
> > value
> > > is a no brainer per say for SQL Server. As you stated it goes to the
end
> > of
> > > the last page.
> > >
> > > --
> > > Andrew J. Kelly SQL MVP
> > >
> > >
> > > "ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
> > > news:31d601c4a4d4$5d7139e0$a301280a@.phx.gbl...
> > > > sql2k sp3
> > > >
> > > > If my Clustered Index is an Identity Coulmn, should
> > > > Inserts still be slowed down. In other words, since a
> > > > newly Inserted row should be stored at the end, why would
> > > > it be any slower than when theres no Clustered Index? It
> > > > shouldnt need to reorganize anything. Aslo, should
> > > > Updates be any slower since you cant Update an Identity
> > > > field anyways?
> > > >
> > > > TIA, ChrisR
> > >
> > >
> >
> >
> >

Clustered index on Identity field

Hi All,
I have heard that if you create a clustered index on identity column(PK
also) it would decrease the page splits. But how?
Thanks,
PradeepIt will eliminate page splits on inserts since all new rows get appended to
the end of the current page. It does not stop pagespilts due to updates on
existing rows on columns with variable lengths. But in any case that should
not be your sole motivation for where you place the Clustered Index. You can
also avoid or minimize page splits with a proper fill factor.
Andrew J. Kelly SQL MVP
"Pradeep Kutty" <pradeepk@.healthasyst.com> wrote in message
news:eW2tO%23HtFHA.2656@.TK2MSFTNGP10.phx.gbl...
> Hi All,
> I have heard that if you create a clustered index on identity column(PK
> also) it would decrease the page splits. But how?
> Thanks,
> Pradeep
>

clustered index on IDENTITY column

Hi, what opinion do you people have with clustered index on IDENTITY column?
Is there more advantage or divantage?
Wouldn't it causes all insertion to be added to the same page. Is this 'hot
page' a big issue? Can it helps to prevent fragmentation? How is it so?
Secondly, is adding IDENTITY column itself recommended? Is it because by
plainly using the logical unique (eg. Passport Number that is assumed to be
unique) would be slower, maybe because it is not integer; or maybe composite
primary key? Is a IDENTITY primary key useful because of its use as a foreig
n
in related table?
thanks
EugeneHi:
Using a clustered index on an identity column is recommended. It helps
because all the values of a clustered index based on identity values are
unique.
The issue is a bit more complex, however, it wouldn't cause more insertion
problems or hot pages than using a not so distinct primary key column.
Perhaps, if you had a specific problem you are workign on, the context for
the question will be clearer.
Thanks,
Webmaster
http://www.kdkeys.net
"Eugene" <Eugene@.discussions.microsoft.com> wrote in message
news:E684212D-5AD0-4F00-8549-780DE4225D86@.microsoft.com...
> Hi, what opinion do you people have with clustered index on IDENTITY
> column?
> Is there more advantage or divantage?
> Wouldn't it causes all insertion to be added to the same page. Is this
> 'hot
> page' a big issue? Can it helps to prevent fragmentation? How is it so?
> Secondly, is adding IDENTITY column itself recommended? Is it because by
> plainly using the logical unique (eg. Passport Number that is assumed to
> be
> unique) would be slower, maybe because it is not integer; or maybe
> composite
> primary key? Is a IDENTITY primary key useful because of its use as a
> foreign
> in related table?
> thanks
> Eugene|||Using a clustered index on an IDENTITY column is usually best, provided it
is the primary key for the table. Here are a couple reasons:
(1) identity columns are ususally smaller, so joins are faster. In addition
the clustered index key is used as the row locator in every nonclustered
index, so a smaller clustered index key means smaller nonclustered indexes.
Smaller is always faster, because fewer disk reads are required to access an
index.
(2) identity columns usually increase, therefore new rows are generally
added at the end of the clustered index, thus avoiding page splits and all
of the performance issues associated with them.
In my opinion, it is always better to have a primary key that is guaranteed
to be stable, as opposed to a natural or composite key that can change.
"Eugene" <Eugene@.discussions.microsoft.com> wrote in message
news:E684212D-5AD0-4F00-8549-780DE4225D86@.microsoft.com...
> Hi, what opinion do you people have with clustered index on IDENTITY
column?
> Is there more advantage or divantage?
> Wouldn't it causes all insertion to be added to the same page. Is this
'hot
> page' a big issue? Can it helps to prevent fragmentation? How is it so?
> Secondly, is adding IDENTITY column itself recommended? Is it because by
> plainly using the logical unique (eg. Passport Number that is assumed to
be
> unique) would be slower, maybe because it is not integer; or maybe
composite
> primary key? Is a IDENTITY primary key useful because of its use as a
foreign
> in related table?
> thanks
> Eugene|||Hi Eugene,
A few issue here
Clustered Index are the data itself. So the leaf pages of the index are the
data pages. Having an identity make no difference to this what so every.
All it means in the identity column sequence is the same as the logical
layout of the leaf pages. Can this stop or prevent fragmentation yeah
perhaps but if say your Passport Number increased in value and didn't change
(as primary key) this would do the same thing. The issue really isn't
identity or not it is the candidate for PK shouldn't change over time. If
it is likely to then it should not be PK. Remember that a leaf page is only
8K in size the data in that page for is a little bit smaller. So the number
of rows that can fit in a page depends on the row size and the intital fill
factor.
There big debate between the issue of int ot big int over other data types
for PK (in terms of speed and index size ) I would say that if you are
looking at this as a problem then your scope for issues are too small. Disk
space is too cheap to worry about in most cases and in most cases the size
of a PK or index isn't then performance problem. More importantly is making
sure you have covering indexes or at least indexes on the tables that relate
to the grouping and where statements in your views and sql statements.
kind regards
Greg O
"Eugene" <Eugene@.discussions.microsoft.com> wrote in message
news:E684212D-5AD0-4F00-8549-780DE4225D86@.microsoft.com...
> Hi, what opinion do you people have with clustered index on IDENTITY
> column?
> Is there more advantage or divantage?
> Wouldn't it causes all insertion to be added to the same page. Is this
> 'hot
> page' a big issue? Can it helps to prevent fragmentation? How is it so?
> Secondly, is adding IDENTITY column itself recommended? Is it because by
> plainly using the logical unique (eg. Passport Number that is assumed to
> be
> unique) would be slower, maybe because it is not integer; or maybe
> composite
> primary key? Is a IDENTITY primary key useful because of its use as a
> foreign
> in related table?
> thanks
> Eugene|||Hi All, thanks for the kind reply.
I am not looking at a particular project now, I want to know the
technical/academic knowledge/experience on this consideration.
Brian, you mentioned, adding to the end of the clustered index prevent page
split, how can it be? Isn't that when the page becomes full, the page might
still split if the b-tree node cannot accomodate further? Or are you
comparing between insertion in the middle of the page? Is the difference in
page split potential very big?
Greg, if the passport number is same throughtout the db lifespan, and
incrementing, then it would be in the same sequence as IDENTITY. HOw bout if
it is always same, but not incrementing (meaning it might be added in the
middle)? What is the impact compare to sequential insertion? I think
something like my question to Brian.
Nope, I am not looking at int and bigint comparison. I agree that disk cost
is cheap, but I have reservation that since bigint is larger, it may result
in less rows being packed in a single page, thus more pages, page split :P
especially in the case where each row size is very small (the extra four
bytes would be insignicant if the row size is large, right?) I have a
question here on the int and bigint cpu performance. Is it because our
current computer is 32bit, so it performs better with four bytes int? Then,
next time when most of the computing systems move to 64bit machine, would
bigint be a better choice?
thanks, thanks a great lot
Eugene|||On Sun, 7 Aug 2005 01:29:01 -0700, Eugene wrote:

>Hi All, thanks for the kind reply.
>I am not looking at a particular project now, I want to know the
>technical/academic knowledge/experience on this consideration.
>Brian, you mentioned, adding to the end of the clustered index prevent page
>split, how can it be? Isn't that when the page becomes full, the page might
>still split if the b-tree node cannot accomodate further?
Hi Eugene,
With an insertion at the end of the page, there won't be a page split.
If the page is full, a new page is simply opened, and the new row is
inserted as the first row on the new page. The new page will of course
also have to be inserted in the next higher level of the B-tree index,
but with the insertion at the end of the table, that new index entry
will also be at the end of the page.

> Or are you
>comparing between insertion in the middle of the page? Is the difference in
>page split potential very big?
That is where the difference occurs. If a row needs to be inserted in
the middle of a page that is already full, half of the rows need to be
moved to a new page to make place. That takes more time than simply
opening a new page for the new row.
Run the following example to see the difference between clustering on an
increasing value or clustering on a random value (be sure to run it a
few times, to exclude the influence of other processes running on your
computer).
CREATE TABLE Test (Col1 int NOT NULL PRIMARY KEY NONCLUSTERED IDENTITY,
Col2 int NOT NULL UNIQUE CLUSTERED,
OtherData char(40) NOT NULL)
go
-- Use same seed to start with
SELECT RAND(123)
go
DECLARE @.Start datetime, @.End datetime, @.Done char(1)
SET @.Done = 'N'
SET @.Start = CURRENT_TIMESTAMP
WHILE @.Done = 'N'
BEGIN
INSERT INTO Test (Col2, OtherData)
SELECT RAND() * 2000000000, ''
IF SCOPE_IDENTITY() >= 10000
SET @.Done = 'Y'
END
SET @.End = CURRENT_TIMESTAMP
SELECT @.Start AS Started, @.End AS Finished, DATEDIFF(ms, @.Start, @.End)
AS Elapsed
go
sp_spaceused 'Test', 'TRUE'
go
DROP TABLE Test
go
CREATE TABLE Test (Col1 int NOT NULL PRIMARY KEY CLUSTERED IDENTITY,
Col2 int NOT NULL UNIQUE NONCLUSTERED,
OtherData char(2) NOT NULL)
go
-- Use same seed to start with
SELECT RAND(123)
go
DECLARE @.Start datetime, @.End datetime, @.Done char(1)
SET @.Done = 'N'
SET @.Start = CURRENT_TIMESTAMP
WHILE @.Done = 'N'
BEGIN
INSERT INTO Test (Col2, OtherData)
SELECT RAND() * 2000000000, ''
IF SCOPE_IDENTITY() >= 10000
SET @.Done = 'Y'
END
SET @.End = CURRENT_TIMESTAMP
SELECT @.Start AS Started, @.End AS Finished, DATEDIFF(ms, @.Start, @.End)
AS Elapsed
go
sp_spaceused 'Test', 'TRUE'
go
DROP TABLE Test
go

>Nope, I am not looking at int and bigint comparison. I agree that disk cost
>is cheap, but I have reservation that since bigint is larger, it may result
>in less rows being packed in a single page, thus more pages, page split :P
>especially in the case where each row size is very small (the extra four
>bytes would be insignicant if the row size is large, right?)
Yes, on big rows, the 4 extra bytes won't make much difference (unless
they are just the few bytes that make the difference between three rows
per page or two rows per page, of course).
But don't forget the indexes. The clustered index key is included in
every nonclustered index as well. Indexes are usually small, so the 4
extra bytes do make a difference there.
If you change the code above to use bigint instead of int, you'll see
that the time to insert all rows goes up a bit. But the real difference
will be in retrieving data - especially on queries that might use an
index scan on the nonclustered index.

> I have a
>question here on the int and bigint cpu performance. Is it because our
>current computer is 32bit, so it performs better with four bytes int? Then,
>next time when most of the computing systems move to 64bit machine, would
>bigint be a better choice?
I doubt if that is a factor of importance. It is my experience that
performance is governed by physical I/O first, logical I/O second. The
amount of work the processor has to do is usually irrelevant - the CPU
will probably still spend most of it's time waiting for the next page to
be read from disk.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Eugene,
It doesn't prevent page splits, it minimizes them. In a clustered index,
the rows of the table live in the leaf pages, which are all at the same
level in the tree. Rows are added to the leaf pages at the end, in order,
so insertions don't cause page splits for leaf pages. With an integer key,
each index page can store 539 index rows, so splits of index pages are also
minimized. If only inserts occur, then I believe that page splits do not
occur at all, because the tree height is the same and optimal regardless of
whether the rightmost node at each level has 1 key (except the root which
has 2 keys, of course) and all nodes to the left are full, or if each node
at each level has the same number of keys. Index pages are therefore
inserted at the end of each level and a new root page is created when the
index becomes full. I'm pretty sure that's what happens, but it's possible
that the rightmost node at each level splits when inserting into a full
index. Even so, the number of splits is still minimized, because inserts do
not occur in the middle of the structure.
"Eugene" <Eugene@.discussions.microsoft.com> wrote in message
news:1B76D478-166D-4A60-AA49-0A38988AD97E@.microsoft.com...
> Hi All, thanks for the kind reply.
> I am not looking at a particular project now, I want to know the
> technical/academic knowledge/experience on this consideration.
> Brian, you mentioned, adding to the end of the clustered index prevent
page
> split, how can it be? Isn't that when the page becomes full, the page
might
> still split if the b-tree node cannot accomodate further? Or are you
> comparing between insertion in the middle of the page? Is the difference
in
> page split potential very big?
> Greg, if the passport number is same throughtout the db lifespan, and
> incrementing, then it would be in the same sequence as IDENTITY. HOw bout
if
> it is always same, but not incrementing (meaning it might be added in the
> middle)? What is the impact compare to sequential insertion? I think
> something like my question to Brian.
> Nope, I am not looking at int and bigint comparison. I agree that disk
cost
> is cheap, but I have reservation that since bigint is larger, it may
result
> in less rows being packed in a single page, thus more pages, page split :P
> especially in the case where each row size is very small (the extra four
> bytes would be insignicant if the row size is large, right?) I have a
> question here on the int and bigint cpu performance. Is it because our
> current computer is 32bit, so it performs better with four bytes int?
Then,
> next time when most of the computing systems move to 64bit machine, would
> bigint be a better choice?
> thanks, thanks a great lot
> Eugene|||A hotspot at the end of a table does exist, but the impact of this is
generally minimal, especially if your RAID controllers can cache writes as
well as reads. (RAID controllers with a battery can implement a write-back
cache as opposed to a write-through cache, which means that the data to be
written remain in RAM and are only periodically flushed out to disk.) I'm
not really convinced that a "hotspot" affects overall performance anyway,
because having writes spread throughout a table can require the head to move
more often (disk ss), which has a much more detrimental impact on
performance.
"Brian Selzer" <brian@.selzer-software.com> wrote in message
news:O4##Ya1mFHA.2444@.tk2msftngp13.phx.gbl...
> Eugene,
> It doesn't prevent page splits, it minimizes them. In a clustered index,
> the rows of the table live in the leaf pages, which are all at the same
> level in the tree. Rows are added to the leaf pages at the end, in order,
> so insertions don't cause page splits for leaf pages. With an integer
key,
> each index page can store 539 index rows, so splits of index pages are
also
> minimized. If only inserts occur, then I believe that page splits do not
> occur at all, because the tree height is the same and optimal regardless
of
> whether the rightmost node at each level has 1 key (except the root which
> has 2 keys, of course) and all nodes to the left are full, or if each node
> at each level has the same number of keys. Index pages are therefore
> inserted at the end of each level and a new root page is created when the
> index becomes full. I'm pretty sure that's what happens, but it's
possible
> that the rightmost node at each level splits when inserting into a full
> index. Even so, the number of splits is still minimized, because inserts
do
> not occur in the middle of the structure.
>
> "Eugene" <Eugene@.discussions.microsoft.com> wrote in message
> news:1B76D478-166D-4A60-AA49-0A38988AD97E@.microsoft.com...
> page
> might
> in
bout
> if
the
> cost
> result
:P
> Then,
would
>|||On Sat, 6 Aug 2005 11:59:07 -0700, Eugene
<Eugene@.discussions.microsoft.com> wrote:
>Hi, what opinion do you people have with clustered index on IDENTITY column
?
>Is there more advantage or divantage?
It's very fashionable, whatever the theoretical or practical
arguments.

>Wouldn't it causes all insertion to be added to the same page. Is this 'hot
>page' a big issue? Can it helps to prevent fragmentation? How is it so?
Other posters have addressed this.

>Secondly, is adding IDENTITY column itself recommended? Is it because by
>plainly using the logical unique (eg. Passport Number that is assumed to be
>unique) would be slower, maybe because it is not integer; or maybe composit
e
>primary key? Is a IDENTITY primary key useful because of its use as a forei
gn
>in related table?
Just a note that if you have a typically modest application of a few
dozen users on a typically modest database of say under 1gb running on
a typically powerful server of 2 processors, 3ghz, 4gb RAM, RAID5,
mirrored logs, ... then you're about 1000% overpowered and few of
these concerns will ever become visible, assuming your data model and
application are put together at all competently.
Heck, half the apps I see anymore turn out to me missing PKs or other
major indices, accidentally dropped (or duplicated!) during
maintenance over the months or years, and nobody notices for months or
years except for a few grumbles, and then the first thing they usually
do is upgrade the hardware, not audit the system!
J.|||I just googled "a little Dr. Codd" and found nothing... same with BOL.
har har har
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1123458943.236091.255690@.g43g2000cwa.googlegroups.com...
> plainly using the logical unique (eg. Passport Number that is assumed
> to be
> unique) would be slower, maybe because it is not integer; or maybe
> composite
> primary key? <<
> Do not use IDENTITY or other proprietary exposed physical locators in
> an RDBMS. Read a little Dr. Codd for the details of what surrogate is.
> Also look up validation and verification as properties for all data
> elements. Then look up the design of navigational databases which
> newbies mimic with IDENTITY. In fact, making IDENTITY the key would
> allow duplicate passport numbers to go undetected
> 2) Most of the time you do not wish to waste your one clustered index
> on a unique column. The Sybase/SQL Server implementation is based on a
> file system. It uses physically contigous storage and tree indexes.
> This means that you get one and only one clustered index per table. If
> you do a lot of GROUP BY's on one set of columns (totals by city, state
> or whatever), then a table scan on that sorted ordering will be much
> faster than random access. This is where you gain performance.
> 3) The "hot page" is not as big an issue as it has been in earlier
> releases. Data quality and integrity is a much, much bigger issue. If
> you have an (n) column nautral key in your data model, you **must**
> enforce it and IDENTITY will not change this fact of life.
> We live in an age of 64-bit hardware, parallel processors and all that
> jazz. Tuning an RDBMS at the byte-level is a waste of time and
> resources. The right answer is to remove redundancy instead. Do the
> math -- how much time do you need to read a byte off of a hard drive?
> What is the speed of main storage? So even if I need 100 times more
> processor time to do a join, I am ahead of ther game.
>

Clustered Index on Date Field or Identity Field ....

Hi All,
I have a table (detail table) with fields ID (Identity) primary Key and a DT
TM datetime field which is a heap.
Right now there is a non clustered index on ID which is used to join with it
s master table.
I have many reports which uses this table and for all the reports the basic
criteria is between DTTM.
say I run the report for say for a date range of 1 month, 1 w or so.
Im planning to add a clustered index on DTTM field so that the reports would
become faster compared to a table scan what its doing now.
My question is, is it a good idea to create a clustered index on a Datetime
field?
or is it a better way to make ID the clustered index and then create a non c
lustered index on DTTM?
But I always had the doubt that, what is the purpose of creating a clustered
index on an identity field that too which is already a primary key,
since an identity field is already ordered. Does it make sense to create a c
lustered in index on Identity field.
Add to this most of my Stored procedures which are used to retrieve uses ID
to join with its master table.
DTTM would be used only in reports...
Thanks,
PradPradeep Kutty wrote:
> Hi All,
> I have a table (detail table) with fields ID (Identity) primary Key and
> a DTTM datetime field which is a heap.
> Right now there is a non clustered index on ID which is used to join
> with its master table.
> I have many reports which uses this table and for all the reports the
> basic criteria is between DTTM.
> say I run the report for say for a date range of 1 month, 1 w or so.
> Im planning to add a clustered index on DTTM field so that the reports
> would become faster compared to a table scan what its doing now.
> My question is, is it a good idea to create a clustered index on a
> Datetime field?
> or is it a better way to make ID the clustered index and then create a
> non clustered index on DTTM?
> But I always had the doubt that, what is the purpose of creating a
> clustered index on an identity field that too which is already a primary
> key,
> since an identity field is already ordered. Does it make sense to create
> a clustered in index on Identity field.
> Add to this most of my Stored procedures which are used to retrieve uses
> ID to join with its master table.
> DTTM would be used only in reports...
> Thanks,
> Prad
>
it seems that a clustered index is better. try both ways and look at the
execution plan(s)|||Pradeep,

>Im planning to add a clustered index on DTTM field so that the reports would become
faster compared to a table scan what its doing now.
Thats a good idea because clustered index is ideal for range search.

>But I always had the doubt that, what is the purpose of creating a clustere
d index on an identity field that too which is already a primary key,
>since an identity field is already ordered. Does it make sense to create a clustere
d in index on Identity field.
One advantage of having a clustered index on the IDENTITY column is that it
will help you avoid page split problems.
But your assumption about the order of IDENTITY value is wrong. IDENTITY onl
oy provides a logical sequence, whereas a clustered index
controls the order in which the rows are physically stored.
--
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"Pradeep Kutty" <pradeepk@.healthasyst.com> wrote in message news:%23EVpg8TrF
HA.716@.TK2MSFTNGP10.phx.gbl...
Hi All,
I have a table (detail table) with fields ID (Identity) primary Key and a DT
TM datetime field which is a heap.
Right now there is a non clustered index on ID which is used to join with it
s master table.
I have many reports which uses this table and for all the reports the basic
criteria is between DTTM.
say I run the report for say for a date range of 1 month, 1 w or so.
Im planning to add a clustered index on DTTM field so that the reports would
become faster compared to a table scan what its doing now.
My question is, is it a good idea to create a clustered index on a Datetime
field?
or is it a better way to make ID the clustered index and then create a non c
lustered index on DTTM?
But I always had the doubt that, what is the purpose of creating a clustered
index on an identity field that too which is already a primary key,
since an identity field is already ordered. Does it make sense to create a c
lustered in index on Identity field.
Add to this most of my Stored procedures which are used to retrieve uses ID
to join with its master table.
DTTM would be used only in reports...
Thanks,
Prad|||One note of caution (playing devil's advocate here).
I don't know how many people you have updating your table or the hardware yo
u use but...
...one problem with clustered indexes based on the ID is that all WRITES mu
st occur on the same place on the disk, or on the same disk if you're using
an array of disks...everyone's writing data to a new row that goes in after
the last row.
If you have a huge number of updates occurring (which you probably don't) th
en this can cause a problem as you effectively get a "hot spot" on the disk
where everyone is attempting to write to the same part of the disk. Compare
this to a clustered index on (say) the surname, where new rows are added to
different parts of the disk (or on different disks in an array of disks).
Griff
"Pradeep Kutty" <pradeepk@.healthasyst.com> wrote in message news:%23EVpg8TrF
HA.716@.TK2MSFTNGP10.phx.gbl...
Hi All,
I have a table (detail table) with fields ID (Identity) primary Key and a DT
TM datetime field which is a heap.
Right now there is a non clustered index on ID which is used to join with it
s master table.
I have many reports which uses this table and for all the reports the basic
criteria is between DTTM.
say I run the report for say for a date range of 1 month, 1 w or so.
Im planning to add a clustered index on DTTM field so that the reports would
become faster compared to a table scan what its doing now.
My question is, is it a good idea to create a clustered index on a Datetime
field?
or is it a better way to make ID the clustered index and then create a non c
lustered index on DTTM?
But I always had the doubt that, what is the purpose of creating a clustered
index on an identity field that too which is already a primary key,
since an identity field is already ordered. Does it make sense to create a c
lustered in index on Identity field.
Add to this most of my Stored procedures which are used to retrieve uses ID
to join with its master table.
DTTM would be used only in reports...
Thanks,
Prad|||If it is used in joins, then I would put the clustered index on the IDENTITY
column. This can speed up inserts into this table, inserts into related ta
bles, and joins between this table and related tables. If the order of the
IDENTITY increment matches the order of the clustered index on the IDENTITY
column, then all inserts will occur at the end of the table, which minimizes
the required index maintenance operations.
If a table has a clustered index, then all nonclustered indexes use the clus
tered index key to locate rows in the table. If you put a nonclustered inde
x on the primary key, then every join will result in an additional step in t
he execution plan--a bookmark lookup. This extra level of indirection can s
ignificantly reduce the performance of every join. In addition, if you use
a clustered index on a datetime column, and the datetime column is not a can
didate key, then SQL Server will add a 4-byte uniqifier to every index row s
o that the index key can be used in nonclustered indexes to locate rows. Th
is increases the size of each nonclustered index, and can further reduce que
ry performance, especially with respect to joins.
To boost performance for reporting, you have other options aside from simply
adding an index. Here are a couple: (1) use a covering index so that the b
ookmark lookup will not be necessary, or (2) create an indexed view, and use
both the datetime and the identity column (in that order) as the clustered
index key for the view. If all of the columns necessary for the query exist
in the index key, then there is no need for SQL Server to access the actual
data row, so the performance degradation resulting from the use a noncluste
red index will be minimized. If that doesn't provide adequate reporting per
formance, the indexed view option will at least meet the select performance
of accessing a table with a clustered index directly, without degrading the
performance of the joins. It should be noted, however, that insert performa
nce will be degraded by the addition of any index or indexed view.
"Pradeep Kutty" <pradeepk@.healthasyst.com> wrote in message news:#EVpg8TrFHA
.716@.TK2MSFTNGP10.phx.gbl...
Hi All,
I have a table (detail table) with fields ID (Identity) primary Key and a DT
TM datetime field which is a heap.
Right now there is a non clustered index on ID which is used to join with it
s master table.
I have many reports which uses this table and for all the reports the basic
criteria is between DTTM.
say I run the report for say for a date range of 1 month, 1 w or so.
Im planning to add a clustered index on DTTM field so that the reports would
become faster compared to a table scan what its doing now.
My question is, is it a good idea to create a clustered index on a Datetime
field?
or is it a better way to make ID the clustered index and then create a non c
lustered index on DTTM?
But I always had the doubt that, what is the purpose of creating a clustered
index on an identity field that too which is already a primary key,
since an identity field is already ordered. Does it make sense to create a c
lustered in index on Identity field.
Add to this most of my Stored procedures which are used to retrieve uses ID
to join with its master table.
DTTM would be used only in reports...
Thanks,
Prad|||Pradeep Kutty wrote:
> Hi All,
> I have a table (detail table) with fields ID (Identity) primary Key
> and a DTTM datetime field which is a heap.
> Right now there is a non clustered index on ID which is used to join
> with its master table.
> I have many reports which uses this table and for all the reports the
> basic criteria is between DTTM.
> say I run the report for say for a date range of 1 month, 1 w or
> so.
> Im planning to add a clustered index on DTTM field so that the
> reports would become faster compared to a table scan what its doing
> now.
> My question is, is it a good idea to create a clustered index on a
> Datetime field?
> or is it a better way to make ID the clustered index and then create
> a non clustered index on DTTM?
> But I always had the doubt that, what is the purpose of creating a
> clustered index on an identity field that too which is already a
> primary key,
> since an identity field is already ordered. Does it make sense to
> create a clustered in index on Identity field.
> Add to this most of my Stored procedures which are used to retrieve
> uses ID to join with its master table.
> DTTM would be used only in reports...
Either I overlooked it or nobody actually mentioned a composite index. If
you always do queries that join by your PK and use only a date range then
a composite clustered index on (timestamp, ID) might also be worth
considering. Or am I missing something here?
Kind regards
robert

clustered index on a identity field

Hi All,
I have heard that if you create a clustered index on a identity field and
its a PK, it reduces the page splits...
But how?
Thanks,
Pradif you mean that the number of pages required to define a table will
grow more slowly, that is correct. but there are many reasons not to do
this. in my experience, clustering any primary key is a pretty bad
idea, especially an identity column. the reasons are twofold:
1) clustering ensures that all like values will be ordered together. if
you apply this to a value that is gauranteed to be unique, all you are
accomplishing is gauranteeing the physical order of the rows on disk,
which would serve no real purpose (i.e. none of your apps should care
what order the rows are on disk).
in my opinion, you should reserve a clustered index for a column that
has non-unique values, and is often used as a search criteria by value.
that way, all of the rows with the same value are physically grouped
together, greatly improving the performance of the disk read to collect
all the data.
2) creating a clustered index on an identity column is very likely to
create insert hotspots, because it is gauranteed that almost every new
row will be written to the same datapage. if you have a large number of
inserts occurring, this can create lock issues, if the current
insertion page is locked for any reason from insertion. it's far
healthier if your database can insert rows into a nicely dispersed set
of data pages, to prevent issues of lock escalation impeding inserts.
anyway, those are just my two cents. i'm sure other people have other
opinions.|||See if this helps:
Tips on Optimizing SQL Server Clustered Indexes
http://www.sql-server-performance.c...red_indexes.asp
AMB
"Pradeep Kutty" wrote:

> Hi All,
> I have heard that if you create a clustered index on a identity field and
> its a PK, it reduces the page splits...
> But how?
> Thanks,
> Prad
>
>|||Can you fix your clock?
"Pradeep Kutty" <pradeepk@.healthasyst.com> wrote in message
news:eCh33%23HtFHA.616@.TK2MSFTNGP11.phx.gbl...
> Hi All,
> I have heard that if you create a clustered index on a identity field and
> its a PK, it reduces the page splits...
> But how?
> Thanks,
> Prad
>|||I disagree.
The physical location on disk of index rows in a clustered index is
undefined. There is no guarantee that the index pages of a clustered index
reside in any specified order on the physical hardware. What is defined is
that the rows within a specific leaf page are in order, and each clustered
index leaf page contains next and previous pointers to locate the next and
previous leaf pages. One of the main benefits of using a clustered index is
that once the first row in an ordered scan is found, SQL Server doesn't need
to walk the b-tree to find the next row. It can use the next pointer to
continue the ordered scan. This can improve query performance considerably.
You should avoid using a non-unique key in a clustered index. If a table
has a clustered index, then every nonclustered index uses the clustered
index key to locate the row in the table. If the key of a clustered index
is not unique, then SQL Server adds a hidden 4-byte uniquifier to the key to
make it unique. The entire key along with the uniqifier is then stored in
every nonclustered index row.
In addition, there is a significant degradation of join performance. If you
put a nonclustered index on the primary key, then every join with related
tables requires an additional lookup to satisfy the query. For each row,
the index for the primary key constraint is consulted yielding the clustered
index key. That key is then used to lookup the row in the clustered index.
Therefore, if you have related tables, then the clustered index should live
on the primary key.
Your assertion about insert hotspots fails to mention the additional index
maintenance (page splits and updates) that is required if your clustered
index isn't on the IDENTITY column. If the clustered index is on an
IDENTITY column, then there will never be any page splits, because inserts
become appends, both in the b-tree index nodes and in the leaf nodes. You
can also use a 100% fillfactor, because there will never be any inserts,
only appends. The tree grows up: at each level of the b-tree, a new page is
appended if it is needed, and if a new page is needed at the root level,
then a new root page is also added. That's why inserts into a table with
only a clustered index on the IDENTITY column perform almost as well as
inserts into a table without any indexes at all. Deletes and updates in
this case require minimal index maintenance, because SQL Server doesn't join
index pages, it only splits them when an insert exceeds the fillfactor
threshold. Deletes just leave holes, so if you expect a lot of deletes,
then you should schedule a periodic DBCC INDEXDEFRAG or DBCC DBREINDEX.
SQL Server uses lazy spooling, which means that changes to the database are
cached and then periodically flushed to disk (checkpointed). (SQL Server
only ensures that writes to the transaction log are flushed to disk before
returning from a commit.) This means that more often than not, the last
page is still resident in memory if there are a large number of inserts,
thereby turning the insert hotspot into a performance improvement.
Finally, unless otherwise forced, the exclusive locks applied by an insert
should not escalate unless the page is full and all of the rows in the page
were just inserted by the current transaction. Therefore, there is little
if any lock contention due to inserts.
"jason" <iaesun@.yahoo.com> wrote in message
news:1126190553.831020.325290@.f14g2000cwb.googlegroups.com...
> if you mean that the number of pages required to define a table will
> grow more slowly, that is correct. but there are many reasons not to do
> this. in my experience, clustering any primary key is a pretty bad
> idea, especially an identity column. the reasons are twofold:
> 1) clustering ensures that all like values will be ordered together. if
> you apply this to a value that is gauranteed to be unique, all you are
> accomplishing is gauranteeing the physical order of the rows on disk,
> which would serve no real purpose (i.e. none of your apps should care
> what order the rows are on disk).
> in my opinion, you should reserve a clustered index for a column that
> has non-unique values, and is often used as a search criteria by value.
> that way, all of the rows with the same value are physically grouped
> together, greatly improving the performance of the disk read to collect
> all the data.
> 2) creating a clustered index on an identity column is very likely to
> create insert hotspots, because it is gauranteed that almost every new
> row will be written to the same datapage. if you have a large number of
> inserts occurring, this can create lock issues, if the current
> insertion page is locked for any reason from insertion. it's far
> healthier if your database can insert rows into a nicely dispersed set
> of data pages, to prevent issues of lock escalation impeding inserts.
> anyway, those are just my two cents. i'm sure other people have other
> opinions.
>|||jason wrote:
> if you mean that the number of pages required to define a table will
> grow more slowly, that is correct. but there are many reasons not to
> do this. in my experience, clustering any primary key is a pretty bad
> idea, especially an identity column. the reasons are twofold:
> 1) clustering ensures that all like values will be ordered together.
> if you apply this to a value that is gauranteed to be unique, all you
> are accomplishing is gauranteeing the physical order of the rows on
> disk, which would serve no real purpose (i.e. none of your apps
> should care what order the rows are on disk).
> in my opinion, you should reserve a clustered index for a column that
> has non-unique values, and is often used as a search criteria by
> value. that way, all of the rows with the same value are physically
> grouped together, greatly improving the performance of the disk read
> to collect all the data.
> 2) creating a clustered index on an identity column is very likely to
> create insert hotspots, because it is gauranteed that almost every new
> row will be written to the same datapage. if you have a large number
> of inserts occurring, this can create lock issues, if the current
> insertion page is locked for any reason from insertion. it's far
> healthier if your database can insert rows into a nicely dispersed set
> of data pages, to prevent issues of lock escalation impeding inserts.
> anyway, those are just my two cents. i'm sure other people have other
> opinions.
I would like to comment on some of what you wrote. Clustering on a
non-unique columns has a few implications.
- SQL Server cannot handle non-unique clustered keys internally and will
automatically add a UNIQUEIDENTIFIER to the rows when necessary. This
increases the key length by 4-bytes for each non-unique value. Using an
IDENTITY value, which is by definition unique, does not have this added
overhead.
- The clustered index key is the pointer into the actual data.
Therefore, the clustered index key is a part of each non-clustered index
key. This can make the non-clustered indexes grow considerably. Given
that using a non-unique set of columns is likely to include at least one
non-integer based column, you are looking at large keys all around. This
will slow down inserting and updating because of the need to hit each
non-clustered index. Clustered IDENTITY columns add the least amount of
overhead to non-clustered indexes.
- A non-unique clustered index key is likely to be more susceptible to
updating. Updating a clustered index key means physically moving the row
to a new location and this will likely cause page splitting, which is a
slow process that causes internal fragmentation. This will slow down
inserting and updating because of the need to locate/relocate the row
and the need to update each non-clustered index key. IDENTITY columns
cannot be updated and are immune to this problem.
- Insert hotspots aren't really a problem any longer like they were in
the old day of page locking. SQL Server uses row locking for inserts.
Keeping the disk heads from moving around can actually increase
performance because of the slow nature of the hard drive. Using an
IDENTITY keeps the disk heads in the same location and prevents page
splitting as new rows are added.
- A non-unique clustered index may require additional maintenance to
avoid page splitting. That is, you may have to implement index rebuilds
using a FILLFACTOR that leaves a certain amount of space available for
inserts on each page. The maintenance is ongoing and is best done during
off-hours; assuming your business has off-hours. This increases the
table size and number of pages and will hurt overall performance.
- Page splitting causes external fragmentation. That is, even if you use
a clustered index on a set of non-unique columns, there's no reason to
assume that pages that contain the same key will be contiguous. So a
read of two pages of a specific clustered index key can cause the disk
heads to move from one end of the table to another. This can be avoided
with some index maintenance. Clustered IDENTITY indexes do not have this
problem.
Having said that, there are reasons to use non-unique clustered indexes.
As you stated, they can, in fact, help the fetching of a number of rows
of a specific key. If your database is used primarily for this type of
operation, a non-unique clustered index may be the way to go. I've
certainly used them this way on many occasions.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||interesting, you clearly have some well informed opinions. they just
happen to disagree with some of the sources i've used to come to my
understanding :) these things happen. i admit, my understanding of
clustered indexing, and indexing in general, is incomplete, but here
are some points that i thought i'd share, to see if perhaps there's an
absolute answer:
* from every text at my disposal, the primary advantage to a clustered
index is the fact that the data is guaranteed to be in the same order
as the index.
* the leaf nodes of a clustered index don't actually point to the next
and previous rows, because they don't have to. the next and previous
values are implied by the next and previous positions on disk.
* nonclustered indexes do point to next and previous data rows, because
the index order may not match the physical order, so a pointer is
required
* the greatest advantages to using clustered indexes, therefore, are
cases where the physical order of the data can increase your
performance. chief among these are (1) columns commonly used in group
by and order by clauses (2) columns on the many side of a one-to-many
relationship. because there is a tremendous increase to the performance
of the reads for these operations (3) columns whose values are
frequently evaluated between a range
* by the same reasoning, clustered indexes are wasted on unique value
columns, as they serve no tangible purpose (except as you mention,
perhaps ameliorating problems using identity columns without a
clustered index)
* using a clustered index on a unique value is wasted. the only reason
is the default for new tables created in enterprise manager is because
it grants advantages to some queries above a table that has zero
clustered indexes (better one than none)
here are my sources for this information:
microsoft sql server 2000 bible, paul nielsen, pp768-771
sql server 2000 for experienced dba's, brian knight, pp252-255
http://msdn.microsoft.com/library/d...ql7perftune.asp
http://msdn.microsoft.com/library/d...br />
apr3.asp
these sources cite specific examples of where and why you would want to
use clustered indexes, and they are all on data that is grouped by,
sorted by, or evaluated in a range. especially date fields (which are
rarely unique, yes?)
one caveat that is mentioned is that you shouldn't apply a clustered
index to a column that is updated often, for the reason you stated,
that the clustered index is appended to every nonclustered index in the
table. but you cite this as a performance hindrance? my undrestanding
is that the presence of the clustered index is actually increasing
performance (if at a small cost of space) by skipping the pointer to
data page step of a lookup.
what are your sources, that we might compare / contrast?
jason|||"jason" <iaesun@.yahoo.com> wrote in message
news:1126206276.049357.153630@.g43g2000cwa.googlegroups.com...
> interesting, you clearly have some well informed opinions. they just
> happen to disagree with some of the sources i've used to come to my
> understanding :) these things happen. i admit, my understanding of
> clustered indexing, and indexing in general, is incomplete, but here
> are some points that i thought i'd share, to see if perhaps there's an
> absolute answer:
> * from every text at my disposal, the primary advantage to a clustered
> index is the fact that the data is guaranteed to be in the same order
> as the index.
>
And the clustered index requires one fewer IO per s since the leaf is the
data page; plus s performance of all non-clustered indexes requires 1-4
extra IO's.

> * the leaf nodes of a clustered index don't actually point to the next
> and previous rows, because they don't have to. the next and previous
> values are implied by the next and previous positions on disk.
>
No. The leaf nodes are data pages. All data pages in a table are arranged
in a doubly linked list: each page has a next page and previous page
pointer. A table scan traverses the table by following the next/previous
pointers from page to page. For a table with a clustered index the order of
rows on a page and the order of pages determined by the next/previous
pointers follow the order of the index. So after you traverse the index
down to the leaf level once you can then follow the next/previous pointers
to follow the index order. So you don't have to go back to the index pages
to satisfy a range query: just s to the first value and the scan to the
end.
A common misconception is that data pages in a clustered index are
guaranteed to be physically ordered. They are not. Only the logical order
of the doubly-linked list of data pages is guaranteed. Difference between
the logical ordering of the pages and the physical order can arise over time
due to index fragmentation.

> * nonclustered indexes do point to next and previous data rows, because
> the index order may not match the physical order, so a pointer is
> required
> * the greatest advantages to using clustered indexes, therefore, are
> cases where the physical order of the data can increase your
> performance. chief among these are (1) columns commonly used in group
> by and order by clauses (2) columns on the many side of a one-to-many
> relationship. because there is a tremendous increase to the performance
> of the reads for these operations (3) columns whose values are
> frequently evaluated between a range
> * by the same reasoning, clustered indexes are wasted on unique value
> columns, as they serve no tangible purpose (except as you mention,
> perhaps ameliorating problems using identity columns without a
> clustered index)
A row in a table with a clustered index can only be accessed through the
clustered index. There's simply no other way to find the row. If you have
a non-clustered unique index, and a non-unique clustered index then
accessing the table through the unique index will be more expensive since
you must traverse the unique index only to get, at the leaf level, a
non-unique clustered index key (plus 4-byte uniqifier), and then you must
traverse the non-unique index down to the leaf level (data page) on which
the row is located. There are situation where this is a net improvement in
performance, but for applications which do primarily single-row
insert/update/delete clustering the primary key optimizes the most important
transaction.

> * using a clustered index on a unique value is wasted. the only reason
> is the default for new tables created in enterprise manager is because
> it grants advantages to some queries above a table that has zero
> clustered indexes (better one than none)
This is not generally the case since choosing the clustered index always
optimizes some access paths and degrades others. You must analyze your
workload and measure the impace of clustering a non-unique tuple.
There are no valid sources for recieved wisdom or rules of thumb for this.
Only knoledge of how SQL Server is implemented and testing.
David|||Perhaps you should bin those books and open up BOL.
SQL Server Architecture/Database Architecture/Physical Database
Architecture/Table and Index Architecture
This section contains information about the physical structure of clustered
indexes, nonclustered indexes, and heaps (tables without clustered indexes).
Creating and Maintaining Databases/Indexes
This section contains information about when to use clustered indexes, why
you should keep the key small, etc.
"jason" <iaesun@.yahoo.com> wrote in message
news:1126206276.049357.153630@.g43g2000cwa.googlegroups.com...
> interesting, you clearly have some well informed opinions. they just
> happen to disagree with some of the sources i've used to come to my
> understanding :) these things happen. i admit, my understanding of
> clustered indexing, and indexing in general, is incomplete, but here
> are some points that i thought i'd share, to see if perhaps there's an
> absolute answer:
> * from every text at my disposal, the primary advantage to a clustered
> index is the fact that the data is guaranteed to be in the same order
> as the index.
> * the leaf nodes of a clustered index don't actually point to the next
> and previous rows, because they don't have to. the next and previous
> values are implied by the next and previous positions on disk.
> * nonclustered indexes do point to next and previous data rows, because
> the index order may not match the physical order, so a pointer is
> required
> * the greatest advantages to using clustered indexes, therefore, are
> cases where the physical order of the data can increase your
> performance. chief among these are (1) columns commonly used in group
> by and order by clauses (2) columns on the many side of a one-to-many
> relationship. because there is a tremendous increase to the performance
> of the reads for these operations (3) columns whose values are
> frequently evaluated between a range
> * by the same reasoning, clustered indexes are wasted on unique value
> columns, as they serve no tangible purpose (except as you mention,
> perhaps ameliorating problems using identity columns without a
> clustered index)
> * using a clustered index on a unique value is wasted. the only reason
> is the default for new tables created in enterprise manager is because
> it grants advantages to some queries above a table that has zero
> clustered indexes (better one than none)
> here are my sources for this information:
> microsoft sql server 2000 bible, paul nielsen, pp768-771
> sql server 2000 for experienced dba's, brian knight, pp252-255
>
http://msdn.microsoft.com/library/d...une.as
p
>
http://msdn.microsoft.com/library/d...l/sql7sapr3.asp[
color=darkred]
> these sources cite specific examples of where and why you would want to
> use clustered indexes, and they are all on data that is grouped by,
> sorted by, or evaluated in a range. especially date fields (which are
> rarely unique, yes?)
> one caveat that is mentioned is that you shouldn't apply a clustered
> index to a column that is updated often, for the reason you stated,
> that the clustered index is appended to every nonclustered index in the
> table. but you cite this as a performance hindrance? my undrestanding
> is that the presence of the clustered index is actually increasing
> performance (if at a small cost of space) by skipping the pointer to
> data page step of a lookup.
> what are your sources, that we might compare / contrast?
> jason
>[/color]|||> There are no valid sources for recieved wisdom or rules of thumb for this.
> Only knoledge of how SQL Server is implemented and testing.
I'm getting that impression, which is why discussions like these are
extremely useful. They can supplement experiences you haven't had yet,
and as you say, give a more complete picture of how SQL Server is
implemented. So thank you very much!
Jason