Category: Uncategorized

There’s still time to register for the 24 Hours of PASS, a series of 24 one-hour sessions being presented on May 19-20, 2010. There’s an amazing lineup of speakers, including:

• Brian Knight
• Kevin Cox of SQL CAT
• Donald Farmer
• Jessica Moss
• Kevin Kline
• and more!

I participated in this last year, and it was great fun. I managed to even watch 2-3 sessions across the 24 hours, but work definitely got in the way. 

This year I’m not participating since this is high school graduation week for my son, and I’m actually on vacation these days. So I’ll miss out, but I’m looking forward to seeing a few of these sessions on recordings later on.

Be sure to register today, and block out some time on your calendar for the sessions you’re interested in.

I think Twitter is a graffiti wall that emulates a series of hallway conversations near the world’s largest food court.

It’s time again for T-SQL Tuesday and this month the topic is LOB, or BLOB data. You can read more about T-QSL Tuesday in the entry above, and check out tSQL2sDay.com for the redirect.

For you older DBAs, this refers to text, ntext, and image data. For the youngsters, you know there are newer, more complete data types in SQL Server 2005 and above that are the (n)varchar(max), the FILESTREAM types (SQL 2008), the XML, spatial (2008) and CLR data types. Basically the data types that hold data exceeding the size of a page (8kb) up to the maximum size of 2^32 bytes.

The Age Old Debate

When I started working with SQL Server, it was with two third party products that my company had purchased. One was an imaging piece of software designed to store scanned documents and the other was a piece of fax software that received faxes electronically and stored them in SQL Server. One of these stored the actual image in the database as the image data type and the other stored the image in the file system with a “pointer” or path to the file in the database.

This used to be a constant debate, and indeed, even with the advent of the FILESTREAM data type, there still is a debate about which way to manage data such as image files, audio files, etc. There’s a great white paper from Microsoft Research you should read if you are considering storing LOB data in your database. It basically notes that if the files are 256kb or smaller, on overage, then keep them in the database. If they average greater than 1MB, use the file system and Filestream. In between, well, you might pick the one that works best for you.

Personally I like both methods. Keeping stuff in the database simplifies backup and restore procedures. I once had to restore a piece of softawre that used the store-paths-in-the-database and we had different paths on the DR server. That was not fun trying to sort out. You also have a clearer idea of how much space you are using with everything in the database.

However in the file system you have other advantages. You can have the web server just send images from its cache instead of requiring processing from the database server. I have always been concerned over load, so this appeals to me. It also lets someone that’s not a database person easily view or work with images in things like Paint if they need to.

So What Should I Do?

If you have images, I’d use the MS Research guidelines, but my real recommendation is that you learn how to work with FILESTREAM, learn the basic API and code needed, and integrate it if you can. One big rason to consider FILESTREAM and storage outside of the database is that many of us look to put our data and log files on RAID 1 or RAID 10 storage arrays. Those are expensive, and much of this data could easily be served from RAID 5 volumes, at a much lower storage cost. When you have a lot of data, this can be a significant cost.

If you have large volume of binary data, like audio or video files, you might consider getting a dedicated appliance at some point as well. I’ve worked with one of these before FILESTREAM, and it worked pretty well. These are Remote Blob Storage (RBS) features built into Sharepoint 2010, and I am guessing that as we accumulate more of this data, and as Sharepoint is deployed more, it makes sense to use these features.

So what should you do? It depends on what will work best in your environment. Learn a bit more about Filestream and make the best decision that you can.

I’ve been trying to learn more about the T-SQL changes in SQL Server 2005/2008 over time. I don’t have requirements to write a lot of T-SQL, so I have to futz around with it and just try things I see in articles or presentations. One that I’ve been meaning to play with for some time is the PIVOT command.

This command is designed to (from BOL): “change a table-valued expression into another table. PIVOT rotates a table-valued expression by turning the unique values from one column in the expression into multiple columns in the output, and performs aggregations where they are required on any remaining column values that are wanted in the final output.”

That’s confusing, but what this basically does is build a cross tab report. I hate doing this stuff in the db, since I think it’s a waste of resources, but I’m learning, so let’s try something. The examples in BOL work, but I wanted to check them, so I started with Jeff Moden’s Cross Tabs and Pivots, Part 1. That was easier to read, and it’s a great introduction. So I build a table.

CREATE TABLE Repairs 
( AUTO VARCHAR(20) 
  , RepairDate DATE 
  , Repair VARCHAR(20) 
  , cost NUMERIC(6, 2) 
) 
GO 
INSERT Repairs SELECT 'Prius', '7/1/2007', 'Oil Change', 75 
INSERT Repairs SELECT 'Prius', '10/1/2007', 'Oil Change', 75 
INSERT Repairs SELECT 'Prius', '1/1/2008', 'Oil Change', 75 
INSERT Repairs SELECT 'Prius', '4/1/2008', 'Oil Change', 75 
INSERT Repairs SELECT 'Prius', '7/1/2008', 'Oil Change', 75 
INSERT Repairs SELECT 'Prius', '9/1/2009', 'Tires', 360 
INSERT Repairs SELECT 'Truck', '8/1/2007', 'Oil Change', 95 
INSERT Repairs SELECT 'Prius', '11/1/2007', 'Oil Change', 95 
INSERT Repairs SELECT 'Prius', '2/1/2008', 'Oil Change', 95 
INSERT Repairs SELECT 'Prius', '5/1/2008', 'Oil Change', 95 
INSERT Repairs SELECT 'Prius', '8/1/2008', 'Oil Change', 95 
INSERT Repairs SELECT 'Truck', '2/1/2009', 'Tires', 560 
INSERT Repairs SELECT 'Porsche', '5/1/2008', 'Oil Change', 120 
INSERT Repairs SELECT 'Porsche', '4/1/2009', 'Oil Change', 120

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

I have repairs and some dates for my cars. What I was trying to do is take the table and pivot it so I can see the cost per repair per year per car. In other words, take this:

and turn it into this:

I looked over the examples I had, and then wrote this:

SELECT 
    Datepart( yyyy, RepairDate) 'Year', 
    AUTO, 
    Sum(Coalesce([Oil Change],0)) 'Oil Changes', 
    Sum(Coalesce([Tires],0)) 'Tires' 
FROM ( SELECT AUTO, RepairDate, Repair, Cost 
          FROM repairs 
       ) AS Expenses 
PIVOT( SUM( cost) FOR Repair IN ([Oil Change], [Tires])) AS pvt 
Group BY 
    Datepart( yyyy, RepairDate) 
    , AUTO 
ORDER BY  Datepart( yyyy, RepairDate) , Auto

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

Which works. I choose the columns I wanted (in this case ‘Oil Changes’ and ‘Tires’) to pivot on and then wrote the statement. It works, but it seems hard to read, to me. I tend to agree with Jeff’s assertion in his article that a simple CASE statement is easier to read.

SELECT 
    Datepart( yyyy, RepairDate) 'Year', 
    AUTO, 
    SUM(CASE WHEN REPAIR = 'Oil Change' then Cost else 0 end) 'Oil Changes', 
    SUM(CASE WHEN REPAIR = 'Tires' then Cost else 0 end) 'Tires' 
FROM repairs 
Group BY 
    Datepart( yyyy, RepairDate) 
    , AUTO 
ORDER BY  Datepart( yyyy, RepairDate) , Auto

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

But that’s me. I haven’t messed with dynamic columns or other changes, but for now I’m not sold that a PIVOT really is that helpful.