Just a day ago while working with database Jr. Developer asked me question how to add column along with column constraint. He also wanted to specify the name of the constraint. The newly added column should not allow NULL value. He requested my help as he thought he might have to write many lines to achieve what was requested.
It is very easy to add column and specify default constraint. I have seen many examples where constraint name is not specified, if constraint name is not specified SQL Server will generate unique name for itself. I prefer to specify my constraint name as per my coding standards. You can read my coding standard here : SQL SERVER Database Coding Standards and Guidelines Complete List Download
ALTER TABLE TestTable
ADD NewCol VARCHAR(50)
CONSTRAINT DF_TestTable_NewCol DEFAULT '' NOT NULL
GO
Reference : Pinal Dave (http://blog.SQLAuthority.com)
Dave,
I think something is wrong with your blog software. The new blog post today is labeled as the 19th, but you posted it today, 3/21. Just wanted you to be aware.
It means that he saved the post at 19th and publish it 21st only
hello sir,
i could not delete a column in a table through ‘ drop column’ option in ‘alter table’ query even though the column is not having any relationships or any other constraints.
please help me.
with regards,
adiraju
Did you get any error? Post the exact code you used
@adiraju
If that column you are trying to drop is used by any derived column, then you cannot drop it.
Example: in Example11 I created a table with out any derived column so I was able to drop column ename.
CREATE TABLE EXAMPLE11( EID INT , ENAME VARCHAR(10))
GO
ALTER TABLE EXAMPLE11
DROP COLUMN ENAME
GO
Result:Command(s) completed successfully.
Example2: In this example I created third column using first and second column, so when I try to drop second column, it gives me an error because second column in used in third column
CREATE TABLE EXAMPLE12 ( COLA INT, COLB INT , COLC AS COLA + COLB )
GO
ALTER TABLE EXAMPLE12
DROP COLUMN COLB
Go
Result: Msg 5074, Level 16, State 1, Line 1
The column ‘colc’ is dependent on column ‘COLB’.
Msg 4922, Level 16, State 9, Line 1
ALTER TABLE DROP COLUMN COLB failed because one or more objects access this column.
Hope this helps.
Thanks,
Imran.
Hi,
Can someone please tell me how to specify a default constraint for an existing column?
Thanks in advance!!
Treasa
ALTER TABLE EXAMPLE12
DROP COLUMN COLC
Dear Treasa!
ALTER TABLE TableName WITH NOCHECK
ADD CONSTRAINT DF_DefaultName DEFAULT ‘Default Value’ FOR ColumnName
Check it out !
Thanks. Your suggestion helps me.
Thanks it helps me……..
Thanks it helps me
Thanks a lot, it help me
I have an existing query which return 1000 of rows, i want to add another column and have constant numerical value (2.7 for example). can someone tell me how to do that? many thanks
até que enfim um exemplo simples e eficaz. boa=good!
Thanks for the solution. Works!
Hi Simon
An Example you can test on Adventure works
select Name, GroupName, 2.7 as Constant from HumanResources.Department
thanks
Hi
Thanks for this.
Hi
Thanks for this best tutorial.
Please can anybody tell me how i need to start working on
Application DBA. I mean from basics.
Thanks in advance.
Santhosh;
I suggest you download Microsofts free database software ‘Sql Server Express, and ‘SQL Server Manager Express’ and then search for ‘Beginning, SQL Server’ under Google videos. Microsofts got some good beginner videos as well. … I started with reading a few books on SQL, and use ‘SQL Authority’ as my key source for help on SQL Server queries etc.
good luck
Server Error in ‘/’ Application.
——————————————————————————–
Index (zero based) must be greater than or equal to zero and less than the size of the argument list.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.FormatException: Index (zero based) must be greater than or equal to zero and less than the size of the argument list.
Source Error:
Line 40: cmd.CommandType = System.Data.CommandType.Text;
Line 41: String s = String.Empty;
Line 42: s = String.Format(“insert into Customerdetails2 values(‘{0}’,'{1}’,{2},{3},{4},{5},’{6}’,'{7}’,'{8}’,'{9}’,'{11}’)”,
Line 43: TextBox1.Text, TextBox2.Text, DropDownList1.SelectedItem.ToString(), DropDownList2.SelectedItem.ToString(), TextBox5.Text, TextBox6.Text,
Line 44: TextBox7.Text, TextBox8.Text, TextBox9.Text, TextBox10.Text,TextBox11.Text);
Source File: E:\MURTHY PRO\cusdetails\cusdetails\Default.aspx.cs Line: 42
Stack Trace:
[FormatException: Index (zero based) must be greater than or equal to zero and less than the size of the argument list.]
System.Text.StringBuilder.AppendFormat(IFormatProvider provider, String format, Object[] args) +7485085
System.String.Format(IFormatProvider provider, String format, Object[] args) +107
System.String.Format(String format, Object[] args) +9
cusdetails._Default.Button1_Click(Object sender, EventArgs e) in E:\MURTHY PRO\cusdetails\cusdetails\Default.aspx.cs:42
System.Web.UI.WebControls.Button.OnClick(EventArgs e) +111
System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +110
System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +10
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +13
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +36
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1565
——————————————————————————–
Version Information: Microsoft .NET Framework Version:2.0.50727.4952; ASP.NET Version:2.0.50727.4927
how to store data in sqlsever2008 with default date and time without giving from frontend
Have a column that has a default value of getdate(). You dont need to pass values explicitely
hi ,
how to change null value to not null in a existing table.
thanks in advance..
First you need to update null values with any default values if there are data in the table. Then use alter command
alter table your_table
alter column your_column datatype
hi i wanna two existing tables to add in new database how to do this with schema and data????????
Script the objects and run them in another database. Use import/export wizard to export data
Hai …
i want sql script to add column in sql table with ordinal position specification.
is it possible. if Please help me ..
Do it via Management studio
thanks for this information. I was using alter table table_name add( First_Name varchar(20), Last_Name varchar(20)) … but it shows an syntax error.
The information was quite helpful.Thank you
I have a table with 5 columns and i need to insert my new column after the second column.How do i achieve this.
The ordinal position of the column doesnt matter as long as it is used in the specific order in the select statement. If yo still need it, do it via Management studio
May i know how to add default value to existing table column?
It is not possible. You need to drop the column and add new column with default option
Hi,
I’m using SQL Server 2008. I’m dynamically creating a Table with default value of ‘NP’ for a some columns. that columns may have “Null” string value, so I would like to set the default value(“NP”) for “Null” string while creating a Table. Please help me on this. If this not possible,please let me know.
Thanks
Alexis
How will modify data while creating a table? Have it as default value and remove that column from the insert statement
What will be the performance impact on inserts if we have default columns for a table
I dont think default values will affect performance
This constraint doesnot add the default value in existing rows in the table??…
ALTER TABLE dbo.utl_site ADD AMI_active CHAR(1)
CONSTRAINT DF_utl_site_AMI_active DEFAULT ‘N’ NULL
all of the rows of utl_site still remains NULL not ‘N’
Can u explain please?
Note that it will affect newly inserted data not the old data. You need to write update statement to update old data
How to add new cloumn to table to hold archival status flag
Alter table tablename
add newcolumn datatype
Hello… can i change default to another type? and drop the default?
please help me..
thanks for advance
Ade Ruyani
Hello Sir,
I have a requirement where in I want to add a Column which can calculate values by its own.
What I want to do is I want to add a expression to that column.
I could do that in visual studio using Datatable.columns(index).expression. but it consumes lot of time. please help.
Here’s a small scenario
CREATE TABLE #Temp (RowNo int identity(1,1),Quantity Decimal(18,5) ,ID int, StudentID int)
Insert into #Temp
Select Quantity,ID, StudentID From Student Where ID = 53
Select * from #Temp
Alter Table #Temp Add Calc Decimal(18,5) Default (‘[Quantity] * [ID] * 100′)
Select * from #Temp
Drop Table #Temp
How many rows are there in the database? Also why are you doing it via Visula Studio? Have you tried doing the same in Query Analyser?
Thanks for your valuable time and quick reply.
There are more than 13000 rows for my condition.
Since I already had computed columns in the table and I want to use the same columns for my new Computed column, I put it in code.
But because of the performance issue I need to do that in the back end itself.
i want to add a coloumn name in my table name member aster but it allow only null value otherwise it say it’s not possible try with alter table plz help me
the given error by sql 2005 is :-
‘MemberMaster’ table
- Unable to modify table.
ALTER TABLE only allows columns to be added that can contain nulls, or have a DEFAULT definition specified, or the column being added is an identity or timestamp column, or alternatively if none of the previous conditions are satisfied the table must be empty to allow addition of this column. Column ‘MemberLogin’ cannot be added to non-empty table ‘MemberMaster’ because it does not satisfy these conditions.
help help help
Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword ‘set’.
hi everyone
can you help me how to set grid column
my gode is here
Dim sql As String = “select code,Name,curr as [Currency],LOCShtNM as [Short Name] from cost_center order By code”
Dim cmd As New SqlCommand(sql, conn)
dset.Clear()
Dim adapt As New SqlDataAdapter
adapt.SelectCommand = cmd
adapt.Fill(dset, “cost_center”)
DataGridView1.DataSource = dset
DataGridView1.DataMember = “cost_center”
conn.Close()
DataGridView1.Columns(2).Width = 280
Hi Dave,
I have a query where I need to create a column say Status.
It is a Not-Null column. The table I m gonna add this column, already have some data in it.
So, wen creating the column I gave a Default value as 1.
Now after addition of this column, in the same script file, I dropped the default constraint. It throws like ‘Status’ column not found.
Please help me out
Hi How to add new column for existing table after first column in SQL 2008
I want to set a column with a default value = value of primary key column. How can I do this? I cannot set col2 value as computed column because I may want to override its value later but by default it should be = col1 value of the same table which is a primary key column. Your help will be appreciated.
Thanks!
how to display the recently entered record in table ?
in my table do not have any date time column .pls send querry sir
–To add a column to display date and time
ALTER TABLE [tablename]
ADD [newcol] DATETIME not null default getdate()
–To display the most recent insert
SELECT * FROM [tablename]
WHERE [newcol] =
(
SELECT MAX([newcol])
FROM [tablename]
)
create table od
(
id int not null,
name char(10),
place char(30) DEFAULT ‘New_Delhi’,
dat dateTIME DEFAULT GETDATE()
)
insert into od values(83,’AMIT’,”,”)
select * from od
but
it show’s result
id name place dat
———– ———- —————————— ———————–
83 kk 1900-01-01 00:00:00.000
wrong Date and place is Empty, please solve my problem
If you enter ” for place and date, this means you are inserting blanks, therefore it will not take default values.
Use the following query:
insert into od (id, name) values (83,’AMIT’)
id name place dat
83 AMIT New_Delhi 2013-03-04 09:54:52.047
thanks
also done where not null is added in the place and dat column
[...] Add Column With Default Column Constraint to Table I prefer to specify my constraint name as per my coding standards. It is very easy to add columns and specify default constraints. I have seen many examples where the constraint name is not specified, if constraint name is not specified SQL Server will generate a unique name for itself. [...]
it is very helpful to me for my table generation.