CODE Magazine - Article: The Baker’s Dozen: 13 Miscellaneous SQL Server Tips

Reader rating:
Article source: CoDe (2012 Jan/Feb)

Article Pages: 1 2 3 - Next >

The Baker’s Dozen: 13 Miscellaneous SQL Server Tips

To use a music analogy, many installments of “The Baker’s Dozen” have been like “concept albums,” where most or all of the tips work towards a big picture. Then there are times where I present a series of random tips that are largely standalone and don’t form a pattern. In this article, I’m going to present 13 random tips for SQL Server and T-SQL programming.

What’s on the Menu?

Getting right to the point, here are the 13 items on the menu for this article:

Baker’s Dozen Spotlight: A T-SQL tip for summarizing data by week, and calculating a 52-week moving average
Using SQL Server table-valued functions as a means of implementing security access by business entities
SQL Server covering indexes for performance
SQL Server SARGs (Searchable Arguments)
T-SQL SUM OVER capability to implement a % of Parent calculation
Programmatically disabling SQL Server constraints and triggers
Correlated subqueries and derived table subqueries and their execution plans
A little brain teaser on the UNION statement
Back to basics - determining when a subquery is necessary
The tinyint data type and when to use it
DISTINCT vs GROUP BY
Creating a result set of integers for a picklist
INSTEAD OF triggers to prevent deletions

The Demo Database for the Examples

With just a few exceptions, the examples use the AdventureWorks2008R2 database. You can find AdventureWorks2008R2 on the CodePlex site. If you’re still using SQL Server 2008 and not 2008R2, the examples will still work - you’ll just need to change any 2008R2 references to 2008.

Tip 1: Baker’s Dozen Spotlight: Summarizing Data by Week and Calculating a 52-week Moving Average

Scenario: I want to take daily sales information from the daily Purchase Order table in the AdventureWorks database and summarize sales by week ending date. For each week, I also want to calculate the average weekly sales going back over the last 52 weeks.

Listings 1 - 3, and Figure 1 demonstrate a result set that summarizes by week-ending date (using a UDF), and also generates a moving average by querying each week against the last 52 weeks - often used for an analytic chart that shows a trend of weeks (Figure 2). The highlights of the T-SQL code are as follows:

Click for a larger version of this image.

Figure 1: Results of Moving Average.

Figure 2: Chart showing data using Moving Average with AdventureWorks demo.

First, in order to summarize sales by week, I need a function that will convert any day to a Saturday date, or end of week date (Listing 1). Many developers have probably seen SQL functions that convert dates to Saturday dates - but it’s worth mentioning that when using a date data type, I can’t do the following (which developers have done for years with a datetime data type):

