Registered Servers in SSMS

In my last blog post, I discussed changing the color of the status bar in SSMS 2008. I received a couple of comments and even an e-mail discussing how this doesn’t seem to always work. After playing with it for a little bit, I’ve found that the status bar color needs to be set in both Query->Connection->Connect/Change Connection… (here-in referred to as simply the Query menu) and Registered Servers.

Let’s run through this. First, connect to an instance with any color using the Query menu.

Connecting via the Query menu

Now, create a new registered server. Make sure to use the same server.

Create a New Registered Server

New Server Registration

New Server Registration

Pick a color, but make sure that it’s different than the previous color. This is just for demonstration purposes only. Since the whole point is to have a consistent color, you would normally use the same color in both connection methods for the same server.

Pick a color

Pick a color

Open a new query window via Registered Servers.

New Query Window

New Query Window

Registered Server Query Window

Registered Server Query Window

Here’s what happens when I connect to the same server using both Registered Servers (left) and another window using the Query menu (right).

SSMS - Same Server, Different Colors

SSMS - Same Server, Different Colors

For anyone who’s using both the Query menu and Registered Servers to connect to servers, then you should walk through the process of connecting to each server via both means and changing the colors to ensure consistency. I did this for 22 servers and it took me less than 10 minutes.

I hope that helps clear up some of the confusion. :)


SSMS Server Settings

I think this has been discussed before on better blogs than mine, but it’s just so darn cool that I want to help spread the word.

In SSMS 2008, you can change the color of the status bar for servers. This gives you a nice visual reminder as to which server you’re currently connecting to. Since I’ve made the DEV/PROD mistake before, this is something I’m a big fan of.

So let’s walk through how you can set this up:

Opening a new connection

Opening a new connection

Click on Options >>

Connection Properties

Connection Properties

Select Use custom color and click on Select…

Pick a color

Pick a color

Choose the color you prefer, then click on OK

Open a new query

Open a new query

Click on Connect.

That’s all there is to it. Pretty easy, huh? Now let’s see what happens when we connect to multiple servers…

Multi-Server Rainbow

Multi-Server Rainbow

Beautiful! SSMS seems to remember the color settings too, so you should only have to set this up once.


Automated Index Defrag Script

UPDATE: This script has been significantly updated. Please view the latest version at

So after much delay, here’s the latest and greatest version of my index defrag script.

A summary of the changes:

  • Added support for centralized execution. Option to defrag indexes for a single database from another database, or for all non-system databases.
  • Consolidated Enterprise and Standard versions of the script with new @editionCheck variable.
  • Added parametrization for MaxDop restrictions during rebuilds; validates that the value does not exceed the actual number of processors on the server.
  • Changed minimum fragmentation default value from 10 to 5 to match BOL recommendations.
  • Limited defrags to objects with more than 8 pages.
  • Added a debug option to give a little more insight into what’s happening and to assist with troubleshooting.
  • Updated logic for handling partitions and LOBs.

And a couple of notes and explanations:

Don’t you know you can just pass NULL to sys.dm_db_index_physical_stats for the DatabaseID?
Yes, I realize you can do this. But I don’t want to defrag the system databases, i.e. tempdb, so I opted to handle it this way instead.

How long will this take?
It depends. I don’t necessarily recommend running it without specifying a database; at least, not unmonitored. You *can* do that, but it could take a while. For example, to run sys.dm_db_index_physical_stats for all databases and tables, totaling 2TB, took me 4.5 hours; that doesn’t even count the actual defrags.

Where should I put this?
It’s up to you. If you have a database for items like centralized maintenance or scratch tables, that may be a good place for it. If you prefer, you could also put this in each individual database and call it locally, too. I would not put this in the master or msdb databases.

This is pretty close to a complete rewrite, so please let me know if you encounter any bugs. And now… the code!

