Search results matching tag 't-sql'

Stored procedure debugging in SSDT and LocalDB

jamiet — Thu, 30 May 2013 19:53:15 GMT

Stored procedure debugging is a feature that has existed in SQL Server Management Studio (SSMS) for some time (since before SQL Server 2005 I believe) yet in my experience not many folks make use of it, indeed many folks aren’t even aware it exists. In this post I shall try and shine a light on what I believe is an underused feature in the SQL Server toolset.

When I have attempted to use debugging within SSMS in the past I have invariably run into problems. I can remember as recently as December 2010 trying to debug a stored procedure on a remote server and it required ports to be opened up on the firewall and other such hoopla – that’s not a pleasant experience when all you want to do is hit F10 and start stepping through your code. Happily in SQL Server 2012 the feature showed up in SQL Server Data Tools (SSDT) which in short is a Visual Studio-based tool aimed squarely at developers rather than DBAs and the advantage this brings is something called LocalDB. LocalDB is quite simply a free, lightweight edition of SQL Server that gets installed with SSDT, lives on your local box, and whose intended use is to help you write code (I do need to blog about LocalDB in more depth at some point). Due to LocalDB being on your local machine debugging T-SQL code there is its as close as its going to get to “it just works”.

Let’s take a look at how this happens, I’m going to demonstrate by debugging stored procedures in an open source project I’m working on called SSIS RestartFramework, the source code (currently) lives at http://ssisrestartframework.codeplex.com/SourceControl/latest in case you want to download it and follow along at home.

As with many features in SSDT the debugging feature is accessed via SQL Server Object Explorer (SSOX). From there one can right click on a stored procedure and select “Debug Procedure…”:

Selecting that option displays a dialog where one specifies values for the parameters of the stored procedure:

Clicking OK launches a query window with some scaffolding code that calls the stored procedure with the specified values:

From there one can step through the code, executing each line in turn, using F10 & F11 just as if this were .Net code. Notice how, in the screenshot below, we are stepping through the code of the [RestartFramework].[PutETLJob] stored procedure which we stepped into (using F11) from the scaffolding code above.

Furthermore this screenshot depicts the Locals pane displaying the value of all in-scope variables and also the very cool ability to pin a variable (in this case @vRowCount) so that it “floats”, making it easy to see the value and also to see it changing.

I should point out that you do not need to have your stored procedures in LocalDB in order to debug them using SSDT, you can connect to any instance of SQL Server from SSOX and then launch the debugging experience.

This has a been an overview of the T-SQL debugging features in SSDT. Let me know if it proves useful to you.

@Jamiet

Increment a variable in the SET clause of an UPDATE statement [T-SQL]

jamiet — Tue, 28 May 2013 11:24:06 GMT

There’s a very nifty feature of T-SQL that, in my experience, not everyone knows about but is handy for generating surrogate key values as part of an ETL routine. I myself had to ask on Twitter what the syntax was because I couldn’t remember it (thanks to Matija Lah for reminding me) so I think its worth blogging in case I forget it again.

To summarize the feature, you can use a variable in the expression of the SET clause in an UPDATE statement while updating it at the same time. The syntax is:

@variable = column = expression

where expression can include @variable.

It will probably crystalise in your brain if you see it in action, so here is a little demo:

