slow-max-query.sql

/*****************************************************************************
Copyright (c) 2017 SQL Workbooks LLC
Terms of Use: https://sqlworkbooks.com/terms-of-service/
Contact: help@sqlworkbooks.com
*****************************************************************************/

IF DB_ID('MAXPROBLEM') IS NOT NULL
BEGIN
    use master;

    ALTER DATABASE MAXPROBLEM SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE MAXPROBLEM;
END

CREATE DATABASE MAXPROBLEM
GO


SET XACT_ABORT ON;
SET ANSI_NULLS ON;
SET ANSI_PADDING OFF;
SET QUOTED_IDENTIFIER ON;
GO

USE MAXPROBLEM
GO


CREATE TABLE dbo.UserDatabaseTable (
RowID INT IDENTITY(1,1),
CharColumn CHAR(13) NOT NULL,
CONSTRAINT PK_UserDatabaseTablePK PRIMARY KEY CLUSTERED (RowID)
);
GO

RAISERROR('Add one million rows to user table...', 1, 1) WITH NOWAIT;
--This query adapted from pattern attributed 
--to Itzik Ben-Gan in https://sqlperformance.com/2013/01/t-sql-queries/generate-a-set-1
WITH e1(n) AS
(
	SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL 
	SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL 
	SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0
), 
e2(n) AS (SELECT 0 FROM e1 CROSS JOIN e1 AS b), 
e3(n) AS (SELECT 0 FROM e1 CROSS JOIN e2), 
e4(n) AS (SELECT 0 FROM e1 CROSS JOIN e3), 
e5(n) AS (SELECT 0 FROM e1 CROSS JOIN e4), 
e6(n) AS (SELECT 0 FROM e1 CROSS JOIN e5)
INSERT dbo.UserDatabaseTable (CharColumn ) 
SELECT TOP (1000000) 'foo' FROM e6;
GO

CREATE INDEX ix_UserDatabaseTable_CharColumn on dbo.UserDatabaseTable (CharColumn);
GO

/* This query is the slow one */
SELECT MAX(CharColumn) FROM dbo.UserDatabaseTable;
GO

/* This query is the fast one */
SELECT TOP (1) CharColumn FROM dbo.UserDatabaseTable ORDER BY CharColumn DESC;
GO


--SELECT OBJECT_NAME(object_id) as table_name,
--    * 
--FROM sys.columns
--WHERE object_id = OBJECT_ID('UserDatabaseTable') 
--    and name='CharColumn';
--GO

--both of these fix the problem:
--ALTER TABLE dbo.UserDatabaseTable ALTER COLUMN CharColumn CHAR(13) NULL;
--GO

--ALTER TABLE dbo.UserDatabaseTable ALTER COLUMN CharColumn CHAR(13) NOT NULL;
--GO