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 →

Non-Deterministic UDF's – Custom Aggregate and Cumulative Functions

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

User Defined Functions (UDF’s) are just about my favorite thing in SQL 2000.I thought the possibilities to be endless, until I realized that they were designed to be completely deterministic:each call to a UDF with the same arguments should always return the same value, and during the evaluation of a UDF it cannot make any changes to the state of the database. Read more →

Two Words: Baseball

Thu May 13, 2004 by Jeff Smith in  sports

It's baseball season again and as many SQLTeamer's have probably discovered over the years, I'm a huge fan of the game. Living in Boston, of course my team of preference is the Red Sox which in itself can be quite a rollercoster ride. 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 →

ASP Report Class Northwind Sample

Thu Nov 20, 2003 by Jeff Smith in  asp

As promised, here's a sample ASP report from Northwind using my ASP Report Class (http://weblogs.sqlteam.com/jeffs/posts/526.aspx).  Hopefully, everyone will agree that the script is very easy to write and read and much shorter. Read more →