Tag dynamic sql

Free Training – SQL Saturday 42

Good news! I’m speaking at SQL Saturday 42 this Saturday. Got nothing to do? Head on down to Goodwill Columbus at 1331 Edgehill Rd, Columbus, OH. Got something to do? Cancel it.

I’m excited about the presentations I’m giving – I haven’t given the indexing presentation in a long time and it should be a lot of fun. And the Dynamic SQL presentation is one of the first in the day. It’s a nice easy way (I think) to get your day started.

A Dynamic World Demands Dynamic SQL

Dynamic SQL is a misunderstood and much maligned part of a DBA’s tool kit – it can be used to solve difficult business problems, respond to diverse data needs, and alleviate performance problems. Many DBAs reject dynamic SQL outright as a potential source of SQL injections, being poorly performing, or just for being a hacky solution in general. Not so! Jeremiah Peschka has been making extensive use of dynamic SQL throughout his career to solve a variety of problems. He’ll set about dispelling these misconceptions and demonstrate how dynamic SQL can become a part of every DBA’s tool kit.

Indexes And Other Free Performance Boosts

The database is often viewed as a major performance bottleneck. There are a number of quick, easy, painless techniques that can increase the performance of an application not just by a small amount, but by orders of magnitude. These techniques includes simple indexing techniques, T-SQL techniques, and general database application design patterns that give great gains in performance. In this session, you will learn how to look at a database to identify these problem areas and how to resolve common issues that you will encounter.

What else?

Let’s say you’re interested in something else. What should you go see? Well, Michael Swart (blog | twitter) put together a nice little blog post about How I plan to spend my weekend.

If you like business intelligence, I suggest you hit up Dave Rodabaugh’s presentations. I cannot speak highly enough of Dave’s work. Not only is he one of the brightest BI people I know, he’s also been a teacher, friend, and mentor to me for a long time.

There will be some kind of dinner/drinks/whatever going on afterwards at Barley’s Smokehouse (map). I plan on being there for a little bit. Even if you can’t make it to the event, head on over there around 6:00PM. I’ll be there.

Shrink, Damn’d Log! Shrink, I Say!

Ever have a database log file grow to epic proportions on the weekend? I have. Ever forget to set up job monitoring on that server? I have. Ever get so pissed off that you decided to write a job to automatically shrink the log files whenever they grow? I have.

Free SQL Server Training in April

I’m going to be speaking at a few events in April. I bet you want to come visit and hear the crazy sounds that I’m going to be making!

Thursday April 8th – Columbus, OH

Fundamentals of SQL Server Internals

The Abstract: Want to know what makes SQL Server tick?
Ever wonder what SQL Server is doing when you run a query?
Ever wonder which parts of SQL Server are responsible for specific functionality?

Jeremiah Peschka can’t promise answers to every question, but he can set you on the path to knowledge about the inner workings of SQL Server.

Location: Battelle for Kids – 1160 Dublin Rd Suite 500, Columbus, OH 43215

Time: 6:30 PM, but you can show up at 6:00 PM for refreshments and socializing.

The Details: This is a fun little romp around SQL Server’s internals. This isn’t intended to be an exhaustive introduction. It’s supposed to be a fun look at how SQL Server works internally and give you more information to get around.

Saturday April 10 – Richmond, VA

I’m speaking at SQL Saturday 30 in Richmond, Virginia!

Fundamentals of SQL Server Internals

This one starts at 8:30 in the morning, so bring your coffee!

A Dynamic World Demands Dynamic SQL

The Abstract: Dynamic SQL is a misunderstood and much maligned part of a DBA’s tool kit – it can be used to solve difficult business problems, respond to diverse data needs, and alleviate performance problems. Many DBAs reject dynamic SQL outright as a potential source of SQL injections, being poorly performing, or just for being a hacky solution in general. Not so! Jeremiah Peschka has been making extensive use of dynamic SQL throughout his career to solve a variety of problems. He’ll set about dispelling these misconceptions and demonstrate how dynamic SQL can become a part of every DBA’s tool kit.

