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.
The interview question that came to my mind was:
Why is there a negative “(-)” sign in the sp_help output?
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.