Default Value For Columns

Question

Default Value For Columns

Nunnery

Ten Centuries

Points: 1293
More actions
June 8, 2010 at 10:53 am

#387100

Is there a limit on the number of default values you can assign to your table? Or are you allowed to specify each column in your table with a default value? Thanks!

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

no limit the way you are thinking...you can have one default per column if you want...a table with 200 columns could potentially each have a default value for each of those columns.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Jetro Ten Centuries Points: 1257 More actions · Answer 2

No, there is no limit 😀

declare @sqlstr NVARCHAR(MAX)

SET @sqlstr = N'CREATE TABLE defaultTester ('

DECLARE @i int = 0

WHILE @i < 1024

BEGIN

SET @sqlstr = @sqlstr + 'col' + cast(@i as varchar) + ' int default(' + cast(@i as varchar) + '),'

SET @i+=1;

print @i

END

set @sqlstr = substring(@sqlstr, 1, len(@sqlstr)-1)

set @sqlstr = @sqlstr +')'

print @sqlstr

EXEC sp_executesql @sqlstr

insert into defaultTester (col1) values (1)

select * from defaultTester

drop table defaultTester

-----------------------
SQL Server Database Copy Tool at Codeplex

Nunnery Ten Centuries Points: 1293 More actions · Answer 3

Great, thank you both for your help!

And is this true for SQL Server 2000 and 2005 as well?