The SQL Server data engine which is introduced by Microsoft. Using SQL, user can create the database and manage them. User can easily execute queries, add records to the database, update and delete data. New databases and tables are created. Procedures and views are added on different tables. Features of SQL Server The different features provided by SQL Server are as listed below: The data present in large tables is distributed into various filegroups. The server can easily access the filegroups and access the data effectively. Data can be stored in XML format. The data in the columns and variables of XML data type are processed An asynchronous, distributed framework is provided for various applications. User can easily send the requests to the web server It provides intellisense support to the user for coding statements An integrated environment for the SQL Server Database Engine, Reporting and analysis services which helps to run the instructions immediately It is easier for user to audit logons, password modifications, accessing data, results of audit are saved in files The resources management is used for maintaining the SQL server workload. The number of resources are available in the resource pool. User can set the value for the CPU and memory utilization from the pool The downtime of the server is reduced as resources of database server are available to the users at all times The HTTP endpoints allows user to access the data from web services The data can be easily migrated from various data sources to a database which is common for all users The CLR features of the framework can be used in the Server database The policies used for configuring and managing the server are defined. A group of policies for naming the tables and procedures is defined SQL Statements Various operations are performed using SQL language. It is used for managing the database. The T – SQL language is used in the SQL server for programming the data. The different SQL statements used for programming are divided into various categories: 1. Data Definition Language ( DDL ) The language is used for defining the database, structures, types, and data constraints on data. CREATE: A new database object is created in SQL DROP: Used for deleting the objects ALTER: The database objects can be changed using the statement 2. Data Control Language ( DCL ) Used for controlling the data access in the database GRANT: The permissions are assigned to the users for accessing the objects of the database REVOKE: The permissions to the user are denied for accessing the objects of the database 3. Data Manipulation Language ( DML ) The data present in the database is easily manipulated using DML statements. INSERT: Useful for adding a new data to the table DELETE: The record is deleted from the table UPDATE: Useful for updating the record in the table 4. Data Query Language ( DQL) The data is queried from the database objects. The SELECT statement is used for selecting the data. Components of SQL Server Each component of SQL Server defines a specific service which is useful for connection between the client and the server. The components of SQL Server are as listed below: Database Engine Integration Services Reporting Services Analysis Services 1. Database Engine Using the database engine, user can store and execute the data on the server. User can easily manage database objects like triggers, views, stored procedures, and tables. There are several background services which are provided by the database engine. Some of them are as listed below: Replication User can easily transfer data from one server to another. The server are present at remote locations which provide fast access to the users available at different places. Later, the server synchronizes the data for ensuring the data consistency. Service Broker The asynchronous communication between the client and the server is supported by service broker. All the request sent by the client are queued at the server end for the processing. Once the server is available, the service broker checks that the request is processed. Notification services The notification messages are sent to the admins and users through the notification services. The messages can be sent on mobiles and computers. Notification applications can be created for sending messages at regular time intervals. Full text search Intelligent search in large databases is possible due to full text search. User can search data which contains specific words or phrases. 2. Integration services Using integration services, data from various sources in different formats is integrated into a consistent format. The data warehouse acts as a common database for the data. The text or flat files are added inside the database. Unnecessary data is not saved inside the data warehouse. It is very useful for the developers to make decisions using the data stored in the warehouse. The SQL Server Integration Services ( SSIS ) Import and Export wizard contains dialogs which are useful for adding data source, destination location, objects used for creating data warehouse. 3. Reporting services It provides support for creating reports based on the database data. There are various tools which helps user to process the data. The centralized reports are created which are located in common server. 4. Analysis services The data saved in the data warehouses is used for analytical task. The decision making was much easier for the decision makers. The BI application are used for analysis. It is useful for evaluating the past numbers and calculating the future decisions. Huge data needs to be sampled. Microsoft SQL Server Analysis Services ( SSAS ) are used for providing Online Analytical Processing ( OLAP ) for the BI applications. The data is arranged in an accessible format. Online analysis of data is also possible using OLAP.
