Aggregation based on Date

Imagine you have Sales database, we'll use AdventureWorks, and you need to return the number of sales by product in the last 30, 90, and 180 days. How would you do this? In the past I might have used temp tables, table variables, or a union in order to get the the numbers for each date range. This would not be, in my opinion, the optimal way to retrieve this data. You could just return all the data with order dates and use Excel, SSRS, or another reporting tool to pivot the data, but once again this is probably not the optimal way to return the data. So, what do I think is the best way to do this? I do this in a single select filtering on the largest date range using CASE to return the data for the smaller date ranges. Here is the code, based on AdventureWorks (I use 7/1/2004 because that is the most recent order date in my copy of AdventureWorks):

SELECT
D.ProductId,
COUNT(H.SalesOrderID) AS sales_for_180,
SUM(D.ORderQty) AS no_products_sold_180,
SUM(CASE
WHEN H.OrderDate >= DATEADD(DAY, -90, '7/31/2004' ) THEN 1
ELSE 0
END) AS sales_for_90,
SUM(CASE
WHEN H.OrderDate >= DATEADD(DAY, -90, '7/31/2004' ) THEN D.OrderQty
ELSE 0
END) AS no_products_sold_for_90,
SUM(CASE
WHEN H.OrderDate >= DATEADD(DAY, -30, '7/31/2004' ) THEN 1
ELSE 0
END) AS sales_for_30,
SUM(CASE
WHEN H.OrderDate >= DATEADD(DAY, -30, '7/31/2004' ) THEN D.OrderQty
ELSE 0
END) AS no_products_sold_for_30
FROM
Sales.SalesOrderHeader H JOIN
Sales.SalesOrderDetail D ON
H.SalesOrderID = D.SalesOrderID
WHERE
H.OrderDate < '7/31/2004' AND
H.OrderDate >= DATEADD(DAY, -180, '7/31/2004')
GROUP BY
D.ProductId

This will return the data you asked for in a single pass and will do an index seek if you have an index on OrderDate. All in all an efficient way to return the desired data. You may have learned this a long time ago, but it is something I recently picked up when answering a question on SQLServerCentral, so I know not everyone knows this. This technique works whenever you need aggregates based on some sort of partition. It could be a breakdown of products within a product category as well.

Ghost of SQL Server 2005 Database Mail Reply To - Fix

If you have been following my posts at all, you know I have had some issues with Database Mail (1, 2, 3, 4). Well the first one has been fixed, I have not tested the fix yet, by Microsoft in Cumulative Update Package 8 for Service Pack 2. The article for that is here, http://support.microsoft.com/kb/953841/en-us. Microsoft never updated my Connect post, where they said it would not be fixed unless a QFE was requested, but I'm not complaining.

Now I need to try it out on my personal SQL Server.

SSIS Webinars, tablediff, and Free Zoom Tool

I had the opportunity to attend 3 of 5 free webinars on SSIS presented by Brian Knight of Pragmatic Works. The 2 I chose not to participate in were introductory in nature, and I have some experience with SSIS. I attended Administering and Deploying SSIS Packages, Performance Tuning in SSIS, and SSIS for the DBA. I enjoyed each of the webinars and picked up some good tips. I enjoy taking any opportunities, particularly free ones, to expand my toolkit and knowledge base. I appreciate Brain and co-workers taking the time to present the webinars as I know that there had to be a lot of time and effort put into doing them. I hope it pays off for them in their business as well. I believe that the goodwill it will. Check out the Pragmatic Works web site as they offer some very good custom SSIS tasks for free! They also offer some other commercial product as well.

