Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Guest Columnist: Gregory Larsen

SQLServerCentral.com profile

Currently a SQL Server DBA. I've been working with SQL Server since 1999. I'm an old-time mainframe DBA. My DBA career started in 1985. Currently studying to obtaining MCDBA.

   Items 1 to 20 of 36    Older >>
 

Stairway to Advanced T-SQL Level 6: Creating Rows Of Data Using The UNPIVOT Operator

The UNPIVOT operator does just the opposite of the PIVOT operator, which we looked at in the previous level. By using the PIVOT operator we can take multiple rows of data and create as single row as output. The UNPIVOT operator will take values from a single row and will create multiple rows. Microsoft introduced the UNPIVOT operator when they rolled out SQL Server 2005. In this level I will be showing you different examples of how to use the UNPIVOT operator.  Read more...
By Gregory Larsen 2015/06/24 | Source: SQLServerCentral.com | Category: stairway series
Rating: |  Discuss |   Briefcase | 4,096 reads

Stairway to Advanced T-SQL Level 4: Record Level Processing Using Transact-SQL Cursors

Using a CURSOR is not normally the best way to process through a set of records. Yet when a seasoned programmer moves to writing TSQL for the first time they frequently look for ways to process a sets of records one row at a time. They do this because they are not used to thinking about processing records as a set. In order to process through a TSQL record set a row at a time you can use a cursor. A cursor is a record set that is defined with the DECLARE CURSOR statement. Cursors can be defined as either read-only or updatable. In this article I will introduce you to using cursors to do record level processing one row at a time.   Read more...
By Gregory Larsen 2015/05/06 | Source: SQLServerCentral.com | Category: stairway series
Rating: |  Discuss |   Briefcase | 4,674 reads

Stairway to Advanced T-SQL Level 5: Turning Data On Its Side Using PIVOT Operator

The PIVOT operator was added to the Transact-SQL language in SQL Server 2005. It allows you to turn your row data on its side, so it can be presented as column data. This is useful when you want take unique column values and have them displayed as column headings, where the column headings are associated with summarized values displayed below each column heading. In this article I will be exploring how to use the PIVOT operator.   Read more...
By Gregory Larsen 2015/05/27 | Source: SQLServerCentral.com | Category: stairway series
Rating: |  Discuss |   Briefcase | 6,869 reads

Stairway to Advanced T-SQL Level 2: Using the APPLY Operator

The APPLY operator allows you to join a record set with a function, and apply the function to every qualifying row of the table (or view). The APPLY operator takes on two formats: CROSS APPLY, or OUTER APPLY. This article will explain the differences between these two formats, and show you examples of how each of these formats work.  Read more...
By Gregory Larsen 2015/01/28 | Source: SQLServerCentral.com | Category: stairway series
Rating: |  Discuss |   Briefcase | 11,512 reads

Stairway to Advanced T-SQL Level 1: Introduction to Advanced Transact SQL Stairway and Using the CROSS JOIN Operator

