Category Archives: #SQLServer

All about Microsoft SQL Server

#0311 – SQL Server – Database files upgraded from version nnnn to mmm – why databases cannot be restored backwards?

#0310 – SQL Server – CONCAT_NULL_YIELDS_NULL property

In the world of electronics and even in case of databases, we know that a NULL is different from a 0 value or a blank string. A NULL stands for undefined value, however a 0 or a blank string is some definite value.

I was recently asked how to prevent string concatenation operations with NULL from resulting into NULL.

When I first heard about the question, my response was – “NULL and blank strings have separate meanings. Why would you want to treat these values as one and the same during a concatenation operation?” I was told that the script had to generate a single address field by concatenating Address Lines 1 & 2, City, State, Postal Code and related information. Because fields like the Address Line 2 may be NULL, then the default behaviour would be that the entire result will become NULL.

The team were looking for validating one of the options that they had come up with, which is what prompted this post.

Let’s take an example:

USE AdventureWorks2012;

GO

SELECT pp.FirstName,

       pp.LastName,

       pbea.AddressTypeID,

       pa.AddressLine1 AS AddressLine1,

       pa.AddressLine2 AS AddressLine2,

       pa.AddressLine1 + CHAR(10) +

       pa.AddressLine2 + CHAR(10) +

       psp.StateProvinceCode + ‘ – ‘ +

       psp.CountryRegionCode AS PersonAddress

FROM HumanResources.Employee AS hre

INNER JOIN Person.Person AS pp ON hre.BusinessEntityID = pp.BusinessEntityID

INNER JOIN Person.BusinessEntityAddress AS pbea ON hre.BusinessEntityID = pbea.BusinessEntityID

INNER JOIN Person.Address AS pa ON pbea.AddressID = pa.AddressID

INNER JOIN Person.StateProvince AS psp ON pa.StateProvinceID = psp.StateProvinceID;

GO

The AddressLine2 column in the Person.Address table is NULL for a lot of the records in the AdventureWorks2012 sample database. Do note that AddressLine1 is not a NULL value. The screenshot below shows the default behaviour of SQL Server when we execute the query provided above.

Changing concatenation behaviour of NULL at the query level

To change the behaviour of string concatenation with respect to NULL, one can use the SET option – CONCAT_NULL_YIELDS_NULL to OFF. Here’s a quick modification to the script above that demonstrates the usage of this SET option.

USE AdventureWorks2012;

GO
SET CONCAT_NULL_YIELDS_NULL OFF
SELECT pp.FirstName,

       pp.LastName,

       pbea.AddressTypeID,

       pa.AddressLine1 AS AddressLine1,

       pa.AddressLine2 AS AddressLine2,

       pa.AddressLine1 + CHAR(10) +

       pa.AddressLine2 + CHAR(10) +

       psp.StateProvinceCode + ‘ – ‘ +

       psp.CountryRegionCode AS PersonAddress

FROM HumanResources.Employee AS hre

INNER JOIN Person.Person AS pp ON hre.BusinessEntityID = pp.BusinessEntityID

INNER JOIN Person.BusinessEntityAddress AS pbea ON hre.BusinessEntityID = pbea.BusinessEntityID

INNER JOIN Person.Address AS pa ON pbea.AddressID = pa.AddressID

INNER JOIN Person.StateProvince AS psp ON pa.StateProvinceID = psp.StateProvinceID;
–Safety Check – turn CONCAT_NULL_YEILDS_NULL back ON

SET CONCAT_NULL_YIELDS_NULL ON

GO

The result of the query execution is shown below.

Changing concatenation behaviour of NULL at the database level

If the entire database requires that concatenation with a NULL should yield NULL, then it would be a good idea to also set the option on the database to OFF using the ALTER DATABASE statement shown below:

ALTER DATABASE AdventureWorks2012 SET CONCAT_NULL_YIELDS_NULL OFF

GO

The Correct Way

Personally, I would not go with either of the two options provided above for two reasons:

At the end of the day, NULL is different from a 0 or blank string. Both have distinct meanings and must not be mixed with each other
The second reason is that if we look at Books-On-Line, it clearly tells us that:

In a future version of SQL Server CONCAT_NULL_YIELDS_NULL will always be ON and any applications that explicitly set the option to OFF will generate an error. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

The method that I consider to be the correct way in addressing the NULL concatenation problem is to use the ISNULL or COALESCE function as shown in the query below.

USE AdventureWorks2012 ;

GO
SELECT pp.FirstName,

       pp.LastName,

       pbea.AddressTypeID,

       pa.AddressLine1 AS AddressLine1,

       pa.AddressLine2 AS AddressLine2,

       pa.AddressLine1 + CHAR(10) +

       ISNULL(pa.AddressLine2, ”) + CHAR(10) +

       psp.StateProvinceCode + ‘ – ‘ +

       psp.CountryRegionCode AS PersonAddress

