Automatic row ID creation upon insert

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Automatic row ID creation upon insert

Author

Topic

Johnny1985
Starting Member

26 Posts

Posted - 2008-04-14 : 15:05:41

Hey people !

Is there a way in SQL Server 2005 to insert a unique row, and that the primary key column (which is an int typed) will get an index number ?
I want this in order to avoid code behind logic.

I mean something like incrementing from last index if all lower numbers are already present, or retrieving the "holed" index ...

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-04-14 : 15:10:37

Use an identity column.

Do not bother with the "holed" index though. It is pointless to fill the gaps.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Johnny1985
Starting Member

26 Posts

Posted - 2008-04-14 : 16:18:50

I've just finished reading about it ...
don't you think it's too much data for what I need ? I mean, I only need the ID column to go 1,2,3.. which afterwards will be used to join tables.

It's just seems so trivial in all the SQL e-book I've read (authors are using xID to join similar rows on different tables).

How are they doing that ?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-04-14 : 16:21:45

What do you mean by too much data? Identity column gives you 1,2,3,...

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

zubamark
Starting Member

23 Posts

Posted - 2008-04-14 : 16:25:27

select identity(int,1,1)id, Field1,....Field10
into New_Table from Your_table
select * from New_Table
grop New_Table

Johnny1985
Starting Member

26 Posts

Posted - 2008-04-14 : 16:26:19

quote:
too much data for what I need ?

= uniqueidentifier GUID (because I'm using server 2005)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-04-14 : 16:29:47

Why are you looking into uniqueidentifier? It is completely different from an identity, which is what I recommended and provides what you are asking for.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-04-14 : 16:32:17

not uniqueidentifier, rather IDENTITY:

DECLARE @Yak TABLE
(
	ID INT IDENTITY(1,1),
	Val INT
)

INSERT @Yak (Val)
SELECT 45
UNION ALL SELECT -99
UNION ALL SELECT -4
UNION ALL SELECT -32
UNION ALL SELECT 12324

SELECT *
FROM @Yak

ID	Val
1	45
2	-99
3	-4
4	-32
5	12324

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources