CREATE TABLE - setting a Default Value for a field

Question

Using SQL 2008 R2 SP1

I am migrating from SQL 2005.  On 2005, I could set the default value like this:

[COLOR] [varchar](50) NOT NULL DEFAULT (''''),

Is this format not supported on 2008 R2 ?  I guess not since I get errors using this syntax.

Is it correct that 2008 R2 only support this syntax?

[COLOR] [varchar](50) NOT NULL,

ALTER TABLE [dbo].[MYTABLE] ADD  CONSTRAINT [DF_MYTABLE_COLOR]  DEFAULT ('''') FOR [COLOR];

Thanks for any ideas,

Tom

---

MisterT99

Answer 1

I have the following syntax which I've recently used for our new tables creation:

CREATE TABLE dbo.ExportColumnHeader (
	ColumnHeaderId INT IDENTITY(1, 1) NOT NULL
	,ExportJobId INT NOT NULL
	,ColumnIndex SMALLINT NOT NULL
	,ColumnLabel VARCHAR(50) NOT NULL CONSTRAINT DEF_ExportColumnHeader_ColumnLabel DEFAULT ''
	,ColumnDataType TINYINT NOT NULL CONSTRAINT DEF_ExportColumnHeader_ColumnDataType DEFAULT 1 CONSTRAINT CK_ExportColumnHeader_ColumnDataType CHECK (
		ColumnDataType IN (
			1
			,2
			,3
			)
		)
	,ColumnFormat VARCHAR(50) NOT NULL CONSTRAINT DEF_ExportColumnHeader_ColumnFormat DEFAULT ''
	,GroupByColumn TINYINT NOT NULL CONSTRAINT DEF_ExportColumnHeader_GroupByColumn DEFAULT 1 CONSTRAINT CK_ExportColumnHeader_GroupByColumn CHECK (
		GroupByColumn IN (
			0
			,1
			,2
			)
		)
	,CONSTRAINT PK_ExportColumnHeader_ColumnHeaderId PRIMARY KEY CLUSTERED (ColumnHeaderID)
	)

So, as you see, I have constraints directly in the create table code but I give names to the constraints.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 2

Naomi,

Thanks for the example.

Best wishes,

Tom

---

MisterT99

Answer 3

Using SQL 2008 R2 SP1

I am migrating from SQL 2005.  On 2005, I could set the default value like this:

[COLOR] [varchar](50) NOT NULL DEFAULT (''''),

Is this format not supported on 2008 R2 ?  I guess not since I get errors using this syntax.

It is perfectly allowed to do the defaults by specifying the default value in that manner.  For example, the following runs without error in 2008R2 SP1

Create Table Foo(ID int primary key, [COLOR] [varchar](50) NOT NULL DEFAULT (''''), OtherData decimal(25));

So I presume your error is coming from something else.  If you could post the whole CREATE TABLE statement that is getting the error, we could probably help you find why you are getting an error.

Tom