The Skinny: I sometimes catch flak for this, but I use a ton of dynamic SQL on a daily basis. It’s the only way I could possibly build some of our most complex reports. Frankly, dynamic SQL can perform just as well as anything else that you’re doing so why not take advantage of the tools at your disposal? Come along and learn more.

Saturday April 17 – Chicago, IL

Double whammy! You can travel to Chi-town and see me present both of my talks again but in reverse order!

A Dynamic World Demands Dynamic SQL

Bring your dancin’ shoes we’re getting that party started at 9:00 AM right after the welcome and keynote.

Fundamentals of SQL Server Internals

Less dancing, more learning this one goes on at 3:00PM. Hopefully your brain won’t be full by this point in time. If it is, I suggest that you empty it.

Links for the Week of 2009.06.12

SQL Server

Denis Gobo provides a double dose of dynamic SQL hotness via Changing exec to sp_executesql doesn’t provide any benefit if you are not using parameters correctly and Avoid Conversions In Execution Plans By Using sp_executesql Instead of Exec. Following his tips in here will net you some considerable benefits in how your dynamic SQL behaves and performs in your production applications. The best part is that he provides a huge amount of example code to back up everything he’s saying.

Free SQL Server tools that might make your life a little easier Mladen Prajdic maintains an amazing list of free tools to help data folks get their job done. SSMS Tools Pack (which Mladen created and maintains) is incredibly helpful. I suspect that if he finds these other tools useful, they’ll probably save you many many hours over the years to come.

SQL Server Troubleshooting Tips and Tricks This isn’t a blog post so much as it is really important to keep around. It’s just a list of tips and tricks to keep things easy and painless with SSMS. If you didn’t check out the pain of the week webcast with Michelle Ufford and Brent Ozar on SSMS, you should do so when you get the chance, it’s well worth it!

More SQL Server Training Videos Training. Free. Videos. I’m in there, that’s really all the reason you need to visit that link.

BONUSTen SQL Server Functions That You Have Ignored Until Now Bonus link from Denis Gobo, once again, covering some great, little known, SQL Server functions.


Unit Testing is Not a “Figure It Out Later” Read this. Read this now. Do it. We’ll still be here. Stop what you’re doing and read this. I cannot stress how important it is for you to read this, even us data folks. If I could put a blink tag around this paragraph, I would. (Thanks to Stephen Wright for finding this link.)

Defensive Programming – Assumptions Must be Guaranteed or Tested Aaron Alton blogs about the importance of defensive programming. While this is specifically from a SQL Server standpoint, it’s important no matter which language you’re using. In fact, Aaron proves an important point – you can put any development practice to use in any language.

Stuff & Things

Improving your intranet – keep it sustainable using kaizen – Patrick Walsh talks about using kaizen to produce evolutionary improvements to an intranet. You could do this with anything, really, but it’s a good read either way you look at it.

I cheated and stole the last two links from Lifehacker, but they’re just too good to not share.

CopyPasteCharacter.com Makes Special Characters Easy to Paste Yup, easy. Nice. Easy mode: engage.

LiberKey Installs 200+ Portable Applications I have too many thumbdrives laying around the house. Now I have a use for them.

Converting Measurements with T-SQL

I frequently find myself having to perform calculations on the source data based on a range of user supplied values. Usually there will be some kind of target and destination parameter supplied. So, for example, the users will want to see total sales in terms of burgers sold or something like that. Here’s where it gets tricky. Let’s say I own two franchises – one that sells burgers and one that sells tacos. I don’t want to know what my taco stand profits look like in terms of burgers. I want to see that number in terms of tacos.

So, what’s the best way to go about this?

Well, in order to get started you’re going to need the fn_split function to split strings and you’ll need a numbers table to create fn_split.

Once you have created your numbers table and the fn_split function you’ll want to set up some data to get started.

