Voice of the DBA

Tag: T-SQL

  • Real World T-SQL Tricks

    I’ve been playing more with T-SQL this year, doing some testing and development in various places. I’ve been trying to improve my skills, and keeping up with the advancements the language has made in the last few versions. Like many of you, if I haven’t had the need for a particular feature, or the chance to implement it, I haven’t done much with it.

    I think that’s the case with many people, who may not catch all the changes in T-SQL with new versions, especially if you upgrade after a year or two and don’t often do more than refactor the existing code you have in production. In the spirit of improving skills, and also learning more about how others work in this profession, I want to ask you:

    Have you used any of these T-SQL items in production code?

    • APPLY
    • THROW
    • LAG/LEAD
    • a tally table
    • symmetric or asymmetric keys
    • TRY..CONVERT
    • CONCAT/FORMAT
    • IIF
    • SEQUENCE

    Some of these have been around for a long time, some are new in SQL Server 2012, but they are all improvements beyond the core T-SQL language that I see so many people posting and asking questions about.

    Let us know this week if you’ve used any of these constructs, and if you can, how they solve a problem that you struggled with in the past, or maybe how these new features have improved performance or sped up code development.

    Steve Jones

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( 2.0MB) podcast or subscribe to the feed at iTunes and LibSyn.

  • T-SQL Tricks – Custom Templates

    I wrote about the Template Explorer, which comes with the SQL Server tools and is visible in Management Studio (SSMS). It’s handy, but there are limited code items in there. What if I want more?

    That’s easy. Suppose I decide that I often need to create procedures with the EXECUTE AS clause. I usually do this:

    CREATE PROCEDURE MyProc
   @id INT
WITH EXECUTE AS OWNER
AS
BEGIN

-- do work
BEGIN TRY

  COMMIT
END TRY
BEGIN CATCH
  ROLLBACK
  EXEC uspErrorHandler;
END CATCH

END

    It’s a basic template of stuff I do. Let’s stick this in our Template Explorer.

    The first thing I do is go to the Stored Procedure folder. I can right click it and I’ll see this:

    templates12

    I choose template and a new one is created. I enter a name and I have a template. The first 6 templates here are defaults. The last one, highlighted below, is the one I created.

    templates13

    Now I right click it again and select Edit. At this point, it will open in a query window. This is just a file in my file system (under ), and like any other query, I can edit it. I paste in my script from above, and change a few items to parameters.

    templates14

    Now I can save this, and the next time I need this, just drag it into the main window and customize it.

    templates15

  • T-SQL Tricks – Templates in SSMS

    There are lots of little tricks that you can use to become more proficient, and efficient, at writing T-SQL. Here’s a short one that might be more for administrators, but I think for developers it’s very handy as well.

    SQL Server tools have included templates for years. I first started using them in Query Analyzer with SQL Server 2000. The tools have changed, but we still have templates in Management Studio (SSMS). We can access the templates by exposing the Template Browser from the View menu (as shown below).

    templates1

    We can also use the CTRL+ALT+T shortcut. Either of these will give us a tab on the right side of our SSMS main window, opposite the Object Explorer, as shown below.

    templates2

    Like all the windows in SSMS, I can detach this, move it, auto hide, etc.

    Each of these folders contains a series of default scripts that are installed with the tools. For example, I can look at the Backup folder and see three scripts in there. I can grab one of them with a left click and "drag" it to the query window and the script will appear in the query window as I’ve shown here.

    templates3

    A CTRL+Shift+M will give me the parameters in the script, which I can replace with my own values (or accept the defaults).

    templates4

    Many of these are generic, but they are useful. I’d encourage you to take a look at them and use them when you can.

    You can customize these, but that’s for another post.

  • Quick Schema Auditing

    I was working on a demo recently and needed to show that a little monitoring can help you catch schema changes. At first I looked at SQL Audit and DDL Triggers, but then I ran across a short custom metric on the SQL Monitor Metrics site that my company, Red Gate Software, put up to help people share their custom monitoring metrics and alerts.

    The metric is called Schema Modified, and it uses a really simple query. This is all is does:

    SELECT DATEDIFF(ss, '1970', MAX([modify_date]))
 FROM [sys].[objects];

    It calls this query every minute for each database on which you have it enabled. This gives you a count of the number of seconds between 1970 and the latest schema modification in your database.

    Now that’s not terribly useful, but if you look for changes in this metric, then it becomes interesting. For example, in one of my tests, I got this value

    1413904788

    If I continued to run the query,  the same value was returned if nothing changed in the database. However once I added a new object, then the value changed to

    1413905295

    That’s an increase, and my alerting was looking for changes in the value, so when this new count of seconds appeared, an alert was raised.

    Using the Information

    What good does it do you to know that something changed? Admittedly, this may or may not be useful. This doesn’t tell you what changed, and certainly help you determine who changed things.

    However, in more than a few of my development jobs, we knew people would change things. That wasn’t the issue. Really we wanted to know that something changed, and if so, we would investigate further. Often we could easily determine who made the change, based on what it was.

    This is really a trigger more for something like production, where I don’t expect changes, except when I deploy things. Any other change is cause for concern, and I might have alerts set to ping people when there’s a change. If we’re making the change, then we ignore the alert, because we’re aware of it.

    If we aren’t deploying changes, then we start investigating immediately.