Jeff's SQL Server Weblog

RSS Feed Homepage

Articles: 1, 2, 3

Data Types -- The Easiest Part of Database Design
2007-07-03 16:13:00
I see it time and time again in forums -- "dates" that don't sort properly, "numbers" that don't add correctly, "boolean" data with 10 different values, and so on ... Since we are rarely provided any DDL to review, it often takes many posts going back and forth until we finally realize: "wait ... you aren't using a datetime data type to store these dates?!" read more...
More About: Design , Data , Database , Part , Types

Retrieving Identity Values When Inserting Multiple Rows
2007-07-02 18:31:00
You're INSERTing multiple rows from an un-normalized import table. Each row is assigned an identity primary key. You know you can use scope_identity() to retrieve one identity at a time, but how do you retrieve a whole set of them? Are cursors the only answer? As usual, it depends on having logical specifications and a good design. read more..
More About: Values , Identity , Ving , Trie

Cartesian Product Updates!
2007-06-26 17:43:00
A couple of quick blog updates for those that are interested: A Comment Milestone! Recently, I realized that I have received 1,000 comments since this little blog was started way back in September 2003.  Has it really been that long!?  Some of the comments are trackbacks, but overall, it's pretty amazing to think about.  Only about 30% of them are people calling me a moron, which is an excellent ratio and well above what I expected.  In all seriousness, though, I love getting feedback, and it is a lot of fun to debate techniques and ideas, so please be sure to leave me a note whenever you enjoy a post or if you think it could be better. FeedBurner I have set my blog up with FeedBurner, which means you can subscribe to the default RSS feed, or the FeedBurner one.  In addition, if you are interested, a great new feature is that you can get email notifications when new posts are added to my blog by clicking here.  It's easy to use and works really well.  All of this is featured i...
More About: Product , Updates , Prod , Cart , Artesian

Creating Array or Table Parameters for a Stored Procedure
2007-06-26 15:36:00
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. SQL Server already has everything you need to do this, and you don't need CSV or XML strings.read more
More About: Creating , Table , Eating , Meters , Array

SQL Data Modeling: Entities versus Attributes
2007-06-19 17:47:00
Introduction There?s a handy little rule of thumb I use when developing my database models.  It comes up often in situations like this: If we are storing phone numbers in the database, should AreaCode be a ?free-form? char(3) column?  Or should it be a foreign key reference to an AreaCodes table? Well, like most things in life, it depends.  There is no universal correct answer.  Some people will say that if you are storing the same piece of information more than once in your tables, you don?t have a normalized design.  Again, that is sometimes true, sometimes not.  For example, I store the number 0 and the first name ?John? lots of places in my data ? should I have tables defining all of these values?  Should every column in all of your tables, then, be a foreign key relation to some table that defines everything?  Of course not. It gets confusing because every database is different, depending on what it is modeling.  When trying to determine how to handle the "area code situ...
More About: Data , Modeling , Versus , Bute , Tribute

What I learned about SQL from using Access
2007-06-13 19:12:00
Microsoft Access / JET SQL doesn't support cursors; the only way to process rows one by one is using VBA and opening a Recordset. This means that you're stuck using set-based "queries". So ... is this a good thing or a bad thing? A very, very good thing -- in fact, it's what makes Access a great tool for learning SQL! read more...
More About: Learn , Learned

Using GROUP BY to avoid self-joins
2007-06-12 17:35:00
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...
More About: Group , Avoid

Slightly more dynamic ORDER BY in SQL Server 2005
2007-06-05 16:24:00
There's a very interesting way of handling complicated, multi-column dynamic sorts over at john-sheenan.com. I am not sure about the efficiency of this approach, but in general dynamic sorting tends not to be very efficient anyway (unless you use dynamically-created SQL statements).  Overall, it is a very clever use of RANK() and definitely worth a look. Thanks for the great idea, John!
More About: Server , Order , Dynamic , Nami , Serve

Don't Let Output Dictate your Design
2007-06-05 14:55:00
I often talk about "database layer" versus "presentation" layer, but even the within just the database layer it is important to understand that how the data is physically stored does not always have to correlate with how the database returns results. read more...
More About: Design