IF OBJECT_ID('conversions') IS NOT NULL
  DROP TABLE conversions;

IF OBJECT_ID('measurements') IS NOT NULL
  DROP TABLE measurements;

CREATE TABLE conversions
  convert_from INT,
  convert_to INT,
  factor DECIMAL (5, 1)

CREATE TABLE measurements
  m_type INT,
  measurement DECIMAL(18, 5)

INSERT INTO conversions
SELECT 1, 1, 1
SELECT 1, 2, 0.5
SELECT 1, 3, 4
SELECT 2, 1, 2
SELECT 2, 2, 1
SELECT 2, 3, 9
SELECT 3, 1, 4;

INSERT INTO measurements
SELECT 1, 50.5
SELECT 1, 100
SELECT 2, 93
SELECT 2, 76
SELECT 3, 56;

If you take a look at the data, you’ll see that there are a lot of possible combinations out there even with just this limited data set:

  FROM measurements AS m
       INNER JOIN conversions AS c
          ON m.m_type = c.convert_from

What’s the best way to limit these results so we aren’t writing strange where clauses? JOINs, of course!

Let’s say that our user wants to see all measurements with a type of 1 converted to the measurement of type 2 and measurements of type 2 converted to type 3 (this doesn’t really make much sense, I know, but business rules don’t have to make sense). Here’s what our input might look like:


SET @source = '1,2';
SET @target = '2,3';

Rather annoyingly, the best way I’ve found part from using comma delimited tuples is to do positional processing of data. Fortunately, the fn_split function supplies a position number as one of the output columns which makes keeping track of this sort of thing infinitely easier.

So, in order to move forward here’s what we do:

  1. Load @source into a temporary table
  2. Load @target into a temporary table
  3. Join #sources to #targets to create a master conversion table
  4. Profit!
IF OBJECT_ID('tempdb..#sources') IS NOT NULL
  DROP TABLE #sources;

IF OBJECT_ID('tempdb..#targets') IS NOT NULL
  DROP TABLE #targets;

IF OBJECT_ID('tempdb..#convert') IS NOT NULL
  DROP TABLE #convert;

SELECT pos, element AS c_from
  INTO #sources
  FROM dbo.fn_split(@source, ',');

SELECT pos, element AS c_to
  INTO #targets
  FROM dbo.fn_split(@target, ',');

SELECT s.pos, s.c_from, t.c_to
  INTO #convert
  FROM #sources AS s
       INNER JOIN #targets AS t
          ON s.pos = t.pos

Woohoo, we’re almost there. But how do we profit from this? Well, we use that query we wrote right after we filled the sample tables with data and we JOIN on the #convert table:

SELECT m.measurement AS original_measurement,
       (m.measurement * c.factor) AS converted_measurement
  FROM measurements AS m
       INNER JOIN conversions AS c
          ON m.m_type = c.convert_from
       INNER JOIN #convert AS conv
          ON c.convert_from = conv.c_from
         AND c.convert_to = conv.c_to

Through the magic of joins we’ve managed to convert more than one type of thing to another type of thing without having to resort to UNIONs (the insert doesn’t really count).

You also can download the entire example source code in a single file, rather than copying and pasting like a fiend: converting-measurements.sql

Dynamic T-SQL Resources

As a follow up to my Dynamic T-SQL presentation, a list of Dynamic T-SQL resources have been posted on the AppDev SIG page of PASS site. Hit up the link for more info.

When I first started writing dynamic SQL, I had a hard time finding any resources on the topic. If you have any that you’d like to share, hit me up in the comments or shoot me an email at jeremiah.peschka@gmail.com and I’ll be sure to review it and put the link up.

Fun with PATINDEX() and DB_NAME()

I’m going to guess everyone can guess that DB_NAME() will give you the name of the current database when called with no parameters. If not, now you know.

PATINDEX() is a little bit more fun, so before we get into things, here’s how PATINDEX() works:

SET @my_var = 'fun with patterns';

