SQL Server 2000 - Default Value for varchar fields

Question

I have a sql server nvarchar field with a "Default Value or Binding" of empty string. It also happens to be a not null field.

Does this mean that there is no default or that it is a default of a string with no characters in it.

If I don't insert a value, will it insert with an empty string or fail with a "not null" error?

Answer 1

The default is a blank (empty) string.

If you don't provide a value, the insert will be successful and the value will be blank, not null.

Answer 2

If you have a true empty string as a default, then it will autopopulate with a 0 length string.

You should be careful to ensure it is a 0 length string and not nothing though. If for instance you are looking in the table builder gui for SSMS and it shows a blank for "Default Value or Binding", that means that there is no default value and an insert will fail if it is not populated. If you want it to have a 0 length string, populate it with '' (two single-quotes together with nothing in between.)

Answer 3

Default value for a column is just that - sql server will put that value when you dont supply one for the column. The value in the column will be an empty string. Not null error will not happen

Answer 4

Its the same as (assuming data is the col in question):

create table #t (id int, data varchar(100) not null default(''))

So:

insert into #t (id) values (1) 
insert into #t (id,data) values (2,default) 
insert into #t (id,data) values (3, 'allowed') 

select * from #t

will return

1
2
3 allowed 

and ..

insert into #t (id,data) values (1, null) 
-- will error

Answer 5

So is there no way to get the null insert to work without error?

insert into #t (id,data) values (1, null) -- will error

I have (temp) create table & using insert into (temp) table for 3 stored procedures. Some return NULL's & I'd like the field to allow these values but use a default, so in the above, I would expect:

1 '' to be inserted for (1,NULL), not an error.