Tag Archives: DBA

Articles for the DBA – accidental or otherwise

Output of the sp_help command showing negative signs for a few columns.

#0413 – SQL Server – Interview Question – Why are some columns displayed with a negative sign in sp_help?


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 showing negative signs for a few columns.

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.

#0408 – SQL Server – Msg 1750: Could not create constraint or index


An trivial problem came to my desk recently. We were having some issues in creating a table. The script was quite simple, and yet we were facing errors as shown below.

USE tempdb;
GO
IF OBJECT_ID('dbo.ConstraintsCheck','U') IS NOT NULL
    DROP TABLE dbo.ConstraintsCheck;
GO

CREATE TABLE dbo.ConstraintsCheck 
    (RecordId INT NOT NULL IDENTITY(1,1),
     Field1   INT NOT NULL,
     Field2   INT NOT NULL
     CONSTRAINT chk_IsField2GreaterThanField1 CHECK (Field2 > Field1)
    );
GO

The script was being run via an installer, and hence all we got was the last part of the error message:

Msg 1750, Level 16, State 0, Line 7
Could not create constraint or index. See previous errors.

If you have already caught the error, great work! As for us, it took a couple of minutes and running the script via SSMS before we realized that the issue was a just a plain human error.

Here’s the full error that we got when the script was executed in SSMS:

Msg 8141, Level 16, State 0, Line 7
Column CHECK constraint for column 'Field2' references another column, table 'ConstraintsCheck'.
Msg 1750, Level 16, State 0, Line 7
Could not create constraint or index. See previous errors.

The first message that is thrown is the key – it clearly tells us that the CHECK constraint definition cannot be created because it references another column. However, this is a fairly common requirement which is what threw us off.

Finally we realized that we did not have a comma in the T-SQL script before the constraint was defined. Without the comma, SQL Server is trying to create a column constraint, when what we wanted was a table constraint. Here’s the extract from TechNet:

  • A column constraint is specified as part of a column definition and applies only to that column.
  • A table constraint is declared independently from a column definition and can apply to more than one column in a table.

So, we just added the comma to convert the column constraint to a table constraint and we were all set.

USE tempdb;
GO
IF OBJECT_ID('dbo.ConstraintsCheck','U') IS NOT NULL
    DROP TABLE dbo.ConstraintsCheck;
GO

CREATE TABLE dbo.ConstraintsCheck 
    (RecordId INT NOT NULL IDENTITY(1,1),
     Field1   INT NOT NULL,
     Field2   INT NOT NULL, --<-- A comma here makes it a legal Table Constraint
     CONSTRAINT chk_IsField2GreaterThanField1 CHECK (Field2 > Field1)
    );
GO

References:

Until we meet next time,

Be courteous. Drive responsibly.

#0406 – SQL Server – Remember that spaces and blank strings are the same


It was recently brought to my attention that a particular script was passing spaces when it should not. Here’s an example:

DECLARE @spaceCharacter NVARCHAR(1) = N' ';
DECLARE @blankCharacter NVARCHAR(1) = N'';

--Confirm that we are looking at different values
--The ASCII codes are different!
SELECT ASCII(@spaceCharacter) AS ASCIICodeForSpace,
       ASCII(@blankCharacter) AS ASCIICodeForBlankString;

--Compare a blank string with spaces
IF (@spaceCharacter = @blankCharacter)
    SELECT 'Yes' AS IsSpaceSameAsBlankString;
ELSE 
    SELECT 'No' AS IsSpaceSameAsBlankString;
GO

/* RESULTS
ASCIICodeForSpace ASCIICodeForBlankString
----------------- -----------------------
32                NULL

IsSpaceSameAsBlankString
------------------------
Yes
*/

01_Symptom

We then checked the LENGTH and DATALENGTH of both strings and noticed something interesting – the check on the LENGTH was trimming out trailing spaces whereas the check on the DATALENGTH was not.

