SQL Server Agent Job Sort Order

Today's post could also be titled "I have no idea what is happening here." We have an agent job, "Job - Do Stuff". We then created a few hundred jobs (templates for the win) all named like "Job - Do XYZ" where XYZ is a mainframe module identifier. When I'm scrolling through the list of jobs, it takes a few passes for my eye to find Do Stuff between DASD and DURR. I didn't want to change the leading portion of my job but I wanted my job to be sorted first. I open an ASCII table and find a useful character that sorts before the dash. Ah, asterisk, ASCII 42 comes before dash, ASCII 45.

Well, that was unexpected. In my reproduction here, the job names will take the form of the literal string "JOB " (trailing space there). I then use a single ASCII character as separator. A use another string literal "CHAR(" and then I display the ASCII ordinal value and for completeness, I close the parenthesis. Thus, JOB * CHAR(42) and JOB - CHAR(45). Assuming I sort ascending alphabetically, which under the sheets I would convert each character to its ASCII value, would lead to me JOB * CHAR(42) on top.

That ain't the way it's being sorted in SSMS though. Let's figure out "is this an application issue or a database issue?" Jobs are stored in the database msdb in a table called sysjobs in the dbo schema. Let's start there.

SELECT
    S.name AS JobName
FROM
    msdb.dbo.sysjobs AS S
WHERE
    S.name LIKE 'JOB%'
ORDER BY
    S.name;

Huh.

Ok, so what goes into sorting? Collations

SELECT
    S.name AS SchemaName
,   T.name AS TableName
,   C.name AS ColumnName
,   T2.name AS DataTypeName
,   C.collation_name AS ColumnCollationName
,   T2.collation_name AS TypeCollationName
FROM
    msdb.sys.schemas AS S
    INNER JOIN
        msdb.sys.tables AS T
        ON T.schema_id = S.schema_id
    INNER JOIN
        msdb.sys.columns AS C
        ON C.object_id = T.object_id
    INNER JOIN
        msdb.sys.types AS T2
        ON T2.user_type_id = C.user_type_id
WHERE
    S.name = 'dbo'
    AND T.name = 'sysjobs'
    AND C.name = 'name';

The name column for dbo.sysjobs is of data type sysname which uses the collation of "SQL_Latin1_General_CP1_CI_AS". If it's the collation causing the "weird" sort, then we should be able to reproduce it, right?

SELECT *
FROM
(
    VALUES
        ('JOB - CHAR(45)' COLLATE SQL_Latin1_General_CP1_CI_AS)
    ,   ('JOB * CHAR(42)' COLLATE SQL_Latin1_General_CP1_CI_AS)
) D(jobName)
ORDER BY
    D.jobName COLLATE SQL_Latin1_General_CP1_CI_AS;

Nope, not the collation since this returns in the expected sort order.

At this point, I waste a lot time going down rabbit holes that this isn't, because in my reproduction was not verbatim. I neglected to preface my strings with an N thus leaving them as ascii strings, not unicode strings.

SELECT *
FROM
(
    VALUES
        (N'JOB - CHAR(45)' COLLATE SQL_Latin1_General_CP1_CI_AS)
    ,   (N'JOB * CHAR(42)' COLLATE SQL_Latin1_General_CP1_CI_AS)
) D(jobName)
ORDER BY
    D.jobName COLLATE SQL_Latin1_General_CP1_CI_AS;

Running that, we get the same sort from sysjobs. At this point, I remember something about unicode sorting being different than old school dictionary sort like I was expecting. And after finding this answer on collations I'm happy simply setting my quest aside and stepping away from the keyboard.

Oh, but if you want to see what the glorious sort order is for characters in the printable range (32 to 127), my script is below. Technically, 127 is a cheat since it's the DELETE but I include it because of where it sorts.

Make the jobs

This script has two templates in it - @MischiefManaged deletes a job and @Template creates a job. I query against sys.all_columns to get a sequential set of numbers from 1 to (127 -32). I use that number and string concatenation (requires 2012+) plus the CHAR function to translate the number into the corresponding ASCII character. It will print out "JOB ' CHAR(39)" once complete because I'm lazy.

DECLARE
    @Template nvarchar(max) = N'
use msdb;
IF EXISTS (SELECT * FROM dbo.sysjobs AS S WHERE S.name = ''<JobName/>'')
BEGIN
    EXECUTE dbo.sp_delete_job @job_name = ''<JobName/>'';
END
EXECUTE dbo.sp_add_job
    @job_name = N''<JobName/>''
,   @enabled = 1
,   @notify_level_eventlog = 0
,   @notify_level_email = 2
,   @notify_level_page = 2
,   @delete_level = 0
,   @category_name = N''[Uncategorized (Local)]'';

EXECUTE dbo.sp_add_jobserver
    @job_name = N''<JobName/>''
,   @server_name = @@SERVERNAME;

EXEC dbo.sp_add_jobstep
    @job_name = N''<JobName/>''
,   @step_name = N''MinimumViableJob''
,   @step_id = 1
,   @cmdexec_success_code = 0
,   @on_success_action = 2
,   @on_fail_action = 2
,   @retry_attempts = 0
,   @retry_interval = 0
,   @os_run_priority = 0
,   @subsystem = N''TSQL''
,   @command = N''SELECT 1''
,   @database_name = N''msdb''
,   @flags = 0;

EXEC dbo.sp_update_job
    @job_name = N''<JobName/>''
,   @start_step_id = 1;
'
,   @MischiefManaged nvarchar(4000) = N'
use msdb;
IF EXISTS (SELECT * FROM dbo.sysjobs AS S WHERE S.name = ''<JobName/>'')
BEGIN
    EXECUTE dbo.sp_delete_job @job_name = ''<JobName/>'';
END'
,   @Token sysname = '<JobName/>'
,   @JobName sysname
,   @Query nvarchar(max);

DECLARE
    CSR CURSOR
FAST_FORWARD
FOR
SELECT
    J.jobName
FROM
(
    SELECT TOP (127-31)
        31 + (ROW_NUMBER() OVER (ORDER BY (SELECT NULL))) AS rn
    FROM sys.all_columns AS AC
) D(rn)
    CROSS APPLY
    (
        SELECT
            CONCAT('JOB ', CHAR(D.rn), ' CHAR(', D.rn, ')')
    )J(jobName)

OPEN CSR;
FETCH NEXT FROM CSR INTO @JobName;

WHILE @@FETCH_STATUS = 0
BEGIN
    BEGIN TRY  
        SET @Query = REPLACE(@Template, @Token, @JobName);
        ---- Uncomment the following to clean up our jobs
        --SET @Query = REPLACE(@MischiefManaged, @Token, @JobName);
        EXECUTE sys.sp_executesql @Query, N'';
    END TRY
    BEGIN CATCH
        PRINT @JobName;
    END CATCH
    FETCH NEXT FROM CSR INTO @JobName;
END
CLOSE CSR;
DEALLOCATE CSR;

At this point, you can refresh the Jobs list in SSMS and the result is this job sort.

Once you're satisfied with how things look, uncomment this line SET @Query = REPLACE(@MischiefManaged, @Token, @JobName); and rerun the script. All will be cleaned up.

Let's just chalk sorting up there with timezones, ok? Sounds easy but isn't. If you know more than me, please explain away in the comments section and share your knowledge.