{"id":15,"date":"2007-07-19T21:58:15","date_gmt":"2007-07-19T19:58:15","guid":{"rendered":"https:\/\/www.sqlinthewild.co.za\/?p=15"},"modified":"2007-07-19T21:58:15","modified_gmt":"2007-07-19T19:58:15","slug":"functions-in-a-where-clause","status":"publish","type":"post","link":"https:\/\/www.sqlinthewild.co.za\/index.php\/2007\/07\/19\/functions-in-a-where-clause\/","title":{"rendered":"Functions in a where clause"},"content":{"rendered":"

Or ‘How to really slow a query down without trying’<\/em><\/p>\n

Here’s a query that looks innocent enough. Table structure and sample data are at the end of the post.<\/p>\n

-- returns 5 out of 5000 rows\nSELECT InsertDate FROM TestingFunction WHERE LEFT(Code,1)='AA'<\/pre>\n
Knowing that there’s an index on the column code, the optimiser might be expected to use an index seek to satisfy the query. However, the execution plan shows an index scan. Why?<\/p>\n
Any form of function on a column in the where clause of a query will prevent SQL from using an index seek to find the records. Even something as simple as SearchColumn+1 will prevent index seeks.<\/p>\n
This is something I see over and over again in sample queries, on forums and in production code. It’s something that looks fine on the average dev system. Couple hundred rows, it’s hard to see a difference. In a production environment with a couple million rows, it’s a very different story.<\/p>\n
There is often, though not always, a way of rewriting the query to move the function elsewhere. In the above example, the following query is equivalent and allows for an index seek.<\/p>\n
 SELECT InsertDate FROM TestingFunction WHERE Code LIKE 'AA%'<\/pre>\n
Sample data:<\/p>\n
CREATE TABLE TestingFunction (\n    ID int identity,\n    Code CHAR(3),\n    InsertDate DATETIME default getdate()\n)\n\nCREATE NONCLUSTERED INDEX idx_TestingFunction_Code on TestingFunction (Code)\n\n;WITH DataInsert (RowNo) AS (\n    SELECT 1 AS RowNo\n    union all\n    select RowNo+1 FROM DataInsert WHERE RowNo<5000\n)\ninsert into TestingFunction (Code, InsertDate)\nSELECT char(FLOOR(Rand(RowNo*78000)*26)+65) + char(FLOOR(Rand(RowNo*15000)*26)+65) + char(FLOOR(Rand(RowNo*51000)*26)+65),\n    DATEADD(mi,Rand(RowNo*78000)*575000,'2000\/01\/01')\n    FROM DataInsert\n    OPTION (MaxRecursion 5000)<\/pre>\n","protected":false},"excerpt":{"rendered":"
Or ‘How to really slow a query down without trying’ Here’s a query that looks innocent enough. Table structure and sample data are at the end of the post. — returns 5 out of 5000 rows SELECT InsertDate FROM TestingFunction… (Read more)<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2}},"categories":[25,15],"tags":[],"class_list":["post-15","post","type-post","status-publish","format-standard","hentry","category-performance","category-sql-server"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_shortlink":"https:\/\/wp.me\/p7h6n-f","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/posts\/15","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/comments?post=15"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/posts\/15\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/media?parent=15"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/categories?post=15"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/tags?post=15"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}