tabs ↹ over ␣ ␣ ␣ spaces

by Jiří {x2} Činčura

Subselects in computed columns in MS SQL

Published 3 May 2009 in Firebird and MS SQL Server

I found nice hack on MS SQL. On Firebird I’m using sometimes computed column(s) with select in definition. It can be performance (or concurrency) problem, but if you use it carefully it’s helpful. And I was missing this feature on MS SQL. But accidentally I found solution/workaround.

You can create a function, that will do the select and return the value you need. The only problem is, that you’re creating dependency between the column and the function.

You can create function for instance:

create function FooBar
  @ID int
returns bit
  declare @result bit;
  if /* some code with (sub)select */
    set @result = 1;
    set @result = 0;
  return @result;

And use it in column for table (it’s just example ;)):

alter table Foo add Bar as (case when dbo.FooBar(ID) = 1 then 'foo' else 'bar' end);

Not nice as in Firebird where you can just use the select, but works. I haven’t measured any performance hit, but sure there will be some slowdown. But if you really need it … :)