?
Solved

auto increase primary key value

Posted on 2003-03-21
4
Medium Priority
?
999 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
  • 2
4 Comments
 
LVL 70

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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

621 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