Why does OBJECT_ID return NULL in a computed column?

Question

I'm trying to produce a usage report for some stored procedures in my database. The view view sys.dm_exec_procedure_stats contains some of what I need, so I decided to start here.

SQL Server keys procedure metadata by object ID. I know only the procedure names, so first I have to map the names to IDs using the OBJECT_ID function.

There are several procedures to check, so I want to put their names in a temp table before processing them.

I thought it would be cute to get the object id from a computed column in the temp table, so I created a table like this:

USE msdb;
GO
CREATE TABLE #procs (
 name SYSNAME,
 [object_id] AS OBJECT_ID(name)
);
INSERT INTO #procs (name) VALUES ('dbo.sp_send_dbmail');
INSERT INTO #procs (name) VALUES ('dbo.sp_add_job');
INSERT INTO #procs (name) VALUES ('dbo.sp_start_job');

With that table in place, the obvious way to get the execution counts looked like this:

SELECT #procs.name, execution_count
FROM sys.dm_exec_procedure_stats AS procstats
INNER JOIN #procs ON #procs.[object_id] = procstats.[object_id]
ORDER BY execution_count DESC;

But it returns an empty result set!

This query shows why the result set is empty:

SELECT name, [object_id]
FROM #procs;

The object_id column is NULL, so an inner join will produce no rows:

name object_id
-------------------- -----------
dbo.sp_send_dbmail NULL
dbo.sp_add_job NULL
dbo.sp_start_job NULL

It's not that computed columns are broken for temp tables. If I add a computed column to reverse the name, it produces the correct value when I select from it:

ALTER TABLE #procs ADD reverse_name AS REVERSE(name);
SELECT name, [object_id], reverse_name
FROM #procs;

Result:

name object_id reverse_name
-------------------- ----------- ---------------------
dbo.sp_send_dbmail NULL liambd_dnes_ps.obd
dbo.sp_add_job NULL boj_dda_ps.obd
dbo.sp_start_job NULL boj_trats_ps.obd

Why does OBJECT_ID return NULL here?

• Kin Shah: you are doing it incorrectly. From BOL (technet.microsoft.com/en-us/library/ms186301.aspx) By default, the SQL Server Database Engine assumes that object_id is in the context of the current database. A query that references an object_id in another database returns NULL or incorrect results.
• Aaron Bertrand: @Kin that's true, but since he issues a USE command, it might not be altogether intuitive from the docs why the "current database context" is not msdb.

Answer 1

A metadata function mentioned in a computed column in a #temp table is going to reference tempdb.sys.objects, not yourdatabase.sys.objects, since the table is created in tempdb. Try adding the database prefix to the insert, and that will make OBJECT_ID go find it in the right place:

CREATE TABLE #procs (
 name SYSNAME,
 [object_id] AS OBJECT_ID(name)
);
INSERT INTO #procs (name) VALUES ('dbo.sp_send_dbmail');
INSERT INTO #procs (name) VALUES ('msdb.dbo.sp_send_dbmail');
SELECT name, [object_id] FROM #procs;

Results:

dbo.sp_send_dbmail NULL
msdb.dbo.sp_send_dbmail 283148054

• Iain Samuel McLean Elder: Thanks, this solved my problem. but I think in the end I would avoid using computed columns entirely. See my comment on Remus' answer.
• Aaron Bertrand: @IainElder whether you use a computed column or not doesn't matter, if you're not in msdb when you insert the value, if you try to derive object_ID() at that time, it will produce NULL for the same reasons. Another important thing to note is that, for user objects, if you store the object_id(), it could change (drop/create).

Answer 2

You are doing overly overly overly complicated something trivial. Remove the computed column, insert OBJECT_ID explicitly.

What happens is that you're lossing control over where is OBJECT_ID evaluated (in which database context). OBJECT_ID will return different values, for same name, in different DBS, or may return NULL for names that do not match current execution context (db). By adding the evaluation onto a #temp table you can't be sure where does the evaluation occur. Is it in tempdb? in msdb? In current db? Well, it looks like is not where you expected it to be, that's sure...

Ditch the fancy cleverness, stick to KISS.

Next, fix your `JOIN #procs ON #procs.[object_id] = procstats.[object_id], as is incorrect. You need to join on object_id and database_id.

• Iain Samuel McLean Elder: I've gone right off computed columns. In my experience, they seem to encourage confusing and unmaintainable code. Next time, I would just insert the values directly, as you say.