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 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...
> 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 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 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 ordered
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...
> > 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)
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...
> 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 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 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 ordered
> 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...
>> > 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
>>|||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 identifier)
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:
> > 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...
> > 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 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 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 ordered
> > 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...
> >> > 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
> >>
> >>
> >>
>
>