Hi jghali,

First, thanks for contributing a question, in spite of the harsh comments so often targeted at QotD contributers. Please submit more in the future!

On your (rhetoric?) question: "Can you imagine, if you are calling 100 000 rows including a computed column? It would definitely take more resources (...)" (and I omitted the last part if this quote on purpose) - I have to disagree. SQL Server performance is almost always I/O bound; the CPU spends enormous amounts of time waiting for the next data to be available. Since a computed column is not stored, it takes less disk space, reducing I/O. Here is a very unlogical and exaggerated example:

CREATE TABLE Demo

(KeyCol int NOT NULL PRIMARY KEY,

SmallString varchar(10) NOT NULL,

Repetitions smallint NOT NULL,

LongString AS REPLICATE(SmallString, Repetitions));

If the LongString column is computed and not persisted, rows take approximately 20-30 bytes (I don't have the exact numbers, as I'm on holiday); a single data page (8K) will store approximately 300 rows. The 100,000 rows you mention will be on less than 350 data pages.

However, if LongString is a persisted computed column or a regular column, then the amount of bytes per row depends on the length of SmallString and the value of Repetitions. If we assume an average length of 5 bytes for SmallString and an average value of 200 Repetitions, the average length of LongString will be 1,000 bytes; the average row length then is 1,020-1,030 bytes, so we can squeeze only 7 or 8 rows in a data page. The same 100,000 rows now take over 13,000 data pages! This will take much longer for SQL Server to read and process.

The last part of your post, the part I previously omitted from my quote, is "specially if the computed column comes from other tables via UDFs...". This is indeed true. SQL Server can't optimize this very well; it will execute the UDF 100,000 times, so if the UDF has to read from another table, that read will be repeated 100,000 times. Extremely bad for performance. This is but one of the many reasons why using a UDF in a computed column is not recommended.