FROM NULL TO NOT NULL, BUT WITH DEFAULT

informatika

I have a suggestion for a new option. Sometimes it happens, that we create a table like this:

CREATE TABLE Test ( ID INT IDENTITY ( 1, 1 ), Field1 INT NOT NULL, Field2 INT NULL )

INSERT INTO Test ( Field1, Field2 ) VALUES( 1, 1 )
INSERT INTO Test ( Field1 ) VALUES( 2 )

SELECT * FROM Test

As you can see, Field2 can be NULL and we also have values in the database which are NULL. But because of new specifications or whatever, we need the table like this:

CREATE TABLE Test ( ID INT IDENTITY ( 1, 1 ), Field1 INT NOT NULL, Field2 INT NOT NULL DEFAULT 0 )

So, for an update, you need to recreate the table. SQL Server Management Studio makes something like this:

CREATE TABLE dbo.Tmp_Test ( ID int NOT NULL IDENTITY (1, 1), Field1 int NOT NULL, Field2 int NOT NULL )  ON [PRIMARY]
GO
ALTER TABLE dbo.Tmp_Test ADD CONSTRAINT DF_Test_Field2 DEFAULT 0 FOR Field2
GO
SET IDENTITY_INSERT dbo.Tmp_Test ON
GO
IF EXISTS(SELECT * FROM dbo.Test) EXEC('INSERT INTO dbo.Tmp_Test (ID, Field1, Field2) SELECT ID, Field1, Field2 FROM dbo.Test WITH (HOLDLOCK TABLOCKX)')
GO
SET IDENTITY_INSERT dbo.Tmp_Test OFF
GO
DROP TABLE dbo.Test
GO
EXECUTE sp_rename N'dbo.Tmp_Test', N'Test', 'OBJECT'

And here is the problem. You cannot insert the values into Tmp_Test, because Field2 does not allow NULL values anymore. But it would be possible, if you would turn the NULL-Value into the Default-Value, for example

SELECT ID, Field1, ISNULL( Field2, 0 /* the new default value */ ) FROM dbo.Test

I know, it is easy to workaround this, with a simple Update-Command before Updating

UPDATE Test SET Field1 = 0 WHERE Field1 IS NULL

But perhaps it is worth some consideration to implement this feature.

Thank you,

Michael Kriegner

Eddie D

Thank for your post into the forum.

I have added your suggestions to an existing Feature Request created regarding the same topic. The reference for this feature request is SC-1428.

Hopefully you will see the requested feature in a future version of SQL Compare.

Many Thanks
Eddie

David Atkinson

SQL Compare and SQL Source Control can now work side by side to achieve what you describe. You can commit your own custom script to source control (in this case you would add the UPDATE statement) and SQL Compare would be able to reuse this script in future deployments.

For more information visit:

http://www.red-gate.com/MessageBoard/vi ... 1312#51312

David Atkinson
Product Manager
Red Gate Software