SQL Server Development home

Code Formatting JOINs APPLY CTEs Geospatial
XML Source Code Control Miscellaneous

SQL – Logical Query Processing Steps

Use of SQL Wildcards and Pattern Matching

Normal Forms by Joe Celko at Redgate Hub (spoiler, there are TEN normal forms)

Implementation of Normalization in SQL Server by Sucharita Das at SQLServerCentral

The “Numbers” or “Tally” Table: What it is and how it replaces a loop by SQL master genius, Jeff Moden at SQLServerCentral

Tally OH! An Improved SQL 8K β€œCSV Splitter” Function also by SQL master genius, Jeff Moden

The SQL Server Numbers Table, Explained – Part 1 by Aaron Bertrand at MSSQLTips

The SQL Server Numbers Table, Explained – Part 2

Using a Table of Numbers by Erland Sommarskog

Performing a Right and Comprehensive Age Calculation, another Tally Table implementation by Sergiy Floka at SQLServerCentral

Code Formatting Resources

Suggested Object Naming Conventions[not at all specific to T-SQL]
Hungarian Notation – The Good, Bad and the Ugly[not at all specific to T-SQL]
A commentary on one notation method
T-SQL Coding Standards by Brian Walker, MSDN(only available as a .PDF that I created before the page became unavailable)
Transact-SQL Formatting Standards (Coding Styles)
By Robert Sheldon at Simple-Talk, and these…

The Basics of Good T-SQL Coding Style

The Basics of Good T-SQL Coding Style – Part 2: Defining Database Objects

The Basics of Good T-SQL Coding Style – Part 3: Querying and Manipulating Data

The Basics of Good T-SQL Coding Style – Part 4: Performance
An online code “beautifier”From wangz.net
See also my “Free Resources for SQL Server” resource as it lists free (and commercial) code formatting tools from vendors like Devart, Quest, and PoorSQL.SQL Prompt is a commercial product from Redgate.
SQL comment header examplesStackOverflow

JOIN References

General JOIN topics
Bunch of Venn diagrams depicting JOINs
 
SQL & the JOIN Operator
From SQLServerCentral.com by Wagner Crivelini.
 
SQL Server Join Examples by Jeremy Kadlec at MSSQLTips.com
 
A Visual Explanation of JOINs
From CodingHorror.com, by Jeff Atwood. Nice diagrams.
 
Visual Representation of SQL Joins
From CodeProject.com.
 
Getting Started with SQL INNER JOIN by Jim Evans at MSSQLTips

JOINs are Easy

Steve Stedman’s JOIN poster v. 4.1

Hidden Pitfalls with INNER JOIN and NOT IN Operators by MMartin1 at SQLServerCentral

Two references on using the BETWEEN Operator in JOIN conditions. Example 1 and Example 2
Your Ultimate Guide to SQL Join: INNER JOIN – Part 1 by Edwin Sanchez at {coding} Sight

Your Ultimate Guide to SQL Joins: OUTER JOIN – Part 2
Join SQL Server tables where columns include NULL values by Greg Robidoux at MSSQLTips
Self JOINs
How to Join to the Same Table Multiple Times for a SQL Server query
by Dallas Snider at MSSQLTips.com
 
The Self Join (Inner and Outer)
by Pinal Dave at SQLAuthority.com
 
Using Self-Joins
(Microsoft TechNet article)
 
Example of where a Self Join is Needed
(see the last query in the article)
Outer JOINs
SQL LEFT JOIN Examples
by John Miner at MSSQLTips

Fun with Outer Joins
by Kenneth Fisher at SQLServerCentral.com
JOIN v. Intersect
Intersect v. Inner Join 
by Pinal Dave at SQLAuthority.com
Cross Joins
The Power of the Cross Join 
by Jeff Smith

Usefulness of Cross Joins specifically as it relates to data warehousing
by Garrett Edmondson at SQLServerCentral.com

APPLY Operator references

Microsoft Virtual Academy presentation on the use of the APPLY operatorby Itzik Ben-Gan and Umachandar Jayachandran

{I don’t think this content is still publicly available, but I may have a copy of the video}
Using Cross Apply in SQL Server 2005A SQLTeam article by Bill Graziano
SQL Server APPLY BasicsA Simple-Talk article by Robert Sheldon
SQL Server CROSS APPLY and OUTER APPLYby Arshad Ali at MSSQLTips
Using ApplyAn MSDN resource
Taking a Look at CROSS APPLYAn October 18, 2007 SQLTeam blog post by Jeff Smith
Using T-SQL CROSS APPLY and OUTER APPLYA Developer.com article by Paul Kimmel
When Should I Use CROSS APPLY over INNER JOIN?From StackOverflow.com

