how to auto generate ID's in MSSQL

  • Hi I am new to SQL server and I am working on a project that needs to insert id's say UID into a user table.How do I do this.Can you refer to me to any article that will help me learn this.Thanks in advance.

    Regards

    Raviteja Chilakamarthi

  • Raviteja,

    I think you are looking for something like this:

    alter table dbo.test

    add testID int identity(1,1) not null

  • Are you talking about how to add a column to a table that could be used as an Identity or are you asking how to insert a value into an identity column. Please be a bit more specific, then people here can give you a good answer.

    -Roy

  • I already have a column name UID in a table called USER, for which I need to generate the ID numbers.

  • USE [test]

    GO

    CREATE TABLE [dbo].[test1](

    [idcol] [int] IDENTITY(1,1) NOT NULL,

    [Somecol] [char](10) NULL

    ) ON [PRIMARY]

    GO

    If your table is like this, You just need to insert into Test1 the value of second column.

    Insert into test1 (SomeCol)

    values 'dfsddfdsd'

    This statement will create a record with idcol as 1. Next time you enter another value, the value of idcol will be 2.

    Roy

    -Roy

  • I want the generated values to be of this format

    U0001

    U0002

    Can I do this with the format that you have given.

  • ravi.chilakamarthi (3/17/2009)


    I want the generated values to be of this format

    U0001

    U0002

    Can I do this with the format that you have given.

    You could define a computed column with the formatted user id something like this:

    CREATE TABLE SystemUser (

    UserId INT IDENTITY(1,1) NOT NULL,

    FormattedUserId AS ('U' + RIGHT('0000000000' + CAST(UserId AS VARCHAR(10)),10)),

    UserName VARCHAR(50)

    )

    INSERT INTO SystemUser VALUES ('Test1')

    INSERT INTO SystemUser VALUES ('Test2')

    INSERT INTO SystemUser VALUES ('Test3')

    INSERT INTO SystemUser VALUES ('Test4')

    SELECT * FROM SystemUser

    DROP TABLE SystemUser

    I chose 10 as the maximum numeric length because INTs can be 10 digits.

    If you were using BIGINT or DECIMAL you'd have to choose a different size.

    The output is:

    UserIdFormattedUserIdUserName

    1U0000000001Test1

    2U0000000002Test2

    3U0000000003Test3

    4U0000000004Test4

    Of course, you'll need to define some further constraints if you want your Ids to be unique and non-negative.

  • This looks great but I dont want the USER ID to be in the table......how can I avoid that??

  • ravi.chilakamarthi (3/18/2009)


    This looks great but I dont want the USER ID to be in the table......how can I avoid that??

    SELECT FormattedUserId, UserName FROM SystemUser

    Why don't you want the UserId in the table?

  • I had the same problem.

    I need to create custom ids for each table, it's a direct order from the boss.

    Until know i got this function to format the IDs (i use REPLICATE it like the excel REPT function):

    CREATE FUNCTION [dbo].[ge_fn_formatID] (

    @Prefix char(1),

    @Id bigint

    )

    RETURNS NVARCHAR(10)

    AS

    BEGIN

    declare @Length int

    set @Length = 9

    RETURN ( SELECT @Prefix + RIGHT(REPLICATE('0', @Length) + CAST(@Id as nvarchar(10)), @Length))

    END

    For this tabe:

    CREATE TABLE [dbo].[teste](

    [id] [nvarchar](50) NOT NULL,

    [teste] [nvarchar](50) NULL,

    CONSTRAINT [PK_teste] PRIMARY KEY CLUSTERED

    (

    [id] ASC

    ))

    When i do the insert, i have to be unsure that no one are selecting or creating a new item, soo i think i have to lock the Transaction, like this:

    begin tran

    begin try

    --

    insert into [dbo].[teste]

    select dbo.[ge_fn_formataID] ('T', count(id)+1), 'Lisbon'

    from [dbo].[teste]

    with (HOLDLOCK)

    end try

    begin catch -- start catch 1

    DECLARE @msg nvarchar(max)

    DECLARE @ErrorMessage NVARCHAR(4000);

    DECLARE @ErrorSeverity nvarchar(10);

    set @msg=N'Erro: %s | Severity: %s'

    SELECT @ErrorMessage = ERROR_MESSAGE(),

    @ErrorSeverity = cast(str(ERROR_SEVERITY()) as nvarchar(10))

    RAISERROR (@msg, 10, 1,@ErrorMessage, @ErrorSeverity)

    IF (@@TRANCOUNT > 0) ROLLBACK

    end catch --end catch 1

    IF (@@TRANCOUNT > 0) COMMIT

    There are any better ideas?

    Thanks

    Luis Costa

  • the way i've always tackled this situation is to go ahead and have an identity column, and then a persisted calculated column to create teh text-based userid;

    For example, Invoices: this does not allow the person to select the Prefix, it gets calculated from another column:

    the identity and the calculated column completely solve the concurrency issue you are fighting with.

    CREATE TABLE Invoices(

    InvoicesID int identity(1,1) not null PRIMARY KEY,

    InvoiceType varchar(3) not null CHECK(InvoiceType IN('PO','RT','PR') ), --purchase order return or purchase request,

    InvoiceNumber AS InvoiceType + RIGHT(REPLICATE('0', 10) + CONVERT(varchar,InvoicesID),10) PERSISTED,

    OtherColumns varchar(30) )

    INSERT INTO Invoices (InvoiceType,OtherColumns)

    SELECT 'PO', 'A Purchase Order' UNION ALL

    SELECT 'RT', 'A Return Document' UNION ALL

    SELECT 'PR', 'A Purchase Request'

    SELECT * FROM Invoices

    /*

    --Results

    1POPO0000000001A Purchase Order

    2RTRT0000000002A Return Document

    3PRPR0000000003A Purchase Request

    */

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Try this.

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • I can't use a calculated field, must be only one column, and must be key.

    I tested in 2 sessions 5000 inserts, each, without lock and I had 4000 errors

    Then i did the same test with (HOLDLOCK), and i got 2 errors in one session, and zero on the other one.

    Then i put a lable just before the begin tran

    and a goto in the catch, after the rollback (with a counter, e.g. try 20x), and i got Zero errors... and 3 minutes to insert 5000 on each session... it's an horror of no errors!

    AHHHHHHHHHHHHHHHHHHHHHHHHHHHH!

    thanks....

  • well an identity will easily fix the contention issue, so if you arbitrarily eliminate that possibility, i'm not sure what you can do.

    lfcost (7/3/2012)


    I can't use a calculated field, must be only one column, and must be key.

    I tested in 2 sessions 5000 inserts, each, without lock and I had 4000 errors

    Then i did the same test with (HOLDLOCK), and i got 2 errors in one session, and zero on the other one.

    Then i put a lable just before the begin tran

    and a goto in the catch, after the rollback (with a counter, e.g. try 20x), and i got Zero errors... and 3 minutes to insert 5000 on each session... it's an horror of no errors!

    AHHHHHHHHHHHHHHHHHHHHHHHHHHHH!

    thanks....

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Thanks.

    You are right.

    Back to identity and do all the job not to show the id column without a mask.

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply