Learn SQL Part -1

1.  What is DB
2.  What is DBMS?
3.  What is RDBMS?
4.  Difference between DBMS and RDBMS
5.  What is SQL Server?
6.  Role of SQL Server
7.  Different Types of SQL Servers
8.  Difference between SQL Server 2000 and SQL Server 2005
9.  Difference between SQL Server 2005 and SQL Server 2008
10.  SQL Server Data Types
11.  New Data Types of SQL Server 2008
12.  SQL Command Types

DB

A database is an organized collection of data. Databases are created to operate large quantities of information by inputting, storing, retrieving, and managing that information. Databases are set up, so that one set of software programs provides all users with access to all the data. Databases use a table format that is made up of rows and columns. Each piece of information is entered into a row, which then creates a record. Once the records are created in the database, they can be organized and operated in a variety of ways that are limited mainly by the software being used. Databases are somewhat similar to spreadsheets, but databases are more demanding than spreadsheets because of their ability to manipulate the data that is stored.

DBMS:

Database management systems (DBMSs) are specially designed applications that interact with the user, other applications, and the database itself to capture and analyze data. A general-purpose database management system (DBMS) is a software system designed to allow the definition, creation, querying, update, and administration of databases. Well-known DBMSs include MySQL, PostgreSQL, SQLite, Microsoft SQL Server, Microsoft Access, Oracle, SAP, dBase, FoxPro, IBM DB2 and FilemakerPro.  A DBMS is responsible for maintaining the integrity and security of stored data, and for recovering information if the system fails.

RDBMS

• RDBMS stands for Relational Database Management System.
• RDBMS is the basis for SQL, and for all modern database systems such as MS SQL Server, IBM DB2, Oracle, MySQL, and Microsoft Access.
• The data in RDBMS is stored in database objects called tables.
• A table is a collection of related data entries and it consists of columns and rows.
• An RDBMS database program (i.e. MS Access, SQL Server, MySQL)
• To use a server-side scripting language, like PHP or ASP
• To use SQL to get the data you want
• To use HTML / CSS

Difference between DBMS and RDBMS

S.No	DBMS	RDBMS
1	Stands for DataBase Management System	Stands for Relational DataBase Management System
2	In dbms no relationship concept	It is used to establish the relationship concept between two database objects, i.e, tables
3	It supports Single User only	It supports multiple users
4	It treats Data as Files internally	It treats data as Tables internally
5	It supports 3 rules of E.F.CODD out off 12 rules	It supports minimum 6 rules of E.F.CODD
6	It requires low Software and Hardware Requirements.	It requires High software and hardware requirements.
7	DBMS is used for simpler business applications	RDBMS is used for more complex applications.
8	DBMS does not impose any constraints or security with regard to data manipulation	RDBMS defines the integrity constraint for the purpose of holding ACID PROPERTY
9	In DBMS Normalization process will not be present	In RDBMS, normalization process will be present to check the database table consistency
10	There is no enforcement to use foreign key concept compulsorily in DBMS	Although the foreign key concept is supported by both DBMS and RDBMS but its only RDBMS that enforces the rules
11	FoxPro, IMS are Examples	SQL Server, Oracle are examples

About SQL Server:

Microsoft SQL Server is a relational database management system developed by Microsoft. As a database, it is a software product whose primary function is to store and retrieve data as requested by other software applications, be it those on the same computer or those running on another computer across a network (including the Internet). 

Different types of SQL Server Versions:

• SQL Server 2000
• SQL Server 2005
• SQL Server 2008 R2
• SQL Server 2012

T-SQL:

T-SQL (Transact-SQL) is the Secondary means of programming and managing SQL Server. It exposes keywords for the operations that can be performed on SQL Server, including creating and altering database schemas, entering and editing data in the database as well as monitoring and managing the server itself. 

What is SQL?

• SQL stands for Structured Query Language
• SQL lets you access and manipulate databases
• SQL is an ANSI (American National Standards Institute) standard

Role of SQL Server

1. SQL can execute queries against a database
2. SQL can retrieve data from a database
3. SQL can insert records in a database
4. SQL can update records in a database
5. SQL can delete records from a database
6. SQL can create new databases
7. SQL can create new tables in a database
8. SQL can create stored procedures in a database
9. SQL can create views in a database
10. SQL can set permissions on tables, procedures, and views

 

Object Explorer

