In my SQLBulkCopy article, I mentioned that you can quickly copy data from anything that implements IDataReader to a SQL Server table using SQLBulkCopy (new in .NET 2.0). In this SQLTeam forum post, Jesse Hersch (jezemine) tells us that SQLBulkCopy only actually uses 3 methods of the interface.
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 →
My latest article has just been posted over at SQLTeam.com:
Use SqlBulkCopy to Quickly Load Data from your Client to SQL Server .NET Framework 2.0 introduces a very handy new class in the System.
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 →
I had to do some data clean up the other day, and really needed some regular expression replacements to do the job. Since .NET has a great RegularExpressions namespace, and since SQL 2005 allows you to integrate .
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 →
I just discovered Joe Celko's weblog; it is really entertaining, at least the 5-6 posts I've read so far! It seems the "blog" is actually just a collection of questions and Celko's rather, umm, "honest" responses from various newsgroups and forums.
Read more →
Time for another exciting edition of the mailbag! Maxime writes:
Hi,First of all, your class is really nice and it handles my problem really well.I would like to ask you something about the class.
Read more →
Here is a comparison of a FULL OUTER JOIN and a CROSS JOIN to achieve the same results. See here for more information on this. – Prepare environment:create table A (id int primary key, descr varchar(100))create table seq (seq int primary key)create table B (id int references A(id), seq int references seq(seq), value money, primary key (id,seq))create table C (id int references A(id), seq int references seq(seq), value money, primary key (id,seq))go– Create sample data:insert into Aselect number, 'Name ' + convert(varchar(10), number)from master.
Read more →
(see here for more information on this ) – set it all up: create table A (id int primary key, descr varchar(100))create table B (id int references A(id), seq int, value money, primary key (id,seq))create table C (id int references A(id), seq int, value money, primary key (id,seq))goinsert into Aselect number, 'Name ' + convert(varchar(10), number)from master.
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 →
Question: How do you create a DateTime in a specific format in SQL Server? Answer: You don't. You can't. The only way to translate a DateTime into a specific format is to convert it to a VARCHAR or other "string" data type.
Read more →
Learn the Database Schema Before you can write any reports off of any system, you must be familiar with the schema you are working with. Print out the data dictionary, schema diagrams, documentation -- anything you can find that will help you work with the database.
Read more →
It's been a while, so let's open up the old mailbag! Jeremy writes:
Can you spare a few minutes to show me a possible way to restrict a column in a table using a check constraint to only allow characters in a given range of the ASCII table?
Read more →
Studies have shown that the "F1" key is the least commonly pressed key on today's keyboards! Ok, well maybe not actual studies, but from my own experience, I am convinced that on many keyboards here around the world the F1 key still has that shiny "new key" look and smell because it's never been used.
Read more →