Friday, April 25, 2008

SQL Teaser: Some Things Are Not What They Seem

This one is a little sneaky, don’t send me hate mail for it.

What does this return?


Copy and paste it into QA/SSMS to verify :-0

Tuesday, April 22, 2008

How to rename a column in a SQL Server table without using the designer

If you have a table and you want to rename a column without using the designer, how can you do that?

First create this table

CREATE TABLE TestColumnChange(id int)
INSERT TestColumnChange VALUES(1)

SELECT * FROM TestColumnChange

As you can see the select statement returns id as the column name. you can use ALTER table ALTER Column to change the dataype of a column but not the name.

Here is what we will do, execute the statement below

EXEC sp_rename 'TestColumnChange.[id]', 'value', 'COLUMN'

Now do the select, you will see that the column name has changed

SELECT * FROM TestColumnChange

That is it, very simple

Monday, April 21, 2008

Interview With Stéphane Faroult About Refactoring SQL Applications

I decided to interview Stéphane Faroult, the author of The Art of SQL because it is one of my favorite SQL books. Stéphane told me he has a new book coming out soon, the name of this book is Refactoring SQL Applications and the majority of this interview is focused on that book.

You can find that interview here: Interview With Stéphane Faroult About Refactoring SQL Applications

Tuesday, April 15, 2008

Solutions for Common T-SQL Problems Wiki Launched

Volunteer Moderators and Answerers who support the Microsoft MSDN SQL Server Forums have launched a Wiki with Solutions for Common T-SQL Problems. Check it out here:

Monday, April 14, 2008

Use IDENT_CURRENT() to return the last identity value generated in all tables in a SQL Server Database

This is how you return all the tables with their last generated identity values in a SQL Server Database

WHERE TABLE_TYPE = 'base table'
AND OBJECTPROPERTY(OBJECT_ID(table_name),'TableHasIdentity')=1

Monday, April 07, 2008

Video: SQL Server Data Services Architecture

SQL Server Technical Evangelist Ryan Dunn sits down and talks turkey with two of the creators of SQL Server Data Services (SSDS), Architect Istvan Cseri and Development Manager Nigel Ellis. Istvan and Nigel dig into how to design applications for SSDS and cover a number of the interesting aspects of working with SSDS in terms of features, design, and security.

Watch it here:

Saturday, April 05, 2008

Links Of The Week 20080405

Here are the links for this week

Send Table or View as embedded HTML in an email – stored procedure
How It Works: SQL Server 2005 DBCC Shrink* May Take Longer Than SQL Server 2000
How It Works: Non-Yielding Resource Monitor
Overhead of Row Versioning
TempDB:: Table variable vs local temporary table
sp_helpindex2 to show included columns (2005+) and filtered indexes (2008) which are not shown by sp_helpindex
Yet Another Stored Procedure vs. Ad-hoc Query Discussion?
Geek City: How SQL Server Detects the Correct Versioned Rows
SQLIOSim is "NOT" an I/O Performance Tuning Tool
Forensic Analysis of a SQL Server 2005 Database Server

Non DB tech
Microsoft Live Search Gains Market Share
Unit Testing with Silverlight
Using Spring.NET and NHibernate with ASP.NET MVC
Enterprise Library 4.0 Community Technology Preview
Hanselminutes Podcast 107 - Digital Photography Explained (for Geeks) with Aaron Hockley
How do Extension Methods work and why was a new CLR not required?
Core War: Two Programs Enter, One Program Leaves
How to set an IIS Application or AppPool to use ASP.NET 3.5 rather than 2.0
Let That Be a Lesson To You, Son: Never Upgrade.
Silverlight 2 DIGG Sample Part I
Silverlight 2 DIGG Sample Part II
Silverlight 2 DIGG Sample Part III
Google Developer Podcast: Picasa Web and Google Gears for Mobile
Ted Neward: On Java and .NET, Software Academics versus Practioners, General Purpose Programming Languages
Whirlwind 7: What's New in C# 3 - Lambda Expressions
Google To Launch BigTable As Web Service To Compete With Amazon's SimpleDB

Non tech
SMCB: Charles Manson Pulls a Radiohead
Possibly the best name ever.
Thank God for Torrents (Pic)

Wednesday, April 02, 2008

Database Refactoring Interview With Scott W. Ambler

Since I am doing scrum and other agile methodologies myself I decided to interview the authority on agile in the database world.

Scott W. Ambler is the Practice Leader Agile Development for IBM Rational

