Category — T-SQL

The first time I read about the new Date and Time datatypes I though “Well, that’s nice”.  But the more I think about it the happier I am.  No more stripping off the time values to do a date only comparison.  Thank goodness.   Some other pluses;  possible use of indexes, safe to use BETWEEN for date ranges, easier reading of code without all of those CONVERT functions. 

But don’t be so eager to leave out the time part.  You need to be absolutely sure that you don’t need the time piece.  If your not sure, keep the time part in a column separate from the date.

There is some good sample data for these datatypes in the AdventureWorks2008 database.  Take a look at the Human Resources.Employee table, Birthdates & Hire Date columns.  And also look at Human Resources.Shift table, Start Time & End Time columns.

Just the other day I received a call from a developer with a database problem. He was receiving timeouts when he called a particular stored procedure from his application. So I took a look at the proc, reviewed the code, and tried to run it. It just ran and ran and ran, no error messages, nothing. Then I tried to recompile the proc, same thing, just hung. So I stopped it, looked at the amount of data it needed to run, not much data involved, so I cut the proc into pieces and ran it a chuck at a time. Finally, I got to the problem part of the code, the part that just hung. In reading the code, I noticed that it was referencing a linked server, hmmm, where does this point to? So, I go into the Client Network Utility and here we are, an old server that no longer exists! Change the server name and port number and yes! The proc complies and runs! Weird, wonder why it just didn’t just return an error message. Oh well, all in a day’s fun!

What is wrong with this query?

SELECT c.CustomerName, b.BillingID
FROM BillingInfo bi
JOIN Customer c
ON bi.CustomerID = c.CustomerID
JOIN DailyTransactions dt
ON bi.BillingID = dt.BillingID
WHERE dt.Transdate >= @startdate
AND dt.Transdate < @enddate

Well, the first thing that I see is that we are joining to the DailyTransactions table, but we aren’t returning
any columns in the results set. Why? The reason is because the goal of this query is to return all customers
that have transactions in a given period, so we must check the DailyTransations table for this. But we don’t care
if the customer had 1 transaction or 1000, nor do we need any columns of data from that table, so here is better
(quicker!) way to do this:

SELECT c.CustomerName, b.BillingID
FROM BillingInfo bi
JOIN Customer c
ON bi.CustomerID = c.CustomerID
WHERE EXISTS (SELECT 1 FROM DailyTransactions dt
WHERE bi.BillingID = dt.BillingID
AND Transdate >= @startdate
AND Transdate < @enddate)

A WHERE EXISTS will be much faster than a join because as soon as the first match is found for that BillingID,
SQL Server stops looking for more, unlike a join, which will match up every row.

When I fixed a stored procedure with this exact problem, I cut the run time required by two-thirds. Since this stored
procedure loads a dropdown in Reporting Services, that cut the time required to load the report.

When I first started working with the new PIVOT command in SQL Server 2005, it didn’t make any sense to me.  So I created some very simple examples on my own to help with my understanding.  Here is one.  First, I create the CTE (as I explained in my last post):

WITH Sales as (
SELECT  SalesOrderID, Name, OrderDate
FROM Sales.SalesTerritory st
INNER JOIN Sales.SalesOrderHeader soh
ON st.TerritoryID = soh.TerritoryID
WHERE OrderDate >= ‘7/1/2001′
AND OrderDate < ‘8/1/2001′
)

The CTE contains the sales orders and the territory name for that sale during the month of July 2001.  Next we will create the PIVOT.  The goal of the PIVOT is to count the number of sales by territory.  Not very exciting, but a good simple example:

SELECT Convert(varchar(20), OrderDate, 101) AS ‘Sales Date’,
Northwest,
Central,
Southwest,
Canada,
France,
Germany,
Australia
FROM sales  — the CTE above
PIVOT
(
COUNT (SalesOrderID)
FOR Name in ([Northwest], [Central], [Southwest], [Canada], [France], [Germany], [Australia])) as a
ORDER BY OrderDate

One downside to the PIVOT command is you need to know the column names at design time.  They must be hard coded.  The only way you can get around this is to use dynamic SQL.

I was planning to do a series of blogs about the new PIVOT and UNPIVOT functionality in SQL Server 2005, but I’ve decided to take a step back and do a post on Common Table Expressions (CTE’s) first. They are used in conjunction with PIVOT and UNPIVOT to make the code a bit cleaner.

If you have used derived tables in your scripts before than Common Table Expressions will easy for you to understand.
Here is a simple derived table example (from the Adventure Works sample DB) that returns the total quantity of items for each order in the month of December 2001:

SELECT SalesOrderID,
NumberOfItems
FROM(

) AS TotalQty

And here is the same query, done as a CTE. Notice the use of the WITH keyword:
WITH TotalQty
AS (

)

SELECT SalesOrderID,
NumberOfItems
FROM TotalQty

If the data is padded with trailing zeros, well than it might as well be a char datatype.

I’ve been working with a series of partitioned tables this past week that are just huge, well over 170 million rows total and over 100 columns. As I was writing some queries for the data I discovered that all of the varchar data was stored with trailing spaces up to the length of the column.  When the data was loaded into the tables, it wasn’t stripped of the trailing spaces.     So, if a column was defined as a varchar (30) and held a piece of data such as “Tom”, it took all 30 bytes not just 3.  Of course this made the tables very large.  I created a clean up script using the trim functions to strip the trailing spaces from the data.

My tables are much smaller now and faster too!