Author: way0utwest

Using 2008 Features in a 2000 Compatibility Database

I saw a note recently from someone asking if they could use CROSS APPLY on a SQL Server 2008 instance with an older database in SQL 2000 compatibility mode. You can.

CREATE DATABASE sql2kCompat
;
go
ALTER DATABASE SQL2KCompat 
  SET COMPATIBILITY_LEVEL = 80
;
go

Once I have a database, I can access any of the newer views and DMVs. For example:

USE SQL2KCompat
;
go
SELECT 
 * 
  FROM sys.dm_database_encryption_keys
;
go

This doesn’t return anything because I don’t have keys, but I do get the headers. Now let’s add some data.

CREATE TABLE [Department](
   [DepartmentID] [int] NOT NULL PRIMARY KEY,
   [Name] VARCHAR(250) NOT NULL,
)
;
GO
INSERT [Department] ([DepartmentID], [Name]) 
 VALUES (1, N'Engineering')
;
INSERT [Department] ([DepartmentID], [Name]) 
 VALUES (2, N'Administration')
;
INSERT [Department] ([DepartmentID], [Name]) 
 VALUES (3, N'Sales')
, (4, N'Marketing')
, (5, N'Finance')
;
GO
CREATE TABLE [Employee](
   [EmployeeID] [int] NOT NULL PRIMARY KEY,
   [FirstName] VARCHAR(250) NOT NULL,
   [LastName] VARCHAR(250) NOT NULL,
   [DepartmentID] [int] NOT NULL REFERENCES [Department](DepartmentID),
)
;
GO
INSERT [Employee] ([EmployeeID], [FirstName], [LastName], [DepartmentID])
 VALUES (1, N'Orlando', N'Gee', 1 )
;
INSERT [Employee] ([EmployeeID], [FirstName], [LastName], [DepartmentID])
 VALUES (2, N'Keith', N'Harris', 2 )
;
INSERT [Employee] ([EmployeeID], [FirstName], [LastName], [DepartmentID])
 VALUES (3, N'Donna', N'Carreras', 3 )
;
INSERT [Employee] ([EmployeeID], [FirstName], [LastName], [DepartmentID])
 VALUES (4, N'Janet', N'Gates', 3 ) 
;
go

I create a few objects, which are standard, but notice the third insert statement for the Department table. It uses the new insert syntax for multiple rows in one statement. That’s not legal in SQL Server 2000, but it works here.

Now I can use CROSS APPLY

SELECT * FROM Department D
 CROSS APPLY
   (
   SELECT * FROM Employee E
   WHERE E.DepartmentID = D.DepartmentID
   ) A
;
GO

This returns me results, just as it does in a SQL Server 2008 database.

It appears that SQL 2008 functions work, which is what I’d hope would happen. However for the purposes of backwards compatibility, the functions that are from SQL Server 2000, should work as expected in SQL Server 2000.

The thing to be aware of is something that wasn’t legal in SQL Server 2000

SELECT
  q.sql_handle 
, t.text 
 FROM sys.dm_exec_query_stats AS q
  CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS t

you get an error. Passing a column into a function wasn’t allowed in SQL Server 2000, so this is a problem.

And a little cleanup

USE MASTER
;
GO
DROP DATABASE SQL2KCompat
;
go

April 4, 2012

My Little Friend
Say hello to my little friend:

I love this button!

This button is my favorite part of Management Studio. It’s on every dialog that I’ve seen in the 2012 version of the tool and I’ve begun using it quite liberally. At a recent talk, I told the audience that they should never click “OK” in SSMS again, and should instead click script and then cancel whenever they were making changes.

Someone asked me why, and I had a few reasons, all of which seem to me good reasons to make use of my little friend. First it’s easy, Apple/iOS easy. When things aren’t easy, we tend not to do them, but clicking “script” is painless, it doesn’t interrupt you, and it produces the script you need in a new query window. That alone ought to be a good reason to use it.

However there are other reasons as well. It’s a chance to see the exact code being run and making sure it’s what you want. If you aren’t sure, and you shouldn’t be running code without understanding it, this is a good way to learn. It’s also a great logging tool. One of the things that has made me successful as a DBA over the years is documenting what I do. Many of my colleagues haven’t done this and then spent hours trying to determine what changed on a particular system. If I script out the code I run, I have something I can easily drop into a log for future reference.

To me, this is one of the tools that should improve the knowledge of SQL Server professionals everywhere, by increasing their awareness and knowledge of what’s happening on their instances. I’d be happier if the “OK” button would just produce the script and never run it, but I’m not sure we’ll get Microsoft to agree to that change.

Now if I only had a “Powershell script” button right next to it…..

Steve Jones

The Voice of the DBA Podcasts

