Voice of the DBA

Tag: T-SQL

Intermediate T-SQL: Window Functions

This is the third hour of a three hour set of presentations on intermediate T-SQL techniques and features.

This presentation covers the window functions that are available in SQL Server. Some of these functions were introduced in SQL Server 2005 and some in SQL Server 2015. We start with a brief introduction to what a window is, how a partition works, and then look at ordering and framing of rows. Then we cover the following in demonstrations:

ROW_NUMBER()

RANK(), DENSE_RANK(), and NTILE()

The OVER clause and windowing in SQL Server 2012

Using PARTITION BY and ORDER BY in the OVER clause

Default framing with RANGE and how ROWS are different

PERCENTILE() functions

This covers a lot of information in an hour, but I try to use small data sets to explain what is happening with the window functions and how they can improve the performance, as well as simplify the code, for a number of common queries.

Slides: TSQL_Windowing.pptx

Code: TSQL_Windowing_code.zip

October 12, 2014
Intermediate T-SQL: PIVOT, UNPIVOT, and APPLY
This is the second of a series of talks I put together on intermediate T-SQL topics that many database developers might not understand. In this talk, we cover the other join-type operators that exist in the FROM clause, outside of the INNER, OUTER, FULL, and CROSS join clauses. We break this hour into three main areas, covering:
- PIVOT and crosstabs
- UNPIVOT
- APPLY
Most of the time is spent on the APPLY operator, which is arguably the most useful of the three. I show how APPLY is like an inner or outer join, and also how it can be used to improve performance of a scalar function, how it can be used to run some queries that are difficult with INNER JOINs, and how we can use this to easily find query plans and code in performance tuning.

The PIVOT operator is compared to a crosstab, which is similar. We see how to write a query using either method. We also show the UNPIVOT operator.

Slides: TSQL_IntermediateQueries.pptx

Code: TSQLPIVOT_Unpivot_Apply.zip
October 12, 2014
Intermediate T-SQL: Writing Cleaner Code
This session is an intermediate T-SQL session that helps users learn how to write better T-SQL code by covering a few items that many database developers might not be aware of. In an hour, users will lean how to:
- write and use CTEs to simplify complex queries.
- learn basic error handling, including recommendations for SQL Server 2012 and beyond with THROW.
- use templates to store common code elements
- tricks in SSMS to speed up code development
- understand what a tally table is and how to use it to perform a few common tasks.
This is the beginning of a three hour series I have on intermediate T-SQL code.

Slides: TSQL_WritingCleanerCode.pptx

Code: TSQL_CleanerCode.zip
October 12, 2014
Testing your API
I think learning to better test our software, including the database objects, is one of the ways in which we’ll build better software applications in the future. Testing is a complex subject, but this is part of a series that looks at ways in which you can use tSQLt.

Checking Table Metadata

One of the easy tests you can write is to compare the meta data of an object to a known quantity. The easy way to do that in tSQLt is to use the AssertResultSetsHaveSameMetaData function. This function compares the structure of two result sets, covering names, ordering, and data types, to determine if they are the same.

Here’s a quick example of how that’s done.

Let’s assume I have this table:
```
 create table Articles  (
    [ArticlesID] [int] identity(1,1) not null,
    [AuthorID] [int] null,
    [Title] [char](142) null,
    [Description] [varchar](max) null,
    [Article] [varchar](max) null,
    [PublishDate] [datetime] null,
    [ModifiedDate] [datetime] null,
    [URL] [char](200) null,
    [Comments] [int] null
  );
```
If I wanted to write a test in tSQLt to check this table for changes or alterations, here’s what I’d do in code:

create procedure Articles.[test Articles_Check_metadata]

as

begin

–Assemble

create table Articles.Expected

(

    [ArticlesID] [int] identity(1,1) not null,

    [AuthorID] [int] null,

    [Title] [char](142) null,

    [Description] [varchar](max) null,

    [Article] [varchar](max) null,

    [PublishDate] [datetime] null,

    [ModifiedDate] [datetime] null,

    [URL] [char](200) null,

    [Comments] [int] null

);

–Act

–Assert

exec tsqlt.AssertResultSetsHaveSameMetaData

@expectedCommand = N’select * from Articles.Expected’,

@actualCommand = N’select * from articles’

;

end

;

go

Note that I don’t have an ACT section in this test.

The Assemble section is easy. I record the size and shape of my table. This will be what I compare the actual table to in the database.

The Assert section is a call to AssertResultSetsHaveSameMetaData, with a SELECT * from the real table being compared to the same SELECT from the expected result table I created. If these match, I pass the test. If they don’t, the test fails.

Why?

This seems silly, I know. What does it matter if the table changes, and it certainly will need to change. I definitely questioned the value of a test like this when I first saw the example. However when I thought about it, and thought about the places in which I’ve developed databases, this makes some sense.

Imagine that I have 3 or 4 (or more) developers. As we get new requirements, we’ll change the schema over time. Imagine that I actually have views built on this table, and other procedures and functions, all of which have some tests on them. If I change this schema, and run a test suite, I could see multiple failures. If I did that, one would hope I realized that the addition of a column here (or a rename) would cause those issues. However if I changed a couple things before running a test, which is something I might do at times, having this test fail tells me quickly that the schema was altered. If someone else changed the schema, I also quickly see that this change was to the schema.

It’s not a big change, but it does allow me to determine that I need to refactor all the objects (potentially) that depend on this table. I can go do that work now, or add it to the list of tasks for this particular development task, and also fix the tests, which should go quickly.

If the work doesn’t go quickly because I have a lot of objects, then I’m really glad that I learned now this is an issue.

This becomes even more valuable with views and procedures returning result sets. If I add a column, then I may or may not want views to change, but certainly a check of view meta data will tell me if they do.
October 2, 2014