/*Create a table and populate with data*/ DROP TABLE [#DimProduct]; --will fail first time you run the script, so just run it again CREATE TABLE [#DimProduct] ([ProductId] INT, [ProductCode] VARCHAR(10)); DECLARE @i INT = 0; INSERT [#DimProduct]([ProductId],[ProductCode])VALUES(1,'qwerty'); /*Simulating an existing record*/ WHILE @i <10 BEGIN /*Simulating some new rows for which we want to generate a a surrogate key*/ INSERT [#DimProduct]([ProductCode])VALUES(REPLACE('qwerty@i','@i',@i)); SET @i += 1; END /*Take a look at the data*/ SELECT * FROM [#DimProduct] /*Generate surrogate keys using an UPDATE*/ DECLARE @MaxSurrogateKey INT = ( SELECT ISNULL(MAX([ProductId]),0) FROM [#DimProduct] ); UPDATE [#DimProduct] SET @MaxSurrogateKey = [ProductId] = @MaxSurrogateKey + 1 /****The clever bit****/ WHERE [ProductId] IS NULL; /*Take another look at the data*/ SELECT * FROM [#DimProduct]

Here’s the output:

Notice that our UPDATE statement has generated contiguous surrogate key values for all rows that previously had a NULL surrogate key. Pretty neat huh? Well, I think so anyway!

@jamiet

Why We Write #4 - An Interview With Doug Lane

drsql — Wed, 24 Apr 2013 02:56:22 GMT

My next guest is Doug Lane, who has been blogging for the past few years on his website: http://www.douglane.net/. Doug is also a speaker who has spoken at SQL Saturdays as well as the SQL PASS Summit, and as I write these interview questions was on his way to speak at the SQL PASS Business Analytics conference in 2013. I also liked quite a few of the pictures in his photostream here (http://www.flickr.com/photos/58251371@N06/), particularly because I tried the bean bag juggling, though I cannot find the picture anymore.

I have never met Doug personally (so as such have never mistaken him for anyone else), and am only acquainted with him through Thomas LaRock's Rockstar Blogger list, so I am looking forward to finding out more about him from the questions in my interview.

He also once won a SQL Cruise from Idera with this YouTube video:

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

1. There was a point in time when you didn't have a blog, didn't tweet, and probably had no public presence whatsoever. And then, one day, you made the decision to put yourself out there. What prompted you to write that first blog entry that got you started?

I had a personal blog about 6-7 years ago but I struggled with it. I did two posts: one about music I listen to while running, and one about a camping trip I took. It was horrible and nobody read it. I'm hoping the internet found a way to bury it alongside my MySpace page.

Then, in late 2010, two things really pushed me to get back into blogging: Brent Ozar's blog and Steve Jones' presentation, "The Modern Resume". Steve convinced me that having a blog was important, and Brent convinced me I was capable of doing it. I'm immensely grateful for their influence. There's no way I'd be writing today without it.

2. We all have influencers that affect our trajectory as a writer. It may be a teacher who told you that you had great potential, or another writer who impressed you that you wanted to be like? Or perhaps on the other end of the spectrum it was a teacher who told you that you were too stupid to write well enough to spell your own name, much less have people one day impressed with your writing? Who were your influences that stand out as essential parts of your journey to the level of writer you have become?

I've heard stories from friends about how they had teachers that told them they weren't cut out for writing, science, athletics, music, and so on. It makes me enormously grateful to have never had a teacher like that. I had some uninspiring teachers, sure, but never one that told me I was destined to fail at something. In fact, several of my English teachers encouraged me to write. There's one in particular I'll never forget. Diana Daniels was my 7th and 8th grade English teacher. For one assignment, she gave me a note that said this:

I still have it. I've gradually thinned out the stuff I kept from school but that one always makes the cut. I still feel good when I read it, even though it's from 25 years ago. Sincere encouragement lasts a lifetime.

I would have LOVED writing for Saturday Night Live, Mystery Science Theater 3000, or Archer. I even chose my college, The University of Iowa, based on the fact they offered screenwriting classes and had a heralded creative writing program. While a writing career for film or television didn't come to be, I'm a firm believer in things happening in the right time and place. I can still write blog posts, presentations, and the occasional ultra-low budget screenplay with a sense of humor.

3. As the years pass, how has your writing changed? Do you feel like it is becoming a more natural process? Or perhaps you get more critical of your own writing to the point that it takes you longer?

Like all writers do, I think I'm getting better with practice. I'm getting better with tempo and efficiency. I'm also getting better at expressing my personality through writing without it resembling a train of thought. I'm a little faster now than when I first started. I don't struggle as much with how I'm going to say something.

On the other hand, I take more time now to make sure what I'm writing is technically correct. I feel like I need to be more accurate and informed on my subjects. When I first started, I thought, "If I miss this detail or that, it's okay." Now I try to make sure the minor details all check out too. As we all know, there's NOTHING worse than being wrong on the internet.

4. Assume a time machine has been created, and you are allowed to go back in time to speak to a group of potential writers, in which you and I are in attendance. What would you tell "past us", and do you think that your advice would change where you and I are in our careers now? Like would you tell yourself that one day you would be sitting here for a rather long period of time answering interview questions and not getting paid for it, instead of doing something else?

First, I'd make sure they were all in a room on the ground floor. Then I'd tell them that in 2013, all of the following will be vastly more famous than even the most popular blogger: a monotesticluar man who cheated like crazy in French bike races, a show about unclaimed storage units, an unfinished calendar from an ancient civilization, skinny jeans for men, and a singular picture of a cat who looks unhappy. Many writers will throw themselves out the windows in shock and despair.

Once the glass shard-encrusted writers have been pulled from the shrubs outside the window (we're on the ground floor for a reason), I'd tell them the good news. I'd show them fivethirtyeight.com, lifehacker.com, deadspin.com, and of course some SQL Server-related blogs. I'd tell them there are huge audiences for the things you're interested in. Combine that with the means to self-publish and promote, and no one can prevent you from being a success.

I'd also share the two thoughts really paralyzed me when I was first starting my blog:

Paralyzing Thought #1: "Someone already wrote about this topic."

Louise Hay put it brilliantly. Addressing a crowd at a convention, she said this: "You'll have all these speakers. We're all going to say the same thing, really. But we're going to do it in slightly different ways. And everybody wants to hear things differently. Just because I say something, some of you will get it, some of you will say 'What is that woman talking about?' But another teacher -- or three or six or twelve -- can say the same thing that I'm saying in different words. And you'll go, 'Oh, that's brilliant! Never heard that before.'"

Whatever you're going to write about, someone's almost certainly already covered it. But they haven't written about it your way. People may not understand an idea until they hear you explain it in your words.

Paralyzing Thought #2: "Nobody's reading this."

That's true in the beginning. Unless you're already well known, very few people will be anticipating your first blog post because you have yet to demonstrate quality and consistency. Your blog is like a retail store. If what you have inside is interesting or useful to people, word will spread and more people will come. Be patient.

It's scary putting yourself out there in printed permanence, I know. But nothing gets you past the fear of publishing faster than hitting the "Publish" button again and again. If you need ideas or motivation, pick up Problogger's 31 Days to a Build a Better Blog. You don't have to do all the exercises in 31 days. Just do one whenever you're stuck. (Let's pretend this e-book exists and I'm not violating the space-time continuum by recommending it.) Gradually, your content will get better and your audience will grow.

I'd conclude by saying, "Don't stress over it. Nothing about your blog is as big a deal as you think it is."

If I told Historical Me that he'd be interviewed about writing and it wasn't conducted from prison, I think he'd be excited.

5. Do you have any assistance from an editor, either formally or informally. And in either case, do you like your set up do you sometimes wish you had it different?

I don't have an editor, and I'm not really worried about having one until I start writing detailed technical posts. That day is still a ways off since I'm really enjoying the topics and level of complexity I'm covering now. I do like to bounce ideas off people, but I don't usually have finished posts proofread by someone else.

A bad habit of mine is proofreading after I've published. (Don't get me wrong, I proofread beforehand too.) I think every post I've done in the last year or so, I went back and updated at least twice after it'd been published. George Lucas admires my inability to leave perfectly good work alone.

6. Finally, beyond the "how" questions, now the big one that defines the interview series. Why do you do write?

I write because I enjoy it and I want to make people's lives better, whether it's getting people unstuck from a technical problem or just making them chuckle. The work I do is immensely enjoyable. I want to share some of that joy. I don't care if I'm paid for it or not -- that doesn't enter into my thought process.

Another reason I write is to establish myself as a resource people can come to when they need a problem solved. I'm not trying to come across as a technical expert (because I'm really not -- not by a long shot), but rather someone who's had to solve the same problems my readers are having. It's funny that 48% of my page views come from a post about connecting a MacBook to a projector. I had no idea that post would be so useful.

While I don't set out to write life-altering material. I have a powerful story coming that I hope will re-orient people. I'm waiting for the right time to publish it -- probably early summer.

Bonus Question: Are there any projects coming up that you would like to tell people about?

One project I've had on the back burner for FAR too long: SQL Server Murder Mystery Hour. Like one of those murder mystery dinner parties, except done at a SQL Saturday or maybe one of the nights of PASS Summit. I've let this idea percolate for two years now (like I said, FAR too long). Here are a few ticklers:

Think Murder on the Orient Express, except it's a database or server that's killed.
Attendees form teams to solve the crime.
Names of the suspects are a gag, e.g., Bill Freeley is a consultant.
Slightly over-the-top, Poirot-style interrogations will reveal clues.

Okay, writing about it really has me excited to get moving on it again.

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

Wow, this has been a fantastic read. Doug has given a lot of great and interesting answers to my questions. Some notes:

I too loved Steve Jones' presentation, "The Modern Resume". Chock full of fantastic information about how to enhance your career and behave yourself (Brent’s blog is great too :)
I have always wanted to be a sitcom writer too. I took some advice from the early Simpson’s writers. The goal is something that is funny over and over again, not just once. I know I still laugh at the Simpson’s after 15-20 viewings of many episodes.
The George Lucas comment about not leaving well enough alone is a problem I have too.
I feel a good bit of kinship with his “why” answers too (other than the MacBook thing, I considered editing Mac references out…)

I am sad a bit that he didn’t hit on my answer to past me, but it will come one day. Like I have said before, when I get the same answer, I will interview myself…

I love the idea for the murder mystery, and am hereby invited to SQL Saturday Nashville .Next to do the SQL Server Murder Mystery at least as a session, or perhaps some other time.. (you will still have to provide your own transportation :).

Next up is Jason Strate (@stratesql), someone I have known for quite a long time (and have worked with several times), and am certainly looking forward to see what he comes up with. I have really enjoyed these interviews so far, and I hope you have too.

Trigger Authoring Decision Tree

merrillaldrich — Wed, 06 Mar 2013 18:46:09 GMT

Obtaining rowcounts when using Composable DML [T-SQL]

jamiet — Thu, 07 Feb 2013 13:59:57 GMT

In my August 2009 blog post Exploring Composable DML I introduced a new feature in SQL Server 2008 called Composable DML and also outlined one of its limitations; namely that data from the OUTPUT cannot be aggregated prior to insertion. Composable DML does have some useful scenarios however and one of those is in capturing and storing values that are replaced by an UPDATE (which I have talked about before in Using Composable DML to maintain entity history). Here’s the basic premise:

INSERT old_values( id, name ) --use Composable DML to store the values that were replaced by an UPDATE
SELECT	mrgout.deleted_id
,		mrgout.deleted_name
FROM	(
		MERGE	tgt
		USING	src
			ON	tgt.id = src.id
		WHEN MATCHED THEN  UPDATE	
			SET	tgt.NAME = src.NAME   WHEN NOT MATCHED THEN   INSERT(id,name)
			VALUES(src.id,src.name)
		OUTPUT $ACTION AS action_
		,		INSERTED.id AS inserted_id
		,		INSERTED.NAME AS inserted_name
		,		DELETED.id AS deleted_id
		,		DELETED.name AS deleted_name
		)mrgout
WHERE	mrgout.action_ = 'UPDATE' --Filtering on $action=UPDATE allows us to get the replaced values from DELETED virtual table
;

This statement updates some rows in [tgt] and stores the old values in [old_values]. I think that’s rather useful, especially in a data warehousing scenario where one may wish to MERGE to a type 1 dimension table. Unfortunately this scenario gives rise to another limitation of Composable DML – the value returned by @@ROWCOUNT is the number of rows that were affected in [old_values], not in [tgt]. The following code (which you can simply copy/paste and execute and which is also available on pastebin) demonstrates this problem:

/******************************************************************************************************************************
A demonstration of capturing rowcounts when using composable DML. The problem I'm trying to demonstrate here is that 
I don't think there is a way to capture the number of rows affected by the MERGE
 
Jamie Thomson, 2013-02-07
******************************************************************************************************************************/
 
/*Setup table first and insert some data into [src]*/
USE tempdb
IF OBJECT_ID('src') IS NOT NULL		DROP TABLE src;
CREATE TABLE src (
	id		INT
,	name    NVARCHAR(MAX)
);
IF OBJECT_ID('tgt') IS NOT NULL		DROP TABLE tgt;
CREATE TABLE tgt (
	id		INT
,	name    NVARCHAR(MAX)
);
/*[updates] will be used as the target of the Composable DML insertion*/
IF OBJECT_ID('old_values') IS NOT NULL		DROP TABLE old_values;
CREATE TABLE old_values (
	id		INT
,	name    NVARCHAR(MAX)
);
INSERT src(id,name)VALUES(1,'don'),(2,'kaina');
GO
 
/*Everything after here gets run twice because the batch ends with GO 2*/
INSERT old_values( id, name ) --use Composable DML to store the values that were replaced by an UPDATE
SELECT	mrgout.deleted_id
,		mrgout.deleted_name
FROM	(
		MERGE	tgt
		USING	src
			ON	tgt.id = src.id
		WHEN MATCHED THEN  UPDATE	
			SET	tgt.NAME = src.NAME   WHEN NOT MATCHED THEN   INSERT(id,name)
			VALUES(src.id,src.name)
		OUTPUT $ACTION AS action_
		,		INSERTED.id AS inserted_id
		,		INSERTED.NAME AS inserted_name
		,		DELETED.id AS deleted_id
		,		DELETED.name AS deleted_name
		)mrgout
WHERE	mrgout.action_ = 'UPDATE' --Filtering on $action=UPDATE allows us to get the replaced values from DELETED virtual table
;
SELECT	[@@ROWCOUNT]=@@ROWCOUNT,row_tally_in_tgt=(SELECT COUNT(*) FROM tgt)  -- <-Rowcount only provides tally of rows affected by the outer INSERT, not the MERGE
GO 2

Here is the output:

Notice that the Composable DML containing the MERGE statement is executed twice. The first execution inserts two rows into [tgt] yet @@ROWCOUNT returns zero because zero rows were inserted into [old_values] by the outer query. The second execution results in two rows in [tgt] being updated hence two rows are inserted into [old_values] and hence why @@ROWCOUNT returns two. It appears there is no way to discover the number of inserts or updates that were committed by the MERGE; if you’re a fan of logging rowcounts during ETL operations (which I think you should be) then this is a big problem. The only way I can think of getting around this problem is to break the statement into two like so (for brevity I haven’t included the full code listing so it is also available on pastebin):

