Microsoft SQL Server Part 1

Microsoft SQL Server Part 1

Microsoft SQL Server

SQL Server is a system of database management (DBMS) developed and marketed by Microsoft.

Originally co-developed by Sybase and Microsoft, Ashton-Tate has also been associated with the first version of the SQL Server that was released in 1989. This version was released on the Unix platform and OS / 2, Microsoft has worked on this system database on Windows since, and it is now only supported on the Microsoft’s systems.

In 1994, the partnership between the two companies broke down, and Microsoft released SQL Server’s version 6.0 and 6.5 only on Windows NT platform.

* Microsoft will continue to market the database engine as SQL Server

* While Sybase, to avoid confusion, has renamed Sybase SQL Server Sybase Adaptive Server Enterprise.

Microsoft SQL Server is now part of the technical strategy for Microsoft in terms of database. The Microsoft SQL Server Desktop Engine (MSDE), which is the foundation of SQL Server will eventually replace the Jet engine (which manages the foundation Access) in applications such as Exchange and Active Directory.

The version of SQL Server 2005 was released on 3 November 2005 at the same time as Visual Studio 2005. Support for Windows Vista and Windows Server 2008 was added until Service Pack 2 (SP2).

The 2008 version of SQL Server (code named Katmai) is available since August 2008. It is currently available as service pack 2. It is available in 9 languages.

For queries, SQL Server uses T-SQL (Transact-SQL), it is an implementation of SQL that supports stored procedures and triggers (trigger). The T-SQL is inconsistent with the PL / SQL Oracle.

For transfers of data, SQL Server uses the TDS (Tabular Data Stream), which was implemented in other databases (in particular in counterpart Sybase), whose specifications are public. An open source implementation of a TDS client is available and is the foundation of SQL Server client project Mono: FreeTDS.

Data Relations

SQL Server is a relational DBMS (RDBMS = Relational Data Base Management System). It is possible to define relationships between tables to ensure strong data integrity stored there. These relationships can be used to modify or delete records which are chain linked.


SQL Server is a transactional RDBMS. It can produce modifications on the data base and to validate or invalidate the data block. This ensures the integrity of information stored in the database.

During a transaction, data blocks containing rows of data modified by the transaction are locked. Other users, depending on the chosen isolation level, should not wait or the end of the transaction to change again.

The locks are made in lines, pages, extensions, tables or database. SQL Server does not lock the resources needed (default entries), and the needs can lock at a higher level (page or object level). This prevents users to wait until the end of a transaction to update data rows that have not been affected by the change and reduces the amount of resources consumed

Transactions are recorded in the log and transaction data modifications are included in the database at the control points (check points). It is possible to force a checkpoint through the reserved word, CHECKPOINT

The transaction log can be stored in three different ways:

* Simple: all changes are recorded in the log except for instructions on loading block (BCP, BULK INSERT, CREATE INDEX, rebuilding indexes and SELECT INTO) that are registered in more simple. The completed transactions are removed from the transaction log for each control point.

* Mode journaling block: minimally logs of the block trade. This mode is used temporarily for the time to make important mass operations.

* Complete Instructions: All changes are recorded in the log. Completed transactions, in which data is written on the disc, is removed from the transaction log for each backup of it.

In the last 2 modes, it is possible to save the database and restore it as it was at any point of time to the nearest second and close the transaction (but with limits on how journaling block). This mode also allows the backup of files or groups of files.

Continued : Microsoft SQL Server Part 2