I have a table created like this originally:
CREATE TABLE dbo.XX (
YY int DEFAULT(NULL)
)
If you do this, and then script the table later on, you will get the following code, plus a randomly named default constraint:
CREATE TABLE [dbo].XX([YY] [int] NULL)
GO
ALTER TABLE [dbo].XX ADD DEFAULT (NULL) FOR [YY]
GO
Now is there any real point in specifying DEFAULT (NULL) on a column instead of just leaving it as [YY] [int] NULL in the first place?
There is no need to add a DEFAULT(NULL) to nullable columns.
If data is not supplied to such columns, they will have a NULL.
The only benefit I see is the one Larry Lustig has posted in his comment to the question - it documents the fact that you have not forgotten to add a default to the nullable column.
None that I can think of.
From a programming point of view NULL is the implicit default anyway without needing to (implicitly) create an actual default constraint in sys.objects.
Both of the following will end up inserting NULL without the default constraint.
INSERT INTO [dbo].XX([YY]) VALUES (DEFAULT)
INSERT INTO [dbo].XX([YY]) DEFAULT VALUES
I don't subscribe to the view in the comments that it documents you didn't "forget" to add a default constraint. Nullable columns rarely have default constraints in my experience so this would just add a load of unneeded clutter.
The more important thing to include is the column nullability - as below - so it is clear to future readers that the column is nullable and so it doesn't depend on ANSI_NULL_DFLT session options what you actually end up with.
CREATE TABLE dbo.XX (
YY int NULL
)
NULLon a column instead of just leaving it as[yy] [INTEGER]in the first place?