USE tempdb DROP DATABASE collationbase go CREATE DATABASE collationbase go USE collationbase go DROP TABLE IF EXISTS words CREATE TABLE words (word nvarchar(50) NOT NULL) go TRUNCATE TABLE words BULK INSERT words FROM 'C:\temp\platsnamn.txt' WITH (DATAFILETYPE = 'char', CODEPAGE = 65001, ROWTERMINATOR='\n') INSERT words (word) SELECT word FROM STHLM.listtest.dbo.usrdictwords WHERE wordno < 150000 go DROP TABLE IF EXISTS worddata CREATE TABLE worddata(id int NOT NULL, word nvarchar(50) NOT NULL, data int NOT NULL, CONSTRAINT pk_worddata PRIMARY KEY(id) ) go TRUNCATE TABLE worddata INSERT worddata (id, word, data) SELECT row_number() OVER (ORDER BY newid()), word, checksum(newid()) % 1000 FROM words CROSS JOIN (VALUES(1), (2), (3), (4), (5), (6), (7), (8), (9), (0)) AS n(n) go DROP TABLE IF EXISTS randomtext CREATE TABLE randomtext(id int NOT NULL, randomtext nvarchar(2000) NOT NULL, CONSTRAINT pk_randomtext PRIMARY KEY (id) ) INSERT randomtext (id, randomtext) SELECT n / 25, string_agg(convert(char(36), newid()) + '*' + lower(convert(char(36), newid())), ' ') FROM NorthNumbers..Numbers WHERE n BETWEEN 1 AND 25000*25 GROUP BY n/ 25 go DROP TABLE IF EXISTS collations CREATE TABLE collations (runorder int NOT NULL, collation sysname NOT NULL, testno tinyint NOT NULL, done bit NOT NULL DEFAULT 0, CONSTRAINT pk_collations PRIMARY KEY (testno, runorder), CONSTRAINT u_collations UNIQUE (collation, testno) ) TRUNCATE TABLE collations INSERT collations (runorder, collation, testno) SELECT row_number() OVER(ORDER BY newid()), name, 1 FROM sys.fn_helpcollations() INSERT collations (runorder, collation, testno) SELECT row_number() OVER(ORDER BY newid()), name, 2 FROM sys.fn_helpcollations() INSERT collations (runorder, collation, testno) SELECT row_number() OVER(ORDER BY newid()), name, 3 FROM sys.fn_helpcollations() INSERT collations (runorder, collation, testno) SELECT row_number() OVER(ORDER BY newid()), name, 4 FROM sys.fn_helpcollations() INSERT collations (runorder, collation, testno) SELECT row_number() OVER(ORDER BY newid()), name, 5 FROM sys.fn_helpcollations() go DROP TABLE IF EXISTS teststmts CREATE TABLE teststmts (id int NOT NULL, label varchar(20) COLLATE Latin1_General_BIN2 NOT NULL, isunicode bit NOT NULL, stmt nvarchar(MAX) NOT NULL, CONSTRAINT pk_teststmts PRIMARY KEY (id), CONSTRAINT u_testsmtts UNIQUE (label, isunicode) ) DROP TABLE IF EXISTS results CREATE TABLE results (collation sysname NOT NULL, testno tinyint NOT NULL, label varchar(20) COLLATE Latin1_General_BIN2 NOT NULL, isunicode bit NOT NULL, tookms int NOT NULL, CONSTRAINT pk_results PRIMARY KEY (collation, testno, label, isunicode), CONSTRAINT fk_results_collations FOREIGN KEY (collation, testno) REFERENCES collations(collation, testno) ) go DELETE teststmts INSERT teststmts (id, label, isunicode, stmt) VALUES (1, 'GROUPBY', 1, 'DECLARE @dummy int SELECT @dummy = COUNT(*) FROM (SELECT word FROM unicodewords GROUP BY word HAVING SUM(data) > 5000) AS x OPTION (HASH GROUP)'), (2, 'GROUPBY', 0, 'DECLARE @dummy int SELECT @dummy = COUNT(*) FROM (SELECT word FROM mbcswords GROUP BY word HAVING SUM(data) > 5000) AS x OPTION (HASH GROUP)'), (3, 'ORDERBY', 1, 'DECLARE @dummy nvarchar(50) ; WITH CTE AS ( SELECT word, row_number() OVER(ORDER BY word) AS rowno FROM unicodewords ) SELECT @dummy = word FROM CTE WHERE rowno = 67212'), (4, 'ORDERBY', 0, 'DECLARE @dummy nvarchar(50) ; WITH CTE AS ( SELECT word, row_number() OVER(ORDER BY word) AS rowno FROM mbcswords ) SELECT @dummy = word FROM CTE WHERE rowno = 67212'), (5, 'LOOPJOIN', 1, 'DECLARE @dummy int SELECT @dummy = SUM(k.data) FROM (SELECT word FROM unicodewords WHERE id BETWEEN 150000 and 250000) d JOIN unicodekeys k ON d.word = k.word OPTION (LOOP JOIN)'), (6, 'LOOPJOIN', 0, 'DECLARE @dummy int SELECT @dummy = SUM(k.data) FROM (SELECT word FROM mbcswords WHERE id BETWEEN 150000 and 250000) d JOIN mbcskeys k ON d.word = k.word OPTION (LOOP JOIN)'), (7, 'LIKE', 1, 'DECLARE @dummy int SELECT @dummy = COUNT(*) FROM randomunicode WHERE randomtext LIKE N''%abc%'''), (8, 'LIKE', 0, 'DECLARE @dummy int SELECT @dummy = COUNT(*) FROM randommbcs WHERE randomtext LIKE ''%abc%''') go ------------------------------------------------------------------- USE collationresults go DROP TABLE wincollations CREATE TABLE wincollations (collation sysname NOT NULL, family varchar(50) NOT NULL, version char(3) NOT NULL, binary char(4) NULL CONSTRAINT ckc_wincollations_binary CHECK (binary IN ('BIN', 'BIN2')), case_ char(2) NULL CONSTRAINT ckc_wincollations_case CHECK (case_ IN ('CI', 'CS')), accent char(2) NULL CONSTRAINT ckc_wincollations_accent CHECK (accent IN ('AI', 'AS')), kana char(2) NULL CONSTRAINT ckc_wincollations_kana CHECK (kana IN ('KI', 'KS')), width char(2) NULL CONSTRAINT ckc_wincollations_width CHECK (width IN ('WI', 'WS')), varsel char(3) NULL CONSTRAINT ckc_wincollations_varsel CHECK (varsel IN ('', 'VSS')), surrogate char(2) NULL CONSTRAINT ckc_wincollations_surrogate CHECK (surrogate IN ('', 'SC')), utf8 char(4) NOT NULL CONSTRAINT ckc_wincollations_utf8 CHECK (utf8 IN ('', 'UTF8')) CONSTRAINT pk_wincollations PRIMARY KEY (collation), CONSTRAINT ckt_wincollations_binary_othsrs CHECK (binary IS NULL AND case_ IS NOT NULL AND accent IS NOT NULL AND kana IS NOT NULL AND width IS NOT NULL AND varsel IS NOT NULL AND surrogate IS NOT NULL OR binary IS NOT NULL AND case_ IS NULL AND accent IS NULL AND kana IS NULL AND width IS NULL AND varsel IS NULL AND surrogate IS NULL) ) go ; WITH pos AS ( SELECT name, iif(charindex('_BIN', name) > 0, charindex('_BIN', name), patindex('%[_]C[IS][_]%', name)) AS pos FROM sys.fn_helpcollations() WHERE name NOT LIKE 'SQL%' ), splitter AS ( SELECT name, designator = substring(name, 1, pos-1), qualifier = substring(name, pos+1, len(name)) FROM pos ) INSERT wincollations(collation, family, version, binary, case_, accent, kana, width, varsel, surrogate, utf8) SELECT name, IIF(designator LIKE '%[0-9]', substring(designator, 1, len(designator) - patindex('%[^0-9]%', reverse(designator))), designator), IIF(designator LIKE '%[0-9]', reverse(substring(reverse(designator), 1, patindex('%[^0-9]%', reverse(designator)) - 1)), '80'), IIF(qualifier LIKE 'BIN%', substring(qualifier, 1, isnull(nullif(charindex('_', qualifier), 0) - 1, len(qualifier))), NULL) AS binary, IIF(qualifier LIKE 'C[IS}[_]%', substring(qualifier, 1, 2), NULL) AS case_, IIF(qualifier LIKE '%[_]A[IS]%', substring(qualifier, 4, 2), NULL) AS accent, CASE WHEN qualifier LIKE 'C[IS}[_]%' THEN IIF (qualifier LIKE '%[_]KS%', 'KS', 'KI') END AS kana, CASE WHEN qualifier LIKE 'C[IS}[_]%' THEN IIF (qualifier LIKE '%[_]WS%', 'WS', 'WI') END AS width, CASE WHEN qualifier LIKE 'C[IS}[_]%' THEN IIF (qualifier LIKE '%[_]VSS%', 'VSS', '') END AS varsel, CASE WHEN qualifier LIKE 'C[IS}[_]%' THEN CASE WHEN qualifier LIKE '%[_]SC%' THEN 'SC' WHEN designator LIKE '%140' THEN 'SC' ELSE ' ' END END AS surrogate, IIF(qualifier LIKE '%UTF8', 'UTF8', '') AS utf8 FROM splitter go DROP TABLE IF EXISTS families CREATE TABLE families (family sysname NOT NULL, cnt int NOT NULL, European bit NOT NULL, CONSTRAINT pk_families PRIMARY KEY (family) ) INSERT families SELECT family, COUNT(*), CASE WHEN iif(charindex('_', family) = 0, family, substring(family, 1, charindex('_', family) - 1)) IN ('Assamese', 'Bengali', 'Chinese', 'Dari', 'Divehi', 'Georgian', 'Indic', 'Japanese', 'Khmer', 'Korean', 'Lao', 'Maltese', 'Maori', 'Nepali', 'Pashto', 'Syriac', 'Thai', 'Tibetan', 'Urdu', 'Vietnamese') THEN 0 ELSE 1 END FROM wincollations GROUP BY family go DROP TABLE IF EXISTS sqlcollations CREATE TABLE sqlcollations (collation sysname NOT NULL, family varchar(50) NOT NULL, style varchar(5) NOT NULL CONSTRAINT ckc_sqlcollations_style CHECK (style IN ('BIN', 'BIN2', 'CI_AS', 'CI_AI', 'CS_AS')), codepage varchar(6), pref bit NOT NULL, CONSTRAINT pk_sqlcollations PRIMARY KEY (collation) ) go ; WITH CTE AS ( SELECT name AS collation, replace(replace(replace(name, 'CI_AI', 'CI-AI'), 'CI_AS', 'CI-AS'), 'CS_AS', 'CS-AS') AS name FROM sys.fn_helpcollations() WHERE name LIKE 'SQL%' ), CTE2 AS ( SELECT collation, substring(name, 1, len(name) - charindex('_', reverse(name))) AS name, substring(name, len(name) - charindex('_', reverse(name)) + 2, len(name)) AS style FROM CTE ), CP AS ( SELECT collation, substring(name, 1, len(name) - charindex('_', reverse(name))) AS name, replace(style, '-', '_') AS style, substring(name, len(name) - charindex('_', reverse(name)) + 2, len(name)) AS CP FROM CTE2 ) INSERT sqlcollations(collation, family, style, codepage, pref) SELECT collation, replace(name, '_Pref', ''), style, CP, iif(name LIKE '%Pref', 1, 0) FROM CP ------------------------------------------------------------- go SELECT r.label, r.isunicode, AVG(CASE binary WHEN 'BIN' THEN tookms END) AS BIN, AVG(CASE binary WHEN 'BIN2' THEN tookms END) AS BIN2, AVG(CASE case_ WHEN 'CI' THEN tookms END) AS CI, AVG(CASE case_ WHEN 'CS' THEN tookms END) AS CS, AVG(CASE accent WHEN 'AI' THEN tookms END) AS AI, AVG(CASE accent WHEN 'AS' THEN tookms END) AS [AS], AVG(CASE kana WHEN 'KI' THEN tookms END) AS KI, AVG(CASE kana WHEN 'KS' THEN tookms END) AS KS, AVG(CASE width WHEN 'WI' THEN tookms END) AS WI, AVG(CASE width WHEN 'WS' THEN tookms END) AS WS, AVG(CASE WHEN version = '140' AND varsel = '' THEN tookms END) AS VSI, AVG(CASE varsel WHEN 'VSS' THEN tookms END) AS VSS, AVG(CASE WHEN version = '80' AND surrogate = '' THEN tookms END) AS No_SC, AVG(CASE surrogate WHEN 'SC' THEN tookms END) AS SC, AVG(CASE WHEN surrogate = 'SC' AND utf8 = '' THEN tookms END) AS No_UTF8, AVG(CASE utf8 WHEN 'UTF8' THEN tookms END) AS UTF8 FROM results r JOIN wincollations w ON r.collation = w.collation WHERE tookms IS NOT NULL /* AND EXISTS (SELECT * FROM families f WHERE f.family = w.family AND f.European = 1) */ GROUP BY r.label, r.isunicode ORDER BY r.label, r.isunicode SELECT r.label, r.isunicode, AVG(CASE binary WHEN 'BIN' THEN tookµs END) AS BIN, AVG(CASE binary WHEN 'BIN2' THEN tookµs END) AS BIN2, AVG(CASE case_ WHEN 'CI' THEN tookµs END) AS CI, AVG(CASE case_ WHEN 'CS' THEN tookµs END) AS CS, AVG(CASE accent WHEN 'AI' THEN tookµs END) AS AI, AVG(CASE accent WHEN 'AS' THEN tookµs END) AS [AS], AVG(CASE kana WHEN 'KI' THEN tookµs END) AS KI, AVG(CASE kana WHEN 'KS' THEN tookµs END) AS KS, AVG(CASE width WHEN 'WI' THEN tookµs END) AS WI, AVG(CASE width WHEN 'WS' THEN tookµs END) AS WS, AVG(CASE WHEN version = '140' AND varsel = '' THEN tookµs END) AS VSI, AVG(CASE varsel WHEN 'VSS' THEN tookµs END) AS VSS, AVG(CASE WHEN version <> '80' AND surrogate = '' THEN tookµs END) AS No_SC, AVG(CASE surrogate WHEN 'SC' THEN tookµs END) AS SC, AVG(CASE WHEN surrogate = 'SC' AND utf8 = '' THEN tookµs END) AS No_UTF8, AVG(CASE utf8 WHEN 'UTF8' THEN tookµs END) AS UTF8 FROM results r JOIN wincollations w ON r.collation = w.collation /* AND EXISTS (SELECT * FROM families f WHERE f.family = w.family AND f.European = 1) */ WHERE tookµs IS NOT NULL GROUP BY r.label, r.isunicode ORDER BY r.label, r.isunicode SELECT r.label, r.isunicode, AVG(CASE WHEN case_ = 'CI' AND accent = 'AI' THEN tookms END) AS CI_AI, AVG(CASE WHEN case_ = 'CI' AND accent = 'AS' THEN tookms END) AS CI_AS, AVG(CASE WHEN case_ = 'CS' AND accent = 'AI' THEN tookms END) AS CS_AI, AVG(CASE WHEN case_ = 'CS' AND accent = 'AS' THEN tookms END) AS CS_AS FROM results r JOIN wincollations w ON r.collation = w.collation WHERE tookms IS NOT NULL AND version = '80' GROUP BY r.label, r.isunicode ORDER BY r.label, r.isunicode SELECT r.label, r.isunicode, cast(1E0 * AVG(CASE WHEN case_ = 'CS' AND accent = 'AS' THEN coalesce(tookms, tookµs) END) / AVG(CASE WHEN case_ = 'CI' AND accent = 'AI' THEN coalesce(tookms, tookµs) END) AS decimal(5,2)) AS [CS_AS/CI_AI] FROM results r JOIN wincollations w ON r.collation = w.collation WHERE w.binary IS NULL GROUP BY r.label, r.isunicode ORDER BY r.label, r.isunicode SELECT r.label, r.isunicode, AVG(CASE WHEN version = '80' THEN tookms END) AS [80], AVG(CASE WHEN version = '90' AND surrogate = '' THEN tookms END) AS [90_noSC], AVG(CASE WHEN version = '90' AND surrogate = 'SC' AND utf8 = '' THEN tookms END) AS [90_SC], AVG(CASE WHEN version = '90' AND surrogate = 'SC' AND utf8 = 'UTF8' THEN tookms END) AS [90_SC_UTF8], AVG(CASE WHEN version = '100' AND surrogate = '' THEN tookms END) AS [100_noSC], AVG(CASE WHEN version = '100' AND surrogate = 'SC' AND utf8 = '' THEN tookms END) AS [100_SC], AVG(CASE WHEN version = '100' AND surrogate = 'SC' AND utf8 = 'UTF8' THEN tookms END) AS [100_SC_UTF8], AVG(CASE WHEN version = '140' AND utf8 = '' THEN tookms END) AS [140], AVG(CASE WHEN version = '140' AND utf8 = 'UTF8' THEN tookms END) AS [140_UTF8] FROM results r JOIN wincollations w ON r.collation = w.collation WHERE tookms IS NOT NULL AND binary IS NULL GROUP BY r.label, r.isunicode ORDER BY r.label, r.isunicode SELECT r.label, r.isunicode, AVG(CASE WHEN version = '80' THEN tookµs END) AS [80], AVG(CASE WHEN version = '90' AND surrogate = '' THEN tookµs END) AS [90_noSC], AVG(CASE WHEN version = '90' AND surrogate = 'SC' AND utf8 = '' THEN tookµs END) AS [90_SC], AVG(CASE WHEN version = '90' AND surrogate = 'SC' AND utf8 = 'UTF8' THEN tookµs END) AS [90_SC_UTF8], AVG(CASE WHEN version = '100' AND surrogate = '' THEN tookµs END) AS [100_noSC], AVG(CASE WHEN version = '100' AND surrogate = 'SC' AND utf8 = '' THEN tookµs END) AS [100_SC], AVG(CASE WHEN version = '100' AND surrogate = 'SC' AND utf8 = 'UTF8' THEN tookµs END) AS [100_SC_UTF8], AVG(CASE WHEN version = '140' THEN tookµs END) AS [140] FROM results r JOIN wincollations w ON r.collation = w.collation WHERE tookµs IS NOT NULL AND binary IS NULL GROUP BY r.label, r.isunicode ORDER BY r.label, r.isunicode ; WITH CTE AS ( SELECT r.label, r.isunicode, w.version, AVG(CASE WHEN w.binary IS NOT NULL THEN r.tookms END) AS binary, AVG(CASE WHEN w.binary IS NULL AND w.utf8 = '' THEN r.tookms END) AS [non-binary], AVG(CASE WHEN w.binary IS NULL AND w.utf8 = 'UTF8' THEN r.tookms END) AS utf8, AVG(CASE WHEN w.binary IS NOT NULL THEN r.tookµs END) AS binary_µ, AVG(CASE WHEN w.binary IS NULL AND w.utf8 = '' THEN r.tookµs END) AS [non-binary_µ], AVG(CASE WHEN w.binary IS NULL AND w.utf8 = 'UTF8' THEN r.tookµs END) AS utf8_µ FROM results r JOIN wincollations w ON r.collation = w.collation GROUP BY r.label, r.isunicode, w.version ) SELECT label, isunicode, version, binary, [non-binary], utf8, binary_µ, [non-binary_µ], utf8_µ, convert(decimal(5,2), 100-1E2*coalesce(binary, binary_µ)/coalesce([non-binary], [non-binary_µ])) AS "binary-gain" FROM CTE ORDER BY label, isunicode, cast(version as int) ; WITH CTE AS ( SELECT r.label, r.isunicode, w.version, AVG(CASE WHEN w.binary = 'BIN' THEN r.tookms END) AS BIN, AVG(CASE WHEN w.binary = 'BIN2' THEN r.tookms END) AS BIN2, AVG(CASE WHEN w.binary = 'BIN' THEN r.tookµs END) AS BIN_µ, AVG(CASE WHEN w.binary = 'BIN2' THEN r.tookµs END) AS [BIN2_µ] FROM results r JOIN wincollations w ON r.collation = w.collation WHERE w.binary IS NOT NULL GROUP BY r.label, r.isunicode, w.version ) SELECT label, isunicode, version, BIN, BIN2, BIN_µ, BIN2_µ, convert(decimal(5,2), 100-1E2*coalesce(BIN2, BIN2_µ)/coalesce(BIN, BIN_µ)) AS [BIN2-ratio] FROM CTE ORDER BY label, isunicode, cast(version as int) SELECT r.label, r.isunicode, cast(1E0 * AVG(CASE WHEN version = '100' THEN coalesce(tookms, tookµs) END) / AVG(CASE WHEN version = '80' THEN coalesce(tookms, tookµs) END) AS decimal(5, 2)) AS [100/80 ratio] FROM results r JOIN wincollations w ON r.collation = w.collation WHERE w.binary IS NULL AND w.version IN ('80', '100') AND w.surrogate = '' AND w.utf8 = '' GROUP BY r.label, r.isunicode ORDER BY r.label, r.isunicode ; WITH CTE AS ( SELECT r.label, AVG(CASE WHEN version = '80' AND r.isunicode = 1 THEN coalesce(tookms, tookµs) END) AS [80-nvarchar], AVG(CASE WHEN version = '100' AND r.isunicode = 1 AND surrogate = '' THEN coalesce(tookms, tookµs) END) AS [100-nvarchar], AVG(CASE WHEN utf8 = 'UTF8' AND r.isunicode = 0 THEN coalesce(tookms, tookµs) END) AS [UTF8-varchar] FROM results r JOIN wincollations w ON r.collation = w.collation WHERE binary IS NULL AND label <> 'MISFIT' GROUP BY r.label ) SELECT label, [80-nvarchar], [100-nvarchar], [UTF8-varchar], cast(1E0*[UTF8-varchar]/[80-nvarchar] AS decimal(5, 2)) [UTF8/80], cast(1E0*[UTF8-varchar]/[100-nvarchar] AS decimal(5, 2)) [UTF8/100] FROM CTE ORDER BY label SELECT r.label, r.isunicode, AVG(CASE WHEN s.style = 'BIN' THEN tookms END) AS BIN, AVG(CASE WHEN s.style = 'BIN2' THEN tookms END) AS BIN2, AVG(CASE WHEN s.style = 'CI_AI' THEN tookms END) AS CI_AI, AVG(CASE WHEN s.style = 'CI_AS' AND s.pref = 0 THEN tookms END) AS CI_AS, AVG(CASE WHEN s.style = 'CI_AS' AND s.pref = 1 THEN tookms END) AS CI_AS_Pref, AVG(CASE WHEN s.style = 'CS_AS' THEN tookms END) AS CS_AS, AVG(CASE WHEN s.style NOT LIKE 'BIN%' THEN tookms END) AS [AllSQLNonBin], AVG(CASE WHEN w.version = '80' AND w.binary IS NULL THEN tookms END) AS [80], AVG(CASE WHEN w.utf8 = 'UTF8' AND w.binary IS NULL THEN tookms END) AS [UTF8], AVG(CASE WHEN w.version = '80' AND w.binary IS NOT NULL THEN tookms END) AS [80-binary] FROM results r LEFT JOIN sqlcollations s ON r.collation = s.collation LEFT JOIN wincollations w ON r.collation = w.collation WHERE tookms IS NOT NULL GROUP BY r.label, r.isunicode ORDER BY r.label, r.isunicode SELECT r.label, r.isunicode, AVG(CASE WHEN style = 'BIN' THEN tookµs END) AS BIN, AVG(CASE WHEN style = 'BIN2' THEN tookµs END) AS BIN2, AVG(CASE WHEN style = 'CI_AI' THEN tookµs END) AS CI_AI, AVG(CASE WHEN style = 'CI_AS' AND pref = 0 THEN tookµs END) AS CI_AS, AVG(CASE WHEN style = 'CI_AS' AND pref = 1 THEN tookµs END) AS CI_AS_Pref, AVG(CASE WHEN style = 'CS_AS' THEN tookµs END) AS CS_AS, AVG(CASE WHEN w.version = '80' AND w.binary IS NULL THEN tookµs END) AS [80], AVG(CASE WHEN w.version = '80' AND w.binary IS NOT NULL THEN tookµs END) AS [80-binary] FROM results r LEFT JOIN sqlcollations s ON r.collation = s.collation LEFT JOIN wincollations w ON r.collation = w.collation AND w.version = '80' WHERE tookµs IS NOT NULL GROUP BY r.label, r.isunicode ORDER BY r.label, r.isunicode ; WITH CTE AS ( SELECT r.label, r.isunicode, r.tookms, r.tookµs, isnull(w.family, s.family) AS family, isnull(w.version, 'SQL') AS version, CASE WHEN w.binary IS NOT NULL OR s.style LIKE 'BIN%' THEN 'BIN' ELSE '' END AS binary, CASE WHEN r.isunicode = 0 AND w.utf8 = 'UTF8' THEN 'UTF8' ELSE '' END AS utf8 FROM results r LEFT JOIN wincollations w ON r.collation = w.collation LEFT JOIN sqlcollations s ON r.collation = s.collation ), aggr AS ( SELECT label, isunicode, family, version, binary, utf8, AVG(tookms) AS tookms, AVG(tookµs) AS tookµs, COUNT(*) AS cnt FROM CTE GROUP BY label, isunicode, family, version, binary, utf8 ) SELECT family, version, binary, utf8, tookms, tookµs, cnt FROM aggr WHERE label = 'MISFIT' AND isunicode = 0 ORDER BY tookms, tookµs go DROP TABLE ratios CREATE TABLE ratios (family sysname NOT NULL, version char(3) NOT NULL, UTF8 char(4) NOT NULL, label varchar(20) NOT NULL, isunicode bit NOT NULL, value float NOT NULL, grpratio float NOT NULL, fullratio float NOT NULL, grppos int NOT NULL, totalpos int NOT NULL, backgrp int NOT NULL, backpos int NOT NULL, CONSTRAINT pk_ratios PRIMARY KEY (family, version, UTF8, label, isunicode) ) go ; WITH keyclean AS ( SELECT w.family, w.version, IIF(r.isunicode = 0, w.utf8, '') AS UTF8, r.label, r.isunicode, convert(float, coalesce(r.tookms, r.tookµs)) AS value FROM results r JOIN wincollations w ON r.collation = w.collation WHERE w.binary IS NULL ), aggr AS ( SELECT family, version, UTF8, label, isunicode, AVG(value) AS value FROM keyclean GROUP BY family, version, UTF8, label, isunicode ) INSERT ratios(family, version, UTF8, label, isunicode, value, grpratio, fullratio, grppos, totalpos, backgrp, backpos) SELECT family, version, UTF8, label, isunicode, value, value / first_value(value) OVER(PARTITION BY version, UTF8, label, isunicode ORDER BY value) AS grpratio, value / first_value(value) OVER(PARTITION BY label, isunicode ORDER BY value) AS fullratio, rank() OVER(PARTITION BY version, UTF8, label, isunicode ORDER BY value) AS grppos, rank() OVER(PARTITION BY label, isunicode ORDER BY value) AS totalpos, rank() OVER(PARTITION BY version, UTF8, label, isunicode ORDER BY value DESC) AS backgrp, rank() OVER(PARTITION BY label, isunicode ORDER BY value DESC) AS backpos FROM aggr SELECT * FROM ratios WHERE totalpos = 1 ORDER BY label, isunicode SELECT * FROM ratios WHERE backpos = 1 ORDER BY label, isunicode SELECT * FROM ratios WHERE grppos = 1 AND version IN ('80', '100') ORDER BY version, UTF8, label, isunicode SELECT * FROM ratios WHERE backgrp = 1 AND version IN ('80', '100') ORDER BY version, UTF8, label, isunicode SELECT family, version, UTF8, AVG(fullratio) AS avgratio FROM ratios GROUP BY family, version, UTF8 ORDER BY avgratio SELECT family, version, UTF8, AVG(grpratio) AS avgratio FROM ratios WHERE version IN ('80', '100') GROUP BY family, version, UTF8 ORDER BY version, UTF8, avgratio SELECT r.label, w.version, w.utf8, cast(1E0 * AVG(CASE r.isunicode WHEN 0 THEN coalesce(tookms, tookµs) END) / AVG(CASE r.isunicode WHEN 1 THEN coalesce(tookms, tookµs) END) AS decimal(5, 2)) AS varcharspeed FROM results r JOIN wincollations w ON r.collation = w.collation WHERE w.binary IS NULL AND r.label <> 'MISFIT' AND w.utf8 = '' GROUP BY r.label, w.version, w.utf8 ORDER BY r.label, w.version, w.utf8 SELECT r.label, AVG(CASE r.isunicode WHEN 0 THEN coalesce(r.tookms, r.tookµs) END) AS varchar, AVG(CASE r.isunicode WHEN 1 THEN coalesce(r.tookms, r.tookµs) END) as nvarchar, cast(1E0 * AVG(CASE r.isunicode WHEN 0 THEN coalesce(r.tookms, r.tookµs) END) / AVG(CASE r.isunicode WHEN 1 THEN coalesce(r.tookms, r.tookµs) END) AS decimal(5, 2)) AS varcharspeed FROM results r JOIN sqlcollations s ON r.collation = s.collation WHERE s.style <> 'BIN' AND r.label <> 'MISFIT' GROUP BY r.label ORDER BY r.label SELECT r.label, r.isunicode, cast(1E0 * AVG(CASE w.binary WHEN 'BIN' THEN coalesce(tookms, tookµs) END) / AVG(CASE w.binary WHEN 'BIN2' THEN coalesce(tookms, tookµs) END) AS decimal(5, 2)) AS [BIN/BIN2] FROM results r JOIN wincollations w ON r.collation = w.collation WHERE w.binary IS NOT NULL GROUP BY r.label, r.isunicode ORDER BY r.label, r.isunicode