A SQL Poem

One of the jobs I recently applied for asked for a SQL poem as part of the application.  I love stuff like this so I took a little time and came up with the following.  (This actually did happen to me once...)

I didn't originally title it, but I think I'll call it...

"Wait... STAT!" by S

The week was starting bad for Joe

His app was running way too slow.

He summoned Steve, from regions nether

To patch performance back together.

“Well, here’s your problem-- clear as Windex:

You’ve got a missing clustered index.

This calls for not just any geek; we'll

Need one who speaks fluent SQL."

Functions, unctions, query plans:

"We'll need more seeks, and fewer scans.

And then, to get some extra speed, yer

Gonna need a stored procedure."

Steve worked fast, with mumbled zeal,

Declared, "Success! Just watch 'er squeal!"

Said Joe, "While I applaud your fervor,

My problem's on this other server."

Google Interview Question: "Explain a database in three sentences to your eight-year-old nephew."

[Reposted from my non-SQL blog.  Apologies to the handful of readers who follow both.]

From this list of Google interview questions, I found the question: "Explain a database in three sentences to your eight year old nephew."  Here's my attempts.

Attempt 1: Trying to Capture Third Normal Form

1. On a sheet of notebook paper, make a list of all your video game systems, and number them 1 through whatever.
2. On another sheet of paper, make a list of all your video games, also numbered 1 through whatever.
3. For each video game, find the game system that it goes with on the first list, and write the number from the first list next to the game on the second list.

Attempt 2: Eight-year-olds Are Still Kinda Into Santa

1. Santa has a list of every kid in the world, which includes the kid's name, address, age, and naughty/nice score.
2. Santa has another list of every toy in the world-- how much it costs, what color it is, how much it weighs (for the sleigh).
3. Santa has a special "many-to-many" list with just the kid-number and toy-number, to keep track of which kids want which toys, so it's really easy to figure out things like how many of each toy he needs to buy.

Attempt 3: They Learn This Stuff in the Womb, I Swear

1. You know how you implemented MongoDB for the social networking site you wrote last year, after the metadata structure got sufficiently gnarly for a simple key/value store, and users were demanding the ability to search on any attribute?
2. Well, when I was a kid, we had these things called Relational Database Management Systems, which were really good at enforcing relational integrity (which was important because the computers still depended on algorithms instead of magic like they do today) but you had to know a thing or two about relational data design, and the RDMS got kind of slow when everyone on the planet started uploading holographic 3D videos of what they had for lunch every day.
3. Now go get your Uncle S  a beer.

SQL Saturday "Action Items"

Kendra Little's "Cerealizable" Isolation Level

SQL Saturday #60 was a huge success!  As a volunteer, I'm proud to be part of a team that put on a great event.  As an attendee, I'm kind of blown away at the quality of the event, especially knowing that it was all donated time and money.  (None of the speakers were paid, and many actually flew to Cleveland in February on their own dimes!  You guys rock!)

The best thing about going to events like SQL Saturday and the PASS Summit is that you get to meet and hear all these fantastic speakers.  (The worst thing is, there's never time to meet and listen to all of them!)  This week's  new "discoveries" for me: Jeremiah Peschka, Aaron Bertrand (although I'm a huge fan of Aaron's "Bad Habits" series already), Kendra Little, and Tom LaRock.  All of these folks are wicked smaht and fun to listen to, and Kendra even draws groovy little cartoon drawrings like the one I shared here.

As usual, I went home with visions of new SQL tools and techniques dancing in my head.  Here's some things on my "to investigate" list:

• Erin Stellato introduced me to ClearTrace!!! Die, die, read80trace!!!  Okay, that's not fair-- read80trace has helped me through many performance audits, but it's kind of unruly to use.  ClearTrace looks like the answer to a SQL Profiler-analyzer's dreams.  Must learn it.
• About everyone made it clear that Twitter is not just for posting your breakfast intentions anymore.  Brent Ozar wrote a little book to help old folks like me catch up.  I actually joined twitter last night, and I feel more connected already, at least to the SQL community.
• Tom LaRock finally explained waitstats in a way that made sense.  I'm vowing to finally start looking at waitstats to diagnose performance issues.
• Kendra Little got me excited about snapshot isolation level again.  I thought it was about the coolest thing ever when it came out, but then never had the time or load test facilities to try it out.  I'm still convinced this will provide huge performance boosts to clients of my former employer, where blocking and deadlocking reign.
• Lastly, I'm going to get on the schedule of my local SQL user group to present on some SQL topic.  Giving SQL presentations was my favorite part of my last job, and seeing all these great presenters has me itching to get back on the other side of the lectern again.

That should be enough to keep me busy for a while.  

An Encryption Problem

Here's the scenario: we have a SQL Server database that until recently stored sensitive information in plaintext  (yikes!)  And we have an Access 2003 database in which we built a custom little front-end, with linked tables to the SQL database.  The vendor recently encrypted some of the fields in the database (thanks!) and now we need to update the Access database to comply.

The documentation from the vendor provided a basic example of accessing the encrypted information:

OPEN SYMMETRIC KEY APPKEY DECRYPTION BY CERTIFICATE APPCERT

SELECT CONVERT(VARCHAR,DECRYPTBYKEY(CUSTOMER.SSN)) AS SSN FROM CUSTOMER

That should be a pretty easy change in most code-- just open the key, wrap the encrypted field in a couple of functions, and you're good to go.  But linked tables in Access present an interesting problem.   I'll spare you the laundry list of things I tried, but suffice it to say that lots of approaches almost worked, but nothing quite let me get the key open before calling DecryptByKey.

What saved me was a little function called DecryptByKeyAutoCert, which extracts the private key from the cert and uses it to decrypt the value, all in one fell swoop.  Without the need to open the key separately, I could create a SQL view, and use INSTEAD OF triggers for the updates.  With baited breath, I linked my new view in Access... and it worked!

In short, here's the SQL that makes it all possible:

create view my_customer as
select customerid, fname, lname, convert(varchar(12),DecryptByKeyAutoCert(cert_id('APPCERT'),null,customer.ssn)) as ssn
from customer

create trigger trg_my_customer_upd on my_customer
instead of update as
begin
     open symmetric key APPKEY decryption by certificate APPCERT
     update customer set fname=i.fname, lname=i.lname, ssn=EncryptByKey(key_guid('APPKEY'),i.ssn)
     from inserted i where i.customerid=customer.customerid
     close symmetric key APPKEY
end

create trigger trg_my_customer_ins on my_customer
instead of insert as
begin
     open symmetric key APPKEY decryption by certificate APPCERT
     insert into customer (customerid, fname, lname, ssn)
     select customerid, fname, lname, EncryptByKey(key_guid('APPKEY'),ssn)
     from inserted
     close symmetric key APPKEY
end

With this view created, we can now issue simple SELECT, UPDATE, and INSERT queries against my_customer, treating the ssn column as a plaintext column.  The view decrypts the data as it comes into the resultset, and the triggers ecrypt the data as it goes back into the database.  In short, the my_customer view now acts just like the original customer table.

All we had to do then was to delete the original linked table in Access, link to the new view, and rename the linked table to the original name.  No need to rewrite any other logic in our mini-application!

I simplified a few things here, and in our case the cert was created without a password, but it's pretty easy to add that in if you need do.