SET @ReturnDate =
@InputDate +
(@@DateFirst -
datepart(weekday,@InputDate

That’s because Microsoft restricted the date data type to only use the DateAdd function: the “+” operator is not valid for the date datatype.

SET @ReturnDate =
  DateAdd(day,
         (@@DateFirst -
            datepart(weekday,@InputDate)),
          @InputDate)

Second, remember that the date range for the result set is the Fiscal Year 2008. Also note that the result set in Figure 1 and the chart in Figure 2 both show all weeks, regardless of whether any purchase orders existed for the week. Many reports/charts need to display all weeks, regardless of whether transactions occurred. Analytically, weeks without data might be as significant as weeks with data. Therefore, a routine (function) that produces all the possible week ending dates will be helpful, especially if many reports need it.

So Listing 2 contains a second function, a table-valued function called CreateDateRange, to create a list of week ending dates for the date range we need. The function uses a recursive query to build a range of dates.

;WITH DateCTE(WeekEndDate) AS
   SELECT @StartDate AS WeekEndDate
      UNION ALL
   SELECT DateAdd(d,7,WeekEndDate)
          AS WeekEndDate
        FROM DateCTE
         WHERE WeekEndDate < @EndDate )

Finally, let’s talk about a calculation involving a 52-week moving average. Essentially, for any one week, we want to get the average sales over the last 52 weeks. For instance, in the tooltip for the plotted point in Figure 2, the week of 4/19/2008 has a 52-week average of roughly $791,835. This means that for the date range of 4/21/2007 to 4/19/2008, the average weekly sales was roughly $791,835. (Subsequently, the 52-week average for the week ending 4/26/2008 would be the average weekly sales from 4/28/2007 to 4/26/2008. This is why we call it a “moving average”, because the range of 52 weeks “moves” with each subsequent week.

Tip 2: Using Table-Valued Functions for Role-based Security

Scenario: I have a SQL query that summarizes purchase orders by Vendor, for a specific ship method. I want our shipping managers to be able to run the query, without actually having SELECT access rights to the table, and so that they can only see summarized vendor orders for their specific ship method.

I frequently go back and forth between OLAP databases and relational databases. I bring this up only because OLAP databases provide dimensional security roles, which make addressing this scenario very easy. However, SQL Server relational databases don’t have built-in row-based security, so we need to implement something.

Because a stated requirement was no SELECT rights to the table, you might be thinking of a stored procedures or a view. Initially this might seem fine, but there are some issues. First, a SQL Server View cannot receive parameters, and we’d need one for the ship method. A stored procedure can certainly receive parameters - and could be a solution - but I’ll add an additional requirement - that database tasks run by a ship manager might want to use the results of the query as an intermediate result set, and subsequently join to other data. Result sets from stored procedures don’t work perfectly in this scenario - you can’t directly join the results of a stored procedure without introducing another table or temp table. Ironically, a view might be better - but we already know that views can’t receive parameters. So what can we do?

A feature in SQL Server 2005 that, to this day, doesn’t get enough attention is table-valued functions. I showed a TVF in Tip #1 to produce a date range - as it turns out, we can use TVFs in this scenario as well.

Listing 4 shows how table-valued functions can be used in conjunction with views to consolidate queries for role-based security. The code walks through creating a TVF with a parameter and then a set of views - and then sets rights for two logins to the views. This helps to overcome a limitation of SQL views - that you can’t pass parameters to them.

By: Kevin S Goff

Kevin S. Goff, a Microsoft MVP award recipient for 2007, is the founder and principal consultant of Common Ground Solutions, a consulting group that provides custom Web and desktop software solutions in .NET, VFP, SQL Server, and Crystal Reports. Kevin is the author of Pro VS 2005 Reporting using SQL Server and Crystal Reports, published by Apress. Kevin has been building software applications since 1988. He has received several awards from the U.S. Department of Agriculture for systems automation. He has also received special citations from Fortune 500 Companies for solutions that yielded six-figure returns on investment. He has worked in such industries as insurance, accounting, public health, real estate, publishing, advertising, manufacturing, finance, consumer packaged goods, and trade promotion. In addition, Kevin provides many forms of custom training. Contact Kevin at kgoff@commongroundsolutions.net

kgoff@commongroundsolutions.net

Listing 1: Scalar function to get Week Ending Date

use AdventureWorks2008R2
go

CREATE FUNCTION [dbo].[WeekEndingDate]
( @InputDate date  )
RETURNS Date
AS
BEGIN
   DECLARE @ReturnDate DATE
   SET @ReturnDate =
       dateadd(day,
              (@@DateFirst - datepart(weekday,@InputDate)),
               @InputDate)
   RETURN @ReturnDate
END
GO

Listing 2: TVF to create a range of Week Ending dates

use AdventureWorks2008R2
go

CREATE FUNCTION [dbo].[CreateDateRange]
(@StartDate Date, @EndDate Date)
RETURNS
@WeekList TABLE (WeekEndDate Date)
AS
BEGIN

;WITH DateCTE(WeekEndDate) AS
   SELECT @StartDate AS WeekEndDate
      UNION ALL
        SELECT DateAdd(d,7,WeekEndDate) AS WeekEndDate
          FROM DateCTE WHERE WeekEndDate < @EndDate )

   INSERT INTO @WeekList
     SELECT * FROM DateCTE
         OPTION (MAXRECURSION 1000)

RETURN
END
GO

Listing 3: Query to use the TVF and scalar UDF

use AdventureWorks2008R2
go

DECLARE @StartDate DATE = dbo.WeekEndingDate('7-1-2007')
DECLARE @EndDate DATE = dbo.WeekEndingDate('6-30-2008')

DECLARE @StartDateOneYearAgo DATE =
       dbo.WeekEndingDate(DATEADD(YEAR,-1,@StartDate))

;WITH TempWeeklyOrderCTE AS
  (SELECT dbo.WeekEndingDate(OrderDate) as WeekEndDate,
           SUM(TotalDue) AS TotalDue
               FROM Purchasing.PurchaseOrderHeader
             WHERE OrderDate BETWEEN
                   @StartDateOneYearAgo AND @EndDate
   GROUP BY dbo.WeekEndingDate(OrderDate) )


SELECT DateListCTE.WeekEndDate,
       TempWeeklyOrderCTE.TotalDue,
       AVG(TempCTEHistory.TotalDue) AS WeeklyMovingAvg
FROM dbo.CreateDateRange(@StartDate, @EndDate) DateListCTE
  LEFT JOIN TempWeeklyOrderCTE
      ON DateListCTE.WeekEndDate =
                  TempWeeklyOrderCTE.WeekEndDate

  LEFT JOIN TempWeeklyOrderCTE AS TempCTEHistory
      ON TempCTEHistory.WeekEndDate BETWEEN
              DATEADD(WEEK,-53,DateListCTE.WeekEndDate) AND
                      DateListCTE.WeekEndDate
       WHERE  DateListCTE.WeekEndDate BETWEEN
              @StartDate AND @EndDate
GROUP BY DateListCTE.WeekEndDate, TempWeeklyOrderCTE.TotalDue
ORDER BY DateListCte.WeekEndDate

Listing 4: Using Table-Valued Functions to facilitate Views for security

use AdventureWorks2008R2
go
CREATE FUNCTION dbo.GetSummarizedPurchaseOrdersForShipMethod
( @ShipMethodID int)
returns table      --  an in-memory table
as
   return
         select Vendor.Name, SUM(TotalDue) as TotDollars
                 from Purchasing.PurchaseOrderHeader POH
                   join Purchasing.Vendor on POH.VendorID =
                                 Vendor.BusinessEntityID
                          where ShipMethodID = @ShipMethodID
                     group by Vendor.Name
go

CREATE VIEW vwSummarizedOrdersShipper_XRQ as
     select * from  dbo.GetSummarizedPurchaseOrdersForShipMethod(1)

go

CREATE VIEW vwSummarizedOrdersShipper_ZY as
     select * from  dbo.GetSummarizedPurchaseOrdersForShipMethod(2)

go

CREATE LOGIN ShipLogin_XRQ WITH PASSWORD = 'J345#$)thb';
CREATE LOGIN ShipLogin_ZY WITH PASSWORD = 'Uor80$23b';

CREATE USER ShipUser_XRQ FOR LOGIN Shiplogin_XRQ;
CREATE USER ShipUser_ZY FOR LOGIN Shiplogin_ZY;

grant select on vwSummarizedOrdersShipper_XRQ to ShipUser_XRQ
grant select on vwSummarizedOrdersShipper_ZY to ShipUser_ZY

execute as login = 'ShipLogin_XRQ'

select * from vwSummarizedOrdersShipper_XRQ
-- User can see the summarized data for ship method 1

select * from vwSummarizedOrdersShipper_ZY
-- User cannot see the summarized data for ship method 2

select * from Purchasing.PurchaseOrderHeader
-- User cannot see Purchasing.PurchaseOrderHeader

select SUSER_NAME()

  select * from  dbo.GetSummarizedPurchaseOrdersForShipMethod(2)
-- Ship Login1 cannot run that function

  select * from  dbo.GetSummarizedPurchaseOrdersForShipMethod(1)
-- Ship Login1 cannot run that function,
-- regardless of the parameter....because shiplogin1 does not have
-- EXECUTE rights to the function

revert

Article Pages: 1 2 3 - Next Page: 'Page 2' >>

How would you rate the quality of this article?

	1	2	3	4	5
Poor						Outstanding

Tell us why you rated the content this way. (optional)

Average rating:
5 out of 5

3 people have rated this article.