I agree with Sergey, varchar(40) and default seems like a better choice. In addition, why do you need to keep PRINT @ExecuteSQL lines?
They are not return to the client anyway.
Also, if the Client table is big, the script may take time.
>The following script usually takes a flash to execute:
>
>
>SET ANSI_WARNINGS OFF
>DECLARE @ExecuteSql nvarchar(4000)
>IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Client' AND COLUMN_NAME = 'test4')
> BEGIN
> BEGIN TRY
> PRINT @ExecuteSql
> SET @ExecuteSql = N'ALTER TABLE [Client] ADD [test4] char(40)'
> EXEC sp_executesql @ExecuteSql
> SET @ExecuteSql = N'UPDATE [Client] SET [test4]='''''
> PRINT @ExecuteSql
> EXEC sp_executesql @ExecuteSql
> END TRY
> BEGIN CATCH
> Print ERROR_MESSAGE()
> END CATCH
> END
> ELSE
> BEGIN
> BEGIN TRY
> SET @ExecuteSql = N'ALTER TABLE [Client] ADD [test4-Temporary] char(40)'
> PRINT @ExecuteSql
> EXEC sp_executesql @ExecuteSql
> SET @ExecuteSql = N'UPDATE [Client] SET [test4-Temporary]=test4'
> PRINT @ExecuteSql
> EXEC sp_executesql @ExecuteSql
> SET @ExecuteSql = N'ALTER TABLE [Client] DROP COLUMN [test4]'
> PRINT @ExecuteSql
> EXEC sp_executesql @ExecuteSql
> PRINT 'Renaming the field'
> EXEC sp_RENAME 'Client.test4-Temporary', 'test4' , 'COLUMN'
> END TRY
> BEGIN CATCH
> Print ERROR_MESSAGE()
> END CATCH
> END
>
>
>Basically, this adds a field into a table.
>
>I have been trying to find what was causing a latency recently. Basically, this now takes 6 seconds to execute.
>
>I cannot understand what caused the latency on that specific server. If I execute that script directly in SSMS on that server, it is fast. If I execute the same script through .NET, by the use of SMO, with something like loServer.ConnectionContext.ExecuteNonQuery(lcScript), it takes longer. But, when this is done from .NET, the server is not the same box as the Web server. If I execute the same .NET code to execute other scripts, it is fast. It is just that one. Something in there is conflicting with something else and it causes some delay in the execution. Anyone would know what it could be?
If it's not broken, fix it until it is.
My Blog