If Not Exists(Select [object_id] From sys.tables Where name = N'dba_indexDefragLog')
    -- Drop Table dbo.dba_indexDefragLog
    Create Table dbo.dba_indexDefragLog
          indexDefrag_id    int identity(1,1)   Not Null
        , databaseID        int                 Not Null
        , databaseName      nvarchar(128)       Not Null
        , objectID          int                 Not Null
        , objectName        nvarchar(128)       Not Null
        , indexID           int                 Not Null
        , indexName         nvarchar(128)       Not Null
        , partitionNumber   smallint            Not Null
        , fragmentation     float               Not Null
        , page_count        int                 Not Null
        , dateTimeStart     datetime            Not Null
        , durationSeconds   int                 Not Null
        Constraint PK_indexDefragLog Primary Key Clustered (indexDefrag_id)
    Print 'dba_indexDefragLog Table Created';
If ObjectProperty(Object_ID('dbo.dba_indexDefrag_sp'), N'IsProcedure') = 1
    Drop Procedure dbo.dba_indexDefrag_sp;
    Print 'Procedure dba_indexDefrag_sp dropped';
Create Procedure dbo.dba_indexDefrag_sp
    /* Declare Parameters */
      @minFragmentation     float           = 5.0  
        /* in percent, will not defrag if fragmentation less than specified */
    , @rebuildThreshold     float           = 30.0  
        /* in percent, greater than @rebuildThreshold will result in rebuild instead of reorg */
    , @executeSQL           bit             = 1     
        /* 1 = execute; 0 = print command only */
    , @database             varchar(128)    = Null
        /* Option to specify a database name; null will return all */
    , @tableName            varchar(4000)   = Null  -- databaseName.schema.tableName
        /* Option to specify a table name; null will return all */
    , @onlineRebuild        bit             = 1     
        /* 1 = online rebuild; 0 = offline rebuild; only in Enterprise */
    , @maxDopRestriction    tinyint         = Null
        /* Option to restrict the number of processors for the operation; only in Enterprise */
    , @printCommands        bit             = 0     
        /* 1 = print commands; 0 = do not print commands */
    , @printFragmentation   bit             = 0
        /* 1 = print fragmentation prior to defrag; 
           0 = do not print */
    , @defragDelay          char(8)         = '00:00:05'
        /* time to wait between defrag commands */
    , @debugMode            bit             = 0
        /* display some useful comments to help determine if/where issues occur */
    Name:       dba_indexDefrag_sp
    Author:     Michelle Ufford,
    Purpose:    Defrags all indexes for one or more databases
      @minFragmentation     defaulted to 10%, will not defrag if fragmentation 
                            is less than that
      @rebuildThreshold     defaulted to 30% as recommended by Microsoft in BOL;
                            greater than 30% will result in rebuild instead
      @executeSQL           1 = execute the SQL generated by this proc; 
                            0 = print command only
      @database             Optional, specify specific database name to defrag;
                            If not specified, all non-system databases will
                            be defragged.
      @tableName            Specify if you only want to defrag indexes for a 
                            specific table, format = databaseName.schema.tableName;
                            if not specified, all tables will be defragged.
      @onlineRebuild        1 = online rebuild; 
                            0 = offline rebuild
      @maxDopRestriction    Option to specify a processor limit for index rebuilds
      @printCommands        1 = print commands to screen; 
                            0 = do not print commands
      @printFragmentation   1 = print fragmentation to screen;
                            0 = do not print fragmentation
      @defragDelay          time to wait between defrag commands; gives the
                            server a little time to catch up 
      @debugMode            1 = display debug comments; helps with troubleshooting
                            0 = do not display debug comments
    Called by:  SQL Agent Job or DBA
    Date        Initials	Description
    2008-10-27  MFU         Initial Release for public consumption
    2008-11-17  MFU         Added page-count to log table
                            , added @printFragmentation option
    2009-03-17  MFU         Provided support for centralized execution, 
                            , consolidated Enterprise & Standard versions
                            , added @debugMode, @maxDopRestriction
                            , modified LOB and partition logic                            
    Exec dbo.dba_indexDefrag_sp
          @executeSQL           = 0
        , @minFragmentation     = 80
        , @printCommands        = 1
        , @debugMode            = 1
        , @printFragmentation   = 1
        , @database             = 'AdventureWorks'
        , @tableName            = 'AdventureWorks.Sales.SalesOrderDetail';
