|
|
|
Grasshopper
 
Group: General Forum Members
Last Login: Today @ 5:16 AM
Points: 20,
Visits: 37
|
|
Hi,
I have 70million records table having email column.I want to add primary key to the email column.
plssssssssssssss suggest me the efficient way to do this in sqlserver 2005.
Thanks in advance...
|
|
|
|
|
Ten Centuries
Group: General Forum Members
Last Login: Friday, March 19, 2010 4:08 AM
Points: 1,090,
Visits: 1,962
|
|
I am assuming the email field is a varchar, adding a primary key to such a field might not be the best thing. I hope you have a test system somewhere to try this but I would look at creating a new table that is partitioned in some way, migrating the data into that table and renaming. Can you provide the full schema of the table?
Facts are stubborn things, but statistics are more pliable - Mark Twain
Carolyn
SQLServerSpecialists
|
|
|
|
|
Grasshopper
Group: General Forum Members
Last Login: Today @ 5:16 AM
Points: 20,
Visits: 37
|
|
Below is the total table with 70 Million records
Total ( Email,FirstName,LastName,Address,City,State,Zipcode,Gender,DOB ,
Phone,WebAddress1,Webaddress2,IPAddress,DateTime,Interest
)
Ofcourse Email is nvarchar(255)
I do not want duplicates in that, so wanted to add Primary Key.
I need the best way for adding that..
My idea is like
ALTER TABLE total ADD PRIMARY KEY (Email)
the above is enough or do we need to mention clustered also
ALTER TABLE total ADD CONSTRAINT pk_email PRIMARY KEY CLUSTERED (Email)
Plsssssssssssss suggest me the best way with syntax
|
|
|
|
|
SSChampion
Group: General Forum Members
Last Login: Today @ 2:26 AM
Points: 17,125,
Visits: 12,228
|
|
An nvarchar(255) is a rather bad choice for a clustered index. Clustered indexes should be narrow (among other things). 512 bytes is not narrow.
If there an existing primary key on the table? Is there an existing clustered index on the table? If you just want to enforce uniqueness, you can define a unique constraint. Is the email column nullable? If so, you can't put a primary key on it, but a unique constraint (or unique index) is allowed
Gail Shaw
We walk in the dark places no others will enter
We stand on the bridge and none may pass
|
|
|
|
|
Grasshopper
Group: General Forum Members
Last Login: Today @ 5:16 AM
Points: 20,
Visits: 37
|
|
No Email column is not Nullable.
No other primary key or clustered index existing in the table.
This is the first one we are going to put.
Do u mean Unique is better than primary key or how is that
Thnx in advance..
|
|
|
|
|
Ten Centuries
Group: General Forum Members
Last Login: Thursday, March 18, 2010 9:03 AM
Points: 1,427,
Visits: 1,506
|
|
DBTeam (11/18/2009)
No Email column is not Nullable.
No other primary key or clustered index existing in the table.
This is the first one we are going to put.
Good.. you have everything clear to create a prmiary key, but wait till you read the entire reply.
Do u mean Unique is better than primary key or how is that
In your case, it is better to have a unique constraint enabled rather than having a primary key (Since it is nvarchar(255).
Bru Medishetty
To get quick answers read the article
How to post data/code on a forum to get the best help
My Blog - learnsqlwithbru.com
|
|
|
|
|
Grasshopper
Group: General Forum Members
Last Login: Today @ 5:16 AM
Points: 20,
Visits: 37
|
|
So it won't create any problem right,
even though if we insert million of records into that in the future.
B'coz earlier we kept Primary key after that insertion process got delayed(taking days together), so we dropped primary key constraint.
Unique constraint will not delay the insertion process right...
Pls help me reg this.......
|
|
|
|
|
SSChampion
Group: General Forum Members
Last Login: Today @ 2:26 AM
Points: 17,125,
Visits: 12,228
|
|
DBTeam (11/18/2009)
So it won't create any problem right,
even though if we insert million of records into that in the future.
Maybe. You need to test.
Unique constraint will not delay the insertion process right...
Maybe. You need to test, especially since it seems you're fairly new to this.
Gail Shaw
We walk in the dark places no others will enter
We stand on the bridge and none may pass
|
|
|
|
|
Ten Centuries
Group: General Forum Members
Last Login: Thursday, March 18, 2010 9:03 AM
Points: 1,427,
Visits: 1,506
|
|
DBTeam (11/18/2009)
So it won't create any problem right,
even though if we insert million of records into that in the future.
It will create a problem when a duplicate record is inserted, other than that no major problems should occur. Of course performance problems may arise at a later time, but that may or may not be because of this.
B'coz earlier we kept Primary key after that insertion process got delayed(taking days together), so we dropped primary key constraint.
SQL Server is designed to work with tables containing millions of records and we all have been using them with primary key, and unique key.
By the way you said earlier it was delaying for days together, how many rows already existed in the table and how many rows were you inserting? There might be many reasons for that to be slow.
To sum it up, I repeat the same advice "test it".
Bru Medishetty
To get quick answers read the article
How to post data/code on a forum to get the best help
My Blog - learnsqlwithbru.com
|
|
|
|
|
Right there with Babe
Group: General Forum Members
Last Login: Yesterday @ 6:25 AM
Points: 751,
Visits: 1,540
|
|
If there was clustered primary key on email, it could have been lagging due to page splits during inserts. I think in this case a nonclustered unique constraint (to ensure uniqueness) would be better.
Regards
Piotr
|
|
|
|
