StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>SET is faster on single runs. You can prove this easily enough. Whether or not it makes a difference is up to you, but I prefer SET, since I don't see the point of SELECT if all the code is doing is an assignment. I prefer to keep SELECT confined to SELECT statements from tables, views, etc.</p> <p>Here is a sample script, with the number of runs set to 1:</p> <pre><code>SET NOCOUNT ON DECLARE @runs int DECLARE @i int, @j int SET @runs = 1 SET @i = 0 SET @j = 0 DECLARE @dtStartDate datetime, @dtEndDate datetime WHILE @runs > 0 BEGIN SET @j = 0 SET @dtStartDate = CURRENT_TIMESTAMP WHILE @j < 1000000 BEGIN SET @i = @j SET @j = @j + 1 END SELECT @dtEndDate = CURRENT_TIMESTAMP SELECT DATEDIFF(millisecond, @dtStartDate, @dtEndDate) AS SET_MILLISECONDS SET @j = 0 SET @dtStartDate = CURRENT_TIMESTAMP WHILE @j < 1000000 BEGIN SELECT @i = @j SET @j = @j + 1 END SELECT @dtEndDate = CURRENT_TIMESTAMP SELECT DATEDIFF(millisecond, @dtStartDate, @dtEndDate) AS SELECT_MILLISECONDS SET @runs = @runs - 1 END </code></pre> <p>RESULTS:</p> <p>Run #1:</p> <p>SET_MILLISECONDS </p> <p>5093</p> <p>SELECT_MILLISECONDS </p> <p>5186</p> <p>Run #2:</p> <p>SET_MILLISECONDS </p> <p>4876</p> <p>SELECT_MILLISECONDS </p> <p>5466</p> <p>Run #3:</p> <p>SET_MILLISECONDS </p> <p>4936</p> <p>SELECT_MILLISECONDS </p> <p>5453</p> <p>Run #4:</p> <p>SET_MILLISECONDS </p> <p>4920</p> <p>SELECT_MILLISECONDS </p> <p>5250</p> <p>Run #5:</p> <p>SET_MILLISECONDS </p> <p>4860</p> <p>SELECT_MILLISECONDS </p> <p>5093</p> <p><strong>Oddly, if you crank the number of runs up to say, 10, the SET begins to lag behind.</strong></p> <p>Here is a 10-run result:</p> <p>SET_MILLISECONDS </p> <p>5140</p> <p>SELECT_MILLISECONDS </p> <p>5266</p> <p>SET_MILLISECONDS </p> <p>5250</p> <p>SELECT_MILLISECONDS </p> <p>5466</p> <p>SET_MILLISECONDS </p> <p>5220</p> <p>SELECT_MILLISECONDS </p> <p>5280</p> <p>SET_MILLISECONDS </p> <p>5376</p> <p>SELECT_MILLISECONDS </p> <p>5280</p> <p>SET_MILLISECONDS </p> <p>5233</p> <p>SELECT_MILLISECONDS </p> <p>5453</p> <p>SET_MILLISECONDS </p> <p>5343</p> <p>SELECT_MILLISECONDS </p> <p>5423</p> <p>SET_MILLISECONDS </p> <p>5360</p> <p>SELECT_MILLISECONDS </p> <p>5156</p> <p>SET_MILLISECONDS </p> <p>5686</p> <p>SELECT_MILLISECONDS </p> <p>5233</p> <p>SET_MILLISECONDS </p> <p>5436</p> <p>SELECT_MILLISECONDS </p> <p>5500</p> <p>SET_MILLISECONDS </p> <p>5610</p> <p>SELECT_MILLISECONDS </p> <p>5266</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload