Data Type details from sphelp

02 Oct 2012

I wanted to learn about data types. Then I went to TechNet somehow most of the important things were there in TechNet documentation. But I found another way to get figures from SQL Server. We can have details of data types from “sphelp”. You just have to give your data type as a parameter like below.

EXEC sp_help int

Then I executed this to every data type except few system data types. And I listed figures on categories which was in TechNet.

Exact Numerics

Type Name	Length	Precision	Scale	Nullable
bigint	8	19	0	Y
bit	1	1	Null	Y
decimal	17	38	38	Y
int	4	10	0	Y
money	8	19	4	Y
numeric	17	38	38	Y
smallint	2	5	0	Y
smallmoney	4	10	4	Y
tinyint	1	3	0	Y

Approximate Numerics

Type Name	Length	Precision	Scale	Nullable
float	8	53	Null	Y
real	4	24	Null	Y

Date and Time

Type Name	Length	Precision	Scale	Nullable
date	3	10	0	Y
datetime2	8	27	7	Y
datetime	8	23	3	Y
datetimeoffset	10	34	7	Y
smalldatetime	4	16	0	Y
time	5	16	7	Y

Binary Strings

Type Name	Length	Precision	Scale	Nullable
binary	8000	8000	Null	Y
image	16	2147483647	Null	Y
varbinary	8000	8000	Null	Y

Character Strings

Type Name	Length	Precision
char	8000	8000
varchar	8000	8000
text	16	2147483647

Unicode Character Strings

Type Name	Length	Precision
nchar	8000	4000
nvarchar	8000	4000
ntext	16	1073741823

Other Data Types

Type Name	Length	Precision	Nullable
hierarchyid	892	892	Y
sql_variant	8016	0	Y
timestamp	8	8	N
uniqueidentifier	16	16	Y
xml	-1	-1	Y

If I haven’t specified any attribute of those data types usually they have their default values. One special thing was there was only one data type which was not nullable. It is “timestamp”. And I was unable to take data of cursor and table from this method.

Data Type details from sphelp

Tags