Michael J. Swart

May 30, 2012

MERGE Statement Generator

Filed under: Miscelleaneous SQL,SQLServerPedia Syndication,Technical Articles — Tags: , — Michael J. Swart @ 12:00 pm

So what I've got here below is a web form. It takes a source query and target table and spits out a merge statement for the standard insert/update use case. I made it for myself but you might find it useful too. I'm counting on this to be a real time saver (at least for me).

Clustered Index Merge Showplan Operator

Some Neat Things

  • This webform doesn't post to any server. It's all javascript that gets executed in your browser. This means you don't have to worry about anyone stealing your code and I don't have to worry about this form going viral (ha ha).
  • SQL Snippets (either kind) don't quite handle the dynamic list of columns here. Otherwise, you would be reading a different post today.
  • The MERGE statement is a lot more versatile than what I show here. I'm just handling the most common use case.

Things to Watch For

  • Concurrency. If this is important to you, remember to use appropriate locks (usually UPDLOCK) on the target table.
  • Make sure the column list of the source query matches that of the target table
  • You're going to have to clean up the script if your target table has columns that are rowversion, identity, computed etc...
  • User input is being used to generate code here. So my SQL-injection spidey-sense starts to tingle. But it's okay in this case because I'm not running anything. I'm just displaying it. You're the one who's running this stuff so it's up to you to vouch for any generated code. Take care.

The Form


Put in your own data (or just use the sample here). When you're ready, hit MERGE!
Source Query
Target Table
Key Columns (one column per line)
Other Columns (one column per line)

Your Merge Query

May 23, 2012

The View of Foreign Keys That I Wanted

Filed under: SQL Scripts,SQLServerPedia Syndication,Technical Articles — Tags: , — Michael J. Swart @ 8:47 pm

tropical sunset

So I’ve been working on an automation project that makes frequent use of foreign key metadata. I find myself writing queries for this data but I discovered that there’s no super-easy out-of-the-box view of foreign keys for me to use. Here are the ones I considered.

INFORMATION_SCHEMA views

INFORMATION_SCHEMA views that give me foreign key information include

  • INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE for the foreign key info
  • INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS for the referring columns,
  • INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE for the referenced columns

The first view here gives a list of foreign keys and you have to join to the other two tables in order to find the column names. But it’s a crummy solution. First of all, if the foreign key has multiple columns, there’s no real way to match the referring columns to the referenced columns.

