Identities Tips

One nice feature of SQL Server that is used quite a bit is the use of identity columns. This function gives you a simple way of creating a unique value for every row in your table. Adding a new column and making it an identity column is an easy thing to do as well as dropping an existing column that is an identity column, but how can you modify an existing column to make it an identity column or remove the identity property from an existing column?

In a previous tip, Using Identity Insert to keep SQL Server database table keys in sync, we discussed how to move data between like tables when the table has an identity column. The biggest drawback when doing this is that you need to specify each column of the table for the INSERT and also make sure the columns on the SELECT match up as well. Doing this for one or two tables is not that hard, but what if you have several tables or an entire database that you need to move and all of the tables have identity columns? In this tip we take a look at a simple way to create the INSERT command using the system meta data.

I am designing a table and I have decided to create an auto-generated primary key value as opposed to creating my own scheme or using natural keys. I see that SQL Server offers globally unique identifiers (GUIDs) as well as identities to create these values. What are the pros and cons of these approaches?

I have a table that makes use of integer based sequential values (identity based or otherwise) and I need to determine what gaps exist within this table. What is the best way to determine these gaps using a set-based approach (i.e. no cursor operations)?

I recently had a business situation where I needed to generate a unique "handle" on groups of rows in my database. This handle (in reality, a grouping identifier) was to be used to logically lock these rows from further processing and to allow them to be picked up for processing by an offline routine as a group. Exploring a number of potential solutions, I finally settled on one that required no manual maintenance on my part. I also wanted to use an approach that would allow me to use built-in SQL Server functionality versus having to write a lot of code.

One nice feature that SQL Server offers is the ability to have an automatic counter or identity value column in your table. You have the ability to have one identity value column per table and this is a simple way of having an automated process of generating a sequential number for each of the records in your table. Once in awhile you may delete the data in the table and what to reset the value or maybe you just want to know what the next number will be. So how do you figure this out?

I have a database table that has a lot of data already in the table and I need to add a new column to this table to include a new sequential number. In addition to adding the column I also need to populate the existing records with an incremental counter what options are there to do this?

When inserting a row into a database table that contains an identity column, I need a way to capture the identity value generated by the database engine after it inserts the row into this table. What can I use to capture this value while also making sure this value is accurate?

One feature of SQL Server that is used in probably just about every database is identity columns. Identity columns allow you to create an auto generated number to keep your data unique. In a previous tip Managing and maintaining identity values we talked about using the DBCC CHECKIDENT command to see current values as well as to reseed values. This is great if you want to do this one table at a time, but is there another way of doing this for all tables?

One thing that DBAs are often faced with is moving data from one database to another database to populate lookup tables or some other key table in your database. This may be to keep a test or development environment in synch or maybe there is a need to populate like databases on other servers with the same data values.

I am designing a table and I have decided to create an auto-generated primary key value as opposed to creating my own scheme or using natural keys. I see that SQL Server offers globally unique identifiers (GUIDs) as well as identities to create these values. What are the pros and cons of these approaches?

One nice feature of SQL Server that is used quite a bit is the use of identity columns. This function gives you a simple way of creating a unique value for every row in your table. Adding a new column and making it an identity column is an easy thing to do as well as dropping an existing column that is an identity column, but how can you modify an existing column to make it an identity column or remove the identity property from an existing column?

One thing that DBAs are often faced with is moving data from one database to another database to populate lookup tables or some other key table in your database. This may be to keep a test or development environment in synch or maybe there is a need to populate like databases on other servers with the same data values.

I have a database table that has a lot of data already in the table and I need to add a new column to this table to include a new sequential number. In addition to adding the column I also need to populate the existing records with an incremental counter what options are there to do this?

In a previous tip, Using Identity Insert to keep SQL Server database table keys in sync, we discussed how to move data between like tables when the table has an identity column. The biggest drawback when doing this is that you need to specify each column of the table for the INSERT and also make sure the columns on the SELECT match up as well. Doing this for one or two tables is not that hard, but what if you have several tables or an entire database that you need to move and all of the tables have identity columns? In this tip we take a look at a simple way to create the INSERT command using the system meta data.