You can find more about Scott here:

I did not feel like duplicating the content here so you can read the interview here: Database Refactoring Interview With Scott W. Ambler

Tuesday, April 01, 2008

SQL Champ - A Quiz to Prove Your SQL Knowledge

In case you are bored CodeProject has a small SQL test (7 questions)

Take the test here: SQL Champ - A Quiz to Prove Your SQL Knowledge

And did you get everything correct?

And yes they wrote Knowlege instead of Knowledge :-)

Not An April Fool’s Joke: SQL Server 2000 Mainstream Support Ends In A Week

April 1st 2008 is the day on which support for SQL Server 2000 ends (and it is also my birthday)

SQL 2000 SP4 - currently supported; support ends on either 12 months after SP5 is released or on 4/8/2008 whichever comes first. Extended support under which you get only break-fix assistance via Premier ends on 4/9/2013. I am not aware of release date for SP5 so far.

SQL 2005 SP2 - currently supported; support ends on either 12 months after SP3 is released or on 4/12/2011 whichever comes first. Extended support under which you get only break-fix assistance via Premier ends on 4/13/2016

More details can be found here:

And here:

Sunday, March 30, 2008

Links Of The Week 20080330

Who needs SQL links when you have this gem?

Check out (grand)ma in the background, she doesn't miss a beat.

Friday, March 28, 2008

How To Use COALESCE And NULLIF To Prevent Updating Columns When A Parameter IS NULL Or A Default Value

A variation of this question popped up twice in the SQL programming newsgroup since yesterday, this means it is time for a blogpost.
Let's say you have a proc like this

CREATE PROC prUpdateTable
int = -1


If the user calls the proc like this exec prUpdateTable null then @Salesman will be null, if the user calls the proc like this exec prUpdateTable then the value of @Salesman will be -1. In both of this cases you don't want to change the value of the column. What can you do?
You can use a combination of NULLIF and COALESCE to handle this. Your update statement would look like this

UPDATE table
Column = COALESCE(NULLIF(@variable,-1),Column)

Here is some code to demonstrate that

CREATE TABLE #foo (id int,salesman int)

1 1
2 1
3 1
4 1

DECLARE @salesman int
@salesman = 5

--Column value will change to 5
SET salesman = COALESCE(NULLIF(@salesman,-1),salesman)

--Column value won't change
SELECT @salesman = -1
SET salesman = COALESCE(NULLIF(@salesman,-1),salesman)

--Column value won't change
SELECT @salesman = NULL
SET salesman = COALESCE(NULLIF(@salesman,-1),salesman)

--Column value will change to 3
SELECT @salesman = 3
SET salesman = COALESCE(NULLIF(@salesman,-1),salesman)

--And here is the output, as you can see when @salesman was -1 or NULL the table did not get updated
1 5
2 1
3 1
4 3


As you can see only the first and the last update statement changed the value of the salesman column
Of course you would never do this if you were to update only one column, you would skip the update instead. If you have to update multiple columns then this is something you can use instead of writing a bunch of dynamic SQL or nested IF statements.

Tuesday, March 25, 2008

Technet Radio: A Look Inside SQLCAT (SQL Customer Advisory Team)

Channel9 has a podcast with the SQLCAT team.

On this episode of TechNet Radio, We learn more about SQL CAT – the SQL Customer Advisory Team. With Launch of SQL 2008 on the way, the SQL CAT team has been working hard preparing lists of best practices, recommendations, technical whitepapers and technical end-to-end case studies on customer implementations.

Listen to it here:

Saturday, March 22, 2008

Links Of The Week 20080322

Here are the links for this week

Programming Policy-Based Management with SMO - Part 1 - Intro
Programming Policy-Based Management with SMO - Part 2 - Conditions
Programming Policy-Based Management with SMO - Part 3 - ExpressionNode and Policy
Programming Policy-Based Management with SMO - Part 4 - Introducing ObjectSets
Programming Policy-Based Management with SMO - Part 5 - TargetSets and TargetSetLevels
Programming Policy-Based Management with SMO - Part 6 - Categories, Subscriptions, Wrapup
How It Works: SQL Server 2005 I/O Affinity and NUMA Don't Always Mix
Database Programming: The String Concatenation XML Trick, Sans Entitization
Database Programming: The String Concatenation XML Trick, Finalized
SQL Server 2008: Interesting Full-Text Dynamic Management Function
Minimal Logging changes in SQL Server 2008 (part-1)
More about sparse columns and column_sets
Which to use: "<>" or "!="?
Hanselminutes #105 - Rocky Lhotka on Data Access Mania, LINQ and CSLA.NET
geekSpeak Recording: Extending SQL Server Integration Services with Reza Madani

