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-----------***/