Set NoCount On;
Set XACT_Abort On;
Set Quoted_Identifier On;
    If @debugMode = 1 RaisError('Dusting off the spiderwebs and starting up...', 0, 42) With NoWait;
    /* Declare our variables */
    Declare   @objectID             int
            , @databaseID           int
            , @databaseName         nvarchar(128)
            , @indexID              int
            , @partitionCount       bigint
            , @schemaName           nvarchar(128)
            , @objectName           nvarchar(128)
            , @indexName            nvarchar(128)
            , @partitionNumber      smallint
            , @partitions           smallint
            , @fragmentation        float
            , @pageCount            int
            , @sqlCommand           nvarchar(4000)
            , @rebuildCommand       nvarchar(200)
            , @dateTimeStart        datetime
            , @dateTimeEnd          datetime
            , @containsLOB          bit
            , @editionCheck         bit
            , @debugMessage         varchar(128)
            , @updateSQL            nvarchar(4000)
            , @partitionSQL         nvarchar(4000)
            , @partitionSQL_Param   nvarchar(1000)
            , @LOB_SQL              nvarchar(4000)
            , @LOB_SQL_Param        nvarchar(1000);
    /* Create our temporary tables */
    Create Table #indexDefragList
          databaseID        int
        , databaseName      nvarchar(128)
        , objectID          int
        , indexID           int
        , partitionNumber   smallint
        , fragmentation     float
        , page_count        int
        , defragStatus      bit
        , schemaName        nvarchar(128)   Null
        , objectName        nvarchar(128)   Null
        , indexName         nvarchar(128)   Null
    Create Table #databaseList
          databaseID        int
        , databaseName      varchar(128)
    Create Table #processor 
          [index]           int
        , Name              varchar(128)
        , Internal_Value    int
        , Character_Value   int
    If @debugMode = 1 RaisError('Beginning validation...', 0, 42) With NoWait;
    /* Just a little validation... */
    If @minFragmentation Not Between 0.00 And 100.0
        Set @minFragmentation = 5.0;
    If @rebuildThreshold Not Between 0.00 And 100.0
        Set @rebuildThreshold = 30.0;
    If @defragDelay Not Like '00:[0-5][0-9]:[0-5][0-9]'
        Set @defragDelay = '00:00:05';
    /* Make sure we're not exceeding the number of processors we have available */
    Insert Into #processor
    Execute xp_msver 'ProcessorCount';
    If @maxDopRestriction Is Not Null And @maxDopRestriction > (Select Internal_Value From #processor)
        Select @maxDopRestriction = Internal_Value
        From #processor;
    /* Check our server version; 1804890536 = Enterprise, 610778273 = Enterprise Evaluation, -2117995310 = Developer */
    If (Select ServerProperty('EditionID')) In (1804890536, 610778273, -2117995310) 
        Set @editionCheck = 1 -- supports online rebuilds
        Set @editionCheck = 0; -- does not support online rebuilds
    If @debugMode = 1 RaisError('Grabbing a list of our databases...', 0, 42) With NoWait;
    /* Retrieve the list of databases to investigate */
    Insert Into #databaseList
    Select database_id
        , name
    From sys.databases
    Where name = IsNull(@database, name)
        And database_id > 4 -- exclude system databases
        And [state] = 0; -- state must be ONLINE
    If @debugMode = 1 RaisError('Looping through our list of databases and checking for fragmentation...', 0, 42) With NoWait;
    /* Loop through our list of databases */
    While (Select Count(*) From #databaseList) > 0
        Select Top 1 @databaseID = databaseID
        From #databaseList;
        Select @debugMessage = '  working on ' + DB_Name(@databaseID) + '...';
        If @debugMode = 1
            RaisError(@debugMessage, 0, 42) With NoWait;
       /* Determine which indexes to defrag using our user-defined parameters */
        Insert Into #indexDefragList
              database_id As databaseID
            , QuoteName(DB_Name(database_id)) As 'databaseName'
            , [object_id] As objectID
            , index_id As indexID
            , partition_number As partitionNumber
            , avg_fragmentation_in_percent As fragmentation
            , page_count 
            , 0 As 'defragStatus' /* 0 = unprocessed, 1 = processed */
            , Null As 'schemaName'
            , Null As 'objectName'
            , Null As 'indexName'
        From sys.dm_db_index_physical_stats (@databaseID, Object_Id(@tableName), Null , Null, N'Limited')
        Where avg_fragmentation_in_percent >= @minFragmentation 
            And index_id > 0 -- ignore heaps
            And page_count > 8 -- ignore objects with less than 1 extent
        Option (MaxDop 1);
        Delete From #databaseList
        Where databaseID = @databaseID;
    Create Clustered Index CIX_temp_indexDefragList
        On #indexDefragList(databaseID, objectID, indexID, partitionNumber);
    Select @debugMessage = 'Looping through our list... there''s ' + Cast(Count(*) As varchar(10)) + ' indexes to defrag!'
    From #indexDefragList;
    If @debugMode = 1 RaisError(@debugMessage, 0, 42) With NoWait;
    /* Begin our loop for defragging */
    While (Select Count(*) From #indexDefragList Where defragStatus = 0) > 0
        If @debugMode = 1 RaisError('  Picking an index to beat into shape...', 0, 42) With NoWait;
        /* Grab the most fragmented index first to defrag */
        Select Top 1 
              @objectID         = objectID
            , @indexID          = indexID
            , @databaseID       = databaseID
            , @databaseName     = databaseName
            , @fragmentation    = fragmentation
            , @partitionNumber  = partitionNumber
            , @pageCount        = page_count
        From #indexDefragList
        Where defragStatus = 0
        Order By fragmentation Desc;
        If @debugMode = 1 RaisError('  Looking up the specifics for our index...', 0, 42) With NoWait;
        /* Look up index information */
        Select @updateSQL = N'Update idl
            Set schemaName = QuoteName(
                , objectName = QuoteName(
                , indexName = QuoteName(
            From #indexDefragList As idl
            Inner Join ' + @databaseName + '.sys.objects As o
                On idl.objectID = o.object_id
            Inner Join ' + @databaseName + '.sys.indexes As i
                On o.object_id = i.object_id
            Inner Join ' + @databaseName + '.sys.schemas As s
                On o.schema_id = s.schema_id
            Where o.object_id = ' + Cast(@objectID As varchar(10)) + '
                And i.index_id = ' + Cast(@indexID As varchar(10)) + '
                And i.type > 0
                And idl.databaseID = ' + Cast(@databaseID As varchar(10));
        Execute sp_executeSQL @updateSQL;
        /* Grab our object names */
        Select @objectName  = objectName
            , @schemaName   = schemaName
            , @indexName    = indexName
        From #indexDefragList
        Where objectID = @objectID
            And indexID = @indexID
            And databaseID = @databaseID;
        If @debugMode = 1 RaisError('  Grabbing the partition count...', 0, 42) With NoWait;
        /* Determine if the index is partitioned */
        Select @partitionSQL = 'Select @partitionCount_OUT = Count(*)
                                    From ' + @databaseName + '.sys.partitions
                                    Where object_id = ' + Cast(@objectID As varchar(10)) + '
                                        And index_id = ' + Cast(@indexID As varchar(10)) + ';'
            , @partitionSQL_Param = '@partitionCount_OUT int OutPut';
        Execute sp_executeSQL @partitionSQL, @partitionSQL_Param, @partitionCount_OUT = @partitionCount OutPut;
        If @debugMode = 1 RaisError('  Seeing if there''s any LOBs to be handled...', 0, 42) With NoWait;
        /* Determine if the table contains LOBs */
        Select @LOB_SQL = ' Select Top 1 @containsLOB_OUT = column_id
                            From ' + @databaseName + '.sys.columns With (NoLock) 
                            Where [object_id] = ' + Cast(@objectID As varchar(10)) + '
                                And (system_type_id In (34, 35, 99)
                                        Or max_length = -1);'
                            /*  system_type_id --> 34 = image, 35 = text, 99 = ntext
                                max_length = -1 --> varbinary(max), varchar(max), nvarchar(max), xml */
                , @LOB_SQL_Param = '@containsLOB_OUT int OutPut';
        Execute sp_executeSQL @LOB_SQL, @LOB_SQL_Param, @containsLOB_OUT = @containsLOB OutPut;
        If @debugMode = 1 RaisError('  Building our SQL statements...', 0, 42) With NoWait;
        /* If there's not a lot of fragmentation, or if we have a LOB, we should reorganize */
        If @fragmentation < @rebuildThreshold Or @containsLOB = 1 Or @partitionCount > 1
            Set @sqlCommand = N'Alter Index ' + @indexName + N' On ' + @databaseName + N'.' 
                                + @schemaName + N'.' + @objectName + N' ReOrganize';
            /* If our index is partitioned, we should always reorganize */
            If @partitionCount > 1
                Set @sqlCommand = @sqlCommand + N' Partition = ' 
                                + Cast(@partitionNumber As nvarchar(10));
        /* If the index is heavily fragmented and doesn't contain any partitions or LOB's, rebuild it */
        If @fragmentation >= @rebuildThreshold And IsNull(@containsLOB, 0) != 1 And @partitionCount <= 1
            /* Set online rebuild options; requires Enterprise Edition */
            If @onlineRebuild = 1 And @editionCheck = 1 
                Set @rebuildCommand = N' Rebuild With (Online = On';
                Set @rebuildCommand = N' Rebuild With (Online = Off';
            /* Set processor restriction options; requires Enterprise Edition */
            If @maxDopRestriction Is Not Null And @editionCheck = 1
                Set @rebuildCommand = @rebuildCommand + N', MaxDop = ' + Cast(@maxDopRestriction As varchar(2)) + N')';
                Set @rebuildCommand = @rebuildCommand + N')';
            Set @sqlCommand = N'Alter Index ' + @indexName + N' On ' + @databaseName + N'.'
                            + @schemaName + N'.' + @objectName + @rebuildCommand;
        /* Are we executing the SQL?  If so, do it */
        If @executeSQL = 1
            If @debugMode = 1 RaisError('  Executing SQL statements...', 0, 42) With NoWait;
            /* Grab the time for logging purposes */
            Set @dateTimeStart  = GetDate();
            Execute sp_executeSQL @sqlCommand;
            Set @dateTimeEnd  = GetDate();
            /* Log our actions */
            Insert Into dbo.dba_indexDefragLog
                , databaseName
                , objectID
                , objectName
                , indexID
                , indexName
                , partitionNumber
                , fragmentation
                , page_count
                , dateTimeStart
                , durationSeconds
                , @databaseName
                , @objectID
                , @objectName
                , @indexID
                , @indexName
                , @partitionNumber
                , @fragmentation
                , @pageCount
                , @dateTimeStart
                , DateDiff(second, @dateTimeStart, @dateTimeEnd);
            /* Just a little breather for the server */
            WaitFor Delay @defragDelay;
            /* Print if specified to do so */
            If @printCommands = 1
                Print N'Executed: ' + @sqlCommand;
        /* Looks like we're not executing, just printing the commands */
            If @debugMode = 1 RaisError('  Printing SQL statements...', 0, 42) With NoWait;
            If @printCommands = 1 Print IsNull(@sqlCommand, 'error!');
        If @debugMode = 1 RaisError('  Updating our index defrag status...', 0, 42) With NoWait;
        /* Update our index defrag list so we know we've finished with that index */
        Update #indexDefragList
        Set defragStatus = 1
        Where databaseID       = @databaseID
          And objectID         = @objectID
          And indexID          = @indexID
          And partitionNumber  = @partitionNumber;
    /* Do we want to output our fragmentation results? */
    If @printFragmentation = 1
        If @debugMode = 1 RaisError('  Displaying fragmentation results...', 0, 42) With NoWait;
        Select databaseID
            , databaseName
            , objectID
            , objectName
            , indexID
            , indexName
            , fragmentation
            , page_count
        From #indexDefragList;
    /* When everything is said and done, make sure to get rid of our temp table */
    Drop Table #indexDefragList;
    Drop Table #databaseList;
    Drop Table #processor;
    If @debugMode = 1 RaisError('DONE!  Thank you for taking care of your indexes!  :)', 0, 42) With NoWait;
    Set NoCount Off;
	Return 0

Thanks to my beta testers, @scoinva, @davidmtate, @jdanton, and @SuperCoolMoss!
Special thanks to SCM for keeping on me to finish this.

Happy Defragging!



Index Fragmentation PotW Webcast – Now Available

The Pain-of-the-Week webcast I did last Thursday is now online! You can find it here:

The topic of the webcast is index fragmentation: what is it, how to identify it, and how to fix it. You can find the materials for this webcast in my previous blog post. I’ll also be updating SQLServerPedia with the materials, hopefully tonight.

Thanks to the great feedback I’ve received, I’ll be doing another Pain-of-the-Week webcast, this time with Brent Ozar. More info to follow as we hammer out the specifics.

In a related note, I have updated my index defrag script. I’m just waiting for my awesome beta testers to let me know everything works for them. It works just fine on my servers (lol) but I don’t have access to any Standard editions, etc., so I just want to be extra sure before I unleash it on the SQL world. I should have it online tomorrow.


PoTW Webcast Materials

For those interested, here’s the links I mentioned during my presentation:

… and here’s the scripts that I used during my presentation:

Use sandbox;
/* Create a table to promote fragmentation */
Create Table dbo.fragmented
    myGuid uniqueidentifier Not Null
    Constraint PK_fragmented Primary Key Clustered (myGuid)
/* Populate our table */
Set NoCount On;
Declare @iterations int = 0;
While @iterations < 250000
    Insert Into dbo.fragmented
    Select NewID();
    Set @iterations += 1;
/* Verify the number of records in our table */
Execute sp_spaceused 'dbo.fragmented';
/* Let's take a look at sys.dm_db_index_physical_stats */
Select Object_Name(ddips.object_id) As 'tableName'
    , As indexName
    , ddips.*
From sys.dm_db_index_physical_stats (DB_ID(), Object_ID(N'fragmented'), NULL, NULL , 'DETAILED') As ddips
Join sys.indexes As i
    On ddips.object_id = i.object_id
    And ddips.index_id = i.index_id;
Select *
From sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID(N'fragmented'), NULL, NULL , 'LIMITED');
/* ShowContig should return us the same information */
DBCC ShowContig ('fragmented');
/* ExtentInfo is an undocumented command that will show us the number of allocated extents */
DBCC ExtentInfo (0, 'fragmented', 1);
/* Grab our page number */
    , AS 'indexName'
    , p.partition_number
    , au.type_desc
      CONVERT (INT, SUBSTRING (au.first_page, 6, 1) +
         SUBSTRING (au.first_page, 5, 1))) +
   ':' + CONVERT (VARCHAR(20),
      CONVERT (INT, SUBSTRING (au.first_page, 4, 1) +
         SUBSTRING (au.first_page, 3, 1) +
         SUBSTRING (au.first_page, 2, 1) +
         SUBSTRING (au.first_page, 1, 1))) AS 'firstPage'
      CONVERT (INT, SUBSTRING (au.root_page, 6, 1) +
         SUBSTRING (au.root_page, 5, 1))) +
   ':' + CONVERT (VARCHAR(20),
      CONVERT (INT, SUBSTRING (au.root_page, 4, 1) +
         SUBSTRING (au.root_page, 3, 1) +
         SUBSTRING (au.root_page, 2, 1) +
         SUBSTRING (au.root_page, 1, 1))) AS 'rootPage'
      CONVERT (INT, SUBSTRING (au.first_iam_page, 6, 1) +
         SUBSTRING (au.first_iam_page, 5, 1))) +
   ':' + CONVERT (VARCHAR(20),
      CONVERT (INT, SUBSTRING (au.first_iam_page, 4, 1) +
         SUBSTRING (au.first_iam_page, 3, 1) +
         SUBSTRING (au.first_iam_page, 2, 1) +
         SUBSTRING (au.first_iam_page, 1, 1))) AS 'firstIAM_page'
FROM sys.indexes AS i
JOIN sys.partitions AS p
    AND i.index_id = p.index_id
JOIN sys.system_internals_allocation_units AS au
    ON p.hobt_id = au.container_id
ORDER BY tableName;
/* View our pages */
DBCC TraceOn (3604);
DBCC Page (sandbox, 1, pageNumberHERE, 3);
DBCC TraceOff (3604);
/* Rebuild our index */
Alter Index PK_fragmented On dbo.fragmented REBUILD;
/* Clean-Up! */
Drop Table dbo.fragmented;

Thank you for watching the webcast! :)



Easy Way To Return Top Records

Okay, so that title may suck. I accept that. It’s late and I can’t think of anything better at the moment. :)

