2

I came across a table like this:

CREATE TABLE TABLE1
(
 CD1    int
,CD2    varchar(16)
,CD3    varchar(21)
,CD4    decimal(14,0)
,CD5    varchar(4)
,CD6    decimal(18,2)
,CD7    AS (left([CD3],(4)))
)

This table has more than 4 BILLION rows (totally unnecessary, but this is another topic).

As you can see in the last column, they use AS (left([CD3],(4))). I think this is pretty useless, since we almost NEVER have a SELECT on this table, and here is just using space.

Isn't it better to select that field during the select when needed?

8

If the computed column isn't persisted or indexed, it's only a virtual column (expression), and it's not taking up any "space".

4

The only reason I can think of for having that column is to index it, because there is some common set of read queries that filter on the first few characters in CD3.

If the computed column is, indeed, indexed, then the it's valuable because:

  • it would required less storage than the full 21 char column
  • it would incur less page reads for seeks and scans than an index on the full 21 char column

You mentioned the table is rarely read, but I would check for indexes that use this computed column to be sure.

If it's not indexed, then see Erik's answer about the storage aspect of non-persisted computed columns.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.