Tag: syndicated

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

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
SQL Saturday #131 – Phoenix Schedule Posted
I got an email today from the SQL Saturday #131 crew with the schedule posted, showing me listed for two sessions. I’m delivering these items:
- Encryption in SQL Server
- Branding Yourself for a Dream Job
These are sessions I’ve done elsewhere and they’ve been popular, so I’m hoping they go over well.

The event is Apr 28, just a few weeks away, in Phoenix, AZ at Chandler-Gilbert Community College Pecos Campus. I like Phoenix, and it will be nice to go back. I’ve been there multiple times for vacation and for baseball. Unfortunately, this will be a short trip, with lots of other stuff scheduled.

If you will be in the area on Apr 28, and want to learn a little SQL, think about registering and coming by the event.
April 2, 2012
Spring SQL Server Connections / DevConnections

I returned late last night from the Spring DevConnections event, delivering three sessions in the SQL Server Connections conference. I talked about Encryption, Filestream/Filetable, and Contained Databases. Always cool to see your name in print

It was a nice event, with decent crowds in the SQL Server. I didn’t have a ton of people, but when you’re competing with the SQLskills crew, Brent Ozar, Allen White, Glen Berry and a few others, it’s hard to draw a large crowd.

Grant and Brad both presented as well, filling up the slots in our SQLServerCentral track, and talking about a wide range of topics. Grant’s talk on continuous integration and deployment was really good, with lots of interesting information. It was good to hear from a few people in the audience that face challenges on this particular topic. We have a whitepaper from Red Gate that talks a little about how we solve this, but Grant gave some other alternatives that you could use to tackle this.

The show was at the MGM Grand this time, and I have to say I really like that resort. Things seemed a little closer together, more food/dining choices, and close to other places on the strip. A walk to the Bellagio was no big deal. There were also three of my favorite morning stops on the road

It wasn’t just me that liked the hotel. I left Paul Randal, Kimberliy Tripp, Brent Ozar, and Glenn Berry at Craftsteak Thur night to go to the airport. I say down before my flight to see a tweet that the First Lady of the US was having dinner that night in the restaurant.

The sessions I attended and delivered were interesting. Lots of system administrators and developers mixed in with the DBAs, looking for information on various features. It was a twist to get questions that sometimes were more basic for DBAs, and at the same time, had a different view and some challenges when presented from another perspective. On a panel with Grant Fritchey and Brent Ozar, we had some interesting HA/DR questions from the audience about 2012 as well, things that made us think a bit.

The exhibition hall was a little small, but it’s nicely set up with long breaks between some sessions for people to wander around and see what new third party tools are available. I spent a little time at the Red Gate booth and had some very interesting conversations with people, including a nice long one about .NET development.

As I do everywhere on the road, I start the mornings out with a run. This time the loop around the MGM complex is a little over a mile and a half, which I enjoyed all three days with Allen White at 6:30 every morning. #sqlrun in Las Vegas!

This time the Windows and Visual Studio tracks were on another floor, and with three sessions to prepare for, I didn’t get the chance to visit those tracks. I did see a great talk from Allen on Server Core, which I’ve been wanting to experiment with. Look for most of my future server VMs to be Server Core, which should make things run faster on my little Air.

I also saw a couple talks from Mark Minasi on Windows 8 and that looks rather interesting in a few ways. I’m not sure I like the client interface, but some of the architectural changes look like good moves on the part of Microsoft. The server side, especially seems like it will end up being a good server OS for management by IT staffs.

The other great thing about a show in Vegas is the wide variety of shows. I asked in my sessions and a few people saw Ka, one of the Cirque du Soleil shows. Someone saw David Copperfield as well, which I would have liked to see. Oh well, there’s always next time.

My entertainment this time was a wonderful dinner with Brent Ozar and Grant Fritchey at e by Jose Andres in the Cosmopolitan. Brent invited us and I wasn’t sure since it’s a very fancy dinner. However I thoroughly enjoyed it. Not sure I’d go back myself, but I’d take someone else, just for the experience. The food was very interesting (I’m not a foodie), and the presentation amazing. I had a great time. Here was one of our “courses”

Overall a great show, and a fun time. SQLServerCentral is one of the sponsors, and we have a track there every show.

We should be back in the fall, this time at the Bellagio, Oct 29-Oct 1. Hopefully you can join us, and we look forward to seeing you then.

March 30, 2012