The handling of dates in TSQL is complex - when SQL Server was Sybase, it was forced by the lack of prevailing standards in SQL to create its own ways of processing and formatting dates and times. Joe Celko looks forward to a future when it is possible to write standard SQL date-processing code with SQL Server.  Read more...

This level discusses how to use a database VIEW to simplify your Transact-SQL(T-SQL) code. By understanding how to use a VIEW you will be able to better support writing T-SQL code to meet complex business requirements. In this article I will be discussing what a database VIEW is and then providing a number of examples to help you understand how you can use a VIEW to implement different coding scenarios.  Read more...

When you've got only a single effective date on a record and you need to artificially create the effective end date based on a following row in T-SQL, the techniques discussed in this article will help you decide what is best.  Read more...

PARSENAME is perhaps the most infrequently used built-in documented function in SQL Server. SQL Server Microsoft Certified Master Wayne Sheffield shows why this nifty function ought to be included in your SQL toolbox.  Read more...

Allowing your application to generate your SQL can be a really bad decision.  Read more...

This stairway level will expand on the subquery topic by discussing a type of subquery known as a correlated subquery, and explores what a correlated subquery is and how it is different from a normal subquery.  Read more...

The way in which SQL Server chooses to join your tables in a query can dramatically affect performance. In this article, Jason Brimhall explains how a hash match works and shows some performance numbers.  Read more...

The client wants to change a date based business rule  Read more...

Steve Jones has a problem with the inconsistency of the CREATE TABLE statement in SQL Server and has an idea on what to do.  Read more...

sp_ExecuteSQL can be used to dynamically execute T-SQL code. Learn how to use it and how to add parameters to control what code is executed.  Read more...

Show the results for a date even if there is no data.  Read more...

Learn how to avoid complex date calculations and increase performance using calendar tables in this article from Todd Fifield.  Read more...

What are your options for sending a variable number of choices in a parameter to a stored procedure? Alex Grinberg looks at three techniques you can use.  Read more...

Window Functions in SQL greatly simplify a whole range of financial and statistical aggregations on sets of data. Because there is less SQL on the page, it is easy to assume that the performance is better too: but is it? Dwain gets out the test harness to investigate.  Read more...

SQL Server MVP Jeff Moden walks us through the classic problem of finding all the "Active" rows for the previous month using start and end dates. This is excellent for those new to T-SQL.  Read more...

How do you configure where your database backups are created? Do you have problems updating all of your backup jobs if the backup location needs to change? See how you can make use of Windows settings and a few lines of simple TSQL to have total control over where you database backups are created.   Read more...

It is ironic that one of the most essential of statistical aggregations, the median, has been so difficult in the past to calculate efficiently in SQL. Dwain Camps sets the candidates to work and identifies the winners and losers.   Read more...

Learn how an outer join works and how you can use it in your applications to find the results you need when matching data isn't in all your tables.  Read more...

It's an old problem with a solution that's nearly as old. SQL Server MVP Jeff Moden shows us the old trick mixed with a slick trick to format the duration as extended hours, minutes, seconds, and milliseconds.   Read more...

The first topic in this stairway will be discussing the TOP clause. The TOP clause allows you to control the number of rows to be returned or affected by a query.  Read more...