One feature of SQL Server that is used in probably just about every database is identity columns. Identity columns allow you to create an auto generated number to keep your data unique. In a previous tip Managing and maintaining identity values we talked about using the DBCC CHECKIDENT command to see current values as well as to reseed values. This is great if you want to do this one table at a time, but is there another way of doing this for all tables?

When inserting a row into a database table that contains an identity column, I need a way to capture the identity value generated by the database engine after it inserts the row into this table. What can I use to capture this value while also making sure this value is accurate?

I recently had a business situation where I needed to generate a unique "handle" on groups of rows in my database. This handle (in reality, a grouping identifier) was to be used to logically lock these rows from further processing and to allow them to be picked up for processing by an offline routine as a group. Exploring a number of potential solutions, I finally settled on one that required no manual maintenance on my part. I also wanted to use an approach that would allow me to use built-in SQL Server functionality versus having to write a lot of code.

I have a table that makes use of integer based sequential values (identity based or otherwise) and I need to determine what gaps exist within this table. What is the best way to determine these gaps using a set-based approach (i.e. no cursor operations)?

One nice feature that SQL Server offers is the ability to have an automatic counter or identity value column in your table. You have the ability to have one identity value column per table and this is a simple way of having an automated process of generating a sequential number for each of the records in your table. Once in awhile you may delete the data in the table and what to reset the value or maybe you just want to know what the next number will be. So how do you figure this out?

In a previous tip, Using Identity Insert to keep SQL Server database table keys in sync, we discussed how to move data between like tables when the table has an identity column. The biggest drawback when doing this is that you need to specify each column of the table for the INSERT and also make sure the columns on the SELECT match up as well. Doing this for one or two tables is not that hard, but what if you have several tables or an entire database that you need to move and all of the tables have identity columns? In this tip we take a look at a simple way to create the INSERT command using the system meta data.

I am designing a table and I have decided to create an auto-generated primary key value as opposed to creating my own scheme or using natural keys. I see that SQL Server offers globally unique identifiers (GUIDs) as well as identities to create these values. What are the pros and cons of these approaches?

I have a database table that has a lot of data already in the table and I need to add a new column to this table to include a new sequential number. In addition to adding the column I also need to populate the existing records with an incremental counter what options are there to do this?

One nice feature of SQL Server that is used quite a bit is the use of identity columns. This function gives you a simple way of creating a unique value for every row in your table. Adding a new column and making it an identity column is an easy thing to do as well as dropping an existing column that is an identity column, but how can you modify an existing column to make it an identity column or remove the identity property from an existing column?

When inserting a row into a database table that contains an identity column, I need a way to capture the identity value generated by the database engine after it inserts the row into this table. What can I use to capture this value while also making sure this value is accurate?

I recently had a business situation where I needed to generate a unique "handle" on groups of rows in my database. This handle (in reality, a grouping identifier) was to be used to logically lock these rows from further processing and to allow them to be picked up for processing by an offline routine as a group. Exploring a number of potential solutions, I finally settled on one that required no manual maintenance on my part. I also wanted to use an approach that would allow me to use built-in SQL Server functionality versus having to write a lot of code.

One feature of SQL Server that is used in probably just about every database is identity columns. Identity columns allow you to create an auto generated number to keep your data unique. In a previous tip Managing and maintaining identity values we talked about using the DBCC CHECKIDENT command to see current values as well as to reseed values. This is great if you want to do this one table at a time, but is there another way of doing this for all tables?

I have a table that makes use of integer based sequential values (identity based or otherwise) and I need to determine what gaps exist within this table. What is the best way to determine these gaps using a set-based approach (i.e. no cursor operations)?

One nice feature that SQL Server offers is the ability to have an automatic counter or identity value column in your table. You have the ability to have one identity value column per table and this is a simple way of having an automated process of generating a sequential number for each of the records in your table. Once in awhile you may delete the data in the table and what to reset the value or maybe you just want to know what the next number will be. So how do you figure this out?

One thing that DBAs are often faced with is moving data from one database to another database to populate lookup tables or some other key table in your database. This may be to keep a test or development environment in synch or maybe there is a need to populate like databases on other servers with the same data values.