DECLARE @spaceCharacter NVARCHAR(1) = N' ';
DECLARE @blankCharacter NVARCHAR(1) = N'';

--Check the length
SELECT LEN(@spaceCharacter) AS LengthOfSpace, 
       LEN(@blankCharacter) AS LengthOfBlankCharacter,
       DATALENGTH(@spaceCharacter) AS DataLengthOfSpace, 
       DATALENGTH(@blankCharacter) AS DataLengthOfBlankCharacter;
GO

/* RESULTS
LengthOfSpace LengthOfBlankCharacter DataLengthOfSpace DataLengthOfBlankCharacter
------------- ---------------------- ----------------- --------------------------
0             0                      2                 0
*/

02_LengthAndDataLength

Often, we loose sight of the most basic concepts – they hide in our subconscious. This behaviour of SQL Server is enforced by the SQL Standard (specifically SQL ’92) based on which most RDBMS systems are made of.

The ideal solution for an accurate string comparison was therefore to also compare the data length in addition to a normal string comparison.

DECLARE @spaceCharacter NVARCHAR(1) = N' ';
DECLARE @blankCharacter NVARCHAR(1) = N'';

--The Solution
IF (@spaceCharacter = @blankCharacter) 
   AND (DATALENGTH(@spaceCharacter) = DATALENGTH(@blankCharacter))
    SELECT 'Yes' AS IsSpaceSameAsBlankString;
ELSE 
    SELECT 'No' AS IsSpaceSameAsBlankString;
GO

/* RESULTS
IsSpaceSameAsBlankString
------------------------
No
*/

03_Solution

Further Reading

  • How SQL Server Compares Strings with Trailing Spaces [KB316626]

Until we meet next time,

Be courteous. Drive responsibly.

#0405 – SQL Server – Msg 5133 – Backup/Restore Errors – Directory lookup for file failed – Operating System Error 5(Access is denied.).


We got a new server recently and one of my colleagues ran into an error when restoring a database. The error was a quite generic (reformatted below for readability):

Msg 5133, Level 16, State 1, Line 1
Directory lookup for the file 
"C:\Users\SQLTwins\Documents\AdventureWorks2014\AdventureWorks2014_Data.mdf" 
failed with the operating system error 5(Access is denied.).

Msg 3156, Level 16, State 3, Line 1
File 'AdventureWorks2014_Data' cannot be restored to 
'C:\Users\SQLTwins\Documents\AdventureWorks2014\AdventureWorks2014_Data.mdf'. 
Use WITH MOVE to identify a valid location for the file.

My immediate reaction was to  review the restore script.

USE [master];
GO
RESTORE DATABASE [AdventureWorks2014]
FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Backup\AdventureWorks2014.bak'
WITH
MOVE 'AdventureWorks2014_Data' TO 'C:\Users\SQLTwins\Documents\AdventureWorks2014\AdventureWorks2014_Data.mdf',
MOVE 'AdventureWorks2014_Log' TO 'C:\Users\SQLTwins\Documents\AdventureWorks2014\AdventureWorks2014_Log.ldf';
GO

All looked well, I subsequently moved to the environmental aspect of troubleshooting. It was a new server and we had just created the target folders to which the database was to be restored.

We attempted to restore to the default database locations configured during SQL Server installation and the restore worked. So, one thing that became clear: the SQL Server service did not have appropriate security rights on the destination folder.

The Solution

Once we determined that it was the security rights on the destination folder, the only thing remaining was to grant the rights. Here’s how we do it.

  1. Cross-check the user set as the service user for Microsoft SQL Server database engine (use the SQL Server Configuration Manager for interacting with the SQL Server service – here’s why).
  2. Under Folder properties, ensure that this user has full security rights (or at least equivalent to the rights assigned on the default database folders specified at the time of installation)

Here’s are detailed screenshots showing the above process.

01_SQLServerConfigurationManager

Identifying the user running the SQL Server Database Engine service

02_GrantingPermissions_01

