Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
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.