October 02, 2012

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 NameLengthPrecisionScaleNullable
bigint8190Y
bit11NullY
decimal173838Y
int4100Y
money8194Y
numeric173838Y
smallint250Y
smallmoney4104Y
tinyint130Y

Approximate Numerics






















Type NameLengthPrecisionScaleNullable
float853NullY
real424NullY

Date and Time














































Type NameLengthPrecisionScaleNullable
date3100Y
datetime28277Y
datetime8233Y
datetimeoffset10347Y
smalldatetime4160Y
time5167Y

Binary Strings

 



























Type NameLengthPrecisionScaleNullable
binary80008000NullY
image162147483647NullY
varbinary80008000NullY

Character Strings




















Type NameLengthPrecision
char80008000
varchar80008000
text162147483647

Unicode Character Strings




















Type NameLengthPrecision
nchar80004000
nvarchar80004000
ntext161073741823

Other Data Types


































Type NameLengthPrecisionNullable
hierarchyid892892Y
sql_variant80160Y
timestamp88N
uniqueidentifier1616Y
xml-1-1Y

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.

No comments:

Post a Comment