Non DB tech
Unit Testing for Silverlight...
IronPython and the DLR march on
The Weekly Source Code 21 - ASP.NET MVC Preview 2 Source Code
Hanselminutes Podcast 104 - Dave Laribee on ALT.NET
The First Rule of Programming: It's Always Your Fault
The Weekly Source Code 20 - A Web Framework for Every Language
It’s common sense, stupid: How Not to Evangelize Unit Testing
Getting LINQ to SQL and LINQ to Entities to use NOLOCK
Google's Design Guidelines
Microsoft give an awesome response to the guy whose XBox was cleaned
Joe Duffy and Igor Ostrovsky: Parallel LINQ under the hood
FLOSS Weekly 27: Ward Cunningham

Non tech - a Google offering for older adults
Open Letter to Comcast: STAY OUT OF MY COMPUTER!
The Laptop Cat [Pic]
Questions on Bear Stearns buyout - shareholders want answers on how the deal was arranged, and gained government approval and financing, all in a few hours, and seemingly without alternative bidders.

Thursday, March 20, 2008

SOT: I found a new 'worst query'

Before you ask, SOT means Slightly Off Topic :-())

Take a look at Hi, I need help on simplyfying this Update query!

I don't even want to count the nested SELECTS, The query is 688 lines long, somehow schadenfreude enters my mind.

Now where does the update come into the picture?

Monday, March 17, 2008

Links Of The Week 20080317

Here are the links for this week

TechNet Radio: SQL 2008 Part 2 of 2: Management, Troubleshooting and Throttling
SQL Server: XQuery/XPath, Retrieval Functions
sp_send_dbmail in a transaction with @query causes unresolvable deadlock
New Features Announced In SQL Server 2008
SQL Down Under show 35 - Roger Doherty - SQL Server 2008 for Database Developers
Applying SQL Server Service Packs and HotFixes
Database Programming: The String Concatenation XML Trick Revisited (Or, Adam Is Right, But We Can Fix It)
Geek City: Nonclustered Index Keys
Sybase iAnywhere Unveils Advantage Database Server 9.0
SQL Server Integration Services and Clustering - confguration gotcha to ensure SSIS works with failover of cluster!

Non DB tech
How the BBC rendered a spinning globe in 1985
I wrote Super Pac-Man: More confessions of an ex-Atari employee
From BFS to ZFS: past, present, and future of file systems - So Great! So Great!
.NET Rocks! #324 - Emre Kiciman on AjaxView
Mashups with SyndicationFeed and LINQ
Microsoft Research Offers Behind-the-Scenes Look at Future of Computing
IBM Researchers Develop World’s Tiniest Nanophotonic Switch to route optical data between cores in future computer chips
Getting up to speed with ASP.NET and the 3.5 Extensions
Using Unity and the ASP.NET MVC Preview 2
PowerShell Plus is now official!
List Of .NET Dependency Injection Containers (IOC).
The Weekly Source Code 19 - LINQ and More What, Less How
You Know, There's a Much Easier Way...

Non tech
Man had $12,000 in debts, repaid $15,000 over 8 yrs, still owes $12,200. Credit card industry stopped him from testifying to congressional panel
XKCD on mythbusters...
JP Morgan "buys" Bear Stearns for $2 a share, Fed flips the actual bill
2008-03-11, Jim Cramer: "No! No! No! Bear Stearns is not in trouble. If anything, they’re more likely to be taken over. Don’t move your money from Bear."
English is our language [PIC]

Wednesday, March 12, 2008

6th Annual Financial Services Developer Conference

I went to the 6th Annual Financial Services Developer Conference today in New York City. This year’s focus was on High Performance Computing (HPC). I must say that I have enjoyed this conference a lot. It seems that SQL Server is very prevalent on the street. The OLAP capabilities of SQL Server are making Sybase slowly disappear from Wall Street. Financial firms are doing some very interesting stuff; there is a hedge fund which records an earnings call, runs it through some voice stress analysis software and based on the outcome of that will short or long the stock. You should hear the stories of how financial firms handle IT, the innovation is happening in the financial markets. I also saw a couple of very cool WPF applications. Check out the demos from Lab49:
Visit to download The Silverlight 2 Retail Financial Services Demonstrator