SELECT PATINDEX('%pattern%', @my_var);
-- returns 10

PATINDEX() returns the position of the pattern in the search expression. It’s 1 indexed for those of you who come from a 0-based string world.

Moving forward!

So far, I haven’t done anything cunning. But what if you have a crazy situation like this:

You have a publisher database. There are two subscriptions set up with bidirectional transactional replication. They are named Subscriber1 and Subscriber2. They are stored on separated physical servers, but they have identical schemas. Funnily enough, and this is where it gets tricky, there’s another database in the picture. One each server there’s a Web database (WebOne and WebTwo). It does reporting and uses tables in the subscriber. It also holds all the stored procedures. Suddenly things get a lot more entertaining because you now have to fully reference table names.

(Is this confusing enough without pictures?)

What to do?!?

Well, for 99% of of your stored procedures, you can use CTRL+H and ignore this blog post. You probably can ignore this blog post for the other 1%. Unless you’re insane, like me, and generate ad hoc SQL from a CLR assembly. If that’s the case, keep reading.

So, I have a CLR assembly which contains a function. That function emits SQL code which is executed via sp_executesql. Rather than maintain two versions of this assembly, one for each server, I decided to use REPLACE, PATINDEX, and DB_NAME to make my life more fun.

DECLARE @condition AS NVARCHAR(10);
SET @condition = '%Two';

IF (SELECT PATINDEX(@condition, DB_NAME())) > 0
  SELECT @magic = REPLACE(@magic, 'Subscriber1', 'Subscriber2');

That’s it. It’s pretty simple, really. This is the part where you remark on how smart I am and send me money via PayPal.

Dynamic SQL Presentation Materials

First off, thanks to everyone who attended today’s Dynamic SQL presentation. I appreciate everyone taking the time out of their busy day to attend the first AppDev SIG Live Meeting in quite some time. Hopefully there will be many more.

I’ve attached a copy of the Dynamic SQL presentation that I gave today. Here’s a run down of the contents of the zip file and an explanation of the database setup:

  • The DynamicSQL database referenced in the SQL is a copy of the AdventureWorks 2005 database renamed to DynamicSQL.
  • A copy of the dbo.Customers table has been imported from the Northwind database.
  • The Dynamic T-SQL Support.sql file contains SQL to create a calendar table and a numbers table that are used in the example SQL as well as the Helper_LongPrint stored procedure that is used to help debug longer stored procedures.
  • The Dynamic T-SQL.sql file contains the example SQL used in the presentation as demo code.
  • QueryStress1.sqlstress and QueryStress2.sqlstress contain the settings for the two SQL Query Stress demonstrations that were shown. QueryStress1 shows a positive baseline for using parameterized SQL whereas QueryStress2 shows a less performant option using a query build in an ad hoc fasion.

If you have any questions, feel free to email me at jeremiah.peschka@gmail.com.

Dyanmic SQL Presentation Tomorrow – March 31

Just a quick reminder that I will be presenting on Dynamic SQL tomorrow at 1pm EST for the PASS AppDev SIG. Details are available in my previous post. Hope to see you all there.

Presenting on March 31

On March 31st at 1PM EST, that’s 10AM to those of you on the West Coast, I will be giving my presentation Dynamic SQL: What’s all the fuss about, anyway? via Live Meeting, courtesy of the Application Development SIG. The presentation will last for approximately 1 hour. Here’s a bit more info to get you all kinds of excited about it:

Dynamic SQL is a powerful but difficult tool to master. Jeremiah will show you techniques for creating rock solid, fast, dynamic SQL that will meet the day to day needs of your production system.

Live Meeting URL: https://www.livemeeting.com/cc/usergroups/join?id=JP2R3Q&role=attend
Audio Dial-In Information:
Toll free: +1 (866) 379-8990
Participant code: 6489756

This site is protected with Urban Giraffe's plugin 'HTML Purified' and Edward Z. Yang's Powered by HTML Purifier. 531 items have been purified.