There is no equivalent to the SSIS Lookup transformation in T-SQL – but there is a workaround if you’re careful. The big issue that you face is about the number of rows that you connect to in the Lookup. SQL Books Online (BOL) says: If there is no matching entry in the reference dataset, no join occurs. By default, the Lookup transformation ...

A post I’ve been meaning to write for a while, good that it fits with this month’s T-SQL Tuesday, hosted by Joey D’Antoni (@jdanton) Ever since I got into databases, I’ve been a fan. I studied Pure Maths at university (as well as Computer Science), and am very comfortable with Set Theory, which undergirds relational database concepts. But I’ve ...

This past weekend saw the first SQL Saturday in Melbourne. Numbers were good – there were about 300 people registered, and the attendance rate seemed high (though I didn’t find out the actual numbers). Looking around during the keynote, I didn’t see many empty seats in the room, and I knew there were 300 seats, plus people continued to arrive as ...

There are quite a few reasons why an Index Scan is better than an Index Seek in the world of SQL Server. And yet we see lots of advice saying that Scans are bad and Seeks are good. Let’s explore why. Michael Swart (@MJSwart) is hosting T-SQL Tuesday this month, and wants people to argue against a popular opinion. Those who know me and have heard ...

I feel like every database project has major decisions now, which are remarkably fundamental to the direction that’s going to be taken. And it’s almost as if new options appear with ever-increasing frequently. Consider a typical database project, involving a transactional system to support an application, with extracts into a data warehouse ...

Suppose your SQL table has a bunch of spatial points (geographies if you like) with an order in which they need to appear (such as time) and you want to convert them into a LineString, or path. One option is to convert the points into text, and do a bunch of string manipulation. I’m not so keen on that, even though it’s relatively ...

“It just runs slow these days” I’m sure you’ve heard this, or even said it, about a computer that’s a few years old. We remember the days when the computer was new, and it seemed to just fly – but that was then, and this is now. Change happens, things erode, and become slower. Cars, people, computers. I can accept that cars get slower. They lose ...

There is a lot of discussion about “the cloud”, and how that affects people’s data stories. Today the discussion enters the realm of T-SQL Tuesday, hosted this month by Jorge Segarra. Over the years, companies have invested a lot in making sure that their data is good, and I mean every aspect of it – the quality of it, the security of it, the ...

CONVERT_IMPLICIT isn’t the only problem with getting data types wrong. You might have the right type, but what if the length is wrong? This post will look at both getting the type wrong and getting the length wrong too. Let’s do some testing. We’ll need a table with indexes. I’d normally use one of the AdventureWorks versions for this, but as ...

Using a hammer to push in a screw isn’t a good idea, no matter how good the hammer is. We all know that. and yet there are times when we get frustrated at the ‘right tool’ and opt for the one that will work. Unfortunately, there are plenty of examples in the IT space – the topic of which is this month’s T-SQL Tuesday, hosted by Rick Krueger ...