SQL Server Management Studio provides features for managing objects in instances of the Database Engine, Analysis Services, Integration Services, and Reporting Services.

Benefits of Object Explorer:

ü   
     Object Explorer provides a hierarchical user interface to view and manage the objects in each instance of SQL Server.

ü 

Query Editor Window:

Use the Database Engine Query Editor to create and run Transact-SQL.

Difference between SQL Server 2000 and SQL Server 2005

S.No	SQL Server 2000	SQL Server 2005
1	Query Analyser and Enterprise manager are separate.	Both are combined as SSMS(Sql Server management Studio).
2	No XML datatype is used.	.XML datatype is introduced.
3	We can create maximum of 65,535 databases.	We can create 2(pow(20))-1 databases.
4	Exception Handling mechanism is not available	Exception Handling mechanism is available
5	There is no Varchar(Max) data type is not available	Varchar(Max) data type is introduced.
6	DDL Triggers is not available	DDL Triggers is introduced
7	DataBase Mirroring facility is not available	DataBase Mirroring facility is introduced
8	RowNumber function for paging is not available	RowNumber function for paging is introduced
9	Table fragmentation facility is not available	Table fragmentation facility is introduced
10	Full Text Search facility is not available	Full Text Search facility is introduced
11	Bulk Copy Update facility is not available	Bulk Copy Update facility is introduced
12	Data Encryption concept is not introduced	.Cannot encrypt the entire database
13	Cannot compress the tables and indexes.	Can Compress tables and indexes.(Introduced in 2005 SP2)
14	No varchar(max) or varbinary(max) is available.	Varchar(max) and varbinary(max) is used.
15	Data Transformation Services(DTS) is used as ETL tool	SQL Server Integration Services(SSIS) is started using from this SQL Server version and which is used as ETL tool

Difference between Sql Server 2005 and Sql Server 2008

S.No	Sql Server 2005	Sql Server 2008
1	XML datatype is introduced.	XML datatype is used.
2	Cannot encrypt the entire database.	Can encrypt the entire database introduced in 2008.
3	Datetime is used for both date and time.	Date and time are seperately used for date and time
4	No table datatype is included.	Table datatype introduced.
5	SSIS is started using.	SSIS avails in this version.
6	CMS is not available.	Central Management Server(CMS) is Introduced.
7	PBM is not available	Policy based management(PBM) server is Introduced.

T-SQL:

T-SQL (Transact-SQL) is the Secondary means of programming and managing SQL Server. It exposes keywords for the operations that can be performed on SQL Server, including creating and altering database schemas, entering and editing data in the database as well as monitoring and managing the server itself. 

SQL Server Data Types

 Exact Numerics

1. bigint
2. numeric
3. bit
4. smallint
5. decimal
6. smallmoney
7. int
8. tinyint
9. Money

Approximate Numerics

1. float
2. real
3. Date and Time
4. date

datetimeoffset

1. datetime2
2. smalldatetime
3. datetime
4.  time

Character Strings

1. char
2. varchar
3. text

Unicode Character Strings

1. nchar
2. nvarchar
3. ntext

Binary Strings

1. binary
2. varbinary
3. image

Other Data Types

1. cursor
2. timestamp
3. hierarchyid

uniqueidentifier

1. sql_variant
2. xml
3. tabl

SQL Server 2008 New Data Types:

Date and Time: 

     Four new date and time data types have been added, making working with time much easier than it ever has in the past. They include: DATE, TIME, DATETIME2, and DATETIMEOFFSET.

Spatial: 
    Two new spatial data types have been added--GEOMETRY and GEOGRAPHY--which you can use to natively store and manipulate location-based information, such as Global Positioning System (GPS) data.

HIERARCHYID: 
   The HIERARCHYID data type is used to enable database applications to model hierarchical tree structures, such as the organization chart of a business.

FILESTREAM: 
     FILESTREAM is not a data type as such, but is a variation of the VARBINARY(MAX) data type that allows unstructured data to be stored in the file system instead of inside the SQL Server database. Because this option requires a lot of involvement from both the DBA administration and development side, I will spend more time on this topic than the rest.

Date and Time