More on Runs and Streaks in SQL
2007-05-30 14:43:00
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. We are accessing a real time proprietary database that approximates classic SQL in syntax. The GUI we are using does not allow anything to be done effectively on the front end. It all has to happen in the query. Trades flow in and we would like to sum them on the fly with only a query. We need to sum based on the change in price. eg for this series of trades (ID is ordered but non-contiguous) Trade ID   Price   Volume 1              4       10 3              4       20 6              5       15 7              4       20 should produce price  volume 4      30 5      15 4      20 so it sums the volume on a change in price. but note if the same price appears again this is not included in the total for the first instance of that price but in the total f...
More About: Runs

Real Programmers don't need to write test applications!
2007-05-23 18:27:00
You are a very important, talented, enterprise-level programmer! You write and maintain millions of lines of code, compiling your applications takes several hours, and your databases contain hundreds of tables with millions of rows. Even the best and brightest and most experienced programmers, now and then, come upon a class or library or technique that is new to them.  That's right, even you!  You can admit it, no one's looking.  But it really has no effect on your productivity, because you can quickly refer to the documentation or books or Google or many other resources to obtain the knowledge you need without missing a beat.  Heck, you don't even need to post a question in a programming forum -- who could help you, after all?  It's usually the other way around, right? So, you've done your research and gained the knowledge to use this feature and it's time to put it to use.  Implementation -- that's what separates the men from the boys, right?  You have a hectic schedule...
More About: Applications , Write , Real , Test , Program

Using SQLBulkCopy to persist any .NET Object
2007-05-22 20:40:00
In my SQLBulk Copy 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.  This means that it is very easy to quickly create your own custom class which implements this interface to bulk copy pretty much any type of object collection or array to a SQL Server table.  He even provides code for a simple abstract SqlBulkCopyReader class that you can inherit from to implement only the necessary parts of the interface. So, if you need to persist large amounts of data from your .net applications directly to SQL Server, and that data is not stored in DataTables, be sure to consider this option.  Thanks, Jesse !
More About: Object

SQL Challenge Response: Finding Consecutive Available Blocks in a Schedule
2007-05-15 15:52:00
Here's my response to a SQL Challenge , regarding how to find consecutive free time slots in a schedule. This can sometimes be tricky to solve in SQL, but using either of the two techniques shown here, it is actually pretty easy. (Updated to show 2 possible solutions) read more...
More About: Schedule , Bloc , Hall , Block

Criteria on Outer Tables
2007-05-14 16:12:00
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. Since it is hard to explain why over and over in forum posts, I thought it might be helpful to address that here once and for all with an example.read more...
More About: Tables , Teri , Crit , Criteria

Hamming Distance Algorithm in SQL
2007-05-09 16:39:00
Here's a simple User-Defined Function implementation of the Hamming Distance Algorithm for SQL Server, useful for tracking down transposition errors in your data. read more...
More About: Ming

Using SQLBulkCopy to quickly transfer data from .NET to SQL Server
2007-05-07 15:29:00
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.Data.SqlClient namespace called SqlBulkCopy that makes it very easy and efficient to copy large amounts of data from your .NET applications to a SQL Server database. You can even use this class to write a short .NET application that can serve as a "middleman" to move data between database servers. If you ever need to move large amounts of data to SQL Server from a .NET application, SQLBulkCopy is the way to go.  What's great about it is that you can use a populated DataTable as the source, or anything that implements IDataReader.  This means that you can use SQLBulkCopy to quickly move data from any OLEDB/ODBC datasource to SQL Server. Informal testing shows that it is about 4 times as fast as executing INSERT statements over and over when copying large amounts of data from a DataTab...
More About: Transfer , Sing , Trans

In CASE you didn't know ....
2007-05-03 15:15:00
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. What does this mean? Most people don't understand what SQL's CASE feature really is, how to use it, or how it works! read more...
More About: Case , Know

SQL Server 2005: Using EXCEPT and INTERSECT to compare tables
2007-05-02 20:47:00
Previously, I had written that UNION ALL (combined with a GROUP BY) is a really quick and easy way to compare two tables. Well, now in SQL 2005, we have another option: using EXCEPT and INTERSECT. And these are even easier! read more...
More About: Tables , Compare , Server , Sing , Compa

