One of the first things I do when I start work on a new database is to use “sp_help” to go through each table and study their structure. I recently noticed something that would make an interesting interview question.

Here’s what I saw during my study.

Output of the sp_help command

The interview question that came to my mind was:

Why is there a negative “(-)” sign in the sp_help output?

The answer

The answer is quite simple – the negative sign simply indicates the columns are in a different sort order. By default, when a sort order is not specified for a column on an index, Microsoft SQL Server arranges it in ascending order. When we explicitly specify a descending sort order of the column on the index, it will be reported with the negative “(-)” sign.

Here is the script I used to capture the screenshot seen above:

USE tempdb;
GO
--Safety Check
IF OBJECT_ID('tempdb..#StudentSubject','U') IS NOT NULL
BEGIN
    DROP TABLE #StudentSubject;
END
GO

--Create a temporary table to demonstrate the point under discussion
CREATE TABLE #StudentSubject 
    (StudentId          INT          NOT NULL,
     SubjectId          INT          NOT NULL,
     DayNumber          TINYINT      NOT NULL,
     SequenceNumber     TINYINT      NOT NULL,
     IsCancelled        BIT          NOT NULL 
                        CONSTRAINT df_StudentSubjectIsCancelled DEFAULT (0),
     Remarks            VARCHAR(255)     NULL,
     CONSTRAINT pk_StudentSubject 
                PRIMARY KEY CLUSTERED (StudentId      ASC,
                                       SubjectId      ASC,
                                       DayNumber      DESC,
                                       SequenceNumber DESC
                                      )
    );
GO

--Notice the DESC keyword against the DayNumber & SequenceNumber columns
--These columns will be reported in index with negative values
sp_help '#StudentSubject';
GO

--Cleanup
IF OBJECT_ID('tempdb..#StudentSubject','U') IS NOT NULL
BEGIN
    DROP TABLE #StudentSubject;
END
GO

Until we meet next time,

Be courteous. Drive responsibly.