In SQL Server 2005 and earlier, SQL Server only offered two date and time data types: DATETIME and SMALLDATETIME. While they were useful in many cases, they had a lot of limitations, including:

     Both the date value and the time value are part of both of these data types, and you can’t choose to store one or the other. This often causes a lot of wasted storage (because you store data you don’t need or want); adds unwanted complexity to many queries because the data types often had to be converted to a different form to be useful; and often reduces performance because WHERE clauses with these data and time data types often had to include functions to convert them to a more useful form, preventing these queries from using indexes.

   They are not time-zone aware, which often requires extra coding for time-aware applications.

     Precision is only .333 seconds, which is often not granular enough for some applications.

     The range of supported dates is not adequate for some applications, and the range does not match the range of .NET CLR DATETIME data type, which requires additional conversion code.

To overcome these problems, SQL Server 2008 introduces four new date and time data types, which include:

DATE: 
   As you can imagine, the DATE data type only stores a date in the format of YYYY-MM-DD. It has a range of 0001-01-01 through 9999-12-32, which should be adequate for most business and scientific applications. The accuracy is 1 day, and it only takes 3 bytes to store the date.

TIME: 
   TIME is stored in the format: hh:mm:ss.nnnnnnn, with a range of 00:00:00.0000000 through 23:59:59:9999999 and is accurate to 100 nanoseconds. Storage depends on the precision and scale selected, and runs from 3 to 5 bytes.

DATETIME2:  
    DATETIME2 is very similar to the older DATETIME data type, but has a greater range and precision. The format is YYYY-MM-DD hh:mm:ss:nnnnnnnm with a range of 0001-01-01 00:00:00.0000000 through 9999-12-31 23:59:59.9999999, and an accuracy of 100 nanoseconds. Storage depends on the precision and scale selected, and runs from 6 to 8 bytes. 

DATETIMEOFFSET: 

    DATETIMEOFFSET is similar to DATETIME2, but includes additional information to track the time zone. The format is YYYY-MM-DD hh:mm:ss[.nnnnnnn] [+|-]hh:mm with a range of 0001-01-01 00:00:00.0000000 through 0001-01-01 00:00:00.0000000 through 9999-12-31 23:59:59.9999999 (in UTC), and an accuracy of 100 nanoseconds. Storage depends on the precision and scale selected, and runs from 8 to 10 bytes.

Spatial

While spatial data has been stored in many SQL Server databases for many years (using conventional data types) SQL Server 2008 includes the introduction of two specific spatial data types which can make it easier for developers to integrate spatial data in their SQL Server-based applications. In addition, by storing spatial data in relational tables, it becomes much easier to combine spatial data with other kinds of business data. For example, by combining spatial data (such as longitude and latitude) with the physical address of a business, applications can be created to map business locations on a map.

They include:

GEOMETRY: 
   The GEOMETRY data type is used to store planar (flat-earth) data. It is generally used to store XY coordinates that represent points, lines, and polygons in a two-dimensional space. For example storing XY coordinates in the GEOMETRY data type can be used to map the exterior of a building.

 GEOGRAPHY: 
    The GEOGRAPHY data type is used to store ellipsoidal (round-earth) data. It is used to store latitude and longitude coordinates that represent points, lines, and polygons on the earth’s surface. For example, GPS data that represents the lay of the land is one example of data that can be stored in the GEOGRAPHY data type.

   
   Another feature of spatial data types is that they support special spatial indexes. Unlike conventional indexes, spatial indexes consist of a grid-based hierarchy in which each level of the index subdivides the grid sector that is defined in the level above. But like conventional indexes, the SQL Server query optimizer can use spatial indexes to speed up the performance of queries that return spatial data.

HIERARCHYID

While hierarchical tree structures are commonly used in many applications, SQL Server has not made it easy to represent and store them in relational tables. In SQL Server 2008, the HIERARCHYID data type has been added to help resolve this problem. It is designed to store values that represent the position of nodes of a hierarchal tree structure.

For example, the HIERARCHYID data type makes it easier to express these types of relationships without requiring multiple parent/child tables and complex joins.

Organizational structures:-

• A set of tasks that make up a larger projects (like a GANTT chart)
• File systems (folders and their sub-folders)
• A classification of language terms 
• A bill of materials to assemble or build a product·   
• A graphical representation of links between web pages

Unlike standard data types, the HIERARCHYID data type is a CLR user-defined type, and it exposes many methods that allow you to manipulate the date stored within it. 

For example, there are methods to get the current hierarchy level, get the previous level, get the next level, and many more. In fact, the HIERARCHYID data type is only used to store hierarchical data; it does not automatically represent a hierarchical structure. It is the responsibility of the application to create and assign HIERARCHYID values in a way that represents the desired relationship

.

FILESTREAM

SQL Server is great for storing relational data in a highly structured format, but it has never been particularly good at storing unstructured data, such as videos, graphic files, Word documents, Excel spreadsheets, and so on. In the past, when developers wanted to use SQL Server to manage such unstructured data, developers essentially had two choices. They could store unstructured data in VARBINARY(MAX) columns inside the database; or they could store the data outside of the database as part of the file system, and include pointers inside a column that pointed to the file’s location. This allowed an application that needed access to the file to find it by looking up the file’s location from inside a SQL Server table.

FILESTREAM storage offers these additional benefits:

As you might expect, using FILESTREAM storage is not right for every situation, for example, it is best used under the following conditions:

1.  When the BLOB file sizes average 1MB or higher.
2.  When fast read access is important to your application.
3.  When applications are being built that use a middle layer for application logic.
4.  When encryption is not required, as it is not supported for FILESTREAM data.

SQL Command Types:

 DML

DML is abbreviation of Data Manipulation Language. It is used to retrieve, store, modify, delete, insert and update data in database.

 Merge :  Can perform insert, update, or delete operations in a single statement using the MERGE statement 

 INSERT :   Inserts data into a table 

 UPDATE: Updates existing data into a table

DELETE : Deletes all records from a table

DDL

DDL is abbreviation of Data Definition Language. It is used to create and modify the structure of database objects in database.

CREATE – Creates objects in the database 

 ALTER – Alters objects of the database

DROP – Deletes objects of the database 

TRUNCATE – Deletes all records from a table and resets table identity to initial value.

DCL

DCL is abbreviation of Data Control Language. It is used to create roles, permissions, and referential integrity as well it is used to control access to database by securing it.

GRANT – Gives user’s access privileges to database

REVOKE – Withdraws user’s access privileges to database given with the GRANT command

TCL
   TCL is abbreviation of Transactional Control Language. It is used to manage different transactions occurring within a database.

COMMIT – Saves work done in transactions 

 ROLLBACK – Restores database to original state since the last COMMIT command in transactions

SAVE TRANSACTION – Sets a savepoint within a transaction

DQL:

DQL is abbreviation of Data Query Language. It is used to Query a data from table.

Ø  SELECT: Retrieves data from a table

Example of Merge

USE tempdb;

GO

CREATE TABLE dbo.Target(EmployeeID int, EmployeeName varchar(10),

     CONSTRAINT Target_PK PRIMARY KEY(EmployeeID));

CREATE TABLE dbo.Source(EmployeeID int, EmployeeName varchar(10),

     CONSTRAINT Source_PK PRIMARY KEY(EmployeeID));

GO

INSERT dbo.Target(EmployeeID, EmployeeName) VALUES(100, 'Mary');

INSERT dbo.Target(EmployeeID, EmployeeName) VALUES(101, 'Sara');

INSERT dbo.Target(EmployeeID, EmployeeName) VALUES(102, 'Stefano');

GO

INSERT dbo.Source(EmployeeID, EmployeeName) Values(103, 'Bob');

INSERT dbo.Source(EmployeeID, EmployeeName) Values(104, 'Steve');

GO

 MERGE statement with the join conditions specified correctly.

USE tempdb;

GO

BEGIN TRAN;

    MERGE Target AS T

    USING Source AS S

    ON (T.EmployeeID = S.EmployeeID) 

    WHEN NOT MATCHED BY TARGET AND S.EmployeeName LIKE 'S%' 

                         THEN INSERT(EmployeeID, EmployeeName) VALUES(S.EmployeeID, S.EmployeeName)

    WHEN MATCHED 

    THEN UPDATE SET T.EmployeeName = S.EmployeeName

    WHEN NOT MATCHED BY SOURCE AND T.EmployeeName LIKE 'S%'

     THEN DELETE 

    OUTPUT $action, inserted.*, deleted.*;

ROLLBACK TRAN;

GO 

 

Using Binary Data

   The binary and varbinary data types store strings of bits. Although character data is interpreted based on the SQL Server code page, binary and varbinary data is simply a stream of bits.

     binary data can store a maximum of 8,000 bytes. Varbinary, using the max specified, can store a maximum of 2^31 bytes. 

Using char and varchar Data

ü  The char or varchar data can be a single character, or a string with a maximum of 8,000 characters for char data, and up to 2^31 characters for varchar data.

