Ooverflow pages nonunique clustered indexes

What are the perforamnce hits for non clustered indexes?
Does SQL 2000 still use overflow pages are created for nonunique clustered
indexes?
--
Thanks,
Jon ASQL Server never used overflow pages...
Can you be a bit more specific in your question? NC indexes has a perf hit w
hen you modify data as
the data in the indexes affected by the modification need to be reflected in
the indexes as well.
Also, page splits can occur. Is that what you are referring to?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Jon A" <JonA@.discussions.microsoft.com> wrote in message
news:C53B3CE3-65A5-4F6A-B848-49D9F7B88F48@.microsoft.com...
> What are the perforamnce hits for non clustered indexes?
> Does SQL 2000 still use overflow pages are created for nonunique clustered
> indexes?
> --
> Thanks,
> Jon A|||If you create a nonunique clustered index does SQL add an identifier to the
key to unforce uniqueness. What type is that? Is it a uniqueidentifier?(16
bytes)
The table I have is unique by a key that would be 41 bytes long. So I was
going to make this the Primary Key as unique non clustered. But this table i
s
very large.
What performance hit do you get from creating a nonunique clustered index?
I cannot control fragmentation without a clustered index.
And if SQl Server adds an indentifier to the key I define, what is the type?
I know that the design is poor for this table but due to the vast amount of
programs that access it I cannot change the design. It has a large number of
inserts.
I am thinking of adding an identity colum and putting the clustered index on
that. And making the primary key a unique non clustered index on the 3
columns that total 41 bytes. Almost all data is update or selected or ordere
d
by these 3 columns.
I feel 41 bytes is to large for a clustered index.
"Tibor Karaszi" wrote:

> SQL Server never used overflow pages...
> Can you be a bit more specific in your question? NC indexes has a perf hit
when you modify data as
> the data in the indexes affected by the modification need to be reflected
in the indexes as well.
> Also, page splits can occur. Is that what you are referring to?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Jon A" <JonA@.discussions.microsoft.com> wrote in message
> news:C53B3CE3-65A5-4F6A-B848-49D9F7B88F48@.microsoft.com...
>
>|||> If you create a nonunique clustered index does SQL add an identifier to the">
> key to unforce uniqueness. What type is that? Is it a uniqueidentifier?(16
> bytes)
Yes, SQL Server add a 4 byte "uniqiefier".

> What performance hit do you get from creating a nonunique clustered index?
Can you qualify "performance hit"?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Jon A" <JonA@.discussions.microsoft.com> wrote in message
news:214C7867-4CFE-4B64-ABF4-78AC62D60BF4@.microsoft.com...[vbcol=seagreen]
> If you create a nonunique clustered index does SQL add an identifier to th
e
> key to unforce uniqueness. What type is that? Is it a uniqueidentifier?(16
> bytes)
> The table I have is unique by a key that would be 41 bytes long. So I was
> going to make this the Primary Key as unique non clustered. But this table
is
> very large.
> What performance hit do you get from creating a nonunique clustered index?
> I cannot control fragmentation without a clustered index.
> And if SQl Server adds an indentifier to the key I define, what is the typ
e?
> I know that the design is poor for this table but due to the vast amount o
f
> programs that access it I cannot change the design. It has a large number
of
> inserts.
> I am thinking of adding an identity colum and putting the clustered index
on
> that. And making the primary key a unique non clustered index on the 3
> columns that total 41 bytes. Almost all data is update or selected or orde
red
> by these 3 columns.
> I feel 41 bytes is to large for a clustered index.
> "Tibor Karaszi" wrote:
>|||I have 300,000 records there are unique by member(int),vin(20),stock(18).
there are 1200 unique members all updates are done by the primary key
(member,vin,stock). My customer does not want to add an identity column.
I am wondering which would be better
1. Put a unique Clustered index on the 40 byte fields
member(int),vin(20),stock(18)
or
2 Put a non Clustered index on member id (4 bytes)(let sql add the identifie
r)
and put the Primary Key on member(int),vin(20),stock(18) as a unique non
clustered index.
This table has heavy updates at night in batch.
There is currently no clustered index and there is no way to control
fragmentation.
"Tibor Karaszi" wrote:

> Yes, SQL Server add a 4 byte "uniqiefier".
>
> Can you qualify "performance hit"?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Jon A" <JonA@.discussions.microsoft.com> wrote in message
> news:214C7867-4CFE-4B64-ABF4-78AC62D60BF4@.microsoft.com...
>
>