The first installment of this new stairway series will be discuss the CROSS JOIN operator. This stairway should help readers prepare for passing the Microsoft Certification exam 70-461: Querying Microsoft SQL Server 2012.  Read more...
By Gregory Larsen 2014/12/17 | Source: SQLServerCentral.com | Category: stairway series
Rating: |  Discuss |   Briefcase | 12,704 reads

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.  Read more...
By Gregory Larsen 2014/07/23 | Source: SQLServerCentral.com | Category: stairway series
Rating: |  Discuss |   Briefcase | 7,925 reads

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.  Read more...
By Gregory Larsen 2014/07/09 | Source: SQLServerCentral.com
Rating: |  Discuss |   Briefcase | 9,513 reads

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.  Read more...
By Gregory Larsen 2014/06/25 | Source: SQLServerCentral.com | Category: stairway series
Rating: |  Discuss |   Briefcase | 7,128 reads

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.  Read more...
By Gregory Larsen 2014/04/09 | Source: SQLServerCentral.com | Category: stairway series
Rating: |  Discuss |   Briefcase | 11,310 reads

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.   Read more...
By Gregory Larsen 2014/04/02 | Source: SQLServerCentral.com | Category: stairway series
Rating: |  Discuss |   Briefcase | 9,700 reads

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.  Read more...
By Gregory Larsen 2014/03/19 | Source: SQLServerCentral.com | Category: stairway series
Rating: |  Discuss |   Briefcase | 8,892 reads

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.  Read more...
By Gregory Larsen 2014/03/05 | Source: SQLServerCentral.com | Category: stairway series
Rating: |  Discuss |   Briefcase | 11,363 reads

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.  Read more...
By Gregory Larsen 2014/01/29 | Source: SQLServerCentral.com | Category: stairway series
Rating: |  Discuss |   Briefcase | 13,847 reads

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.  Read more...
By Gregory Larsen 2014/01/15 | Source: SQLServerCentral.com | Category: stairway series
Rating: |  Discuss |   Briefcase | 15,495 reads

Stairway to T-SQL DML Level 12: Using the MERGE Statement

The final level of this stairway looks at the MERGE statement in detail, focusing on how to perform insert, update and delete logic using the MERGE statement.  Read more...
By Gregory Larsen 2014/08/20 (first published: 2013/01/16) | Source: SQLServerCentral.com | Category: stairway series
Rating: |  Discuss |   Briefcase | 18,087 reads

Stairway to T-SQL DML Level 11: How to Delete Rows from a Table

You may have data in a database that was inserted into a table by mistake, or you may have data in your tables that is no longer of value. In either case, when you have unwanted data in a table you need a way to remove it. The DELETE statement can be used to eliminate data in a table that is no longer needed. In this article you will see the different ways to use the DELETE statement to identify and remove unwanted data from your SQL Server tables.  Read more...
By Gregory Larsen 2012/09/19 | Category: stairway series
Rating: |  Discuss |   Briefcase | 10,481 reads

Stairway to T-SQL DML Level 10: Changing Data with the UPDATE Statement

Unless you are working on a reporting-only application you will probably need to update tables in your SQL Server database. To update rows in a table you use the UPDATE statement. In this level we will be discussing how to find and update records in your database, and discuss the pitfalls you might run into when using the UPDATE statement.   Read more...
By Gregory Larsen 2012/10/10 | Category: stairway series
Rating: |  Discuss |   Briefcase | 7,474 reads

Stairway to T-SQL DML Level 8: Using the ROLLUP, CUBE and GROUPING SET operator in a GROUP BY Clause

In this article I will be expanding on my discussion of the GROUP BY clause by exploring the ROLLUP, CUBE and GROUPING SETS operators. These additional GROUP BY operators make it is easy to have SQL Server create subtotals, grand totals, a superset of subtotals, as well as multiple aggregate groupings in a single SELECT statement.  Read more...
By Gregory Larsen 2012/08/22 | Category: stairway series
Rating: |  Discuss |   Briefcase | 13,136 reads

Stairway to T-SQL DML Level 9: Adding Records to a table using INSERT Statement

Not all applications are limited to only retrieving data from a database. Your application might need to insert, update or delete data as well. In this article, I will be discussing various ways to insert data into a table using an INSERT statement.  Read more...
By Gregory Larsen 2012/09/05 | Source: SQLServerCentral.com | Category: stairway series
Rating: |  Discuss |   Briefcase | 8,198 reads

Stairway to T-SQL DML Level 7: Summarizing Data Using a Simple GROUP BY Clause

In this article you will be see how to group data using the simple GROUP BY clause, in order to summarize more complex data.  Read more...
By Gregory Larsen 2012/01/04 | Category: stairway series
Rating: |  Discuss |   Briefcase | 9,969 reads
   Items 1 to 20 of 36    Older >>