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 ««12

Computed column with/with out PERSISTED Expand / Collapse
Author
Message
Posted Thursday, January 24, 2013 11:11 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 2:35 PM
Points: 12,962, Visits: 32,498
i thought it was clear: i read it as howard saying there is no need to mark an calculated column as persisted in order to index it...it's not a requirement.

simple example:
--drop table products
CREATE TABLE Products(
id INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
pname VARCHAR(30),
CostPerItem MONEY,
ItemsPerCase INT,
ValuePerCase AS CostPerItem * ItemsPerCase
)

INSERT INTO Products
SELECT 'Bananas',.49,40 UNION ALL
SELECT 'Apples',.49,36 UNION ALL
SELECT 'Pineapples',3.49,5

SELECT * FROM Products where ValuePerCase > 18

CREATE INDEX IX_Product_ValuePerCase ON Products(ValuePerCase)

SELECT * FROM Products where ValuePerCase > 18



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 #1411271
Posted Thursday, January 24, 2013 4:35 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 3:26 PM
Points: 2,330, Visits: 3,509
Lowell (1/24/2013)
i thought it was clear: i read it as howard saying there is no need to mark an calculated column as persisted in order to index it...it's not a requirement.

simple example:
--drop table products
CREATE TABLE Products(
id INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
pname VARCHAR(30),
CostPerItem MONEY,
ItemsPerCase INT,
ValuePerCase AS CostPerItem * ItemsPerCase
)

INSERT INTO Products
SELECT 'Bananas',.49,40 UNION ALL
SELECT 'Apples',.49,36 UNION ALL
SELECT 'Pineapples',3.49,5

SELECT * FROM Products where ValuePerCase > 18

CREATE INDEX IX_Product_ValuePerCase ON Products(ValuePerCase)

SELECT * FROM Products where ValuePerCase > 18




Seems an odd way to read it in context here, since the OP stated in his original post that he could index a computed column w/o explicitly persisting it. I guess I just didn't understand why that needed to be restated separately.


SQL DBA,SQL Server MVP('07, '08, '09)

Carl Sagan said: "There is no such thing as a dumb question." Sagan obviously never watched a congressional hearing!
Post #1411398
Posted Thursday, January 24, 2013 4:47 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 3:26 PM
Points: 2,330, Visits: 3,509
SQL* (1/23/2013)
What is the need of computed column if we can do the same thing in the query/application?


The two big advantages of computed columns are:
1) a consistent definition;
2) defined in only one place.

1) If you define a computed column in the table, you know that all queries are using the same computation to generate the new value. If you specify the computation itself in different queries and views, some of them could be different from each other, possibly even to the extent of making the value incorrect.

2) Say you need to change/update the computation. With a computed column, you simply drop the old definition and add the new one -- all queries automatically now refer to the new definition. If you've done the computation in multiple queries/views, you must find them all and change them -- NOT fun.


For example:

ALTER TABLE dbo.tablename ADD 
full_name AS
first_name +
ISNULL(' ' + middle_name, '') +
last_name

--requirement comes in to add a possible "salutation" to full_name
ALTER TABLE dbo.tablename
DROP COLUMN full_name
ALTER TABLE dbo.tablename ADD
full_name AS
ISNULL(salutation + ' ', '') +
first_name +
ISNULL(' ' + middle_name, '') +
last_name


Voila: every use of full_name from then automatically includes the required "salutation" with no other coding changes required.






SQL DBA,SQL Server MVP('07, '08, '09)

Carl Sagan said: "There is no such thing as a dumb question." Sagan obviously never watched a congressional hearing!
Post #1411405
Posted Friday, January 25, 2013 2:04 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, November 3, 2014 8:05 AM
Points: 1,191, Visits: 9,892
ScottPletcher (1/24/2013)

Seems an odd way to read it in context here, since the OP stated in his original post that he could index a computed column w/o explicitly persisting it. I guess I just didn't understand why that needed to be restated separately.


I was addressing the statement the OP quoted from the MS documentation that, out of context, suggested that you were required to mark a column as persisted before indexing it.
Post #1411533
Posted Sunday, January 27, 2013 11:16 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, December 11, 2014 2:19 AM
Points: 415, Visits: 1,605
HowardW (1/25/2013)
ScottPletcher (1/24/2013)

Seems an odd way to read it in context here, since the OP stated in his original post that he could index a computed column w/o explicitly persisting it. I guess I just didn't understand why that needed to be restated separately.


I was addressing the statement the OP quoted from the MS documentation that, out of context, suggested that you were required to mark a column as persisted before indexing it.


Is there any specific technical reason for this suggestion. As we are able to create index without making the computed column as PERSIST. Only the difference is the execution plan for the both is different, there is extra computed column icon in execution plan for without PERSIST case.


Post #1412188
Posted Monday, January 28, 2013 12:07 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: 2 days ago @ 7:27 AM
Points: 35,769, Visits: 32,437
My personal opinion is that if you're not going to persist a calculated column, write a derived table or view, instead. That way, no one can screw up and actually make queries that even try to use it effeciently.

--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1412201
Posted Monday, January 28, 2013 9:22 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 3:26 PM
Points: 2,330, Visits: 3,509
Jeff Moden (1/28/2013)
My personal opinion is that if you're not going to persist a calculated column, write a derived table or view, instead. That way, no one can screw up and actually make queries that even try to use it effeciently.



Typically the overhead to materialize the computed column (cc) at run time trivial. If it is, a cc has a lot of advantages, so I suggest using it as intended.

A derived table risks differing definitions in multiple queries.

A view forces people to use different queries to access the data depending on whether they need a certain cc or not. If a query needs that cc added, you have to rewrite the query just because of that.


SQL DBA,SQL Server MVP('07, '08, '09)

Carl Sagan said: "There is no such thing as a dumb question." Sagan obviously never watched a congressional hearing!
Post #1412497
Posted Monday, January 28, 2013 5:06 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: 2 days ago @ 7:27 AM
Points: 35,769, Visits: 32,437
ScottPletcher (1/28/2013)
Jeff Moden (1/28/2013)
My personal opinion is that if you're not going to persist a calculated column, write a derived table or view, instead. That way, no one can screw up and actually make queries that even try to use it effeciently.



Typically the overhead to materialize the computed column (cc) at run time trivial. If it is, a cc has a lot of advantages, so I suggest using it as intended.

A derived table risks differing definitions in multiple queries.

A view forces people to use different queries to access the data depending on whether they need a certain cc or not. If a query needs that cc added, you have to rewrite the query just because of that.


Sorry Scott... I meant to put out the bright orange barrels, cones, and flags that warned people that sarcasm was "ON". Go back and read it again. It was dripping in sarcasm in support of the use of CC especially persisted CC.

I'll try to give better warnings in the future.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1412706
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse