How does case sensitive affects searches in SQL Server
I see regularly the following search:
SELECT * FROM table_abc WHERE UPPER(column_name) = 'XYZ'
- Is the UPPER(column_name) = 'XYZ' necessary? Is there a better way?
- The UPPER(column_name) = 'XYZ' is because the programmer does not know how is the data represented.
- This is not restricted to UPPER but is also used as LOWER.
Data used
- USE adventureworkslt;
- go
- SELECT TOP 30 PERCENT AddressID, AddressLine1, City,
- StateProvince, CountryRegion
- FROM saleslt.address
- ORDER BY city DESC;
- go
AddressID AddressLine1 City StateProvince CountryRegion ----------- ------------------------------------------------------------ ------------------------------ -------------------------------------------------- -------------------------------------------------- 756 855 East Main Avenue Zeeland Michigan United States 656 7 Pioneer Business Park York England United Kingdom 654 308-3250 Casting Road York England United Kingdom 659 Warrington Ldc Unit 25/2 Woolston England United Kingdom 843 253950 N.E. 178th Place Woodinville Washington United States 620 Horizon Outlet Woodbury Minnesota United States 568 25269 N. Wood Dale Rd Wood Dale Illinois United States 573 25269 Wood Dale Rd. Wood Dale Illinois United States 640 251 The Metro Center Wokingham England United Kingdom 493 6th Floor 5250 Main Street Winnipeg Manitoba Canada 503 P.O. Box 44000 Winnipeg Manitoba Canada 1059 The Quad @ WestView Whittier California United States 455 2550 Signet Drive Weston Ontario Canada 1012 789 West Alameda Westminster Colorado United States 1010 5650 West 88th Ave. Westminster Colorado United States 786 6789 Warren Road Westland Michigan United States 653 Burgess Hill West Sussex England United Kingdom 565 25149 Howard Dr West Chicago Illinois United States 847 25102 Springwater Wenatchee Washington United States 510 750 Philip Street Waterloo Ontario Canada 499 Suite 2502 410 Albert Street Waterloo Ontario Canada 862 3307 Evergreen Blvd Washougal Washington United States 636 Science Park South, Birchwood Warrington England United Kingdom 1081 25871 North Main Street Walnut Creek California United States 641 Bradford W. York England United Kingdom 644 Bradford W. York England United Kingdom 1037 North County Square Vista California United States 1084 25751 University Drive Vista California United States 997 70259 West Sunnyview Ave Visalia California United States 524 9693 Louis H Lafontaine Ville De'anjou Quebec Canada 498 25537 Hillside Avenue Victoria British Columbia Canada 508 2900 - 25055 Dunning Street Vancouver British Columbia Canada 469 400-25155 West Pender St Vancouver British Columbia Canada 457 5250-505 Burning St Vancouver British Columbia Canada 473 595 Burning Street Vancouver British Columbia Canada 468 Po Box 83270 Vancouver British Columbia Canada 545 999 West Georgia St. Vancouver Ontario Canada 1086 57251 Serene Blvd Van Nuys California United States 1045 Nut Tree Factory Vacaville California United States 1039 Mountain Square Upland California United States 879 Valley Mall Union Gap Washington United States 992 9992 Whipple Rd Union City California United States 627 Sports Stores @ Tuscola Tuscola Illinois United States 1062 Factory Stores/tucson Tucson Arizona United States 1072 6500 East Grant Road Tucson Arizona United States 851 994 Sw Cherry Park Rd Troutdale Oregon United States 1073 3065 Santa Margarita Parkway Trabuco Canyon California United States 1022 99235 Hawthorne Blvd. Torrance California United States 463 992 St Clair Ave East Toronto Ontario Canada 555 9950 Ferrand Drive, 9th Floor Toronto Ontario Canada 489 9960 King Street E. Toronto Ontario Canada 537 No. 25800-130 King Street West Toronto Ontario Canada 476 258 King Street East Toronto Ontario Canada 454 3255 Front Street West Toronto Ontario Canada 461 2560 Bay Street Toronto Ontario Canada 517 2573 Dufferin Street Toronto Ontario Canada 448 2575 Bloor Street East Toronto Ontario Canada 478 2545 King Street West Toronto Ontario Canada 470 2511 Baker Road Toronto Ontario Canada 550 2520 Flanders Road Toronto Ontario Canada 447 22580 Free Street Toronto Ontario Canada 538 25 Danger Street West Toronto Ontario Canada 480 25 First Canadian Place Toronto Ontario Canada 482 2500 University Avenue Toronto Ontario Canada 549 609 Evans Avenue Toronto Ontario Canada 548 63 Free St. Toronto Ontario Canada 462 630 University Avenue Toronto Ontario Canada 446 52560 Free Street Toronto Ontario Canada 452 55 Lakeshore Blvd East Toronto Ontario Canada 547 7000 Victoria Park Avenue Toronto Ontario Canada 477 6th Floor Ferguson Block Toronto Ontario Canada 539 6th Floor, 25st Canadian Place Toronto Ontario Canada 1104 9927 N. Main St. Tooele Utah United States 906 7009 Sw Hall Blvd. Tigard Oregon United States 863 2507 Pacific Ave S Tacoma Washington United States 894 3390 South 23rd St. Tacoma Washington United States 506 255117 - 101 Avenue Surrey British Columbia Canada 474 252345 8810th Avenue Surrey British Columbia Canada 509 Suite 25800 3401 - 10810th Avenue Surrey British Columbia Canada 1087 253731 West Bell Road Surprise Arizona United States 619 First Colony Mall Sugar Land Texas United States 1013 54254 Pacific Ave. Stockton California United States 615 Fountains On The Lake Stafford Texas United States 902 950 Gateway Street Springfield Oregon United States 873 44606 N Division St Spokane Washington United States 878 4210 E Sprague Ave Spokane Washington United States 899 North 93270 Newport Highway Spokane Washington United States 1008 910 Main Street. Sparks Nevada United States 788 2533 Eureka Rd. Southgate Michigan United States 776 997000 Telegraph Rd. Southfield Michigan United States 1032 Simi @ The Plaza Simi Valley California United States 534 Depot 80 Sillery Quebec Canada 1058 Riverside Sherman Oaks California United States 856 3025 E Waterway Blvd Shelton Washington United States 872 755 W Washington Ave Ste D Sequim Washington United States 876 765 Delridge Way Sw Seattle Washington United States 849 4251 First Avenue Seattle Washington United States 859 1050 Oak Street Seattle Washington United States 832 2251 Elliot Avenue Seattle Washington United States 1094 25250 N 90th St Scottsdale Arizona United States 1093 7750 E Marching Rd Scottsdale Arizona United States 479 2550 Middlefield Road Scarborough Ontario Canada 1027 409 Santa Monica Blvd. Santa Monica California United States 1019 4660 Rodeo Road Santa Fe New Mexico United States 1004 25600 E St Andrews Pl Santa Ana California United States 1098 Po Box 252525 Santa Ana California United States 1090 25130 South State Street Sandy Utah United States 877 4781 Highway 95 Sandpoint Idaho United States 1088 99040 California Avenue Sand City California United States 1052 San Diego Factory San Ysidro California United States 991 60025 Bollinger Canyon Road San Ramon California United States 1074 9920 Bridgepointe Parkway San Mateo California United States 1047 Stevens Creek Shopping Center San Jose California United States 1018 630 N. Capitol Ave. San Jose California United States 1007 77 Beale Street San Francisco California United States 1000 22555 Paseo De Las Americas San Diego California United States 1050 University Town Center San Diego California United States 1079 25150 El Camino Real San Bruno California United States 611 23025 S.W. Military Rd. San Antonio Texas United States 605 7760 N. Pan Am Expwy San Antonio Texas United States 596 72540 Blanco Rd. San Antonio Texas United States 602 6425 Nw Loop 410 San Antonio Texas United States 632 Fiesta Trail Shopping Center San Antonio Texas United States 622 Management Mall San Antonio Texas United States 990 5420 West 22500 South Salt Lake City Utah United States 996 2575 West 2700 South Salt Lake City Utah United States 886 Lancaster Mall Salem Oregon United States 492 99954 Boul. Laurier, Local 060, Place Sainte-Foy Quebec Canada 629 St. Louis Marketplace Saint Louis Missouri United States 584 250880 Baur Blvd Saint Louis Missouri United States 601 6996 South Lindbergh Saint Louis Missouri United States 595 660 Lindbergh Saint Louis Missouri United States 494 Box 99354 300 Union Street Saint John Brunswick Canada 599 70 N.W. Plaza Saint Ann Missouri United States 794 998 Forest Road Saginaw Michigan United States (135 row(s) affected)
I am using this much data as a sample because we will need it for the Saint Louis, San Antonio...
Upper case sensitive search.
- SELECT AddressID, AddressLine1, City,
- StateProvince, CountryRegion
- FROM saleslt.address
- WHERE UPPER(City) = 'SAN ANTONIO';
- go
AddressID AddressLine1 City StateProvince CountryRegion ----------- ------------------------------------------------------------ ------------------------------ -------------------------------------------------- -------------------------------------------------- 611 23025 S.W. Military Rd. San Antonio Texas United States 602 6425 Nw Loop 410 San Antonio Texas United States 596 72540 Blanco Rd. San Antonio Texas United States 605 7760 N. Pan Am Expwy San Antonio Texas United States 632 Fiesta Trail Shopping Center San Antonio Texas United States 622 Management Mall San Antonio Texas United States (6 row(s) affected)
It worked!, SQL Server properly found all the 'San Antonio' addresses.
Execution Plan for the UPPER query
Plain search
Now we are going to do the same search but without the UPPER.
- SELECT AddressID, AddressLine1, City,
- StateProvince, CountryRegion
- FROM saleslt.address
- WHERE City = 'SAN ANTONIO';
- go
AddressID AddressLine1 City StateProvince CountryRegion ----------- ------------------------------------------------------------ ------------------------------ -------------------------------------------------- -------------------------------------------------- 611 23025 S.W. Military Rd. San Antonio Texas United States 602 6425 Nw Loop 410 San Antonio Texas United States 596 72540 Blanco Rd. San Antonio Texas United States 605 7760 N. Pan Am Expwy San Antonio Texas United States 632 Fiesta Trail Shopping Center San Antonio Texas United States 622 Management Mall San Antonio Texas United States (6 row(s) affected)
- It worked!, SQL Server properly found all the 'San Antonio' addresses.
- Same results as the the query with upper(City) = 'SAN ANTONIO';
Execution Plan for the query w/o the UPPER
Notice the estimated operator cost is 0. Can't get any faster.
Conclusion
- The standard install of Microsoft SQL Server is CASE INSENSITIVE.
- Therefore all searches are CASE INSENSITIVE.
To find out if a database is case sensitive or not:
- SELECT CAST(SERVERPROPERTY('collation') AS VARCHAR(32)) AS 'Collation',
- CAST(SERVERPROPERTY('SqlCharSet') AS VARCHAR(10)) AS 'CharSet',
- CAST(SERVERPROPERTY('SqlCharSetName') AS VARCHAR(10)) AS 'CharSet',
- CAST(SERVERPROPERTY('SqlSortOrder') AS VARCHAR(8)) AS 'Sort Order',
- CAST(SERVERPROPERTY('SqlSortOrderName') AS VARCHAR(12)) AS 'Order Name'
Collation CharSet CharSet Sort Order Order Name -------------------------------- ---------- ---------- ---------- ------------ SQL_Latin1_General_CP1_CI_AS 1 iso_1 52 nocase_iso (1 row(s) affected)
The Order Name will tell you if the SQL Server database is case sensitive or not.