Tag: syndicated

  • SQL Server 2012 Beta Exams

    I signed up for a few of the SQL Server 2012 Beta certification exams, and have been going through them over the last few days. The exams I signed up for are:

    I took the first one on Monday, and the second on Wednesday. I have the other two scheduled for Friday and next Wednesday.

    I can’t really comment on what was in the exam, but I did see some evolutions of the testing software, which was nice. I like a few of the changes, and they should help people demonstrate a few more skills than the old multiple-guess exams. There are still multiple choice questions, but some new ways to ask someone to show knowledge.

    I’m not really worried about being certified, but I do like to see how the certification process works, I might write some stuff to help people (I’ve worked on three cert books in the past) and most importantly, I get a rough idea of what I know on the exams. They’ll ask me a few things I might not have looked at in depth, and I certainly found a few holes in my knowledge over the last few exams.

    If you have the chance to take a beta, I’d encourage you. They’re free, and the time they take can be a valuable aid in helping plan your future learning.

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

    compat1

    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
  • 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:

    sales1

    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.

  • 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:

    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.