>>>>>>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. Cascade updates is a performance hit. By extension, meaningful pks becoming keys means the invoice number could be a substring of the grandchild key of a grandchild table. That cascade update of a sub-string is very difficult.

It is not about meaning full keys. It is about static vs changeable keys. A key generated by the database could get meaning in the outside world.
As an example invoice number should be static no matter what. However the key does have meaning in the outside world. It is just that the database is authoritive on the invoice number.

If do not know whether it is still the case, but Navision is using meaningless but visible keys. The keys are absolutely static, but they can be used for reference in the outside world (e.g. correspondence with clients).

However, I would never make an invoice number field an Identity field. Many clients want to have some control over the numbering (e.g. beginning with the year)

There is so much confusion about this topic, and no-one seems to get it right.