Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Computed Column Expand / Collapse
Author
Message
Posted Friday, May 28, 2010 5:22 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, May 23, 2013 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
Posted Friday, May 28, 2010 5:48 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 12:01 PM
Points: 12,744, Visits: 31,074
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
Posted Friday, May 28, 2010 6:11 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, May 23, 2013 10:41 PM
Points: 310, Visits: 649
Thanks Lowell.
Post #929609
Posted Saturday, May 29, 2010 6:30 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 5:59 AM
Points: 11,168, Visits: 10,931
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 »

Add to briefcase

Permissions Expand / Collapse