Search results matching tags 'sql' and 'development'

Please Vote: Windowing Enhancements in SQL Server

Mike C — Fri, 20 Nov 2009 03:45:00 GMT

Back in January 2007, SQL guru Itzik Ben-Gan posted a series of MS Connect enhancement requests concerning windowing function enhancements. Those who have used the ROW_NUMBER(), RANK(), DENSE_RANK(), and NTILE() functions on SQL 2005 and 2008, you already know how useful they are. They simplify code and can improve performance considerably over the alternatives, which usually include self-joins, temp tables and/or cursors in various combinations.

Well, the windowing functionality that you've seen in SQL 2005 and 2008 is just the tip of the iceberg. The ISO SQL standard actually defines several additional options for these functions that SQL Server doesn't yet support. These additional options allow you to do some pretty amazing calculations. The ROWS and RANGE window subclauses that the standard defines allows you to perform "sliding window" calculations; the ORDER BY clause for aggregate functions which simplifies complex running sum (and other) calculations.

As a SQL developer or DBA, these enhancements will simplify your life. But don't take my word for it - read Itzik's white paper at http://www.insidetsql.com/OVER_Clause_and_Ordered_Calculations.doc. Then let Microsoft know you want these enhancements in SQL Server by voting on Itzik's enhancement requests at the following links:

Progressive Ordered Calculations: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=254397

FIRST_VALUE, LAST_VALUE functions: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=254395

DISTINCT clause for aggregates: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=254393

ROWS and RANGE window subclauses: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=254392

Vector expressions: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=254391

TOP OVER: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=254390

LAG and LEAD functions: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=254388

ORDER BY for aggregates: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=254387

Read Itzik's article, vote on the Connect items, and spread the word!

"SQL Server MVP Deep Dives" Book Available for Early Access/Pre-Orders

Mike C — Thu, 17 Sep 2009 00:21:00 GMT

The MVP authored book, SQL Server MVP Deep Dives is now available for early access and pre-orders at http://manning.com/nielsen/. The book is divided into 5 sections that cover everything from building a proper relational database to developing BI solutions:

1. Database Design and Architecture
2. Database Development
3. Database Administration
4. Performance Tuning and Optimization
5. BI Development

This book covers a lot of ground with a lot of expert tour guides. Weighing in at 850 pages, it was written, tech-reviewed and edited by dozens of Microsoft MVPs with intimate knowledge of SQL Server. The contributor list includes some of the biggest names in SQL Server: Paul Nielsen, Adam Machanic, Kalen Delaney, Itzik Ben-Gan, Aaron Bertrand, Hugo Kornelis, John Paul Cook, and several others. For me getting a copy of this book will be like getting the opportunity to pick the brains of some of the most knowledgable folks in the industry on a wide variety of topics.

This is already one of my favorite books that I've had the chance to contribute to (I wrote a couple of chapters discussing on SQL Server XML in general and SQL Server XQuery specifically) because all of the royalties go to a charity that helps children who are war victims!

So go get the book, learn what the pros know, and do your good deed for the day -- all at the same time!

You can get more info on SQL Server MVP Deep Dives at Manning Publications: http://manning.com/nielsen/ and at Amazon http://www.amazon.com/SQL-Server-MVP-Deep-Dives/dp/1935182048/.

"Cloning" Symmetric Keys

Mike C — Thu, 18 Jun 2009 01:40:00 GMT

It's well-known by now that SQL Server 2005 and 2008 include new encryption-related statements that allow you to create and administer encryption keys. You can use CREATE CERTIFICATE to create or import a certificate or DROP ASYMMETRIC KEY to remove an asymmetric key from the database, for instance. One of the interesting ommissions from the T-SQL encryption statements is the statements necessary to backup and restore a symmetric key. Why would you want to do this? I can think of a couple of reasons off the top of my head:

You need to backup symmetric keys (and all other encryption keys, in fact) as part of an overall disaster recovery (DR) program. If a server needs to be rebuilt you obviously need a way to restore all encryption keys.
You need to implement the same symmetric keys on multiple servers. There could be a couple of reasons for this -- you might encrypt data on one server and decrypt it on another, or you might be load-balancing across a server farm and need identical encryption keys on multiple servers simultaneously.