ü  The varchar data types can take two forms. varchar data can be of a specified maximum length of characters

Using hierarchyid Data Types (Database Engine)

The hierarchyid data type is system-provided. Use hierarchyid as a data type to create tables with a hierarchical structure, or to reference the hierarchical structure of data in another location.

Hierarchical data is defined as a set of data items that are related to each other by hierarchical relationships. Hierarchical relationships are where one item of data is the parent of another item. Hierarchical data is common in databases. Examples include the following:

·         An organizational structure

·         A file system

·         A set of tasks in a project

·         A taxonomy of language terms

·         A graph of links between Web pages

 

Using Integer Data

Integers are whole numbers and do not contain decimals or fractions.

   bigint

Has a length of 8 bytes and stores numbers from –2^63 (-9,223,372,036,854,775,808) through 2^63-1 (9,223,372,036,854,775,807).

    integer or int

Has a length of 4 bytes, and stores numbers from -2,147,483,648 through 2,147,483,647. 

smallint 
 as a length of 2 bytes, and stores numbers from -32,768 through 32,767.

    
     tinyint

Has a length of 1 byte, and stores numbers from 0 through 255.

Using decimal, float, and real Data

The decimal data type can store a maximum of 38 digits, all of which can be to the right of the decimal point. The decimal data type stores an exact representation of the number; there is no approximation of the stored value.

The two attributes that define decimal columns, variables, and parameters are:

·         

Specifies the precision, or the number of digits the object can hold.

·         

Specifies the scale or number of digits that can be placed to the right of the decimal point.

p and s must observe the rule: 0 <= s <= p <= 38.

The default maximum precision of numeric and decimal data types is 38. In Transact-SQL, numeric is functionally equivalent to the decimal data type.

Use the decimal data type to store numbers with decimals when the data values must be stored exactly as specified.

Using float and real Data

ü  The float and real data types are known as approximate data types. The behaviour of float and real follows the IEEE 754 specification on approximate numeric data types.

ü    Approximate numeric data types do not store the exact values specified for many numbers; they store an extremely close approximation of the value. For many applications, the tiny difference between the specified value and the stored approximation is not noticeable. At times, though, the difference becomes noticeable.

ü  Because of the approximate nature of the float and real data types, do not use these data types when exact numeric behaviour is required, such as in financial applications, in operations involving rounding, or in equality checks. Instead, use the integer, decimal, money, or smallmoney data types.

ü  Avoid using float or real columns in WHERE clause search conditions, especially the = and <> operators. It is best to limit float and real columns to > or < comparisons.

Using Monetary Data

ü  Microsoft SQL Server stores monetary data, or currency values, using two data types: money and smallmoney. These data types can use any one of the following currency symbols.

