All datatypes basics in SQL Server 2008R2.

/**********Script Start Date: 06-09-2014, Author: Shiva N, purpose : Datatypes in SQL Server 2008R2 in Real time environment-----------***/DBCC FREESYSTEMCACHE('ALL')
GO
IF OBJECT_ID(N'tblWithAllDatatypes') IS NOT NULL
BEGIN
DROP TABLE tblWithAllDatatypes
END
GO
CREATE TABLE tblWithAllDatatypes (
[Bit1] BIT DEFAULT(1) --Supports only "NULL,0,1"
,[Tinyint2] TINYINT DEFAULT(CASE WHEN @@ROWCOUNT=0 THEN 0 ELSE 255 END) -- Does not Supports negative values
,[Smallint3] SMALLINT DEFAULT(CASE WHEN ISNULL(@@ROWCOUNT,0)=0 THEN -32767 ELSE 32767 END) --Limit is: -32767 to +32767
,[Int4] INT DEFAULT(CASE WHEN ISNULL(@@ROWCOUNT,0)=0 THEN -2147483647 ELSE 2147483647 END)
,[Bigint5] BIGINT DEFAULT(CASE WHEN ISNULL(@@ROWCOUNT,0)=0 THEN -9223372036854775807 ELSE 9223372036854775807 END)
,[Float6] FLOAT DEFAULT(12.34)
,[Decimal7] DECIMAL(10,3) DEFAULT(10.01)
,[Smallmoney16] SMALLMONEY DEFAULT(214748)
,[Money17] MONEY DEFAULT(CASE WHEN ISNULL(@@ROWCOUNT,0)=0 THEN -922337203685477.5808 ELSE 922337203685477.5807 END) 
,[Numeric18] NUMERIC(10,8) DEFAULT(10.11) --you cant give NUMERIC(10,11), becuase 10<11.
,[Binary8] BINARY DEFAULT(1)
,[Varbinary9] VARBINARY DEFAULT(010)
,[Uniqueidentifier10] UNIQUEIDENTIFIER
,[Real11] REAL DEFAULT(12.34)
,[Char19] CHAR(10) DEFAULT('Char')
,[Varchar20] VARCHAR(10) DEFAULT('Varchar')
,[Text21] TEXT DEFAULT('Text')
,[Nchar22] NCHAR(10) DEFAULT('据类型为')
,[Nvarchar23] NVARCHAR(10) DEFAULT('数据类型为')
,[Ntext24] NTEXT DEFAULT('数据类型')
,[Image12] IMAGE DEFAULT('a.jpg')
,[Geography13] GEOGRAPHY DEFAULT(GEOGRAPHY::STGeomFromText('LINESTRING(-122.360 47.656, -122.343 47.656 )', 4326))
,[Geometry14] GEOMETRY DEFAULT(GEOMETRY::STGeomFromText('LINESTRING(-122.360 47.656, -122.343 47.656 )', 4326))
,[Sql_variant15] SQL_VARIANT DEFAULT(1)
,[Date25] DATE DEFAULT(GETDATE())
,[Time26] TIME DEFAULT(GETDATE())
,[Smalldatetime27] SMALLDATETIME DEFAULT(GETDATE())
,[Datetime28] DATETIME DEFAULT(GETDATE())
,[Datetime229] DATETIME2 DEFAULT(GETDATE())
,[Timestamp30] TIMESTAMP
,[Datetimeoffset31] DATETIMEOFFSET DEFAULT(GETDATE()+'00:00')
,[Xml32] XML DEFAULT('<xml>Shiva</xml>')
)



INSERT INTO tblWithAllDatatypes (bit1)
select null;
INSERT INTO tblWithAllDatatypes (bit1)
select 0;
INSERT INTO tblWithAllDatatypes (bit1)
select 1;
GO
SELECT * FROM tblWithAllDatatypes
 
/**********Script End Date: 06-09-2014, Author: Shiva N, purpose : Datatypes in SQL server-----------***/