It seems like a bit of an oversight to not include BACKUP and RESTORE SYMMETRIC KEY options in T-SQL, but in practice you can effectively achieve the same end results with the standard CREATE SYMMETRIC KEY statement. Basically the CREATE SYMMETRIC KEY statement gives you an option to "clone" the exact same symmetric key on any SQL Server 2005 or 2008 instance, anywhere, at any time. To create a cloneable symmetric key you need to specify two special CREATE SYMMETRIC KEY options:

The IDENTITY_VALUE option, which SQL Server uses to generate a GUID (uniqueidentifier) for the key
The KEY_SOURCE option, which SQL Server uses as key material to generate the actual key

As long as you specify the same values for the IDENTITY_VALUE and KEY_SOURCE options (and the same ALGORITHM), your symmetric key will be exactly the same no matter where, when, or how many times you create it. To be honest, if I were creating a list, always use IDENTITY_VALUE and KEY_SOURCE would be listed in the top 10 list of SQL Server encryption best practices.

Here's a quick sample demonstrating the CREATE SYMMETRIC KEY statement with IDENTITY_VALUE and KEY_SOURCE specified:

CREATE SYMMETRIC KEY test_aes128_key
WITH KEY_SOURCE = 'I am the very model of a modern major general',
IDENTITY_VALUE = 'E pluribus unum',
ALGORITHM = AES_128
ENCRYPTION BY PASSWORD = 'p@$$w0rd';

This CREATE SYMMETRIC KEY statement will create the same symmetric encryption key on any SQL Server 2005 or 2008 instance on which you run it. This brings up another point, about security. If the same KEY_SOURCE and IDENTITY_VALUE options can create the exact same encryption key on any of your servers, they will create the exact same encryption key on any of my servers, or any server owned by any hacker anywhere in the world. So once you've run your CREATE SYMMETRIC KEY statement, the IDENTITY_VALUE and KEY_SOURCE need to be handled like any other secure information. Don't leave them lying around where just anyone can access them. Store them with your certificates, key backups, and other confidential materials in a secure off-site location.

So what happens when you don't specify the IDENTITY_VALUE and KEY_SOURCE options? Well, basically SQL Server generates an unpredictable GUID to identify the symmetric key and the encryption key source material is randomly generated. Basically you'll never regenerate the exact same key again. Ever. There could be situations where this would be handy. The concept of "session keys" comes to mind. A session key is basically a "temporary" key that's only required to encrypt data for a user during a single session. Since it only exists for the life of the session, a totally randomly-generated key is just fine.

For my tastes, it would make more sense to require IDENTITY_VALUE and KEY_SOURCE options by default. If you didn't want to specify both of these options there should be another option/indicator specifically to say that you want these options generated randomly. At any rate, it's a good idea to get into the habit of treating these options as if they are mandatory unless they have a very specific special-purpose requirement (e.g., "session keys").

Presenting in Louisville; Meeting in Cincinnati

Mike C — Mon, 01 Jun 2009 23:15:00 GMT

I'll be presenting "New SQL Server 2008 Features" in downtown Louisville, KY on June 22, in conjunction with the Louisville SQL Server Users Group thanks to group leader Malathi Mahadevan. We'll be giving away some SQL 2008 books and other goodies. I'll post further details (exact time and location) shortly.

Update: The address is for the Louisville meeting is:

Louisville Free Public Library
301 York Street
Louisville, KY 40202

We'll be meeting from 12 noon to 1 PM.

June 23rd I'll be stopping by the Cincinnati .NET User Group (for the UG meeting and a Geek Dinner -- hopefully at Skyline Chili! ;-) ) thanks to Mike Wood, director of the Cincy .NET Users Group. We'll be having an informal discussion of SQL Server and .NET technology and features after the main meeting, having some good food, and giving away a few more books! Visit http://cinnug.org for more info.

Practical SQL Server XML: Part 2

Mike C — Wed, 04 Mar 2009 02:28:00 GMT

In response to a challenge from Pinal Dave, a fellow MVP and a winner of an award for his blog at the MVP Summit 2009, I am writing a couple of articles to demonstrate practical uses for SQL Server’s XML capabilities. (Dave’s great blog is at http://blog.sqlauthority.com/).

Dave posted the first post in this series over at http://blog.sqlauthority.com/2009/03/17/sql-server-practical-sql-server-xml-part-one-query-plan-cache-and-cost-of-operations-in-the-cache/, describing how to shred the XML query plans in the SQL Server query plan cache. In this post I’ll talk about sending multiple parameters, and even parameter sets, to SQL Server via XML. SQL Server MVP Jacob Sebastian is finishing up the series with a post on his blog at http://blog.beyondrelational.com/2009/03/practical-sql-server-xml-part-three.html.

One of the most common requests in the SQL Server newsgroups is “how do I send a delimited list of values to my stored procedures?” There are many creative answers, including SQL MVP Erland Sommarskog’s popular article “Arrays and Lists in SQL Server” (http://www.sommarskog.se/arrays-in-sql-2005.html). Erland touches on the method of XML shredding that I’ll cover here.

For this example I’ll use the AdventureWorks 2008 database. This sample database is available for download from CodePlex (http://sqlserversamples.codeplex.com/) for SQL Server 2008. For this example I’ll first create a procedure that inserts a single record that must be put in two separate tables:

CREATE PROCEDURE Person.AddPerson

@PersonType nchar(2) = NULL,

@NameStyle dbo.NameStyle = NULL,

@Title nvarchar(8) = NULL,

@FirstName dbo.Name = NULL,

@MiddleName dbo.Name = NULL,

@LastName dbo.Name = NULL,

@Suffix nvarchar(10) = NULL,

@EmailPromotion int = NULL,

@EmailAddress nvarchar(50) = NULL

BEGIN

-- Get a new BusinessEntityID

DECLARE @BusinessEntityID int;

INSERT INTO Person.BusinessEntity(rowguid)

VALUES (NEWID());

SET @BusinessEntityID = SCOPE_IDENTITY();

-- Insert Person

INSERT INTO Person.Person

(

BusinessEntityID,

PersonType,

NameStyle,

Title,

FirstName,

MiddleName,

LastName,

Suffix,

EmailPromotion

)

VALUES

(

@BusinessEntityID,

@PersonType,

@NameStyle,

@Title,

@FirstName,

@MiddleName,

@LastName,

@Suffix,

@EmailPromotion

);

-- Insert Email Address

INSERT INTO Person.EmailAddress

(

BusinessEntityID,

EmailAddress

)

VALUES

(

@BusinessEntityID,

@EmailAddress

);

END

The procedure simply accepts several parameters that reflect a single Person record. The first step is to insert a row into the Person.BusinessEntity table to get a business entity ID. Then the remaining parameter values are inserted into the Person.Person and Person.EmailAddress tables. Simple enough. But what if you want to insert 2 Person records? You can round-trip to the server twice with two calls to this procedure, or you can double up the number of parameters and add the extra code in the procedure to deal with it.

But then again, what if you want to add 100 Person records? How about a thousand or a million? That’s a lot of round trips to the server or a lot of extra parameters to add (SQL Server actually maxes out at around 2,100 parameters). If you have SQL Server 2008 and you’re using a .NET client you can use table-valued parameters. But what if you’re not using a .NET client or SQL 2005 is your target database? In that case you can use XML to accomplish the same thing.

Using XML to add data to a single table is normally a pretty straightforward shredding operation, but we’re actually touching three different tables in this case. And one of the tables generates an identity value that the other two tables need. This means the code is not so simple as a straight single-table insert, especially if you decide not to use cursors. Here’s my XML version of adding a Person:

CREATE PROCEDURE Person.AddPersonXML

@People xml = NULL

BEGIN

-- Generate and store all new business entity IDs

DECLARE @TemporaryIDs TABLE

(

BusinessEntityID int NOT NULL

);

-- Number the XML nodes and generate business entity IDs

WITH NodeNumbers

(

SELECT

ROW_NUMBER() OVER (ORDER BY C.value('PersonType', 'nchar(2)')) AS NodeNumber

FROM @People.nodes('//Person') T(C)

)

INSERT INTO Person.BusinessEntity

(

rowguid

)

OUTPUT

inserted.BusinessEntityID

INTO @TemporaryIDs

(

BusinessEntityID

)

SELECT NEWID()

FROM NodeNumbers nn;

WITH PersonNodes

(

SELECT

ROW_NUMBER() OVER (ORDER BY C.value('PersonType[1]', 'nchar(2)')) AS JoinID,

C.query('.') AS PersonData

FROM @People.nodes('//Person') T(C)

BusinessEntityIDs

(

SELECT

ROW_NUMBER() OVER(ORDER BY BusinessEntityID) AS JoinID,

BusinessEntityID

FROM @TemporaryIDs

GetPersonData

(

SELECT

bei.BusinessEntityID,

pn.PersonData.value(N'(Person/PersonType)[1]', N'nchar(2)') AS PersonType,

pn.PersonData.value(N'(Person/NameStyle)[1]', N'dbo.NameStyle') AS NameStyle,

pn.PersonData.value(N'(Person/Title)[1]', N'nvarchar(8)') AS Title,

pn.PersonData.value(N'(Person/FirstName)[1]', N'dbo.Name') AS FirstName,

pn.PersonData.value(N'(Person/MiddleName)[1]', N'dbo.Name') AS MiddleName,

pn.PersonData.value(N'(Person/LastName)[1]', N'dbo.Name') AS LastName,

pn.PersonData.value(N'(Person/Suffix)[1]', N'nvarchar(10)') AS Suffix,

pn.PersonData.value(N'(Person/EmailPromotion)[1]', N'int') AS EmailPromotion

FROM PersonNodes pn

INNER JOIN BusinessEntityIDs bei

ON pn.JoinID = bei.JoinID

)

INSERT INTO Person.Person

(

BusinessEntityID,

PersonType,

NameStyle,

Title,

FirstName,

MiddleName,

LastName,

Suffix,

EmailPromotion

)

SELECT

BusinessEntityID,

PersonType,

NameStyle,

Title,

FirstName,

MiddleName,

LastName,

Suffix,

EmailPromotion

FROM GetPersonData;

WITH PersonNodes

(

SELECT

ROW_NUMBER() OVER (ORDER BY C.value('PersonType[1]', 'nchar(2)')) AS JoinID,

C.query('.') AS PersonData

FROM @People.nodes('//Person') T(C)

BusinessEntityIDs

(

SELECT

ROW_NUMBER() OVER(ORDER BY BusinessEntityID) AS JoinID,

BusinessEntityID

FROM @TemporaryIDs

GetPersonData

(

SELECT

bei.BusinessEntityID,

pn.PersonData.value(N'(Person/EmailAddress)[1]', N'nvarchar(50)') AS EmailAddress

FROM PersonNodes pn

INNER JOIN BusinessEntityIDs bei

ON pn.JoinID = bei.JoinID

)

INSERT INTO Person.EmailAddress

(

BusinessEntityID,

EmailAddress

)

SELECT

BusinessEntityID,

EmailAddress

FROM GetPersonData;

END

You notice in this example that we first have to get business entity IDs for each person. I use a table variable and the DML OUTPUT clause to grab the business entity IDs ahead of time. Then I shred the XML in two steps, once to put relevant data in the Person.Person table and another to put the email addresses in Person.EmailAddress. The procedure call looks like this:

EXEC Person.AddPersonXML N'<Person>

<FirstName>George</FirstName>

<LastName>Jetson</LastName>

<EmailAddress>George.Jetson@spacelysprockets.com</EmailAddress>

</Person>

<LastName>Flintstone</LastName>

<EmailAddress>Fred.Flintstone@sladegravelpit.com</EmailAddress>

</Person>';

As you can see, we can easily add as many people as you want with a single XML parameter. That is, so long as you respect the 2.1 GB size limit on SQL Server LOB data types like XML. Also since you are using XML you can use any client application in any language that can generate XML character data; you’re not limited to just any .NET-specific functionality.

You can verify that the people above were added with a few simple queries:

SELECT *
FROM Person.Person
WHERE BusinessEntityID > 20777;

SELECT *
FROM Person.EmailAddress
WHERE BusinessEntityID > 20777;

SELECT *
FROM Person.BusinessEntity
WHERE BusinessEntityID > 20777;

The results look like the following:

In the third article of this series at http://blog.beyondrelational.com/2009/03/practical-sql-server-xml-part-three.html, MVP and author Jacob Sebastian will show you how to add XML Schema validations to your xml data type parameter sets.