Introduction to SQL Server

Discussion in 'SQL Server' started by MinalS, Mar 16, 2016.

Tags:
  1. 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:
    1. The data present in large tables is distributed into various filegroups. The server can easily access the filegroups and access the data effectively.
    2. Data can be stored in XML format. The data in the columns and variables of XML data type are processed
    3. An asynchronous, distributed framework is provided for various applications. User can easily send the requests to the web server
    4. It provides intellisense support to the user for coding statements
    5. An integrated environment for the SQL Server Database Engine, Reporting and analysis services which helps to run the instructions immediately
    6. It is easier for user to audit logons, password modifications, accessing data, results of audit are saved in files
    7. 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
    8. The downtime of the server is reduced as resources of database server are available to the users at all times
    9. The HTTP endpoints allows user to access the data from web services
    10. The data can be easily migrated from various data sources to a database which is common for all users
    11. The CLR features of the framework can be used in the Server database
    12. 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:
    1. Database Engine
    2. Integration Services
    3. Reporting Services
    4. 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.
     

Share This Page

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice