Indexing non-unique data

I have two tables which are related. The first table(A) has a sequentially assigned unique key (primary) that has a cluster index built on it. This table has roughly 1,000,000 rows of data and grows daily.

The second table(B) has a sequentially assigned unique key (primary). There is a column in table(B) which contains table(A)'s unique key. For each row in the table(A) there are roughly 30 rows in table(B).

Should I build a clustered index on the table(B) column which contains the key to table(A) or a non-clustered index?You can have only one clustered index on a table, though you can have many non-clustered indexes. Since you may have many foreign keys in a table you can't make all these lookups clustered, so generally non-clustered indexes are applied to foreign keys.|||I have two tables which are related. The first table(A) has a sequentially assigned unique key (primary) that has a cluster index built on it. This table has roughly 1,000,000 rows of data and grows daily.

The second table(B) has a sequentially assigned unique key (primary). There is a column in table(B) which contains table(A)'s unique key. For each row in the table(A) there are roughly 30 rows in table(B).

Should I build a clustered index on the table(B) column which contains the key to table(A) or a non-clustered index?
I think custured index should do the trick,its my opinion...I feel clustered index are best for low selectiviy columns,i.e. the column which have many duplicates values. But see what the gurus suggest...|||You can have only one clustered index on a table, though you can have many non-clustered indexes. Since you may have many foreign keys in a table you can't make all these lookups clustered, so generally non-clustered indexes are applied to foreign keys.
But can't we make the foreign key column clustered index? I mean making the unique key not a clustered index...only a unique key column|||Hi Istaks

Welcome to the forum

Not a guru but some musings:

Well - a clustered index determines the physical order storage of data. So - it is useful if placed on an incrementing field as far as insertion of data is concerned as there will be no page splits based on insertion. It is also useful if you are likely to use >, < or between comparisons in a where condition on the clustered field.

The former is not the case. Inequality operators are rarely used on identities so Id go with no too :)|||So on table(B) create a clustered index on the unique key and a non-clustered index on the foreign key?

All selections from this table will be based on the foreign key.|||If ALL selects on this table will reference the foreign key and you will not be searching for individual records, then you would get a performance boost from using a clustered index on your foreign key.

Indexing and partitioning of the tables.

We have 6 lac row records which will be increasing with time. So for
optimized performance please provide solution related to partitioning
and indexing of tables.
Is partitioning necessary for optimization of performance, keeping in
mind , future growth of records.
Karan
karanlobana@.gmail.com wrote:
> We have 6 lac row records which will be increasing with time. So for
> optimized performance please provide solution related to partitioning
> and indexing of tables.
> Is partitioning necessary for optimization of performance, keeping in
> mind , future growth of records.
Disclaimer: haven't use partitioning in SQL 2005 myself - only on Oracle
instances.
It depends on the volume and the queries you do. If volume is rather high
and queries always include a specific narrow time range then it's likely
that your app will benefit from partitioning.
robert
|||Forgive me but I don't know what "lac" means but please note that
partitioning in 2005 is primarily for data management not performance. That
does not mean that you can not get increased performance from a proper
partitioning scheme but that is usually not the primary goal. Partitioning
allow you to manage very large amounts of data much more easily and
efficient than if they were all in a single table or partition.
Andrew J. Kelly SQL MVP
<karanlobana@.gmail.com> wrote in message
news:1137392219.125403.291600@.g14g2000cwa.googlegr oups.com...
> We have 6 lac row records which will be increasing with time. So for
> optimized performance please provide solution related to partitioning
> and indexing of tables.
> Is partitioning necessary for optimization of performance, keeping in
> mind , future growth of records.
> Karan
>
|||Andrew J. Kelly wrote:
> Forgive me but I don't know what "lac" means but please note that
> partitioning in 2005 is primarily for data management not
> performance. That does not mean that you can not get increased
> performance from a proper partitioning scheme but that is usually not
> the primary goal. Partitioning allow you to manage very large amounts
> of data much more easily and efficient than if they were all in a
> single table or partition.
Oracle's optimizer restricts access to only some partitions if he can
determine from the table, partitioning set up and indexing that this is
more efficient. Does the optimizer in SQL 2005 not do this? I browsed
BOL but couldn't find anything definitive on the matter.
Kind regards
robert
|||Absolutely. If the WHERE clause is such that it can determine based on the
partitioning scheme which partition(s) the data resides and the indexing is
aligned with the partitions it will only search those partitions. That is
and of itself does not guarantee better performance. It may or it may not.
It is just as fast to seek an individual row from a single partition out of
many as it is from one very large table, assuming it was properly indexed to
begin with. So it depends on exactly what they are doing and how.
Andrew J. Kelly SQL MVP
"Robert Klemme" <bob.news@.gmx.net> wrote in message
news:eAQ0kzqGGHA.1124@.TK2MSFTNGP10.phx.gbl...
> Andrew J. Kelly wrote:
> Oracle's optimizer restricts access to only some partitions if he can
> determine from the table, partitioning set up and indexing that this is
> more efficient. Does the optimizer in SQL 2005 not do this? I browsed
> BOL but couldn't find anything definitive on the matter.
> Kind regards
> robert
>
|||Andrew J. Kelly wrote:
> Absolutely. If the WHERE clause is such that it can determine based
> on the partitioning scheme which partition(s) the data resides and
> the indexing is aligned with the partitions it will only search those
> partitions. That is and of itself does not guarantee better
> performance. It may or it may not. It is just as fast to seek an
> individual row from a single partition out of many as it is from one
> very large table, assuming it was properly indexed to begin with. So
> it depends on exactly what they are doing and how.
Thanks for the explanation and verifying that my expectations are met! Of
course performance in the end still depends on what query is actually
executed.
Cheers
robert