Thinking Set-Based .... or not?
2007-04-30 19:03:00
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. However, don't completely forget the most important skill that you learned in the procedural world!read more...
More About: King , Thinking , Base

Regular Expression Replace in SQL 2005 (via the CLR)
2007-04-27 22:20:00
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 Regular Expressions namespace, and since SQL 2005 allows you to integrate .NET CLR functions in your T-SQL code, I thought I'd go ahead and experiment with creating a RegExReplace() function. read more...
More About: Place , Sion , Xpress , Regular Expression

Custom Auto-Generated Sequences in SQL Server
2007-04-24 14:36:00
Be sure to visit SQLTeam.com to check out my latest article, Custom Auto -Gene ra ted Sequences in SQL Server .  It addresses a common question we see in the SQL Team forums.  From the intro: This article by Jeff Smith covers different ways to create sequence numbers. It starts with the basic identity and GUIDs and quickly moves to much more complicated sequence numbers including those with auto-incrementing mixed letters and numbers such as "A0001", etc. Jeff shows one of the easiest ways I've ever seen to efficiently handle very odd sequence numbers. In other words, if you want your database to assign customer numbers formatted like "AB-023" automatically, this is the article for you. Enjoy!

Have you seen Joe's "Blog" Yet?
2007-04-21 16:13:00
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. An example: Your code is so awful, you even use the "tbl-" prefixes to tell us you have no idea about RDBMS! You keep converting dates to strings because you are writing COBOL in SQL and want strings! Why do your have "CREATE TABLE #tmp_sched" when view would work? Answer: because magnetic tape files have to be materialized Why do you spit on ISO-11179 rules and use a "tbl-" prefix? Because you know only BASIC programming, which needs the prefixes for the one pass compiler. You write SQL with flags like it was 1950's Assembly language! Flags in SQL!! Ghod Damn!! Varying length identifiers!? And I loved the way spit on ANSI/ISO Standards with "SET QUOTED_IDENTIFIER OFF", etc.? Is this for real or just a parody?  I...
More About: Blog , Have

Sorting Columns with the C# Pivot Function
2007-04-20 18:05:00
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.  I would like to order the pivot columns by column name but I'm unable to do that yet.  Do you think you could help me with that ? Thank you in advance for your time and sorry to bother you with this question but I've tried to post in forums but didn't get any results. Maxime (Note: Maxime is referring to my simple Pivo t function discussed here.  The idea is, as always, to let SQL focus on returning raw, summarized and filtered data in a standard row/column result, and to let your very capable and efficient presentation layer handle all formatting -- which also happens to include pivoting data from rows to columns.) Sure thing, Maxime, that's a reasonable request and a great idea for improving the code. If you are using .NET 2.0, you can easily re-arrange columns in a Data...
More About: Columns , Sorting , Sort , Function

Better Alternatives to a FULL OUTER JOIN
2007-04-19 17:56:00
As many of you know, I am pretty much completely against ever using RIGHT OUTER JOINS, since they make your SQL code less readable and are easily rewritten as LEFT OUTER JOINs. In addition, I have yet to find a situation where a FULL OUTER JOIN makes sense or is necessary -- I have found that in just about every case other techniques work better.read more...
More About: Join , Better , Full , Alternatives , Bett

How to format a Date or DateTime in SQL Server
2007-04-13 22:45:00
Everything you ever wanted to know about how to use SQL Serve r 's advanced features to format Dates into any format that you need! Here, for the first time, all in one place, is the secret that no one wants you know about how easy it is to format data in T-SQL! read more...
More About: Form , Format

How to be an Effective Report Writer
2007-04-13 17:03:00
A non-technical guide to writing reports, with a focus on managing requirements, results, and expectations. read more...
More About: Report , Writer , Repo , Effective

SQL Check Constraints: Like George Costanza, sometimes you need to do the O
2007-04-05 20:26:00
It's been a while, so let's open up the old mailbag. Today's question is about writing a character-based check constraint. The answer: Simple ... do the *opposite* ! read more..
More About: Check , George , Times , Need , Costa