Financial Services Developer Conference

Carl Franklin from Dotnetrocks recorded a podcast at the event, this podcast was about concurrency and how to handle multi-core programming. You should definitely check it out when it becomes available on dotnetrocks. If you are interested about PLINQ and concurrent programming with .NET then visit this site:

Dotnetrocks Podcast Recording

Tomorrow there are three sessions at the same time that I want to attend
Software Engineering with VSTS
Parallelize you .Net applications with parallel extensions to the .NET framework.
Useful evolution: Programming the New features in SQL Server 2008.

I have my blackcrackberry with me so if you are attending the event tomorrow then send me an email at and maybe we can discuss about this event during lunch.

Sunday, March 09, 2008

Links Of The Week 20080309

Here are the links for this week

Podcast: SQL Down Under show 34 - Geoff Hiten - SQL Server Clustering and SANs
Feature synergy in SQL Server 2008
How It Works: SQLIOSim - Checksums
Change collation in tempdb
Query Optimization DMV du jour - sys.dm_exec_query_optimizer_info
Data Warehouse Scalability Features in SQL Server 2008
SQL Server 2008: Changes to enabling FILESTREAM from CTP-6 onwards
Appending Data Using SQL 2008 Filestream
SSDS is Microsoft's Amazon S3 competitor
Manageability Presentation from the SQL2K8 Launch

Non DB tech
Sun: We'll put Java on the iPhone (InfoWorld)
Filters for Google Reader
Hanselminutes Podcast 103 - Quetzal Bradley on Testing after Unit Tests and the Myth of Code Coverage
ASP.NET MVC Session at Mix08, TDD and MvcMockHelpers
Steve Souders asks: "How green is your web page?"
A Question of Programming Ethics
Why I Love Hackers
The Weekly Source Code 18 - Deep Zoom (Seadragon) Silverlight 2 MultiScaleImage Mouse Wheel Zooming and Panning Edition
Google Releases an API for Contacts

Mix08 Sessions
Adding Instant Messaging to Any Site
Keiji Kanazawa, Nikhil Kothari

Advanced Cross-Browser Layout with Internet Explorer 8
Scott Dickens

Advanced Search Engine Optimization: Generating More Site Traffic from Search
Nathan Buggia

Bringing Hosters and Developers Together with IIS7
Robert Cameron, Tito Leverette

Building AOL's High Performance, Enterprise Wide Mail Application With Silverlight 2
Eric Hoffman, Marc Katchay, Stefan Gal

Building Applications and Services with .NET Framework 3.5
Justin Smith

Building Great AJAX Applications from Scratch Using ASP.NET 3.5 and Visual Studio 2008
Brad Adams

Building Rich Internet Applications Using Microsoft Silverlight 2, Part 1
Joe Stegman, Mike Harsh

Building Rich Internet Applications Using Microsoft Silverlight 2, Part 2
Joe Stegman, Mike Harsh

Creating a RESTful API with Windows Communication Foundation
Haider Sabri, Paul Walker, Viphak Lay

Creating Better User Experiences: Design Methods
Dan Harrelson, Kim Lenox

Creating Better User Experiences: Design Strategy
Dan Harrelson, Kim Lenox

Creating Better User Experiences: Information Architecture
Dan Harrelson, Kim Lenox

Creating Better User Experiences: Interaction Design
Dan Harrelson, Kim Lenox

Creating Rich, Dynamic User Interfaces with Silverlight 2
Karen Corby

Design at Speed of Light
Cinthya Urasaki, Daniel Makoski, John Reid, Tim Wood, Zachary Jean Paradis

Developing Applications with Microsoft Virtual Earth
Chris Pendleton

Developing Applications Using the Model View Controller Pattern
Scott Hanselman

Developing Data Driven Applications Using ASP.NET Dynamic Data Controls
David Ebbo

Developing with Windows Live
Angus Logan

E-commerce Done Right
Gerard Johnson, Matthews Rechs, Nicholas Rockwell, Steve Nelson

Encoding Video for Microsoft Silverlight
Ben Waggoner

Everything You Need to Know about Diagnostics and Debugging on Microsoft Internet Information Services 7
Mai-lan Tomsen Bukovec

Exploring Moonlight: Novell's Implementation of Silverlight on Linux
Miguel de Icaza

From Flash to Silverlight: A Rosetta Stone
Rick Barazza

From Long Tail to Fuzzy Tale: Why "Fuzzy" Is the New Clear
David Armano

