Skip to content

Instantly share code, notes, and snippets.

@nchammas
Created May 29, 2012 05:24
Show Gist options
  • Save nchammas/2822774 to your computer and use it in GitHub Desktop.
Save nchammas/2822774 to your computer and use it in GitHub Desktop.
Date functions like GETDATE() and SYSUTCDATETIME() are folded by the optimizer.
SET NOCOUNT ON;
USE [tempdb];
GO
-- Try substituting GETDATE() for SYSUTCDATETIME() everywhere in this script
-- and you will get the same results.
CREATE TABLE a_table (
an_integer INT DEFAULT (1)
, a_string CHAR(10) DEFAULT ('ohmigodwut')
, a_datetime DATETIME2(7) DEFAULT (SYSUTCDATETIME())
);
INSERT INTO a_table
DEFAULT VALUES;
GO
-- Use the power of exponential growth.
-- This is the fastest way to create a massive table *from nothing*.
-- Number of rows created will be 2^x, where x is the number after the GO.
INSERT INTO a_table (an_integer)
SELECT an_integer
FROM a_table;
GO 25 -- This will produce >33 million rows, enough to let a few seconds pass while we populate or update this table.
UPDATE a_table
SET a_datetime = SYSUTCDATETIME();
-- the SYSUTCDATETIME() above is folded by the optimizer
SELECT DISTINCT a_datetime
FROM a_table;
DECLARE @start DATETIME2 = SYSUTCDATETIME();
-- insert a large number of rows at once
INSERT INTO a_table (an_integer)
SELECT an_integer
FROM a_table;
DECLARE @finish DATETIME2 = SYSUTCDATETIME();
-- it took many seconds to insert them, right?
SELECT DATEDIFF(SECOND, @start, @finish) AS insert_duration_s;
-- functions called by DEFAULTs are also folded
-- otherwise, you'd expect more than two values below
SELECT DISTINCT a_datetime
FROM a_table;
DROP TABLE a_table;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment