Friday, November 23, 2012

SQL Server Data Types and Ranges


SQL Server Data Types and Ranges



Quick reference for SQL Server data types
Applies to: SQL Server 2000, SQL Server 2005, SQL Server 2008
Saving disk space is not the only benefit of properly selected data types for SQL Server columns. In addition to disk space benefits, the right data types reduce backup times, improve SQL Server performance, and enhance the execution of queries and joins. Here’s a comprehensive list of all SQL Server datatypes , ranges, and limitations:

Exact numerics

Type From To
bigint -9,223,372,036,854,775,808 9,223,372,036,854,775,807
int -2,147,483,648 2,147,483,647
smallint -32,768 32,767
tinyint 0 255
bit 0 1
decimal -10^38 +1 10^38 –1
numeric -10^38 +1 10^38 –1
money -922,337,203,685,477.5808 +922,337,203,685,477.5807
smallmoney -214,748.3648 +214,748.3647
numeric and decimal are Fixed precision and scale data types and are functionally equivalent.

Approximate numerics

Type From To
float -1.79E + 308 1.79E + 308
real -3.40E + 38 3.40E + 38

datetime and smalldatetime

Type From To
datetime (3.33 milliseconds accuracy) Jan 1, 1753 Dec 31, 9999
smalldatetime (1 minute accuracy) Jan 1, 1900 Jun 6, 2079
date (1 day accuracy. Introduced in SQL Server 2008) Jan 1, 0001 Dec 31, 9999
datetimeoffset (100 nanoseconds accuracy. Introduced in SQL Server 2008) Jan 1, 0001 Dec 31, 9999
datetime2 (100 nanoseconds accuracy. Introduced in SQL Server 2008) Jan 1, 0001 Dec 31, 9999
time (100 nanoseconds accuracy. Introduced in SQL Server 2008) 00:00:00.0000000 23:59:59.9999999

Character Strings

Type Description
char Fixed-length non-Unicode character data with a maximum length of 8,000 characters.
varchar Variable-length non-Unicode data with a maximum of 8,000 characters.
varchar(max) Variable-length non-Unicode data with a maximum length of 231 characters (Introduced in SQL Server 2005).
text Variable-length non-Unicode data with a maximum length of 2,147,483,647 characters.

Unicode Character Strings

Type Description
nchar Fixed-length Unicode data with a maximum length of 4,000 characters.
nvarchar Variable-length Unicode data with a maximum length of 4,000 characters.
nvarchar(max) Variable-length Unicode data with a maximum length of 230 characters (Introduced in SQL Server 2005).
ntext Variable-length Unicode data with a maximum length of 1,073,741,823 characters.

Binary Strings

Type Description
binary Fixed-length binary data with a maximum length of 8,000 bytes.
varbinary Variable-length binary data with a maximum length of 8,000 bytes.
varbinary(max) Variable-length binary data with a maximum length of 231 bytes (Introduced in SQL Server 2005).
image Variable-length binary data with a maximum length of 2,147,483,647 bytes.

Other Data Types

  • sql_variant: Stores values of various SQL Server-supported data types, except text, ntext, and timestamp.
  • timestamp: Stores a database-wide unique number that gets updated every time a row gets updated.
  • uniqueidentifier: Stores a globally unique identifier (GUID).
  • xml: Stores XML data. You can store xml instances in a column or a variable (Introduced in SQL Server 2005).
  • cursor: A reference to a cursor.
  • table: Stores a result set for later processing.
  • hierarchyid: A variable length, system data type used to represent position in a hierarchy (Introduced in SQL Server 2008).

Introduction To C-Sharp (C#)


Introduction To C-Sharp (C#)

         
The C# language is disarmingly simple, with only about 80 keyword and a dozen built-in datatypes, but C# is highly expressive, when it comes to implementing modern programming concepts. C# includes all the support for structured, component-based, object-oriented programming that one expects of modern language built on the shoulders of C++ and Java.

The C# language was developed by a small team led by two distinguished Microsoft engineers, Anders Hejisberg and Scott Wiltamuth. Hejisberg is also known for creating Turbo Pascal, a popular language for PC programming, and for leading the team that designed Borland Delphi, one of the first successful integrated development environment for client/server programming.

All the heart of any object-oriented language is its support for defining and working with classes. Classes define new types, allowing you to extend the language to better model the problem you are trying to solve. C# contains keywords for declaring new classes and their methods and properties, and for implementing encapsulation, inheritance and polymorphism the three pillars of object-oriented programming.

In C# everything pertaining to a class declaring is found in the declaration itself. C# class definitions do not require separate header files or Interface Definition Language(IDL) files. Moreover, C# supports a new XML style of inline documentation that greatly simplifies the creation of online and print reference documentation for an application.

C# also supports interfaces, a means of making a contract with a class for services that the interface stipulates. In C#, a class can inherit from only a single parent, but a class can implement multiple interfaces. When it implements an interfaces, a C# class in effect promises to provide the functionality the interface specifies.

C# also provides support for structs, a concept whose meaning has changed significantly from  C++. In C#, a struct is restricted, lightweight type that, when instantiated , makes fewer demand on the operating system and on memory than a conventional class does. A struct can’t inherit from a class or be  inherited from, but a struct can implement an interfcace.

C# provides component-oriented features, such as a properties, events and declarative constructs. Component-oriented programming is supported by the CLR’s support for storing metadata with the code for the class. The metadata describes the class, including its methods and properties, as well as its security needs to carry out its functions. A  compiled class is thus a self-contained unit, therefore, a hosting environment that known how to read a class ‘metadata’ and code needs no other information to make use of it. Using C# and the CLR, it is possible to add custom metadata to a class by creating custom attributes.

Likewise, it is possible to read class metadata using CLR types that support reflection. An assembly is a collection of files hat appear to the programmer to be single dynamic link library or executable. In .NET, an assembly is the basic unit reuse, visioning, security and deployment. The CLR provides a  number of classes for manipulating assemblies.

 A final note about C# is that it also provides support for directly accessing memory using C++ style and keywords for bracketing such operations as unsafe and for warning the CLR garbage collector not to collect objects referenced by pointers until they are released.