September 15, 2011 at 7:15 pm
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
September 15, 2011 at 9:01 pm
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
September 15, 2011 at 9:16 pm
I want to do it with a script
thanks
September 15, 2011 at 9:20 pm
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
September 15, 2011 at 9:22 pm
I am sorry but i did not understand "PS:- dont tell me you know the exact name as well"
September 15, 2011 at 9:25 pm
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
September 15, 2011 at 11:18 pm
SQLTestUser (9/15/2011)
I am trying to add a null column and set it to a default value of 0with
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
September 19, 2011 at 4:30 pm
SQLTestUser (9/15/2011)
I am trying to add a null column and set it to a default value of 0with
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'
September 19, 2011 at 4:43 pm
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
September 19, 2011 at 4:47 pm
Need to include "WITH VALUES" on the default add.
Section 'H' at this link: http://msdn.microsoft.com/en-us/library/ms190273.aspx
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
Viewing 10 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy