TOP Enhancements in Yukon

In previous versions of SQL Server TOP allowed you to limit the number of rows returned as a number or percentage in Select Queries. I use this all the time. It has gotten more flexible in Yukon where TOP can be used in Delete, Update and Insert queries. What is even cooler is that Yukon will allow you to specify the number of rows (or percent) by using variables or subqueries. I love the ability to dynamically set the TOP, so much that I have started writing Stored Procedures that accept a NumberofRows parameter like so: 

Create Procedure usp_SEL_ReturnTopOrders

@NumberofRows int

As

Select TOP (@NumberofRows) OrderID

From Orders

Order By OrderID

Executing the stored procedure is easy, just pass in how many records that you want (in this case it is 100):

usp_SEL_ReturnTopOrders 100

Using a subquery can be very powerful when you are doing things on the fly. A real simple example to demonstrate the concept is show here, we are getting the TOP n customers based on how many records we have in our Employees table:

Select TOP (Select Count(*) from Employees) *

From Orders

See my article on SQLJunkies.com.

PIVOT Data in Yukon (RIP Case and Rozenshtein)

Let’s face it, users usually want to see data in tabular format, which is a bit of a challenge when we usually store data in a highly relational form. PIVOT is a new TSQL operator that you can specify in your FROM clause to rotate rows into columns and create a traditional “Crosstab” query without using CASE, Rozenshtein or subqueries. You have always been able to do this easily in Access with Pivot and Transform, but SQL Server was always a step behind.

Using PIVOT is easy. First in your select statement you need to specify the values you want to pivot on, in the following example, we will use the Year of the Order. Our FROM clause looks normal except for the PIVOT part. The PIVOT statement creates the value we plan on showing in the rows of the newly created columns, in this case I am using the aggregate SUM of the TotalAmount (a calculated field in our FROM clause). Then we have to use the FOR operator to list the values we are going to Pivot on in the OrdersYear column.  The example is shown here:

SELECT CustomerID, [1996] AS 'Y1996', [1997] AS 'Y1997', [1998] AS 'Y1998', [1999] as 'Y1999'

FROM

     (SELECT CustomerID,OD.UnitPrice * OD.Quantity - OD.Discount AS TotalAmt,

          Year(dbo.Orders.OrderDate) as OrdersYear

          FROM dbo.Orders INNER JOIN dbo.[Order Details] As OD

              ON dbo.Orders.OrderID = OD.OrderID)

          As Orders

     PIVOT

     (

          SUM(TotalAmt)

          FOR OrdersYear IN([1996], [1997], [1998], [1999])

     ) AS XTabData

Order BY CustomerID

The results look like this:

CustomerID	Y1996	Y1997	Y1998
ALFKI	NULL	2293.25	2301.9
ANATR	88.8	799.75	514.4
ANTON	403.2	6451.15	660
AROUT	1379	6588.4	5838.4
BERGS	4324.4	14532.25	8108.5
BLAUS	NULL	1079.8	2160
BLONP	9986.2	8371.05	730
BOLID	982	4035.3	279.8
BONAP	4202.35	12460.7	7184.7

That is all there is to it. Of course this is a real simple example to show you the new concept, you can then of course get more sophisticated aggregates and even use Common Table Expressions in the FROM clause. Also you can use the UNPIVOT operator to normalize data that is already pivoted.

See my article on SQLJunkies.com.

A Dutch Houseboat

So yesterday I was on a true houseboat in the Netherlands. Apparently my pal Remi grew up on a houseboat and we went to his childhood home to drop off something to his parents after he picked me up at the airport. It was very cool and you could really feel the house rock. What a treat.

An Access Killer?

The reporting engine in Access is almost 10 years old with major modification. It is that good. Now something better has come around. SQL Server Reporting Services, due out any week now, could be an Access killer. After demoing it to the London Database Forum last night, we all realized that it is pretty cool. An Access Report Killer, yes it is (but Access will still be better for local disconnected reporting). Attention Access developers, SSRS is based off the old Access report designed, so you have a leg up, so go learn it. If you don’t know about SQL Server Reporting Services, go download it from Microsoft now!

And Microsoft: Its about time!

A killer Infield

Japanese star Kazuo Matsui decided to join the New York Mets, accepting the Amazins’ three-year offer today. Kazuo has been called a "faster, stronger version of Inchiro.” Now the Mets will have two killer shortstops. My guess is the Jose Reyes will go to second. With Matsui and Reyes, the Mets now have two switch-hitters at the top of their lineup who have speed, power, high OBPs, high BAs and great defensive skills. Playoff bound? Not yet. A major improvement, sure.

Now the true question: Is New York City ready for two Matsui’s??

Molloy Boys Take Bubble Baths…

I was changing this at Kenny Anderson last night while I was in the front row of the Sonics loss against Indiana. He must not have heard this chant in over 15 years. You see Kenny is a fellow Queens boy who attended the (all boys) Arch Bishop Molloy which was a arch rival to my High School St. Francis Prep and we use to taunt him with this chant in his 4 years as the #1 High School player in the country. Kenny is the best passer in the NBA and along with Reggie Miller just housed the Seattle Super Sonics. I had my fun at Kenny’s expense, but he got the last laugh.  

SCO is Desperate

SCO, who is suing IBM over Linux (and is threatening more lawsuits against corporate Linux users), yesterday attacked the GNU GPL (General Public License) in which Linux is distributed. In an open letter from SCO CEO Darl McBride, SCO said the the GPL is in violation the United States Constitution (and also some U.S. copyright and patent laws).  

They are bringing in the US Constitution to this debate? Please.

Here is my open letter to SCO:

Dear Darl McBride,

Drop the damn lawsuit already.

Regards,

Stephen Forte

New York, NY

Open Season for Hackers on Linux Systems

Yet another high-profile attack on Linux- someone broke into one of the servers used to distribute versions of Gentoo Linux on Tuesday.

CLR v TSQL Stored Procedures

I am here in Redmond chatting with a few fellow .NET gurus about SQL Server and Yukon’s ability to create stored procedures with any CLR language. We have decided, TSQL for everything, except when you 1. need something from the .NET framework like encryption or RegEx and 2. you are doing something that is a very CPU intensive operation.

So why bother with the CLR inside of Yukon? I think that the convergence is a good thing, but TSQL will be around for a long time (as it should be). It is not an every day occurrence that you will use the CLR (think replacement for extended stored procedures), but when you need it, you will be very thankful.

So I am going to make a prediction that a lot of people will try to create their whole app around CLR stored procedures when they fire up Yukon for the first time. Please don’t fall in this trap!

Linux For the Masses?

Linux has always been a back end server and workstation for uber geeks only. A few workstation class systems have come out in the past but a compelling one is now released to the world, the Java Desktop System (JDS) from Sun Microsystems.

While this shows how desperate Sun is for any kind of revenue and proof of their relevance, JDS is pretty cool. It combines Linux, Mozilla, GNOME and StarOffice in one unit. Selling for around $150, this creates true competition to the Windows/Office offering. (Attention DOJ and EC, drop your suits. J)

So, does this mean corporate American (and elseware) will run out in droves and drop Windows/Office for JDS? Probably not. There are migration, document conversion, TCO and other concerns, but enough will make the move to JDS to scare the crap out of Redmond. I have to say that this is exciting. True competition is only good for the consumer, Microsoft and Sun will create better software and offer it cheaper than it is today, that is Adam Smith at work.