SQL Server Performance

simple sql question

Discussion in 'SQL Server 2005 General Developer Questions' started by meishern, Aug 16, 2006.

  1. meishern New Member

    Hi.

    Please help me.

    Table 'Counter' has a date field and an integer field. I want to auto increase the integer field by 1 using the update statement. I DO NOT want to first run a select statement to find out the initial value of the integer field prior to incrementing it. how would i go about doing this?

    Thanks a lot!
    meishern, Aug 16, 2006
    #2

  2. satya Moderator

    Refer to SQL Server 2005 books online about IDENTITY column in thise case.

    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing.
    satya, Aug 16, 2006
    #2

  3. FrankKalis Moderator

    Not sure if this is what you're looking for:


    CREATE TABLE SEQ_T
    (
    col1 INT DEFAULT 0
    )
    DECLARE @SEQUENCE INT
    SET @SEQUENCE = 0
    WHILE @SEQUENCE <10
    BEGIN
    INSERT INTO SEQ_T DEFAULT VALUES
    SET @SEQUENCE = @SEQUENCE+1
    END
    SET @SEQUENCE = 0
    UPDATE SEQ_T SET @SEQUENCE = col1 = @SEQUENCE + 1
    SELECT * FROM SEQ_T
    DROP TABLE SEQ_T

    col1
    -----------
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10

    (10 row(s) affected)

    The only drawback here is, that you have no control over the order in which the rows are updated. It might or might not match your expectations. And if that doesn't help you, please explain with an example what you are looking for.


    --
    Frank Kalis
    Moderator
    Microsoft SQL Server MVP
    Webmaster:http://www.insidesql.de
    FrankKalis, Aug 16, 2006
    #2

Share This Page