SQL Server Development home

Code Formatting JOINs APPLY CTEs Spatial
XML Source Code Control Miscellaneous

SQL – Logical Query Processing Steps

Use of SQL Wildcards and Pattern Matching

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

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.

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.
 
JOINs are Easy
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
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 operator by Itzik Ben-Gan and Umachandar Jayachandran .
 
SQL Server APPLY Basics
      (A Simple-Talk article by Robert Sheldon)
 
Using Cross Apply in SQL Server 2005
      (A SQLTeam article by Bill Graziano)
Using Apply
      (An MSDN resource)
Taking a Look at CROSS APPLY
      (An October 18, 2007 SQLTeam blog post
         by Jeff Smith)
Using T-SQL CROSS APPLY and OUTER APPLY
      (A 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:
SQL Server Common Table Expressions (CTE) by Syed Shanu at SQLShack
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.
Recursive Common Table Expression (CTE) links:
Recursive Queries using Common Table Expressions (CTE) in SQL Server
by Edwin Sarmiento
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.

Spatial / Geometry / Geography references

Beginning Spatial with SQL Server 2008 Apress, by Alastair Aitchison
Pro Spatial with SQL Server 2012 Apress, by Alastair Aitchison
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
Wikipedia article on NULL
Recommended by student Julie Vanbuskirk. Good examples of several NULL issues.
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