SqlQuantumLeap

T-SQL script to compare case-insensitive techniques

Jan 30th, 2019
363
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. /****************************************************************************************************************
  2.     This SQL pertains to the following DBA.StackExchange answer:
  3.     https://stackoverflow.com/questions/1224364/sql-server-ignore-case-in-a-where-expression/54431309#54431309
  4.  
  5.     By: Solomon Rutzky ( https://SqlQuantumLeap.com/ )
  6.     Created on: 2019-01-30
  7.     Last Modified on: 2019-01-30
  8.     URL of this script: https://pastebin.com/4MabaHKe
  9.  ****************************************************************************************************************/
  10.  
  11. PRINT 'This script is not meant to execute all at once!';
  12. PRINT 'Please highlight and execute each section individually.';
  13. GO
  14. -- https://sqlquantumleap.com/2018/12/28/prevent-full-script-execution-understanding-and-using-parseonly-and-noexec/
  15. SET PARSEONLY ON;
  16. GO
  17.  
  18.  
  19. ---------------------------------------------------
  20. -- PART 1: SETUP
  21. ---------------------------------------------------
  22.  
  23.  
  24. USE [tempdb];
  25. -- DROP TABLE #Data;
  26. CREATE TABLE #Data
  27. (
  28.   [ID] INT NOT NULL IDENTITY(1, 1) PRIMARY KEY,
  29.   [Stuff] VARCHAR(100) COLLATE Latin1_General_100_CS_AS
  30. );
  31.  
  32. -- TRUNCATE TABLE #Data;
  33. DECLARE @NumRows INT;
  34. SET @NumRows = 1000000;
  35.  
  36. INSERT INTO #Data ([Stuff])
  37.   SELECT TOP (@NumRows) NEWID()
  38.   FROM   master.sys.all_columns ac1
  39.   CROSS JOIN   master.sys.all_columns ac2
  40.  
  41.  
  42. CREATE NONCLUSTERED INDEX [IX_#Data_Stuff]
  43.   ON #Data([Stuff] ASC)
  44.   WITH (FILLFACTOR = 100);
  45.  
  46. -- CHECKPOINT;
  47.  
  48.  
  49.  
  50. ---------------------------------------------------
  51. -- PART 2: Get value to paste into WHERE clause of queries in Part 3
  52. ---------------------------------------------------
  53.  
  54. SELECT * FROM #Data WHERE [ID] = 600123;
  55. -- 6BE5D7B8-2AB2-4693-88FF-900089A61668
  56.  
  57.  
  58.  
  59. ---------------------------------------------------
  60. -- PART 3: RUN TESTS!!!
  61. ---------------------------------------------------
  62. GO
  63.  
  64.  
  65. SET STATISTICS TIME ON;
  66. SELECT *
  67. FROM   #Data dt
  68. WHERE  dt.[Stuff] = '6BE5D7B8-2AB2-4693-88FF-900089A61668';
  69. SET STATISTICS TIME OFF;
  70. -- 1 row
  71. -- Index Seek
  72. -- CPU time = 0 ms,  elapsed time = 0 ms.
  73.  
  74.  
  75.  
  76. SET STATISTICS TIME ON;
  77. SELECT *
  78. FROM   #Data dt
  79. WHERE  dt.[Stuff] = LOWER('6BE5D7B8-2AB2-4693-88FF-900089A61668');
  80. SET STATISTICS TIME OFF;
  81. -- 0 rows
  82. -- Index Seek
  83. -- CPU time = 0 ms,  elapsed time = 0 ms.
  84.  
  85.  
  86.  
  87. SET STATISTICS TIME ON;
  88. SELECT *
  89. FROM   #Data dt
  90. WHERE  UPPER(dt.[Stuff]) = UPPER('6BE5D7B8-2AB2-4693-88FF-900089A61668');
  91. SET STATISTICS TIME OFF;
  92. -- 1 row
  93. -- Index Scan (parallel)
  94. -- CPU time = 392 ms,  elapsed time = 140 ms.
  95.  
  96.  
  97.  
  98. SET STATISTICS TIME ON;
  99. SELECT *
  100. FROM   #Data dt
  101. WHERE  dt.[Stuff] = '6BE5D7B8-2AB2-4693-88FF-900089A61668' COLLATE Latin1_General_100_CI_AS;
  102. SET STATISTICS TIME OFF;
  103. -- 1 row
  104. -- Index Scan (parallel)
  105. -- CPU time = 234 ms,  elapsed time = 85 ms.
  106. -- CPU time = 172 ms,  elapsed time = 89 ms.
  107.  
  108.  
  109.  
  110. GO
  111. SET PARSEONLY OFF;
  112. RAISERROR('Ooops. Looks like you hit F5, Control-E, or the "Execute" button.', 16, 1);
  113. GO
RAW Paste Data