Persisted Columns

Question

Persisted Columns

Steve Jones - SSC Editor

SSC Guru

Points: 734449
More actions
September 1, 2014 at 11:16 pm

#74499

Comments posted to this topic are about the item Persisted Columns
September 2, 2014 at 6:19 am

This was removed by the editor as SPAM

Viewing 15 posts - 1 through 15 (of 24 total)

You must be logged in to reply to this topic. Login to reply

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 1

Nice question, didn't know about this syntax.

Although dropping and recreating the column also works and at this point in time, a lot of people thought that was the answer.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Raghavendra Mudugal SSChampion Points: 10658 More actions · Answer 2

Thanks for the post, SJ, good one

but, not sure if I am missing here anything, I tried using ALTER TABLE ALTER COLUMN, it gives the error.

ALTER TABLE MyTest

ALTER COLUMN third PERSISTED

Msg 4928, Level 16, State 1, Line 1

Cannot alter column 'third' because it is 'COMPUTED'.

Even, the two links you referred, agrees that it cannot be altered but drop and re-create.

Dropping and re-creating works

ALTER TABLE MyTest

ADD third as (first + sec) persisted

I executed this on SQL 2012 DEV EDT

Microsoft SQL Server 2012 (SP1) - 11.0.3153.0 (X64)

Jul 22 2014 15:26:36

Copyright (c) Microsoft Corporation

Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

ww; Raghu
--
The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 3

Raghavendra Mudugal (9/2/2014)
Thanks for the post, SJ, good one
but, not sure if I am missing here anything, I tried using ALTER TABLE ALTER COLUMN, it gives the error.
ALTER TABLE MyTest
ALTER COLUMN third PERSISTED
Msg 4928, Level 16, State 1, Line 1
Cannot alter column 'third' because it is 'COMPUTED'.

You forgot the ADD keyword.

ALTER TABLE MyTest

ALTER COLUMN third ADD PERSISTED

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

robertjtjones SSC-Addicted Points: 484 More actions · Answer 4

Like it - but agree with above - dropping and recreating would achieve the same goal.

Raghavendra Mudugal SSChampion Points: 10658 More actions · Answer 5

Koen Verbeeck (9/2/2014)
You forgot the ADD keyword.
ALTER TABLE MyTest
ALTER COLUMN third ADD PERSISTED

Thanks Keon, I saw that in the local_help after posting, just missed it.

Hope you can add some light on this..

ALTER COLUMN
Specifies that the named column is to be changed or altered.
The modified column cannot be any one of the following:
•A column with a timestamp data type.
•The ROWGUIDCOL for the table.
•A computed column or used in a computed column.

highlighted in bold, I guess it is refereeing to "cannot modify the computed column's definition? (like how it is computed)". "ADD PERSISTED" is like enabling the attribute but it is not touching the actual definition. Is this is correct?

ww; Raghu
--
The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 6

Raghavendra Mudugal (9/2/2014)
Thanks ~~Keon~~ Koen, I saw that in the local_help after posting, just missed it.
Hope you can add some light on this..
ALTER COLUMN
Specifies that the named column is to be changed or altered.
The modified column cannot be any one of the following:
•A column with a timestamp data type.
•The ROWGUIDCOL for the table.
•A computed column or used in a computed column.
highlighted in bold, I guess it is refereeing to "cannot modify the computed column's definition? (like how it is computed)". "ADD PERSISTED" is like enabling the attribute but it is not touching the actual definition. Is this is correct?

This is indeed when you alter the column definition.

With ADD PERSISTED you add the property to the column, but you are not modifying the column definition itself.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Raghavendra Mudugal SSChampion Points: 10658 More actions · Answer 7

Koen Verbeeck (9/2/2014)
This is indeed when you alter the column definition.
With ADD PERSISTED you add the property to the column, but you are not modifying the column definition itself.

Thanks, that helps.

(I apologize for spelling your name wrong)

ww; Raghu
--
The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.

Nakul Vachhrajani SSChampion Points: 10283 More actions · Answer 8

Like it, got it wrong, and learnt about the syntax (i.e. the "ADD" keyword).

However, dropping and recreating works just as well (and was the only option I knew about till a minute ago!).

Thanks & Regards,
Nakul Vachhrajani.
http://nakulvachhrajani.com

Follow me on
Twitter: @sqltwins

Gazareth One Orange Chip Points: 27737 More actions · Answer 9

Hmm, not great to have a perfectly valid answer marked as wrong!

Nice to know you can add the persisted property without dropping though.

I wonder if there's any real difference between the two methods behind the scenes, other than to the metadata.

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 10

Gazareth (9/2/2014)
I wonder if there's any real difference between the two methods behind the scenes, other than to the metadata.

That would indeed be interesting to know.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

danrst171 Mr or Mrs. 500 Points: 571 More actions · Answer 11

Gazareth (9/2/2014)
Hmm, not great to have a perfectly valid answer marked as wrong!
Nice to know you can add the persisted property without dropping though.
I wonder if there's any real difference between the two methods behind the scenes, other than to the metadata.

I guess it is incorrect because of the wording.

The other answer was "You must...", but because there is an alternative, you don't have to do that.

-------------------------------------------------
Trainee DBA

paul.knibbs SSCoach Points: 15320 More actions · Answer 12

This is one of those questions where I'm glad I didn't read the documentation and selected what looked like the most logical answer, because BOY is that documentation for ALTER COLUMN contradictory! It says you can't use it on a computed column, but further down talks about the ADD PERSISTED syntax saying that one can *only* be used a computed column??? :crazy:

Ed Wagner SSC Guru Points: 287024 More actions · Answer 13

I learned something new today. I didn't know about the add persisted syntax either. Got it wrong, but still learned something, so thanks for the question.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions