Table variables in SQL Server

Working in SQL requires a mind-shift from procedural thinking to set-based thinking.  Although you can get SQL to operate one record at a time, it's usually slower, and almost always painful to force SQL to work like that.

One of the implications of set-based operations is that traditional single-value variables can't quite handle many processing needs -- you need a collection-like variable, and traditionally, that's meant a temporary, or temp table.  Temp table implementation is pretty similar across database engines, but it's also slightly cumbersome.  In many cases, SQL Server's Table variables are a better solution.

Although Microsoft introduced table variables with SQL Server 2000, I hadn't had occasion to try them out until recently.  Like cursors, temporary tables have usually been just enough of a pain that I avoided them until I really needed one.


CREATE TABLE #mystuff
(
id INT,
stuff VARCHAR(50)
)

-- do stuff with your table

DROP TABLE #mystuff

The thing that always messed me up when using temp tables was the DROP TABLE at the end.  When developing or debugging, or when working with ad-hoc SQL, I frequently missed the DROP, which showed up shortly afterwards when I tried to CREATE a table that already existed.

Table variables eliminate the need for the DROP, because they are reclaimed automatically as soon as they go out of scope:


CREATE TABLE @mystuff
(
id INT,
stuff VARCHAR(50)
)

-- do stuff with your table

Other advantages: for small tables, Table variables are more efficient than "real" temp tables, and they can be used in user-defined functions (unlike temp tables).  Like temp tables, you can create constraints, identity columns, and defaults - plenty of power to help you work through set-based problems.

For more on Table variables, see the following articles:

Table Variables In T-SQL

Should I use a #temp table or a @table variable?

Reblog this post [with Zemanta]

New syncing options for WinMo phones

Several mobile phones
Image via Wikipedia

Microsoft and Google have each announced syncing tools for Windows Mobile phones recently, but based on what I'm seeing, I'm sticking with a service you've probably never heard of.

Microsoft announced "My Phone" last week, and today announced that it will be available for free.  At present, it's in limited beta, but I'd expect it to be unleashed on willing participants pretty soon.  This service looks to be pretty limited, though -- it looks to be a great way to back up your phone, but not too much beyond that.

Today, though, Google announced plans to support syncing for contacts and calendars to a bunch of phones, including Windows Mobile.  Woot!  A closer look at the fine print, though confirms that this (like all new Google features) is a beta feature, and may not be completely baked yet.  Two-way sync, in particular, seems to be iffy for Windows Mobile devices.

Microsoft Office Outlook
Image via Wikipedia

So who needs these services, anyway? WinMo phones already synch to Outlook just fine, don't they?

Personally, I want something like this because I don't use Outlook anymore.

A few months ago, in a fit of Vista-inspired digust, I loaded Ubuntu on my home desktop, and vowed never to be stuck on a single desktop OS again.  I'd been using Outlook, and this was the only way I'd been able to sync contacts and calendars to my Windows Mobile phone, and so began a long quest for a wireless sync option for my phone.  I was amazed at how difficult this actually turned out to be, but I ended up using a service that I've been pretty happy with.

Continue reading "New syncing options for WinMo phones"

Countdown to release, according to Microsoft

Chart showing the stages in the software relea...
Image via Wikipedia

A while ago, I pointed out a Microsoft development team that was doing a great job of giving us glimpses inside the sausage factory.  In that instance, the Windows Home Server team was showing us how bugs are managed late in the release cycle.

Now, Microsoft is opening up again - on a larger scale this time.  A blogger on the Windows 7 team  has written a really informative post on the process the Win 7 team expects to use to move from Beta to General Availability.

If you ship commercial software, you need to understand the terms used in this article.  You also need to make sure your developers and your boss understand them.  These milestones are the dates that drive your release, and it's critical that everyone in your company shares an understanding of what these milestones mean.

If you develop in-house software, you may not use these terms, but you should still understand the concepts.  The same idea applies to size: smaller projects will have fewer public milestones.

Once you've decided on how you're going to define these milestones for your organization, keep track of projected and actual dates for a couple of releases.  Now, as you plan future releases, you've got some valuable data to help you: all things being equal, you'd expect to spend similar amounts of time in each stage of the release for similarly-sized features.  Use these ratios as a sanity check against your project plan; if your ratios are way off, you'd better be able to explain why.

Note that if you change the meaning of these terms every time you use them, they instantly become useless for estimates and measurements, and quickly become useless as a means of communication, so pick a quantifiable definition you like, and stick to it!

Reblog this post [with Zemanta]

What, exactly, is wrong with “Private Clouds”?

Racks of telecommunications equipment in part ...
Image via Wikipedia

I recently saw an interesting post by Gordon Haff that claims that cloud computing concepts can't really be applied to enterprises smaller in scale than the Googles, Microsofts, and Amazons of the world.

Humbug, I say.  I certainly didn't have that impression when I learned about Azure.

I'll concede that there aren't too many enterprises that have the scale of Google or Microsoft, but there are quite a lot of enterprises that would see benefits of cloud computing concepts in their internal data centers.

You see, the "infinite scalability" promise of cloud computing is only one of the benefits that cloud computing promises.  Most of the concepts we're seeing in cloud computing were already trends in big data centers before they turned into cloud features.

Continue reading "What, exactly, is wrong with “Private Clouds”?"