Showing posts from December, 2016

Temp Tables vs Table Varibles: The Great Debate

There seems to be a lot of confusion around the differences between  temp tables and table variables (in the classic sense; I'm not talking about in memory table types or anything here). Some people say you should only use table variables. Some people say you should only use temp tables. Most people caution a more nuanced approach. Here I want to cover what I think are the biggest issues in an attempt to shed some light on this surprisingly tricky topic.

Statistics This is usually the most important factor when considering which one to use. It boils down to two facts, and everything else flows from these: Temp Tables maintain statistics, Table Variables do not.

Table Variables Because table variables maintain no statistics, the Query Optimizer always assumes the contain exactly one row. As a result, any joins against a table variable, unless explicitly told to behave otherwise will (probably) alwaysbe a nested loop join.

As long as the number of rows in the table variable are small…

65335 Factorial


Beware the Errors of SSIS

SSIS is a bit of a touchy subject where I work, and the more I use it, the more I sort of understand why.

While SQL Server is my background and I consider myself an expert in TSQL (not like, MVP level or anything), I don't do too much in SSIS. We have a home grown architecture which, in many ways, mimics functionality SSIS can do, and, for our purposes, usually works better and comes with fewer headaches. That said, there are some operations SSIS is simply hands down the best tool for the job. But I didn't come here to talk to you to day about when to or not to use SSIS. No dear reader, I come with a warning/complaint about how it surfaces error.

The description of how it surfaces errors is "not very well". It's undoubtedly something you get better at teasing out over time, but I think largely because of the breadth of systems it needs to interact with, surfacing meaningful errors for specific stages can often seem to have absolutely nothing to do with the error …

Big Crazy Linked Server Query

I got bored and was fiddling around with a statement I'd been doing manually in several steps to try to make it as easy to use (not read) and as quick as possible. The rundown is this:

    I have a query I want to run which spans two databases.

    The relevant schema for these two servers is as follows:

-- Server 1     -- Has about 500m rows     create table dbo.orders     (         order_id varchar(55) primary key clusterd,         Data varchar(8000) -- just a placeholder to show that there's other stuff in here     )     -- Server 2     create table dbo.orderLog     (         order_id varchar(55) primary key clustered,         PayloadId int unique,         OtherData varchar(8000) -- Placeholder for other columns.        --Not that it matters, but this data is different than the data on server 1     )     -- Has about 20 million rows     create table     (         RecordId int identity(1,1) primary key clustered,         PayloadId int,         TriggerDate dateti…