Getting Started with Adobe After Effects - Part 6: Motion Blur
First Time? You can support us by signing up. It takes only 5 seconds. Click here to sign up. If you already have an account, click here to login.
Loading

1st Prize - Nokia Lumia 710


Win 31 copies of

SQLServer Quiz 2012 - What is an IDENTITY Value?

  • What is an IDENTITY Value? What are the different ways to get IDENTITY values and properties of IDENTITY Values? If you are going to answer that identity value is ever increasing, is it possible to have identity value ever decreasing value?

    Posted on 01-03-2012 00:00 |
    InterviewQuestions
    73 · 2% · 775

12  Answers  

Subscribe to Notifications
Previous 1 | 2 Next
  • Score
    3

    **Click here -- What is IDENTITY Value

    Click here - Properties of IDENTITY Values**

    Different ways to get IDENTITY

    1) SCOPEIDENTITY - Returns the last identity value within the current execution scope. SCOPEIDENTITY is recommended for most scenarios.

    2) @@IDENTITY- Contains the last identity value generated in any table in the current session. @@IDENTITY can be affected by triggers and may not return the identity value that you expect.

    3) IDENT_CURRENT - Returns the last identity value generated for a specific table in any session and any scope.

    4) Useing Select Query retrive max column value

    Identity value can be increasing Or decressing , we have do specify Identity Specifications i.e. Identity increement and Seed value

    Please refer above links also

    Replied on Jan 3 2012 1:04AM  . 
    Yogesh Kamble
    142 · 1% · 349
  • Score
    6

    What is an IDENTITY Value? Identity value is an auto number value column on a table. Identity property depends on its increment factor whether its an incremental value or decremental value. It has a seed to start a number with.

    What are the different ways to get IDENTITY values and properties of IDENTITY Values?

    1.Scope_Identity

    2.@@Identity

    3.IDENT_Current

    4.$Identity

    Example:

    create Table TestIdentity (Id int identity(1,-1), name Varchar(50))

    Insert into TestIdentity Select 'SQLZealot'

    Insert into TestIdentity Select 'SQLZealot1'

    Insert into TestIdentity Select 'SQLZealot2'

    Select SCOPE_IDENTITY()

    Select @@IDENTITY

    Select IDENT_CURRENT('TestIdentity')

    Select Min($IDENTITY) From TestIdentity /Min or Max to get the identity as per the increment factor. If its a positive increment, use MAX . If its a negative increment, use MIN./

    If you are going to answer that identity value is ever increasing, is it possible to have identity value ever decreasing value?

    Yes it is possible. It depends on the increment factor.

    Replied on Jan 3 2012 5:00AM  . 
    Latheesh NK
    51 · 4% · 1178
  • Score
    7

    Identity:

    is a sql server generated number on a table column based on the seed and increment.Seed is the starting value the column should start with.Increment is the incremental or decremental factor by which the column would be assigned the values. -Identity column cant accept NULL values.Only one identity column can exist in a table. -Rollback cannot affect identity values. -We can create this identity on a column during the table creation as following CREATE TABLE tablename

    (

    columnname datatype identity [(seed, increment)

    [NOT FOR REPLICATION]],

    [columnname ...]

    )

    -Following data types can be used for Identity column: int,bigint,smallint,tinyint decimal or numeric with a scale of 0.

    -IDENTITYINSERT : Setting this to ON allows the explicit values to be inserted into the identity column of a table.Once this is set column list should be specified in the insert statement while inserting new records.This works only within the session. This can be done as follows SET IDENTITYINSERT [tablename] ON

    -We can check the identity value of a given table using the below command DBCC CHECKIDENT('tablename',RESEED) -We can reseed the identity value using the below commad: DBCC CHECKIDENT('tablename',RESEED,number to be set)

    We can retrieve the last inserted IDENTITY value in the following ways:

    1.@@IDENTITY:

    It returns the last IDENTITY value generated in a session regardless of the table that generated the value and scope of the statement that generated the value.It returns the last identity value entered into a table in our current session.NULL is returned if no identity value is inserted in a table. SELECT @@IDENTITY

    2.SCOPE_IDENTITY():

    It returns the last identity value generated in a session and by a statement in the same scope,regardless of the table that produced the value.It returns the last identity value created in the current session and current scope. SELECT SCOPE_IDENTITY()

    3.IDENT_CURRENT:

    It returns the last identity value generated in the given table.regardless of the session that generated the value,and regardless of the scope of the statement that generated the value.It returns thw identity value generated for a specific table in any session and any scope. SELECT IDENT_CURRENT('tablename')

    If you are going to answer that identity value is ever increasing, is it possible to have identity value ever decreasing value?

    No,identity value is neither ever increasing nor ever decreasing.It depends on the size of the data type.Incremental or decremental factor should not cross the data type range.ArithmeticOverflow Error is thrown if it crosses the range of the mentioned data types.

    TINYINT - can have identity value in the range of 0 to 255.

    SMALLINT - can have identity value in the range of -32,768 to 32,767.

    INT - can have identity value in the range of -2,147,483,648 to 2,147,483,647.

    BIGINT - can have identity value in the range of -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807.

    DECIMAL & NUMERIC with a scale of 0 - can have identity value in the range of - 10^38 to 10^38.

    Replied on Jan 3 2012 10:59AM  . 
    SreelekhaVikram
    1343 · 0% · 17
  • Score
    3

    Identity column generates sequential values for new records using a seed value. SELECT SCOPEIDENTITY(),SELECT @@IDENTITY,SELECT IDENTCURRENT are the different ways to get IDENTITY. We can specify the Identity increment value to make it increasing or decreasing.

    Ex - Identity Seed = 100, Identity Increment value = -1 will give values like 100,99,98 etc.

    Replied on Jan 3 2012 9:22PM  . 
    Anup Warrier
    209 · 1% · 224
  • Score
    4

    What is an IDENTITY Value? What are the different ways to get IDENTITY values and properties of IDENTITY Values? If you are going to answer that identity value is ever increasing, is it possible to have identity value ever decreasing value?

    Identity is an attribute for a column. This property can be set on the INT type column as well as on BIGINT type. We can set initial value (Identity Seed) and Increment Value. By default both values are 1,1. By specifying negative Increment value we can get ever decreasing value.

    There are many ways of getting the identity value back. The most reliable way is in using OUTPUT clause of the INSERT command. We can also use SCOPE_IDENTITY() function.

    @@IDENTITY will return last used identity value which may be different than the identity for the value we're inserting record into if that table has an INSERT trigger which inserted record(s) into another table with identity column.

    I am not considering other methods as they can not be used in multi-user environment.

    Pinal Dave has a very good blog SQL SERVER – @@IDENTITY vs SCOPEIDENTITY() vs IDENT_CURRENT – Retrieve Last Inserted Identity of Record

    And I found another interesting blog by Pinal Dave SQL SERVER – 2008 – SCOPEIDENTITY Bug with Multi Processor Parallel Plan and Solution which also confirms my statement about using OUTPUT clause for getting the identity value.

    Replied on Jan 3 2012 9:53PM  . 
    Naomi
    31 · 6% · 1776
  • Score
    4

    What is an IDENTITY Value? Identity is an attribute for a column.Identity value is an auto number value column on a table. Identity property depends on its increment factor whether its an incremental value or decremental value. It has a seed to start a number with.

    What are the different ways to get IDENTITY values and properties of IDENTITY Values?

    1.@@IDENTITY:

    It returns the last IDENTITY value generated in a session regardless of the table that generated the value and scope of the statement that generated the value.It returns the last identity value entered into a table in our current session.NULL is returned if no identity value is inserted in a table. SELECT @@IDENTITY

    2.SCOPE_IDENTITY():

    It returns the last identity value generated in a session and by a statement in the same scope,regardless of the table that produced the value.It returns the last identity value created in the current session and current scope. SELECT SCOPE_IDENTITY()

    3.IDENT_CURRENT:

    It returns the last identity value generated in the given table.regardless of the session that generated the value,and regardless of the scope of the statement that generated the value.It returns thw identity value generated for a specific table in any session and any scope. SELECT IDENT_CURRENT('tablename')

    Example:

    create Table TestIdentity (Id int identity(1,-1), name Varchar(50))

    Insert into TestIdentity Select 'SQLShan'

    Insert into TestIdentity Select 'SQLShan1'

    Insert into TestIdentity Select 'SQLShan2'

    Select SCOPE_IDENTITY()

    Select @@IDENTITY

    Select IDENT_CURRENT('TestIdentity')

    If you are going to answer that identity value is ever increasing, is it possible to have identity value ever decreasing value?

    Yes it is possible. It depends on the increment factor. But the maximum size of the data type always count on it.

    Replied on Jan 3 2012 10:19PM  . 
    SQLShan
    690 · 0% · 48
  • Score
    4

    What is an IDENTITY Value?

    Identity value is an auto number (increment or decrement) value for a column in a table. We can provide seed value to start the number with - will be assigned to the first row in the table.

    You must specify both the seed and increment or neither. If neither is specified, the default is (1,1).

    A table can have only one column defined with the IDENTITY property, and that column must be defined by using a decimal, int, numeric, smallint, bigint, or tinyint data type.

    What are the different ways to get IDENTITY values and properties of IDENTITY Values?

    1.Scope_Identity() - Returns the last identity value inserted into an identity column in the same scope.

    2.@@Identity - is a system function that returns the last-inserted identity value

    3.IDENT_CURRENT - Returns the last identity value generated for a specified table or view.

    4.$Identity - Can be used in SELECT clause to return max or min value for identity column

    We can also use:

    IDENT_INCR - Returns the last identity value generated for a specified table or view.

    IDENT_SEED - Returns the original seed value (returned as numeric(@@MAXPRECISION,0)) that was specified when an identity column in a table or a view was created.

    If you are going to answer that identity value is ever increasing, is it possible to have identity value ever decreasing value?

    Yes it is possible. It depends on the increment factor specified.

    Replied on Jan 3 2012 11:14PM  . 
    Hardik Doshi
    20 · 9% · 2853
  • Score
    6

    Identity values

    Identity values are automatically generated unique numeric values for each row in an Identity column when a new record is added. This makes sure that each record has a unique value for that field. An identity column are managed by the server and usually cannot be modified.

    Syntax: IDENTITY [ (seed , increment ) ]

    Seed: Is the value that is used for the very first row loaded into the table.

    increment :Is the incremental value that is added to the identity value of the previous row that was loaded.Wee need to specify both the seed and increment or neither. If neither is specified, the default is (1,1).

    Different ways to get IDENTITY values and properties of IDENTITY Values

    1. Global variable @@IDENTITY

    Returns the last identity value generated in any table in the current session.

    2. SCOPE_IDENTITY() Function

    Returns :last identity value generated in any table only within the current scope. SELECT SCOPE_IDENTITY()

    3. IDENT_CURRENT

    Is not limited by scope and session; it is limited to a specified table. IDENTCURRENT returns the identity value generated for a specific table in any session and any scope. Eg:-SELECT IDENTCURRENT('table')

    4. Pseudo Column $Identty :

    Returns inserted identity values. Eg:- SELECT $IDENTITY FROM table_name

    5. DBCC CHECKIDENT:

    Helps to checks and corrects the current identity value for the specified table .DBCC CHECKIDENT('table’) displays current identity value and column value.

    Syntax:

    DBCC CHECKIDENT

    ( 'table_name' 
    
        [ , { NORESEED 
    
                | { RESEED [ , new_reseed_value ] } 
    
            } 
    
        ] 
    
    )
    

    Arguments:

    'table_name' Is the name of the table for which to check the current identity value. Table names must conform to the rules for identifiers. For more information, see Using Identifiers. The table specified must contain an identity column.

    NORESEED Specifies that the current identity value should not be corrected.

    RESEED Specifies that the current identity value should be corrected. newreseedvalue Is the value to use in reseeding the identity column.

    6.IDENTINCR() Function : IDENTINCR ( 'tableorview' ): Returns the increment value specified during the creation of an identity column in a table or view that has an identity column.

    7.IDENTITY() Function
    Is used only in a SELECT statement with an INTO table clause to insert an identity column into a new table. Although similar, the IDENTITY function is not the IDENTITY property that is used with CREATE TABLE and ALTER TABLE.

    Syntax: IDENTITY ( datatype [ , seed , increment ] ) AS columnname:

    Arguments

    data_type:Is the data type of the identity column. Valid data types for an identity column are any data types of the integer data type category (except for the bit data type), or decimal data type.

    seed:Is the value to be assigned to the first row in the table. Each subsequent row is assigned the next identity value, which is equal to the last IDENTITY value plus the increment value. If neither seed nor increment is specified, both default to 1.

    increment:Is the increment to add to the seed value for successive rows in the table.

    column_name:Is the name of the column that is to be inserted into the new table.

    Decreasing Identity Value

    It is possible to have a decreasing identity values.If we give a negative seed value it will generate a decreasing identity value. SQL Server will always choose the highest number as its current seed for a positive increment value or the lowest for a negative increment value. Suppose,

    CREATE TABLE MyTable (MyID Int IDENTITY(1000000, -100) NOT NULL
                        ,MyDescription NVarChar(50) NOT NULL )
    

    This will Create table with an IDENTITY property that is set to start at 1,000,000 and decrement by 100 for every row added

    Reference:

    http://msdn.microsoft.com/en-us/library/ms187342.aspx

    http://www.java2s.com/Code/SQLServer/Sequence/SQLServerchoosesthehighestnumberasitscurrentseedforapositiveincrementvalueorthelowestforanegativeincrementvalue.htm][2]

    Replied on Jan 4 2012 12:01AM  . 
    Sineetha
    106 · 2% · 492
  • Score
    4

    What is an IDENTITY Value?

    ANS Identity value is an auto number value column on a table.

    Syntax

    IDENTITY [ ( seed , increment ) ]

    Arguments

    seed

    Is the value that is used for the very first row loaded into the table.

    increment

    Is the incremental value that is added to the identity value of the previous row that was loaded.

    You must specify both the seed and increment or neither. If neither is specified, the default is (1,1).

    http://msdn.microsoft.com/en-us/library/aa933196(v=sql.80).aspx

    @@IDENTITY
    SELECT @@IDENTITY

    DBCC CHECKIDENT
    DBCC CHECKIDENT (Tablename, NORESEED)

    MAX FUNCTION
    SELECT MAX(id) FROM Tablename

    TOP 1 AND ORDER BY DESC
    SELECT TOP 1 id FROM Tablename ORDER BY id DESC

    IDENTCURRENT
    SELECT IDENT
    CURRENT('Tablename')

    SCOPEIDENTITY
    SELECT SCOPE
    IDENTITY()

    Retrieve identity column properties for SQL Server database tables

    SELECT   OBJECT_NAME(OBJECT_ID) AS TABLENAME, 
             NAME AS COLUMNNAME, 
             SEED_VALUE, 
             INCREMENT_VALUE, 
             LAST_VALUE, 
             IS_NOT_FOR_REPLICATION 
    FROM     SYS.IDENTITY_COLUMNS 
    ORDER BY 1
    

    is it possible to have identity value ever decreasing value?
    Yes it is possible. It depends on the increment factor.
    Example
    Identity Seed = 50, Identity Increment = -1 will give values like 50,49,48,47 etc.

    Replied on Jan 4 2012 10:42AM  . 
    Mitesh Modi
    18 · 10% · 3080
  • Score
    5

    IDENTITY (Property) It a numeric value generated by SQL Server. It is associated with a column of a table. We can set the SEED and INCREMENT values for an identity column.

    If an identity column exists for a table with frequent deletions, gaps can occur between identity values. If this is a concern, do not use the IDENTITY property. However, to make sure that no gaps have been created or to fill an existing gap, evaluate the existing identity values before explicitly entering one with SET IDENTITY_INSERT ON.

    If you are reusing a removed identity value, use the sample code in example B to look for the next available identity value. Replace tablename, columntype, and MAX(columntype) - 1 with a table name, identity column data type, and numeric value of the maximum allowed value (for that data type) -1.

    Use DBCC CHECKIDENT to check the current identity value and compare it with the maximum value in the identity column.

    Ways to Get Identity Value

    Following are the functions used to get IDENTITY Value;

    • @@IDENTITY

    After an INSERT, SELECT INTO, or bulk copy statement is completed, @@IDENTITY contains the last identity value that is generated by the statement. If the statement did not affect any tables with identity columns, @@IDENTITY returns NULL. If multiple rows are inserted, generating multiple identity values, @@IDENTITY returns the last identity value generated. If the statement fires one or more triggers that perform inserts that generate identity values, calling @@IDENTITY immediately after the statement returns the last identity value generated by the triggers. If a trigger is fired after an insert action on a table that has an identity column, and the trigger inserts into another table that does not have an identity column, @@IDENTITY returns the identity value of the first insert. The @@IDENTITY value does not revert to a previous setting if the INSERT or SELECT INTO statement or bulk copy fails, or if the transaction is rolled back.

    Failed statements and transactions can change the current identity for a table and create gaps in the identity column values. The identity value is never rolled back even though the transaction that tried to insert the value into the table is not committed. For example, if an INSERT statement fails because of an IGNOREDUPKEY violation, the current identity value for the table is still incremented.

    • SCOPE_IDENTITY

    Returns the last identity value inserted into an identity column in the same scope. A scope is a module: a stored procedure, trigger, function, or batch. Therefore, two statements are in the same scope if they are in the same stored procedure, function, or batch.

    • IDENT_CURRENT

    Returns the last identity value generated for a specified table or view. The last identity value generated can be for any session and any scope.

    Replied on Jan 9 2012 4:42AM  . 
    ATif-ullah Sheikh
    133 · 1% · 391
Previous 1 | 2 Next

Your Answer


Sign Up or Login to post an answer.
Please note that your answer will not be considered as part of the contest because the competition is over. You can still post an answer to share your knowledge with the community.

Copyright © Rivera Informatic Private Ltd.