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]