Jeff Smith Blog

SQL Server Express Automated Backups

Thu Dec 6, 2007 by Jeff Smith in  techniques, code-library-sql

I recently set up a web application using SQL Server Express for a client, and it was the first time I had done any "production" level work using the Express edition. Read more →

Hamming Distance Algorithm in SQL

Wed May 9, 2007 by Jeff Smith in  t-sql, code-library-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 →

Essential SQL Server Date, Time and DateTime Functions

Tue Jan 2, 2007 by Jeff Smith in  t-sql, code-library-sql, datetime-data

Part I: Standard Date and Time Functions I've posted some variations of these before, but here they all are in 1 easy package: The essential date and time functions that every SQL Server database should have to ensure that you can easily manipulate dates and times without the need for any formatting considerations at all. Read more →

Another Dynamic SQL CrossTab Stored Procedure

Mon May 2, 2005 by Jeff Smith in  t-sql, crosstabs-pivoting-data, code-library-sql

First off, before going any further make sure you have read the hall of fame SQLTeam article by Rob Volk on generating crosstab results using a flexible, dynamic stored procedure that has been viewed over 100,000 times! Read more →

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 →

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 →

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 →

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 →

PARSENAME() - a simple way to parse (some) strings in SQL

Tue Sep 30, 2003 by Jeff Smith in  miscellaneous, t-sql, code-library-sql

Handy reminder: The PARSENAME() function can be useful for parsing small strings. It returns parts 1-4 (working right to left) of a string, with each part delimited by periods. For example: Read more →