title
Identity Column in SQL Server - Part 7

description
In this video we will learn 1. What is Identity column 2. Creating an Identity column 3. Supply explicit values for identity columns using IDENTITY_INSERT 4. Reset identity value using DBCC CHECKIDENT command Text version of the video http://csharp-video-tutorials.blogspot.com/2012/08/identity-column-in-sql-server-part-7.html Healthy diet is very important both for the body and mind. If you like Aarvi Kitchen recipes, please support by sharing, subscribing and liking our YouTube channel. Hope you can help. https://www.youtube.com/channel/UC7sEwIXM_YfAMyonQCrGfWA/?sub_confirmation=1 Slides http://csharp-video-tutorials.blogspot.com/2013/08/part-7-identity-column.html All SQL Server Text Articles http://csharp-video-tutorials.blogspot.com/p/free-sql-server-video-tutorials-for.html All SQL Server Slides http://csharp-video-tutorials.blogspot.com/p/sql-server.html All Dot Net and SQL Server Tutorials in English https://www.youtube.com/user/kudvenkat/playlists?view=1&sort=dd All Dot Net and SQL Server Tutorials in Arabic https://www.youtube.com/c/KudvenkatArabic/playlists

detail
{'title': 'Identity Column in SQL Server - Part 7', 'heatmap': [{'end': 183.111, 'start': 142.686, 'weight': 0.886}], 'summary': "Explores sql server's identity column, covering its definition, creation, and management in tblperson table. it discusses using identity columns for data insertion, setting up identity columns, and managing identity insert in sql server.", 'chapters': [{'end': 66.351, 'segs': [{'end': 66.351, 'src': 'embed', 'start': 21.665, 'weight': 0, 'content': [{'end': 27.409, 'text': "Now, to understand this better, let's look at the existing table that we have been working with in all these parts.", 'start': 21.665, 'duration': 5.744}, {'end': 38.693, 'text': 'we have this table called tblPerson and if you look at this tblPerson table here, it has got four columns id, name, email, gender, id and age.', 'start': 29.11, 'duration': 9.583}, {'end': 45.915, 'text': "and if you look at the id column here, it's a primary key column but it's not an identity column.", 'start': 38.693, 'duration': 7.222}, {'end': 48.455, 'text': "How do I know it's not an identity column?", 'start': 46.555, 'duration': 1.9}, {'end': 56.485, 'text': 'If you go to the properties of that particular column In the properties window, you see that identity.', 'start': 48.815, 'duration': 7.67}, {'end': 57.485, 'text': 'it is set to false.', 'start': 56.485, 'duration': 1}, {'end': 59.507, 'text': "So it's not an identity column.", 'start': 57.905, 'duration': 1.602}, {'end': 66.351, 'text': 'So if a column is not an identity column, then in SQL Server, you will have to supply a value for that.', 'start': 59.887, 'duration': 6.464}], 'summary': 'The tblperson table has four columns: id, name, email, and gender, with id not being an identity column.', 'duration': 44.686, 'max_score': 21.665, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/aOkFE6NLGCQ/pics/aOkFE6NLGCQ21665.jpg'}], 'start': 0.389, 'title': 'Sql server - understanding identity column', 'summary': 'Delves into the concept of an identity column in sql server, covering its definition, creation, explicit value assignment, and resetting, illustrated through the tblperson table and its non-identity id column.', 'chapters': [{'end': 66.351, 'start': 0.389, 'title': 'Sql server - understanding identity column', 'summary': 'Discusses the concept of an identity column in sql server, including its definition, creation, explicit value assignment, and resetting, using the example of the tblperson table and its id column, which is not an identity column.', 'duration': 65.962, 'highlights': ['Definition of an identity column in SQL Server The chapter explains the concept of an identity column and its properties in SQL Server, highlighting the need to supply a value if a column is not an identity column.', 'Example of tblPerson table and its id column The tblPerson table and its id column are used as examples to illustrate the difference between an identity column and a non-identity column in SQL Server.', 'Importance of explicit value assignment for non-identity columns It emphasizes the requirement of supplying a value for non-identity columns in SQL Server, showcasing the properties window to demonstrate the absence of identity setting for the id column in the tblPerson table.']}], 'duration': 65.962, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/aOkFE6NLGCQ/pics/aOkFE6NLGCQ389.jpg', 'highlights': ['Definition of an identity column in SQL Server The chapter explains the concept of an identity column and its properties in SQL Server, highlighting the need to supply a value if a column is not an identity column.', 'Example of tblPerson table and its id column The tblPerson table and its id column are used as examples to illustrate the difference between an identity column and a non-identity column in SQL Server.', 'Importance of explicit value assignment for non-identity columns It emphasizes the requirement of supplying a value for non-identity columns in SQL Server, showcasing the properties window to demonstrate the absence of identity setting for the id column in the tblPerson table.']}, {'end': 480.773, 'segs': [{'end': 138.044, 'src': 'embed', 'start': 112.626, 'weight': 0, 'content': [{'end': 117.269, 'text': "For example, if you have a web application, let's say you are registering as a customer.", 'start': 112.626, 'duration': 4.643}, {'end': 121.172, 'text': "you don't provide your customer ID through that registration form.", 'start': 117.269, 'duration': 3.903}, {'end': 125.955, 'text': 'That is something we want to have automatically calculated by SQL Server.', 'start': 121.572, 'duration': 4.383}, {'end': 132.061, 'text': 'Okay, so under those circumstances, we can actually make use of the identity column.', 'start': 126.519, 'duration': 5.542}, {'end': 138.044, 'text': "Okay, so if you mark a column as an identity column in SQL Server, you don't have to supply a value for that.", 'start': 132.361, 'duration': 5.683}], 'summary': 'In sql server, an identity column can automatically calculate customer ids, simplifying the registration process.', 'duration': 25.418, 'max_score': 112.626, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/aOkFE6NLGCQ/pics/aOkFE6NLGCQ112626.jpg'}, {'end': 183.111, 'src': 'heatmap', 'start': 142.686, 'weight': 0.886, 'content': [{'end': 149.448, 'text': "Okay, since we already have a table called tblPerson, let's try to create another table called tblPerson1 maybe.", 'start': 142.686, 'duration': 6.762}, {'end': 152.049, 'text': "So I'm going to create this table.", 'start': 149.468, 'duration': 2.581}, {'end': 155.591, 'text': "I'm going to call, maybe we'll call this personId.", 'start': 152.41, 'duration': 3.181}, {'end': 158.789, 'text': "And obviously, it's an integer.", 'start': 157.188, 'duration': 1.601}, {'end': 162.893, 'text': 'Now, if you look at this particular column,', 'start': 159.37, 'duration': 3.523}, {'end': 171.501, 'text': 'select that column and then drag this column properties window and you see that there is something called identity specification.', 'start': 162.893, 'duration': 8.608}, {'end': 176.705, 'text': 'And if you click the plus sign next to that, you see that is identity.', 'start': 171.801, 'duration': 4.904}, {'end': 183.111, 'text': 'Do you want this column to be an identity column? Yes, I want this column to be an identity column.', 'start': 177.166, 'duration': 5.945}], 'summary': 'Creating table tblperson1 with column personid as an integer and setting it as an identity column.', 'duration': 40.425, 'max_score': 142.686, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/aOkFE6NLGCQ/pics/aOkFE6NLGCQ142686.jpg'}, {'end': 211.431, 'src': 'embed', 'start': 183.829, 'weight': 1, 'content': [{'end': 192.311, 'text': '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.', 'start': 183.829, 'duration': 8.482}, {'end': 192.931, 'text': 'What are these?', 'start': 192.391, 'duration': 0.54}, {'end': 198.293, 'text': 'Basically, you are saying, you know, by converting this person ID column into an identity column,', 'start': 193.571, 'duration': 4.722}, {'end': 204.334, 'text': "you're telling SQL Server you want the value for this column to be automatically computed when we insert a new row.", 'start': 198.293, 'duration': 6.041}, {'end': 211.431, 'text': 'Okay, now if you want the value to be automatically computed, You can specify how you want that to be computed.', 'start': 204.574, 'duration': 6.857}], 'summary': 'Explaining how to convert a column into an identity column in sql server for automatic value computation.', 'duration': 27.602, 'max_score': 183.829, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/aOkFE6NLGCQ/pics/aOkFE6NLGCQ183829.jpg'}, {'end': 374.832, 'src': 'embed', 'start': 345.892, 'weight': 3, 'content': [{'end': 350.577, 'text': "And we know that personId is an identity column, so we don't have to supply a value for that.", 'start': 345.892, 'duration': 4.685}, {'end': 353.68, 'text': 'All I have to do is supply the value for name column.', 'start': 351.078, 'duration': 2.602}, {'end': 355.022, 'text': "For example, let's say john.", 'start': 353.7, 'duration': 1.322}, {'end': 361.323, 'text': "And when I execute this query, what's going to happen is, since we specified the seed as 1 and increment as 1,", 'start': 355.819, 'duration': 5.504}, {'end': 368.068, 'text': "it's going to start at 1 and then put person ID as 1 for this user, John.", 'start': 361.323, 'duration': 6.745}, {'end': 369.989, 'text': "So let's execute that.", 'start': 368.908, 'duration': 1.081}, {'end': 374.832, 'text': 'When we select all the rows from this table, look at that, I get this person ID 1.', 'start': 370.429, 'duration': 4.403}], 'summary': 'By setting seed and increment as 1, personid starts at 1, as seen in the example with john.', 'duration': 28.94, 'max_score': 345.892, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/aOkFE6NLGCQ/pics/aOkFE6NLGCQ345892.jpg'}, {'end': 426.853, 'src': 'embed', 'start': 390.303, 'weight': 2, 'content': [{'end': 393.746, 'text': 'Now, so I have marked this column as an identity column.', 'start': 390.303, 'duration': 3.443}, {'end': 395.087, 'text': 'Now, everything is fine.', 'start': 394.086, 'duration': 1.001}, {'end': 401.513, 'text': "I'm inserting new records and, you know, the person ID is automatically being calculated, which is good.", 'start': 395.127, 'duration': 6.386}, {'end': 405.485, 'text': 'Okay, so at the moment we have person ID 1, 2 and 3.', 'start': 402.284, 'duration': 3.201}, {'end': 412.248, 'text': "Let's say for some reason, you know, maybe John has left our company or something and this record got deleted.", 'start': 405.485, 'duration': 6.763}, {'end': 413.908, 'text': "Let's delete that record.", 'start': 412.568, 'duration': 1.34}, {'end': 426.853, 'text': 'Delete from tbl person 1 where person ID is equal to 1.', 'start': 414.969, 'duration': 11.884}], 'summary': 'Column set as identity, person id auto-calculated, 3 records, 1 deleted.', 'duration': 36.55, 'max_score': 390.303, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/aOkFE6NLGCQ/pics/aOkFE6NLGCQ390303.jpg'}], 'start': 66.391, 'title': 'Using and setting identity columns in sql', 'summary': 'Explains the concept of using identity columns in sql server, which simplifies data insertion and eliminates the need for user input. it also discusses setting up an identity column in sql, specifying seed, increment, marking as primary key, inserting records, and reusing old values.', 'chapters': [{'end': 204.334, 'start': 66.391, 'title': 'Using identity columns in sql server', 'summary': 'Explains the concept of using identity columns in sql server to automatically generate unique values for a column, simplifying data insertion and eliminating the need for users to provide the values, with an example demonstrating the process and properties involved in creating an identity column.', 'duration': 137.943, 'highlights': ['The chapter explains the concept of using identity columns in SQL Server to automatically generate unique values for a column, simplifying data insertion and eliminating the need for users to provide the values.', 'An example is given where a new row with multiple columns is inserted, demonstrating the need for providing values for each column, including the use of primary key column for unique identification.', 'The process and properties involved in creating an identity column, such as identity seed and identity increment, are detailed, indicating the automatic computation of values for the column when inserting a new row.']}, {'end': 480.773, 'start': 204.574, 'title': 'Setting identity column in sql', 'summary': 'Discusses setting up an identity column in sql, specifying seed, increment, marking as primary key, inserting records, and reusing old values, demonstrating the process and its implications.', 'duration': 276.199, 'highlights': ['The chapter discusses setting up an identity column in SQL and customizing its seed and increment values, with examples of starting values and increment options. It explains the process of setting up an identity column in SQL and customizing the seed and increment values, such as starting at 1 or a thousand and incrementing by one, two, three, four, five, ten, or hundred.', 'Demonstration of inserting records into a table with an identity column, showing how the column automatically calculates the Person ID and increments it based on the specified values. It demonstrates how to insert records into a table with an identity column, where the Person ID is automatically calculated and incremented based on the specified seed and increment values, such as starting at 1 and incrementing by 1 or custom options like 5.', 'Explanation of reusing old values for the identity column, showcasing the implications of deleting records and inserting new ones, and mentioning the possibility of reusing old values. It explains the implications of reusing old values for the identity column, addressing the scenario of deleting records and inserting new ones, and mentions the possibility of reusing old values for the identity column.']}], 'duration': 414.382, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/aOkFE6NLGCQ/pics/aOkFE6NLGCQ66391.jpg', 'highlights': ['The chapter explains the concept of using identity columns in SQL Server to automatically generate unique values for a column, simplifying data insertion and eliminating the need for users to provide the values.', 'The process and properties involved in creating an identity column, such as identity seed and identity increment, are detailed, indicating the automatic computation of values for the column when inserting a new row.', 'Demonstration of inserting records into a table with an identity column, showing how the column automatically calculates the Person ID and increments it based on the specified values.', 'The chapter discusses setting up an identity column in SQL and customizing its seed and increment values, with examples of starting values and increment options.', 'Explanation of reusing old values for the identity column, showcasing the implications of deleting records and inserting new ones, and mentioning the possibility of reusing old values.']}, {'end': 910.463, 'segs': [{'end': 561.385, 'src': 'embed', 'start': 534.526, 'weight': 0, 'content': [{'end': 538.449, 'text': 'First of all, you have to turn the identity insert on.', 'start': 534.526, 'duration': 3.923}, {'end': 547.121, 'text': "Okay, so for this table, I want to tell to SQL Server, okay, I'm going to supply the values for identity column explicitly.", 'start': 539.318, 'duration': 7.803}, {'end': 550.762, 'text': 'And how do you do that? By turning this identity insert on.', 'start': 547.161, 'duration': 3.601}, {'end': 552.442, 'text': "So let's do that first.", 'start': 551.142, 'duration': 1.3}, {'end': 558.424, 'text': "Okay, so to turn the identity insert on, you'll use the set command.", 'start': 553.023, 'duration': 5.401}, {'end': 560.725, 'text': 'And what is that? Identity insert.', 'start': 558.784, 'duration': 1.941}, {'end': 561.385, 'text': 'Copy that.', 'start': 560.805, 'duration': 0.58}], 'summary': 'Turn on identity insert for sql server table using the set command.', 'duration': 26.859, 'max_score': 534.526, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/aOkFE6NLGCQ/pics/aOkFE6NLGCQ534526.jpg'}, {'end': 728.458, 'src': 'embed', 'start': 688.764, 'weight': 1, 'content': [{'end': 695.09, 'text': 'One way to explicitly supply values for identity column is basically to turn on the identity insert.', 'start': 688.764, 'duration': 6.326}, {'end': 704.659, 'text': '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.', 'start': 698.153, 'duration': 6.506}, {'end': 708.583, 'text': "When we insert a new row into the table, we don't have to supply values.", 'start': 704.679, 'duration': 3.904}, {'end': 716.13, 'text': 'But there might be scenarios where you have deleted some records and there are gaps generated and you want to fill those gaps.', 'start': 709.005, 'duration': 7.125}, {'end': 720.913, 'text': 'You can temporarily turn off identity insert and then fill those gaps.', 'start': 716.37, 'duration': 4.543}, {'end': 727.437, 'text': 'I mean, you can turn on identity insert and then fill those gaps and turn off identity insert.', 'start': 722.234, 'duration': 5.203}, {'end': 728.458, 'text': 'All right.', 'start': 728.197, 'duration': 0.261}], 'summary': 'Turning on identity insert allows filling gaps in identity columns.', 'duration': 39.694, 'max_score': 688.764, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/aOkFE6NLGCQ/pics/aOkFE6NLGCQ688764.jpg'}, {'end': 806.704, 'src': 'embed', 'start': 780.015, 'weight': 3, 'content': [{'end': 787.4, 'text': 'So if you have deleted all the rows and you want to reset the identity value, is that possible? Absolutely.', 'start': 780.015, 'duration': 7.385}, {'end': 795.746, 'text': "We have some commands called DBCC, Database Consistency Check commands, which we'll be talking about in a great detail in a later session.", 'start': 787.46, 'duration': 8.286}, {'end': 806.704, 'text': '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 zero.', 'start': 795.766, 'duration': 10.938}], 'summary': 'Dbcc command can reset identity value to start again at zero.', 'duration': 26.689, 'max_score': 780.015, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/aOkFE6NLGCQ/pics/aOkFE6NLGCQ780015.jpg'}], 'start': 481.26, 'title': 'Sql identity columns', 'summary': 'Discusses the implications of supplying a value for an identity column in sql server and the process of managing identity insert, including turning it on and off, explicitly supplying values, filling gaps, and resetting values using dbcc commands.', 'chapters': [{'end': 533.605, 'start': 481.26, 'title': 'Understanding identity columns in sql', 'summary': 'Explains the implications of explicitly supplying a value for an identity column in sql server, leading to an error due to the identity insert being off, which necessitates changes in order to explicitly supply a value for an identity column.', 'duration': 52.345, 'highlights': ['When trying to explicitly supply a value for an identity column in SQL Server, an error occurs due to the identity insert being off.', 'The error message specifies that an explicit value for the identity column in table TBL, Person 1 can only be specified when a column list is used and the identity insert is on.', 'This situation necessitates changes in order to explicitly supply a value for an identity column.']}, {'end': 910.463, 'start': 534.526, 'title': 'Managing identity insert in sql server', 'summary': 'Discusses the process of managing identity insert in sql server, including turning identity insert on and off, explicitly supplying values for identity columns, filling gaps in identity values, and resetting identity values using dbcc commands.', 'duration': 375.937, 'highlights': ['Identity Insert: Turning on and off The process of turning on and off the identity insert for a table is explained, with the demonstration of using the set command to turn on identity insert for a specific table.', 'Explicitly Supplying Values for Identity Columns The importance of specifying column names when inserting values for identity columns is highlighted, with an example of inserting values for the person ID and name columns and the subsequent successful execution of the query.', 'Filling Gaps in Identity Values The scenario of filling gaps in identity values is discussed, emphasizing the temporary turning off of identity insert, filling the gaps, and then turning it back on.', "Resetting Identity Values Using DBCC Commands The process of resetting identity values using DBCC commands is demonstrated, with the specific example of using 'dbcc checkident' to reseed the starting value at zero for a table, resulting in the subsequent insertion of a new row with the reset identity value."]}], 'duration': 429.203, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/aOkFE6NLGCQ/pics/aOkFE6NLGCQ481260.jpg', 'highlights': ['The process of turning on and off the identity insert for a table is explained.', 'The importance of specifying column names when inserting values for identity columns is highlighted.', 'The scenario of filling gaps in identity values is discussed, emphasizing the temporary turning off of identity insert, filling the gaps, and then turning it back on.', "The process of resetting identity values using DBCC commands is demonstrated, with the specific example of using 'dbcc checkident' to reseed the starting value at zero for a table."]}], 'highlights': ['The process of turning on and off the identity insert for a table is explained.', 'The importance of specifying column names when inserting values for identity columns is highlighted.', 'The scenario of filling gaps in identity values is discussed, emphasizing the temporary turning off of identity insert, filling the gaps, and then turning it back on.', "The process of resetting identity values using DBCC commands is demonstrated, with the specific example of using 'dbcc checkident' to reseed the starting value at zero for a table.", 'The chapter explains the concept of using identity columns in SQL Server to automatically generate unique values for a column, simplifying data insertion and eliminating the need for users to provide the values.', 'The process and properties involved in creating an identity column, such as identity seed and identity increment, are detailed, indicating the automatic computation of values for the column when inserting a new row.', 'Demonstration of inserting records into a table with an identity column, showing how the column automatically calculates the Person ID and increments it based on the specified values.', 'The chapter discusses setting up an identity column in SQL and customizing its seed and increment values, with examples of starting values and increment options.', 'Explanation of reusing old values for the identity column, showcasing the implications of deleting records and inserting new ones, and mentioning the possibility of reusing old values.', 'Definition of an identity column in SQL Server The chapter explains the concept of an identity column and its properties in SQL Server, highlighting the need to supply a value if a column is not an identity column.', 'Example of tblPerson table and its id column The tblPerson table and its id column are used as examples to illustrate the difference between an identity column and a non-identity column in SQL Server.', 'Importance of explicit value assignment for non-identity columns It emphasizes the requirement of supplying a value for non-identity columns in SQL Server, showcasing the properties window to demonstrate the absence of identity setting for the id column in the tblPerson table.']}