asked
Setting an int column as an Identity column
I've been at my current employer for several years. The database that we use was set up all those years ago by someone who left us quite a long time ago. For the most part, I like what he did. But there's one thing he did which I've never understood. At the root of our database schema is one table, called **ClientInformation**. This has the basic demographic information about the client. From off of that some several other tables which specify additional information relevant to our business with the client (I won't go into here, as it isn't pertinent). However, the very odd thing that my boss did, way back when he first set everything up, was to not use an identity column for the int column that is the primary key for **ClientInformation**. I don't know why he did that, but he did. The primary key column, which is an int, is called "ClientNumber". My boss, way back then, wanted to start the numbering of clients with 100000. I didn't know SQL Server then, as good as I do now, but I wasn't setting things up then. I believe it is possible, when setting up a new table, to specify the initial value and the step value of an identity column for a table. My guess is that my boss didn't know about that, back then, and so he made it complicated. What he did was created a different table, a 1 row, 1 column table, to track what the last client number that was issued, was. What is worse, is he put it into a different database. So, we've got one database with all of the client information and related data in it, but another database with various odds and ends, which has, amoung other things, this 1 row, 1 column table that is used to track what the last client number issued is. I'd like to simplify things by just making that ClientNumber column in the **ClientInformation** table, an identity column, but picking up with the next int value. How do I do that on an established table?
A good starting point may be this article on the main SSC site: [Changing a Non-IDENTITY column to IDENTITY and vice versa][1] Another to help you understand it a bit would be this:[How do I... Reseed a SQL Server identity column?][2] [1]:
http://www.sqlservercentral.com/articles/T-SQL/61979/ [2]:
http://blogs.techrepublic.com.com/datacenter/?p=406
@Blackhawk-17 These are very useful links. I wish I knew about them so I would not have to re-create the wheel by typing my answer below. Too bad that somehow the system says that I ran out of votes for today (and thus cannot up vote your answer until tomorrow) though I can swear that there is no way that I already voted 30 times.
Hello @Blackhawk-17. I've got a follow-up question for you (and everyone else) concerning the "Changing a Non-IDENTITY column to IDENTITY and vice verse". In the section for SQL before SQL Server 2005, in Method 2, it mentions, "Alter all the objects with schemabinding if any" and later, "Recreate functions and views with schemabinding". I'm not familiar with schemabinding, so I had to look it up. I have no idea if any of our views or functions use schemabinding to the table I'm trying to alter by changing the INT column to an IDENTITY column. How do I find how?
You can try the following: SELECT [object_id] FROM sys.all_sql_module WHERE [is_schema_bound] = 1 Join it to sys.system_objects to get the name or inline an OBJECT_NAME function.
Unfortunately, I do not know the way to alter the table like this directly. Here is one way which begin to show how to make it work, but it does not include any implications which might be caused by existing references between ClientInformation and other existing tables. The usual way to achieve what you want goes like this: begin tran; create table dbo.ClientInformationTemp ( ClientNumber int not null identity(100000, 1), -- other columns definitions from original table ); go -- important, allow manual inserts of the values which -- will eventually become working on auto pilot set identity_insert dbo.ClientInformationTemp on; go -- insert data from original to the temp table, including -- identity column. This is possible because identity_insert -- on the table is temporarily set to on :) insert into dbo.ClientInformationTemp ( ClientNumber --, all other columns ) select ClientNumber --, all other columns from dbo.ClientInformation; go -- set the identity_insert setting back to normal (off) set identity_insert dbo.ClientInformationTemp off; go -- drop the original table and remane the temp one to the original drop table dbo.ClientInformation go execute sp_rename N'dbo.ClientInformationTemp', N'ClientInformation', 'object'; go commit tran; From this point on, every new insert will auto-generate correct value for the ClinetNumber, and the latter will not have to be included in the insert statements. By the way, this way of using so-called seed tables used to be pretty common in the past. I never understood why people had to do it. The only explanation could be either lack of knowledge or usage of some obscure databases which do not have identity readily available. Oleg
Maybe your boss had a good reason for doing this. IDENTITY is a great way to generate surrogate keys. It's not a good idea to use an IDENTITY column for storing a meaningful business key however, because you lose the ability to update the column and don't have the degree of control that is often required for business keys. ClientNumber sounds to me like it might be a business key. I would not want to make it an IDENTITY column. Although maybe I would add an IDENTITY column as a surrogate key for that table if it needed one.
@Rod When considering a primary key column(s), identity is a good choice because because it immediately complies with requirement for a PK values to be never changing for existing records and ever-increasing for new records. Same can be applied to using newsequentialid() default for GUID column. These prevent the page splits when new records are inserted. The term "surrogate keys" means that they are pretty much meaningless for business users who would like to have the key to embed some sort of self-explaining information. For example, they would prefer employee's last name plus first name rather than some obscure (surrogate) employee id. Frequently, designers opt to implement surrogate keys because these are best compliant with key requirements and also have some unique "natural" columns as part of other index. This is what **dportas** means saying "I would add an identity column as a surrogate key for that table if it needed one"
Thank you for the explanation of surrogate keys, Oleg. Then I'd have to say our client number field is surrogate, because it has no meaning at all, apart from the record it is in and the client identified in that record. Nothing from the client is used to generate the client number.