FROM HumanResources.Employee AS hre

INNER JOIN Person.Person AS pp ON hre.BusinessEntityID = pp.BusinessEntityID

INNER JOIN Person.BusinessEntityAddress AS pbea ON hre.BusinessEntityID = pbea.BusinessEntityID

INNER JOIN Person.Address AS pa ON pbea.AddressID = pa.AddressID

INNER JOIN Person.StateProvince AS psp ON pa.StateProvinceID = psp.StateProvinceID;

GO

#0309 – SQL Server – ALTER TABLE – Use the WITH VALUES to populate default values when adding new columns

1 Reply

Last week, Madhivanan (B) wrote an informative post on “Adding/Altering a column with default value”. The post outlines a general guideline on the steps required when adding a new column (or altering an existing column) with a default value constraint on it.

The post prompted me to revisit the Books On Line page for the ALTER TABLE statement and with subsequent research, I realized that SQL Server actually empowers us with different methods to work with default values depending upon whether we are adding a new column to an existing table or when we are altering an existing column.

Adding a new column to an existing table

If the column is being added to an existing table and has a default definition available, then the ALTER TABLE statement allows us to use the WITH VALUES clause to store the default value in each existing record of the table. Let’s study the usage of the WITH VALUES clause with an example.

Adding a new, NULL-able column with default values

The query below is quite simple. It builds upon the example from Madhivanan’s post, so the overall structure and test data are similar.

Create a new table with some columns
Add some test data into the column
Alter the table with add a new, NULL-able column with a default constraint
In the same ALTER TABLE statement, use with WITH VALUES clause to also populate the columns
Finally, select data from the table to see the output

USE tempdb;

GO

–Safety Check

IF OBJECT_ID(‘dbo.test’,’U’) IS NOT NULL

    DROP TABLE dbo.test;

GO
–Create the test table

CREATE TABLE dbo.test ( Col1 INT );

GO
–Insert some test data

INSERT INTO dbo.test ( Col1 )

SELECT 1 UNION ALL

SELECT 2 UNION ALL

SELECT 3 UNION ALL

SELECT 4 UNION ALL

SELECT 5;

GO
–Add a new, NULL-able column with default constraint

–Use with WITH VALUES clause to also populate the columns

ALTER TABLE dbo.test

    ADD col2 INT DEFAULT(0)

    WITH VALUES;

GO
–Select from the table to check the data

SELECT Col1,

       Col2

FROM dbo.test;

GO
–Confirm the Column properties

SELECT isc.TABLE_SCHEMA,

       isc.TABLE_NAME,

       isc.COLUMN_NAME,

       isc.IS_NULLABLE,

       isc.COLUMN_DEFAULT

FROM INFORMATION_SCHEMA.COLUMNS AS isc

WHERE isc.TABLE_SCHEMA = ‘dbo’

  AND isc.TABLE_NAME = ‘test’

  AND isc.COLUMN_NAME = ‘Col2’;

GO

The result is as shown below. As you can see, SQL Server added the new column and also populated existing records with the default value of 0.

Adding a new, NOT NULL column with default values

The same example can be repeated for a NOT NULL column with similar results. The script for the same is shown below.

USE tempdb;

GO

–Safety Check

IF OBJECT_ID(‘dbo.test’,’U’) IS NOT NULL

    DROP TABLE dbo.test;

GO
–Create the test table

CREATE TABLE dbo.test ( Col1 INT );

GO
–Insert some test data

INSERT INTO dbo.test ( Col1 )

SELECT 1 UNION ALL

SELECT 2 UNION ALL

SELECT 3 UNION ALL

SELECT 4 UNION ALL

SELECT 5;

GO
–Add a new, NOT NULL column with default constraint

–Use with WITH VALUES clause to also populate the columns

ALTER TABLE dbo.test

    ADD col2 INT NOT NULL DEFAULT(0)

    WITH VALUES;

GO
–Select from the table to check the data

SELECT Col1,

       Col2

FROM dbo.test;

GO
–Confirm the Column properties

SELECT isc.TABLE_SCHEMA,

       isc.TABLE_NAME,

       isc.COLUMN_NAME,

       isc.IS_NULLABLE,

       isc.COLUMN_DEFAULT

FROM INFORMATION_SCHEMA.COLUMNS AS isc

WHERE isc.TABLE_SCHEMA = ‘dbo’

  AND isc.TABLE_NAME = ‘test’

  AND isc.COLUMN_NAME = ‘Col2’;

GO

The results are shown in the screenshot below. As you can see, the WITH VALUES clause added the NOT NULL new column after adding default values to the existing records.

Altering an existing column

The method highlighted by Madhivanan (B) in his post – “Adding/Altering a column with default value” is the only option that I know of based on my research. The basic steps therefore are:

Add the default constraint on the column (if applicable)
Update the NULL values on already existing records with the desired default value
Alter the column to be NOT NULL

#0308 – SQL Server – INSERT statement – both named parameters and sequence are important

Leave a reply

Working with team(s)/team member(s) new to SQL Server is always fun, and helps me gauge the various aspects of SQL Server that I have been taking for granted, but are not so obvious to all.

One of the basic T-SQL programming rules we have in the team is to never have statements like SELECT * FROM dbo.tableName or INSERT INTO dbo.tableName SELECT fieldList FROM dbo.sourceTable in PoC and production code, which effectively is:

Always use column lists and never rely on the wild-card (*) or implicit column lists.

So, one fine day, one of my team members came up to me with a query. He was facing an issue with one of the INSERT statements. He had ensured that all columns were listed in both the INSERT and SELECT statements and also ensured that the number of columns was also matching. Yet, SQL Server was returning an error upon execution of the INSERT statement.

Allow me to reproduce this situation with an example.

USE tempdb;
GO

--Safety Check
IF OBJECT_ID('dbo.ProductList','U') IS NOT NULL
    DROP TABLE dbo.ProductList;
GO

--Create the table
CREATE TABLE dbo.ProductList (ProductId INT IDENTITY(1,1) NOT NULL,
                              Vendor VARCHAR(50) NOT NULL,
                              ProductName VARCHAR(50) NOT NULL,
                              ProductVersion VARCHAR(20) NOT NULL,
                              IsRetired BIT NOT NULL,
                              RecordDate DATETIME  NOT NULL,
                              CONSTRAINT pk_ProductId 
                                PRIMARY KEY CLUSTERED (ProductId)
                             );
GO

Normally, we would insert data into the table using a simple INSERT statement with a SELECT returning the data to be inserted.

USE tempdb;
GO

--Insert test data
INSERT INTO dbo.ProductList (Vendor, ProductName, ProductVersion, IsRetired, RecordDate)
SELECT 'Microsoft' AS Vendor,
       'Windows' AS ProductName,
       '95' AS ProductVersion,
       1 AS IsRetired,
       GETUTCDATE() AS RecordDate;
GO

However, this team member had the sequence of the columns in the INSERT statement and in the SELECT misaligned, as shown below.

USE tempdb;
GO

--Notice the mismatch in the column listing for the INSERT and SELECT statements
INSERT INTO dbo.ProductList (IsRetired, ProductName, ProductVersion, RecordDate, Vendor)
SELECT 'Microsoft' AS Vendor,
       'Windows' AS ProductName,
       '8' AS ProductVersion,
       0 AS IsRetired,
       GETUTCDATE() AS RecordDate;
GO

This mismatch between the two column lists resulted into the following error:

Msg 245, Level 16, State 1, Line 3

Conversion failed when converting the varchar value ‘Microsoft’ to data type bit.

The error is not actually misleading or incorrect in any way. What SQL Server is actually doing is to match up the ordinal positions of the values in the SELECT statement to the column listing in the INSERT statement. In this case, the first column of the SELECT statement is a VARCHAR column (Vendor) whereas the corresponding column in the INSERT is a BIT column (IsRetired). Because the two cannot be implicitly converted, SQL Server returns an error.

CAUTION!

In the above example, SQL Server returned an error because the implicit conversion failed. However, if the implicit conversion had succeeded, no error would have been returned and we would have ended up with incorrect/bad data in our tables. Here’s an example where the columns on the SELECT statement have been misaligned such that implicit conversion happens:

USE tempdb;
GO
INSERT INTO dbo.ProductList (Vendor, ProductName, ProductVersion, IsRetired, RecordDate)
SELECT 'Microsoft' AS Vendor,
       'Windows' AS ProductName,
       0 AS IsRetired,
       '2012' AS ProductVersion,
       GETUTCDATE() AS RecordDate;
GO

This statement would have generated bad data in our data table – the misaligned columns in the SELECT statement would have gone unnoticed unless someone did a SELECT on the records inserted.

The bottom line therefore is:

When working with column listing for INSERT statements, both the column names and sequence are important!

#0307 – SQL Server – sp_refreshsqlmodule_internal error; Msg 207 and SCHEMABINDING

Leave a reply

Recently, I was called upon by one of the teams in our organization who were attempting to rename a column and had ended up with a weird error when refreshing a view based on the modified table. The error that they were facing was similar to:

Msg 207, Level 16, State 1, Procedure sp_refreshsqlmodule_internal, Line 71
Invalid column name ‘Vendor’.

