May 18, 2015 at 4:37 pm
I've been monkeying around with some obfuscation code... Over the weekend I decided to give it the ability to do a case sensitive character swap. Updating the code was pretty straight forward but when I was through, I noticed that I was getting Cardinality Estimate warnings that I wasn't getting before.
These usually aren't too difficult to figure out but this one has me stumped. Any guidance would be appreciated...
Anyway, here is some test data and two versions of the executed SQL (the base code is all dynamic and the two code versions are the result of toggling the @MatchCase parameter).
/* ========================================
CREATE TABLE
======================================== */
CREATE TABLE [dbo].[PersonInfoSmall](
[PersonID] [BIGINT] NOT NULL,
[FirstName] [NVARCHAR](50) NOT NULL,
[MiddleName] [NVARCHAR](50) NULL,
[LastName] [NVARCHAR](50) NOT NULL,
[Suffix] [NVARCHAR](10) NULL,
[SSN] [VARCHAR](11) NULL,
[PhoneType] [NVARCHAR](50) NOT NULL,
[PhoneNumber] [NVARCHAR](25) NOT NULL,
[AddressType] [NVARCHAR](50) NOT NULL,
[StreetAddress] [NVARCHAR](121) NOT NULL,
[City] [NVARCHAR](30) NOT NULL,
[StateCode] [NCHAR](3) NOT NULL,
[PostalCode] [NVARCHAR](15) NOT NULL,
[CountryCode] [NVARCHAR](3) NOT NULL,
[EmailAddress] [NVARCHAR](50) NULL,
CONSTRAINT [pk_PersonInfoSmall] PRIMARY KEY CLUSTERED
(
[PersonID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY];
/* ========================================
INSERT TEST DATA
======================================== */
INSERT dbo.PersonInfoSmall (PersonID,FirstName,MiddleName,LastName,Suffix,SSN,PhoneType,PhoneNumber,AddressType,StreetAddress,City,StateCode,PostalCode,CountryCode,EmailAddress) VALUES
(1,'Ken','J','Sánchez',NULL,'447-32-2074','Cell','697-555-0142','Home','4350 Minute Dr. ','Newport Hills','WA ','98006','US','ken0@adventure-works.com'),
(2,'Terri','Lee','Duffy',NULL,'760-54-9791','Work','819-555-0175','Home','7559 Worth Ct. ','Renton','WA ','98055','US','terri0@adventure-works.com'),
(3,'Roberto',NULL,'Tamburello',NULL,'859-43-1194','Cell','212-555-0187','Home','2137 Birchwood Dr ','Redmond','WA ','98052','US','roberto0@adventure-works.com'),
(4,'Rob',NULL,'Walters',NULL,'850-22-7557','Cell','612-555-0100','Home','5678 Lakeview Blvd. ','Minneapolis','MN ','55402','US','rob0@adventure-works.com'),
(5,'Gail','A','Erickson',NULL,'611-73-9736','Cell','849-555-0139','Home','9435 Breck Court ','Bellevue','WA ','98004','US','gail0@adventure-works.com'),
(6,'Jossef','H','Goldberg',NULL,'974-32-1091','Work','122-555-0189','Home','5670 Bel Air Dr. ','Renton','WA ','98055','US','jossef0@adventure-works.com'),
(7,'Dylan','A','Miller',NULL,'131-44-2861','Work','181-555-0156','Home','7048 Laurel ','Kenmore','WA ','98028','US','dylan0@adventure-works.com'),
(8,'Diane','L','Margheim',NULL,'924-08-1331','Cell','815-555-0138','Home','475 Santa Maria ','Everett','WA ','98201','US','diane1@adventure-works.com'),
(9,'Gigi','N','Matthew',NULL,'808-63-2924','Cell','185-555-0186','Home','7808 Brown St. ','Bellevue','WA ','98004','US','gigi0@adventure-works.com'),
(10,'Michael',NULL,'Raheem',NULL,'275-76-7260','Work','330-555-2568','Home','1234 Seaside Way ','San Francisco','CA ','94109','US','michael6@adventure-works.com'),
(11,'Ovidiu','V','Cracium',NULL,'434-79-5045','Cell','719-555-0181','Home','5458 Gladstone Drive ','Kenmore','WA ','98028','US','ovidiu0@adventure-works.com'),
(12,'Thierry','B','D''Hers',NULL,'650-70-7097','Work','168-555-0183','Home','1970 Napa Ct. ','Bothell','WA ','98011','US','thierry0@adventure-works.com'),
(13,'Janice','M','Galvin',NULL,'507-76-5812','Work','473-555-0117','Home','3397 Rancho View Drive ','Redmond','WA ','98052','US','janice0@adventure-works.com'),
(14,'Michael','I','Sullivan',NULL,'833-52-9726','Cell','465-555-0156','Home','6510 Hacienda Drive ','Renton','WA ','98055','US','michael8@adventure-works.com'),
(15,'Sharon','B','Salavaria',NULL,'982-69-7407','Cell','970-555-0138','Home','7165 Brock Lane ','Renton','WA ','98055','US','sharon0@adventure-works.com'),
(16,'David','M','Bradley',NULL,'079-93-8229','Work','913-555-0172','Home','3768 Door Way ','Redmond','WA ','98052','US','david0@adventure-works.com'),
(17,'Kevin','F','Brown',NULL,'907-51-5826','Cell','150-555-0189','Home','7883 Missing Canyon Court ','Everett','WA ','98201','US','kevin0@adventure-works.com'),
(18,'John','L','Wood',NULL,'414-66-0678','Work','486-555-0150','Home','9906 Oak Grove Road ','Redmond','WA ','98052','US','john5@adventure-works.com'),
(19,'Mary','A','Dempsey',NULL,'328-12-6547','Cell','124-555-0114','Home','6307 Greenbelt Way ','Bellevue','WA ','98004','US','mary2@adventure-works.com'),
(20,'Wanida','M','Benshoof',NULL,'277-77-2991','Work','708-555-0141','Home','6951 Harmony Way ','Sammamish','WA ','98074','US','wanida0@adventure-works.com'),
(21,'Terry','J','Eminhizer',NULL,'140-99-8627','Cell','138-555-0118','Home','8668 Via Neruda ','Bellevue','WA ','98004','US','terry0@adventure-works.com'),
(22,'Sariya','E','Harnpadoungsataya',NULL,'481-53-1452','Work','399-555-0176','Home','1185 Dallas Drive ','Everett','WA ','98201','US','sariya0@adventure-works.com'),
(23,'Mary','E','Gibson',NULL,'051-38-6781','Work','531-555-0183','Home','3928 San Francisco ','Everett','WA ','98201','US','mary0@adventure-works.com'),
(24,'Jill','A','Williams',NULL,'019-75-8697','Work','510-555-0121','Home','3238 Laguna Circle ','Everett','WA ','98201','US','jill0@adventure-works.com'),
(25,'James','R','Hamilton',NULL,'840-93-0285','Work','870-555-0122','Home','9652 Los Angeles ','Monroe','WA ','98272','US','james1@adventure-works.com'),
(26,'Peter','J','Krebs',NULL,'066-79-1833','Work','913-555-0196','Home','3670 All Ways Drive ','Bellevue','WA ','98004','US','peter0@adventure-works.com'),
(27,'Jo','A','Brown',NULL,'812-61-9001','Cell','632-555-0129','Home','9693 Mellowood Street ','Duvall','WA ','98019','US','jo0@adventure-works.com'),
(28,'Guy','R','Gilbert',NULL,'158-31-4682','Work','320-555-0195','Home','7726 Driftwood Drive ','Monroe','WA ','98272','US','guy1@adventure-works.com'),
(29,'Mark','K','McArthur',NULL,'750-80-0696','Work','417-555-0154','Home','9863 Ridge Place ','Redmond','WA ','98052','US','mark1@adventure-works.com'),
(30,'Britta','L','Simon',NULL,'098-40-7755','Cell','955-555-0169','Home','2046 Las Palmas ','Edmonds','WA ','98020','US','britta0@adventure-works.com'),
(31,'Margie','W','Shoop',NULL,'026-59-9628','Cell','818-555-0128','Home','2080 Sycamore Drive ','Edmonds','WA ','98020','US','margie0@adventure-works.com'),
(32,'Rebecca','A','Laszlo',NULL,'744-02-2498','Cell','314-555-0113','Home','3197 Thornhill Place ','Bellevue','WA ','98004','US','rebecca0@adventure-works.com'),
(33,'Annik','O','Stahl',NULL,'839-08-1858','Work','499-555-0125','Home','7842 Ygnacio Valley Road ','Seattle','WA ','98104','US','annik0@adventure-works.com'),
(34,'Suchitra','O','Mohan',NULL,'320-11-1038','Cell','753-555-0129','Home','5678 Clear Court ','Bellevue','WA ','98004','US','suchitra0@adventure-works.com'),
(35,'Brandon','G','Heidepriem',NULL,'557-38-8878','Cell','429-555-0137','Home','8000 Crane Court ','Redmond','WA ','98052','US','brandon0@adventure-works.com'),
(36,'Jose','R','Lugo',NULL,'390-14-8862','Cell','587-555-0115','Home','5125 Cotton Ct. ','Seattle','WA ','98104','US','jose0@adventure-works.com'),
(37,'Chris','O','Okelberry',NULL,'215-61-8897','Cell','315-555-0144','Home','8467 Clifford Court ','Redmond','WA ','98052','US','chris2@adventure-works.com'),
(38,'Kim','B','Abercrombie',NULL,'217-85-3724','Cell','208-555-0114','Home','9752 Jeanne Circle ','Carnation','WA ','98014','US','kim1@adventure-works.com'),
(39,'Ed','R','Dudenhoefer',NULL,'518-75-1073','Work','919-555-0140','Home','4598 Manila Avenue ','Seattle','WA ','98104','US','ed0@adventure-works.com'),
(40,'JoLynn','M','Dobney',NULL,'059-16-9557','Work','903-555-0145','Home','7126 Ending Ct. ','Seattle','WA ','98104','US','jolynn0@adventure-works.com'),
(41,'Bryan',NULL,'Baker',NULL,'076-39-1847','Cell','712-555-0113','Home','2275 Valley Blvd. ','Monroe','WA ','98272','US','bryan0@adventure-works.com'),
(42,'James','D','Kramer',NULL,'665-55-1869','Cell','119-555-0117','Home','4734 Sycamore Court ','Monroe','WA ','98272','US','james0@adventure-works.com'),
(43,'Nancy','A','Anderson',NULL,'246-27-4036','Cell','970-555-0118','Home','7820 Bird Drive ','Sammamish','WA ','98074','US','nancy0@adventure-works.com'),
(44,'Simon','D','Rapier',NULL,'682-14-3756','Work','963-555-0134','Home','3421 Bouncing Road ','Duvall','WA ','98019','US','simon0@adventure-works.com'),
(45,'Thomas','R','Michaels',NULL,'043-07-0014','Cell','278-555-0118','Home','7338 Green St. ','Edmonds','WA ','98020','US','thomas0@adventure-works.com'),
(46,'Eugene','O','Kogan',NULL,'903-17-2135','Cell','173-555-0179','Home','991 Vista Verde ','Duvall','WA ','98019','US','eugene1@adventure-works.com'),
(47,'Andrew','R','Hill',NULL,'105-45-1379','Cell','908-555-0159','Home','6629 Polson Circle ','Everett','WA ','98201','US','andrew0@adventure-works.com'),
(48,'Ruth','Ann','Ellerbrock',NULL,'564-98-8089','Cell','145-555-0130','Home','2176 Apollo Way ','Everett','WA ','98201','US','ruth0@adventure-works.com'),
(49,'Barry','K','Johnson',NULL,'496-20-0637','Work','206-555-0180','Home','3114 Notre Dame Ave. ','Snohomish','WA ','98296','US','barry0@adventure-works.com'),
(50,'Sidney','M','Higa',NULL,'201-22-6770','Work','424-555-0189','Home','9277 Country View Lane ','Edmonds','WA ','98020','US','sidney0@adventure-works.com'),
(51,'Jeffrey','L','Ford',NULL,'477-47-8682','Work','984-555-0185','Home','5734 Ashford Court ','Monroe','WA ','98272','US','jeffrey0@adventure-works.com'),
(52,'Doris','M','Hartwig',NULL,'561-17-3941','Cell','328-555-0150','Home','5553 Cash Avenue ','Kenmore','WA ','98028','US','doris0@adventure-works.com'),
(53,'Diane','R','Glimp',NULL,'722-89-2907','Cell','202-555-0151','Home','9006 Woodside Way ','Redmond','WA ','98052','US','diane0@adventure-works.com'),
(54,'Bonnie','N','Kearney',NULL,'191-96-9982','Work','264-555-0150','Home','4852 Chaparral Court ','Snohomish','WA ','98296','US','bonnie0@adventure-works.com'),
(55,'Taylor','R','Maxwell',NULL,'244-33-1497','Work','508-555-0165','Home','504 O St. ','Edmonds','WA ','98020','US','taylor0@adventure-works.com'),
(56,'Denise','H','Smith',NULL,'148-04-5118','Cell','869-555-0119','Home','5669 Ironwood Way ','Kenmore','WA ','98028','US','denise0@adventure-works.com'),
(57,'Frank','T','Miller',NULL,'541-16-9723','Work','167-555-0139','Home','591 Merriewood Drive ','Snohomish','WA ','98296','US','frank1@adventure-works.com'),
(58,'Kendall','C','Keil',NULL,'602-82-7850','Work','138-555-0128','Home','7439 Laguna Niguel ','Seattle','WA ','98104','US','kendall0@adventure-works.com'),
(59,'Bob','N','Hohman',NULL,'125-21-3379','Work','611-555-0116','Home','6387 Scenic Avenue ','Bothell','WA ','98011','US','bob0@adventure-works.com'),
(60,'Pete','C','Male',NULL,'922-52-2122','Cell','768-555-0123','Home','5375 Clearland Circle ','Seattle','WA ','98104','US','pete0@adventure-works.com'),
(61,'Diane','H','Tibbott',NULL,'278-56-6015','Work','361-555-0180','Home','8192 Seagull Court ','Kenmore','WA ','98028','US','diane2@adventure-works.com'),
(62,'John','T','Campbell',NULL,'574-56-8367','Work','435-555-0113','Home','2639 Anchor Court ','Seattle','WA ','98104','US','john0@adventure-works.com'),
(63,'Maciej','W','Dusza',NULL,'575-23-0799','Cell','237-555-0128','Home','3026 Anchor Drive ','Edmonds','WA ','98020','US','maciej0@adventure-works.com'),
(64,'Michael','J','Zwilling',NULL,'205-57-8880','Cell','582-555-0148','Home','7511 Cooper Dr. ','Edmonds','WA ','98020','US','michael7@adventure-works.com'),
(65,'Randy','T','Reeves',NULL,'106-87-3702','Cell','961-555-0122','Home','3632 Bank Way ','Edmonds','WA ','98020','US','randy0@adventure-works.com'),
(66,'Karan','R','Khanna',NULL,'857-29-8373','Work','447-555-0186','Home','1102 Ravenwood ','Seattle','WA ','98104','US','karan0@adventure-works.com'),
(67,'Jay','G','Adams',NULL,'351-38-6090','Work','407-555-0165','Home','896 Southdale ','Monroe','WA ','98272','US','jay0@adventure-works.com'),
(68,'Charles','B','Fitzgerald',NULL,'850-51-2656','Work','931-555-0118','Home','5263 Etcheverry Dr ','Sammamish','WA ','98074','US','charles0@adventure-works.com'),
(69,'Steve','F','Masters',NULL,'824-96-5572','Work','712-555-0170','Home','1398 Yorba Linda ','Seattle','WA ','98104','US','steve0@adventure-works.com'),
(70,'David','J','Ortiz',NULL,'203-87-4862','Work','712-555-0119','Home','931 Corte De Luna ','Seattle','WA ','98104','US','david2@adventure-works.com'),
(71,'Michael','Sean','Ray',NULL,'914-69-3898','Cell','156-555-0199','Home','6498 Mining Rd. ','Seattle','WA ','98104','US','michael3@adventure-works.com'),
(72,'Steven','T','Selikoff',NULL,'635-94-5763','Work','925-555-0114','Home','181 Gaining Drive ','Everett','WA ','98201','US','steven0@adventure-works.com'),
(73,'Carole','M','Poland',NULL,'214-85-0566','Cell','688-555-0192','Home','8411 Mt. Orange Place ','Edmonds','WA ','98020','US','carole0@adventure-works.com'),
(74,'Bjorn','M','Rettig',NULL,'367-31-6343','Work','199-555-0117','Home','5802 Ampersand Drive ','Seattle','WA ','98104','US','bjorn0@adventure-works.com'),
(75,'Michiko','F','Osada',NULL,'749-12-8723','Work','984-555-0148','Home','1962 Ferndale Lane ','Sammamish','WA ','98074','US','michiko0@adventure-works.com'),
(76,'Carol','M','Philips',NULL,'905-37-2754','Cell','609-555-0153','Home','6872 Thornwood Dr. ','Bothell','WA ','98011','US','carol0@adventure-works.com'),
(77,'Merav','A','Netz',NULL,'623-81-1349','Work','224-555-0187','Home','5666 Hazelnut Lane ','Seattle','WA ','98104','US','merav0@adventure-works.com'),
(78,'Reuben','H','D''sa',NULL,'610-88-0736','Work','191-555-0112','Home','1064 Slow Creek Road ','Seattle','WA ','98104','US','reuben0@adventure-works.com'),
(79,'Eric','L','Brown',NULL,'765-19-9179','Cell','680-555-0118','Home','5086 Nottingham Place ','Duvall','WA ','98019','US','eric1@adventure-works.com'),
(80,'Sandeep','P','Kaliyath',NULL,'256-56-2566','Work','166-555-0156','Home','4310 Kenston Dr. ','Sammamish','WA ','98074','US','sandeep0@adventure-works.com'),
(81,'Mihail','U','Frintu',NULL,'065-45-5848','Work','733-555-0128','Home','2466 Clearland Circle ','Edmonds','WA ','98020','US','mihail0@adventure-works.com'),
(82,'Jack','T','Creasey',NULL,'949-74-1951','Cell','521-555-0113','Home','874 Olivera Road ','Seattle','WA ','98104','US','jack1@adventure-works.com'),
(83,'Patrick','M','Cook',NULL,'593-85-8650','Cell','425-555-0117','Home','9537 Ridgewood Drive ','Seattle','WA ','98104','US','patrick1@adventure-works.com'),
(84,'Frank','R','Martinez',NULL,'404-93-6256','Work','203-555-0196','Home','5724 Victory Lane ','Snohomish','WA ','98296','US','frank3@adventure-works.com'),
(85,'Brian','Richard','Goldstein',NULL,'797-98-5738','Cell','730-555-0117','Home','8157 W. Book ','Bothell','WA ','98011','US','brian2@adventure-works.com'),
(86,'Ryan','L','Cornelsen',NULL,'789-21-3563','Work','208-555-0114','Home','177 11th Ave ','Sammamish','WA ','98074','US','ryan0@adventure-works.com'),
(87,'Cristian','K','Petculescu',NULL,'786-30-0458','Cell','434-555-0133','Home','7166 Brock Lane ','Seattle','WA ','98104','US','cristian0@adventure-works.com'),
(88,'Betsy','A','Stadick',NULL,'625-36-3100','Cell','405-555-0171','Home','5672 Hale Dr. ','Bothell','WA ','98011','US','betsy0@adventure-works.com'),
(89,'Patrick','C','Wedge',NULL,'022-22-1768','Cell','413-555-0124','Home','3067 Maya ','Bellevue','WA ','98004','US','patrick0@adventure-works.com'),
(90,'Danielle','C','Tiedt',NULL,'084-75-7540','Cell','500-555-0172','Home','5203 Virginia Lane ','Kenmore','WA ','98028','US','danielle0@adventure-works.com'),
(91,'Kimberly','B','Zimmerman',NULL,'925-58-4507','Work','123-555-0167','Home','8656 Lakespring Place ','Seattle','WA ','98104','US','kimberly0@adventure-works.com'),
(92,'Tom','M','Vande Velde',NULL,'934-61-1931','Work','295-555-0161','Home','5242 Marvelle Ln. ','Everett','WA ','98201','US','tom0@adventure-works.com'),
(93,'Kok-Ho','T','Loh',NULL,'418-52-8714','Work','999-555-0155','Home','3708 Montana ','Bellevue','WA ','98004','US','kok-ho0@adventure-works.com'),
(94,'Russell',NULL,'Hunter',NULL,'351-20-5698','Work','786-555-0144','Home','7616 Honey Court ','Seattle','WA ','98104','US','russell0@adventure-works.com'),
(95,'Jim','H','Scardelis',NULL,'755-03-9369','Work','679-555-0113','Home','172 Turning Dr. ','Edmonds','WA ','98020','US','jim0@adventure-works.com'),
(96,'Elizabeth','I','Keyser',NULL,'464-52-0317','Cell','318-555-0137','Home','350 Pastel Drive ','Kent','WA ','98031','US','elizabeth0@adventure-works.com'),
(97,'Mandar','H','Samant',NULL,'498-80-1203','Work','140-555-0132','Home','2598 La Vista Circle ','Duvall','WA ','98019','US','mandar0@adventure-works.com'),
(98,'Sameer','A','Tejani',NULL,'969-35-9160','Cell','990-555-0172','Home','5379 Treasure Island Way # 14','Duvall','WA ','98019','US','sameer0@adventure-works.com'),
(99,'Nuan',NULL,'Yu',NULL,'589-12-2342','Cell','913-555-0184','Home','3454 Bel Air Drive ','Bellevue','WA ','98004','US','nuan0@adventure-works.com'),
(100,'Lolan','B','Song',NULL,'130-05-1131','Cell','582-555-0178','Home','8152 Claudia Dr. ','Edmonds','WA ','98020','US','lolan0@adventure-works.com')
The "Case Insensitive" version...
/* ======================================================================
Case Insensitive Query
====================================================================== */
;WITH n (n) AS (
SELECT 1 FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) n (n)
), Tally (n) AS (
SELECT TOP 121
ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM n n1, n n2, n n3, n n4
),x AS (
SELECT v, ROW_NUMBER() OVER (ORDER BY NEWID()) AS rn1, ROW_NUMBER() OVER (ORDER BY NEWID()) AS rn2
FROM (VALUES ('0'),('1'),('2'),('3'),('4'),('5'),('6'),('7'),('8'),('9')) n (v)
UNION ALL
SELECT v, ROW_NUMBER() OVER (ORDER BY NEWID()) + 10 AS rn1, ROW_NUMBER() OVER (ORDER BY NEWID()) + 10 AS rn2
FROM (VALUES ('A'),('B'),('C'),('D'),('E'),('F'),('G'),('H'),('I'),('J'),('K'),('L'),('M'),
('N'),('O'),('P'),('Q'),('R'),('S'),('T'),('U'),('V'),('W'),('X'),('Y'),('Z')) a (v)
), rv AS (
SELECT
CAST(x1.v AS CHAR(1)) AS BegVal,
CAST(x2.v AS CHAR(1)) AS EndVal
FROM
x x1
JOIN x x2
ON x1.rn1 = x2.rn2
)
SELECT TOP 100
x.PersonID,
(SELECT COALESCE((SELECT TOP 1 rv.EndVal FROM rv WHERE rv.BegVal = CAST(SUBSTRING(x.FirstName, t.n, 1) AS CHAR(1))), CAST(SUBSTRING(x.FirstName, t.n, 1) AS CHAR(1)))
FROM Tally t
WHERE t.n <= LEN(FirstName)
FOR XML PATH(''),TYPE).value('.','NVARCHAR(MAX)') AS FirstName,
x.MiddleName,
(SELECT COALESCE((SELECT TOP 1 rv.EndVal FROM rv WHERE rv.BegVal = CAST(SUBSTRING(x.LastName, t.n, 1) AS CHAR(1))), CAST(SUBSTRING(x.LastName, t.n, 1) AS CHAR(1)))
FROM Tally t
WHERE t.n <= LEN(LastName)
FOR XML PATH(''),TYPE).value('.','NVARCHAR(MAX)') AS LastName,
x.Suffix,
(SELECT COALESCE((SELECT TOP 1 rv.EndVal FROM rv WHERE rv.BegVal = CAST(SUBSTRING(x.SSN, t.n, 1) AS CHAR(1))), CAST(SUBSTRING(x.SSN, t.n, 1) AS CHAR(1)))
FROM Tally t
WHERE t.n <= LEN(SSN)
FOR XML PATH(''),TYPE).value('.','NVARCHAR(MAX)') AS SSN,
x.PhoneType,
(SELECT COALESCE((SELECT TOP 1 rv.EndVal FROM rv WHERE rv.BegVal = CAST(SUBSTRING(x.PhoneNumber, t.n, 1) AS CHAR(1))), CAST(SUBSTRING(x.PhoneNumber, t.n, 1) AS CHAR(1)))
FROM Tally t
WHERE t.n <= LEN(PhoneNumber)
FOR XML PATH(''),TYPE).value('.','NVARCHAR(MAX)') AS PhoneNumber,
x.AddressType,
(SELECT COALESCE((SELECT TOP 1 rv.EndVal FROM rv WHERE rv.BegVal = CAST(SUBSTRING(x.StreetAddress, t.n, 1) AS CHAR(1))), CAST(SUBSTRING(x.StreetAddress, t.n, 1) AS CHAR(1)))
FROM Tally t
WHERE t.n <= LEN(StreetAddress)
FOR XML PATH(''),TYPE).value('.','NVARCHAR(MAX)') AS StreetAddress,
x.City,
x.StateCode,
x.PostalCode,
x.CountryCode,
(SELECT COALESCE((SELECT TOP 1 rv.EndVal FROM rv WHERE rv.BegVal = CAST(SUBSTRING(x.EmailAddress, t.n, 1) AS CHAR(1))), CAST(SUBSTRING(x.EmailAddress, t.n, 1) AS CHAR(1)))
FROM Tally t
WHERE t.n <= LEN(EmailAddress)
FOR XML PATH(''),TYPE).value('.','NVARCHAR(MAX)') AS EmailAddress
FROM
dbo.PersonInfoSmall x
And the "Case Sensitive" version...
/* ======================================================================
Case Sensitive Query
====================================================================== */
;WITH n (n) AS (
SELECT 1 FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) n (n)
), Tally (n) AS (
SELECT TOP 121
ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM n n1, n n2, n n3, n n4
),x AS (
SELECT v, ROW_NUMBER() OVER (ORDER BY NEWID()) AS rn1, ROW_NUMBER() OVER (ORDER BY NEWID()) AS rn2
FROM (VALUES ('0'),('1'),('2'),('3'),('4'),('5'),('6'),('7'),('8'),('9')) n (v)
UNION ALL
SELECT v, ROW_NUMBER() OVER (ORDER BY NEWID()) + 10 AS rn1, ROW_NUMBER() OVER (ORDER BY NEWID()) + 10 AS rn2
FROM (VALUES ('A'),('B'),('C'),('D'),('E'),('F'),('G'),('H'),('I'),('J'),('K'),('L'),('M'),
('N'),('O'),('P'),('Q'),('R'),('S'),('T'),('U'),('V'),('W'),('X'),('Y'),('Z')) a (v)
UNION ALL
SELECT v, ROW_NUMBER() OVER (ORDER BY NEWID()) + 36 AS rn1, ROW_NUMBER() OVER (ORDER BY NEWID()) + 36 AS rn2
FROM (VALUES ('a'),('b'),('c'),('d'),('e'),('f'),('g'),('h'),('i'),('j'),('k'),('l'),('m'),
('n'),('o'),('p'),('q'),('r'),('s'),('t'),('u'),('v'),('w'),('x'),('y'),('z')) a (v)
), rv AS (
SELECT
CAST(x1.v AS CHAR(1)) COLLATE Latin1_General_CS_AS AS BegVal,
CAST(x2.v AS CHAR(1)) AS EndVal
FROM
x x1
JOIN x x2
ON x1.rn1 = x2.rn2
)
SELECT TOP 100
x.PersonID,
(SELECT COALESCE((SELECT TOP 1 rv.EndVal FROM rv WHERE rv.BegVal = CAST(SUBSTRING(x.FirstName, t.n, 1) AS CHAR(1))), CAST(SUBSTRING(x.FirstName, t.n, 1) AS CHAR(1)) COLLATE Latin1_General_CS_AS)
FROM Tally t
WHERE t.n <= LEN(FirstName)
FOR XML PATH(''),TYPE).value('.','NVARCHAR(MAX)') AS FirstName,
x.MiddleName,
(SELECT COALESCE((SELECT TOP 1 rv.EndVal FROM rv WHERE rv.BegVal = CAST(SUBSTRING(x.LastName, t.n, 1) AS CHAR(1))), CAST(SUBSTRING(x.LastName, t.n, 1) AS CHAR(1)) COLLATE Latin1_General_CS_AS)
FROM Tally t
WHERE t.n <= LEN(LastName)
FOR XML PATH(''),TYPE).value('.','NVARCHAR(MAX)') AS LastName,
x.Suffix,
(SELECT COALESCE((SELECT TOP 1 rv.EndVal FROM rv WHERE rv.BegVal = CAST(SUBSTRING(x.SSN, t.n, 1) AS CHAR(1))), CAST(SUBSTRING(x.SSN, t.n, 1) AS CHAR(1)) COLLATE Latin1_General_CS_AS)
FROM Tally t
WHERE t.n <= LEN(SSN)
FOR XML PATH(''),TYPE).value('.','NVARCHAR(MAX)') AS SSN,
x.PhoneType,
(SELECT COALESCE((SELECT TOP 1 rv.EndVal FROM rv WHERE rv.BegVal = CAST(SUBSTRING(x.PhoneNumber, t.n, 1) AS CHAR(1))), CAST(SUBSTRING(x.PhoneNumber, t.n, 1) AS CHAR(1)) COLLATE Latin1_General_CS_AS)
FROM Tally t
WHERE t.n <= LEN(PhoneNumber)
FOR XML PATH(''),TYPE).value('.','NVARCHAR(MAX)') AS PhoneNumber,
x.AddressType,
(SELECT COALESCE((SELECT TOP 1 rv.EndVal FROM rv WHERE rv.BegVal = CAST(SUBSTRING(x.StreetAddress, t.n, 1) AS CHAR(1))), CAST(SUBSTRING(x.StreetAddress, t.n, 1) AS CHAR(1)) COLLATE Latin1_General_CS_AS)
FROM Tally t
WHERE t.n <= LEN(StreetAddress)
FOR XML PATH(''),TYPE).value('.','NVARCHAR(MAX)') AS StreetAddress,
x.City,
x.StateCode,
x.PostalCode,
x.CountryCode,
(SELECT COALESCE((SELECT TOP 1 rv.EndVal FROM rv WHERE rv.BegVal = CAST(SUBSTRING(x.EmailAddress, t.n, 1) AS CHAR(1))), CAST(SUBSTRING(x.EmailAddress, t.n, 1) AS CHAR(1)) COLLATE Latin1_General_CS_AS)
FROM Tally t
WHERE t.n <= LEN(EmailAddress)
FOR XML PATH(''),TYPE).value('.','NVARCHAR(MAX)') AS EmailAddress
FROM
dbo.PersonInfoSmall x
Both execution plans are also attached...
Thanks in advance,
Jason
May 18, 2015 at 8:29 pm
I'm not 100% sure about this and hope someone corrects me if I'm wrong...
If the collation that the table is stored in is Case Insensitive then you use a Case Sensitive collation in your query the statistics become meaningless (or at least much less useful) for that particular index query. This because the index/stats are not taking into consideration the case for a specific character. a's and A's can be stored in an index as char(65) when case insensitive whereas a's and A's have to be stored as char(65) [lower case a] and char(97) [upper case A] when case sensitive.
I am guessing here. A good test to see if I'm correct would be to change the table collation to a Case Sensitive collation, re-index, rebuild your stats and try your query again to see if you still get the same warning. I suspect that it will go away.
In this case (pardon the pun) the bad cardinality estimate does not seem to impact the query plan because both queries produce the same query plan irrespective of the warning. Not to say that you could not update your query to produce a better plan all together.
-- Itzik Ben-Gan 2001
May 18, 2015 at 9:40 pm
Alan.B (5/18/2015)
I'm not 100% sure about this and hope someone corrects me if I'm wrong...If the collation that the table is stored in is Case Insensitive then you use a Case Sensitive collation in your query the statistics become meaningless (or at least much less useful) for that particular index query. This because the index/stats are not taking into consideration the case for a specific character. a's and A's can be stored in an index as char(65) when case insensitive whereas a's and A's have to be stored as char(65) [lower case a] and char(97) [upper case A] when case sensitive.
I am guessing here. A good test to see if I'm correct would be to change the table collation to a Case Sensitive collation, re-index, rebuild your stats and try your query again to see if you still get the same warning. I suspect that it will go away.
In this case (pardon the pun) the bad cardinality estimate does not seem to impact the query plan because both queries produce the same query plan irrespective of the warning. Not to say that you could not update your query to produce a better plan all together.
Alan,
Thank you for taking a look. I took your suggestion and altered the table to use COLLATE Latin1_General_CS_AS on all of the 5 "columns to obfuscate" and rebuilt the clustered index... It didn't do anything for the Sensitive version, it just added implicit conversion warnings to the insensitive version.
While the 2 plans are not identical, they are very similar. The insensitive version is faster but I suspect that has more to do with the fact that the sensitive version has to deal with a 62 character map, as oppose to the 36 character map of the insensitive version. Considering the fact that a new map is generated for every unique value in each of the obfuscated columns, the extra values really add up. So... I think you're correct when you suppose that the warnings aren't slowing anything down.
(Yes... This is intentional... The idea is to make it possible to post sensitive data on an open forum without fear that someone will be able to decipher the content. So, the emphasis is on security rather than speed.)
In any case, both scripts (and the same test data) were generated using the same dynamic query just with different parameter selections. The two I posted just so happen two of the nastier options.
May 18, 2015 at 9:48 pm
Jason A. Long (5/18/2015)
Alan.B (5/18/2015)
I'm not 100% sure about this and hope someone corrects me if I'm wrong...If the collation that the table is stored in is Case Insensitive then you use a Case Sensitive collation in your query the statistics become meaningless (or at least much less useful) for that particular index query. This because the index/stats are not taking into consideration the case for a specific character. a's and A's can be stored in an index as char(65) when case insensitive whereas a's and A's have to be stored as char(65) [lower case a] and char(97) [upper case A] when case sensitive.
I am guessing here. A good test to see if I'm correct would be to change the table collation to a Case Sensitive collation, re-index, rebuild your stats and try your query again to see if you still get the same warning. I suspect that it will go away.
In this case (pardon the pun) the bad cardinality estimate does not seem to impact the query plan because both queries produce the same query plan irrespective of the warning. Not to say that you could not update your query to produce a better plan all together.
Alan,
Thank you for taking a look. I took your suggestion and altered the table to use COLLATE Latin1_General_CS_AS on all of the 5 "columns to obfuscate" and rebuilt the clustered index... It didn't do anything for the Sensitive version, it just added implicit conversion warnings to the insensitive version.
While the 2 plans are not identical, they are very similar. The insensitive version is faster but I suspect that has more to do with the fact that the sensitive version has to deal with a 62 character map, as oppose to the 36 character map of the insensitive version. Considering the fact that a new map is generated for every unique value in each of the obfuscated columns, the extra values really add up. So... I think you're correct when you suppose that the warnings aren't slowing anything down.
(Yes... This is intentional... The idea is to make it possible to post sensitive data on an open forum without fear that someone will be able to decipher the content. So, the emphasis is on security rather than speed.)
In any case, both scripts (and the same test data) were generated using the same dynamic query just with different parameter selections. The two I posted just so happen two of the nastier options.
Very interesting. Were the implicit conversion warnings referring to an implicit conversion to nvarchar(max)?
-- Itzik Ben-Gan 2001
May 18, 2015 at 9:52 pm
Nope... All CHAR(1) to CHAR(1)... (not shure how the [Union####] come into play...)
here's the warning section of the plan.
<Warnings>
<PlanAffectingConvert ConvertIssue="Cardinality Estimate" Expression="CONVERT_IMPLICIT(char(1),CONVERT(char(1),[Union1063],0),0)" />
<PlanAffectingConvert ConvertIssue="Cardinality Estimate" Expression="CONVERT_IMPLICIT(char(1),CONVERT(char(1),[Union1094],0),0)" />
<PlanAffectingConvert ConvertIssue="Cardinality Estimate" Expression="CONVERT_IMPLICIT(char(1),CONVERT(char(1),[Union1168],0),0)" />
<PlanAffectingConvert ConvertIssue="Cardinality Estimate" Expression="CONVERT_IMPLICIT(char(1),CONVERT(char(1),[Union1199],0),0)" />
<PlanAffectingConvert ConvertIssue="Cardinality Estimate" Expression="CONVERT_IMPLICIT(char(1),CONVERT(char(1),[Union1257],0),0)" />
<PlanAffectingConvert ConvertIssue="Cardinality Estimate" Expression="CONVERT_IMPLICIT(char(1),CONVERT(char(1),[Union1354],0),0)" />
<PlanAffectingConvert ConvertIssue="Cardinality Estimate" Expression="CONVERT_IMPLICIT(char(1),CONVERT(char(1),[Union1385],0),0)" />
<PlanAffectingConvert ConvertIssue="Cardinality Estimate" Expression="CONVERT_IMPLICIT(char(1),CONVERT(char(1),[Union1459],0),0)" />
<PlanAffectingConvert ConvertIssue="Cardinality Estimate" Expression="CONVERT_IMPLICIT(char(1),CONVERT(char(1),[Union1490],0),0)" />
<PlanAffectingConvert ConvertIssue="Cardinality Estimate" Expression="CONVERT_IMPLICIT(char(1),CONVERT(char(1),[Union1548],0),0)" />
<PlanAffectingConvert ConvertIssue="Cardinality Estimate" Expression="CONVERT_IMPLICIT(char(1),CONVERT(char(1),[Union1645],0),0)" />
<PlanAffectingConvert ConvertIssue="Cardinality Estimate" Expression="CONVERT_IMPLICIT(char(1),CONVERT(char(1),[Union1676],0),0)" />
<PlanAffectingConvert ConvertIssue="Cardinality Estimate" Expression="CONVERT_IMPLICIT(char(1),CONVERT(char(1),[Union1750],0),0)" />
<PlanAffectingConvert ConvertIssue="Cardinality Estimate" Expression="CONVERT_IMPLICIT(char(1),CONVERT(char(1),[Union1781],0),0)" />
<PlanAffectingConvert ConvertIssue="Cardinality Estimate" Expression="CONVERT_IMPLICIT(char(1),CONVERT(char(1),[Union1839],0),0)" />
<PlanAffectingConvert ConvertIssue="Cardinality Estimate" Expression="CONVERT_IMPLICIT(char(1),CONVERT(char(1),[Union1936],0),0)" />
<PlanAffectingConvert ConvertIssue="Cardinality Estimate" Expression="CONVERT_IMPLICIT(char(1),CONVERT(char(1),[Union1967],0),0)" />
<PlanAffectingConvert ConvertIssue="Cardinality Estimate" Expression="CONVERT_IMPLICIT(char(1),CONVERT(char(1),[Union2041],0),0)" />
<PlanAffectingConvert ConvertIssue="Cardinality Estimate" Expression="CONVERT_IMPLICIT(char(1),CONVERT(char(1),[Union2072],0),0)" />
<PlanAffectingConvert ConvertIssue="Cardinality Estimate" Expression="CONVERT_IMPLICIT(char(1),CONVERT(char(1),[Union2130],0),0)" />
<PlanAffectingConvert ConvertIssue="Cardinality Estimate" Expression="CONVERT_IMPLICIT(char(1),CONVERT(char(1),[Union2518],0),0)" />
<PlanAffectingConvert ConvertIssue="Cardinality Estimate" Expression="CONVERT_IMPLICIT(char(1),CONVERT(char(1),[Union2549],0),0)" />
<PlanAffectingConvert ConvertIssue="Cardinality Estimate" Expression="CONVERT_IMPLICIT(char(1),CONVERT(char(1),[Union2623],0),0)" />
<PlanAffectingConvert ConvertIssue="Cardinality Estimate" Expression="CONVERT_IMPLICIT(char(1),CONVERT(char(1),[Union2654],0),0)" />
<PlanAffectingConvert ConvertIssue="Cardinality Estimate" Expression="CONVERT_IMPLICIT(char(1),CONVERT(char(1),[Union2712],0),0)" />
</Warnings>
May 19, 2015 at 9:15 pm
After doing some more digging, the closest thing I could find is an apparent isssue with the Cardinality Estimator, reported by Dave Ballantyne.
New "Type Conversion in Expression....." warning in SQL2012 ,to noisy to practical use
While not identical, the two warning are very similar.
Unfortunately, it appears that the conversion does cause a performance hit. Executing Dave's test code with and without the CONVERT(CHAR(10)...), showed the non converted version consistently executing faster (avg on 35.4 ms vs 40.6 ms with "Discard results after execution" turned on).
Anyone have any ideas???
May 19, 2015 at 11:16 pm
Here is my take on what you are seeing with the warning in the execution plan for the case sensitive query. You are casting data from a case insensitive collation to a case sensitive collation. SQL Server isn't quite sure what to do with this as the only information it has is on the case insensitive data in the database.
I took your case sensitive query, removed all references to collation. I then ran your query against the sample data you provided on my instance of SQL Server 2012 which is configured using Latin1_General_BIN2, a case sensitive collation. No warnings were generated in the execution plan.
Like my simplistic explanation?
May 20, 2015 at 12:12 am
Jason A. Long (5/19/2015)
Unfortunately, it appears that the conversion does cause a performance hit. Executing Dave's test code with and without the CONVERT(CHAR(10)...), showed the non converted version consistently executing faster (avg on 35.4 ms vs 40.6 ms with "Discard results after execution" turned on).Anyone have any ideas???
A collation change is essentially an implicit conversion, which has the same effects as a function used on a column. Less efficient, harder for the cardinality estimator to get an accurate row count etimation. Hence you'll have warnings and slower queries.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 20, 2015 at 6:01 am
Lynn Pettis (5/19/2015)
.Like my simplistic explanation?
Yes I do. Thank you very much. Your answer, combined with Gail's, makes perfect sense. Case sensitive collation isn't something I use on a regular basis... By that, I mean I've only had to use a handful of times when dealing with tables that have an odd ball case sensitive column or a case sensitive database. (IIRC, Our MS Lync database is case sensitive... Didn't enjoy querying it.)
I hadn't ever thought of changing collation as being equivalent to using a function (maybe because the syntax doesn't follow normal function syntax?) before but, in that light, it does make perfect sense.
Thank you both! Your time and answers are very much appreciated.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply