?
Solved

auto increase primary key value

Posted on 2003-03-21
4
Medium Priority
?
980 Views
Last Modified: 2011-10-03
Hi,

I am thinking if there is a way to let SQL server auto increase a primary key value. Like "serial" data type in informix, informix auto increases its number whenever a record is inserted into the database.

I only found there is "uniqueidentifier data type" in SQL, which taks 16 bytes. This is two huge for some application, I only need an integer.

Is there a way , i.e. write a function in SQL to let it auto increase a primary key value(plus one on privious record's primary key)? I don't want application program to trace this value.

How much cost will those functions be? i.e. time used in SQL server. I need continusly insert records to database in realtime.

thanks,

Gossip

0
Comment
Question by:Gossip
[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
  • 2
4 Comments
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 80 total points
ID: 8183842
You can designate a column as IDENTITY(1,1) (or some other starting value, increment).  For example:

CREATE TABLE x (
    col1 INT IDENTITY(1,1) NOT NULL,
    col2 VARCHAR(10),
    col3 DECIMAL(3,2)
)


Then, when you insert, don't specify a value for col1 at all.  For example:

INSERT INTO x VALUES('a', 1.5)
INSERT INTO x VALUES('b', 2.75)
0
 

Expert Comment

by:Mr_Pennypacker
ID: 8184823
You can also select a column and set it to uniqueidentifier and set the default value to (newid())

CREATE TABLE x (
   col1 uniqueidentifier ROWGUIDCOL  NOT NULL,
   col2 VARCHAR(10),
   col3 DECIMAL(3,2)
)

MS Sql server will automatically generate a globally unique identifier (GUID) for each record inserted.  A guid is a 16-byte binary values that is unique and no other computer in the world will generate a duplicate of that GUID value.

You also insert without specifying a value for col1 as:
INSERT INTO x VALUES('a', 1.5)
INSERT INTO x VALUES('b', 2.75)
0
 

Expert Comment

by:Mr_Pennypacker
ID: 8184829
oops, you need to add a default value to col1 for it to automatically generate a GUID for each new entry, so it should read something like:

CREATE TABLE MyUniqueTable
   (UniqueColumn   UNIQUEIDENTIFIER      DEFAULT NEWID(),
   Characters      VARCHAR(10) )
0
 

Author Comment

by:Gossip
ID: 8210257
thank you, ScottPletche, Mr_Pennypacker ! that works!
0

Featured Post

Get real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

752 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