Stairway to T-SQL: Beyond The Basics

Following on from his Stairway to T-SQL DML, Gregory Larsen covers more advanced aspects of the T-SQL language such as subqueries.

• Stairway to T-SQL: Beyond The Basics Level 1: The TOP Clause

  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.

• Stairway to T-SQL: Beyond The Basics Level 2: Writing Subqueries

  In this level of the Beyond the T-SQL Basics stairway, we will be discussing the different aspects of a subquery.

• Stairway to T-SQL: Beyond The Basics Level 3: Building a Correlated Subquery

  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.

• Stairway to T-SQL: Beyond The Basics Level 4: Using Views to Simplify Your Query

  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.

• Stairway to T-SQL: Beyond The Basics Level 5: Storing Data in Temporary Tables

  There are times when retrieving data for complex business requirement requires you to temporarily store one or more results sets for a short period of time. Typically these temporary tables are stored in the scope of the current connection, but they may also need to be available across multiple connections.

• Stairway to T-SQL: Beyond The Basics Level 6: Using the CASE Expression and IIF Function

  There are times where you need to write a single T-SQL statement that is able to return different T-SQL expressions based on the evaluation of another expression. When you need this kind of functionality you can use the CASE expression or IIF function to meet this requirement. In this Stairway level Gregory Larsen reviews the CASE and IIF syntax and showing you examples of how the CASE expression and IIF function.

• Stairway to T-SQL: Beyond The Basics Level 7: Controlling the Flow of Your T-SQL

  When you are writing TSQL code there are times when you want to perform the same logic over and over again. To accomplish this you can used the WHILE keyword. There are other times when you want to perform a conditional test and depending on the results of the test you execute one code block for the TRUE condition and possibly a different code block when the condition is FALSE. When you need to conditionally execute code based on the outcome of a condition you can used the IF keyword. In this Stairway level I will be discussing how to use the WHILE and IF constructs to control the flow of your TSQL code.

• Stairway to T-SQL: Beyond The Basics Level 8: Coding Shortcuts using += and -= Operators

  No one wants to use more keystrokes than they have to when they write a chunk of T-SQL code. To help with minimizing the number of characters a T-SQL developer needs to type the Microsoft team introduced three new shortcuts operators when they release SQL Server 2008. These shorts cuts are the String Concatenation, Add EQUALS, and the Subtract EQUALS operators.

• Stairway to T-SQL: Beyond The Basics Level 9: Dynamic T-SQL Code

  There times when you need to write T-SQL code that creates specific T-SQL Code and executes it. When you do this you are creating dynamic T-SQL code. When writing dynamic T-SQL you need to understand how dynamic code opens the possibilities for a SQL injection attack.