ALTER

Question

Post reply

ALTER

Sankar Reddy

SSCrazy

Points: 2410
More actions
July 13, 2010 at 8:40 pm

#223244

Comments posted to this topic are about the item ALTER
[font="Courier New"]Sankar Reddy | http://SankarReddy.com/[/url][/font]

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

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

Nakul Vachhrajani SSChampion Points: 10294 More actions · Answer 1

Really good question. Thank-you!

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

Follow me on
Twitter: @sqltwins

malleswarareddy_m SSCertifiable Points: 5847 More actions · Answer 2

good Question.Learned some new point about nulls

Malleswarareddy
I.T.Analyst
MCITP(70-451)

Carlo Romagnano SSC-Insane Points: 22668 More actions · Answer 3

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

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 4

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/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 5

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 SSC-Addicted Points: 422 More actions · Answer 6

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 Ten Centuries Points: 1112 More actions · Answer 7

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. 🙂

Carlo Romagnano SSC-Insane Points: 22668 More actions · Answer 8

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.

Rose Bud Hall of Fame Points: 3147 More actions · Answer 9

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.

SanjayAttray SSChampion Points: 13157 More actions · Answer 10

As the DBA was on vacation ................ Huh. No way. My manager would give me a call to make changes no matter if I am sitting in Taj trump:hehe:. And that too not without testing and a through input of performance documented.

SQL DBA.

Sankar Reddy SSCrazy Points: 2410 More actions · Answer 11

@mike-2 Dougherty, @wware, @SanjayAttray

Is it mentioned anywhere in QoD that this change is going to be deployed to production while the DBA is on vacation?

I don’t think I ever mentioned that part, (IMO) it is irrelevant to the QoD.

This question is NOT about performance also, and is phrased in such a way that if you follow the question clearly then you can eliminate the other possibilities and arrive at the right answer easily. This is just my opinion.

However, have you choosed the right answer because you know the concept behind the NULL bitmap in data pages vs Non-Clustered indexes? The intent of this question is to share that information about the difference between them.

If at-least few people realize they learnt something new (about NULL bitmap concept) then this QoD has served its purpose.

Thank You.

[font="Courier New"]Sankar Reddy | http://SankarReddy.com/[/url][/font]

Rose Bud Hall of Fame Points: 3147 More actions · Answer 12

Only teasing...enjoyed the back-story! Didn't mean to distract from your main point.

Definitely learned something about NULL bitmaps. Your explanation was top-notch. By "performance" I was referring to speed of the alter statement, as you did in your answer choices.

My apologies if offense was taken by well-intended, light-hearted comments.

webrunner SSC-Dedicated Points: 31862 More actions · Answer 13

Great question. I got it wrong, but at least I learned that the two options, though ostensibly only slightly different from each other, have very different results. I need to study the NULL bitmap in more detail.

Thanks,

webrunner

-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

Sankar Reddy SSCrazy Points: 2410 More actions · Answer 14

[font="Courier New"]@wware,

I didn’t see anything offensive in anyone’s reply so far nor was I offended in anyway. So, there is absolutely no need to apologize here.

Criticism is good and if taken the right way, will help to write better and unambiguous content. I definitely got what I wanted from this QoD and hopefully will comeback with more in the near future.

[/font]

[font="Courier New"]Sankar Reddy | http://SankarReddy.com/[/url][/font]