/****************************************************************************************************************
    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