Jeff Smith Blog

Date Only and Time Only User Defined Dataypes in SQL Server 2000

Thu Dec 2, 2004 by Jeff Smith in  t-sql, code-library-sql, datetime-data

In this post: http://weblogs.sqlteam.com/jeffs/archive/2004/12/02/2954.aspx I talked about separating dates from times for certain columns in your database. One thing I have never really used before in SQL Server 2000 is user-defined datatypes and rules. Read more →

Breaking apart the DateTime datatype – Separating Dates from Times in your Tables

Thu Dec 2, 2004 by Jeff Smith in  t-sql, datetime-data

Update: More on dates and times in T-SQL here. I don't like storing dates along with the time portion in my database tables. Sure, I use the datatype, but I wish SQL provided a “Date” datatype and a “Time” datatype that were separate. Read more →

Returning Random Numbers in a SELECT statement

Mon Nov 22, 2004 by Jeff Smith in  t-sql

As it is implemented, the RAND() function in SQL Server doesn't let you return a different random number per row in your SELECT statement. For example, if you execute this: Read more →

A Quick Lesson on SQL Indexes

Fri Nov 12, 2004 by Jeff Smith in  miscellaneous, t-sql, techniques

Just a quick post of some benchmarking code for our good friend Guayo at TheDailyWTF, in regards to this discussion. The debate is, essentially: Assuming everything is properly indexed, which is faster/more efficient? Read more →

The shortest, fastest, and easiest way to compare two tables in SQL Server: UNION !

Wed Nov 10, 2004 by Jeff Smith in  t-sql, techniques, group-by

When you have two tables (or resultsets from SELECT statements) that you wish to compare, and you want to see any changes in ANY columns, as well as to see which rows exist in 1 table but not the other (in either direction) I have found that the UNION operator works quite well. Read more →

Searching a column for all words, any words, or exact phrase in a SQL Table

Tue Nov 2, 2004 by Jeff Smith in  t-sql, code-library-sql

(This is from the SQLTeam forums. I noticed a reply of mine to a user's problem might be useful to others .. so here it is!) Here's one way to implement a search, similar to Google's, if you don't want to (or can't) use full text indexing. Read more →

Delete Duplicates And Resolve Foreign Key References in SQL

Thu Oct 7, 2004 by Jeff Smith in  t-sql, database-design, joins-relations

Here's a scenario many of us have encountered: You inherit a database. TableX has an identity column "ID" set up as the primary key, and contains many duplicates. It is clear a better, natural primary key is a combination of columns A and B. Read more →

Passing Arrays of Values as Parameters to Stored Procedures

Mon Oct 4, 2004 by Jeff Smith in  t-sql

I see many people have the need to pass in multiple pieces of information to stored procedures in a parameter, often as a CSV string. For example, you might have a stored procedure that will return customer info for all customer ID's passed in a CSV string, called something like this: Read more →

Using SQL to solve the "Sliding Tiles" puzzle

Wed Sep 22, 2004 by Jeff Smith in  miscellaneous, t-sql, techniques

I just wanted to take a minute to highlight this thread in the sqlteam forums: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=39736 It's really interesting stuff, and a bunch of us worked together to come up with some really cool ideas about using T-SQL to solve the classic sliding tile puzzle -- you know, where tiles on a square board are mixed up, with one missing, and you slide them around until the picture is complete or they are in the proper order. Read more →

More aggregate SQL functions

Sun Jul 11, 2004 by Jeff Smith in  t-sql, code-library-sql

Despite the cool things you can do with writing your own custom aggregate functions as shown in my last article, in many cases you don't need custom functions to get what you need. Read more →

SQL Server 2000 UDF's : Custom Aggregate and Cumulative Functions

Thu Jun 3, 2004 by Jeff Smith in  t-sql

If the topic intrigues you, please check out my latest article: http://weblogs.sqlteam.com/jeffs/articles/1490.aspx A little too long for a blog unfortunately. Before reading the article, keep in mind it's purely an academic exercise. Read more →

Efficient and DYNAMIC Server-Side Paging with SQL Server 2000

Mon Mar 22, 2004 by Jeff Smith in  t-sql, paging-data

(UPDATE:If you are using SQL Server 2005 or above, there is a much easier and more efficient wayto page your data.) Here is a completely dynamic version of my paging technique in the form of a stored procedure. Read more →

Using OPENXML to process CSV (comma-separated value) strings

Sun Jan 25, 2004 by Jeff Smith in  t-sql

This may be common knowledge to those among us that are handy with XML (I'm not), but since I just uncovered this nice little trick I thought I'd pass it along either way. Read more →

Efficient paging of recordsets: SQL Server 2000

Mon Dec 22, 2003 by Jeff Smith in  t-sql, paging-data, code-library-sql

Here's the most efficient way that I can think of to return rows @a through @b from a table or query, when each row is ordered and can be uniquely identified by a set of columns. Read more →

Create a Date in T-SQL

Mon Dec 15, 2003 by Jeff Smith in  t-sql, code-library-sql, datetime-data

Another way to create dates easily, w/o using CONVERT or using a UDF (i.e., if you have SQL 7.0), if you are starting out with integers representing the year, month and day of the date you need to create: Read more →

Creating Dates and Date Math in T-SQL with a UDF

Tue Dec 9, 2003 by Jeff Smith in  t-sql, datetime-data

Update: Please refer to this page for an update on this concept; I recommend that you use the newer functions presented on that page. To me, this is a pretty handy function to have in your toolbox: Read more →

A Recursive User-Defined Function (SQL Server 2000)

Fri Nov 21, 2003 by Jeff Smith in  t-sql

Anyone know what this will return? create function RecursiveTest(@V int) returns @t table (i int) as begin set @v = @v - 1 if @v<>0 insert into @t  select @v union select * from dbo. Read more →

SQL WHERE clauses: Avoid CASE, use Boolean logic

Fri Nov 14, 2003 by Jeff Smith in  t-sql, techniques, efficiency

As some of you may know, I recommend to avoid using CASE expressions in the WHERE clause of a query to express conditional logic. I prefer to have everything translated to simple ANDs, ORs and NOTs to keep things a) portable, b) easier to read and c) efficient. Read more →

Keep those SQL Crosstabs flexible!

Fri Oct 24, 2003 by Jeff Smith in  t-sql, crosstabs-pivoting-data, techniques

I recently helped someone with performing some simple cross tabs (or pivots) in SQL, such as: Select CustomerID, SUM(CASE WHEN type = 'Land' THEN Amount ELSE 0 END) as Land, SUM(CASE WHEN type = 'Sea' THEN Amount ELSE 0 END) as Sea, SUM(CASE WHEN type = 'Air' THEN Amount ELSE 0 END) as Air FROM SomeTable GROUP BY CustomerID Read more →

Comparing sets of rows from two tables

Fri Oct 3, 2003 by Jeff Smith in  t-sql, joins-relations

I thought I'd post a blog showing a technique I haven't seen elsewhere. This is from a recent post of mine at the SqlTeam forums. The problem: you have two tables, each containing mutiple rows of data per “SetID”. Read more →