Data Type details from sphelp

I wanted to learn about data types. Then I went to technet some how most of the important things were there in technet documentations. 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 were 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.

Advertisements

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