Getting Clued In to Experience Management
Lou Carbone

How Design Impacts the Bottom Line
Dave Blakely, David Watson, Jason Brush, Jimmy Kim, Luke Wroblewski, Will Tschumy

I Wanna Go Mobile!
Chad Stoller, David-Michael Davies, Derrick Oien, Eric Breitbard, George Linardos, Michael Platt

Integrating Media in Silverlight Applications
Ed Maia

Integrating Your Site With Internet Explorer 8
Jane Kim

Internationalizing XAML Applications in Windows Presentation Foundation and Microsoft Silverlight
Ken Azuma, Laurence Moroney, Ted Kitamura

Introducing SQL Server Data Services
Nigel Ellis

Is Web 2.0 Sustainable? What business models will work?
Dave McClure, Don Dodge, Kimbal Musck, Robert Scoble, Ryan McIntyre

Keynote I
Dean Hachamovitch, Ray Ozzie, Scott Guthrie

Keynote II
Guy Kawasaki, Steve Ballmer

Making it Simple: Designer/Developer Workflow
Christian Schormann, Ken Azuma, Marcelo Marer, Mark Ligameri, Robby Ingebretsen, Robert Tuttle, Ryan Lane

Microsoft Expression Web: From Comp, to CSS, to Code!
Nishant Kothary, Tyler Simpson

Mobile Devices and Microsoft Silverlight: A Primer on the New Technology
Amit Chopra, David Kline

Monetization 101
Erynn Petersen

Opportunities and Challenges in Mashing Up the Web
Aaron Fulkerson, Andi Gutmans, Michael Scherotter, Sam Ramji, Shawn Burke

Overview of Microsoft Ad Formats
Alam Ali

Partying with PHP on Microsoft Internet Information Services 7
Drew Robbins

Real World Design: Working with Silverlight and WPF in the Design Studio
Beau Ambur, Chip Aubry, Chris Bernard, Nathan Dunlap, Rich Weston

Real-World AJAX with ASP.NET
Nikhil Kothari

RESTful Data Services with the ADO.NET Data Services Framework
Pablo Castro

Silverlight and Advertising
Eric Schmidt

Silverlight as a Gaming Platform
Joel Neubeck, Scott McAndrew

Social Networks: Where Are They Taking Us?
David Morin, Garrett Camp, Guy Kawasaki, John Richards, Joseph Smarr, Marc Canter

The Back of the Napkin: Solving Design Problems (and Selling Your Solutions) with Pictures
Dan Roam

The Business of Microsoft Silverlight
Danny Riddell

The Human Brain Relationship: Advanced and Adaptive User Interfaces
Ya'akov Greenshpan, Yochay Kiriaty

The Open Question
Andi Gutmans, Miguel de Icaza, Mike Schroepfer, Sam Ramji

Tools and Applications for Publishers
Rishi Bal

Touch Me: Where Are Interfaces Going?
Chris Bernard, Dale Herigstad, Daniel Makoski, Dave Wolfe, Doug Cook, Yoshihiro Saito

Understanding Microsoft Partner Programs for Designers, Developers, and Agencies
Bill Vlandis, Hakan Soderbom

Using Microsoft Silverlight for Creating Rich Mobile User Experiences
Giorgio Sardo

Virgins, Spaceships and Hob-nailed Boots!
Paul Dawson

Web 2.0 and Beyond: What Is the Business Reality?
Bryan Biniak, Chris Saad, Frank Arrigo, Loic Le Meur, Tim Kendall

Welcome to Internet Explorer 8
Chris Wilson

What Is the face of the next Web?
Anthony Franco, Chris Bernard, Garrick Schmitt, Mark Kurtz, Paul Dain, Tjeerd Hoek

What's New in Windows Presentation Foundation 3.5
Rob Relyea

What's the Secret Formula?
Dan Harrelson, Daniel Makoski, Jensen Harris, Mike Schroepfer, Nishant Kothary

Windows Presentation Foundation Using Microsoft Visual Studio 2008
Mark Wilson-Thomas

Working with Data and Web Services in Microsoft Silverlight 2
Eugene Osovetsky

Non tech
Battle Plans [Pic]
History: From Moses to Hitler, Mike Tyson and Bill Clinton ( amazing pic )
Olympics clean up Chinese style: Inside Beijing's shocking death camp for cats [PICS]
Apple, Google... And then, You. [Pic]
Geek Flow Chart [Pic]
Fruits. Water. Action! (pics)