ê¿êspi20.com

Ok — enough of my blathering about feeling off and being down. I need to get back to blogging about something besides myself — talk about self centered! Sheesh!

At work recently I had an issue crop up that I thought might make a worthy blog post.  It’s a SQL thing, so if that’s not what you’re here to read, you might want to move on to the archives. There are plenty of read-worthy posts there — I’ve only written some 750 or so over the past two years….(modest shrug)….not all that many, truly.

Ok  SQL — get your mind on the subject!

With the advent of SQL 2005 we have a plethora of new tools at hand. (*love that word plethora — it rolls off the tongue even when you’re only reading it). A couple of new data types and a new variable type.

The TABLE variable. What an idea! Now you no longer have to create those # tables that hog up the tempdb. Or the ## tables when the # table doesn’t suit your needs. Oh you can still create and use those! They aren’t deprecated or anything and there are times when you WANT to use them instead of a table variable.

Let’s look at a table variable though — you’ve never seen one in action before have you??

[code]

*/just like with any variable, you begin by declaring it */

declare @mytable table()

*/now wasn't that easy?*/

[/code]

“But that’s not all there is to it surely!” you say….no, not quite.

Just like with other types of tables, you can name your columns, give them data types and say if they are to be null or not null. You can even declare a primary key or identity column.

[code]

declare @mytable table (

col1 char(15) [null, not null][primary key]
,col2 int identity (1,1)
,col3 numeric (15,9)
,col4 varchar(50) [null, not null])

[/code]

I don’t want to lull you into a false sense of security; working with variable tables can be a bit tricky too. While you can easily insert data into them, when you go to join them in queries, you HAVE to alias them.

[code]

select a.col1, a.col2, a.col3, b.cola, b.colb, b.colc

from @mytable a join myothertable b on

a.col2=b.cold

where someconditionismet

[/code]

Once the code runs, the variable table disappears *pouf*. Which means you don’t have to do that clean up like you do with # or ## tables  – no “drop table #mytable” in other words.

That’s because the variable tables (except under certain circumstances) are not written to the tempdb.

They are executed entirely in memory.

“So what exactly does that mean?” you astutely ask.

It means that a portion of your RAM is used to hold the data in memory until the query executes and the variable is no longer in use.

Now that’s all fine and dandy if it’s a simple table that holds a few hundred or even a few thousand rows. But what if it’s going to have to hold a lot more data than that? And what if your memory is being used by another process and isn’t available for use by the variable table? What then? Huh? Huh? Huh?

Well, THEN it writes to the disk — usually the one your tempdb is located on. Is that such a bad thing? Well, it could be. Because it’s writing to the disk in this fashion it’s more like an application that’s writing to the disk because there isn’t enough memory to support what it’s doing — it slows EVERYTHING down. And you know what happens when EVERYTHING on your SQL Server gets slowed down, don’t you? The phones start ringing and the users start whining and your day just went south!

Another drawback to variable tables is that beyond putting a primary key on them, you cannot index them.

That means that every query results in a full table scan which is another performance hit. Again, not that big of a deal when you’re only talking about a few rows (or a few hundred or even a few thousand rows).

In my case , the table in question was part of a loop. It held 10K rows and a loop through a 2,200 row table for matches was running. Not only was it bringing my server nearly to it’s knees every time it ran, but also took FOREVER to complete (well, if you want to get technical, it was taking 7-9  minutes — isn’t that forever for a SQL query to run???). Making that table a # table and putting an index on it was the key to reducing that heartburn!

Variable tables — they are tres cool, handy as all get out, but have some gotchas that you need to watch out for.

Till next time — happy data mining!

ê¿ê