T-SQL Useful Links
This article will share collection of links in regards to various aspects in Transact-SQL language. Many of these links come very handy answering various questions in SQL Server related forums.
Some links in this article may be outdated and no longer working :( If you find such link, please update the article with correct reference or remove it.
T-SQL Resources for Beginners and not so...
Select Top N Rows per Group
- Optimizing TOP N per Group Queries - blog by Itzik Ben-Gan explaining various optimization ideas. (4/22/2020 - Original link no longer working, so I found in archives a new link and updated)
- Including an Aggregated Column's Related Values - this blog presents several solutions of the problem with explanations for each.
- Including an Aggregated Column's Related Values - Part 2 - the second blog in the series with use cases for the previous blog.
Performance Optimization
- Speed Up Performance And Slash Your Table Size By 90% By Using Bitwise Logic - interesting and novel blog by Denis Gobo.
- Only In A Database Can You Get 1000% + Improvement By Changing A Few Lines Of Code - very impressive blog by Denis Gobo.
- Slow in the Application, Fast in SSMS? - comprehensive long article by Erland Sommarskog.
- Performance consideration when using a Table Variable - Peter Larsson article.
- LEFT JOIN vs NOT EXISTS - performance comparison by Gail Shaw.
EXECUTE vs sp_ExecuteSQL
- Avoid Conversions In Execution Plans By Using sp_executesql Instead of Exec - by Denis Gobo.
- Changing exec to sp_executesql doesn't provide any benefit if you are not using parameters correctly - by Denis Gobo.
SQL Server Internals
- How SQL Server stores data - by Dmitri Korotkevich.
- Inside the Storage Engine: Anatomy of a record - by Paul Randal.
- Advanced T-SQL Tuning - Why Internals Knowledge Matters - very interesting article by Paul White.
- Brad's Sure Guide to SQL Storage Compress
- Do not use spaces or other invalid characters in your column names - helpful tip by George Mastros.
Dynamic Search
- Do you use ISNULL(...). Don't, it does not perform - short blog by Denis Gobo.
- Dynamic Search Conditions in T-SQL Version for SQL 2008 (SP1 CU5 and later) - long and comprehensive article by Erland Sommarskog.
- Catch All Queries - short blog by Gail Shaw.
- Sunday T-SQL tip: How to select data with unknown parameter set - nice blog by Dmitri Korotkevich.
- Relevant MSDN forum's thread
- Is this worth the effort - Discussion about NULL integer parameters.
Option recompile
Dates
- Dear ISV: You’re Keeping Me Awake Nights with Your VARCHAR() Dates - Hopefully this article is strong enough to convince you to never use varchar for dates.
- The ultimate guide to the datetime datatypes - very long and comprehensive article by Tibor Karaszi.
- Bad habits to kick : mis-handling date / range queries - from the Aaron Bertrand Series of Bad Habits to Kick
- Date Range WHERE Clause Simplification - article by Erik E.
- Weekly data thread
- T-SQL: Date Related Queries - Naomi's TechNet WiKi article.
- How to get the first and last day of the Month, Quarter, Year
- Split Date Range into Months
Calendar table
Gaps and Islands
- T-SQL: Gaps and Islands Problem
- MSDN Thread with Hunchback solution
- Refactoring Ranges - blog by Plamen Ratchev.
Concurrency
- Patterns that do not work as expected - by Alex Kuznetsov.
- Developing Modifications that Survive Concurrency - very long and interesting article by Alex Kuznetsov.
Parameter Sniffing
- Parameter Sniffing - blog by Plamen Ratchev.
Cursors
- The Truth about Cursors - Part 1 - Series of blogs about cursors by Brad Schulz.
- The Truth about Cursors - Part 2
- The Truth about Cursors - Part 3
Information about All objects
- How to get information about all databases without a loop
- How to search a value in all columns in all tables
- How to script all stored procedures in a database
- Find All Tables With Triggers In SQL Server
- Find all Primary and Foreign Keys In A Database
String Manipulations
- Handy String Functions - several functions emulating VFP functions by Brad Schulz.
- MSDN thread about RegEx in T-SQL
- CLR RegEx - interesting series about CLR RegEx
- Create Random String - 7 different options including CLR code.
String Split
- Arrays & Lists in SQL Server - long article by Erland Sommarskog.
- Integer List Splitting - by Brad Schulz.
- Splitting list of integers - another roundup - by Aaron Bertrand.
- Tally OH! An Improved SQL 8K “CSV Splitter” Function - by Jeff Moden.
XML
- XML get related tables info
- XML Shred Issues
- XML Performance
- MSDN Thread about XML Update in a loop
- SQL Server - (XML,XQUERY,XPATH)
- Jacob Sebastian XML Blogs
Concatenate Rows
- MSDN thread about concatenating rows
- Making a list and checking it twice
- Concatenating Rows - Part 1
- Concatenating Rows - Part 2
- String concatenation techniques
Common Table Expression (CTE)
- CTE and hierarchical queries
- CTE: Coolest T-SQL Enhancement - interesting blog by Brad Schulz.
CTE Performance
CTE syntactic sugar
CTE versus Temp Table
PIVOT & UNPIVOT
- Understanding SQL Server 2000 Pivot with Aggregates
- Dynamic Pivot on multiple columns
- T-SQL: Dynamic Pivot on Multiple Columns
- SQL Server Pivot
- Spotlight on UNPIVOT, Part 1
- Spotlight on UNPIVOT, Part 2
Running Total
ASP.NET
- Getting the identity of the most recently added record - Mikesdotnetting blog.
- How to insert information into multiple related tables and return ID using SQLDataSource
- How to Avoid SQL Injection Attack - Long FAQ on ASP.NET forum.
- SQL Server 2008 Table-Valued Parameters and C# Custom Iterators: A Match Made In Heaven!
Other Topics
Design Decisions
- Surrogate vs. Natural Keys - Quiz question and answers.
- DATABASE DESIGN - SURROGATE KEYS: PART 1 OF MANY (RULES FOR SURROGATE KEYS, E. F. CODD AND C J DATE RESEARCH AND PROBLEMS THEY SOLVE) - very good article by Tony Rogerson.
- Sub Queries in Check Constraint
- Parent-Children's data as an hierarchical tree
Many tables JOIN calculation problem
Blocking problems
Deleting Records
Structure change problem
NOT IN problem
JOIN problem
Orphans check
Update Records in batch
- Update Records in Batch
- BULK INSERT into a table with specific columns
- Using Bulk Insert to import inconsistent data format (using pure T-SQL)
UPDATE FROM
MSSQL install on Linux
Questions and Surveys - random order
See Also
This article participated in the TechNet Guru for October competition and won the Gold prize.