SQL Server

SQL SERVER

       A database management, or DBMS, gives the user access to their data and helps them transform the data into information. Such database management systems include dBase, paradox, IMS, SQL Server and SQL Server.  These systems allow users to create, update and extract information from their database.

          A database is a structured collection of data.  Data refers to the characteristics of people, things and events.  SQL Server stores each data item in its own fields.  In SQL Server, the fields relating to a particular person, thing or event are bundled together to form a single complete unit of data, called a record (it can also be referred to as raw or an occurrence).  Each record is made up of a number of fields.  No two fields in a record can have the same field name.

          During an SQL Server Database design project, the analysis of your business needs identifies all the fields or attributes of interest.  If your business needs change over time, you define any additional fields or change the definition of existing fields.

SQL SERVER TABLES

          SQL Server stores records relating to each other in a table.  Different tables are created for the various groups of information. Related tables are grouped together to form a database.

PRIMARY KEY

          Every table in SQL Server has a field or a combination of fields that uniquely identifies each record in the table.  The Unique identifier is called the Primary Key, or simply the Key.  The primary key provides the means to distinguish one record from all other in a table.  It allows the user and the database system to identify, locate and refer to one particular record in the database.

RELATIONAL DATABASE

          Sometimes all the information of interest to a business operation can be stored in one table.  SQL Server makes it very easy to link the data in multiple tables. Matching an employee to the department in which they work is one example.  This is what makes SQL Server a relational database management system, or RDBMS.  It stores data in two or more tables and enables you to define relationships between the table and enables you to define relationships between the tables.

FOREIGN KEY

          When a field is one table matches the primary key of another field is referred to as a foreign key.  A foreign key is a field or a group of fields in one table whose values match those of the primary key of another table.

REFERENTIAL INTEGRITY

          Not only does SQL Server allow you to link multiple tables, it also maintains consistency between them.  Ensuring that the data among related tables is correctly matched is referred to as maintaining referential integrity.

DATA ABSTRACTION

          A major purpose of a database system is to provide users with an abstract view of the data.  This system hides certain details of how the data is stored and maintained. Data abstraction is divided into three levels.

Physical level:  This is the lowest level of abstraction at which one describes how the data are actually stored.

Conceptual Level:  At this level of database abstraction all the attributed and what data are actually stored is described and entries and relationship among them.

View level:  This is the highest level of abstraction at which one describes only part of the database.

ADVANTAGES OF RDBMS

·         Redundancy can be avoided

·         Inconsistency can be eliminated

·         Data can be Shared

·         Standards can be enforced

·         Security restrictions can be applied

·         Integrity can be maintained

·         Conflicting requirements can be balanced

·         Data independence can be achieved.

DISADVANTAGES OF DBMS

          A significant disadvantage of the DBMS system is cost.  In addition to the cost of purchasing of developing the software, the hardware has to be upgraded to allow for the extensive programs and the workspace required for their execution and storage.  While centralization reduces duplication, the lack of duplication requires that the database be adequately backed up so that in case of failure the data can be recovered.

FEATURES OF SQL SERVER (RDBMS)

          SQL SERVER is one of the leading database management systems (DBMS) because it is the only Database that meets the uncompromising requirements of today’s most demanding information systems.  From complex decision support systems (DSS) to the most rigorous online transaction processing (OLTP) application, even application that require simultaneous DSS and OLTP access to the same critical data, SQL Server leads the industry in both performance and capability.

SQL SERVER is a truly portable, distributed, and open DBMS that delivers unmatched performance, continuous operation and support for every database.

SQL SERVER RDBMS is high performance fault tolerant DBMS which is specially designed for online transactions processing and for handling large database application.

SQL SERVER with transactions processing option offers two features which contribute to very high level of transaction processing throughput, which are

the row level lock manager

ENTERPRISE WIDE DATA SHARING

          The unrivaled portability and connectivity of the SQL SERVER DBMS enables all the systems in the organization to be linked into a singular, integrated computing resource.

PORTABILITY

          SQL SERVER is fully portable to more than 80 distinct hardware and operating systems platforms, including UNIX, MSDOS, OS/2, Macintosh and dozens of proprietary platforms.  This portability gives complete freedom to choose the database server platform that meets the system requirements.

OPEN SYSTEMS

          SQL SERVER offers a leading implementation of industry –standard SQL.  SQL Server’s open architecture integrates SQL SERVER and non –SQL SERVER DBMS with industry’s most comprehensive collection of tools, application, and third party software products SQL Server’s Open architecture provides transparent access to data from other relational database and even non-relational database.

DISTRIBUTED DATA SHARING

          SQL Server’s networking and distributed database capabilities to access data stored on remote server with the same ease as if the information was stored on a single local computer.  A single SQL statement can access data at multiple sites. You can store data where system requirements such as performance, security or availability dictate.

UNMATCHED PERFORMANCE

          The most advanced architecture in the industry allows the SQL SERVER DBMS to deliver unmatched performance.

SOPHISTICATED CONCURRENCY CONTROL

          Real World applications demand access to critical data.  With most database Systems application becomes “contention bound” – which performance is limited not by the CPU power or by disk I/O, but user waiting on one another for data access . SQL Server employs full, unrestricted row-level locking and contention free queries to minimize and in many cases entirely eliminates contention wait times.

NO I/O BOTTLENECKS

          SQL Server’s fast commit groups commit and deferred write technologies dramatically reduce disk I/O bottlenecks. While some database write whole data block to disk at commit time, SQL Server commits transactions with at most sequential log file on disk at commit time, On high throughput systems, one sequential writes typically group commit multiple transactions.  Data read by the transaction remains as shared memory so that other transactions may access that data without reading it again from disk.  Since fast commits write all data necessary to the recovery to the log file, modified blocks are written back to the database independently of the transaction commit, when written from memory to disk