Bad blog title aside, let’s take a pretty common data request. You need to return the top sales performer in each department. If you’ve ever had this type of request, then you know there’s a few different ways of handling this, and it can be a little complicated. Today, Dave Carlile shared with me a new and pretty simple way of handling this with Row_Number().

The syntax for Row_Number is a little different than what you may be used to: ROW_NUMBER ( ) OVER ( [ PARTITION BY yourColumn ] ORDER BY yourColumn )

PARTITION BY is what you want to group by. This is optional.

ORDER BY is how you want to order your data before assigning a row number. This is required.

Let’s take a look at an example.

/* Create a table to play with */
Create Table dbo.sales
      order_id      int Identity(1,1)
    , salesPerson   varchar(20) 
    , department    varchar(20)   
    , total         money
    Constraint PK_sales
        Primary Key Clustered(order_id)
/* Load it up with some bogus records */
Insert Into dbo.sales
Select 'Amanda', 'Sales', 420 Union All
Select 'Barry', 'Sales', 360 Union All
Select 'Chris', 'Marketing', 398 Union All
Select 'David', 'Sales', 371 Union All
Select 'Ethan', 'Customer Support', 123 Union All
Select 'Faith', 'Sales', 206 Union All
Select 'Gavin', 'Marketing', 396 Union All
Select 'Heather', 'Marketing', 51 Union All
Select 'Iris', 'Customer Support', 79 Union All
Select 'Jamie', 'Customer Support', 242;
/* Examine what values are returned for each record */
Select ROW_NUMBER() Over(Partition By department Order By total Desc) As 'salesRank'
    , salesPerson
    , department
    , total
From dbo.sales;
/* Let's grab just the top sales performer in each department */
With myCTE As
    Select ROW_NUMBER() Over(Partition By department Order By total Desc) As 'salesRank'
        , salesPerson
        , department
        , total
    From dbo.sales
Select salesPerson
    , department
    , total
From myCTE
Where salesRank = 1
Order By total Desc;

Let’s take a look at the options we’ve specified for Row_Number(). Since we want to know who has the top sales, we’re going to order by [total] in descending order. We also want to assign each department its own rank, so we’re going to group (partition) by the [department] column. If we did not include the “Partition By” clause, then we’d get only 1 record returned, which would be the top overall sales person (in this case, Amanda).

Now let’s do the same thing, but this time we want to return the top 2 sales person in each department.

/* Now grab the top TWO sales performer in each department */
With myCTE As
    Select ROW_NUMBER() Over(Partition By department Order By total Desc) As 'salesRank'
        , salesPerson
        , department
        , total
    From dbo.sales
Select salesPerson
    , department
    , total
From myCTE
Where salesRank <= 2 -- this is the only difference
Order By department
    , salesRank;
/* Clean-Up! */
Drop Table dbo.sales;

That’s all there is to it! Pretty cool, huh? I haven’t gotten around to performance testing on large data sets yet, but I definitely like the simplicity of the approach.

