>>>>>Hi,
>>>>>
>>>>>I need to change the type of a PK column of a table from type NUMERIC (6,0) to INT (When I created this table I never thought that the number of records would grow to be more than 999,999 but now I need more).
>>>>>
>>>>>When I change the type in the SSMS it works, but it takes quite a bit of time on a table of about 600,000 rows. I thought that changing the type using the script would be faster. And I tried the following script:
>>>>>

>>>>>ALTER TABLE dbo.MyTable
>>>>>ALTER COLUMN wo_number INT 
>>>>>

>>>>>
>>>>>But I get the following errors:
>>>>>

>>>>>he object 'PK_MyTable' is dependent on column 'wo_number'.
>>>>>Msg 5074, Level 16, State 1, Line 1
>>>>>The index 'open_wo' is dependent on column 'wo_number'.
>>>>>

>>>>>That is, the PK index and another index do not allow the script to change the type. Does it mean that the only way to change the type of this PK is via SSMS?
>>>>
>>>>Just FYI, rather than having different sizes of primary keys, just use Int for every primary key. Don't forget to start the numbering at -2 billion. The primary key is not supposed to have any meaning to anyone. Candidate keys might have meaning, but are not used for joining. Further, you'd never have run into this problem.
>>>
>>>I agree that Int is the best choice for every primary key (I am refactoring this application that started in Clipper that didn't have Int type). But I am not 100% in agreement that the PK could not have a meaning. Thank you.
>>
>>Just as having int be the PK type everywhere as a standard is a good thing, so is having meaningless keys as a standard. The point to them is to prevent any kind of cascade updates. You have an invoice number on the invoice header. You have a meaningless key on the invoice header. You use that key to join to line items. The invoice number can be changed all you want with no effect on the line items.
>
>The problem I see with NOT using PK for certain Meaningful field is as follows. Say I have a PK field that is not meaningful and user NEVER sees it. So this is done. But I need to have another Int field that is Meaningful and user will see (e.g. Invoice Number, in your example). Then I cannot make this Meaningful field to be an Identity field and therefore have to have some code that will increment the value. Which is the extra code that can be avoided if the field is the Identity field. And if it is identity field why not to make it PK.

Wrong. You make the invoice field autoincrement/IDENTITY as well as the primary key. Cascade updates is a performance hit. By extension, meaningful pks becoming keys means the invoice number could be a substring of the key of a grandchild table. That cascade update of a sub-string is very difficult.

Also consider that the identity is a shortcut to not using a default value. The value placed in that field could be a guid - by using a default value. It would be logical and consistent to use default value to place a value in the primary key every time. Some places have rules governing generating the invoice number. That makes it a business rule. You'd have to use default value to generate the invoice number and again it would be a bad choice for a key.