Jeff Smith Blog

Random Thoughts & Cartesian Products with Microsoft SQL Server

Is it a String Literal or an Alias?

Every now and then I see T-SQL code written like this: select somecolumn as 'columnname'from sometable Notice that 'columnname', despite its appearance, is not a string literal or a string expression, it is the alias that we are assigning to the column somecolumn. Read more →

Working with Date and/or Time values in SQL Server: Don't Format, Don't Convert – just use DATETIME

The Importance of Data Types Imagine that SQL Server only provided two data types: the MONEY data type to store numeric values, and VARCHAR to store text. If you are designing a database in this scenario and you need to store or return integer values, which data type – MONEY or VARCHAR – would you use? Read more →

More on GROUP BY; Examining SUM(Distinct)

I've written a two part article on using SQL GROUP BY clauses over at SQLTeam.com. It's always a common topic of discussion and confusion amongst beginner and intermediate SQL programmers alike, so I thought I'd write a fairly long and hopefully comprehensive piece that takes a common summary report request and works towards the solution step-by-step. Read more →

Retrieving Identity Values When Inserting Multiple Rows

Suppose you have the following tables: create table Customers (CustomerID int identity primary key, CustomerName varchar(100) not null)create table AddressTypes (AddressType varchar(10) primary key)create table CustomerAddress (CustomerID int references Customers(CustomerID),AddressType varchar(10) references AddressTypes(AddressType),Street varchar(100),City varchar(100),State varchar(2),ZIP varchar(20),primary key (CustomerID, AddressType)) This is a simple schema for which a Customer can have multiple addresses, one per AddressType. Read more →

SQL Server 2005: Specifying Partitions for Aggregate Functions

Did you know that a new feature in SQL Server 2005 allows you to specify an OVER partition for aggregate functions in your SELECT statement?  For example: select customerID, productID, orderDate, orderAmount, sum(orderAmount) OVER (Partition by CustomerID) as CustTotalfrom OrderscustomerID productID orderDate orderAmount CustTotal———– ———– ———————– ————- ———1 1 2007-01-01 00:00:00. Read more →

Criteria on Outer Joined Tables

As this nice SQLTeam article explains, when using an OUTER JOIN, you should put criteria on the outer table in the join condition, not in the WHERE clause. However, I often see a "workaround" to avoid this simple and solid rule, which might seem to work but actually doesn't. Read more →

Hamming Distance Algorithm in SQL

Here's a simple User-Defined Function implementation of the Hamming Distance Algorithm for SQL Server: create function HamDist(@value1 char(8000), @value2 char(8000)) returns int as begin declare @distance int declare @i int declare @len int select @distance = 0, @i =1, @len = case when len(@value1) > len(@value2) then len(@value1) else len(@value2) end if (@value1 is null) or (@value2 is null) return null while (@i <= @len) select @distance = @distance + case when substring(@value1,@i,1) ! Read more →