Tag: T-SQL

A short piece, as I ran into the need recently to alter a column to NOT NULL status. I’ve rarely done this in the past, usually specifying NOT NULL when I create the table. Often in future changes, I’ve been wary of not allowing NULLs since I’ll always find an application, or worse, a business situation where there is no good value available. However that’s a separate discussion.

Altering the Column

Let’s say I have a column that is specified as NULL in a table, and I want to change that. I initially tried this:

ALTER TABLE Tags ALTER COLUMN Status NOT NULL;

However, I got a syntax error. For the life of me, I couldn’t understand why, so I looked up the syntax. If you look at the ALTER TABLE syntax, it shows that the ALTER COLUMN item needs the type included. While I am not changing the data type, to alter the column, I need to do:

ALTER TABLE Tags ALTER COLUMN Status tinyint NOT NULL;

Another inconsistency in SQL. We don’t provide the whole definition again, and here we need to provide the column definition, even when only changing one of the settings.

I was scanning Twitter the other day and saw a note from someone that they had written a query using an obscure T-SQL command and were glad it had worked. I exchanged a note with the person and they mentioned that they had to look up the command and syntax periodically when they had to write a similar query.

I mentioned templates.

If  you haven’t used these, you should, and I wrote a basic post about how to access them and one on customizing these for yourself. These templates are like Snippets in SQL Prompt (Which are way more useful to me), and they are a tool every DBA should use.

Here’s one way I think they’re really helpful:

Suppose I need to write a PIVOT query. I rarely do this, and it’s not too hard, but I write this query:

select
    *
  from
    ( select
          runner
        , miles
        , mins
        from
          results
    ) as rawdata pivot ( avg(mins) for [miles] in ( [3], [5], [10] ) ) as pivotresults
;
GO

That’s easy enough, but it’s specific for my tables. However when I glance at it, I can see that there’s an aggregate columns, and I know the PIVOT requires that I list the values that are to be used in the columns.

What if I change the query? I can do this:

select
    *
  from
    ( select
          runner
        , <pivotcol, varchar, miles>
        , <aggcol, varchar, mins>
        from
          results
    ) as rawdata pivot ( avg(<aggcol, varchar, mins>) for [<pivotcol, varchar, miles>] in ( [3], [5], [10] ) ) as pivotresults
;
GO

Now if I make this a template:

I can drag this into a new query window. When I see it, I can CTRL+Shift+M and get this:

Now I change a few values and I have a pivot.

Of course, I need to actually enter the values I want, but this gets my PIVOTs done quickly without the need to decode BOL or swing by SQLServerCentral. Once I do that, I have a query I can use.

I’d encourage you to use templates. They’re very, very handy for quick sections of code that you use often, or want to remember in the future.