Indexing and partitioning of the tables.

We have 6 lac row records which will be increasing with time. So for
optimized performance please provide solution related to partitioning
and indexing of tables.
Is partitioning necessary for optimization of performance, keeping in
mind , future growth of records.
Karankaranlobana@.gmail.com wrote:
> We have 6 lac row records which will be increasing with time. So for
> optimized performance please provide solution related to partitioning
> and indexing of tables.
> Is partitioning necessary for optimization of performance, keeping in
> mind , future growth of records.
Disclaimer: haven't use partitioning in SQL 2005 myself - only on Oracle
instances.
It depends on the volume and the queries you do. If volume is rather high
and queries always include a specific narrow time range then it's likely
that your app will benefit from partitioning.
robert|||Forgive me but I don't know what "lac" means but please note that
partitioning in 2005 is primarily for data management not performance. That
does not mean that you can not get increased performance from a proper
partitioning scheme but that is usually not the primary goal. Partitioning
allow you to manage very large amounts of data much more easily and
efficient than if they were all in a single table or partition.
Andrew J. Kelly SQL MVP
<karanlobana@.gmail.com> wrote in message
news:1137392219.125403.291600@.g14g2000cwa.googlegroups.com...
> We have 6 lac row records which will be increasing with time. So for
> optimized performance please provide solution related to partitioning
> and indexing of tables.
> Is partitioning necessary for optimization of performance, keeping in
> mind , future growth of records.
> Karan
>|||Andrew J. Kelly wrote:
> Forgive me but I don't know what "lac" means but please note that
> partitioning in 2005 is primarily for data management not
> performance. That does not mean that you can not get increased
> performance from a proper partitioning scheme but that is usually not
> the primary goal. Partitioning allow you to manage very large amounts
> of data much more easily and efficient than if they were all in a
> single table or partition.
Oracle's optimizer restricts access to only some partitions if he can
determine from the table, partitioning set up and indexing that this is
more efficient. Does the optimizer in SQL 2005 not do this? I browsed
BOL but couldn't find anything definitive on the matter.
Kind regards
robert|||Absolutely. If the WHERE clause is such that it can determine based on the
partitioning scheme which partition(s) the data resides and the indexing is
aligned with the partitions it will only search those partitions. That is
and of itself does not guarantee better performance. It may or it may not.
It is just as fast to seek an individual row from a single partition out of
many as it is from one very large table, assuming it was properly indexed to
begin with. So it depends on exactly what they are doing and how.
Andrew J. Kelly SQL MVP
"Robert Klemme" <bob.news@.gmx.net> wrote in message
news:eAQ0kzqGGHA.1124@.TK2MSFTNGP10.phx.gbl...
> Andrew J. Kelly wrote:
> Oracle's optimizer restricts access to only some partitions if he can
> determine from the table, partitioning set up and indexing that this is
> more efficient. Does the optimizer in SQL 2005 not do this? I browsed
> BOL but couldn't find anything definitive on the matter.
> Kind regards
> robert
>|||Andrew J. Kelly wrote:
> Absolutely. If the WHERE clause is such that it can determine based
> on the partitioning scheme which partition(s) the data resides and
> the indexing is aligned with the partitions it will only search those
> partitions. That is and of itself does not guarantee better
> performance. It may or it may not. It is just as fast to seek an
> individual row from a single partition out of many as it is from one
> very large table, assuming it was properly indexed to begin with. So
> it depends on exactly what they are doing and how.
Thanks for the explanation and verifying that my expectations are met! Of
course performance in the end still depends on what query is actually
executed.
Cheers
robert

