Jeff's SQL Server Blog

Random Thoughts & Cartesian Products with Microsoft SQL Server
posts - 155, comments - 2679, trackbacks - 64

My Links



Welcome to my weblog. My name is Jeff Smith, I am software developer in Boston, MA and I was recently named a 2009 SQL Server MVP. Check in frequently for tips, tricks, commentary and ideas on SQL Server and .NET programming.



Post Categories




Code Library - SQL

Snippets of code, functions, stored procedures and more that you can generally cut and paste and use wherever you like.
SQL Server Express Automated Backups

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. As such, this was the first time I ever needed to configure automated backups for a SQL Express database.  However, there is no built-in way to schedule backups without using manual scripts or external applications.  I briefly toyed with writing something myself to get the job done, but of course we should not reinvent the wheel so I turned to Google to find out what's out there for options....

posted @ Thursday, December 06, 2007 3:10 PM | Feedback (2) | Filed Under [ Techniques Code Library - SQL ]

Hamming Distance Algorithm in SQL

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.


posted @ Wednesday, May 09, 2007 10:39 AM | Feedback (6) | Filed Under [ T-SQL Code Library - SQL ]

Essential SQL Server Date, Time and DateTime 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.


posted @ Tuesday, January 02, 2007 11:38 AM | Feedback (98) | Filed Under [ T-SQL Code Library - SQL DateTime Data ]

Another Dynamic SQL CrossTab Stored Procedure

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!


posted @ Monday, May 02, 2005 2:00 PM | Feedback (60) | Filed Under [ T-SQL CrossTabs / Pivoting Data Code Library - SQL ]

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

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. Rules are like CHECK constraints, but from what I understand they are very non-standard.


posted @ Thursday, December 02, 2004 2:41 PM | Feedback (8) | Filed Under [ T-SQL Code Library - SQL DateTime Data ]

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

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. This allows you to pass a string of words, and indicate that either ALL words must match, ANY must match, or the exact string must match.

posted @ Tuesday, November 02, 2004 4:35 PM | Feedback (27) | Filed Under [ T-SQL Code Library - SQL ]

More aggregate SQL functions

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. This should answer some of the questions raised in the comments from my article.


posted @ Sunday, July 11, 2004 10:12 PM | Feedback (1) | Filed Under [ T-SQL Code Library - SQL ]

Efficient paging of recordsets: SQL Server 2000

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, in SQL 2000.


posted @ Monday, December 22, 2003 12:31 PM | Feedback (35) | Filed Under [ T-SQL Paging Data Code Library - SQL ]

Create a Date in T-SQL

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:


posted @ Monday, December 15, 2003 10:07 AM | Feedback (6) | Filed Under [ T-SQL Code Library - SQL DateTime Data ]

PARSENAME() - a simple way to parse (some) strings in 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.


posted @ Tuesday, September 30, 2003 5:09 PM | Feedback (2) | Filed Under [ Miscellaneous T-SQL Code Library - SQL ]

Powered by:
Powered By Subtext Powered By ASP.NET