Inserting DEFAULT VALUES

Have you ever worked with a table that has default values accepted for each column? I hope not, but if so, this article is for you.

I was recently working with a table like this for some testing. There wasn’t a “great” way to load thousands of rows into this table without using a loop.

SQL Server 2008 introduced the following optional argument to assist with this, appropriately named: DEFAULT VALUES.

It’s demo time.

I’m going to create a table that will work for only allowing DEFAULT VALUES inserts.

CREATE TABLE dbo.Defaults
(
	Id int NOT NULL IDENTITY
        CONSTRAINT Defaults_PK PRIMARY KEY CLUSTERED,
	String nvarchar (32) NOT NULL
        CONSTRAINT DF_Defaults_String DEFAULT '',
	CreationDate datetime NOT NULL
        CONSTRAINT DF_Defaults_CreationDate DEFAULT CURRENT_TIMESTAMP
);

Our table has three columns, each of which does not require specified preset values.

I can use the following statement to add a single row.

INSERT INTO dbo.Defaults DEFAULT VALUES;

This will give us the following result (based on the time of execution).

IdStringCreationDate
12020-04-20 20:58:35.507

What if I wanted to do this 100,000 times? I could easily just adjust that previous statement to follow with a GO 100000. Like so.

INSERT INTO dbo.Defaults DEFAULT VALUES;
GO 100000

That finished on my machine in about 23 seconds. Using GO in that way requires an execution loop, which means it will go row by row. I think we can do better.

We could also use a MERGE statement to complete this task. It’s absolutely not the way the statement was intended to be used, but it works, nonetheless.

WITH CTE
AS
(
    SELECT TOP (100000) NULL AS Id
    FROM sys.objects AS O
    CROSS JOIN sys.objects AS O2
    CROSS JOIN sys.objects AS O3
)
MERGE INTO dbo.Defaults
USING CTE AS Src
ON dbo.Defaults.Id = Src.Id
WHEN MATCHED THEN 
    UPDATE SET Defaults.String = ''
WHEN NOT MATCHED THEN 
    INSERT DEFAULT VALUES;

In this example, we’re using some CROSS JOINs to the sys.objects view to generate some rows. I normally wouldn’t recommend hitting these system views to perform row generation, but it works in a time crunch. We’re merging our results into our dbo.Defaults table based on a match on the “Id” column. Since our “Id” column would never be NULL, we’ll always hit the “NOT MATCHED” portion of our MERGE statement.

In that example, I was able to populate 100,000 rows in less than one second.

Conclusion

I’m not really sure if this would ever serve a real “need” in a normal environment. I did, however, think it was interesting regardless. Hopefully if you’re doing some testing you’re able to use it too!

Thanks for reading!

Leave a Reply

Your email address will not be published. Required fields are marked *