Jeff Smith Blog

Random Thoughts & Cartesian Products with Microsoft SQL Server

UNPIVOT: Normalizing data on the fly

Everyone seems to want to "pivot" or "cross tab" data, but knowing how to do the opposite is equally important. In fact, I would argue that the skill of "unpivoting" data is more useful and more important and more relevant to a SQL programmer, since pivoting results in denormalized data, while unpivoting can transform non-normalized data into a normalized result set. Read more →

You Know, There's a Much Easier Way…

Let's say you are struggling on a programming project. Your code is growing exponentially and becoming more convoluted by the day, and it is clearly out of control. You're getting run-time errors, compile-time errors, wrong output, no output, endless loops, your machine is overheating, and perhaps you are starting to feel like you might be a little over your head. Read more →

DataTable, DataView and CreateDataReader

Here's something I was not aware of in .NET 2.0+ that I recently discovered. There is a new DataTableReader class that implements IDataReader so that you can loop through an in-memory DataTable using the same interface that you would to loop through a SqlDataReader. Read more →

Does SQL Server Short-Circuit?

I got an email recently regarding one of my early blog posts from the olden days: Steve Kass wrote about your post:"there is no guarantee that WHERE <filter 1> OR <filter 2> will be optimized so that the filters are evaluated in the order typed". Read more →

Top N Percent per Group

Here's a good question in the feedback from my post about using the T-SQL 2005 features to return the Top N per Group of a result set: Sani writes: What about Top n Percent per Group? Read more →

A Follow-Up on Programming Forums ….

I have to wonder: is it a sign of good writing when people interpret your words in multiple ways, or is it a sign of bad writing? I really don't know – I think it can go either way, I suppose it depends on what your intent is. Read more →

Interesting Database Modeling Dilemma

Let's say you have a database that contains Companies, Products and Stores. Products and Stores are unique to each company – i.e., they are not "shared" across companies. create table Companies(CompanyID int identity primary key,Name varchar(100))create table Products(ProductID int identity primary key,CompanyID int references Companies(CompanyID) not null,Name varchar(100))create table Stores(StoreID int identity primary key,CompanyID int references Companies(CompanyID) not null,Name varchar(100)) OK, looks very simple and standard, right? Read more →

SELECT DISTINCT and ORDER BY

Let's take a look at another one of those stupid, arbitrary SQL Server error messages that Bill Gates clearly only created because Micro$oft is evil and incompetent and they want to annoy us (and probably kill baby squirrels, too): Msg 145, Level 15, State 1, Line 4ORDER BY items must appear in the select list if SELECT DISTINCT is specified. Read more →