Common Table Expression (CTE) references

Common Table Expression (CTE) links:
Everything You Need to Know About SQL CTE in One Spot by Edwin Sanchez at {coding}Sight
SQL Server Common Table Expressions (CTE) by Syed Shanu at SQLShack
CTE in SQL Server Examples by Ryan Kennedy at MSSQLTips
Guide for CTE in SQL Server by Rajendra Gupta at {coding} Sight
Standard Deviations with CTE in SQL
by Mike Davis, Interesting technique using two CTEs.
Simplify Large Queries with Temporary Tables, Table Variables and CTEs
by Stan Kulp
Usage of CTE: Trick with Dates
by Suresh Maganti
How I Write Queries Using PSQL: Common Table Expressions
by Selena Deckelmann, Although the article is on CTEs in PostgreSQL it’s still useful.
CTEs instead of Derived Tables by Steve Stedman
(A CTE Tutorial on Performance, Stored Procedures, Recursion, Nesting, and the use of Multiple CTEs)

Here are a bunch more CTE links at Steve’s website. He even wrote and sells an ebook at Amazon UK, such as
CTE Hierarchy compared to the alternative
Recursive Common Table Expression (CTE) links:
Recursive Queries using Common Table Expressions (CTE) in SQL Server
by Edwin Sarmiento
Fundamentals of table expressions, Part 6 – Recursive CTEs by Itzik Ben-Gan at SQLPerformance.com
How Recursive Common Table Expresssions Work
by “PracticalDBA”
Using a Recursive CTE to Generate a List
by Svetlana Marinova
SQL Server Function to return a range of dates
by Albert Hetzel, NULL date values are omitted from the result set.
Working With Multi-Select Parameters for SSRS Reports
by Mickey Stuewe, This is really more of a Reporting Services reference but the query example used for the report data involves a recursive CTE.
See Aaron Bertrand’s great example on this page, used for creating a Date Dimension.

Geospatial / Geometry / Geography references

Beginning Spatial with SQL Server 2008
Apress, by Alastair Aitchison
Pro Spatial with SQL Server 2012
Apress, by Alastair Aitchison
Create, Populate, and Transform Geographic Coordinates in SQL Server Tablesby Rick Dobson at MSSQLTips
Bing Maps API documentation
Bing Maps Platform
SQLServerSpatialToolsFormerly a CodePlex project but now on GitHub
Open Geospatial Consortium (OGC)
World Atlas, Longitute & Lattitude facts
SpatialReference.orgFind your references in any number of formats!
MapCruzin website“Your home for Community Mapping and Participatory GIS Software, Tools, Maps, Shapefiles, and Tutorials”.
US CensusCartographic Boundary Files

XML (Extensible Markup Language) references

XML TutorialAn excellent tutorial from W3Schools.com
XPath TutorialA tutorial from W3Schools
XQuery TutorialA tutorial from W3Schools
Another XML Tutorial found at: XML-Training-Guide.comAnd a ton of other resources.
See also this free ebook, “The Art of XSD
Scripts to use XML to insert and update rows in a SQL Server tableby Wayne Ackman at MSSQLTips
SQL Server XML Bulk Loading Exampleby Tim Cullen at MSSQLTips
Commercial examples of Custom XML Markup Languages:
National Digital Forecast Database (NDFD) Extensible Markup Language (XML) (and a web service)
 
ISBNdb.com, Remote Data Access Introduction
Altova provides a bunch of XML and other development tools

Source Code Control references

Database Source Control – The Cribsheetfrom Redgate Hub
Azure DevOps ServerTeam Foundation Server is now called Azure DevOps Server, 2019-03-05
SourceGear Vault and DiffMergeThe Pro version integrates with Visual Studio

See also Eric Sink’s free ebook, “Version Control by Example
Perforce HelixALM
Subversion

VisualSVN // Subversion for Visual Studio
an Apache project

Miscellaneous references

Simple T-SQL Proper Case User-Defined FunctionJeff Smith blog
Columnstore blog by Niko Neugebauer
Wikipedia article on NULL
Recommended by student Julie Vanbuskirk. Good examples of several NULL issues.
Simple Image Import and Export Using T-SQL for SQL Serverby Eli Leiba at MSSQLTips
The Curse and Blessings of Dynamic SQLby Erland Sommarskog
SQL Server Service Broker Example on How to Configure, Send and Receive MessagesFrom MSSQLTips.com by Jayakumar Krishnan.
Gaps in SQL Server Identity Columns
Excellent 404 page at CodeProject
The Connection Strings Reference
Dr. Dobbs – The World of Software Development
How to Call a SharePoint Web Service from a SQL Server CLR FunctionFrom MSSQLTips.com by Ray Barley.
Graph Database references