Computed Column

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » T-SQL (SS2K8) » Computed Column

Computed Column

Rate Topic

Display Mode

Topic Options

Author

Message

luckysql.kinda

Posted Friday, May 28, 2010 5:22 AM

Old Hand

Group: General Forum Members
Last Login: Yesterday @ 10:41 PM
Points: 310, Visits: 649

Hi,

I am creating a table EmployeeResult. I want to include computed column which will be 1 if EmpAns = RealAns and will be 0 if not.

CREATE TABLE EmployeeResult
(ERId INT IDENTITY(1,1),
QuestionNo TINYINT,
EmpAns CHAR,
RealAns CHAR,
Comparison AS CASE EmpAns, RealAns
WHEN EmpAns = RealAns THEN 1
ELSE 0
END
PERSISTED)

Post #929583

Lowell

Posted Friday, May 28, 2010 5:48 AM

SSChampion

Group: General Forum Members
Last Login: Today @ 1:40 PM
Points: 11,647, Visits: 27,753

you had it almost perfect, just needed to remove the stuff between CASE and WHEN

CREATE TABLE EmployeeResult
(ERId INT IDENTITY(1,1),
QuestionNo TINYINT,
EmpAns CHAR,
RealAns CHAR,
Comparison AS 
  CASE 
    WHEN EmpAns = RealAns THEN 1
    ELSE 0
  END
PERSISTED)

Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son

Post #929596

luckysql.kinda

Posted Friday, May 28, 2010 6:11 AM

Old Hand

Group: General Forum Members
Last Login: Yesterday @ 10:41 PM
Points: 310, Visits: 649

Thanks Lowell.

Post #929609

Paul White

Posted Saturday, May 29, 2010 6:30 AM

SSChampion

Group: General Forum Members
Last Login: Today @ 1:49 AM
Points: 10,990, Visits: 10,540

Side note:

It's not often worth persisted expressions like this - this optimiser very frequently chooses to recalculate the value from the base columns instead - particularly if the computed value is part of a useful index, and the base columns are.

Not a huge consideration here, given the size of the persisted data and the complexity of the expression.

For complex or otherwise expensive expressions, consider indexing the computed column without persisting it (or adding it as an INCLUDEd column on an existing index). It surprises some people that you can (very often) index a non-persisted computed column, but it is true.

Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi

Post #930033

« Prev Topic | Next Topic »

Permissions