Alter statement -

[vkEE]

Hi, Well, this is kind of embarrassing, i always used the GUI for altering the database tables in sql server 2005. Now, in sql server 2008, I need to write sql statements to do this. I need to alter the existing column, and set it to not null with default value 0. I got the not null part working, get error on default value.

alter table dbo.mytable
alter column [VendorLevelId] INT Not NULL Default 0

Msg 156, Level 15, State 1, Line 2 Incorrect syntax near the keyword 'Default'.

[Richard Deeming]

Well, that's odd! BOL[^] says you should be able to use alter column ... set default x to set the default value, but it doesn't seem to work. Try using three statements:

ALTER TABLE dbo.mytable
ADD CONSTRAINT DF_mytable_VendorLevelId DEFAULT (0) FOR VendorLevelId;

UPDATE dbo.mytable
SET VendorLevelId = 0
WHERE VendorLevelId Is Null;

ALTER TABLE dbo.mytable
ALTER COLUMN VendorLevelId int NOT NULL;

---

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer

[RedDk]

Here's an example from BOL (2k8):

CREATE TABLE dbo.doc_exy (column_a INT ) ;
GO

INSERT INTO dbo.doc_exy (column_a) VALUES (10) ;
GO

Straightforwardly:

SELECT * FROM dbo.doc_exy

Gets:

10

Now, doing the operation appears to suggest that no default value is ever needed:

ALTER TABLE dbo.doc_exy ALTER COLUMN column_a DECIMAL (5, 2);
GO

SELECT * FROM dbo.doc_exy

Shows what I mean:

10.00

DROP TABLE dbo.doc_exy ;
GO