Tag Archives: #TSQL

Articles on T-SQL. This can be a script or a syntax element

#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.

#0403 – SQL Server – CAST/CONVERT to string – Pad zeroes or spaces to an integer


Helping the community via forums often leads to some very interesting moments. Recently, I came across quite a common question – as part of a data migration, someone wanted to pad integers with zeroes. There are various variations to this question, namely:

How do I pad zeroes to  convert an integer to a fixed length string?

How do I pad zeroes before an integer?

How to I pad blank spaces before an integer?

All of these questions have quite a simple solution, which I am going to present before you today.

The script demonstrates the process of padding the required values to a set of integers in a test table. The script:

  1. Converts the Integer to a string
  2. Appends this string representation of the integer to the padding string
  3. Finally, returns the required number of characters from the right of the string

For the purposes of this demo, I have shown the result with two padding characters – a zero (0) and an asterisk (*).

Have you ever faced such a requirement as part of a data migration or an integration? Do you use a similar approach? Do share your thoughts and suggestions in the space below.

--Pad zeroes in string representation of a number
USE tempdb;
GO
--Safety Check
IF OBJECT_ID('dbo.TestTable','U') IS NOT NULL
BEGIN
   DROP TABLE dbo.TestTable;
END
GO

--Create the test tables
CREATE TABLE dbo.TestTable
            (RecordId    INT NOT NULL IDENTITY(1,1),
             RecordValue INT     NULL
            );
GO

--Populate some test data
INSERT INTO dbo.TestTable (RecordValue)
VALUES (123),
       (1023),
       (NULL);
GO

/**************** PADDING CHARACTER: ZERO (0) ****************************/

--Change the padding character and the number of strings as required
DECLARE @requiredStringLength INT = 10;
DECLARE @paddingCharacter CHAR(1) = '0'

--The script:
--1. Converts the Integer to a string
--2. Appends this string representation of the integer to the padding string
--3. Finally, returns the required number of characters from the right of the string
SELECT RecordId,
       RecordValue AS OriginalValue,
       RIGHT( (REPLICATE( @paddingCharacter, @requiredStringLength )
              + CAST(RecordValue AS VARCHAR(20))
              ),
              @requiredStringLength
            ) AS PaddedValue
FROM dbo.TestTable AS tt;
GO

/* RESULTS
RecordId    OriginalValue PaddedValue
----------- ------------- ------------
1           123           0000000123
2           1023          0000001023
3           NULL          NULL

*/

/**************** PADDING CHARACTER: ASTERISK (*) ****************************/

--Change the padding character and the number of strings as required
DECLARE @requiredStringLength INT = 10;
DECLARE @paddingCharacter CHAR(1) = '*'

--The script:
--1. Converts the Integer to a string
--2. Appends this string representation of the integer to the padding string
--3. Finally, returns the required number of characters from the right of the string
SELECT RecordId,
       RecordValue AS OriginalValue,
       RIGHT( (REPLICATE( @paddingCharacter, @requiredStringLength )
               + CAST(RecordValue AS VARCHAR(20))
              ),
              @requiredStringLength
            ) AS PaddedValue
FROM dbo.TestTable AS tt;
GO

/* RESULTS
RecordId    OriginalValue PaddedValue
----------- ------------- ------------
1           123           *******123
2           1023          ******1023
3           NULL          NULL
*/

Until we meet next time,

Be courteous. Drive responsibly.

Screenshots showing that objects have been given default constraint names by SQL Server in case a name was not supplied by the user

#0401 – SQL Server – Script to validate object naming convention


A few weeks ago, I ran into a question on one of the forums asking for a script that can help the team validate object naming conventions. Immediately, I was able to sympathize with the team.

What happens is that when developers use the graphical (GUI) tools in the SQL Server Management Studio (SSMS) or via a simple script, they often fail to specify a name to each individual constraint. These slips are not intentional – developers don’t often realize that each constraint is an independent object because they are ultimately related to  another user defined object (a table).

However, when a name is not explicitly specified for a particular constraint, what Microsoft SQL Server does is provide a name by combining the following:

  1. A standard prefix indicating the object (e.g. “DF” for default constraints)
  2. 9 characters of the object name
  3. 5 characters of the field name
  4. Finally, the unique Id of the object, represented in hexa-decimal format

While this format will always generate a unique value, it would generate names that may not be intuitive. It is therefore a common  practice to review the database code and review for compliance with naming conventions  that have been defined in the product/project.

This logic can be leveraged during code reviews/audits to identify objects where standard project naming conventions are not met.

To demonstrate the functionality of the script, I create one table with a wide range of constraints – none of which have a name specified.

USE [tempdb];
GO
IF OBJECT_ID('dbo.ConstraintsWithoutNames','U') IS NOT NULL
BEGIN
    DROP TABLE dbo.ConstraintsWithoutNames;