/*Setup part is the same as before, we do need an extra table for capturing the output of our MERGE tho*/
IF OBJECT_ID('mrgout') IS NOT NULL		DROP TABLE mrgout;
CREATE TABLE mrgout (
	action_			NVARCHAR(MAX)
,	inserted_id		INT
,	inserted_name    NVARCHAR(MAX)
,	deleted_id		INT
,	deleted_name    NVARCHAR(MAX)
);
INSERT src(id,name)VALUES(1,'don'),(2,'kaina');
GO
 
/*Everything after here gets run twice because the batch ends with GO 2*/
TRUNCATE TABLE mrgout;
INSERT mrgout(action_, inserted_id, inserted_name,deleted_id, deleted_name )
SELECT	mrgout.action_
,		mrgout.inserted_id
,		mrgout.inserted_name
,		mrgout.deleted_id
,		mrgout.deleted_name
FROM	(
		MERGE	tgt
		USING	src
			ON	tgt.id = src.id
		WHEN MATCHED THEN  UPDATE	
			SET	tgt.NAME = src.NAME   WHEN NOT MATCHED THEN   INSERT(id,name)
			VALUES(src.id,src.name)
		OUTPUT $ACTION AS action_
		,		INSERTED.id AS inserted_id
		,		INSERTED.NAME AS inserted_name
		,		DELETED.id AS deleted_id
		,		DELETED.name AS deleted_name
		)mrgout
;
INSERT	dbo.old_values(id,name)
SELECT	deleted_id,deleted_name FROM mrgout
WHERE	mrgout.action_ = 'UPDATE' --Filtering on $action=UPDATE allows us to get the replaced values from DELETED virtual table
SELECT	[INSERT_@@ROWCOUNT]=(SELECT COUNT(*) FROM mrgout WHERE action_ = 'INSERT'),[UPDATE_@@ROWCOUNT]=(SELECT COUNT(*) FROM mrgout WHERE action_ = 'UPDATE'),row_tally_in_tgt=(SELECT COUNT(*) FROM tgt)   GO 2

Executing that returns:

This is much better. We now know the tally of insertions and updates committed by the MERGE, unfortunately we have had to do it in two separate statements which in a way defeats the point of using MERGE in the first place (and don’t forget some of the other current problems with MERGE). If you can think of a better way of doing it then I’m all ears – please reply in the comments below.

I’m not saying don’t use MERGE and I’m not saying don’t use Composable DML; just be aware of their limitations. Personally I think there should be built-in functions, similar to @@ROWCOUNT, that return the number of rows INSERTed/DELETEd/UPDATEd by a MERGE; Aaron Bertrand agreed and raised a Connect submission to that affect: Katmai : Merge does not distinguish rowcounts in triggers which has, unfortunately, “been closed as won’t fix”.

@Jamiet

Using constraints to enforce uniqueness of ordered sets of rows

Alexander Kuznetsov — Mon, 28 Jan 2013 20:10:00 GMT

Even the simplest data integrity rules are not easy to implement in the database if instead of individual rows we need to deal with groups or subsets.

For example, making sure that a column value is unique in a table is as trivial as creating a unique constraint or index. However, what are we supposed to do if we do not want to store a group of values twice? What if we store cooking recipes as sequences of steps (sets of values), and we do not want to store one and the same recipe (one set of values) more than once?

In the following sections we shall solve this problem using only constraints and indexed views, just for fun, as a SQL puzzle. (Of course, we could use a trigger or some other thing with subqueries - but that would be a different puzzle).

At the time of this writing I have not yet used in production the technique I am going to describe. I think it is quite complex and looks tricky, and the reason is simple: in my opinion RDBMS have not been designed to deal with this kind of problems. As such, we have to come up with complex workarounds.

Enforcing Uniqueness of Ordered Sets, of Limited Size Only

In this solution we shall deal with sets that have at most five elements.

In some cases, the order of elements in a subset matters. For example, the following two sequences of instructions yield different results.

Cooking a hard-boiled egg:

Put eggs into pan, add water
Bring water to boiling, boil 4 minutes;
Remove eggs and cool them down in cold water

Keeping the egg uncooked:

Put eggs into pan, add water
Remove eggs and cool them down in cold water
Bring water to boiling, boil 4 minutes

These two recipes are clearly different, and they yield different results. Of course, the second recipe makes no sense, but we want the database to store them both.

Suppose, however, that the database should not be able to store one and the same recipe more than once.

Setting up tables and test data

Before implementing this requirement, let us create the tables and add test data. Both recipes discussed above involve the same three steps:

CREATE TABLE dbo.RecipeSteps
    (
      RecipeStepId INT NOT NULL ,
      Intsructions VARCHAR(50) NOT NULL ,
      CONSTRAINT PK_RecipeSteps PRIMARY KEY ( RecipeStepId )
    ) ;
GO
INSERT  INTO dbo.RecipeSteps
        ( RecipeStepId, Intsructions )
VALUES  ( 1, 'Put eggs into pan, add water' ),
        ( 2, 'Bring water to boiling, boil 4 minutes' ),
        ( 3, 'Remove eggs and cool them down in cold water' ) ;

Let us upload upload the first recipe, as follows:

CREATE TABLE dbo.RecipesStepByStep
    (
      RecipeId INT NOT NULL ,
      StepNumber SMALLINT NOT NULL ,
      RecipeStepId INT NOT NULL ,
      CONSTRAINT PK_RecipesStepByStep PRIMARY KEY ( RecipeId, StepNumber ) ,
      CONSTRAINT FK_RecipesStepByStep_RecipeSteps FOREIGN KEY ( RecipeStepId ) REFERENCES dbo.RecipeSteps ( RecipeStepId )
    ) ;
GO

INSERT  INTO dbo.RecipesStepByStep
        ( RecipeId, StepNumber, RecipeStepId )
VALUES  ( 1, 1, 1 ),
        ( 1, 2, 2 ),
        ( 1, 3, 3 ) ;

SELECT  StepNumber ,
        Intsructions
FROM    dbo.RecipeSteps AS rs
        JOIN dbo.RecipesStepByStep AS sbs ON rs.RecipeStepId = sbs.RecipeStepId
WHERE   sbs.RecipeId = 1 ;