Navigating into file system folder security options to grant access to the SQL Server service

02_GrantingPermissions_02

Choosing the appropriate account running the SQL Server service

02_GrantingPermissions_03

Applying appropriate rights to folder

By the way: If you encounter similar issues in accessing your backup files, the root cause and solution are the same. Check the permissions on the folders housing your backups and you should  see that the database engine does not have the necessary security rights.

Further Reading

Maintaining permissions on data folders and appropriate registry entries is something that is handled by the SQL Server Configuration Manager when you change the service account under which  the database engine is running. If you use services.msc (the old way), this is not done and your SQL Server may stop working.

  • Changing SQL Server Service Account or Password – Avoid restarting SQL Server [Blog Link]
  • Blog Post #0344 – SQL Server – Missing Configuration Manager on Windows 8 [Blog Link]

Until we meet next time,

Be courteous. Drive responsibly.

#0404 – SQL Server – Interview Question – What is logical data integrity?


Recently, I encountered an interesting question in one of the forums:

What is logical data integrity?

The person who posted the question was reading about SQL Server and databases in general, when this term was encountered. Because the answer to this question can help clarify one’s understanding of data design  concepts, I thought it would also make a very interesting interview question as well.

Today, I try to describe that data integrity is.

What is data integrity?

Data is a critical part of any business. But, data by itself holds no value. For data to be information of business value, it needs to be valid with respect to the business domain.

A piece of data may be perfectly acceptable from the physical design perspective, but may be still be invalid for the domain.

Let’s take an example – a rate of 2000 is perfectly acceptable for an integer. That is physical data integrity – the value is valid with respect to the physical design of the database. But, if  we are talking  about an application that captures and analyzes patient/medicinal data, the rate of 2000 is totally invalid and indicates some sort of logical bug/corruption.

Other examples would be a meeting end date that’s less than the meeting start date or a business/person without a name.

A data point may not be acceptable within the business rules defined for a domain. Similarly, what’s valid as a data point for one domain may be invalid for another domain. Ensuring that your database only accepts valid values with respect to your domain is what I call logical data integrity”.

Types of Data Integrity

Logical data integrity can be enforced in two ways:

Declarative Data Integrity

If data  integrity is enforced via the data model (implemented via the Data-Definition-Language, i.e. DDL), it is declarative data  integrity. One would enforce declarative integrity via the elements of the table definition:

  • Appropriate Data-Types
    • In our example for the medical domain, it would limit the possibility of corruption if a TINYINT is used to store the heart rate instead of an INT
  • Primary Keys
    • Avoid the insertion of duplicate data!
  • Foreign Keys
    • Ensures that all references are known (it is a valid primary key in another table)
  • Default, Check, Unique and Not-NULL constraints
    • Unique and Not-NULL constraints help maintain uniqueness and avoid insertion of unknown (NULL) data
    • Usage of default constraints ensure that by default unknown (NULL) values are replaced by valid default values
    • Check constraints help ensure that data meets the valid range defined by the business (e.g. a check constraint would help ensure that the meeting end date is greater than or equal to the start date)

Procedural Data Integrity

Legacy applications (I have worked on a few that match this description) which were originally developed in the days of flat-file databases, often used procedural code to enforce data integrity.

When these were migrated to Microsoft SQL Server, the integrity was enforced via stored procedures and triggers to avoid re-engineering the database structure and changing the application code to match the new structure.

Data integrity enforced via code, i.e. via stored procedures, triggers and/or functions is called procedural data integrity.

My take: Procedural code can be disabled, fail or have bugs. This may cause the application code to generate bad/invalid data rather than prevent it.

I believe procedural data integrity is acceptable as long  as it is used as a “fail-safe” mechanism. The primary mechanism to ensure logical data integrity should be declarative in nature, in my humble opinion.

The above is my take on logical data integrity. I welcome your thoughts on the subject in the space below.

Until we meet next time,

Be courteous. Drive responsibly.