Columns Without Data Type
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(P1-S1, P2-S2) + 1
|
MAX(S1, S2)
|
e1 - e2
|
MAX(S1, S2) + MAX(P1-S1, P2-S2) + 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(P1-S1, P2-S2)
|
MAX(S1, S2)
|
e1 % e2
|
MIN(P1-S1, P2 -S2) + MAX( S1,S2 )
|
MAX(S1, S2)
|
References
Related tropics
http://sqlknowledgebank.blogspot.in/2012/05/computed-columns.html
DATETIME in WHERE Clause
Introductions
I saw that a lot of developer makes some common mistake when they are working with DATETIME data type in T-SQL statement. In this article I am trying to gather some common tips and tricks related to DATETIME data type when used in WHERE clause of SQL Query.
Point in focus
1. Understanding the DATETIME data type
2. Understanding current session date format
3. SET DATE FORMAT
4. Is my input string is supported DATETIME data type.
5. Best SQL Statement to compare DATETIME data type
6. Related tropics
Understanding the DATETIME data type
Before using the DATETIME data type in WHERE clause of SQL query, it is better to understand the data type properly.
DATETIME data type contains DATE + TIME of day with fractional seconds that is based on a 24-hour clock.
DATE range: January 1, 1753, through December 31, 9999
TIME range: 00:00:00 through 23:59:59.997
Element ranges:
1. YYYY is four digits from 1753 through 9999 that represent a year.
2. MM is two digits, ranging from 01 to 12, that represent a month in the specified year.
3. DD is two digits, ranging from 01 to 31 depending on the month that represent a day of the specified month.
4. hh is two digits, ranging from 00 to 23, that represent the hour.
5. mm is two digits, ranging from 00 to 59, that represent the minute.
6. ss is two digits, ranging from 00 to 59, that represent the second.
7. n* is zero to three digits, ranging from 0 to 999, that represent the fractional seconds.
Character length: 19 positions minimum to 23 maximum
Storage size: 8 bytes
Understanding current session date format
To understand what date format my current session is supported, please run the query mentioned bellow.
SELECT date_format
FROM sys.dm_exec_sessions
WHERE session_id=@@SPID
Output:
date_format
mdy
So my current session supports the date format mdy that means Month-Day-Year
SET DATE FORMAT
SET DATEFORMAT is used to Sets the order of the date parts (month/day/year).
For example:
SET DATEFORMAT DMY
GO
SELECT date_format
FROM sys.dm_exec_sessions
WHERE session_id=@@SPID
GO
Output:
date_format
dmy
Is my input string is supported DATETIME data type
When we supply some parameter in WHERE condition of SQL server to compare with DATETIME data type, we must check that is the input string parameter supports the DATETIME data type. This is done by ISDATE().Returns 1 if the expression is a valid DATE, TIME, or DATETIME value; otherwise, 0.
For example:
SELECT ISDATE('22-01-2012')
It returns 0 as the default date format is mdy.
Now use this.
SET DATEFORMAT DMY
GO
SELECT ISDATE('22-01-2012')
GO
It returns 1.
Best SQL Statement to compare DATETIME data type
To understand it properly, I am going to demonstrate an example.
Step-1 [ Create the Base Table ]
CREATE TABLE my_DATE
(ID INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
VAL DATETIME NOT NULL);
Step-2 [ Inserting some value on it ]
INSERT INTO my_DATE
(VAL)
VALUES('01-22-2012 10:00:00'),
('01-22-2012 11:00:00'),
('01-22-2012 12:00:00'),
('01-22-2012 12:02:00'),
('01-22-2012 12:05:00'),
('01-22-2012 12:07:00')
Please look at the Insert statement carefully as the value contains DATE + TIME
Step-3 [ Now make a query to retrieve data for date 01-22-2012 ]
If we make this query like this
SELECT * FROM my_DATE WHERE VAL='01-22-2012'
It will not return any thing as TIME part is added with date part. The above query try to retrieve only the date part and 00:00:00 as time part.
We can individually retrieve a date with time by this
SELECT * FROM my_DATE WHERE VAL='01-22-2012 10:00:00'
Output:
ID VAL
1 2012-01-22 10:00:00.000
Step-4 [ So How to retrieve data for date 01-22-2012 ]
This can be done by
SELECT * FROM my_DATE WHERE DATEDIFF(day, val, '01-22-2012')=0
Output:
ID VAL
1 2012-01-22 10:00:00.000
2 2012-01-22 11:00:00.000
3 2012-01-22 12:00:00.000
4 2012-01-22 12:02:00.000
5 2012-01-22 12:05:00.000
6 2012-01-22 12:07:00.000
Related tropics
1. BETWEEN Clause Vs [ >= AND <= ]
Hope you like it.
