Subscribe to this blog
Briefcase
Print
You know, I believe the old saying, “If you don’t use it you will lose it”. Well, I write a lot of Date Manipulation (DM) T-SQL these days and very little (Data Definition) DD. Since my projects have all completed and rolled to production, I am on the backend writing reports and doing some performance tuning. Even when I was doing a lot of DML I never really had to do what I was asked by a developer recently. The question was, “Can you add multiple columns to a Table with a single T-SQL Statement. I thought to myself I am sure you can, but I wasn’t quite sure how. So I took at stab at it:
ALTER TABLE dbo.Table1
ADD Column1 int
ADD Column2 int
Sorry that’s not correct. I know what you are thinking, you call yourself a DBA. Well, I really never had to do this before. So I went directly to my reference of choice, Books Online (BOL). I filtered my search on ALTER TABLE, scrolled down a bit and low and behold there was the answer:
ALTER TABLE dbo.Table1
ADD
Column1 int,
Column2 int
Very simple solution, you only need to add the ADD keyword once, then all you need to do is delimit the column definitions with commas. Maybe the guys at SQLServerCentral can use this as the Question of the Day. Thanks again BOL.
Talk to you soon,
Patrick LeBlanc
SQL Down South
Posted by sanjeet.uchil on 13 August 2009
nice, thats why i like sql. even complex looking problems have simple solutions.
Posted by alexgmz23 on 13 August 2009
I agree with sanjeet, sometimes the more complex the problem is the more simple is the solution.
Posted by mjmoch on 13 August 2009
Can you point me in the direction of the BOL site you use as a reference? I would love to add that site as another SQL tool.
Posted by Patrick LeBlanc on 13 August 2009
mjmock, the source of the information can be obtained from either SQL Server Books Online:
ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10de_6tsql/html/f1745145-182d-4301-a334-18f799d361d1.htm
Or msdn:
msdn.microsoft.com/.../ms190273.aspx
Hope this helps. If not let me know.
Posted by Charles Kincaid on 13 August 2009
One of the tricks that I se is the scripting feature in SSMS. I keep a "play" database on a server just to try stuff out.
I open up the table designer, chose my test table, add a couple of columns, and generate the change script.
ALTER TABLE dbo.KrapTable ADD
AddFirstNumber bigint NULL,
AddSomeText nvarchar(MAX) NULL
Now some changes done by SSMS recreate the entire table. Like the recent change I had to make for a warehousing project where they expanded the Pallet number column. SSMS wanted to recreate each table when ALTER TABLE did each table in a fraction of a second.
Posted by john.decoville on 13 August 2009
Hey Patrick!
How come you make things so easy compared to MSDN?
They should hire you!
Thanks,
--John
Posted by David Walker on 13 August 2009
While we're nitpicking (we're programmers, after all): The expression is "lo and behold", not "low and behold".
Or, from old books and pirate tales, "Lo! And behold!". Low has nothing to do with it!
David Walker
Posted by Patrick LeBlanc on 13 August 2009
Thanks David, you know a fellow co-worker brought that to my attention and I meant to change it, but I forgot.
Thanks
Posted by Dave F on 13 August 2009
Lo and behold: means look and notice; used often in the Bible.
Low and behold: a recent movie about life in New Orleans after Katrina.
That should settle who's more powerful: Bible or Hollywood. ;=)
Posted by Amit Lohia on 13 August 2009
Now just imaging this was an interview question and someone got it wrong it does not mean they are a bad DBA.
Judging someone skill level in 1 hour interview is an art.
Posted by geerobg on 13 August 2009
~Now just imaging this was an interview question and someone got it wrong it does not mean they are a bad DBA.~
Nope... but it would be definitely indicate someone without much architecture experience. That said... I wonder how well I'd do without access to my vast compendium of scripts (I have gotten lazy as of late).
And with intellisense becoming more prevalent I can feel my gray matter slowly turning black.
Posted by Adam Tappis on 17 August 2009
No disrespect to the author, but I don't really see how something so trivial even warrants an article.
BOL should be any respectable DBA's or developers bread and butter as no-one is capable of keeping that mass of information and details in their head.
Posted by Varinder Sandhu on 31 December 2010
Really Good Article