Have been to an amazing trip visiting few of my friends and was not able to spend some time in my technical blogging :-p
Okay! Anyways I’m back to business after a loong (No Pun Intended) trip!
Thought of sharing one of my sickest and weirdest experience happened today because of my recent practice of editing tables using SSMS GUI.
Do you love Altering a table using GUI in SSMS?? If you said “yes, I do” then it’s time to think about what you are really doing with your SQL Servers!! If you are learning T-SQL, Unarguably this is a fabulous place to learn syntax-es. But what if you are lazy in typing or used to do it even though you’ve enough exp supporting SQL Servers? – No excuses, you’ve to change your practice!
You don’t love GUI for Altering your tables but are you forced to practice sooo? Well, that’s the case with me. I’ll try to change with this lesson learnt:)
I’m supporting an environment where application team wants columns to be added in middle of the table since past 12 months (I know what you are thinking, it doesn’t make any sense adding a column in middle of table, all it matters how we write our select statements.) I had enough argument with Application team and they are not willing to change the practice:( 😦
For that we need to use GUI thing of SSMS for editing table. Doing those requests since past 12 months i’m kind of get used to it even for increasing column width! I pity myself!!
Okay! coming to the scenario, we’ve a 9 GB table where the request is to change the width of a column from varchar(25) to varchar(40) and as usual i started using SSMS GUI instead of simple ALTER statement and it was timing out again and again. I changed timeout settings from 30 seconds to 120,240,300 seconds, nothing helped and i started to press PANIC button on my Keyboard😦
I wasted 30 mins of my time banging my head and at last i called one of the senior most DBA’s in my team and he was looking for how and what exactly i was trying to do. When i explained what i’m doing, he reminded me about there is something called “ALTER TABLE” in SQL Server!!!LOL….And I was like, what am i doing and i slapped myself saying “what an idiot am i”.
ALTER TABLE / ALTER COLUMN did the magic in matter of seconds.
Epitome: SSMS is very poor and it does lot of unnecessary stuff behind the scenes for altering any table.(look your self by scripting out when using GUI). Believe me you’ll be surprised how SSMS is handling those things and i hope Microsoft will change things in future versions of SSMS.
MSSQLTREK 