Sql Server 2008 R2 table columns and default values

Question

What is the point of default values in a column of a table? According to http://technet.microsoft.com/en-us/library/ms187872.aspx

You can specify a default value that will be entered in the column in SQL Server 2012 by using SQL Server Management Studio or Transact-SQL. If you do not assign a default value and the user leaves the column blank, then:

If you set the option to allow null values, NULL will be inserted into the column.

If you do not set the option to allow null values, the column will remain blank, but the user will not be able to save the row until they supply a value for the column.

I don't get it! If you don't allow nulls on a column surely it should insert a default value in that column if not supplied by the user. So why doesn't it?

Is your question what is the point of default values, or that you are trying to use them are are unsuccessful? — Kevin DiTraglia
– Kevin DiTraglia, Commented Aug 28, 2013 at 16:15
As your statement says -- that only applies if you have not specified a default value -- what exactly is your point? It can't insert a default value if none was specified. — Hogan
– Hogan, Commented Aug 28, 2013 at 16:15
Well if you do supply a default on a column which does not allow nulls then leave the column blank it wont insert a the default value. — Paul
– Paul, Commented Aug 28, 2013 at 16:21

Raphaël Althaus · Accepted Answer · 2013-08-28 16:22:42Z

7

You must have missed a part of the doc.

You can specify a default value that will be entered in the column in SQL Server 2012 by using SQL Server Management Studio or Transact-SQL. If you do NOT assign a default value and the user leaves the column blank, then: If you set the option to allow null values, NULL will be inserted into the column. If you do not set the option to allow null values, the column will remain blank, but the user will not be able to save the row until they supply a value for the column.

So if you DO assign a default value, it will be used as assignment when user leaves the column blank.

The "little bit weird thing" is that the doc explains first what will happen if you don't use the DEFAULT (which is probably not what you're waiting for : you'd like to know what will happen when you use it). Have also been confused at my first "too fast" reading.

edited Aug 28, 2013 at 16:22

answered Aug 28, 2013 at 16:15

Raphaël Althaus

60.6k6 gold badges106 silver badges127 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

ypercubeᵀᴹ Over a year ago

The doc explains first what will happen if you do use the DEFAULT: "You can specify a default value that will be entered in the column in SQL Server 2012" but in a single line and has much more explanation for what happens if you don't :)

Raphaël Althaus Over a year ago

@ypercube well, yes, but it's partially wrong... It should be at least something like that "You can specify a default value that will be entered in the column in Sql Server 2012 if you leave the column blank"

Collectives™ on Stack Overflow

Sql Server 2008 R2 table columns and default values

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related