Hi all,

I'm new to this, so please bear with me...

I have a table containing 700,000+ records but there is not a column that
will uniquely identify any idividual record in the table. In order to
increase the performance of the database, should I create a new column
called "ID" (like in MS Access) and insert an integer, say record number, to
make each record unique? If so, what would be the best way to accomplish
this? If this is not a good solution, would creating a composite index
solve the problem? And again, how can that be accomplished? Thank you so
much in advance.

Roger

Report this post to a moderator

Roger,

Assuming that your number one priority is "increase the performance of the
database", then creating an index (clustered index or nonclustered index) is
certainly a right step in the right direction. However, an index is only
helpful IF and only if the index is used.

For example, if you put a clustered index on a social security number field,
but then create queries like ...

select * from mytable where first_name = "Joe"

Well, then the index is of zero help. However, if all your queries used
social security number as one of the criteria, then the index **might** be
well placed.

Unfortunately, placing an index on the right column is a rather complicated
thing to explain in full detail, but essentially, it comes down to what
columns in your "typical" queries are used as criteria. There are tools
available in Query Analyzer that will allow you to determine if the index
you've built is helpful (See "Show Execution Plan"). However, as I alluded
to before, this isn't something that you will pick up in 15 minutes or less.
Also, creating a clustered index on multiple columns must be well thought
out as there are other negatives about creating large/wide indexes.

In Books Online, see "indexes, overview".

You also mentioned an interest in creating an Identity field to create a
unique column. This decision is certainly dependent on the other columns in
your table, and how you would use the column. Would you use the new
identity field in all your queries to identify different records? Or are
you doing it because you want to create primary key? Creating a primary key
isn't useful from the standpoint of performance UNLESS you are going to use
the primary key as criteria in all your queries ... like

Select col1, col2, col3 from mytable where my_primary_key = 35

Most importantly, it is an immediate red flag that you don't have a natural
primary key. Certainly, you would want to have a justifiable reason for not
having one ... it makes me question the quality of your data.

Hope this is helpful and gets you moving in the correct direction.

Mark
www.dovetaildatabases.com

"RYoh" <ryoh@adelphia.net> wrote in message
news:ecmsvrKpCHA.2412@TK2MSFTNGP12...
> Hi all,
>
> I'm new to this, so please bear with me...
>
> I have a table containing 700,000+ records but there is not a column that
> will uniquely identify any idividual record in the table. In order to
> increase the performance of the database, should I create a new column
> called "ID" (like in MS Access) and insert an integer, say record number,
to
> make each record unique? If so, what would be the best way to accomplish
> this? If this is not a good solution, would creating a composite index
> solve the problem? And again, how can that be accomplished? Thank you so
> much in advance.
>
> Roger
>
>

Report this post to a moderator

You can create a numeric column with an IDENTITY attribute that works
similar to the Autonumber data type in Access. You could also create
an unclustered index for this column. Creating a composite index may
also help you uniquely identify the record but there are times when
having a single unique column is advantageous.

On Sun, 15 Dec 2002 18:29:35 -0800, "RYoh" <ryoh@adelphia.net> wrote:

>Hi all,
>
>I'm new to this, so please bear with me...
>
>I have a table containing 700,000+ records but there is not a column that
>will uniquely identify any idividual record in the table. In order to
>increase the performance of the database, should I create a new column
>called "ID" (like in MS Access) and insert an integer, say record number, to
>make each record unique? If so, what would be the best way to accomplish
>this? If this is not a good solution, would creating a composite index
>solve the problem? And again, how can that be accomplished? Thank you so
>much in advance.
>
>Roger
>

Report this post to a moderator

Hi Roger.

Are any queries actually issued against this table or is it just used to log
information?

If there are no queries issued against the table, then adding another column
will simply increase the size of the table and add additional overhead in
calculating whatever values go into that column.. This will likely slow down
the database..

If there are queries issued against the table (and they relate to the
application rather than ad-hoc reporting type queries) then the index is
likely to improve performance, but only if the index is actually useful to
the query. How will you know if the index is useful to the query? Use the
Index Tuning Wizard - it will recommend whatever index it would actually
"like" to see on the table. You can do this from the Query Analyser -
highlight the query in the query pane, then select "Index Tuning Wizard"
from the Query menu.

A general rule of thumb is that indexes slow down data inserts / updates,
but improve queries / joins etc. Another good rule of thumb is that the ITW
is the best place to start learning about which indexes will be most useful
to the SQL Server Cost Based Optimizer. Much better than guessing..

HTH

Cheers,
Greg Linwood

"RYoh" <ryoh@adelphia.net> wrote in message
news:ecmsvrKpCHA.2412@TK2MSFTNGP12...
> Hi all,
>
> I'm new to this, so please bear with me...
>
> I have a table containing 700,000+ records but there is not a column that
> will uniquely identify any idividual record in the table. In order to
> increase the performance of the database, should I create a new column
> called "ID" (like in MS Access) and insert an integer, say record number,
to
> make each record unique? If so, what would be the best way to accomplish
> this? If this is not a good solution, would creating a composite index
> solve the problem? And again, how can that be accomplished? Thank you so
> much in advance.
>
> Roger
>
>

Report this post to a moderator

Greg, thank you very much. I used the Index Tunnig Wizard and it worked out
great! I really appreciated all of you guys help! Happy holidays.

Roger
"Greg Linwood" <g_linwood@hotmail.com> wrote in message
news:u1BAWdNpCHA.1628@TK2MSFTNGP12...
> Hi Roger.
>
> Are any queries actually issued against this table or is it just used to
log
> information?
>
> If there are no queries issued against the table, then adding another
column
> will simply increase the size of the table and add additional overhead in
> calculating whatever values go into that column.. This will likely slow
down
> the database..
>
> If there are queries issued against the table (and they relate to the
> application rather than ad-hoc reporting type queries) then the index is
> likely to improve performance, but only if the index is actually useful to
> the query. How will you know if the index is useful to the query? Use the
> Index Tuning Wizard - it will recommend whatever index it would actually
> "like" to see on the table. You can do this from the Query Analyser -
> highlight the query in the query pane, then select "Index Tuning Wizard"
> from the Query menu.
>
> A general rule of thumb is that indexes slow down data inserts / updates,
> but improve queries / joins etc. Another good rule of thumb is that the
ITW
> is the best place to start learning about which indexes will be most
useful
> to the SQL Server Cost Based Optimizer. Much better than guessing..
>
> HTH
>
> Cheers,
> Greg Linwood
>
> "RYoh" <ryoh@adelphia.net> wrote in message
> news:ecmsvrKpCHA.2412@TK2MSFTNGP12...
> > Hi all,
> >
> > I'm new to this, so please bear with me...
> >
> > I have a table containing 700,000+ records but there is not a column
that
> > will uniquely identify any idividual record in the table. In order to
> > increase the performance of the database, should I create a new column
> > called "ID" (like in MS Access) and insert an integer, say record
number,
> to
> > make each record unique? If so, what would be the best way to
accomplish
> > this? If this is not a good solution, would creating a composite index
> > solve the problem? And again, how can that be accomplished? Thank you
so
> > much in advance.
> >
> > Roger
> >
> >
>
>

Report this post to a moderator