/****************************************************************************************************************
This SQL pertains to the following DBA.StackExchange answer:
https://stackoverflow.com/questions/1224364/sql-server-ignore-case-in-a-where-expression/54431309#54431309
By: Solomon Rutzky ( https://SqlQuantumLeap.com/ )
Created on: 2019-01-30
Last Modified on: 2019-01-30
URL of this script: https://pastebin.com/4MabaHKe
****************************************************************************************************************/
PRINT 'This script is not meant to execute all at once!';
PRINT 'Please highlight and execute each section individually.';
GO
-- https://sqlquantumleap.com/2018/12/28/prevent-full-script-execution-understanding-and-using-parseonly-and-noexec/
SET PARSEONLY ON;
GO
---------------------------------------------------
-- PART 1: SETUP
---------------------------------------------------
USE [tempdb];
-- DROP TABLE #Data;
CREATE TABLE #Data
(
[ID] INT NOT NULL IDENTITY(1, 1) PRIMARY KEY,
[Stuff] VARCHAR(100) COLLATE Latin1_General_100_CS_AS
);
-- TRUNCATE TABLE #Data;
DECLARE @NumRows INT;
SET @NumRows = 1000000;
INSERT INTO #Data ([Stuff])
SELECT TOP (@NumRows) NEWID()
FROM master.sys.all_columns ac1
CROSS JOIN master.sys.all_columns ac2
CREATE NONCLUSTERED INDEX [IX_#Data_Stuff]
ON #Data([Stuff] ASC)
WITH (FILLFACTOR = 100);
-- CHECKPOINT;
---------------------------------------------------
-- PART 2: Get value to paste into WHERE clause of queries in Part 3
---------------------------------------------------
SELECT * FROM #Data WHERE [ID] = 600123;
-- 6BE5D7B8-2AB2-4693-88FF-900089A61668
---------------------------------------------------
-- PART 3: RUN TESTS!!!
---------------------------------------------------
GO
SET STATISTICS TIME ON;
SELECT *
FROM #Data dt
WHERE dt.[Stuff] = '6BE5D7B8-2AB2-4693-88FF-900089A61668';
SET STATISTICS TIME OFF;
-- 1 row
-- Index Seek
-- CPU time = 0 ms, elapsed time = 0 ms.
SET STATISTICS TIME ON;
SELECT *
FROM #Data dt
WHERE dt.[Stuff] = LOWER('6BE5D7B8-2AB2-4693-88FF-900089A61668');
SET STATISTICS TIME OFF;
-- 0 rows
-- Index Seek
-- CPU time = 0 ms, elapsed time = 0 ms.
SET STATISTICS TIME ON;
SELECT *
FROM #Data dt
WHERE UPPER(dt.[Stuff]) = UPPER('6BE5D7B8-2AB2-4693-88FF-900089A61668');
SET STATISTICS TIME OFF;
-- 1 row
-- Index Scan (parallel)
-- CPU time = 392 ms, elapsed time = 140 ms.
SET STATISTICS TIME ON;
SELECT *
FROM #Data dt
WHERE dt.[Stuff] = '6BE5D7B8-2AB2-4693-88FF-900089A61668' COLLATE Latin1_General_100_CI_AS;
SET STATISTICS TIME OFF;
-- 1 row
-- Index Scan (parallel)
-- CPU time = 234 ms, elapsed time = 85 ms.
-- CPU time = 172 ms, elapsed time = 89 ms.
GO
SET PARSEONLY OFF;
RAISERROR('Ooops. Looks like you hit F5, Control-E, or the "Execute" button.', 16, 1);
GO
Comments