Changing a computed column

Question

Post reply

Changing a computed column

Steve Jones - SSC Editor

SSC Guru

Points: 736212
More actions
December 17, 2015 at 8:04 pm

#75729

Comments posted to this topic are about the item Changing a computed column

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

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

HappyGeek SSCoach Points: 18729 More actions · Answer 1

Nice question Steve, thanks, a timely reminder about indexing.

...

Carlo Romagnano SSC-Insane Points: 22669 More actions · Answer 2

In case of shortening or if the type is CHAR/NCHAR the following error is raised:

Msg 5074, Level 16, State 1, Line 17

The index 'IX_MySales_CustomerReport' is dependent on column 'OrderStatus'.

Msg 4922, Level 16, State 9, Line 17

ALTER TABLE ALTER COLUMN OrderStatus failed because one or more objects access this column.

paul s-306273 SSChampion Points: 10730 More actions · Answer 3

paul s-306273

SSChampion

Points: 10730

December 18, 2015 at 2:07 am

#1846710

...makes sense.

Thanks Steve.

Ed Wagner SSC Guru Points: 287026 More actions · Answer 4

I was looking for the trick I was missing, but never found it. 😉 Thanks, Steve.

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

Ed Wagner SSC Guru Points: 287026 More actions · Answer 5

Carlo Romagnano (12/18/2015)
In case of shortening or if the type is CHAR/NCHAR the following error is raised:
Msg 5074, Level 16, State 1, Line 17
The index 'IX_MySales_CustomerReport' is dependent on column 'OrderStatus'.
Msg 4922, Level 16, State 9, Line 17
ALTER TABLE ALTER COLUMN OrderStatus failed because one or more objects access this column.

That's correct, but I don't recall seeing an answer that shortened the width of the column. I can't go back and look at them again and I'm not completely caffeinated yet, so I might be remembering incorrectly, so I could be wrong in my recollection.

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

Carlo Romagnano SSC-Insane Points: 22669 More actions · Answer 6

Ed Wagner (12/18/2015)
Carlo Romagnano (12/18/2015)
In case of shortening or if the type is CHAR/NCHAR the following error is raised:
Msg 5074, Level 16, State 1, Line 17
The index 'IX_MySales_CustomerReport' is dependent on column 'OrderStatus'.
Msg 4922, Level 16, State 9, Line 17
ALTER TABLE ALTER COLUMN OrderStatus failed because one or more objects access this column.
That's correct, but I don't recall seeing an answer that shortened the width of the column. I can't go back and look at them again and I'm not completely caffeinated yet, so I might be remembering incorrectly, so I could be wrong in my recollection.

It's just a remark! 😀

Ed Wagner SSC Guru Points: 287026 More actions · Answer 7

Carlo Romagnano (12/18/2015)
Ed Wagner (12/18/2015)
Carlo Romagnano (12/18/2015)
In case of shortening or if the type is CHAR/NCHAR the following error is raised:
Msg 5074, Level 16, State 1, Line 17
The index 'IX_MySales_CustomerReport' is dependent on column 'OrderStatus'.
Msg 4922, Level 16, State 9, Line 17
ALTER TABLE ALTER COLUMN OrderStatus failed because one or more objects access this column.
That's correct, but I don't recall seeing an answer that shortened the width of the column. I can't go back and look at them again and I'm not completely caffeinated yet, so I might be remembering incorrectly, so I could be wrong in my recollection.
It's just a remark! 😀

Ah - got it.

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

George Vobr SSCrazy Eights Points: 9996 More actions · Answer 8

Thanks Steve, may could help also this reference: https://msdn.microsoft.com/en-us/library/ms190806.aspx

Xavon SSCrazy Points: 2280 More actions · Answer 9

Trying to figure out what the question had to do with a computed column had me stymied for a few minutes. I kept going back over it, trying to figure out what I was missing. I guess the answer is nothing, the question title was just a red herring.

Sean Lange SSC Guru Points: 286590 More actions · Answer 10

Xavon (12/18/2015)
Trying to figure out what the question had to do with a computed column had me stymied for a few minutes. I kept going back over it, trying to figure out what I was missing. I guess the answer is nothing, the question title was just a red herring.

Same here. There is no computed column so the question title really had me scratching my head.

Good question.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 11

Xavon (12/18/2015)
Trying to figure out what the question had to do with a computed column had me stymied for a few minutes. I kept going back over it, trying to figure out what I was missing. I guess the answer is nothing, the question title was just a red herring.

Ahh, sorry. I had a computed column in there earlier and changed the code. I must have mistitled this one without realizing it during testing.

SQL_Hunt SSC-Dedicated Points: 33518 More actions · Answer 12

SQL_Hunt

SSC-Dedicated

Points: 33518

December 18, 2015 at 11:50 am

#1846858

Nice Question. Thnx Steve!

Thanks.

patricklambin SSCrazy Eights Points: 9994 More actions · Answer 13

Thanks for this easy question ( not surprising as it is the week-end ).

Just a little remark : this question was treated 3 years ago in a session by an escalation engineer who did a remark maybe important : it is possible to decrease the size of a column ( varchar/nvarchar ) if for all the rows , the new size is bigger than the maximum size of the column in all rows.

As I am not sure to be clear ( I am admitting that I have a poor written English language and the spoken one is worst ) : I have 4 rows with the values

value1 value01 value001 for a varchar(20) no problem to decrease the size to varchar(10) , but it is impossible to change to a varchar(7) as the size of value001 is 8.

If I am going wrong , please , could you provide a link giving a full ( and easy ) explanation for my error ? Thanks beforehand.

Have a nice day/week-end.

TomThomson SSC Guru Points: 104773 More actions · Answer 14

Carlo Romagnano (12/18/2015)
Ed Wagner (12/18/2015)
Carlo Romagnano (12/18/2015)
In case of shortening or if the type is CHAR/NCHAR the following error is raised:
Msg 5074, Level 16, State 1, Line 17
The index 'IX_MySales_CustomerReport' is dependent on column 'OrderStatus'.
Msg 4922, Level 16, State 9, Line 17
ALTER TABLE ALTER COLUMN OrderStatus failed because one or more objects access this column.
That's correct, but I don't recall seeing an answer that shortened the width of the column. I can't go back and look at them again and I'm not completely caffeinated yet, so I might be remembering incorrectly, so I could be wrong in my recollection.
It's just a remark! 😀

The interesting thing about that remark is that it makes it clear that a constant length colum can't have it's length changed while included in an index - so Steve's explanation "If you are lengthening a column, you can just alter it, even if the column is included in an index" isn't quite correct.

Nice easy Friday question anyway.

Tom