Solved

generating a key for a table

Posted on 2013-10-23
15
135 Views
Last Modified: 2016-02-11
hi guys

I have a table with 10 columns, with no primary key.

Is there a way i can add an extra column to the table and populate/generate a key for each row?
There can be two or more rows which can have exactly same column values.

Any ideas?

thanks.
0
Comment
Question by:royjayd
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 4
  • 4
15 Comments
 
LVL 66

Assisted Solution

by:Jim Horn
Jim Horn earned 250 total points
ID: 39595083
This will add an id column that will automatically incrment 1, 2, 3, ...
ALTER TABLE YourTABLE
ADD id int identity(1,1) PRIMARY KEY

Open in new window

0
 

Author Comment

by:royjayd
ID: 39595121
ok, thanks
lets say i have rows with id

1
2
3
4
and so on

if row 3 is deleted then will id 4 become id 3 ?

thx.
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 39595143
>if row 3 is deleted then will id 4 become id 3 ?
Nope.  These numbers are generated once the row is initially inserted, and never to be reused, so in your example if 3 is deleted 4 is still 4, and does not 'move' to 3.
0
Does Your Cloud Backup Use Blockchain Technology?

Blockchain technology has already revolutionized finance thanks to Bitcoin. Now it's disrupting other areas, including the realm of data protection. Learn how blockchain is now being used to authenticate backup files and keep them safe from hackers.

 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 39595178
>> There can be two or more rows which can have exactly same column values. <<

If all the columns are exactly the same, and if there's no time element (such as datetime) in the data, why not just prevent the duplicate row(s) from INSERTing instead?  Why would you need repeated copy(ies) of exactly the same data in a table??
0
 

Author Comment

by:royjayd
ID: 39595197
>>>Why would you need repeated copy(ies) of exactly the same data in a table??
well that is a good point :-)
i am going to ask my manager, the problem here is we dont generate the data directly, we get the data from another team and process it.

thx.
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 39595219
It still is probably a good idea to add an identity column, but don't let that overshadow making the correct data decision anyway :-).

If you can, just delete the excess duplicate rows, less overhead all around.
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 39595231
>we get the data from another team and process it.
Sounds like you have a need to initially 'stage' the data into separate tables, where validations such as uniqueness, numbers are numbers, dates are dates, no one was born yesterday, products don't cost a brazillion dollars, Cleveland is not in Indiana, etc. are performed, before the data is ultimately inserted into your production environments.
0
 

Author Comment

by:royjayd
ID: 39595240
yes, agree
Is there a way we can add a trigger (or some db utility) which checks before inserting every row and if a duplicate row already exists  it doesnt add the new row.
Can something of that kind be possible?

thanks.
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 39595272
Yes that's possible, and there are lots of threads out there on how to do this, as long as you have all ten or so columns in the WHERE clause.

How many rows are we inserting here?   If large, a merge would be a better approach, either in T-SQL or in SSIS.  Especially if the business wants some kind of performance metrics on how many rows were inserted - updated - rejected - whatever.
0
 

Author Comment

by:royjayd
ID: 39595358
>>How many rows are we inserting here?
Between 600000 and 800000

thx
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 39595426
A trigger would be more overhead than an EXISTS check in the INSERT statement, although you will still need the trigger as a "failsafe" mechanism.

You also want to include DISTINCT in the SELECT list on the INSERT to prevent duplicates from within the INSERT itself.  That will add some overhead to the INSERT, because the rows will have to be sorted, but that can't be avoided.
0
 

Author Comment

by:royjayd
ID: 39598584
thanks.
I tried doing
ALTER TABLE YourTABLE
ADD id int identity(1,1) PRIMARY KEY

first time i am inserting 100,000 rows, i see id values from 1 to 100,000

then i do
DELETE from TABLE;

Then when i populate 100,000 rows again i see
id starting from 100001, 100002, ect

Curious why doesnt it start from zero..

thanks.
0
 

Author Comment

by:royjayd
ID: 39598615
intrestingly when i do
truncate table MYTABLE     (instead of DELETE statement)
and
insert the data again  the id starts from 0 and not 100001.
But i dont have previlege to do truncate on the prod database.

Any idea how I can make sure that id always start from 0?

Thanks.
0
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 250 total points
ID: 39600236
DELETE FROM dbo.tablename

DBCC CHECKIDENT ( 'dbo.tablename', RESEED, 0 )
0
 

Author Comment

by:royjayd
ID: 39612334
thanks! this clears my doubt.
0

Featured Post

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

My client has a dictionary table. They're defining a list of standard naming convention. Now, they are requiring my team to provide us a mechanism how to match new incoming data with existing data in their system.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

626 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question