x

creating a primary key on table with data in it

hi,

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.

Tauseef
more ▼

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

Tauseef_jan gravatar image

Tauseef_jan
51 2 2 2

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

Grant Fritchey gravatar image

Grant Fritchey ♦♦
98.1k 19 21 74

(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

Fatherjack gravatar image

Fatherjack ♦♦
42.3k 75 78 108

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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

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

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x34

asked: May 22, 2012 at 04:06 PM

Seen: 688 times

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