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




SQL Server 2005

Making use of the great new features in SQL 2005.
How to calculate Median in SQL Server

Nothing earth-shattering here, I was just helping out a colleague with this so I thought I'd post up the example I gave him. -- sample table: create table People (     Person varchar(1) primary key,     City varchar(10),     Age int ) go -- with some sample data: insert into People select 'A','Boston',23 union all  -- odd # select 'B','Boston',43 union all select 'C','Boston',29 union all select 'D','Chicago',15 union all -- single # select 'E','NY',12 union all  -- even # select 'F','NY',55 union all select 'G','NY',57 union all select 'H','NY',61 go -- here's our query, showing median age per city: select city,     AVG(age) as MedianAge from (     select City, Person, Age,         ROW_NUMBER() over (partition by City order by Age...

posted @ Monday, August 30, 2010 12:00 AM | Feedback (1) | Filed Under [ T-SQL Techniques SQL Server 2005 GROUP BY ]

UNPIVOT: Normalizing data on the fly

Everyone seems to want to "pivot" or "cross tab" data, but knowing how to do the opposite is equally important.  In fact, I would argue that the skill of "unpivoting" data is more useful and more important and more relevant to a SQL programmer, since pivoting results in denormalized data, while unpivoting can transform non-normalized data into a normalized result set.  We all know that there's lots of bad databases designs out there, so this can be a handy technique to know.  Of course, even a well designed, fully normalized database can still benefit from "unpivoting" from time to time,...

posted @ Wednesday, April 23, 2008 10:33 AM | Feedback (17) | Filed Under [ T-SQL CrossTabs / Pivoting Data Techniques SQL Server 2005 Report Writing SQL Server 2008 ]

Top N Percent per Group

Here's a good question in the feedback from my post about using the T-SQL 2005 features to return the Top N per Group of a result set: Sani writes: What about Top n Percent per Group??? I would greatly appreciate an input on that as well. That's a good question, and also easily solvable.  One way to do this that I thought of was by using a combination of rank() and a count(*) partitioned aggregate function, which is also a new SQL Server 2005 feature. Simply calculate the rank() of each row in the group, and also the count(*) of all rows in the...

posted @ Thursday, February 21, 2008 1:05 PM | Feedback (6) | Filed Under [ SQL Server 2005 ]

Implementing Table Inheritance with SQL Server

I have a new article up at SQLTeam: Implementing Table Inheritance with Sql Server It discusses the situation where you have multiple entities that are distinct, yet they have many attributes or relations in common.   There is an easy way to simplify your database design and your code if you use the concept of a "base table" for that common data, which is very similar to the concept of Inheritance in Object-Oriented programming. I recently had to do this for a client that tracks Contributions to their foundation; there is a base set of data that all Contributions have, such as who donated,...

posted @ Thursday, February 21, 2008 11:05 AM | Feedback (6) | Filed Under [ SQL Server 2005 Database Design Links ]

Date Only and Time Only data types in SQL Server 2005 (without the CLR)

In this post, I showed a simple way to create simple but useful Date and Time user-defined data types in SQL Server 2000.  Here's how to do it in SQL Server 2005, without the need for CLR types or anything fancy. First, we must create two user defined data types: create type Date from dateTime create type Time from dateTime So, internally (and externally to our clients), these types are really just DateTime.  But, we will apply some rules to these types so that the Date data type will always be constrained to a time exactly at midnight, and the the Time data type...

posted @ Wednesday, October 31, 2007 9:13 AM | Feedback (16) | Filed Under [ Techniques SQL Server 2005 DateTime Data ]

Taking a look at CROSS APPLY

Applying a Sub-Query, Joining a Derived Table ... I think the easiest way to think of CROSS APPLY is that it is like doing a CROSS JOIN with a correlated sub-query instead of a derived table.  Let's see if I can explain that .... A derived table is "self-contained", in that all tables and columns in the parent SELECT are not accessible (though variables and parameters can be referenced).  For example, consider: select A.*, b.X from A cross join (select B.X from B where B.Val=A.Val) b That is not legal because A.Val is out of scope within the derived table; this is because the derived...

posted @ Thursday, October 18, 2007 5:01 PM | Feedback (10) | Filed Under [ T-SQL Techniques SQL Server 2005 Joins/Relations ]

SQL 2005 PIVOT Operator (link)

There's two interesting posts over at the MSDN blogs from Craig Freedman about the new PIVOT operator in SQL 2005. First, he gives a nice overview of the operator and how to use it, and then he follows it up with a performance analysis as well. A good read, check it out.

posted @ Thursday, July 12, 2007 9:24 AM | Feedback (2) | Filed Under [ CrossTabs / Pivoting Data SQL Server 2005 Links ]

SQL Server 2005: CROSS APPLY

SQL Server User-Defined Functions (UDFs) can return either a single value or virtual tables. However, sometimes we might like for a User-Defined Function to simply return more than 1 piece of information, but an entire table is more than what we need.

posted @ Tuesday, June 12, 2007 8:52 AM | Feedback (3) | Filed Under [ Techniques SQL Server 2005 Links ]

SQL Server 2005: Specifying Partitions for Aggregate Functions

Did you know that a new feature in SQL Server 2005 allows you to specify an OVER partition for aggregate functions in your SELECT statements?


posted @ Monday, May 21, 2007 2:52 PM | Feedback (7) | Filed Under [ T-SQL SQL Server 2005 Links GROUP BY ]

SQL Server 2005: Using EXCEPT and INTERSECT to compare tables

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!


posted @ Wednesday, May 02, 2007 2:47 PM | Feedback (26) | Filed Under [ T-SQL SQL Server 2005 ]

Regular Expression Replace in SQL 2005 (via the CLR)

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 RegularExpressions 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.


posted @ Friday, April 27, 2007 4:20 PM | Feedback (13) | Filed Under [ .NET (C# / VB) SQL Server 2005 Code Library - C# / VB.NET ]

More with SQL Server 2005 : Top n Per Group, Paging, and Common Table Expressions

I wrote about a few of the new features in SQL 2005 and how they can be used to solve some old "classic" SQL problems very easily, and I thought I'd briefly discuss a few more. Also, if you enjoy baseball, read on!


posted @ Friday, March 30, 2007 9:21 AM | Feedback (31) | Filed Under [ T-SQL Sports Paging Data SQL Server 2005 ]

SQL Server 2005: Using PARTITION and RANK in your criteria

The RANK and PARTITION features in 2005 are simply amazing. They make so many "classic" SQL problems very easy to solve.


posted @ Wednesday, March 28, 2007 2:18 PM | Feedback (34) | Filed Under [ T-SQL Sports SQL Server 2005 ]

Lots of great SQL Server 2005 Express Downloads

Have you visited the SQL Server 2005 Express Downloads page lately?


posted @ Thursday, April 27, 2006 10:07 AM | Feedback (2) | Filed Under [ SQL Server 2005 ]

Some Simple SQL Rules to Live By

Most of these are really basic. Some are my take on established ideas and standards, and may be controversial. Agree or disagree? All feedback is welcome! (well ... mostly the "I agree, you're a genius" feedback is welcome ... but I'll accept all of it, I suppose)


posted @ Tuesday, March 14, 2006 12:42 PM | Feedback (33) | Filed Under [ T-SQL Techniques Efficiency SQL Server 2005 Security ]

Powered by:
Powered By Subtext Powered By ASP.NET