?
Solved

I want to add a new column with a default value.

Posted on 2011-05-10
7
Medium Priority
?
297 Views
Last Modified: 2012-08-14
I want to add a new column with a default value.

When I add the column will the default value be set the existing rows or will it only apply to new rows?

        ALTER TABLE [dbo].[shop]
        ADD [hasCSV] BIT CONSTRAINT DF_hasCSV DEFAULT 0  
0
Comment
Question by:Mr_Shaw
  • 3
  • 3
7 Comments
 
LVL 18

Accepted Solution

by:
sventhan earned 1000 total points
ID: 35727864
< When I add the column will the default value be set the existing rows or will it only apply to new rows?

Only new rows. You've to update the existing rows manually.
0
 
LVL 9

Assisted Solution

by:mayank_joshi
mayank_joshi earned 1000 total points
ID: 35727875
if you define NOT NULL on the column the existing rows will be updated atomatically.
0
 
LVL 18

Expert Comment

by:sventhan
ID: 35727876
Sorry. If its a new column you should be fine with this statement...

 ALTER TABLE [dbo].[shop]
        ADD [hasCSV] BIT CONSTRAINT DF_hasCSV DEFAULT 0
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 9

Expert Comment

by:mayank_joshi
ID: 35727886
on the other hand if you define NULL on the column existing rows will not be updated and the default value will be set only on new rows.


ALTER TABLE {TABLENAME} 
ADD {COLUMNNAME} {TYPE} {NULL|NOT NULL} 
CONSTRAINT {CONSTRAINT_NAME} DEFAULT {DEFAULT_VALUE}

Open in new window


0
 
LVL 9

Expert Comment

by:mayank_joshi
ID: 35727926
what i m trying to say is:-

 ALTER TABLE [dbo].[shop]
 ADD [hasCSV] BIT NOT NULL CONSTRAINT DF_hasCSV DEFAULT 0   

Open in new window

this will update the existing rows.
0
 
LVL 18

Expert Comment

by:sventhan
ID: 35727946
okay. if you use this

ALTER TABLE [dbo].[shop]
        ADD [hasCSV] BIT CONSTRAINT DF_hasCSV DEFAULT 0


you should do this ...

update [dbo].[shop]  set hascsv = 0 where hascsv is null

Sorry for the confusion....

0
 

Author Closing Comment

by:Mr_Shaw
ID: 35727975
thanks
0

Featured Post

Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Suggested Courses

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question