Indexing and partitioning of the tables.

We have 6 lac row records which will be increasing with time. So for
optimized performance please provide solution related to partitioning
and indexing of tables.
Is partitioning necessary for optimization of performance, keeping in
mind , future growth of records.
Karankaranlobana@.gmail.com wrote:
> We have 6 lac row records which will be increasing with time. So for
> optimized performance please provide solution related to partitioning
> and indexing of tables.
> Is partitioning necessary for optimization of performance, keeping in
> mind , future growth of records.
Disclaimer: haven't use partitioning in SQL 2005 myself - only on Oracle
instances.
It depends on the volume and the queries you do. If volume is rather high
and queries always include a specific narrow time range then it's likely
that your app will benefit from partitioning.
robert|||Forgive me but I don't know what "lac" means but please note that
partitioning in 2005 is primarily for data management not performance. That
does not mean that you can not get increased performance from a proper
partitioning scheme but that is usually not the primary goal. Partitioning
allow you to manage very large amounts of data much more easily and
efficient than if they were all in a single table or partition.
--
Andrew J. Kelly SQL MVP
<karanlobana@.gmail.com> wrote in message
news:1137392219.125403.291600@.g14g2000cwa.googlegroups.com...
> We have 6 lac row records which will be increasing with time. So for
> optimized performance please provide solution related to partitioning
> and indexing of tables.
> Is partitioning necessary for optimization of performance, keeping in
> mind , future growth of records.
> Karan
>|||Andrew J. Kelly wrote:
> Forgive me but I don't know what "lac" means but please note that
> partitioning in 2005 is primarily for data management not
> performance. That does not mean that you can not get increased
> performance from a proper partitioning scheme but that is usually not
> the primary goal. Partitioning allow you to manage very large amounts
> of data much more easily and efficient than if they were all in a
> single table or partition.
Oracle's optimizer restricts access to only some partitions if he can
determine from the table, partitioning set up and indexing that this is
more efficient. Does the optimizer in SQL 2005 not do this? I browsed
BOL but couldn't find anything definitive on the matter.
Kind regards
robert|||Absolutely. If the WHERE clause is such that it can determine based on the
partitioning scheme which partition(s) the data resides and the indexing is
aligned with the partitions it will only search those partitions. That is
and of itself does not guarantee better performance. It may or it may not.
It is just as fast to seek an individual row from a single partition out of
many as it is from one very large table, assuming it was properly indexed to
begin with. So it depends on exactly what they are doing and how.
--
Andrew J. Kelly SQL MVP
"Robert Klemme" <bob.news@.gmx.net> wrote in message
news:eAQ0kzqGGHA.1124@.TK2MSFTNGP10.phx.gbl...
> Andrew J. Kelly wrote:
>> Forgive me but I don't know what "lac" means but please note that
>> partitioning in 2005 is primarily for data management not
>> performance. That does not mean that you can not get increased
>> performance from a proper partitioning scheme but that is usually not
>> the primary goal. Partitioning allow you to manage very large amounts
>> of data much more easily and efficient than if they were all in a
>> single table or partition.
> Oracle's optimizer restricts access to only some partitions if he can
> determine from the table, partitioning set up and indexing that this is
> more efficient. Does the optimizer in SQL 2005 not do this? I browsed
> BOL but couldn't find anything definitive on the matter.
> Kind regards
> robert
>|||Andrew J. Kelly wrote:
> Absolutely. If the WHERE clause is such that it can determine based
> on the partitioning scheme which partition(s) the data resides and
> the indexing is aligned with the partitions it will only search those
> partitions. That is and of itself does not guarantee better
> performance. It may or it may not. It is just as fast to seek an
> individual row from a single partition out of many as it is from one
> very large table, assuming it was properly indexed to begin with. So
> it depends on exactly what they are doing and how.
Thanks for the explanation and verifying that my expectations are met! Of
course performance in the end still depends on what query is actually
executed.
Cheers
robert

Indexing - Uniqueness vs Highly uplicate

Hi,

First of all my apologies if you have seen this mail already but I am
re-sending as there were some initial problems.

This query is related to defining indexes to be unique or not and
consequences thereof.

Some documented facts that I am aware of include

1. Defining uniqueness allows optimiser to create optimal plans eg
select based on keys in such an index allows the optimiser to determine
at most only one row will be returned

2. Defining uniqueness ensures that rule (business/Primary key) is
enforced, regradless of how the data is entered.

We have many cases where non unique indexes are defined. The approach to
date has been that even though we are aware of some of the benefits
offered by defining uniqueness , we have chosen not to add keys to non
unique indexes such that they become unique. The primary reason for this
was that we did not want to make the keys comprising the indexes
unnecessarily large and therefore ensuing consequences when DML
statements are performed.

However, I have concerns that having highly duplicate indexes can have
performance impacts , including deadlocking. I am also aware Sybase used
to store duplicate values in overflow pages and therefore there were
performance consequences. Could SQL 2000 have the same behaviour ?

Thanking you in advance

Puvendran

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!Puvendran Selvaratnam (puvendran.selvaratnam@.btfinancialgroup.com) writes:
> 1. Defining uniqueness allows optimiser to create optimal plans eg
> select based on keys in such an index allows the optimiser to determine
> at most only one row will be returned
> 2. Defining uniqueness ensures that rule (business/Primary key) is
> enforced, regradless of how the data is entered.

If you want to enforce uniqueness that comes from business rules, you
should use PRIMARY KEY and UNIQUE constraints. Not that it matters in
terms of performance; the constraints are just a different name for an
index. But because it gives you information about your database. Plus
that you can add a foreign-key constraint that refers to a PK or UNIQUE
constraint, but not a plain index.

> We have many cases where non unique indexes are defined. The approach to
> date has been that even though we are aware of some of the benefits
> offered by defining uniqueness , we have chosen not to add keys to non
> unique indexes such that they become unique. The primary reason for this
> was that we did not want to make the keys comprising the indexes
> unnecessarily large and therefore ensuing consequences when DML
> statements are performed.

This strategy is correct.

Note also, that in the end the index will always be unique in SQL Server.
For a non-unique clustered index, SQL Server adds a 32-bit "uniquifier" to
the index. For this reason, I tend to add the PK to a clustered index,
if it is 32 bits, not if is longer.

For a non-clustered index, the row locator is the key value of the
clustered index. (If there is no clustrered index, the row locator will
be a row id.)

Thus, the columns of the clustered index will always be there, at least
in the leaf level of the index. Note that this also means that a wide
cluster index, will also have an impact on the non-clustered index.

> However, I have concerns that having highly duplicate indexes can have
> performance impacts , including deadlocking. I am also aware Sybase used
> to store duplicate values in overflow pages and therefore there were
> performance consequences. Could SQL 2000 have the same behaviour ?

Yes, up do SQL 6.5, SQL Server had overflow pages for non-unique
clustered index, but as indicated above, this is no longer the case.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp