Created
May 29, 2012 05:24
-
-
Save nchammas/2822774 to your computer and use it in GitHub Desktop.
Date functions like GETDATE() and SYSUTCDATETIME() are folded by the optimizer.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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