Identity Column in SQL Server - Part 7

welcome to presume technologies I am venket this is part 7 of sequel server in this session we'll learn everything about an identity column what is an identity column how to create an identity column how to supply explicit values for an identity column and how to reset values of an identity column now to understand this better let's look at the existing table that have that we have been working with in all these parts now we have this table called TBL person and if you look at this TBL person table here it has got four columns ID name email gender ID and age and if you look at the ID column here it's a primary key column but it's not an identity column okay how do I know if it how do I know it's not an identity column if you go to the properties of that particular column in the properties window you see that identity it is set to false so it's not an identity column so if a column is not an identity column then in sequel server you will have to supply a value for that for example when I try to insert a row into this table and you row into this table how many columns are there we have 1 2 3 4 5 columns so we have to supply the values for all the five columns including the ID column now let's say I want to insert it insert towards record so the ID is 7 name is Todd email gender ID and age so if we go ahead and execute this query it gets inserted fine but but if you look at the ID column why are we using this primary key column basically to uniquely identify each record in this table let's assume if there are two servers if I want to uniquely identify them I use their ID there now I need usually are some things that users need not have to provide for example if you have a web application let's say you are registering as a customer you don't provide your customer ID through that registration form that is something we want to have automatically calculated by sequel server ok so under those circumstances we can actually make use of the identity column ok so if you mark it as an identity column in sequel server you don't have to supply a value for that now let us see how to create a table with an identity column okay since we already have a table called TBL person let's try to create another table called TBL person one maybe so I'm going to create this table I'm gonna call maybe we'll call this person ID and obviously it's an integer now if you look at this particular column you know select that column and then drag this column properties window and you see that there is something called identity specification and if you click the plus sign next to that you see that you know ease identity okay do you want this column to be an identity column yes I want this column to be an identity column okay and if you look at it as soon as I have selected yes there are two other properties here identity seed and identity increment what are these basically you are saying you know by converting this person ID column into an identity column you're telling sequel server you want the value for this column to be automatically computed when we insert a new row okay now if you want the value to be automatically computed you have I mean you can specify how you want that to be computed for example do you want that value to start at one or do you want to start at 100 okay so that's the seed okay where should I start usually it's one by default but you can customize that for example if you want to start a thousand you can do so just put seed a thousand there and how much do you want to increment by every time when you add a new record do you want to increment by one two three four five or ten or hundred okay let's say for example I want the initial record to have thousand and every next record that I insert needs to be incremented five by five is that possible absolutely put that increment as five and every new record you insert it will be incremented by five but it doesn't make sense and reality in reality it usually the seed is 1 and the increment is 1 but remember you can customize that you want to alright so we specified the information that is required for marking the column as an identity column and since I want this to act as a primary key key I can select primary key otherwise you can just leave it it's up to you so I select it as primary key and then maybe the name of the person let's say this is n we're care of 50 that's all I want at this point let's save this table let's call this TBL person 1 click OK which should create that table okay now if we look at that column let's close that so if you look at TBL person 1 if I expand columns refresh so we have this person ID column which is again set as primary key and if you go to if we go to the properties window you should see this is an identity column true seed at 1 and increment at 1 cool now if I have to insert a row into this particular table so select star from TBL person 1 so we don't have any rows in there so if I want to insert a value into this table insert into TBL person 1 values look at this now how many columns have we got within this table within this table we have got 2 columns now and we know that person ID is an identity column so we don't have to supply a value for that all I have to do is supply the value for name column for example let's say John and when I execute this query what's going to happen is since we specified the seed s1 and increment this one it's going to start at 1 and then put person IDs 1 for this user John so let's execute that when we select all the rows from this table look at that I get this person ID want and on the other hand let's say for example if I insert Toms record execute that select that row we should have automatically got 2 so it's incrementing by 1 because we specified that during marking that column as an identity column all right now so I have marked this column as an identity column now everything is fine I am inserting new records and you know the person ID automatically being calculated which is good okay so at the moment we have person ID one two and three let's say for some reason you know maybe John has left our company or something and this record got deleted let's delete that record delete it delete from tbo person one their person ID is equal to one so when I delete that row you see that okay I have Tom and Sarah who is number two and number three now let's say there is another person called Todd and I want to add him to our database when I insert him and when we select the data back look at that Todd gets a person idea for it doesn't use reuse the ID that is not there you know there is a gap here number one record is basically not present and when I insert a new record I want to reuse that old value is that possible absolutely okay there are several ways to do that let's see first of all how to supply now let's say for example I'm gonna insert maybe another record Jane and I want to issue him person ID of one is that possible absolutely okay let's pass in person ID here and see what's gonna happen when I try to insert that record remember person ID is an identity column you don't have to supply a value for that it will be computed by sequel server automatically however here since we want to reuse that person ID of one with Jane user I am supplying this value explicitly and let's try to insert this and see what happens okay when I press f5 look at that we get an error okay what does the error say an explicit value for the identity column so an ask an explicit value for the identity column in table TBL person one can only be specified when a column list is used and the identity insert is horn so which means you have to do some changes here if you want to explicitly supply a value for an identity column okay first of all you have to turn the identity insert on okay so for this table I want to tell the sequel server okay I'm going to supply the values for identity column explicitly and how do you do that by turning this identity insert on so let's do that first okay so to turn the identity insert on you'll use the set command and what is that identity insert copied that set identity insert so for what I need to set the identity insert for this table TBL person one on all you have to do is this so when I execute this query okay command completed successfully which means we have turned on the identity insert for the stabile person 1 and the next thing that we have to do is when we insert this query look at this if I I mean we have turned the identity and sat on but when I execute this query I get the error again because we have done half a half of it we need to supply a column list as well so in the insert query not just the values I need to specify what these values are for for which column I want this values for person ID and name columns and now when I execute this query you should see that Jane has got ID value of 1 okay now since I have this identity insert turned on is it possible to drop this value and just supply name what happens in that case look at that let's try to you know I'm not gonna pass a value for person ID column and let's say for example I have it I want to insert Martin so I'm just supplying a value for the name column okay but keep in mind we have the identity insert turned on for this table so when I execute that query look at this explicit value must be specified why because you have the identity and set and on so so if you don't want to supply the value explicitly for the identity column then you have to turn off that because you fill the gap so let me turn off so that when I insert the next record it's going to get the next incremented value for that column so when I press f5 it's turned off right now and when we insert this guy now we are gonna get a new ID for that person you know now the sequence continues okay one way to explicitly supply values for identity column is basically to turn on the identity insert cool so we basically have seen so remember if a column is marked as an identity column then the values for this column are automatically generated okay when we insert a new row into the table we don't have to supply values but there might be scenarios where you know you have deleted some records and there are gaps generated and you want to fill those gaps you can temporarily turn off identity insert and then fill those gaps I mean you can turn on identity insert and then fill those gaps and turn off identity insert all right now let's say I have deleted all the rows from this table and I want to reset the identity insert I mean the value for identity column is that possible absolutely let's do one thing let's delete everything from TBL person so I'm gonna delete all the rows from TBL worsen TBL person one table and look at this when you delete all the rows from TBL person table don't assume that it's going to reset the values for identity column okay so when I select rows from the table obviously they would there wouldn't be anything and now when I insert a new row for Martin and when we select that row now look at this is getting the next identity value it's not resetting that value okay so if you have deleted all the rows and you want to reset the identity value is that possible absolutely we have some commands called dbcc database consistency check commands which we'll be talking about in a great detail in a later session but understand at the time being that we have this dbcc command which we can use to reset the identity value and then start again at 0 so how do we do that we use that dbcc check ident check identity basically dbcc check identity for this TBL person reseed that starting at 0 okay starting at 0 so let us see how to what what this command does so I'm gonna say dbcc check identity that's the function and which for which table we want to do that we want to do that for TBL person 1 table and what do we want to do we want to reseed starting at 0 okay look at this when I execute this query what's gonna happen okay so we basically have deleted everything from this table so there's nothing there so when we execute this command look at that you know current identity value 0 current column value 0 now if I am going to insert this more row now what's gonna happen the current identity value is 0 and if you remember we have said its increment value is 1 so for this new row Martin it's gonna get 1 and insert that it's gonna start that again so now when we select the values from TBL person table you should see that Martin has got 1 okay so it's also possible to reset the values for identity columns if you have deleted all the rows in the table by using this dbcc check identity command we'll be talking about dbcc commands in sequel server in a later session on the slide you can find resources for asp.net and C should have interview questions that's it for today thank you for listening have a great day

Channel: kudvenkat

Views: 573,630

Keywords: Identity column, IDENTITY_INSERT, DBCC CHECKIDENT, Reset identity value, table identity reset, identity_insert in sql server, reset identity column sql server, reseed identity column sql server, reseed id column sql server, sql server set identity_insert on, sql turn identity_insert on

Id: aOkFE6NLGCQ

Length: 15min 11sec (911 seconds)

Published: Sat Aug 11 2012