Thanks, Dave! :)

Update: Aaron The Hobt has already done some performance testing on this very subject. The results? Not as good as I was hoping. :(

As an aside, I’m going to be participating in the Pain of the Week webcast tomorrow at 11 AM ET. This free webcast will be on index fragmentation: what is it, how to find it, and how to fix it. If you’re interested, you can register here:

This will be only my second time speaking to an audience (the first time was yesterday at our first PASS Chapter meeting!). So if nothing else, it may be good for a few laughs. :)


380PASS Meeting – Success!

Our first meeting was a success by all accounts! We had a great turnout with 30 people showing up.

As an ice breaker activity, we had folks pair up and try to identify something related to SQL Server, i.e. commands or terms, for every letter of the alphabet. There was a 5 minute time limit, and with only 20 seconds to spare, Randy Vandergaast and Dave Brosseau turned their sheet in to each win a $25 gift card to Barnes & Noble.

Chris Leonard gave the group a great overview of PASS and the PASS Summit in Seattle.

Brian Duhn then gave a comprehensive overview of how to set up Server-to-Server Service Broker. The entire slide show can be found at, under Resources.

Thanks to our awesome sponsors, we had some great prizes to give away, and everyone walked away with something.

Thanks to everyone who attended for helping us have a great launch meeting! :)


380PASS Meeting Tonight!

Our very first I380 PASS meeting is tonight! If you’ve not already registered for the event, you’re still welcome to come. The meeting’s being held at New Horizons in Hiawatha (map). Food and beverages will be provided at 5:30pm, and the meeting will start at 6pm.

Brian Duhn (MCITP), a senior DBA with, will be presenting on Server-to-Server Service Broker. Quest has generously provided food and awesome swag for tonight’s meeting.

More details can be found on our official Chapter website at

I look forward to seeing you there! :)


SQL Quiz 3: Are you being treated fairly?

In his latest SQL Quiz, Chris Shaw (aka @SQLShaw) asked,

“Do you feel like you are being treated fairly at your current or past employers? The question stems from the fact that very few people today stay at a company 20 to 30 years like they did when I was growing up. Do you feel like the company feels a loyalty toward the employee or do you think that they look at you just as head count?”

Wow. Talk about a loaded question!

I guess first we need to settle on a definition of “being treated fairly.” For my purposes, I’m going to interpret this as a mutually beneficial relationship in which my contributions are acknowledged and rewarded, and I’m able to maintain a healthy work-life balance.

So let’s take a look at the question again.

Am I being treated fairly at my current place of employment?
Absolutely. I love this company. It’s a good, positive work environment, and I’m encouraged to maintain a good work-life balance. For example, when I put in extra hours during a big project or an unexpected crisis, I usually get to leave work a little early on other days.

Side note: today’s my team’s Employee Appreciation Day. This afternoon, we’re going out to eat at a Japanese steakhouse, then out for a movie. All on the company’s dime and time. How cool is that?! Yes, we have openings. ;)

Have I been treated fairly at previous places of employment?
Umm… ermm… various sounds of hesitation…

The short answer is “no.” But, really, it’s been my own fault. I’ve let companies take advantage of me. I’ve worked too many hours, taken on too much responsibility, let myself be paid crap wages. So really, if anything’s to blame, it’s my eager-to-please nature. The reality is, if you open yourself up to being taken advantage of, you will be. This is true of people as well as companies.

If you don’t feel you’re being treated fairly at your job, schedule a meeting with your boss and explain your concerns. Don’t be all whiny about it, either. Be professional and stick to facts, such as “This last month, I’ve averaged 70 hours a week. Either reduce my hours, pay me overtime, or give me comp time.” It’s a pretty fair request. If they don’t address your concerns, then it’s probably time to start looking for a new job.

Do you feel like the company feels a loyalty toward the employee or do you think that they look at you just as head count?

It depends on the company. I think, realistically, most companies don’t feel any loyalty to an employee. That doesn’t mean the employee’s not appreciated and valued, and the company doesn’t want to retain the employee. But rather, at the end of the day, the company will do what’s best for the company, and if that means letting a valuable employee go for the “greater good of the company”… so be it.

Tag! You’re It.

This can be a pretty sensitive topic, so I’m going to tag the one guy I know who’s ballsy enough to do it. Hey, SQLAgentMan (@SQLAgentMan)… yeah, I’m talking about you.


PASS Update


Hi folks. If lately you’ve noticed a few less blog posts than normal, it’s because I’m spending a lot of time starting up a brand new PASS Chapter. In fact, we’re only one week away from our first meeting! For those who haven’t been following the updates, we’re starting a PASS Chapter in east Iowa (Cedar Rapids, Iowa City, Davenport, etc.). Our first meeting will feature a presentation by Brian Duhn, Senior DBA, MCITP, on server-to-server service broker.

Any and everyone is invited to join us. More information can be found on

Pain of the Week Webcast

I’ve also been asked by Quest Software to give a webcast on index fragmentation next week. The webcast will air on Thursday, 12 March 2009, at 10AM CT. This will be my first webcast, so be gentle! More information can be found on Quest’s Pain of the Week website.

SQL Challenge

If you haven’t heard of it yet, Adam Machanic has posted a T-SQL challenge. Check it out… the winner gets a 1-year subscription to MSDN!