Working With Identity Column In SQL Server
Hello Dear!
I am back with another tech post, where I want to tell you all about the main points which are useful in working with Identity Columns in SQL Server. The SQL Server is developed by the Microsoft as a relational database management system which primarily functions to store and retrieve data as and when requested by other software applications. Coming to Identity Columns, they have a name, initial seed and step. It is not guaranteed to be unique, in order to make it one you must place a unique index on every Identity Column.
The syntax or how you will generally see an Identity Column (default) is as below:
Ex : CREATE TABLE #a (i INT IDENTITY(1,1), j INT)
We can insert duplicate rows, and yet separate them by the identity column value, where the identity also shows the order in which the rows were inserted. By using the dbcc checkident we can change the current identity seed after pointing it. The current seed value is changed to generate the next value, which is the step added to the current seed: not one more than the maximum value in the table. A failure in an insert can also change the value of the current seed. You can insert an explicit value using set indentity_insert on and include the column list. If a value is greater than the current seed in the direction of the step, when inserted explicitly will update the current seed. See the current see the value is updated to the original seed value by a Truncate table. By using the Scope-identity () you can find the last identity value allocated. While there is no provision to change the property of an existing identity property you are allowed to add an identity column to the existing table. You can use the identity function to create an identity column on a table created using select info. The identity columns and their properties can be viewed via sys.identity_columns. There is no guarantee in allocating the identity values in the order of rows in a text file while using the bulk insert feature. In some cases it is more feasible to use a view to bulk insert into a table with an identity column.
This was about the key points that will help you in working with Identity Columns in SQL server. The topic of concern is of a great depth and hence I have tried to cover only the important notes in this post. I will come back with more useful tech posts here.
Your comments and reviews will fuel us to do create many more good posts. So please feel free to use the comment section below!
For more info: cpd- India
Batches: Regular & Weekends for Working Profession
For FREE DEMO CLASS CALL – 011-65164822 / 91- 8860352748
CPD Technologies
Block C 9/8, Sector – 7, Rohini, Delhi – 110085, India
Landmark: Near Rohini East Metro Station, Opposite Metro Pillar No-397
Website:- www.cpd-india.com
Email:- support@cpd-india.com
