Introduction
"Can you make a table with CREATE TABLE statement, where there are 4 columns and 1 of the columns is without data type?"
If we heard this above statement, we definitely think for 2 to 3 seconds. That the columns without data type?
The fact is not like that. The columns without data type are not possible. If we look at the above statement carefully it says CREATE TABLE statement… some kind of syntax.
It is taking about COMPUTED COLUMNS.
Here in this article, I am not going to discuss about the COMPUTED COLUMNS. Here I am trying to discuss about the DATA TYPE, PRECISION and SCALE of the computed columns.
Example 1
First we take an example of COMPUTED COLUMNS with CREATE TABLE statement to understand the data type of computed columns.
IF OBJECT_ID('TBL_EMPLOYEE') IS NOT NULL
BEGIN
DROP TABLE TBL_EMPLOYEE;
END
GO
CREATE TABLE TBL_EMPLOYEE
(
EMPID INT IDENTITY(1,1) PRIMARY KEY,
EMPSAL DECIMAL(20,2) NOT NULL,
EMPGRADE AS (CASE WHEN EMPSAL>=20000 THEN 'A'
WHEN EMPSAL>=10000 AND EMPSAL<20000 THEN 'B'
WHEN EMPSAL>=1 AND EMPSAL<10000 THEN 'C' END)
);
GO
 Insert Some record
INSERT INTO TBL_EMPLOYEE
(EMPSAL)
VALUES (5000),(10000),(12000),(15000),(2000),(22000)
 Dispaly records
SELECT * FROM TBL_EMPLOYEE;
Result set:
EMPID EMPSAL EMPGRADE
  
1 5000.00 C
2 10000.00 B
3 12000.00 B
4 15000.00 B
5 2000.00 C
6 22000.00 A
(6 row(s) affected)
Now we type to find the Data type of COMPUTED COLUMNS named "EMPGRADE".
SELECT COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH,
NUMERIC_PRECISION, NUMERIC_SCALE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'TBL_EMPLOYEE'
Result set:
COLUMN_NAME

DATA_TYPE

CHARACTER_MAXIMUM_LENGTH

NUMERIC_PRECISION

NUMERIC_SCALE

EMPID

int

NULL

10

0

EMPSAL

decimal

NULL

20

2

EMPGRADE

varchar

1

NULL

NULL

So for the COMPUTED COLUMNS named "EMPGRADE" the data type is VARCHAR and the size is 1. So it the DATA TYPE of COMPUTED COLUMNS depends on what it stores. Please have a look of the CREATE TABLE syntax example again.
CREATE TABLE TBL_EMPLOYEE
(
EMPID INT IDENTITY(1,1) PRIMARY KEY,
EMPSAL DECIMAL(20,2) NOT NULL,
EMPGRADE AS (CASE WHEN EMPSAL>=20000 THEN 'A'
WHEN EMPSAL>=10000 AND EMPSAL<20000 THEN 'B'
WHEN EMPSAL>=1 AND EMPSAL<10000 THEN 'C' END)
);
Please look at the marked line. In columns named "EMPGRADE" is CASE statement the input value is one character length. So it takes VARCHAR(1) as data types.
Example 2
To understand it properly, we are taken an little bit complex example to understand data type and width.
IF OBJECT_ID('TBL_EMPLOYEE') IS NOT NULL
BEGIN
DROP TABLE TBL_EMPLOYEE;
END
GO
CREATE TABLE dbo.TBL_COLUMNSPLEX
(
COLUMNS1 DECIMAL(20,2),
COLUMNS2 NVARCHAR(10),
COLUMNS3 DATETIME,
COLUMNS4 DECIMAL(10,2),
COLUMNS5 AS COLUMNS1 + COLUMNS4,
COLUMNS6 AS '1 ST COLUMNS :' + CAST(COLUMNS1 AS NVARCHAR(10)) +
'2 ND COLUMNS :' + COLUMNS2 +
'3 RD COLUMNS :' + CONVERT(NVARCHAR(20), COLUMNS3, 120),
COLUMNS7 AS COLUMNS2 + ' : ' + CAST(COLUMNS4 AS NVARCHAR(36))
)
GO
 Insert Some record
INSERT INTO TBL_COLUMNSPLEX
(COLUMNS1, COLUMNS2, COLUMNS3, COLUMNS4)
VALUES (100, 'JOYDEEP', GETDATE(), 200.22)
GO
 Dispaly records
SELECT * FROM TBL_COLUMNSPLEX
Now we type to find the Data type of COMPUTED COLUMNS.
SELECT COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH,
NUMERIC_PRECISION, NUMERIC_SCALE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'TBL_COLUMNSPLEX'
COLUMN_NAME

DATA_TYPE

CHARACTER_MAXIMUM_LENGTH

NUMERIC_PRECISION

NUMERIC_SCALE

COLUMNS1

decimal

NULL

20

2

COLUMNS2

nvarchar

10

NULL

NULL

COLUMNS3

datetime

NULL

NULL

NULL

COLUMNS4

decimal

NULL

10

2

COLUMNS5

decimal

NULL

21

2

COLUMNS6

nvarchar

82

NULL

NULL

COLUMNS7

nvarchar

49

NULL

NULL

Now discuss about DATATYPE and size of the COMPUTED COLUMNS.
Here are the computed columns are "COLUMNS5", "COLUMNS6", "COLUMNS7".
Here "COLUMNS5" Data type is DECIMAL. Precision is 21 and the Scale is 2. To understand it properly, how the precision and scale is set, we aging make a closer look of CREATE TABLE statement.
CREATE TABLE dbo.TBL_COLUMNSPLEX
(
COLUMNS1 DECIMAL(20,2),
COLUMNS2 NVARCHAR(10),
COLUMNS3 DATETIME,
COLUMNS4 DECIMAL(10,2),
COLUMNS5 AS COLUMNS1 + COLUMNS4,
COLUMNS6 AS '1 ST COLUMNS :' + CAST(COLUMNS1 AS NVARCHAR(10)) +
'2 ND COLUMNS :' + COLUMNS2 +
'3 RD COLUMNS :' + CONVERT(NVARCHAR(20), COLUMNS3, 120),
COLUMNS7 AS COLUMNS2 + ' : ' + CAST(COLUMNS4 AS NVARCHAR(36))
)
GO
Here we are taking Precision and P, Scale as S and Expression E.
Precision Calculation
Here the COLUMNS5 = COLUMNS1(20,2) + COLUMNS4(10,2)
So the COLUMNS5 = E1 + E2
Formula COLUMNS5 = MAX(S1, S2) + MAX(P1 – S1, P2 – S2) + 1
Putting the Values = MAX(2, 2) + MAX(20  2 , 10 – 2) + 1
= MAX(2, 2) + MAX(18 , 8) + 1
= 2 + 18 +1
= 21
Scale Calculation
Here the COLUMNS5 = COLUMNS1(20,2) + COLUMNS4(10,2)
So the COLUMNS5 = E1 + E2
Formula COLUMNS5 = MAX(S1, S2)
Putting the Values = MAX(2, 2)
= 2
When two char, varchar, binary, or varbinary expressions are concatenated, the length of the resulting expression is the sum of the lengths of the two source expressions or 8,000 characters, whichever is less.
When two nchar or nvarchar expressions are concatenated, the length of the resulting expression is the sum of the lengths of the two source expressions or 4,000 characters, whichever is less.
The numeric operations chart for computed columns are mentioned bellow
Operation

Precision

Scale

e1 + e2

MAX(S1, S2) + MAX(P1S1, P2S2) + 1

MAX(S1, S2)

e1  e2

MAX(S1, S2) + MAX(P1S1, P2S2) + 1

MAX(S1, S2)

e1 * e2

P1 + P2 + 1

S1 + S2

e1 / e2

P1  S1 + S2 + MAX(6, S1 + P2 + 1)

MAX(6, S1 + P2 + 1)

e1 { UNION  EXCEPT  INTERSECT } e2

MAX(S1, S2) + MAX(P1S1, P2S2)

MAX(S1, S2)

e1 % e2

MIN(P1S1, P2 S2) + MAX( S1,S2 )

MAX(S1, S2)

References
Related tropics
Hope you like it.
Posted by: MR. JOYDEEP DAS
Another thing that is important.
ReplyDelete
IF OBJECT_ID('TBL_CONVERSION') IS NOT NULL
BEGIN
DROP TABLE TBL_CONVERSION;
END
GO
CREATE TABLE dbo.TBL_CONVERSION
(
COLUMNS1 TINYINT,
COLUMNS2 TINYINT,
COLUMNS3 AS COLUMNS1 * COLUMNS2
);
GO
CREATE INDEX IX_TBL_CONVERSION ON TBL_CONVERSION (COLUMNS3);
GO
In this case if we try to insert some records

INSERT INTO TBL_CONVERSION
(COLUMNS1, COLUMNS2)
VALUES (255, 255);
It gives and error
Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type tinyint.
The statement has been terminated.
To Solve it

IF OBJECT_ID('TBL_CONVERSION') IS NOT NULL
BEGIN
DROP TABLE TBL_CONVERSION;
END
GO
CREATE TABLE dbo.TBL_CONVERSION
(
COLUMNS1 TINYINT,
COLUMNS2 TINYINT,
COLUMNS3 AS CONVERT(INT, COLUMNS1) * CONVERT(INT, COLUMNS2)
);
GO
CREATE INDEX IX_TBL_CONVERSION ON TBL_CONVERSION (COLUMNS3);
GO
INSERT INTO TBL_CONVERSION
(COLUMNS1, COLUMNS2)
VALUES (255, 255);
Thank you.
Dear readers,
DeletePlease provide the Feedback and your valuable opinion that we can learn a lot for you.
Thanking you.
It’s really nice article. Specially the Precision Calculation and Scale Calculation.
ReplyDeleteThanks "Shipra"
DeleteIt is excellent one. Specially the Precision Calculation and Scale Calculation. Thanks to bring this knowledge infront of us.
ReplyDeleteThanks "Sukamal"
Deleteis the data stored physically for the computed column?
ReplyDeleteThanks "Sridhar" for your Interest.
DeleteQuestion:
is the data stored physically for the computed column?
Answer:
The Database Engine uses the PERSISTED keyword in the CREATE TABLE and ALTER TABLE statements to physically store computed columns in the table.
Web References:
You can find it in my previous post listed in Related Tropics
http://sqlknowledgebank.blogspot.in/2012/05/computedcolumns.html
If have you any query please mail me at me.mr.sqlsercer@gmail.com
I try my best to solve it.