Post reply

Column_Type

  • July 20, 2010 at 12:11 am

    #221487

    Hi

    What is Column_Type. Why is it used for?

    Thanks

    S

    John

  • July 20, 2010 at 2:35 am

    #1195356

    Hi

    Are you talking about Data_Type?

  • July 20, 2010 at 3:00 am

    #1195366

    Could you be more specific please? Column_Type found where?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • July 20, 2010 at 3:38 am

    #1195377

    I found the below in BOL in IDENTITY section where a variable is declared as column_type:

    -- Here is the generic syntax for finding identity value gaps in data.

    -- The illustrative example starts here.

    SET IDENTITY_INSERT tablename ON

    DECLARE @minidentval column_type

    DECLARE @maxidentval column_type

    DECLARE @nextidentval column_type

    SELECT @minidentval = MIN($IDENTITY), @maxidentval = MAX($IDENTITY)

    FROM tablename

    IF @minidentval = IDENT_SEED('tablename')

    SELECT @nextidentval = MIN($IDENTITY) + IDENT_INCR('tablename')

    FROM tablename t1

    WHERE $IDENTITY BETWEEN IDENT_SEED('tablename') AND

    @maxidentval AND

    NOT EXISTS (SELECT * FROM tablename t2

    WHERE t2.$IDENTITY = t1.$IDENTITY +

    IDENT_INCR('tablename'))

    ELSE

    SELECT @nextidentval = IDENT_SEED('tablename')

    SET IDENTITY_INSERT tablename OFF

    -- Here is an example to find gaps in the actual data.

    -- The table is called img and has two columns: the first column

    -- called id_num, which is an increasing identification number, and the

    -- second column called company_name.

    -- This is the end of the illustration example.

    -- Create the img table.

    -- If the img table already exists, drop it.

    -- Create the img table.

    IF OBJECT_ID ('dbo.img', 'U') IS NOT NULL

    DROP TABLE img

    GO

    CREATE TABLE img (id_num int IDENTITY(1,1), company_name sysname)

    INSERT img(company_name) VALUES ('New Moon Books')

    INSERT img(company_name) VALUES ('Lucerne Publishing')

    -- SET IDENTITY_INSERT ON and use in img table.

    SET IDENTITY_INSERT img ON

    DECLARE @minidentval smallint

    DECLARE @nextidentval smallint

    SELECT @minidentval = MIN($IDENTITY) FROM img

    IF @minidentval = IDENT_SEED('img')

    SELECT @nextidentval = MIN($IDENTITY) + IDENT_INCR('img')

    FROM img t1

    WHERE $IDENTITY BETWEEN IDENT_SEED('img') AND 32766 AND

    NOT EXISTS (SELECT * FROM img t2

    WHERE t2.$IDENTITY = t1.$IDENTITY + IDENT_INCR('img'))

    ELSE

    SELECT @nextidentval = IDENT_SEED('img')

    SET IDENTITY_INSERT img OFF

    John

  • July 20, 2010 at 5:35 am

    #1195425

    I think it means that you need to put there whatever the column type is. Int if the column is int, decimal if the column is decimal, etc. Just like you'd need to replace TableName with the name of the table that you're looking at.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • July 20, 2010 at 5:40 am

    #1195429

    My bad.

    John

Viewing 6 posts - 1 through 5 (of 5 total)

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