creating a primary key on table with data in it


I have a table with 28898683 rows and without a primary key. the select query takes 25 mins to retrieve all the data.

I tried creating a Pr.key but got error because of duplicate rows.

Tried creating an index with (ignore_dup_key = ON) but same error.

please help, so that the data retrieval is fast.


more ▼

asked May 22, 2012 at 04:06 PM in Default

avatar image

81 3 4 6

How are you trying to create the index? What DDL statement are you using?

May 22, 2012 at 04:17 PM ThomasRushton ♦♦

Could you provide some examples of the queries you are running against the table?

May 23, 2012 at 12:44 PM Blackhawk-17
(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

You don't necessarily need a primary key (although one wonders how you uniquely identify the data in your table without having one) but instead an index. Probably you need a clustered index. Use the CREATE INDEX syntax to make one using the column (or columns) that is most commonly used to retrieve your data. http://msdn.microsoft.com/en-us/library/ms188783.aspx

more ▼

answered May 22, 2012 at 05:50 PM

avatar image

Grant Fritchey ♦♦
137k 20 46 81

(comments are locked)
10|1200 characters needed characters left

Having an index on this table wont speed up the time to get the data when you select all the data. Whether it is an index or a table, all the data is all the data and that needs to be read from the disk and passed to your client. If your query however selects only a few columns from the table and then filters out a lot of rows with a WHERE clause then indexing will help.

When you tried to create the primary key did you expect the columns to be unique in their values? Do you think you have a data integrity issue?

As Grant says, you can still create a clustered index on your table that will significantly improve query times - so long as you are filtering out rows and not requesting every column.

more ▼

answered May 23, 2012 at 07:55 AM

avatar image

Fatherjack ♦♦
43.8k 79 101 118

The CI will help as long as they aren't just firehosing data.

May 23, 2012 at 12:43 PM Blackhawk-17

"firehosing" - I like that term! The phrase " ... retrieve all the data." makes me jumpy.

May 23, 2012 at 01:36 PM Fatherjack ♦♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: May 22, 2012 at 04:06 PM

Seen: 878 times

Last Updated: May 23, 2012 at 01:36 PM

Copyright 2018 Redgate Software. Privacy Policy