Showing posts from December, 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 go 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…

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'…