Datatype Changes and its effect on Index

Question

Post reply

Datatype Changes and its effect on Index

Rechana Rajan

SSCertifiable

Points: 7721
More actions
March 14, 2017 at 6:28 am

#321337

Changes dataype of a column which is not part of Clustered index key or any non-clustered index and the change updated the statistics of both clustered and non-clustered index? Does this really rebuild clustered and non-clustered index or just updated the statistics?

Viewing 7 posts - 1 through 6 (of 6 total)

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

Steve Jones - SSC Editor SSC Guru Points: 736478 More actions · Answer 1

If this column is not used in an index, and not part of the PK/FKs, then just statistics

Johan Bijnens SSC Guru Points: 135375 More actions · Answer 2

it depends, for some data type changes it has to rewrite the whole table to get all internal stuff in order. ( e.g. from char to varchar, varchar to nvarchar, float to int, ... )

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Rechana Rajan SSCertifiable Points: 7721 More actions · Answer 3

Steve Jones - SSC Editor - Wednesday, March 15, 2017 7:12 AM
If this column is not used in an index, and not part of the PK/FKs, then just statistics

Wow..Steve answering for me..

Thanks a lot Steve. Can you help me understand more , what if the column is not part of index but is part of FK? modifying the existing column which is not part of any index don't have any impact on clustered index?

Rechana Rajan SSCertifiable Points: 7721 More actions · Answer 4

ALZDBA - Wednesday, March 15, 2017 7:30 AM
it depends, for some data type changes it has to rewrite the whole table to get all internal stuff in order. ( e.g. from char to varchar, varchar to nvarchar, float to int, ... )

Thanks ALZDBA,

can you share some links or docs?

Johan Bijnens SSC Guru Points: 135375 More actions · Answer 5

ALTER COLUMN: https://msdn.microsoft.com/en-us/library/ms190273.aspx

"Modify columns" https://msdn.microsoft.com/en-us/library/ms190259.aspx

implicit conversions https://msdn.microsoft.com/en-us/library/ms187928.aspx