StepNumber Intsructions
---------- --------------------------------------------------
1          Put eggs INTO pan, ADD water
2          Bring water TO boiling, boil 4 minutes
3          Remove eggs AND cool them down IN cold water

Let us also add the second recipe:

INSERT  INTO dbo.RecipesStepByStep
        ( RecipeId, StepNumber, RecipeStepId )
VALUES  ( 2, 1, 1 ),
        ( 2, 2, 3 ),
        ( 2, 3, 2 ) ;

SELECT  StepNumber ,
        Intsructions
FROM    dbo.RecipeSteps AS rs
        JOIN dbo.RecipesStepByStep AS sbs ON rs.RecipeStepId = sbs.RecipeStepId
WHERE   sbs.RecipeId = 2 ;

StepNumber Intsructions
---------- --------------------------------------------------
1          Put eggs INTO pan, ADD water
2          Remove eggs AND cool them down IN cold water
3          Bring water TO boiling, boil 4 minutes

Right now nothing prevents us from adding a second copy of the first recipe. You can run the following script and see for yourself that it succeeds:

To enforce uniqueness of recipes, we shall use an indexed view and a unique index on top of it.

To keeps the scripts simple, let us assume that recipies cannot consist of more than five steps:

ALTER TABLE dbo.RecipesStepByStep
ADD CONSTRAINT RecipesStepByStepMax5 CHECK (StepNumber BETWEEN 1 AND 5) ;

Let us also create an indexed view that stores all the steps of a recipe in one row.

Once we have all the steps in one row, we can use a unique index to make sure that a sequence of steps is unique. We want the contents of the indexed view to look as follows, with the first line representing the first recipe:

RecipeId Step1 Step2 Step3 Step4 Step5 ----------- ----------- ----------- ----------- ----------- ----------- 1 1 2 3 0 0 2 1 3 2 0 0

Note: To get around the limitations of indexed views, we need to make sure that all the pivoted columns are not nullable. This is why columns Step4 and Step5 are zeroes instead of nulls – otherwise we would not be able to create the unique index RecipesStepByStepsPivoted_UniqueRecipes in the following script:

CREATE VIEW dbo.RecipesStepByStepsPivoted WITH SCHEMABINDING AS SELECT RecipeId, count_big(*) AS Cnt, SUM(CASE WHEN StepNumber = 1 THEN RecipeStepId ELSE 0 END) AS Step1, SUM(CASE WHEN StepNumber = 2 THEN RecipeStepId ELSE 0 END) AS Step2, SUM(CASE WHEN StepNumber = 3 THEN RecipeStepId ELSE 0 END) AS Step3, SUM(CASE WHEN StepNumber = 4 THEN RecipeStepId ELSE 0 END) AS Step4, SUM(CASE WHEN StepNumber = 5 THEN RecipeStepId ELSE 0 END) AS Step5 FROM dbo.RecipesStepByStep GROUP BY RecipeId ; GO CREATE UNIQUE CLUSTERED INDEX RecipesStepByStepsPivoted_CI ON dbo.RecipesStepByStepsPivoted(RecipeId) ; GO CREATE UNIQUE INDEX RecipesStepByStepsPivoted_UniqueRecipes ON dbo.RecipesStepByStepsPivoted(Step1, Step2, Step3, Step4, Step5) ; GO SELECT RecipeId , Step1 , Step2 , Step3 , Step4 , Step5 FROM dbo.RecipesStepByStepsPivoted ; RecipeId Step1 Step2 Step3 Step4 Step5 ----------- ----------- ----------- ----------- ----------- ----------- 1 1 2 3 0 0 2 1 3 2 0 0

It is easy to verify that the unique index built on top of the indexed view does enforce the uniqueness of recipes:

BEGIN TRANSACTION ;

INSERT  INTO dbo.RecipesStepByStep
        ( RecipeId, StepNumber, RecipeStepId )
VALUES  ( 3, 1, 1 ),
        ( 3, 2, 2 ),
        ( 3, 3, 3 ) ;

SELECT  StepNumber ,
        Intsructions
FROM    dbo.RecipeSteps AS rs
        JOIN dbo.RecipesStepByStep AS sbs ON rs.RecipeStepId = sbs.RecipeStepId
WHERE   sbs.RecipeId = 3 ;

ROLLBACK ;

Cannot INSERT duplicate KEY row IN object 'dbo.RecipesStepByStepsPivoted' WITH UNIQUE INDEX 'RecipesStepByStepsPivoted_UniqueRecipes'.
The statement has been terminated.

RecipeId    Step1       Step2       Step3       Step4       Step5
----------- ----------- ----------- ----------- ----------- -----------
1           1           2           3           0           0
2           1           3           2           0           0

Note: because we are using an undex to enforce uniqueness, we must set a limit on the number of elements. It does not have to be as low as five – we set it that low just to keep the examples short.

Let us close a couple of loopholes in this limited solution and move on to another problem.

First, we used zeroes in the indexed view to indicate that there is no such step in the recipe. In our example both recipies had three steps, so the indexed view had zeroes in Step4 and Step5 columns in both rows. To distinguish between missing steps and valid step IDs, we need to make sure that zero is not a valid ID:

ALTER TABLE dbo.RecipeSteps
ADD CONSTRAINT CHK_RecipeSteps_PositiveId CHECK ( RecipeStepId > 0 );

Also we need to make sure that step numbers in the recipe have no gaps, otrherwise we still can store the same recipe twice. The following script demonstrates the loophole:

BEGIN TRANSACTION ;

INSERT  INTO dbo.RecipesStepByStep
        ( RecipeId, StepNumber, RecipeStepId )
VALUES  ( 3, 1, 1 ),
        ( 3, 3, 2 ),
        ( 3, 5, 3 ) ;

SELECT  StepNumber ,
        Intsructions
FROM    dbo.RecipeSteps AS rs
        JOIN dbo.RecipesStepByStep AS sbs ON rs.RecipeStepId = sbs.RecipeStepId
WHERE   sbs.RecipeId = 3 ;

ROLLBACK ;

StepNumber Intsructions
---------- --------------------------------------------------
1          Put eggs INTO pan, ADD water
3          Bring water TO boiling, boil 4 minutes
5          Remove eggs AND cool them down IN cold water

To close the loophole, we need to make sure that step numbers have no gaps. The following script enforces the rule:

ALTER TABLE dbo.RecipesStepByStep
ADD PreviousStepNumber AS CAST(CASE WHEN StepNumber > 1 THEN StepNumber-1 END AS SMALLINT) PERSISTED ;
GO

ALTER TABLE dbo.RecipesStepByStep
ADD CONSTRAINT FK_RecipesStepByStep_PreviousStep
FOREIGN KEY(RecipeId, PreviousStepNumber)
REFERENCES dbo.RecipesStepByStep(RecipeId, StepNumber);

Conclusion

As we have seen, enforcing the uniqueness of ordered groups of rows with constraints is doable, although somewhat complex. I am not sure if I would use this approach with a large production table. I think that RDBMS is not designed to natively solve these kinds of problems - this is why solving this apparently simple problem is so complicated.

If you are using some platform other than RDBMS to solve such problems, please comment this post and share your experiences.

One more reason to to understand query plans, not directly performance related

drsql — Wed, 23 Jan 2013 04:28:00 GMT

One of the things that separates a good programmer from a great one is a firm understanding about what is going on inside the computer. For some programming languages, it is very obvious what is going on inside the computer because you are working at a very low level. For example, if you are a C/C++ programmer writing an OS, you will know a lot about the hardware as you will interact with it directly. As a .NET programmer you are more encapsulated from the hardware experience, making use of the .NET framework.

None of the aforementioned programming languages comes anywhere close to the level of encapsulation that we SQL programmers work with. When you execute a statement like:

SELECT *
FROM Tablename

