Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- /****************************************************************************************************************
- 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
Add Comment
Please, Sign In to add comment