I actually used something I learned in one of the Webinars today, Webinar SSIS for the DBA,. I used an Execute SQL Task to populate an object variable with a list of schemas and tables and then used a For Each Loop Task to loop through the result set and use the Execute Process Task to run the tablediff utility Microsoft has included with SQL Server 2005 (I read about it in Benjamin Wright-Jones' blog earlier today). It is a command line utility that compares 1 set of tables (source and destination) and I wanted to compare all the tables in my database to my development database so I needed the loop. Tablediff compares the schemas and if they are different will return an error, "can not compare the table x to table y because the schemas are different". If the schemas are the same it will, depending on the parameters you supply, show any differences in the data and prepare a T-SQL scipt to synchronize the data. I actually was only interested in comparing the schemas and, not having read all the documentation, was hoping it would report those differences as well. Maybe in a later version of SQL Server a schema comparison tool will be included (sorry Red Gate). It was still a good exercise, and knowing about the tablediff utility will probably come in handy later.

One last thing, Brian used a nice free tool to zoom and markup during his presentation. It is called ZoomIt and is by Sysinternals (now part of Microsoft). I would recommend it to anyone who give presentations as it really helped us see what Brian was doing.

Powerless

My family and just returned to Florida from a trip to northern New England to visit churches and individuals that financially support our ministry with New Tribes Mission. I think this needs a little explanation for those who are not familiar with the workings independent, non-denominational missions. New Tribes does not pay any salaries, but each individual that becomes a member of the mission needs to visit churches and individuals of like faith in order to raise the funds necessary to live. Then after you are working for the mission you return on regular basis to those churches and individuals to report on what you have been doing. I liken it to the Interview and Performance Review process that you go through when landing and keeping a job. The first time you visit a church to present the ministry you will be doing, you are interviewing with them. When they choose to financially support you, they have "hired" you. Then you return to tell them about the work you have done, you are having your performance review. Have you really done what you told them you were going to do? Hopefully the answer is "Yes", and they will continue to financially support you and maybe give you raise.

All that to actually get to the real story. We flew from Orlando to Manchester, NH, on Thursday night and had our first speaking engagement at Calvary Baptist Church in Newport, ME, on Sunday morning. Thursday and Friday night we stayed at my brother's house in Derry, NH. Well, I setup my laptop at my brother's to use while we were there. On Saturday we packed up and headed for Newport. I went to the church Saturday night to setup and did not have a power strip so I got everything setup and tested (projector & laptop) using my laptop battery. On Sunday morning I arrived early to get the power strip I was borrowing setup so I could plug in my laptop. I went to my laptop bag to get my power cord and, OH NO, it was not there! I had left it at my brother's 3 1/2 hours and 140 miles away! I currently use a 5 year Dell Latitude D600, so the battery life is not long, so I knew I could be in trouble. I use the PowerPoint slides as my notes and expand on them extemporaneously and I had not printed out the presentation, so I had no notes. Having attended a Successful Technical Speaking class presented by Andy Warren at End to End Training this spring, I knew better, but hey, I'd never not had power before, so why be prepared? Needless to say, about halfway through the presentation the battery died and I had a blank screen. Since I had recently put together the presentation, most of it was fresh in my mind, so I was able to complete it without leaving out any major points, but this was not a situation I am interested in repeating. In the long run, this worked out, as I learned that several slides incorporated more detail than was necessary and I was able to remove them to improve the flow of the presentation. Thankfully the people were very gracious and understanding, so my faux pas was forgiven.

The moral of the story, make sure you pack everything and print out those slides!

Audit Trails and Logging

This week at SQLServerCentral there have been a couple of articles on Audit Trails and Logging (Part One and Part Two) that did a good job giving the basics of logging changes made to the data. In the discussion for part two someone asked about logging select statements and the returned results. While logging the selects is reasonable, logging the results of each query is, in my mind, a little out of whack. I understand why you may want to log the results as they may change over time. For example, Mary Smith married Joe Johnson and takes his last name, or hyphenates so she is now Mary Johnson or Mary Smith-Johnson. A query for LastName = 'Smith' the day before the change returns Mary, but the day after it does not. So in the event that data was compromised it will be more difficult to determine whose information was compromised if only the query is logged and not the data. There are several questions that come up now:

• How much space do you have for logging and is the expense associated with it justifiable?
• How far back do you keep the logged data?
  
• Do you log queries of the logged data?
• Who has access to the logged data?

These are questions that come up right off the top of my head and do not address the mechanics of the logging. There are no "Select" triggers in SQL Server so that eliminates triggers, which also eliminates being able to log ad-hoc queries using Access, SSMS, Query Analyzer, or any OLE DB or ODBC connection (Excel). One solution suggested in the discussion is using a server-side trace which works for logging the query and who ran it, but will not log the results. As I am a firm believer in not granting direct table access, I would start out by putting logging in the stored procedures used for data access. Of course, that can lead to some issues with developers who would prefer to use an ORM tool, like Linq To SQL or SubSonic, for the data access layer. Granted they are still able to use stored procedures through these tools, but one of the selling points is that you "never have to write another stored procedure". So now they not only have to data access code in the DAL, but also auditing code. If the audit needs to include the data, can I do a batch insert with the ORM tool using the dataset, list, collection I already have or do I have to iterate through the results and insert one at a time? Or do I have a separate call to the database that runs the select again, like:

Insert Into audit
Select
query,
user,
data columns....
From
table(s)
Where
criteria

This means now I am storing the user and query with each row returned and I am doubling the reads on my database.

I certainly don't have the solution to this problem. I know that for Inserts/Updates/Deletes, I like triggers because they put all the code in one place and will fire on all changes unless someone explicitly disables triggers before making a change and if this happens you have other problems.

I'd be interested in hearing what other people think.

June oPASS Meeting

I attended the June oPASS meeting and post meeting gathering at Bennigan's this evening. The speakers were Ginny Caughey and Steve Lasker, Program Manager for SQL Server Compact Edition, and, guess what, they spoke on SQL Server Compact Edition. It was a very informative meeting as I have had no experience or exposure to SQL Server CE and the presentation certainly gave me something to think about. I actually have a personal project that I am currently using SQL Server Express with, but I am now considering moving to SQL Server CE. Why? SQL Server CE is lightweight, portable, and appears to meet the application requirements. It would also give me some experience with SQL Server CE which I can use to evaluate it for use in future projects. The database can be packaged with the application and if I want to move it to another PC I can simply copy and paste. After the session 7 of us, including the speakers, adjourned to Bennigan's for some more discussion. I always find it enlightening to listen to Andy Warren voice his opinions. He is very much an idea guy, and has usually thought through the subject. We discussed the direction of SQL Server CE, Linq to SQL, and SQL Server Data Services. It's nice to hear about development directions from someone at Microsoft and to see the interest in "user" opinions and ideas.

Once again I found it to be a productive use of my time, even though the topic was not one that seemed to apply to my interests and work. As always, I encourage you to take the time to find and attend user groups in your area.

ANSI_PADDING and Variable Length Character Columns in SQL Server

I have been involved in a very interesting thread on SQLServerCentral.com about trailing spaces in strings. It has changed the way I will be looking as strings going forward. I had always assumed that when a character column was defined as variable length (varchar, nvarchar) any trailing spaces inserted into the column would be trimmed. Well this WAS how SQL Server handled it because SQL Server defaulted the ANSI PADDING setting to OFF. Now SQL Server in SSMS (SQL Server Management Studio) the connection settings set ANSI PADDING to ON. This means that any table created using SSMS will use the ANSI PADDING ON setting, regardless of the default database setting. ANSI PADDING ON means that 'a ' will store the 'a' and the space while ANSI PADDING OFF only stores the 'a'. This becomes an issue when upgrading from an earlier version of SQL Server or, as in the thread, you are not aware of this change and are not coding appropriately for it.

Why is this a problem? Well. Here is a table showing how SQL Server acts with the 2 ANSI_PADDING options (courtesy of Matt Miller in the thread mentioned above and some testing):

SQL Function	Result (ANSI Padding ON)	Result (ANSI Padding OFF)
Primary Key	Trim it. Duplicate key error	Trim it
Unique Index	Trim it. Duplicate key error	Trim it
Comparison ("=", "!=")	Trim it. 'a' = 'a '	Trim it
Comparison ("like using _")	Don't trim it	Trim it
Concatenation	Don't trim it. 'a ' + 'a' = 'a a'	Trim it 'a ' + 'a' = 'aa'
Storage	Don't trim it	Trim it
Group By	Trim it	Trim it
Order By	Trim it	Trim it

The RED rows show where there are differences in behavior. Here is what Microsoft has to say from SQL Server 2005 BOL:

Important:

In a future version of SQL Server ANSI_PADDING will always be ON and any applications that explicitly set the option to OFF will produce an error. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

Note:

We recommend that ANSI_PADDING always be set to ON

Here is some code that proves the behavior:

SET Ansi_Padding OFF

CREATE TABLE test.categories
(
CategoryID INT PRIMARY KEY CLUSTERED IDENTITY(1,1),
CategoryName VARCHAR(15),
Name2 VARCHAR(15),
NCategoryName NVARCHAR(15),
NName2 NVARCHAR(15)
)
--Create Unique Index UX_varchar_test On test.categories(CategoryName)
--Create Unique Index UX_nvarchar_test On test.categories(NCategoryName)

INSERT INTO test.categories
(
CategoryName,
Name2,
NCategoryName,
NName2
)
SELECT 'a', 'b', 'a', 'b' UNION ALL
SELECT 'a ', 'b','a ', 'b' UNION ALL
SELECT 'a ', 'b', 'a ', 'b' UNION ALL
SELECT 'a ', 'b', 'a ', 'b' UNION ALL
SELECT 'a ', 'b','a ', 'b' UNION ALL
SELECT 'a ', 'b','a ', 'b' UNION ALL
SELECT 'a ', 'b','a ', 'b'

-- length, storage, concatenation
SELECT
CASE
WHEN SESSIONPROPERTY('ANSI_PADDING') = 0 THEN 'Off'
ELSE 'ON'
END AS ansi_padding_setting,
CategoryID,
CategoryName,
CategoryName + Name2 AS concat_names,
LEN(CategoryName) AS len_name,
DATALENGTH(CategoryName) AS Datalength_Name,
NCategoryName,
NCategoryName + NName2 AS concat_unicode_names,
LEN(NCategoryName) AS len__unicode_name,
DATALENGTH(NCategoryName) AS Datalength_Unicode_Name
FROM
test.categories

-- varchar group by
SELECT
CASE
WHEN SESSIONPROPERTY('ANSI_PADDING') = 0 THEN 'Off'
ELSE 'ON'
END AS ansi_padding_setting,
'varchar' AS data_type,
'group by' AS test_function,
COUNT(categoryid) AS num_recs,
categoryName
FROM
test.categories
GROUP BY
categoryName

-- nvarchar group by
SELECT
CASE
WHEN SESSIONPROPERTY('ANSI_PADDING') = 0 THEN 'Off'
ELSE 'ON'
END AS ansi_padding_setting,
'nvarchar' AS data_type,
'group by' AS test_function,
COUNT(categoryid) AS num_recs,
NcategoryName
FROM
test.categories
GROUP BY
NcategoryName

-- varchar order by
SELECT
CASE
WHEN SESSIONPROPERTY('ANSI_PADDING') = 0 THEN 'Off'
ELSE 'ON'
END AS ansi_padding_setting,
'varchar' AS data_type,
'order by' AS test_function,
categoryid,
categoryName
FROM
test.categories
ORDER BY
categoryName DESC

-- nvarchar order by
SELECT
CASE
WHEN SESSIONPROPERTY('ANSI_PADDING') = 0 THEN 'Off'
ELSE 'ON'
END AS ansi_padding_setting,
'nvarchar' AS data_type,
'order by' AS test_function,
categoryid,
ncategoryName
FROM
test.categories
ORDER BY
ncategoryName DESC

-- varchar =
SELECT
CASE
WHEN SESSIONPROPERTY('ANSI_PADDING') = 0 THEN 'Off'
ELSE 'ON'
END AS ansi_padding_setting,
'varchar' AS data_type,
'=' AS test_function,
categoryid,
categoryName
FROM
test.categories
WHERE
categoryName = 'a'

-- nvarchar =
SELECT
CASE
WHEN SESSIONPROPERTY('ANSI_PADDING') = 0 THEN 'Off'
ELSE 'ON'
END AS ansi_padding_setting,
'nvarchar' AS data_type,
'=' AS test_function,
categoryid,
ncategoryName
FROM
test.categories
WHERE
NcategoryName = 'a'

-- varchar like %
SELECT
CASE
WHEN SESSIONPROPERTY('ANSI_PADDING') = 0 THEN 'Off'
ELSE 'ON'
END AS ansi_padding_setting,
'varchar' AS data_type,
'Like %' AS test_function,
categoryid,
categoryName
FROM
test.categories
WHERE
categoryName LIKE 'a%'

-- nvarchar like %
SELECT
CASE
WHEN SESSIONPROPERTY('ANSI_PADDING') = 0 THEN 'Off'
ELSE 'ON'
END AS ansi_padding_setting,
'nvarchar' AS data_type,
'Like %' AS test_function,
categoryid,
ncategoryName
FROM
test.categories
WHERE
ncategoryName LIKE 'a%'

-- varchar like _
SELECT
CASE
WHEN SESSIONPROPEERTY('ANSI_PADDING') = 0 THEN 'Off'
ELSE 'ON'
END AS ansi_padding_setting,
'varchar' AS data_type,
'Like _' AS test_function,
categoryid,
categoryName
FROM
test.categories
WHERE
categoryName LIKE 'a_'

-- nvarchar like _
SELECT
CASE
WHEN SESSIONPROPERTY('ANSI_PADDING') = 0 THEN 'Off'
ELSE 'ON'
END AS ansi_padding_setting,
'nvarchar' AS data_type,
'Like _' AS test_function,
categoryid,
ncategoryName
FROM
test.categories
WHERE
ncategoryName LIKE 'a_'
GO

SET Ansi_Padding ON

CREATE TABLE test.categories2
(
CategoryID INT PRIMARY KEY CLUSTERED IDENTITY(1,1),
CategoryName VARCHAR(15),
Name2 VARCHAR(15),
NCategoryName NVARCHAR(15),
NName2 NVARCHAR(15)
)
--Create Unique Index UX_varchar_test On test.categories2(CategoryName)
--Create Unique Index UX_nvarchar_test On test.categories2(NCategoryName)

INSERT INTO test.categories2
(
CategoryName,
Name2,
NCategoryName,
NName2
)
SELECT 'a', 'b', 'a', 'b' UNION ALL
SELECT 'a ', 'b','a ', 'b' UNION ALL
SELECT 'a ', 'b', 'a ', 'b' UNION ALL
SELECT 'a ', 'b', 'a ', 'b' UNION ALL
SELECT 'a ', 'b','a ', 'b' UNION ALL
SELECT 'a ', 'b','a ', 'b' UNION ALL
SELECT 'a ', 'b','a ', 'b'

-- length, storage, concatenation
SELECT
CASE
WHEN SESSIONPROPERTY('ANSI_PADDING') = 0 THEN 'Off'
ELSE 'ON'
END AS ansi_padding_setting,
CategoryID,
CategoryName,
CategoryName + Name2 AS concat_names,
LEN(CategoryName) AS len_name,
DATALENGTH(CategoryName) AS Datalength_Name,
NCategoryName,
NCategoryName + NName2 AS concat_unicode_names,
LEN(NCategoryName) AS len__unicode_name,
DATALENGTH(NCategoryName) AS Datalength_Unicode_Name
FROM
test.categories2

-- varchar group by
SELECT
CASE
WHEN SESSIONPROPERTY('ANSI_PADDING') = 0 THEN 'Off'
ELSE 'ON'
END AS ansi_padding_setting,
'varchar' AS data_type,
'group by' AS test_function,
COUNT(categoryid) AS num_recs,
categoryName
FROM
test.categories2
GROUP BY
categoryName

-- nvarchar group by
SELECT
CASE
WHEN SESSIONPROPERTY('ANSI_PADDING') = 0 THEN 'Off'
ELSE 'ON'
END AS ansi_padding_setting,
'nvarchar' AS data_type,
'group by' AS test_function,
COUNT(categoryid) AS num_recs,
NcategoryName
FROM
test.categories2
GROUP BY
NcategoryName

-- varchar order by
SELECT
CASE
WHEN SESSIONPROPERTY('ANSI_PADDING') = 0 THEN 'Off'
ELSE 'ON'
END AS ansi_padding_setting,
'varchar' AS data_type,
'order by' AS test_function,
categoryid,
categoryName
FROM
test.categories2
ORDER BY
categoryName DESC

-- nvarchar order by
SELECT
CASE
WHEN SESSIONPROPERTY('ANSI_PADDING') = 0 THEN 'Off'
ELSE 'ON'
END AS ansi_padding_setting,
'nvarchar' AS data_type,
'order by' AS test_function,
categoryid,
ncategoryName
FROM
test.categories2
ORDER BY
ncategoryName DESC

-- varchar =
SELECT
CASE
WHEN SESSIONPROPERTY('ANSI_PADDING') = 0 THEN 'Off'
ELSE 'ON'
END AS ansi_padding_setting,
'varchar' AS data_type,
'=' AS test_function,
categoryid,
categoryName
FROM
test.categories2
WHERE
categoryName = 'a'

-- nvarchar =
SELECT
CASE
WHEN SESSIONPROPERTY('ANSI_PADDING') = 0 THEN 'Off'
ELSE 'ON'
END AS ansi_padding_setting,
'nvarchar' AS data_type,
'=' AS test_function,
categoryid,
ncategoryName
FROM
test.categories2
WHERE
NcategoryName = 'a'

-- varchar like %
SELECT
CASE
WHEN SESSIONPROPERTY('ANSI_PADDING') = 0 THEN 'Off'
ELSE 'ON'
END AS ansi_padding_setting,
'varchar' AS data_type,
'Like %' AS test_function,
categoryid,
categoryName
FROM
test.categories2
WHERE
categoryName LIKE 'a%'

-- nvarchar like %
SELECT
CASE
WHEN SESSIONPROPERTY('ANSI_PADDING') = 0 THEN 'Off'
ELSE 'ON'
END AS ansi_padding_setting,
'nvarchar' AS data_type,
'Like %' AS test_function,
categoryid,
ncategoryName
FROM
test.categories2
WHERE
ncategoryName LIKE 'a%'

-- varchar like _
SELECT
CASE
WHEN SESSIONPROPERTY('ANSI_PADDING') = 0 THEN 'Off'
ELSE 'ON'
END AS ansi_padding_setting,
'varchar' AS data_type,
'Like _' AS test_function,
categoryid,
categoryName
FROM
test.categories2
WHERE
categoryName LIKE 'a_'

-- nvarchar like _
SELECT
CASE
WHEN SESSIONPROPERTY('ANSI_PADDING') = 0 THEN 'Off'
ELSE 'ON'
END AS ansi_padding_setting,
'nvarchar' AS data_type,
'Like _' AS test_function,
categoryid,
ncategoryName
FROM
test.categories2
WHERE
ncategoryName LIKE 'a_'


DROP TABLE test.categories2
DROP TABLE test.categories


The key, as always, is to understand the system you are working with and design and code against it appropriately. As noted by the Original Poster in the SSC thread, "Trim your strings before inserting".