ALTER

Question

ALTER

Viewing 15 posts - 16 through 30 (of 44 total)

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

michael.kaufmann SSCrazy Points: 2816 More actions · Answer 1

My gutt told me to go for option 1, but not because of it running faster, but because I wondered why one would change a non-nullable column to nullable (especially when it holds the login name).

Put these considerations aside (as this wasn't where the QotD was aiming), and being under the impression that the null bitmap is always present, picked option 5...

Lesson learned--thank you very much.

mtassin SSC-Insane Points: 23099 More actions · Answer 2

michael.kaufmann (7/14/2010)
My gutt told me to go for option 1, but not because of it running faster, but because I wondered why one would change a non-nullable column to nullable (especially when it holds the login name).

That's why I went with Option 1. The Business had already determined that the LOGIN column must be non nullable. While I'm on vacation I don't expect my guys to make these kinds of changes, but even if they did, replacing a NOT NULL column with a NULLABLE one would incur more wrath than they'd ever want to run into.

--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link[/url]
For tips on how to post your problems[/url]

SQLRNNR SSC Guru Points: 281334 More actions · Answer 3

Thanks for the Question.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

gary.mazzone SSCarpal Tunnel Points: 4682 More actions · Answer 4

Good post. Always good to learn something new in the QoD

webrunner SSC-Dedicated Points: 31711 More actions · Answer 5

mtassin (7/14/2010)
michael.kaufmann (7/14/2010)
My gutt told me to go for option 1, but not because of it running faster, but because I wondered why one would change a non-nullable column to nullable (especially when it holds the login name).
That's why I went with Option 1. The Business had already determined that the LOGIN column must be non nullable. While I'm on vacation I don't expect my guys to make these kinds of changes, but even if they did, replacing a NOT NULL column with a NULLABLE one would incur more wrath than they'd ever want to run into.

I waffled over whether leaving the NOT NULL out (Option 2) would cause the column to be changed to nullable, or whether it would be ignored and leave the column with its current setting (NOT NULL). I guess I now know what would happen - the column would change to nullable unless one explicitly specified NOT NULL in the ALTER statement.

As they say, I learn something new every day. Unfortunately, I am falling behind because I NEED to learn 2-5 new things every day. 🙂

- 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

Daniel Bowlin SSC-Dedicated Points: 34566 More actions · Answer 6

I had a tough time with this one. I have to do some more reading. Thanks for the question and teh explanation.

UMG Developer SSChampion Points: 13482 More actions · Answer 7

Interesting question, but it seems like it was more of a trick question than anything... The questions says "management wants to change the loginname so that it can accept longer loginnames up to 60 characters and that is the only change requested."

Given that Option 1 is the only option, Option 2 violates what management requested by making the field NULLable as well. So I chose the correct answer not actually thinking about the performance difference, as I didn't see that it was relevant.

Though I did learn a little more about the NULL bitmap, so that is a good thing. 🙂

Ron McCullough SSC Guru Points: 63877 More actions · Answer 8

As stated by others:

Good post. Always good to learn something new in the QOD

Thanks for a thought provoking / learning experience keep up the good work.

Sankar Reddy - Looking forward to additional QOD from you

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

andrew@tregonning.net SSCommitted Points: 1503 More actions · Answer 9

Given that option 2 changes the column to allow nulls, and that was not desired, I believe "Option 2 will fail" is the best answer.

-at

Sankar Reddy SSCrazy Points: 2410 More actions · Answer 10

[font="Courier New"]Going thru the comments, it seems NOT everyone is aware that NOT NULL clause is necessary when altering the column. It’s a simple fact but not everyone knows about that. I am still surprised over ¾ of the people got this incorrectly. I hope at-least few people will know more about NULL bitmap because of this QoD.

Hugo mentioned that I might get some grief over the answer options. I tried to come up with a different set of answer options that are intelligent yet simple and relevant but didn’t make it this time.

I tried to bring a fresh look for the QoD, like a code review instead of just asking a random question. Look at this question as a code review project and not just a random out of the box question.

I have few ideas for the next QoD’s and will try to make them better from the feedback I received here. Thanks for participating in this and sharing the feedback (both good & bad). Appreciate it.

[/font]

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

ziangij SSCertifiable Points: 7983 More actions · Answer 11

ziangij

SSCertifiable

Points: 7983

July 14, 2010 at 10:56 pm

#1193238

thanks , really nice question...:-)

back_of_the_fleet Say Hey Kid Points: 683 More actions · Answer 12

I too learned something new today about something I didn't even know existed (NULL bitmap).

Excellent question.

JR

John Rogerson
BI Technical Lead
Clear Channel International

john.arnott SSChampion Points: 11882 More actions · Answer 13

Sankar Reddy (7/14/2010)
[font="Courier New"]Going thru the comments, it seems NOT everyone is aware that NOT NULL clause is necessary when altering the column. It’s a simple fact but not everyone knows about that. I am still surprised over ¾ of the people got this incorrectly. I hope at-least few people will know more about NULL bitmap because of this QoD........[/font]

Count me as one who hadn't realized that an ALTER column statement defaults to making it nullable. While the behind-the-scenes, performance related explanation is appreciated, the primary lesson is to include NOT NULL in the ALTER statement unless you really want to change the column to nullable.

One more thought: Isn't it a shame that when we're doing real-world changes we don't get neatly wrapped multiple-choice questions?

UMG Developer SSChampion Points: 13482 More actions · Answer 14

john.arnott (7/15/2010)

Sankar Reddy (7/14/2010)
One more thought: Isn't it a shame that when we're doing real-world changes we don't get neatly wrapped multiple-choice questions?

Yep, that is one of the things I have said to people when they comment on my high scores on the MCTS and MCITP tests.

When my boss comes to me and says they need something to be setup he doesn't give me five choices with one of them being correct. That would be nice, but not realistic.

Sankar Reddy SSCrazy Points: 2410 More actions · Answer 15

[font="Courier New"]I am glad this QoD has helped people learn new things. The credit should directly go to Hugo Kornelis for his excellent feedback otherwise this would have been an incorrect/ordinary question.[/font]

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