Adding a null column and setting it to a default value of 0

Question

Adding a null column and setting it to a default value of 0

SQLTestUser

Hall of Fame

Points: 3997
More actions
September 15, 2011 at 7:15 pm

#247860

I am trying to add a null column and set it to a default value of 0
with
ALTER TABLE X add acolumn smallint NULL Default '0'
but when i look at the table the column acolumn is still displaying null for its values eventhoug the default value has been set to 0
do i have to update the values to 0 again can it not be done with a single alter statement
the alternative was to
alter it then update it
please let me know if anyone has a better idea
thanks

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

crazy4sql SSCoach Points: 19590 More actions · Answer 1

right click on your database -- select design--select required column--in column properties, general tab you will see the "default value or binding". Mention 0 here.

----------
Ashish

SQLTestUser Hall of Fame Points: 3997 More actions · Answer 2

SQLTestUser

Hall of Fame

Points: 3997

September 15, 2011 at 9:16 pm

#1382361

I want to do it with a script

thanks

crazy4sql SSCoach Points: 19590 More actions · Answer 3

i thought you smart enough to generate the script after my previous comment.

ALTER TABLE [yourtable] ADD CONSTRAINT [constraintname] DEFAULT ((0)) FOR [columnname]

PS:- dont tell me you know the exact name as well.

----------
Ashish

SQLTestUser Hall of Fame Points: 3997 More actions · Answer 4

I am sorry but i did not understand "PS:- dont tell me you know the exact name as well"

crazy4sql SSCoach Points: 19590 More actions · Answer 5

I am sorry but i did not understand "PS:- dont tell me you know the exact name as well"

one day you will, till then dont reply 😀 😀 😀

----------
Ashish

SQLRNNR SSC Guru Points: 281334 More actions · Answer 6

SQLTestUser (9/15/2011)
I am trying to add a null column and set it to a default value of 0
with
ALTER TABLE X add acolumn smallint NULL Default '0'
but when i look at the table the column acolumn is still displaying null for its values eventhoug the default value has been set to 0
do i have to update the values to 0 again can it not be done with a single alter statement
the alternative was to
alter it then update it
please let me know if anyone has a better idea
thanks

You should update the values to 0 after running that Alter statement.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

OTF SSCertifiable Points: 6104 More actions · Answer 7

SQLTestUser (9/15/2011)
I am trying to add a null column and set it to a default value of 0
with
ALTER TABLE X add acolumn smallint NULL Default '0'
but when i look at the table the column acolumn is still displaying null for its values eventhoug the default value has been set to 0
do i have to update the values to 0 again can it not be done with a single alter statement
the alternative was to
alter it then update it
please let me know if anyone has a better idea
thanks

I believe the UPDATE to the default value will only be done for you if you specify NOT NULL when creating the column.

ALTER TABLE X add acolumn smallint NOT NULL Default '0'

Lowell SSC Guru Points: 323518 More actions · Answer 8

ALTER TABLE X add acolumn smallint NULL Default 0 WITH VALUES

the optional WITH VALUES populates existing rows with the default value in the new column

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 9

Need to include "WITH VALUES" on the default add.

Section 'H' at this link: http://msdn.microsoft.com/en-us/library/ms190273.aspx

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA