SQL Basic - Data Types

Char / nChar: Fixed length character, nChar is used for unicode / Special characters

VarChar / nVarChar / VarChar(Max) / nVarChar(Max): Variable length character, max limit of VarChar is 4000 character, max length of nVarChar is 2000 character, max length of VarChar(Max) is 4 GB, max length of nVarChar(Max) is 2 GB

Datetime2 / DateTime / SmallDateTime / Date / Time: For DateTime Range January 1, 1753, through December 31, 9999 , a single datetime variable need 8 bytpe, internally first four bytes represent days, no of days before and after 1 January 1900, remaining 4 bytes represent time in milliseconds midnight

DateTimeOffset: Datetime with timezone, here hours would be in 24 hour format and time zone range in between 0 to 14

TinyInt(0 to 255), smallint (-32768 to 32768), int, bigint

Decimal / Float / Real / Numeric:  store decimal values

Bit: Store 0 or 1 value, smallest data type


Spatial Datatype: Spatial data represents information about the physical location and shape of geometric objects.
There are 2 type of spatial datatypes in sql server Geometry and Geography.
Point, LineString, CircularString, CompundCurve, Polygon, CurvePolygon, MultiPoint, MultiPolygon, MultiLineString are methods.
DECLARE @g1 geometry;
DECLARE @g2 geometry;
SET @g1 = geometry::STGeomFromText('LINESTRING(1 1, 5 1, 3 5, 1 1, 3 3)', 0);
--SET @g1 = geometry::STGeomFromText('LINESTRING(0 0, 2 2, 4 0)', 0);
SET @g2 = geometry::STGeomFromText('CIRCULARSTRING(0 0, 2 2, 4 0)', 0);
IF @g1.STIsValid() = 1 AND @g2.STIsValid() = 1
  BEGIN
      --SELECT @g1.ToString()--, @g2.ToString()
      --SELECT @g1.STLength() AS [LS Length]--, @g2.STLength() AS [CS Length]
   SELECT  @g1
  END

Sql_Variant: The Sql_Variant datatype is use to store data from many different datatypes

SELECT SQL_VARIANT_PROPERTY(@v,'BaseType')

, SQL_VARIANT_PROPERTY(@v,'TotalBytes')

, SQL_VARIANT_PROPERTY(@v,'MaxLength')

As Microsoft recomeded, we should avoid using SQL Server’s sql_variant data type. Some limitations:

Variants won’t work with LIKE in a WHERE clause.

OLE DB and ODBC providers automatically convert variants to nvarchar(4000)