February, 2017 | Michael J. Swart

February 22, 2017

A Program to Find INSERT Statements That Don’t Specify Columns

Filed under: Miscelleaneous SQL,SQLServerPedia Syndication,Technical Articles — Michael J. Swart @ 8:00 am

I’ve got a program here that finds SQL in procedures with missing column specifications.

Update 10/26/2018: See comments for a quick TSQL-only solution

Table Columns Are Ordered

Even though they shouldn’t be.

Unlike mathematical relations, SQL tables have ordered columns, but please don’t depend on it.
In other words, try to treat these tables as the same because it’s super-awkward to turn one into the other:

CREATE TABLE PEOPLE
(
    LastName varchar(200),
    FirstName varchar(200)
)

CREATE TABLE PEOPLE
(
    FirstName varchar(200),
    LastName varchar(200)
)

Don’t Omit Column Specification

And don’t forget to specify the columns in your INSERT statement. No excuses.

You’re depending on the column ordering if you write INSERT statements like this:

INSERT PEOPLE /* no column spec */
VALUES ('Rob', 'Farley'),
       ('Angela', 'Henry'),
       ('Andy', 'Leonard'),
       ('Richard', 'Douglas'),
       ('David', 'Maxwell'),
       ('Aaron', 'Nelson'),
       ('Paul', 'Randal'),
       ('Buck', 'Woody');

We recently got burned by something like this 🙁

Find Missing Column Specifications

Thomas LaRock recently encouraged DBAs to branch out horizontally. In that spirit, don’t be too afraid of the C#. I’ve got a program here that finds procedures with missing column specifications.

If for some reason, you don’t care about enforcing this rule for temp tables and table variables, then uncomment the line // visitor.TolerateTempTables = true;
It uses ScriptDom which you can get from Microsoft as a nuget package.
The performance is terrible in Visual Studio because ScriptDom uses Antlr which uses exceptions for flow control and this leads to lots of “first chance exceptions” which slows down debugging. Outside of Visual Studio, it’s just fine.

using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using Microsoft.SqlServer.TransactSql.ScriptDom;
 
class Program {
 
    static void Main(string[] args) {
 
        SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder {
            DataSource = ".",
            InitialCatalog = "test_database",
            IntegratedSecurity = true
        };
 
        using (SqlConnection conn = new SqlConnection(builder.ToString())) {
            conn.Open();
            SqlCommand command = new SqlCommand(@"
                SELECT OBJECT_SCHEMA_NAME(object_id) [schema], 
                       OBJECT_NAME(object_id)        [procedure], 
                       OBJECT_DEFINITION(object_id)  [sql]
                  FROM sys.procedures 
                 ORDER BY OBJECT_SCHEMA_NAME(object_id), OBJECT_NAME(object_id) ;", conn);
            SqlDataReader reader = command.ExecuteReader();
            while (reader.Read()) {
                string schema = reader["schema"].ToString();
                string procedure = reader["procedure"].ToString();
                string sql = reader["sql"].ToString();
                if (SqlHasInsertWithoutColumnList(sql)) {
                    Console.WriteLine( $"{schema}.{procedure}" );
                }
            }
        }            
    }
 
    static bool SqlHasInsertWithoutColumnList(string SQL) {
        SQLVisitor visitor = new SQLVisitor();
        // visitor.TolerateTempTables = true;
        TSql130Parser parser = new TSql130Parser(true);
        IList<ParseError> errors;
        var fragment = parser.Parse(new System.IO.StringReader(SQL), out errors);
        fragment.Accept(visitor);
        return visitor.HasInsertWithoutColumnSpecification;
    }
}
 
internal class SQLVisitor : TSqlFragmentVisitor {
    public bool HasInsertWithoutColumnSpecification { get; set; }
    public bool TolerateTempTables { get; set; }
 
