Tag: T-SQL

  • From the Labs of SQL Prompt

    I love SQL Prompt as an add-in for SSMS. The intellisense is very handy for me and I’ve gotten used to certain shortcut combinations that make it easy for me to write T-SQL quickly and get information on parameters without opening Books Online. It’s works better than the native intellisense for me, though perhaps I’ve just gotten used to it. When it’s not installed on an instance in one of my VMs, writing code is a chore.

    When I was in Cambridge recently, I had the chance to sit down with one of the developers of SQL Prompt and he showed me a few things I had never seen.

    For the most part when I install SQL Prompt, I leave it with the defaults. There are a few snippets that I change quickly, like the ssf snippet. This normally produces a “SELECT * FROM” and I add a “TOP 10” to it in order to reduce the amount of data I bring back.

    However there are a few features in SQL Prompt that are “experimental” in nature. They are complete, but not deployed into the product by default. You can access them from the SQL Prompt menu in Management Studio.

    prompt3

    This brings up the Experimental Features tab in the options dialog, which you can see below. There aren’t a lot of features, but these are ideas that have been suggested, or are working, but they developers aren’t sure if they are completely spec’d out.

    prompt4

    You can enable a few of these to see if you really want to see how they work. For example, I’ve enabled “Automatic Refresh Suggestions”. Since I tend to work in one database at a time and create lots of objects, I want this to happen. I can ALT+S, Enter for this, but I’d like to tool to do it for me.

    These items change periodically, and some link to related tools (like SQL Tab Magic), and they give you a chance to test the way the feature works and provide feedback. If you are a SQL Prompt user, you might check out this tab.

    If you’d like to see what SQL Prompt can do for you, download a free trial and give it a try:

    14-day-free-trial

  • Creating a Sequence in SQL Server 2012

    Sequences are a new object in SQL Server 2012 that generate just what the name implies: a sequence of numbers.

    To create a sequence, you use standard DDL with the CREATE SEQUENCE command. This command takes a type, a starting value, a min, max, and a few other parameters. You can read about the meanings of the parameters in Books Online, and learn how to use the parameters.

    When I first head about sequences, I thought this was neat, but not terribly useful. The idea of a custom sequence seems more like an edge case. However the more I’ve looked at them, the more it starts to make some sense.

    Let’s look at a real world example where we use a sequence since we often use these as a basis for some computer algorithm. Suppose I have a group of kids and I need to assign them to groups. In the real world, we line up the kids and we have them count off to some number. It’s often “1”, “2”, “1”, “2”, but it could be something else. In a youth group a few years ago, we counted off into 5 groups to divide the kids up.

    In this case, suppose I have kids and I want to get them into these groups:

    sequence1

    In order to get that in T-SQL, it can be cumbersome, especially if there isn’t a data point that cycles things, but rather a set counter. Here I can create a simple sequence:

    create sequence RoundRobin
     Start with 1
     increment by 1
      minvalue 1
      maxvalue 3
     cycle
    ;
    

    I have 10 rows in my table, being the list of kids:

    create table Kids
    ( Kid varchar(10)
    )
    go
    insert Kids
     values
      ('Emma')
    , ('Tabitha')
    , ('Kendall')
    , ('Delaney')
    , ('Kyle')
    , ('Jessica')
    , ('Josh')
    , ('Kirsten')
    , ('Amanda')
    , ('Jimmy')
    ;
    go
    

    To integrate the sequence, I do this:

    select
      'Group' = next value for dbo.RoundRobin
    , Kid
     from Kids
    ;
    

    That returns the results above.

    It’s not necessarily a common solution, but there might be places where you want custom counters, perhaps counting by a certain value. Suppose you want to alternate counters among two different people in a table. An identity doesn’t allow this, but you could set up two sequences, say one for even numbers, and one for odd numbers, and let each person use the appropriate sequence for inserts.

    Sequences are simple, but they can be useful in some cases. I’ll write a bit more about a few gotchas I’ve run into with sequences soon.

  • Hashing Collisions

    One of the problems with hashing is that you can have collisions from values that are not very similar. This means that if you are using hashing as a way to identify similar values, you need to make further checks with the original data after the hash matches are gathered.

    This post will show a few examples of the collisions that can occur if you use the CHECKSUM() or BINARY_CHECKSUM() functions.

    If we examine this code:

    -- Checksum
    declare 
      @i varchar(200)
    , @j varchar(200);
    
    select @i = 'LE';
    select @j = 'AAAAAAAAAAAAAAAALE';
    
    select 
      Plaintext = @i
    , checksum = CHECKSUM(@i)
    UNION ALL 
    SELECT
      Plaintext = @j
    , checksum = CHECKSUM(@j);
    GO
    

    This returns a result like this:

    hashing2

    Note that these two values are the same as far as the checksum hash goes.

    If we switch to BINARY_CHECKSUM(), we can get similar results.

    -- binary_checksum is no better
    declare 
      @i varchar(200)
    , @j varchar(200)
    , @k varchar(200);
    
    
    select @i = 'LE'
    select @j = 'Ou'
    select @k = 'MU'
    
    select 
      Plaintext = @i
    , BINARY_CHECKSUM(@i)
    UNION ALL 
    SELECT
      Plaintext = @j
    , BINARY_CHECKSUM(@j)
    UNION ALL 
    SELECT
      Plaintext = @k
    , BINARY_CHECKSUM(@k)
    GO
    
    

    hashing3

    While these two functions can be useful, you do have to be careful with the results. A matching hash from these functions does not mean that the source data is the same.

  • 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