END
GO

CREATE TABLE dbo.ConstraintsWithoutNames 
    ([RecordId]     INT          NOT NULL IDENTITY(1,1) 
                                 PRIMARY KEY CLUSTERED,
     [RecordName]   VARCHAR(255)     NULL,
     [RecordStatus] TINYINT      NOT NULL DEFAULT (0) 
                    CHECK ([RecordStatus] IN (0, 2, 4, 8))
    );
GO

Now, the following script is a simple string search that looks for strings ending with the hexa-decimal representation of the parent object.

USE [tempdb];
GO
SELECT * 
FROM [sys].[objects] AS [so]
WHERE [so].[is_ms_shipped] = 0 --Considering user objects only
  AND [so].[name] LIKE ('%' + REPLACE(CONVERT(NVARCHAR(255),CAST([so].[object_id] AS VARBINARY(MAX)),1),'0x',''))
                        --Only those objects whose names end with the hexadecimal
                        --representation of their object Id
Screenshots showing that objects have been given default constraint names by SQL Server in case a name was not supplied by the user

Objects given default constraint names

I  hope you found this script useful. Please do  share your ideas/scripts that you may be using in your day-to-day activities.

Until we meet next time,

Be courteous. Drive responsibly.

Showing the use of OLE DB fast load options when loading data

#0397 – SQL Server – SSIS – OLE DB Destination – SEQUENCE can be used only with fast load options


Traditionally, sequential numbers in a database system were managed by the use of IDENTITY columns. SQL Server 2012 saw the advent of the SEQUENCE object, which can be used to generate a sequence of numeric values according to a user-defined specification. Recently, I was working on an SSIS package which used the OLE DB transformation to load data into a table where one of the columns was populated by the SEQUENCE object. During the development of this  package, I ran into an interesting issue.

Standard load options of the SSIS OLE DB destination throw an error when inserting data into a table where SEQUENCE is used.

The workaround is quite simple – the OLE DB fast load options. Allow me to walk through a sample demonstrating this issue and the workaround.

Assume that we have an extremely basic sample SEQUENCE object which  starts at 1 and increments by 1. This SEQUENCE is then used as a default value for one of the columns in our test table.

USE [tempdb];
GO
--Safety  Check
IF OBJECT_ID('df_RecordId','D') IS NOT NULL
BEGIN
    ALTER TABLE dbo.SequenceTest 
        DROP CONSTRAINT df_RecordId;
END
GO

IF OBJECT_ID('dbo.RecordIdSequence','SO')  IS NOT NULL
BEGIN
    DROP SEQUENCE dbo.RecordIdSequence;
END
GO

IF OBJECT_ID('dbo.SequenceTest','U') IS NOT NULL
BEGIN
    DROP TABLE dbo.SequenceTest;
END
GO

--Create the sequence first
CREATE SEQUENCE dbo.RecordIdSequence
    START WITH  1
    INCREMENT BY 1;
GO

--Use this SEQUENCE on a default constraint
--associated with one of the columns in the table
CREATE TABLE dbo.SequenceTest 
    (RecordId INT NOT NULL 
              CONSTRAINT df_RecordId DEFAULT
              NEXT VALUE FOR dbo.RecordIdSequence,
     RowValue VARCHAR(50)
    );
GO

My SSIS package has nothing else other than a simple Data-Flow Transformation with 1 (one) OLE DB source and 1 (one) OLE DB destination.

The OLE DB source uses the following query to generate the test data.

SELECT t.RowValue
FROM (VALUES ('One'),
             ('Two'),
             ('Three'),
             ('Four')
     ) AS t (RowValue)

The OLE DB destination uses the standard (non fast-load) configuration as shown in the screenshot below.

Showing use of the SSIS OLE DB destination standard table load configuration

OLE DB standard table load configuration

Running this SSIS package results in an error. The error text is provided below.

[OLE DB Destination [2]] Error: SSIS Error Code DTS_E_OLEDBERROR. 
An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. 
Source: "Microsoft SQL Server Native Client 11.0" 
Hresult: 0x80004005 
Description: 
"NEXT VALUE FOR function cannot be used if ROWCOUNT option has been set, 
or the query contains TOP or OFFSET. ".
SSIS table load error when using OLE DB standard load into a table with the SEQUENCE object

SSIS table load error when using OLE DB standard load into a table with the SEQUENCE object

To resolve the error, all we need to do is to  change the OLE DB destination to use fast load options, by choosing “Table or View – fast load” as the “Data access mode“.

Showing the use of OLE DB fast load options when loading data

OLE DB Fast load options

By making this small change, the SSIS package runs successfully, and data is also successfully inserted into the table.

0397-oledb_fastloadconfigurationsuccess0397-oledb_fastloadconfigurationsuccess2

Further Reading

Until we meet next time,

Be courteous. Drive responsibly.