They had already attempted to refresh the view definition using the sp_refreshview system stored procedure as mentioned in one of my previous posts on refreshing the view metadata for non schema-bound view.

As I finished my investigations with them I realized that their view, although created without SCHEMABINDING, was created with the best practices of having a clearly defined column listing. Changing the names of the underlying columns (or even dropping any column) therefore requires us to drop and recreate the view.

Such views should be evaluated for use of the SCHEMABINDING clause. The SCHEMABINDING clause will prevent the changes to the underlying table because SQL Server would be able to detect that dependent objects will be affected by the change.

Here’s a quick example of how to reproduce the error on your box, and the affect of SCHEMABINDING:

USE tempdb;
GO

/************** STEP 0 - SAFETY CHECK **************/
--Safety Check
IF OBJECT_ID('dbo.vProductList','V') IS NOT NULL
    DROP VIEW dbo.vProductList;
GO

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

/************** STEP 1 - CREATE TEST TABLE, VIEW AND DATA **************/
--Create the table
CREATE TABLE dbo.ProductList (ProductId INT IDENTITY(1,1),
                              Vendor VARCHAR(50),
                              ProductName VARCHAR(50),
                              CONSTRAINT pk_ProductId PRIMARY KEY CLUSTERED (ProductId)
                             );
GO

--Create the view
CREATE VIEW vProductList AS
SELECT pl.ProductId,
       pl.Vendor,
       pl.ProductName
FROM dbo.ProductList AS pl;
GO

--Insert some test data
INSERT INTO dbo.ProductList (Vendor, ProductName)
VALUES ('Microsoft', 'Windows'),
       ('Microsoft', 'SQL Server'),
       ('Microsoft', 'BizTalk')
GO

/************** STEP 2 - Rename the column and attempt to refresh the view **************/
EXEC sp_rename @objname = 'dbo.ProductList.Vendor', @newname = 'VendorName', @objtype = 'COLUMN';
GO

SELECT * FROM dbo.ProductList;
GO

--Now, attempt to refresh the view
EXEC sp_refreshview 'dbo.vProductList';
GO

/************** STEP 3 - Recover from the error **************/
--Recreate the view
IF OBJECT_ID('dbo.vProductList','V') IS NOT NULL
    DROP VIEW dbo.vProductList;
GO

--Create the view
CREATE VIEW vProductList AS
SELECT pl.ProductId,
       pl.VendorName,
       pl.ProductName
FROM dbo.ProductList AS pl;
GO

--SELECT from the view
SELECT * FROM dbo.vProductList;
GO

/************** STEP 4 - Affect of SCHEMABINDING **************/
--Recreate the view
IF OBJECT_ID('dbo.vProductList','V') IS NOT NULL
    DROP VIEW dbo.vProductList;
GO

--Create the view
CREATE VIEW vProductList 
WITH SCHEMABINDING 
AS
SELECT pl.ProductId,
       pl.VendorName,
       pl.ProductName
FROM dbo.ProductList AS pl;
GO

--SELECT from the view
SELECT * FROM dbo.vProductList;
GO

--Now, let us attempt to rename the column in the base table
EXEC sp_rename @objname = 'dbo.ProductList.VendorName', @newname = 'Vendor', @objtype = 'COLUMN';
GO

/************** STEP 99 - CLEANUP **************/
--Cleanup
IF OBJECT_ID('dbo.vProductList','V') IS NOT NULL
    DROP VIEW dbo.vProductList;
GO

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

When SCHEMABINDING is used, SQL Server generates the following error when we attempt to alter the base table:

Msg 15336, Level 16, State 1, Procedure sp_rename, Line 497

Object ‘dbo.ProductList.VendorName’ cannot be renamed because the object participates in enforced dependencies.

SCHEMABINDING therefore helps to prevent breaking any dependent objects as a result of a base object change.

SQLTwins by Nakul Vachhrajani

SQL Server tips and experiences dedicated to my twin daughters.

Category Archives: #SQLServer

#0311 – SQL Server – Database files upgraded from version nnnn to mmm – why databases cannot be restored backwards?

Further Reading:

#0310 – SQL Server – CONCAT_NULL_YIELDS_NULL property

Changing concatenation behaviour of NULL at the query level

Changing concatenation behaviour of NULL at the database level

The Correct Way

Further Reading

#0309 – SQL Server – ALTER TABLE – Use the WITH VALUES to populate default values when adding new columns

Adding a new column to an existing table

Adding a new, NULL-able column with default values

Adding a new, NOT NULL column with default values

Altering an existing column

Further Reading

#0308 – SQL Server – INSERT statement – both named parameters and sequence are important

Further Reading

#0307 – SQL Server – sp_refreshsqlmodule_internal error; Msg 207 and SCHEMABINDING

Further Reading