Category: T-SQL

Gail Shaw shows how to build gapless sequential values:

To be clear, I don’t think this is a good idea. The identity column works well if a sequential series of numbers are needed. If the number sequence needs to that spans tables, then the sequence object is a good replacement.

But, there will always be some requirements that insist on gap-less sequences, or insist on not using identity (probably for ‘compatibility’ reasons), so let’s see how to do it properly.

For true surrogate keys, this is a bad idea because it is both unnecessary and overkill.  Where this becomes useful is cases where an auditor is expecting a proper sequence without any gaps, such as invoice numbers, check numbers, or purchase order numbers.  As Gail mentions, those are uncommon scenarios.

Alan Yu announces an update to mssql-cli:

Special commands are meant to make your life easier. They are shortcuts to perform common tasks and queries. All special commands start with a backslash (\), and you can use the built-in IntelliSense to see a list of special commands you can use. You can also learn more by running the following command:

$ mssql-cli>\?

To start off, let’s say you have a common query you run often, but don’t want to type it out each time or scroll through your history. You can “save” your query by running the following special command:

$ mssql-cli>\sn address select * from "Person"."AddressType"

To execute this query, simply run:

$ mssql-cli>\n address

Click through to learn more and see it in action.

Vitaly Bruk explains what schemabinding is and why we sometimes need WITH SCHEMABINDING in our code:

In SQL Server, when we use the “WITH SCHEMABINDING” clause in the definition of an object (view or function), we bind the object to the schema of all the underlying tables and views. This means that the underlying tables and views cannot be modified in a way that would affect the definition of the schema-bound object. It also means that the underlying objects cannot be dropped. We can still modify those tables or views, as longs as we don’t affect the definition of the schema-bound object (view or function).

If we reference a view or function in a schema-bound object (view or function), then the underlying view or function must also be schema-bound. And we cannot change the collation of a database, if it contains schema-bound objects.

I’ve only used schemabinding when mandated (e.g., using row-level security or creating an indexed view), but I can see the value behind using it with normal development.

Louis Davidson has a couple of ways of tracking progress and success of T-SQL code:

For a process that runs for 10 seconds, this is no problem at all. But when you are doing 100s of loops, and they take time, you don’t want to wait. Eventually, data will start spurting out, but not immediately. We want immediately, even if it isn’t the optimum way.

The PRINT statement won’t output immediately, but its cousin, good old RAISERROR, does. Using a severity of 10 for the message, the message will be output just like a PRINT message. Then, adding WITH NOWAIT to the RAISERROR, the messages will no longer be queued for output, and will be returned immediately.

Both of the techniques Louis shares are useful for keeping track of progress in code.  I’d expect that as tooling gets more sophisticated with respect to live execution plans, we might eventually get to the point where there’s an overall expected progress indicator, something which would be quite useful when three levels of management are standing at your desk waiting for something to finish.

Drew Furgiuele hates the pernicious effect of graphical user interfaces and wants to return to nobler times:

Okay, fine, who’s this really for?

Anyone, I suppose, but in reality? If you’re stuck on a remote system that only allows terminal logins, this is a super handy tool to use. If you’re used to text-based editors, like Emacs, Nano, or even vi or vim, you’ll feel right at home in here. You can type in commands and then run queries. There’s even multi-line support for more complex stuff. There’s another side to this too: if you’re the kind of DBA that gets really mad if someone installs management studio on a server, then this might be a solution: it’s got a very small installation footprint, and you don’t get a full UI experience so unless you know all the big T-SQL commands for heavy administration (like for, say, dealing with an availability group or adding permissions, or taking or restoring backups), you won’t get much use out of it. But in a pinch, on a server, in a crisis where you can’t tell if SQL is up and serving queries? It just might work out well for you.

Read the whole thing for an enlightening Q&A session, although the question-asker is kind of a jerk to the answerer.

Chris Hyde has started a new series on palindromes in T-SQL:

Immediately I realized that this algorithm will need to accomplish two different things.  I first need to remove all non-alphabetic characters from the string I am testing, because while “able was I ere I saw Elba” is palindromic even leaving the spaces intact, this will not work for other well-known palindromes such as “A man, a plan, a canal, Panama!”  Then the second task is to check that the remaining string is the same front-to-back as it is back-to-front.

With the help of Elder’s Dead Roots Stirring album I set out to find the most efficient T-SQL code to accomplish this task.  My plan was to avoid resorting to Google for the answer, but perhaps in a future post I will go back and compare my solution to the best one I can find online.  For this first post in the series I will tackle only the first task of removing the non-alphabetic characters from the string.

Read on to see how Chris takes on this task.

Bill Fellows has a query which gets input parameters for table-valued functions:

In my ever expanding quest for getting all the metadata, I how could I determine the metadata for all my table valued functions? No problem, that’s what sys.dm_exec_describe_first_result_set is for. SELECT * FROM sys.dm_exec_describe_first_result_set(N'SELECT * FROM dbo.foo(@xmlMessage)', N'@xmlMessage nvarchar(max)', 1) AS DEDFRS

Except, I need to know parameters. And I need to know parameter types. And order. Fortunately, sys.parameters and sys.types makes this easy. The only ugliness comes from the double invocation of row rollups

Click through for the script.

Andy Mallon has written a T-SQL function to parse file paths from strings:

Writing & reading code is easier if you understand the logic before attacking the code. I find this to be particularly important when you anticipate complicated code. SQL Server sucks at parsing strings, so I anticipate complicated code.

How do you identify the directory from a file path? That’s just everything up to the last slash–and I like to include that final slash to make it clear it’s a directory.

How do you identify the file name from a file path? That’s just everything after the final slash.

The key here is going to be identifying that final slash, and grabbing the text on either side.

Read on for the function.