Currency or monetary data does not need to be enclosed in single quotation marks ( ' ). It is important to remember that while you can specify monetary values preceded by a currency symbol, SQL Server does not store any currency information associated with the symbol, it only stores the numeric value. 

SQL Server does not store any currency information associated with the symbol, it only stores the numeric value. For example, to assign a value of 100 Dollars to a variable, you can do the following:

DECLARE @dollars AS money

SET @dollars = $100

SELECT @dollars

The value returned is 100.0000, without a currency symbol.

If an object is defined as money, it can contain a maximum of 19 digits, 4 of which can be to the right of the decimal. The object uses 8 bytes to store the data. The money data type therefore has a precision of 19, a scale of 4, and a length of 8.

money and smallmoney are limited to four decimal points. Use the decimal data type if more decimal points are required.

Using Large-Value Data Types

ü  The max specifier expands the storage capabilities of the varchar, nvarchar, and varbinary data types. varchar(max), nvarchar(max), and varbinary(max) are collectively called large-value data types. You can use the large-value data types to store up to 2^31-1 bytes of data.

ü The large-value data types are similar in behaviour to their smaller counterparts, varchar, nvarchar and varbinary. This similarity enables SQL Server to store and retrieve large character, Unicode, and binary data more efficiently.

Using text and image Data

i  Microsoft SQL Server stores character strings longer than 8,000 characters and binary data longer than 8,000 bytes in special data types named text and image. Unicode strings longer than 4,000 characters are stored in the ntext data type.

ii  For example, a large text file (.txt) of customer information must be imported into your SQL Server database. This data should be stored as one piece of data rather than integrated into the multiple columns of your data tables. You can create a column with the text data type for this purpose. However, if you must store company logos currently stored as Tagged Image File Format (TIFF) images (.tif) that are 10 KB each, create a column with the image data type.

iii  If the textual data to be stored is in Unicode format, use the ntext data type. For example, a form letter created for international customers is likely to contain international spellings and characters used in various different languages. Store this data in an ntext column.

iv  Usually, text, ntext, or image strings are large, a maximum of 2GB, character or binary strings stored outside a data row. The data row contains only a 16-byte text pointer that points to the root node of a tree built of internal pointers that map the pages in which the string fragments are stored.

v   With SQL Server, you can store small to medium text, ntext, and image values in a data row, thereby increasing the speed of queries accessing these values.

vi  When the text, ntext, or image string is stored in the data row, SQL Server does not have to access a separate page or set of pages to read or write the string. This makes reading and writing the text, ntext, or image in-row strings about as fast as reading or writing varchar, nvarchar, or varbinary strings.

vii  To store text, ntext, or image strings in the data row, enable the text in row option using the sp_tableoption stored procedure.

Using Special Data Types

This topic describes the special data types that are available in SQL Server. Special data types are those that do not fit into any of the other data type categories. In SQL Server, the special data types include the bit, hierarchyid, sql_variant, sysname, table, timestamp and alias data types.

Bit

The bitdata type is a numeric data type that stores either 0 or 1. The string values true and false can be converted to bit values, as shown in the following example:

SELECT CONVERT (bit, 'true')

SELECT CONVERT(bit, 'false')

sql_variant

The sql_variant data type enables a single column, parameter, or variable to store data values of different data types. Each instance of asql_variant column records the value and the metadata that describes the value. The following metadata is available:

•          Base data type
•          Maximum size
•          Scale
•          Precision
•          Collation

table

The table data type functions like a temporary table. It is used to store a result set for later processing. This data type can only be used to define local variables of type table and the return value of a user-defined function.

The definition of a table variable or return value includes definitions of the columns, the data type, precision, and scale of each column, and optional PRIMARY KEY, UNIQUE, NULL, and CHECK constraints. A user-defined table cannot be used as a data type.

The format of the rows that are stored in a table variable or that are returned by a user-defined function must be defined when the variable is declared or the function is created. The syntax is based on the CREATE TABLE syntax, for example:

DECLARE @TableVar TABLE (Cola int PRIMARY KEY, Colb char(3))

INSERT INTO @TableVar VALUES (1, 'abc')

INSERT INTO @TableVar VALUES (2, 'def')

SELECT * FROM @TableVar

GO

table 
variables and user-defined functions that return a table can be used only in certain SELECT and INSERT statements, and where tables are supported in the UPDATE, DELETE, and DECLARE CURSOR statements. table variables and user-defined functions that return a table cannot be used in any other Transact-SQL statements.

Indexes or other constraints that are applied to the table must be defined as part of the DECLARE variable or CREATE FUNCTION statement. They cannot be applied later because the CREATE INDEX or ALTER TABLE statements cannot reference table variables and user-defined functions.

Using Unicode Data

In SQL Server, these data types support Unicode data:

•       nchar
•       nvarchar
•       ntext

Use of nchar, nvarchar, and ntext is the same as char, varchar, and text, respectively, except that:

• Unicode supports a wider range of characters.
• More space is needed to store Unicode characters.
• The maximum size of nchar columns is 4,000 characters, not 8,000 characters like char and varchar.
• The maximum size for nvarchar columns, using the max specifier, is 2^31-1 bytes. For more information about nvarchar(max), see Using Large-Value Data Types.
• Unicode constants are specified with a leading N: N'A Unicode string'.
• All Unicode data uses the character set defined by the Unicode standard. Unicode collations used for Unicode columns are based on attributes such as case sensitivity, accent sensitivity, Kana sensitivity, width sensitivity and binary.

Differences between BINARY AND VARBINARY

BINARY1

• Fixed-length binary data with a length of n bytes, where n is a value from 1 through 8,000. The storage size is n bytes.

 
VARBINARY

• Variable-length binary data. n can be a value from 1 through 8,000.
• Max indicates that the maximum storage size is 2^31-1 bytes.
• The storage size is the actual length of the data entered + 2 bytes.
• The data that is entered can be 0 bytes in length.
• The ANSI SQL synonym for varbinary is binary varying.

Conclusion:

• Use binary when the sizes of the column data entries are consistent.
• Use varbinary when the sizes of the column data entries vary considerably.
• Use varbinary(max) when the column data entries exceed 8,000 bytes.

Differences between CHAR AND VARCHAR

CHAR

• Fixed-length. so any remaining space in the field is padded with blanks.
• non-Unicode character data with a length of n bytes, n must be a value from 1 through 8,000. The storage size is n bytes
• The SQL-2003 synonym for char is character.

VARCHAR

• Variable-length, non-Unicode character data. n can be a value from 1 through 8,000. max indicates that the maximum storage size is 2^31-1 bytes. 
• The storage size is the actual length of data entered + 2 bytes. 
• The data entered can be 0 characters in length. 
• The SQL-2003 synonyms for varchar are char varying or character varying.
• For example, if you set a VARCHAR(100) datatype = 'Jen', then it would take up 3 bytes (for J, E, and N) plus 2 bytes, or 5 bytes in all.

 
Conclusion:

• Use Char, When Using the fixed length data in column & data entries are consistent, like phone number
• Use Varchar When using the variable length data in column & data entries vary considerably, like address

 

Differences between VARCHAR/VARCHAR(MAX) and NVARCHAR/NVARCHAR(MAX)

Varchar/Varchar(MAX)

• Varchar(8000) stores a maximum of 8000 characters. Varchar(max) stores a maximum of 2 147 483 647 characters or 2GB. 
• Variable length Non-Unicode Characters data.
• The storage size is the actual length of data entered + 2 bytes
• The SQL-2003 synonyms for varchar are char varying or character varying.

 
NVarchar/NVarchar(MAX)

• NVarchar(4000) stores a maximum of 4000 characters. Varchar(max) stores a maximum of 1 073 741 823 characters or 1GB. 
• Variable length Unicode Characters data.
• The storage size, in bytes, is two times the number of characters entered + 2 bytes
• The SQL-2003 synonyms for nvarchar are national char varying and national character varying.

 

Differences between NCHAR AND NVARCHAR
NCHAR

• Fixed-length. so any remaining space in the field is padded with blanks.
• Unicode character data with a length of n bytes, n must be a value from 1 through 4,000. The storage size is n bytes
• The ISO synonyms for nchar are national char and national character.

 
NVARCHAR

• Variable-length, non-Unicode character data. n can be a value from 1 through 4,000. max indicates that the maximum storage size is 2^31-1 bytes (2GB). 
• The storage size is in bytes, is two times the actual length of data entered+ 2 bytes
• The ISO synonyms for nvarchar are national char varying and national character varying.

 
Conclusion:

• Use nchar when the sizes of the column data entries are probably going to be similar.
• Use nvarchar when the sizes of the column data entries are probably going to vary considerably.

Differences between NTEXT, TEXT AND IMAGE
NTEXT

• Variable-length Unicode data with a maximum string length of 2^30 - 1 (1,073,741,823).
• Storage size, in bytes, is two times the string length entered.
• The ISO synonym for ntext is national text.

 
TEXT

• Variable-length non-Unicode data in the code page of the server and with a maximum string length of 2^31-1 (2,147,483,647).
• When the server code page uses double-byte characters, the storage is still 2,147,483,647 bytes.
• Depending on the character string, the storage size may be less than 2,147,483,647 bytes.

IMAGE

• Variable-length binary data from 0 through 2^31-1 (2,147,483,647) bytes.

 

NUMERIC DATATYPES
 
Difference between Decimal[ (p[ ,s] )] and Numeric[(p[ ,s] )]
 
Fixed precision and scale numbers.

• When maximum precision is used, valid values are from - 10^38 +1 through 10^38 - 1.
• The ISO synonyms for decimal are dec and dec(p, s). 
• Numeric is functionally equivalent to decimal.

p (precision)

• The maximum total number of decimal digits that can be stored, both to the left and to the right of the decimal point. 
• The precision must be a value from 1 through the maximum precision of 38.
• The default precision is 18.
•  

s (scale)

• The maximum number of decimal digits that can be stored to the right of the decimal point.
• Scale must be a value from 0 through p.
• Scale can be specified only if precision is specified.
• The default scale is 0; therefore, 0 <= s <= p.
• Maximum storage sizes vary, based on the precision.

 
Precision Storage bytes
 

1 - 9 5
10-19 9
20-28 13
29-38 17