tabs ↹ over ␣ ␣ ␣ spaces

by Jiří {x2} Činčura

Subselects in computed columns in MS SQL

3 May 2009 1 mins Firebird, 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 … 😃

Profile Picture Jiří Činčura is .NET, C# and Firebird expert. He focuses on data and business layers, language constructs, parallelism, databases and performance. For almost two decades he contributes to open-source, i.e. FirebirdClient. He works as a senior software engineer for Microsoft. Frequent speaker and blogger at