Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Computed Columns

Posted on 2012-04-11
10
Medium Priority
?
433 Views
Last Modified: 2012-04-14
hi experts, can yo explain me with transact sql code this

"You may be asked if non-persisted computed columns can improve performance. Unfortunately the answer to this covers material that is advanced for this point in the course but included here for trainer reference. Computed columns can help with performance, even when they are not persisted and not referenced directly. If you have a predicate like: unitprice * quantity > 2500 Sql Server cannot make any prediction on rows returned but if you create a computed column on unitprice * quantity, even if it is not persisted, SQL Server can and will create a statistic on it, which can lead to a much better plan. And this works, even when you not directly reference the computed column, as the query optimizer recognizes that it is the same expression. "
0
Comment
Question by:enrique_aeo
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
  • +1
10 Comments
 
LVL 42

Assisted Solution

by:dqmq
dqmq earned 800 total points
ID: 37836255
SQL Server keeps statistics that are used for optimizing queries.  Computed columns add information to the statistics that may produce better execution plans in some cases.   In the example given, the WHERE clause includes an expression that can be duplicated in a non-persistent computed column.  Declaring the computed column means better statistics even if the computed column is not referenced in the query.

I would describe it as a quirky and idiomatic optimization technique that might be useful about once in a career.
0
 

Author Comment

by:enrique_aeo
ID: 37836883
can you explaine me with transact sql code
0
 
LVL 42

Accepted Solution

by:
dqmq earned 800 total points
ID: 37838934
Not sure what your looking for, but here's the best I can do.

create  table t1 (id int, price int, tax int)
create  table t2 (id int, price int, tax int, Total as price + tax )
create  table t3 (id int, price int, tax int, Total as price + tax )

select ID from t1 where price + tax > 2
select ID from t2 where price + tax > 2
select ID from t3 where price  > 2

exec sp_helpstats 't1', 'all'
exec sp_helpstats 't2', 'all'
exec sp_helpstats 't3', 'all'

Open in new window



T1
_WA_Sys_00000002_0169315C            price
_WA_Sys_00000003_0169315C            tax

T2
_WA_Sys_00000002_025D5595            price
_WA_Sys_00000003_025D5595            tax
_WA_Sys_00000004_025D5595            Total

T3
_WA_Sys_00000002_035179CE            price




Note the additional statistics produced for the computed column T2.Total even though it is not used in the query.  Note that no statistics are produced for the similar computed column T3.Total.    

That proves the assertion that statistics may be used for non-persisted computed columns when the underlying expression is referenced in the predicate.  I have no way to prove if that results in a better execution plan, but I expect the cases where it does are rare.
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 37839287
As always, it depends on the specific queries.

Under the right conditions, this will have a significant beneficial impact on the SQL plans generated.

In other cases, it will have virtually no impact.
0
 
LVL 42

Expert Comment

by:dqmq
ID: 37839419
As always, it depends on the specific queries

Absolutely.  And the data population.  But, in this case we are talking about non-persisted columns and to me that implies non-indexed, as well.
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 37839428
Non-persisted doesn't "imply" non-indexed, it guarantees it.  You can't possibly index a non-persisted column.

Not being indexed doesn't prevent statistics on it from affecting the final query plan, though.
0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 800 total points
ID: 37840846
Non-persisted doesn't "imply" non-indexed, it guarantees it.  You can't possibly index a non-persisted column.
Actually in some cases you can index a computed column that is not Persisted.  Here is an example:
CREATE TABLE MyTable
    (
     ID integer,
     Price integer,
     Tax integer,
     Total AS Price + Tax                -- PERSISTED
    )
go
CREATE INDEX IX_MyTable_Total ON MyTable (Total)
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 37842766
Then SQL will persist it for you.  You cannot index a non-persisted column.
0
 
LVL 70

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 400 total points
ID: 37843369
Well, SQL will have to persist the column in the index itself; it may or may not have to persist the column in the original table.  But it cannot index a column that it does not store somewhere.
0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 800 total points
ID: 37844108
Well, SQL will have to persist the column in the index itself; it may or may not have to persist the column in the original table
Exactly.
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…

618 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question