Monday, December 15, 2014

Computed OBJECT_NAME column

I was writing a script to pull a bunch of meta data to dynamically generate some other scripts, and I built a temp table to house various tables and different dynamic sql strings I was planning to use. For whatever reason, I opted to make the PK the object_id and build a computed column to output the name. When I did this, I got an odd result though. Assuming you have AdventureWorks2012 installed (it doesn't need to be that version, you just need some table objects), run the following code:
use adventureworks2012

declare @tables table
    TableId int primary key clustered,
    TableName as object_name(TableID)

insert into @Tables (TableId)
select top 10 object_id
from sys.tables

select TableId, ComputedTableName = TableName, OnTheFlyTableName = object_name(TableId)
from @Tables
You should see something that looks like this: The problem is that when you declare the computed column on @tables, it assumes tempdb as the context for the object_name() function call. So even if you're currently using AdventureWorks2012, that object_name call is always made against tempdb. To fix the issue, you can add a second optional parameter to the object_name() function call with the database you want it to search against.
declare @tables table
    TableId int primary key clustered,
    TableName as object_name(TableID, db_id('AdventureWorks2012'))

Monday, December 8, 2014

Covering Indices

I had a question posed to me where a person was working on a procedure which was running great until he added one new column to the table and query. To start at the end and work my way forwards, the issue was that the new column cause the query to no longer be "covered". What the heck does that mean? In short, it means that if you look up something in a given index, every piece of information SQL Server needs to return that data can be found in the index. To understand how that's accomplished you'll need a little primer on index structure. This is not an in-depth review of indices, but the concepts will be good enough. Take the following table, with some test data put in:
if object_id('tempdb.dbo.#Users') is not null drop table #Users
create table #Users
    UserID int identity(1,1) primary key clustered,
    FirstName varchar(100),
    LastName varchar(100),
    Birthday date

insert into #Users (FirstName, LastName, Birthday)
values ('Gabe', 'Tower', '1986-08-29'),
    ('Abe', 'Lincoln', '1809-02-12'),
    ('Michael', 'Phelps', '1985-06-30'),
    ('Ving', 'Rhames', '1959-05-12')

insert into #Users
select top 100000
    'FirstName_' + cast(newid() as char(36)),
    'LastName_' + cast(newid() as char(36)),
    Birthday = a.create_date
from sys.objects a, sys.objects b, sys.objects c
The clustered index IS the table, sorted by the primary key (in this case, UserID). You can envision this structure pretty intuitively because it look exactly like your data:
Any search on the clustered index is, by definition, a covered query, because the clustered index consists of everything in the table. Take the folowing simple query:
select UserID, FirstName, LastName, Birthday
from #Users
where UserID = 2
Internally, SQL reads through the clustered index till it finds UserID: 2 and it's done.
Now what happens if you want to search by last name (a more realistic example)? Well lets build an index to support that:
create nonclustered index #idx_#Users_LastName_NonCovering on #Users (LastName)
Internally, that index consists of your key column(s) (in this case, just LastName), and a pointer to the row on which it lives. Something like this:

Now let's search this table for the last name, Lincoln, using, for now, only the columns the index covers.
select UserID, LastName
from #Users
where LastName = 'Lincoln'
You get a nice speedy lookup, just as you'd expect. When it finds the LastName "Lincoln" the index literally contains everything needed to satisfy this query, i.e. the LastName (as part of the index key) and the UserID (as part of the Clustered index pointer).
But what happens if I want to return my FirstName when I lookup my last name? The nonclustered index doesn't have this information directly available. It has to lookup this info in the clustered index by means of the clustered index pointer. This is a costly operation and can bring a query to its knees very quickly if you don't know what you're looking for. But lets say I know that most of the time I lookup a last name, I also care about the first name. Wouldn't it be nice not to have to make that additional jump to look up that value in the clustered index?
select UserID, LastName, FirstName
from #Users
where LastName = 'Lincoln'

Enter Include columns. By using the INCLUDE keyword in an index declaration, you can sort of pin additional values on to the index for just this purpose Let's take our original Nonclustered index we added and include FirstName in it. I'll remind you at this point that an index can cover a query by virtue of being part of the KEY of the index and not an included column, but included columns are a much more lightweight way to get at this data if you're not needing to search on those values.
create nonclustered index #idx_#Users_LastName_Covering on #Users (LastName) 
include (FirstName)
This index now looks like the first, but with included values in-line, so it doesn't ever need to go anywhere else, and once again, the search on LastName including FirstName is "covered".

When I first started developing, I found it hard to think about indexes as physical objects, but once I got a handle for what a nonclustered index looked like and how it related to the clustered index, this sort of thing started to make perfect sense to me. I hope this gives you an inkling as to what's going on here when a query isn't covered, and perhaps, more importantly, what the heck your developer friends mean when they use the word.

Saturday, November 22, 2014


This is a cool little feature I found the other day doing some research on a SQL Server Central article. Someone asked if they could essentailly check for syntax errors in dynamically generated maintenance scripts. The first thought I had was to simply wrap each statement in a try/catch and an explicit transaction, but he also said that these were index maintenance statements and it wasn't feasible to be doing all the "heavy lifting" of rebuilding and reorganizing indices just to validate the SQL.

Enter the obscure NOEXEC setting. As the name suggests, when set ON, it will parse/validate sql but not execute it. While this is admittedly a pretty fringe case you'd need this for, it's none the less a very elegant solution to problems like the aforementioned.

set noexec on

alter index IXN__myIndex
    on dbo.myTable

set noexec off

Friday, October 17, 2014

View Default Constraints

A co-worker today couldn't figure out how a column, ostensibly being set to null held a value of 0 instead of null. I looked at the populating code and couldn't find anywhere a null was being replaced with a value of zero, so the most likely candidate was a default constraint on the column. Turned out he wasn't sure how to find default constraints on columns.

The easiest way is to use the sp_help function on the table name, or highlight the table name and hit alt-f1.

exec sp_help myTable

At the bottom, you'll find all the constraints on the table, including default constraints.

If however your table is not part of the dbo schema (as the Aventureworks sample database is fond of doing) you won't be able to use those methods on the table as it assumes the object exists in the 'dbo' schema. Instead, you can access the data via the Dynamic Management Views (DMVs) like this:
    TableName = object_name(c.object_id),
    DefaultConstraintName =,
    ColumnName =,
    DefaultValue = ds.[definition]
from sys.default_constraints ds
inner join sys.columns c
    on ds.parent_object_id = c.object_id
        and ds.parent_column_id = c.column_id

Monday, October 6, 2014

Regular Expressions in SSMS

Several times a year I get asked something like "Can SQL do regular expressions"? The answer is essentially no (I know, I know, CLRs). But while SQL statements can't make sure of regular expressions to search through table data, you CAN use them in the course of creating and editing SQL scripts.

SQL Server Management Studio as well as Visual Studio, which the later versions of SQL Server use as their IDE support regular expressions in find-and-replace operations. If you're wholly unfamiliar with regular expressions, there are tons of resources out there to help you learn. Some good info can be found at I personally bought a book called Introducing Regular Expressions which I found quite useful. It's not my intent to teach regular expression here, but to show you how to find a regex character you need and give you some find-and-replace regular expressions I use almost daily.

First things first, SQL Server uses some funky notation for some of its special characters. I found it particularly difficult to find this page on Google as well, but this is, as close to I can find, the definitive source describing the regex syntax used in SSMS and VS

Search Text With Regular Expressions

Commonly used expressions

Again, I don't want to give a comprehensive overview of regular expressions in SQL, but show you some of the tricks I use a lot which can save a lot of time in editing procedures. 
  • :a (alphanumeric)
    • I probably use :a more than anything else. It matches any alphanumeric character which is great of SQL objects that use camel case. Add a + or * to the end of it to quickly find a list of column names.
    • Example:
      • :a+
      • Matches any string of 1 to n alphanumeric characters
  • :d (digit)
    • To be honest I rarely use this one, but it's another simple useful one to identify any digit between 0 and 9. Again, you can modify it with a + or * to find strings of numbers.
    • Example
      • :d*
      • Matches any string of 0 to n digits
  • . (Any character)
    • As the description suggests, a period "." will match any single character; be it white space, a number a letter or so forth. I don't think it will capture line breaks.
  • [] (character set)
    • Another one I use all the time. Those of you who read my comment about easily identifying object names may have pointed out that object names can contain underscores, variables are declared with an @ sign, and temp tables are declared with a # sign. Build a character set inside brackets to allow what you need.
    • Note that you CAN use special characters in your character sets such as :a and :d
    • Example
      • [:d@]
      • Matches the @ sign or any digit
  • {} (Tagged Expression)
    • The syntax for this is, from what I hear, a little different from some other regex dialects. I don't know how many you can have nested in a single regex, but I've never had to use more than 2 or 3 at a time.
    • How these work in find-and-replace is each tagged expression maps to a number which can be accessed in the "replace" bar by a slash "\" and the number of the tagged expression.
    • To figure out what the number of the expression is, start counting from the left-most open curly bracket in ward (starting at 1). 
    • Hello {{\@}:a+}
      • \2 matches the @ character
      • \1 matches the @ character AND a string of one or more alpha numeric characters
    • \0 always represents the entire regular expression
  • \ (Escape)
    • Always a good one to know. slash "\" escapes a special character
    • Example:
      • \@


Here are some example regular expression similar (if not verbatim) to what I use quite often.I don't show it in my screenshots, but generally, you'll want to select "use selection" from the "look in" dropdown or you will apply it to the whole script. Also, check the box under "find options" that says "Use:" and then select "Regular Expressions" from the dropdown.
  • Select variables as columns
  • Injecting a string into a set of column names
  • Aliasing columns
  •  Make a serialized string easier to read

While this isn't a comprehensive list of what you can do with regular expressions and find-and-replace, hopefully it gives you some good ideas of ways to speed up your work. Combined with the Square Marquee  I talked about in a previous post, you can do some pretty creative things to speed up editing and creation of stored procedures.

If you have regular expression you use regularly (get it?) or any questions, please feel free to leave me a comment below.

Friday, October 3, 2014

Square marquee

For those of you who may not have known, there’s a cool trick you can do in Visual Studio or SSMS; the alt-drag marquee. Many of these functions can be handled with fine and replace with or without regular expressions, but sometimes those are more trouble than it’s worth.

The basics of alt-drag are this. Say I have a block of text, such as a comment block of a procedure which I want to replace all at once. Hold alt and drag to the bottom right, and you get a marquee area which respects x/y coordinates rather than just lines and indentation.

Now the cool part. If you’ve ever used copy-paste with these marquees, you’ll find they work… oddly. However like any good Jedi, you can turn your enemies weakness into your strength.

Say for example, I forgot to type select statements for a derived table like this:

You could use find replace to replace “all” with “all select’, you could use a series of copy/paste commands and down arrow keys, or this:

Create a zero-width vertical marquee and start typing. Every text line which is crossed by the marquee will begin typing there (it works with ctrl-v_ as well.

Note, you can also hold alt+shift and use the arrow keys to make a square marquee as well.