SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


ALTER


ALTER

Author
Message
Sankar Reddy
Sankar Reddy
Mr or Mrs. 500
Mr or Mrs. 500 (514 reputation)Mr or Mrs. 500 (514 reputation)Mr or Mrs. 500 (514 reputation)Mr or Mrs. 500 (514 reputation)Mr or Mrs. 500 (514 reputation)Mr or Mrs. 500 (514 reputation)Mr or Mrs. 500 (514 reputation)Mr or Mrs. 500 (514 reputation)

Group: General Forum Members
Points: 514 Visits: 1250
Comments posted to this topic are about the item ALTER

Sankar Reddy | http://SankarReddy.com/
Nakul Vachhrajani
Nakul Vachhrajani
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2330 Visits: 2145
Really good question. Thank-you!

Thanks & Regards,
Nakul Vachhrajani.
http://nakulvachhrajani.com
Be courteous. Drive responsibly.

Follow me on
Twitter: @sqltwins
Google Plus: +Nakul
malleswarareddy_m
malleswarareddy_m
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2235 Visits: 1189
good Question.Learned some new point about nulls

Malleswarareddy
I.T.Analyst
MCITP(70-451)
Carlo Romagnano
Carlo Romagnano
SSCertifiable
SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)

Group: General Forum Members
Points: 5145 Visits: 3327
Option 1 is the right and only choice, Option 2 runs fast same as Option 1.
The performance problem is done converting NULL to NOT NULL and not viceversa.

Try this script to verify:
create table zz(a varchar(20) not null)
insert into zz(a) select a.id from sysobjects,sysobjects a

-- same performance
SET IMPLICIT_TRANSACTIONS ON
go
alter table zz alter column a varchar(50) not null
alter table zz alter column a varchar(50)
go
--
rollback
go
-- converting null to not null may take a while
alter table zz alter column a varchar(50)
alter table zz alter column a varchar(50) not null
go
rollback

I run on tuttopodismo
Hugo Kornelis
Hugo Kornelis
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11442 Visits: 12007
Carlo, you are missing an impportant point in your repro.

The performance difference in the question is not caused by rebuilding or converting the table contents, but by rebuilding / converting the nonclustered index. Since your repro lacks a nonclustered index, it's not a surprise that you don't see a performance difference.


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Bhuvnesh
Bhuvnesh
SSCertifiable
SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)

Group: General Forum Members
Points: 5548 Visits: 4076
Good question , i took some minutes to understand this

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
Sanjay-940444
Sanjay-940444
SSC Rookie
SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)

Group: General Forum Members
Points: 46 Visits: 104
Here question is not about performance but WHAT change company wants.
There was a not null constraint already on this column means company didnt want to have null value for this columns and while increasing the size we have to keep this in mind.2nd option will make it nullable hence accepting null value (atleast one as it is a unique constraint) which will violate an already existing business rule.

rgds
Mike Dougherty
Mike Dougherty
SSC-Enthusiastic
SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)

Group: General Forum Members
Points: 188 Visits: 952
how about:
Schema changes to a 50 million row table should not be done while the DBA is on vacation. Especially when the table in question potentially affects "login" of 50 million customers. Smile
Carlo Romagnano
Carlo Romagnano
SSCertifiable
SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)

Group: General Forum Members
Points: 5145 Visits: 3327
Hugo Kornelis (7/14/2010)
Carlo, you are missing an impportant point in your repro.

The performance difference in the question is not caused by rebuilding or converting the table contents, but by rebuilding / converting the nonclustered index. Since your repro lacks a nonclustered index, it's not a surprise that you don't see a performance difference.

OK, for performance question.
But the two options do very different things: option 2 changes the column from NOT NULLABLE TO NULLABLE.
The hint is to enlarge a column not to change attribute.

I run on tuttopodismo
Rose Bud
Rose Bud
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1619 Visits: 1062
I was amused to see Sanjay and Mike D had the same thought process I did: Should John the developer be willing to risk the DBA's post-vacation wrath by allowing nulls on that column? (even if we are only talking about one null, at most)

But, we digress from the main point about performance which was made quite well by this well-written question and thorough explanation of the correct answer. Thanks, Sankar.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search