Systems Engineering and RDBMS

Data Type for Computed Columns

Posted by decipherinfosys on October 16, 2008

We have covered computed columns in a couple of our previous blog posts. One of the questions that a reader had recently asked was about the data type of the computed column when using operations on different data-type columns and how can one control it. Let’s pick up an example and go through it:

CREATE TABLE dbo.DIS_COMP_TEST
(
COL1 INT,
COL2 NVARCHAR(10),
COL3 DATETIME,
COL4 UNIQUEIDENTIFIER,
COL5 NUMERIC(11,2),
COL6 AS COL1 + COL5,
COL7 AS ‘FIRST COLUMN:’ + CAST(COL1 AS NVARCHAR(10)) + ‘ SECOND COLUMN:’ + COL2 + ‘ DATE:’ + CONVERT(NVARCHAR(20), COL3, 120),
COL8 AS COL2 + ‘:’ + CAST(COL4 AS NVARCHAR(36))
)
GO

INSERT INTO dbo.DIS_COMP_TEST (COL1, COL2, COL3, COL4, COL5) VALUES (1000, ‘TESTCASE’, GETDATE(), NEWID(), 11111.34);
GO

And let’s look at the data now:

COL1        COL2       COL3                    COL4                                 COL5                                    COL6                                    COL7                                                                       COL8
----------- ---------- ----------------------- ------------------------------------ --------------------------------------- --------------------------------------- -------------------------------------------------------------------------- -----------------------------------------------
1000        TESTCASE   2008-10-16 08:46:23.800 10D1B8DB-3EB8-47C5-91DF-4234705276D2 11111.34                                12111.34                                FIRST COLUMN:1000 SECOND COLUMN:TESTCASE DATE:2008-10-16 08:46:23          TESTCASE:10D1B8DB-3EB8-47C5-91DF-4234705276D2

And if we take a look at the table definition for the computed columns now:

SELECT COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION, NUMERIC_SCALE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = ‘DIS_COMP_TEST’
GO

COLUMN_NAME  DATA_TYPE          CHARACTER_MAXIMUM_LENGTH NUMERIC_PRECISION NUMERIC_SCALE
------------ ------------------ ------------------------ ----------------- -------------
COL1         int                NULL                     10                0
COL2         nvarchar           10                       NULL              NULL
COL3         datetime           NULL                     NULL              NULL
COL4         uniqueidentifier   NULL                     NULL              NULL
COL5         numeric            NULL                     11                2
COL6         numeric            NULL                     13                2
COL7         nvarchar           74                       NULL              NULL
COL8         nvarchar           47                       NULL              NULL

Now, look at the three computed columns. Since we did not specify a specific data type for the columns, it made the data-type as what it came out to be based on the concatenation of the columns – like nvarchar(47) for the last one is because of COL2 (nvarchar(10)) + ‘:’ and COL4 which is uniqueidentifier (so 36 positions) and hence 10 + 1 + 36 = 47. And likewise for the computed column COL7, since we were doing string operations, it took all of them up and added them up to be nvarchar(74). For the one where we are adding the integer and the numeric data type columns – it is the same thing: INT ranges from “-2^31 (-2,147,483,648) to 2^31-1 (2,147,483,647)” and Numeric (11,2) means 9 before the decimal and 2 after. So, if we are going to be concatenating them, we need to allow for atleast 11 before the decimal and 2 after and hence the resulting data type is Numeric(13,2).

Now, having said that, it is always a good practice to actually cast the data-type to be something specific so that one does not run into such issues. Here is an example:

DROP TABLE DIS_COMP_TEST;
CREATE TABLE dbo.DIS_COMP_TEST
(
COL1 TINYINT,
COL2 TINYINT,
COL3 AS COL1 * COL2,
)
GO

As you already know, the valid value range for the tinyint data type is: 0 to 255. Let’s try to insert a record into this table:

INSERT INTO dbo.DIS_COMP_TEST (COL1, COL2) VALUES (255, 255);

This does not give us an error. However, if you select from this table now, you will get the error:

SELECT * FROM DIS_COMP_TEST;

COL1 COL2 COL3
—- —- —-
Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type tinyint.

There are two things to note here: Had we created an index on the computed column, the record would have failed at the time of the write operation itself. Since the index did not exist, it computes the value for the computed column at the time of the selection of the data. Here is the proof:

DROP TABLE DIS_COMP_TEST;
CREATE TABLE dbo.DIS_COMP_TEST
(
COL1 TINYINT,
COL2 TINYINT,
COL3 AS COL1 * COL2,
)
GO

CREATE INDEX DIS_COMP_TEST_IND_1 ON DIS_COMP_TEST (COL3);

INSERT INTO dbo.DIS_COMP_TEST (COL1, COL2) VALUES (255, 255);

Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type tinyint.
The statement has been terminated.

And how can we overcome this issue? By explicitly casting the column:

CREATE TABLE dbo.DIS_COMP_TEST
(
COL1 TINYINT,
COL2 TINYINT,
COL3 AS CAST(COL1 AS INT) * CAST(COL2 AS INT)
)
GO

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: