ISNULL changes to NOT to allow NULLs

Hi folks. Actually, it is pretty old trick/feature I would like to show you. Let's say we have a table called 'Test1' with one column as VARCHAR(10) which allows inserting NULLs. Well,we want to change this to NOT allow NULLs and I'm also going to change datatype for this column at the same time ,so just for testing let's assume that 'Test1' does not contain alphabetic values.


CREATE TABLE Test1
(
Col1 varchar(10) NOT NULL,
)
INSERT INTO Test1 VALUES('1')

EXEC sp_rename 'Test1', 'Test1_Old'
GO
SELECT ISNULL(CAST(Col1 AS int), 0) AS Col1 ---Makes Col1 not to allow NULLs
INTO Test1
FROM Test1_Old

DROP TABLE Test1_Old

Make sure that you do not have duplicates, so you can create a constraint on the column.
/*
ALTER TABLE Test1
ADD CONSTRAINT PK_Test1
PRIMARY KEY (Col1)
*/
GO