    public override void ExplicitVisit(InsertStatement node) {
        if (node.InsertSpecification.Columns.Any())
            return;
 
        var source = node.InsertSpecification.InsertSource as ValuesInsertSource;
        if (source != null && source.IsDefaultValues)
            return;
 
        if (TolerateTempTables) {
            var target = node.InsertSpecification.Target as NamedTableReference;
            if (target != null && !target.SchemaObject.BaseIdentifier.Value.StartsWith("#")) {
                HasInsertWithoutColumnSpecification = true;
            }
        } else {
            HasInsertWithoutColumnSpecification = true;
        }
    }
}

using System; using System.Collections.Generic; using System.Data.SqlClient; using System.Linq; using Microsoft.SqlServer.TransactSql.ScriptDom; class Program { static void Main(string[] args) { SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder { DataSource = ".", InitialCatalog = "test_database", IntegratedSecurity = true }; using (SqlConnection conn = new SqlConnection(builder.ToString())) { conn.Open(); SqlCommand command = new SqlCommand(@" SELECT OBJECT_SCHEMA_NAME(object_id) [schema], OBJECT_NAME(object_id) [procedure], OBJECT_DEFINITION(object_id) [sql] FROM sys.procedures ORDER BY OBJECT_SCHEMA_NAME(object_id), OBJECT_NAME(object_id) ;", conn); SqlDataReader reader = command.ExecuteReader(); while (reader.Read()) { string schema = reader["schema"].ToString(); string procedure = reader["procedure"].ToString(); string sql = reader["sql"].ToString(); if (SqlHasInsertWithoutColumnList(sql)) { Console.WriteLine( $"{schema}.{procedure}" ); } } } } static bool SqlHasInsertWithoutColumnList(string SQL) { SQLVisitor visitor = new SQLVisitor(); // visitor.TolerateTempTables = true; TSql130Parser parser = new TSql130Parser(true); IList<ParseError> errors; var fragment = parser.Parse(new System.IO.StringReader(SQL), out errors); fragment.Accept(visitor); return visitor.HasInsertWithoutColumnSpecification; } } internal class SQLVisitor : TSqlFragmentVisitor { public bool HasInsertWithoutColumnSpecification { get; set; } public bool TolerateTempTables { get; set; } public override void ExplicitVisit(InsertStatement node) { if (node.InsertSpecification.Columns.Any()) return; var source = node.InsertSpecification.InsertSource as ValuesInsertSource; if (source != null && source.IsDefaultValues) return; if (TolerateTempTables) { var target = node.InsertSpecification.Target as NamedTableReference; if (target != null && !target.SchemaObject.BaseIdentifier.Value.StartsWith("#")) { HasInsertWithoutColumnSpecification = true; } } else { HasInsertWithoutColumnSpecification = true; } } }

In my environment, I found twelve examples which I’ll be fixing soon.

-- Comments (6)

February 14, 2017

Generate Permutations Fast using SQL

Filed under: Miscelleaneous SQL — Michael J. Swart @ 9:49 am

If you google “generating permutations using SQL”, you get thousands of hits. It’s an interesting problem if not very useful.
I wrote a solution recently and thought I’d share it. If you’re keen, try tackling it yourself before moving on.

My Solution

Notice the use of recursive CTEs as well as bitmasks and the exclusive or operator (^).

with Letters as 
( 
  select letter 
    from ( values ('a'), ('b'), ('c'), ('d'), ('e'), ('f'), ('g'), ('h'), ('i') ) l(letter) 
),
Bitmasks as 
( 
  select cast(letter as varchar(max)) as letter, 
         cast(power(2, row_number() over (order by letter) - 1) as int) as bitmask 
    from Letters 
),
Permutations as
(
  select letter as permutation,
         bitmask
    from Bitmasks
 
  union all
 
  select p.permutation + b.letter,
         p.bitmask ^ b.bitmask
    from Permutations p
    join Bitmasks b
         on p.bitmask ^ b.bitmask > p.bitmask
)
select permutation
  from Permutations
 where bitmask = power(2, (select count(*) from Letters)) - 1

362880 rows (9!) in less than ten seconds. Let me know what you come up with.

-- Comments (3)

February 10, 2017

What Does “monitorLoop” mean in the Blocked Process Report

Filed under: SQLServerPedia Syndication — Michael J. Swart @ 9:11 am

Some trivia for your Friday. I recently got a question asking about what the attribute “monitorLoop” is in the blocked-process-report because it’s not really documented.

You won’t find too much explanation about that field in the official documentation but I believe I know what it means.

The blocked process report is closely tied to deadlock detection and it’s generated by the same process as the deadlock monitor. If you remember, the deadlock monitor runs frequently looking for deadlocks (which are just blocking chains in a circle). It runs every couple seconds when there are no deadlocks, and if it detects any, it runs a bit more frequently. Each time it runs it’s called a monitor loop. The monitorLoop is just a number that starts at zero when the server restarts and increments by one each time the monitor runs.

For me, when processing a collection of blocked process reports, it’s useful way to group them together so that if several blocked process reports have the same monitor loop, then they were detected at the same time and can be part of the same blocking chain.

This attribute wasn’t always there. There are some old versions of SQL Server that don’t provide the monitorLoop attribute. This is a reminder that the schema of the blocked process report can change (has changed and will change) without notice.

-- Comments (1)