Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
COUNT(*) vs COUNT(1) in SQL Server
-- Copyright Data Geekery GmbH
--
-- Licensed under the Apache License, Version 2.0 (the "License");
-- you may not use this file except in compliance with the License.
-- You may obtain a copy of the License at
--
-- http://www.apache.org/licenses/LICENSE-2.0
--
-- Unless required by applicable law or agreed to in writing, software
-- distributed under the License is distributed on an "AS IS" BASIS,
-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
-- See the License for the specific language governing permissions and
-- limitations under the License.
--
-- This version displays relative execution times (fastest execution = 1)
-- According to our understanding of SQL Server licensing, such benchmark results may be published
-- as they cannot be compared to other databases and do not provide absolute time values
CREATE TABLE t (i INT);
WITH d (i) AS (
SELECT 1
UNION ALL
SELECT i + 1
FROM d
WHERE i < 100
)
INSERT INTO t SELECT row_number() OVER (ORDER BY (SELECT 1)) FROM d AS d1, d AS d2, d AS d3;
DECLARE @ts DATETIME;
DECLARE @repeat INT = 100;
DECLARE @r INT;
DECLARE @i INT;
DECLARE @dummy VARCHAR;
DECLARE @s1 CURSOR;
DECLARE @s2 CURSOR;
DECLARE @results TABLE (
run INT,
stmt INT,
elapsed DECIMAL
);
SET @r = 0;
WHILE @r < 5
BEGIN
SET @r = @r + 1
SET @s1 = CURSOR FOR
-- Paste statement 1 here
SELECT COUNT(*) FROM t;
SET @s2 = CURSOR FOR
-- Paste statement 2 here
SELECT COUNT(1) FROM t;
SET @ts = current_timestamp;
SET @i = 0;
WHILE @i < @repeat
BEGIN
SET @i = @i + 1
OPEN @s1;
FETCH NEXT FROM @s1 INTO @dummy;
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM @s1 INTO @dummy;
END;
CLOSE @s1;
END;
DEALLOCATE @s1;
INSERT INTO @results VALUES (@r, 1, DATEDIFF(ms, @ts, current_timestamp));
SET @ts = current_timestamp;
SET @i = 0;
WHILE @i < @repeat
BEGIN
SET @i = @i + 1
OPEN @s2;
FETCH NEXT FROM @s2 INTO @dummy;
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM @s2 INTO @dummy;
END;
CLOSE @s2;
END;
DEALLOCATE @s2;
INSERT INTO @results VALUES (@r, 2, DATEDIFF(ms, @ts, current_timestamp));
END;
SELECT 'Run ' + CAST(run AS VARCHAR) + ', Statement ' + CAST(stmt AS VARCHAR) + ': ' + CAST(CAST(elapsed / MIN(elapsed) OVER() AS DECIMAL(10, 5)) AS VARCHAR)
FROM @results
UNION ALL
SELECT ''
UNION ALL
SELECT 'Copyright Data Geekery GmbH'
UNION ALL
SELECT 'https://www.jooq.org/benchmark';
DROP TABLE t;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.