When you need to summarize transactional data by Month, there are several ways to do it, some better than others. What to ultimately choose depends on your needs. Techniques to Avoid
Read more →
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 →
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 →
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 →
A occasional question seen in the forums, which was just recently asked today, is:
"I know I can use SELECT * FROM tableto get all of the columns from a table, but is there a way to write SELECT * minus columnXFROM Tableto get all of the columns except for certain ones?
Read more →
A quick follow up to the "Nested WHERE-IN" anti-pattern post from yesterday … If you didn't get a chance, be sure to read the comments from that post as well, there are some great points in there so far.
Read more →
There's a fascinating technique that I see many beginners use when writing SQL statements, and I call it the "Nested WHERE-IN" anti-pattern. It is, unfortunately, a common SQL technique used to avoid JOINS at all costs.
Read more →
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 →
Introduction SQL is a set-based language, and often we wish to pass sets of data as a parameter to a stored procedure. For example, you might wish to pass a set of Customers to an invoice generating stored procedure, or a set of employees which for you’d like to calculate a particular bonus.
Read more →
Sometimes, it appears that a necessary solution to common SQL problems is to join a table to itself. While self-joins do indeed have their place, and can be very powerful and useful, often times there is a much easier and more efficient way to get the results you need when querying a single table.
Read more →
That's right boys and girls, it's what you've been waiting for all weekend: Another edition of the mailbag! Damian writes:
Hi I have a tricky SQL question that I have been trawling the net and workmates to find an answer.
Read more →
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:
Read more →
(Note: Updated 5/15/2007 @ 12:25 PM EST to show another possible solution with different results.) Here's my response to the SQL Challenge given here. The challenge involves having a schedule table with days and times, and displaying all available consecutive free time slots to schedule an event.
Read more →
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 →
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 →
A Google search for the phrase
sql "case statement" returns 127,000 results. Meanwhile, if we do a search for the phrase sql "case expression" we get back only 43,900 results.
Read more →
Previously, I had written that UNION ALL (combined with a GROUP BY) is a really quick and easy way to compare two tables. You don't need to worry about NULLS, the code is fairly short and easy to follow, and you can view exceptions from both tables at the same time.
Read more →
Thinking "Set-Based" So, I hear you're a "set-based SQL master"! As Yoda once said, you've "unlearned what you have learned". You've trained yourself to attack your database code not from a procedural, step-by-step angle, but rather from the set-based "do it all at once" approach.
Read more →
Be sure to visit SQLTeam.com to check out my latest article, Custom Auto-Generated Sequences in SQL Server. It addresses a common question we see in the SQL Team forums. From the intro:
Read more →
As many of you know, I strongly recommend that you avoid using RIGHT OUTER JOINs, since they make your SQL code less readable and are easily rewritten as LEFT OUTER JOINs.
Read more →