The second thing is that we don’t see foreign keys that point to unique Keys (as pointed out by Aaron Bertrand in his post The case against INFORMATION_SCHEMA views.

So that’s out. What else have we got in?

Microsoft SQL Server system views

These views include

  • sys.foreign_keys
  • sys.foreign_key_columns
  • with support from sys.columns and sys.tables

These are the views I deserve, but not the views I need right now. The joins are just too annoying to remember and type each time.

Besides, the word “parent” used here changes with context. The parent table in a foreign key relationship owns the foreign key and does the pointing. But say I’m modeling a hierarchy. In the context of the data model, children records point to their parent records. The mental effort needed to keep these straight is not difficult, but it’s annoying.

My Own Views

So I’ve created my own, the goal is to simplify typing and minimize joins. I skip the word “parent” all together and use “referrer” and “referrenced”. Feel free to use and build on these.

Update June 7, 2012: I added columns ReferrerColumnCanBeNull to each view. I found I wanted it, so I added it here.

FOREIGN_KEYS

CREATE VIEW dbo.FOREIGN_KEYS
AS
    SELECT  SCHEMA_NAME(fk.schema_id) AS FKSchema ,
            fk.name AS FK ,
            SCHEMA_NAME(p.schema_id) AS ReferrerSchema ,
            p.name AS Referrer ,
            STUFF(CAST(( SELECT ',' + c.name
                         FROM   sys.foreign_key_columns fkc
                                JOIN sys.columns c ON fkc.parent_object_id = c.object_id
                                                      AND fkc.parent_column_id = c.column_id
                         WHERE  fkc.constraint_object_id = fk.object_id
                         ORDER BY fkc.constraint_column_id ASC
                       FOR
                         XML PATH('') ,
                             TYPE
                       ) AS NVARCHAR(MAX)), 1, 1, '') AS ReferrerColumns ,
            ISNULL(( SELECT TOP 1
                            1
                     FROM   sys.foreign_key_columns fkc
                            JOIN sys.columns c ON fkc.parent_object_id = c.object_id
                                                  AND fkc.parent_column_id = c.column_id
                     WHERE  fkc.constraint_object_id = fk.object_id
                            AND c.is_nullable = 1
                   ), 0) AS ReferrerColumnsCanBeNull ,
            SCHEMA_NAME(r.schema_id) AS ReferencedSchema ,
            r.name AS Referenced ,
            STUFF(CAST(( SELECT ',' + c.name
                         FROM   sys.foreign_key_columns fkc
                                JOIN sys.columns c ON fkc.referenced_object_id = c.object_id
                                                      AND fkc.referenced_column_id = c.column_id
                         WHERE  fkc.constraint_object_id = fk.object_id
                         ORDER BY fkc.constraint_column_id ASC
                       FOR
                         XML PATH('') ,
                             TYPE
                       ) AS NVARCHAR(MAX)), 1, 1, '') AS ReferencedColumns ,
            fk.delete_referential_action_desc AS deleteAction ,
            fk.update_referential_action_desc AS updateAction ,
            fk.object_id AS FKId ,
            p.object_id AS ReferrerId ,
            r.object_id AS ReferencedId
    FROM    sys.foreign_keys fk
            JOIN sys.tables p ON p.object_id = fk.parent_object_id
            JOIN sys.tables r ON r.object_id = fk.referenced_object_id
GO

FOREIGN_KEY_COLUMNS

CREATE VIEW dbo.FOREIGN_KEY_COLUMNS
AS
SELECT  SCHEMA_NAME(fk.schema_id) AS FKSchema ,
        fk.name AS FK ,
        SCHEMA_NAME(p.schema_id) AS ReferrerSchema ,
        p.name AS Referrer ,
        pc.name AS ReferrerColumn ,
		pc.is_nullable AS ReferrerColumnCanBeNull ,
        SCHEMA_NAME(r.schema_id) AS ReferencedSchema ,
        r.name AS Referenced,
        rc.name AS ReferencedColumn ,
        fk.object_id AS FKId ,
        fkc.constraint_column_id AS FKColumnId ,
        p.object_id AS ReferrerId ,
        r.object_id AS ReferencedId
FROM    sys.foreign_keys fk
        JOIN sys.foreign_key_columns fkc ON fkc.constraint_object_id = fk.object_id
        JOIN sys.tables p ON p.object_id = fk.parent_object_id
        JOIN sys.columns pc ON fkc.parent_object_id = pc.object_id
                               AND fkc.parent_column_id = pc.column_id
        JOIN sys.tables r ON r.object_id = fk.referenced_object_id
        JOIN sys.columns rc ON fkc.referenced_object_id = rc.object_id
                               AND fkc.referenced_column_id = rc.column_id
GO

May 14, 2012

Lessons From Geordi La Forge

Filed under: Data Cartoons,Miscelleaneous SQL,SQLServerPedia Syndication,Tongue In Cheek — Michael J. Swart @ 11:56 am

I’ve been rewatching Star Trek again, I find I relate most to Geordi La Forge. He’s a technical guy. He wears the yellow shirt of engineering instead of the red shirt of command.

So I was inspired to write this light hearted post. I usually find non-technical posts a little wishy-washy (No offense to you Seth-Godin-types). But I liked the way this drawing turned out and I had to use it somehow right?

Picture of Geordi Laforge captioned W W G L F D

You may have already seen articles or websites elsewhere that detail a comparison between Star Trek operations and company operations. Well here’s another one. For the moment we’ll sidestep the lessons learned from Picard (on how to be a inspiring leader) and go straight to Geordi. Here are the lessons:

Make Everyone Believe You Can Do Anything …

Here’s how Geordi’s typically responds to requests from the captain:

Can you fix the neutrino flux generator? ==> either "I can fix that captain. Estimated time two minutes." or "Sorry captain, The flux couplers aren't in phase."

Did you see what he did there? It’s not a choice between “I can” or “I can’t”. It’s also not a choice between “it’s possible” or “it’s impossible”. If Geordi can accomplish a task, he essentially takes the credit with “I can do this”. If he can’t accomplish the task, then his words imply “It can’t be done.” He never implies that his skills are lacking. So are Geordi’s skills ever lacking? No:

… And Then Make Sure They’re Right …

Even when the pressure’s on and someone calls your bluff. There’s a persistence that can pay off:
RIKER: Gentlemen, we’re giving you an assignment. The one thing we don’t want to hear is that it’s impossible.
PICARD: I need the transporters to function, despite the hyperonic radiation.
LA FORGE: Yeah, but that’s imp… Yes, sir.

and then later:
LA FORGE: Captain – we can do it. We can modify the transporters. It’ll take fifteen years and a research team of a hundred…

Most impossible things are actually just really really difficult. For example, recently I asked a question on stackexchange about avoiding downtime during a migration. The first feedback I got was the comment “I don’t think [the downtime] can be avoided” which got a couple up-votes. But it turns out there was a way; it just took a lot of effort (I blogged about that effort in this series). It’s the yellow shirt’s job to explain the “how” and “what” so that the red shirts can make informed choices.

Later on in another episode:
LA FORGE: Ferengi codes are damn near impossible to break.
PICARD: Gentlemen, I have the utmost confidence in your ability to perform… the impossible.

See! It paid off. But it’s easy for me to give advice that boils down to “be able to do anything” it’s another thing to make it so. How do you get great at what you do?

… By Being Awesome

Easier said than done right?

So Geordi’s got skills, and he uses those skills to build a great career for himself. But where did he get those skills? I figure you can become great at something in a number of ways:

  • Be talented at it (you can’t change you, but you can change fields)
  • Hard work. There’s no getting by without hard work. Imagine you’re on vacation sitting on a beach. You’re looking at the hut next to you and see some guy in a chair reading SQL Server Pro on his iPad. You just can’t compete with that guy. I’m not suggesting you read trade magazines on holiday, but are you in a field where you might want to?

It boils down to finding your passion. And I hope you’ve found yours. Geordi is a guy whose passionate about the latest thing in his field. It almost seems like he cares about it more than job security:

PICARD: Warp without warp drive.
RIKER: They’re gonna put you out of a job, Geordi.
LA FORGE: I hope so, Commander.

Antilessons From Geordi

But I like my role models the same way I like a buffet. I pick and choose the good lessons and leave the rest behind. Here’s a Geordi “lesson” that didn’t make the cut:

LA FORGE: I don’t know, Data, my gut tells me we ought to be listening to what this guy’s trying to tell us.
DATA: Your gut?
LA FORGE: It’s just a… a feeling, you know, an instinct. Intuition.
DATA: But those qualities would interfere with rational judgment, would they not?
LA FORGE: You’re right, sometimes they do.
DATA: Then… why not rely strictly on the facts?
LA FORGE: Because you just can’t rely on the plain and simple facts. Sometimes they lie.

I don’t buy it. I get what he’s trying to say and the sentiment is correct, but the point of view is wrong. Facts don’t lie by definition. When Geordi says that facts lie sometimes, I would say instead that the facts are incomplete, or an assumption has been made incorrectly.

Once I was asked by a developer why his code was throwing the error message:

Table ‘noidentity’ does not have the identity property. Cannot perform SET operation.

even though the table in question was showing that it did have an identity column.

I said show me. And he did. And was it the same table? It was. And was it the same database? Yes. Are you sure? Of course. Can you show me the connection string? Hang on a second…. ohhhh. (Omitted in this mini-dialogue is  about 15 minutes of head-scratching on both our parts).

Yes, sometimes the facts lie… but only when they’re not facts.

More Geordi Lessons in The Comments

I’ve put a few more of my favorites in the comment section of this post. Do you have any favorite Geordi lessons (or Star Trek lessons in general)?

Powered by WordPress