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...

• SQL Cheat Sheet
• SQL Tutorial

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.

↑ Return to Top

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

• Option recompile discussion thread

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

• Why should I consider a Calendar table?

↑ Return to Top

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 

↑ Return to Top

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

↑ Return to Top

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

↑ Return to Top

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

• Umachandar Jayachandran ideas
• MS Feedback Suggestion by Adam Machanic

CTE syntactic sugar

• MSDN related thread
• Another related thread by Umachandar Jayachandran

CTE versus Temp Table

• MSDN Thread by Umachandar Jayachandran
• MSDN thread by Adam Haines

↑ Return to Top

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

↑ Return to Top

Running Total

• MSDN thread with many helpful links
• Lightning Fast Hybrid RUNNING TOTAL - Can you slow it down?

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

• Shrink SQL Database During Restore
• Convert VBA Code to Transact-SQL
• Crazy TSQL Queries play time

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

• Aggregates with multiple tables
• Question on query with sum using 5 tables

Blocking problems

• Blocking sessions script

Structure change problem

• Can not change structure

NOT IN problem

• Why you should never use IN/NOT IN in SQL

JOIN problem 

• Why LEFT JOIN doesn't bring records from the LEFT table

Orphans check

• Discussion about disabled constraints and finding orphan records

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

• Dear FROM clause

MSSQL install on Linux

Questions and Surveys - random order

• Randomize order interesting problem

---

See Also

• Transact-SQL Portal

---

This article participated in the TechNet Guru for October competition and won the Gold prize.