--- title: T-SQL Mistakes: Careless and Careful Write Six Queries description: Six T-SQL anti-patterns are benchmarked side by side on 1.2 million rows in SQL Server 2025, pairing a careless query against a careful rewrite. Wrapping a... canonical: https://daily.dev/posts/t-sql-mistakes-careless-and-careful-write-six-queries-yclc27gxr twitter:card: summary_large_image twitter:site: @dailydotdev og:type: website og:site_name: daily.dev og:title: T-SQL Mistakes: Careless and Careful Write Six Queries | daily.dev og:description: Six T-SQL anti-patterns are benchmarked side by side on 1.2 million rows in SQL Server 2025, pairing a careless query against a careful rewrite. Wrapping a... og:url: https://daily.dev/posts/t-sql-mistakes-careless-and-careful-write-six-queries-yclc27gxr og:image: https://api.daily.dev/og/posts/YClC27gxR.png og:image:alt: T-SQL Mistakes: Careless and Careful Write Six Queries og:image:width: 1200 og:image:height: 630 og:locale: en --- > ## Documentation Index > Fetch the complete documentation index at: https://daily.dev/llms.txt > Use this file to discover all available pages before exploring further. # T-SQL Mistakes: Careless and Careful Write Six Queries **[SQL Authority](https://daily.dev/sources/sql-authority)** · 5 min read · 0 upvotes · 0 comments ## Summary Six T-SQL anti-patterns are benchmarked side by side on 1.2 million rows in SQL Server 2025, pairing a careless query against a careful rewrite. Wrapping a column in YEAR()/MONTH() instead of a date range costs 37 times the reads and 43 times the time. A scalar function called per row costs 257 times the reads of a join. A string built in a loop with += is killed after 831 seconds of CPU and 584 million reads, while STRING_AGG finishes 200,000 rows in 1.7 seconds. NOT IN against a column containing NULL silently returns zero rows instead of the correct 19,998. Careful wins five of six; the DISTINCT-vs-EXISTS pair is a draw where DISTINCT was actually faster. ## Full article daily.dev links to this article rather than hosting it. Read it at the original source: ## Questions this post answers ### Why does WHERE NOT IN (SELECT CustomerID FROM Blocked) return zero rows even though most customers aren't blocked? If the subquery's result set contains even one NULL, NOT IN can never evaluate to true for any row, so the query silently returns zero rows instead of an error. In a benchmark with a 3-row blocked list containing one NULL, NOT IN returned 0 while the equivalent NOT EXISTS version correctly returned 19,998 using only 101 reads versus 60,101. _Anyone debugging a NOT IN query that returns suspiciously empty results can compare fixes like this on daily.dev._ ### Why is wrapping a date column in YEAR() and MONTH() in a WHERE clause slower than a date range comparison? Wrapping a column in a function like YEAR(OrderDate) = 2026 prevents the index on that column from being seeked, forcing a full scan instead. A benchmark on 1.2 million rows showed the function-wrapped version took 2,424 reads and 172 ms, versus 65 reads and 4 ms for an equivalent range filter (OrderDate >= '2026-06-01' AND OrderDate < '2026-07-01'), a 37x difference in reads. _Developers tuning slow date-filtered queries can track optimization patterns like this on daily.dev._ ### Is it faster to use STRING_AGG or a WHILE loop with string concatenation to build a comma-separated list in SQL Server? STRING_AGG is dramatically faster. Building a 200,000-row comma-separated string with a WHILE loop and += concatenation was killed after 831 seconds of CPU time and 584,857,439 logical reads without finishing, because each append copies the entire string so far. STRING_AGG completed the same 200,000 rows in 1,737 ms with 1,250,099 reads on SQL Server 2025. _Developers choosing a string-aggregation approach can compare performance trade-offs like this on daily.dev._ ## Similar posts on daily.dev - [Slow Queries: A Field Guide to Eight Species](https://daily.dev/posts/slow-queries-a-field-guide-to-eight-species-0sf8eayfx) · SQL Authority · 0 upvotes · 0 comments - [SQL Has Rules You Don’t Notice Until Your Query Is Wrong](https://daily.dev/posts/sql-has-rules-you-don-t-notice-until-your-query-is-wrong-ue7ucmkyu) · Medium · 0 upvotes · 0 comments - [ClickHouse Will Let You Be Wrong at Scale](https://daily.dev/posts/clickhouse-will-let-you-be-wrong-at-scale-prxaelmw6) · Mat Duggan · 1 upvotes · 0 comments - [Beautiful T-SQL Queries: Three Could Delete the Company](https://daily.dev/posts/beautiful-t-sql-queries-three-could-delete-the-company-ni51yxeeb) · SQL Authority · 36 upvotes · 7 comments --- Tags: [#backend](https://daily.dev/tags/backend), [#microsoft-sql-server](https://daily.dev/tags/microsoft-sql-server) [View this post on daily.dev](https://daily.dev/posts/t-sql-mistakes-careless-and-careful-write-six-queries-yclc27gxr) ```json {"@context":"https://schema.org","@graph":[{"@type":"Organization","@id":"https://daily.dev/#organization","name":"daily.dev","url":"https://daily.dev","logo":{"@type":"ImageObject","url":"https://daily.dev/apple-touch-icon.png","width":180,"height":180},"sameAs":["https://twitter.com/dailydotdev","https://github.com/dailydotdev","https://www.linkedin.com/company/daily-dev-ltd"]},{"@type":"WebSite","@id":"https://daily.dev/#website","url":"https://daily.dev","name":"daily.dev","publisher":{"@id":"https://daily.dev/#organization"},"potentialAction":{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https://daily.dev/search?q={search_term_string}"},"query-input":"required name=search_term_string"}}]} {"@context":"https://schema.org","@type":"TechArticle","headline":"T-SQL Mistakes: Careless and Careful Write Six Queries","url":"https://daily.dev/posts/t-sql-mistakes-careless-and-careful-write-six-queries-yclc27gxr","mainEntityOfPage":{"@type":"WebPage","@id":"https://daily.dev/posts/t-sql-mistakes-careless-and-careful-write-six-queries-yclc27gxr"},"datePublished":"2026-10-26T15:18:37.319Z","dateModified":"2026-10-26T15:19:08.732Z","description":"Six T-SQL anti-patterns are benchmarked side by side on 1.2 million rows in SQL Server 2025, pairing a careless query against a careful rewrite. Wrapping a...","image":"https://media.daily.dev/image/upload/f_auto,q_auto/v1/posts/f8b30e58622b259c904e06c1e466e6bd?_a=AQAEuop","thumbnailUrl":"https://media.daily.dev/image/upload/f_auto,q_auto/v1/posts/f8b30e58622b259c904e06c1e466e6bd?_a=AQAEuop","isAccessibleForFree":true,"articleSection":"SQL Authority","inLanguage":"en","publisher":{"@type":"Organization","name":"daily.dev","url":"https://daily.dev","logo":{"@type":"ImageObject","url":"https://daily.dev/apple-touch-icon.png","width":180,"height":180}},"author":{"@type":"Organization","name":"SQL Authority","logo":"https://media.daily.dev/image/upload/s--reCGnvgi--/f_auto,q_auto/v1780213436/logos/sql-authority?_a=BAMAMiWQ0","url":"https://daily.dev/sources/sql-authority"},"commentCount":0,"discussionUrl":"https://daily.dev/posts/t-sql-mistakes-careless-and-careful-write-six-queries-yclc27gxr","interactionStatistic":[{"@type":"InteractionCounter","interactionType":{"@type":"LikeAction"},"userInteractionCount":0},{"@type":"InteractionCounter","interactionType":{"@type":"CommentAction"},"userInteractionCount":0}],"keywords":"backend,microsoft-sql-server","timeRequired":"PT5M"} {"@context":"https://schema.org","@type":"BreadcrumbList","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https://daily.dev"},{"@type":"ListItem","position":2,"name":"SQL Authority","item":"https://daily.dev/sources/sql-authority"},{"@type":"ListItem","position":3,"name":"T-SQL Mistakes: Careless and Careful Write Six Queries"}]} {"@context":"https://schema.org","@type":"FAQPage","@id":"https://daily.dev/posts/t-sql-mistakes-careless-and-careful-write-six-queries-yclc27gxr#faq","mainEntity":[{"@type":"Question","name":"Why does WHERE NOT IN (SELECT CustomerID FROM Blocked) return zero rows even though most customers aren't blocked?","acceptedAnswer":{"@type":"Answer","text":"If the subquery's result set contains even one NULL, NOT IN can never evaluate to true for any row, so the query silently returns zero rows instead of an error. In a benchmark with a 3-row blocked list containing one NULL, NOT IN returned 0 while the equivalent NOT EXISTS version correctly returned 19,998 using only 101 reads versus 60,101. Anyone debugging a NOT IN query that returns suspiciously empty results can compare fixes like this on daily.dev."}},{"@type":"Question","name":"Why is wrapping a date column in YEAR() and MONTH() in a WHERE clause slower than a date range comparison?","acceptedAnswer":{"@type":"Answer","text":"Wrapping a column in a function like YEAR(OrderDate) = 2026 prevents the index on that column from being seeked, forcing a full scan instead. A benchmark on 1.2 million rows showed the function-wrapped version took 2,424 reads and 172 ms, versus 65 reads and 4 ms for an equivalent range filter (OrderDate \u003e= '2026-06-01' AND OrderDate \u003c '2026-07-01'), a 37x difference in reads. Developers tuning slow date-filtered queries can track optimization patterns like this on daily.dev."}},{"@type":"Question","name":"Is it faster to use STRING_AGG or a WHILE loop with string concatenation to build a comma-separated list in SQL Server?","acceptedAnswer":{"@type":"Answer","text":"STRING_AGG is dramatically faster. Building a 200,000-row comma-separated string with a WHILE loop and += concatenation was killed after 831 seconds of CPU time and 584,857,439 logical reads without finishing, because each append copies the entire string so far. STRING_AGG completed the same 200,000 rows in 1,737 ms with 1,250,099 reads on SQL Server 2025. Developers choosing a string-aggregation approach can compare performance trade-offs like this on daily.dev."}}]} ```