We publish three versions of the podcast each day for you to enjoy.
- Watch the Windows Media Podcast – 19.5MB WMV
- Watch the iPod Video Podcast – 16.7MB MP4
- Watch the MP3 Audio Podcast – 3.4MB MP3
April 4, 2012
Too Many Bricks, Too Much Data
Too many bricks are hard to keep straight

At one point in my career I was working on a new application and we were debating about storing certain metrics related to the application usage and customer’s behavior. My boss asked for my opinion and I said that more data was better than less and we could always delete the data if we found it was not being used.

However we rarely delete data. We seem loathe to remove old data, even when we find it’s slowing down our systems. Worse still, there aren’t great methods for even stripping out, and preserving older data other than custom work in each system. I ran across a piece that talks about the tremendous amount of data that we are constantly acquiring, a deluge that overwhelms us in so many scientific areas. There are some endeavors collecting so much data that they must restore to storing data in networked systems, making the data sets available only through software that can combine the information from various databases.

In the corporate world of data we usually don’t deal with such large amounts of data, but we are often dealing with hardware that constrains our ability to work effectively with the data we have. We find that our disparate systems are spread across so many places that it can become hard to aggregate the bits together and extract information.

I do think that we will start to feel the stresses of dealing with so much data and finding the meaningful information from it. The people that learn to do this well, and filter their bits effectively will become very valuable to their companies in the future.

Steve Jones

The Voice of the DBA Podcasts

We publish three versions of the podcast each day for you to enjoy.
- Watch the Windows Media Podcast – 19.5MB WMV
- Watch the iPod Video Podcast – 16.1MB MP4
- Watch the MP3 Audio Podcast – 3.3MB MP3
April 3, 2012
Which product was purchased the most? T-SQL
Suppose you had a list of product sales and were curious about which one was sold the most? It’s a simple query, and one that I used to ask people in interviews so I thought it would make a nice easy blog post.

If I look at AdventureWorks2008, I have a Sales.SalesOrderDetail table that roughly looks like this:

There’s a productID in there, and if I want to see how products were sold, I can do this:
```
SELECT 
   COUNT(DISTINCT ProductID)
 FROM Sales.SalesOrderDetail
 
```
However that doesn’t help me with the actual products. I could instead do this:
```
SELECT 
    productid
  , COUNT(*)
 FROM Sales.SalesOrderDetail
 GROUP BY ProductID
 
```
That isn’t ordered, so I can easily add an ORDER BY to see the products sold and the counts.
```
SELECT 
    productid
  , COUNT(*)
 FROM Sales.SalesOrderDetail
 GROUP BY ProductID
 ORDER BY COUNT(*) DESC
```
That gives me a list, but I really want to just get the top seller. That’s easy as well.
```
SELECT TOP 1
    productid
  , COUNT(*)
 FROM Sales.SalesOrderDetail
 GROUP BY ProductID
 ORDER BY COUNT(*) DESC
```
Which returns:

productid
———– ———–

870 4688

However I don’t know which product this is, so I’d really want a join here.
```
SELECT TOP 1
    p.Name
  , COUNT(*) 
 FROM Sales.SalesOrderDetail sod
   INNER JOIN Production.Product p
     ON sod.ProductID = p.ProductID
 GROUP BY p.Name
 ORDER BY COUNT(*) DESC
 
 
```
Which lets me know that a water bottle was the most sold item.

Name
————————————————– ———–

Water Bottle – 30 oz. 4688

However I wasn’t asked for the count of sales, just the most sold item. I don’t really need the count in order for the query to work. I can do this:
```
SELECT TOP 1
    p.Name
 FROM Sales.SalesOrderDetail sod
   INNER JOIN Production.Product p
     ON sod.ProductID = p.ProductID
 GROUP BY p.Name
 ORDER BY COUNT(*) DESC
 
```
Which just returns

Name

————————————————–

Water Bottle – 30 oz.

What if there were two items that had the same sales? I’d really want to include WITH TIES to be complete.
```
SELECT TOP 1 WITH TIES
    p.Name
 FROM Sales.SalesOrderDetail sod
   INNER JOIN Production.Product p
     ON sod.ProductID = p.ProductID
 GROUP BY p.Name
 ORDER BY COUNT(*) DESC
```
It’s simple, easy, but it’s a query that trips a lot of people up in the intervening steps. I’d prefer you quickly returned the last query to me (wrote it, told me, etc.), but if you had to work through it, I’d hope that you talked about the steps I did as you went through deriving the query so I can understand how you think, and how you improve. If you have me the first query, I’d probably hint around with you about the items forgotten.

You could use a CTE, subquery, or other methods, which are more complex, less efficient, and unnecessary, but could be valid answers. It’s best to stick with something simple, but go with your first instinct and work through it. If you get stuck, backtrack and try something else, explaining along the way. Or tell the interviewer you don’t know if you don’t.

It’s best to be honest, and to show that you can learn, correct yourself, or even admit you don’t know.
April 2, 2012