A firestorm of code is executed to optimize your query, find the data on disk, fetch that data, format it for presentation, and then send it to the client. And this is the super dumbed down version. SQL is a declarative language, where basically we format a question or task for the system to execute without telling it how. It is my favorite type of language because all of the pushing bits around get tedious. However, what is important for the professional SQL programmer is to have some understanding of what is going under the covers, understanding query plans, disk IO, CPU, etc. Not necessarily to the depth that Glenn Alan Berry (http://sqlserverperformance.wordpress.com/) does, but certainly a working knowledge.

Performance is the obvious reason, since it is clearly valuable to be able to optimize a query, but sometimes it can come in handy to debug an issue you are having with a query. Today, I ran across an optimizer condition that, while perfectly understandable in functional terms, would have driven me closer to nuts if I hadn’t been able to read a query plan. The problem came in based on the number of rows returned, either it worked perfectly or it failed with an overflow condition. Each query seemingly touches the exact same rows in the table where the overflow data exists…or did it.

The setup. The real query that the problem was discovered in was our data warehouse, and was a star schema configuration with 20+ joins. In the reproduction, I will use a simple table of numbers to serve as the primary table of the query.

create table ExampleTable -- It really doesn’t matter what this table has. The datevalue column will be used to
                           -- join to the date table, that I will load from the
(                          -- from the values I put in this table to make sure all data does exist
    i int constraint PKExampleTable primary key,
    dateValue date
)

;with digits (i) as( --(The code for this comes from my snippet page: http://www.drsql.org/Pages/Snippets.aspx).
                    select 1 as i union all select 2 as i union all select 3 union all
                    select 4 union all select 5 union all select 6 union all select 7 union all
                    select 8 union all select 9 union all select 0)
,sequence (i) as (
                    select D1.i + (10*D2.i) + (100*D3.i) + (1000*D4.i) + (10000*D5.i) + (100000*D6.i)
                    from digits as D1, digits AS D2, digits AS D3 ,digits AS D4, digits as D5, digits As D6
)
insert into ExampleTable(i, dateValue)
select i, dateadd(day, i % 10,getdate()) -- Puts in 10 different date values
from sequence
where i > 0 and i < 1000
order by i

Next I will load the date table with all of the distinct dateValue values that we loaded into the ExampleTable, plus one, which is the max date value for the datatype. In the “real” world case, this is one of our surrogate null values we use to indicate that it is the end date. (Yes, we are ignoring the Y10K problem.)

create table date
(
datevalue date constraint PKDate primary key
)

insert into date
select distinct dateValue
from ExampleTable
union all
select '99991231'
go

In the typical usage, the number of rows is quite small. In our queries, we are adding 1 to the dateValue to establish a range of a day (in the real query it was actually a month). Executing the following query that returns 99 rows is successful:

select *, dateadd(day,1,date.dateValue)
from   ExampleTable
         join date
            on date.dateValue = ExampleTable.dateValue
where i < 100

However, remove the where clause (causing the query to return 999 rows):

select *, dateadd(day,1,date.dateValue)
from   ExampleTable
          join date
             on date.dateValue = ExampleTable.dateValue

And you will see that this results in an overflow condition...

Msg 517, Level 16, State 3, Line 2
Adding a value to a 'date' column caused an overflow.

Hmmm, this could be one of those days where I don’t get a lot of sleep :). Next up, I check the max date value that can be returned.

--show that the top value that could be returned is < maxdate
select max(date.dateValue)
from   ExampleTable
         join date
            on date.dateValue = ExampleTable.dateValue

At this point, I start feeling like I am going nuts. The value returned is 2013-01-30. So no data is actually returned that should be too large for our date column… So then I think, well, let's add one to that value and take the max:

select max(date.dateValue), max(dateadd(day,1,date.dateValue))
from   ExampleTable
         join date
            on date.dateValue = ExampleTable.dateValue

This returns, mockingly:

Msg 517, Level 16, State 3, Line 2
Adding a value to a 'date' column caused an overflow.

So, since it worked with fewer rows earlier. I decide to try lowering the number of rows again, this time using a derived table, and it DOESN’T error out, even though it is obvious (because I stacked the deck…data) that the same data is just repeated for the dateValue, particularly since we get the same max dateValue as we did earlier.

select max(date.dateValue), max(dateadd(day,1,date.dateValue))
from   (select top 100 * from ExampleTable order by i) as ExampleTable
         join date
            on date.dateValue = ExampleTable.dateValue

Well, you are possibly thinking, this just doesn't make sense. It is how I felt too after trying to do the logic in my head. I will admit that I didn’t know about query plans I would have been completely lost. But alas, the answer was fairly easily located in the plan. Taking a look at the plan for the query version that returns 99 rows:

select *, dateadd(day,1,date.dateValue)
from   ExampleTable
         join date
            on date.dateValue = ExampleTable.dateValue
where i <= 100

We get the following estimated plan:

In this plan, it uses a nested loops operator, which basically will do 100 seeks from the top input (the ExampleTable), for each row fetching the date value, and then calculating the scalar value (dateadd(day,1,date.dateValue) ) on the values that match in the plan. Since the 9999-12-31 date is never used, there is no overflow.

However, when the number of rows in the when the size of the output reaches a certain tolerance (in this case 999 instead of 99) from the following query:

select *, dateadd(day,1,date.dateValue)
from   ExampleTable
         join date
            on date.dateValue = ExampleTable.dateValue

We get a different plan, one that is causing us issues:

Instead of nested loops, it uses a Hash Match Join, which takes the entirety of the smaller table and builds an internal hash index (basically setting up buckets that can be scanned much faster than an entire table…in our case, probably just a single bucket), and then scan the other set checking to see if the row exists in the hash index.

It is in the process of building the hash index that our query runs into trouble. Since the date table is so much smaller, it plans to build the hash index on that table, and pre-creates the scalar values as it is doing the scan, since there are 11 rows in the date table, rather than having to calculate the value 999 times if it did it after the join. When it adds a day to the 9999-12-31 date, it fails.

I know, the question of how practical is this scenario is bound to arise. I won’t lie to you and suggest that it is likely to happen to you as it it did to me. However, the point of this blog isn’t that this one scenario is bound to happen to you, but rather that understanding how SQL Server executes queries will help to give you insight to fix problems with your system, mostly performance, but sometimes every esoteric issues that won't just leap out as being based on the query plan that was chosen. (For more reading on query plans, check out Grant Fritchey’s Simple-Talk book on query plans: http://www.amazon.com/Server-Execution-Plans-Grant-Fritchey/dp/1906434026).

In the end, the fix to my problem was simple. Make sure that the value that has meaning in the table, but not in the query, was filtered out:

select *, dateadd(day,1,date.dateValue)
from   ExampleTable
         join date
            on date.dateValue = ExampleTable.dateValue
               and date.dateValue < '9999-12-31'

Note: a commenter noted that in some cases, excluding the offensive data using the ON criteria/WHERE clause may not solve the issue. This is very true, and really will be made evident in the plan. I would expect it to be more likely to be definitely excluded in the JOIN clause, but you really can't guarantee anything that the optimizer might do without changing the source data (or representing the source data using a derived table as):

select *, dateadd(day,1,date.dateValue)
from ExampleTable
       join (select * from date where date.dateValue < '9999-12-31') as date
            on date.dateValue = ExampleTable.dateValue

Looking at the different variances to the plan you should be able to diagnose a "hidden" problem such as I have described by finding the flow of data and making sure that the filtering operation happens before the calculating of the scalar that causes the overflow error. This may harm performance in my query for even the more "ideal" case where it could have used indexes, so you may yet have more work to do...But this is what makes data programming fun, now isn't it?

The client code that handles timeouts

Alexander Kuznetsov — Thu, 10 Jan 2013 21:05:00 GMT

After timeouts we need to make sure that active transactions, if any, are rolled back. All timeout handling must be done on the client. This post provides the implementation and unit tests.

Implementation

The following class extends SqlCommand and rolls back active transactions after timeouts:

public static class SqlCommandExtentions { public static void ExecuteNonQueryWithErrorHandling(this SqlCommand command) { try { command.ExecuteNonQuery(); } catch (SqlException e) { HandleSqlException(command, e); throw; } } public static SqlDataReader ExecuteReaderWithErrorHandling(this SqlCommand command) { try { return command.ExecuteReader(); } catch (SqlException e) { HandleSqlException(command, e); throw; } } private static void HandleSqlException(SqlCommand command, SqlException e) { const int timeoutCode = -2; if (e.Number != timeoutCode) return; var rollbackCommand = command.Connection.CreateCommand(); rollbackCommand.CommandText = "IF @@TRANCOUNT > 0 BEGIN ; ROLLBACK ; END ;"; rollbackCommand.CommandType = CommandType.Text; rollbackCommand.CommandTimeout = 0; rollbackCommand.ExecuteNonQueryWithErrorHandling(); } }

Testing

We need to unit test the two methods. For each one, we need to test the following four cases:

Command succeeds
Command blows up with some other error, which must not be caught by our handling
Command times out, active transaction
Command times out, no active transaction

The following tests cover these four cases for ExecuteReader. The other four tests are very similar, so there is no need to post them.

[Test] public void ExecuteReaderWithErrorHandling_WorksOnSuccess() { using (var dr = ExecuteReaderWithErrorHandling(sqlConn, "SELECT 1 AS n")) { Assert.IsTrue(dr.Read()); Assert.AreEqual(1, dr.GetInt32(0)); } } [Test] public void ExecuteReaderWithErrorHandling_HandlesTimeout_ActiveTransaction() { try { ExecuteReaderWithErrorHandling(sqlConn, "EXEC dbo.IWillTimeOut", timeoutInSeconds: 1); Assert.Fail("Must throw exception"); } catch(SqlException e) { Assert.AreEqual(-2, e.Number); VerifyNoOpenTransaction(sqlConn); } } [Test] public void ExecuteReaderWithErrorHandling_HandlesTimeout_NoActiveTransaction() { try { ExecuteReaderWithErrorHandling(sqlConn, "EXEC dbo.IWillTimeOutWithoutTransaction", timeoutInSeconds: 1); Assert.Fail("Must throw exception"); } catch (SqlException e) { Assert.AreEqual(-2, e.Number); VerifyNoOpenTransaction(sqlConn); } } [Test] public void ExecuteReaderWithErrorHandling_ThrowsOtherExceptions() { try { ExecuteReaderWithErrorHandling(sqlConn, "EXEC dbo.IThrowError8134"); Assert.Fail("Must throw exception"); } catch (SqlException e) { Assert.AreEqual(8134, e.Number); VerifyNoOpenTransaction(sqlConn); } }

These unit tests utilize a few database objects:

CREATE FUNCTION dbo.IWillCompleteInNSeconds ( @NumSeconds INT ) RETURNS INT AS BEGIN ; DECLARE @StartTime DATETIME , @Toggle INT ; SELECT @StartTime = GETDATE() , @Toggle = 1 ; WHILE DATEDIFF(SECOND, @StartTime, GETDATE()) < @NumSeconds BEGIN ; SET @Toggle = 1 - @Toggle ; END ; RETURN 0 ; END ; GO CREATE PROCEDURE dbo.IWillTimeOut AS BEGIN ; BEGIN TRANSACTION ; SELECT dbo.IWillCompleteInNSeconds( 5 ) AS SomeNumber ; COMMIT ; END ; GO CREATE PROCEDURE dbo.IWillTimeOutWithoutTransaction AS BEGIN ; WAITFOR DELAY '00:00:05' ; END ; GO CREATE PROCEDURE dbo.IThrowError8134 AS BEGIN ; SET XACT_ABORT ON ; DECLARE @i INT ; SELECT @i = 1/0 ; SELECT 1 AS n; END ;

After the timeout

Alexander Kuznetsov — Wed, 09 Jan 2013 18:23:00 GMT

After the timeout, we may sometimes, but not always, end up with an outstanding transaction. If this is the case, we need to rollback the transaction ourselves. Otherwise the consequent commands which use the same connection might not work as expected.

Let us debug through some client code and see what is happening in more detail.

Prerequisites

We shall need a few database objects, as follows:

CREATE TABLE dbo.Log1(InTran VARCHAR(20)); GO CREATE PROCEDURE dbo.TestTran AS BEGIN IF @@TRANCOUNT > 0 BEGIN INSERT dbo.Log1(InTran) VALUES('In Tran'); END ELSE BEGIN INSERT dbo.Log1(InTran) VALUES('Not In Tran'); END ; END ; GO CREATE FUNCTION dbo.IWillCompleteInNSeconds ( @NumSeconds INT ) RETURNS INT AS BEGIN ; DECLARE @StartTime DATETIME , @Toggle INT ; SELECT @StartTime = GETDATE() , @Toggle = 1 ; WHILE DATEDIFF(SECOND, @StartTime, GETDATE()) < @NumSeconds BEGIN ; SET @Toggle = 1 - @Toggle ; END ; RETURN 0 ; END ; GO CREATE PROCEDURE dbo.IWillTimeOut AS BEGIN ; BEGIN TRANSACTION ; SELECT dbo.IWillCompleteInNSeconds( 5 ) AS SomeNumber ; COMMIT ; END ; GO

Running the example

In the following test, add three breakpoints at the placeholder statements:

[Test] public void TimeoutTest() { for (var i = 0; i < 1000; i++) { using (var conn = new SqlConnection(localConnString)) { conn.Open(); try { const string iwilltimeout = "EXEC dbo.IWillTimeOut"; ExecuteSql(conn, iwilltimeout, 1); } catch (SqlException e) { System.Console.WriteLine(e.Message + " " + e.Number); } const string saveTrancount = "EXEC dbo.TestTran"; ExecuteSql(conn, saveTrancount); System.Console.WriteLine("Breakpoint placeholder 1"); } using (var conn = new SqlConnection(localConnString)) { conn.Open(); const string saveTrancount = "EXEC dbo.TestTran"; System.Console.WriteLine("Breakpoint placeholder 2"); ExecuteSql(conn, saveTrancount); System.Console.WriteLine("Breakpoint placeholder 3"); } } }

Let us start a profiling session that captures individual SP:StmtStarting and SP:StmtCompleted events. Also let us debug through this code.

First breakpoint - outstanding transaction

When we stop at the first breakpoint, let us run the following query:

SELECT * FROM dbo.Log1 WITH(NOLOCK)

It will return "In Tran", which means that after the timeout dbo.TestTran was called in the middle of an outstanding transaction.

Note: not all timeouts result in outstanding transactions. For example, later you can rerun this example using a different stored procedure:

ALTER PROCEDURE dbo.IWillTimeOut AS BEGIN ; BEGIN TRANSACTION ; WAITFOR DELAY '00:00:05' ; COMMIT ; END ; GO

You can see for yourself that when this procedure times out, there will be no outstanding transaction.

Second breakpoint - still outstanding transaction

Let us debug to the second breakpoint. Technically the first connection went out of scope and has been reused by the second using block, but we can still see the same uncommitted data:

SELECT * FROM dbo.Log1 WITH(NOLOCK)

The connection has not been reset yet - we do not see sp_reset_connection in the Profiler yet.

Third breakpoint - connection reset

Only when we actually get to run a command against the reused connection, the connection is actually reset. We can observe sp_reset_connection call in the Profiler, and the uncommitted data inserted by the first invocation of dbo.TestTran is gone.

Note: to my best knowledge, there is no guarantee that the same connection will be reused. It consistently happens on my box, but it could work differently on yours. As usual with closed source systems, one can never know for sure.

Consequences of continuing to use the same connection after the timeout

As we have seen, if we catch a timeout and later modify the database using the same connection, all the modifications done after the timeout may be eventually rolled back and lost, without us getting any error messages.

Conclusion

We need to rollback the outstanding transaction ourselves. We must also be aware that not all timeouts result in outstanding transactions. I have written two extension methods that implement it, and I will post it soon.

You may want to read more about connection pools here

The perils of double-dash comments [T-SQL]

jamiet — Thu, 06 Dec 2012 09:21:39 GMT

I was checking my Twitter feed on my way in to work this morning and was alerted to an interesting blog post by Valentino Vranken that highlights a problem regarding the OLE DB Source in SSIS. In short, using double-dash comments in SQL statements within the OLE DB Source can cause unexpected results. It really is quite an important read if you’re developing SSIS packages so head over to SSIS OLE DB Source, Parameters And Comments: A Dangerous Mix! and be educated. Note that the problem is solved in SSIS2012 and Valentino explains exactly why.

If reading Valentino’s post has switched your brain into “learn mode” perhaps also check out my post SSIS: SELECT *... or select from a dropdown in an OLE DB Source component? which highlights another issue to be aware of when using the OLE DB Source.

As I was reading Valentino’s post I was reminded of a slidedeck by Chris Adkin entitled T-SQL Coding Guidelines where he recommends never using double-dash comments:

That’s good advice!

@Jamiet