4.1 Introduction to SQL Server
SQL Server is a data engine introduced by Microsoft. It provides an environment used to create and manage databases. It allows secure and efficient storage. It provides other components and services that support the business intelligence platform to generate reports and help analyze the data.
4.2 SQL Server components
SQL Server contains a number of components. Each component is provided with the specific services and support to the clients connected to the server.
The following diagram shows the components of the SQL Server.
The server contains the following components:
a) Database Engine
b) Integration Services
c) Analysis Services
d) Reporting Services
a) Database Engine: The component provides support to store query, process and secure data on the server. It allows user to create and manage database objects. The following background services are provided by the engine.
Service Broker: It provides support for asynchronous communication between clients and the database server. The client sends a request to the server and continues to work. The broker ensures that the request is processed whenever the server is available.
Replication: It allows the user to copy and distribute data and database objects from one server to another. The servers can be located at remote locations to provide fast access to users.
Full Text search: It allows the user to implement fast and intelligent search in large databases. It allows searching records containing words and phrases.
Notification services: It allows generating and sending notification messages to the user or administrators about the event.
b) Integration Services: The service allows gathering and integrating varied data in a consistent format in a common database. The database is known as data warehouse. The warehouse contains integrated databases, text files or flat files.
c) Analysis Services: The warehouses are designed to facilitate reporting and analysis. The applications are widely using this data store for analytical purpose. The applications used for this purpose are known as BI applications.
d) Reporting Services: They provide support to generate complete reports on data in the database engine in the data warehouse. These services provide a set of tools that help in creating and managing reports in different formats.
4.3 SQL Server integration with the .NET Framework
The SQL Server is integrated with the .NET framework as shown in the following figure.
The .NET framework is an environment used to build, deploy, and execute business applications. The server used various services provided by the framework. The component uses the framework services to generate and send notification messages.
The .NET framework consists of the following components:
a) Development tools and languages
b) Base Class Library
c) Common Language Runtime
a) Development tools and language: They are used to create interface for Windows forms, Web forms, and console applications. They include Visual Studio and Visual C# developer. The languages that can be used are VB.NET, C#, and F#.
b) Base Class Library: The framework consists of the class library that acts as a base class for any .NET language such as VB.NET or C#. The library is object oriented.
c) Common Language Runtime: It is the most important component in the framework. It provides the following features:
1) Automatic memory management: It is used for allocating and de-allocating the memory of an application.
2) Standard type system: It provides the user with some common data types known as Common Type System (CTS).
3) Language Interoperability: It helps the user to create applications that can be used with many programming languages.
4) Platform Independence: It allows the code execution from the platform that is supported by the CLR.
4.4 Features of SQL Server
The features provided by the SQL Server are as mentioned below:
1) Scalability: It allows distributing data in the large tables into different file groups. The server can access the file groups simultaneously.
2) CLR integration: It allows user to use the CLR features of the .NET Framework into the server database.
3) Service oriented architecture: It provides distributed, asynchronous application framework for large scale applications.
4) Web services support: It allows direct access to the data from web services by implementing the HTTP endpoints.
5) High security: It implements high security by adding policies for log on and passwords.
6) Support for data migration and analysis: It provides tools to migrate data from data sources to a common database.
7) Policy based management: It is used to define a set of policies for configuring and managing data.
8) Resource governor: It is used to manage the workload of the server by allocating and managing resources.
4.5 SQL statements
The SQL statements can be divided into following categories.
1) Data Definition Language (DDL): It is used to define database, data types and data structure and constraints. Such DDL statements are as follows:
a) CREATE: It is used to create a new database object.
b) ALTER: It is used to modify the database objects
c) DROP: It is used to delete the objects
2) Data Manipulation Language (DML): It is used to manipulate the data in the database objects. Some of the DML statements are as follows:
a) INSERT: It is used to insert the new data record into the table
b) UPDATE: It is used to modify the existing record in the table
c) DELETE: It is used to delete a record from a table
3) Data Control Language: It is used to control the data access in the database. Some of the DCL statements are as follows:
a) GRANT: It is used to assign the permissions to users to access the objects
b) REVOKE: It is used to deny the permissions to users to access the objects
4) Data Query Language: It is sued to query data from database objects. The SELECT statement is used to select the data from the database.