What' s the best way to change a column size?

[TheBugSlayer]

I need to increase the size of two varchar columns, each in a different table. I tried to do it in SSMS (2005), at anoff-peak hour however I have concerns and ran into a timeout issue.
I got the warning about affected tables (checked the box, so that' s fine), saved the change script then proceeded. The tables that were going to be modified as a consequence of resizing the field in my table, which has around 4 million records, were modified but modifying the field size timed out...Initially I thought of logging on in single user mode but since the only problem I have is the time out and I had stopped all other applications connection to the DB I kind of discarded concurrency as a problem.

Foreign keys were dropped prior so I recreated them from the change script I had saved before. The fields in the foreign key constraints have nothing to do with the one I am modifying. At this time I am wondering if there is a better way to make the change. The field the size of which I am increasing is not involved in any constraints...

If you know of a better way please let me know.

MCITP SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)

 

[gmmastros]

Forget about trying to do this in the table designer part of SSMS. When you modify a table it actually creates a new table, copies the data over, drops the original, and finally renames the new to be the same as the original. This is why you are getting timeouts.

Instead, you should open a query window and run an "alter table alter column command". You should look up the syntax, but the basic command is...

Alter Table YourTableName Alter Column YourColumnName VarChar(35)

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

To see what I mean, you can do this...

Right click a table, and click Design. Change one of the columns data type (ex: varchar(20) to varchar(30)). On the top menu, click "Table Designer" -> "Generate Change Script". You will see the TSQL that SSMS generates for you.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[TheBugSlayer]

Hi George. I know exactly what you' re saying as I always save scripts prior to making the changes. In addition to creating the temp table, copying data and renaming it and drops and recreates indexes all over the place. It just never has taken this long before. But then again, this server here needs a LOT of work. I will definitely use the command later on. Thanks.

MCITP SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)

 

[TheBugSlayer]

This is what I came up with:

WITH TablesToChange_CTE
AS
(
SELECT so.name, so.type, sc.max_length
FROM sys.columns sc
LEFT JOIN sys.objects so ON so.object_id = sc.object_id
WHERE sc.name LIKE 'ClientID' AND 
      sc.max_length <> 50 AND 
      so.type = 'U' AND
      so.name NOT LIKE 't%'
--ORDER BY 1
)
SELECT 
  'ALTER TABLE dbo.' + Name + 
  ' ALTER COLUMN ClientID VARCHAR(50) NOT NULL' FROM TablesToChange_CTE ORDER BY Name

That produce the ALTER TABLE statement for all tables containing the field that needed to be modified. The biggest table took 38 seconds to update. The rest completed in under a second.

Thank you.

MCITP SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)

 

[gmmastros]

In your original question...

The fields in the foreign key constraints have nothing to do with the one I am modifying.

Do you mean... this ClientID column is not included in any foreign key constraints? I'm also curious as to why you have a varchar(50) id column?

The problem I have is... It seems like this is a key (primary & foreign). It also seems like this is a value that can change. I say this because you want to make the column wider. I'm a big fan of surrogate (integer) keys. I would store the varchar "ClientIdNumber" in a single table, and have ClientId be an integer in all the tables. This will make your tables and indexes narrower which will save you space and improve performance.

Please understand that I am not criticizing you (or your database schema). I'm just trying to understand the reasoning.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[TheBugSlayer]

George, I am 100% with you.

But as it oftens happens I did not take part in the DB design...I am new and starting to find flaws and inconsistencies, some of which you mentioned. For instance, ClientID is not defined as a key in spite of what it' s name implies; there is a surrogate key (identity, integer) on the Client table named ID. This ID field is the referenced in the FK constraints, of course.

What I am saying is that I am modifying ClientID, which is not a key, however SSMS still is dropping the FK constraints (which reference Client.ID) to recreate them and the indexes later. One of the biggest problems here is redundancy: the other tables have both a ClientKey, which is a FK based on Client.ID AND a ClientID field! So, two fields to refer to the same Client record. As far as ClientID changing, no, it does not change. However, the company grows by acquisition...each new business they acquire defines a different format and max size for ClientID. I hope I made it clearer.

MCITP SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)