SQL Server Components
SQL Server Database Engine
The core service for storing and processing data.
SQL Server Database Engine includes the Database Engine, the core service for storing, processing, and securing data, Replication, full-text search, and tools for managing relational and XML data.
The Database Engine also features these components
- Full-Text Search
- Service Broker
- Replication
- Notification Services
Analysis Services (SSAS)
Tools for creating and managing online analytical processing (OLAP) and data mining applications.
Reporting Services (SSRS)
Components for creating and deploying reports.
Reporting Services includes server and client components for creating, managing, and deploying tabular, matrix, graphical, and free-form reports. Reporting Services is also an extensible platform that you can use to develop report applications.
Integration Services (SSIS)
Tools for moving, copying, and transforming data
Integration Services is a set of graphical tools and programmable objects for moving, copying, and transforming data.
Integration Services (SSIS)
SQL Server Management Tools
SQL Server Management Studio
SQL Server Management Studio is an integrated environment to access, configure, manage, and administer components of SQL Server. Management Studio lets developers and administrators of all skill levels use SQL Server.
SQL Server Configuration Manager
SQL Server Configuration Manager provides basic configuration management for SQL Server services, server protocols, client protocols, and client aliases.
SQL Server Profiler
SQL Server Profiler provides a graphical user interface to profile and trace an instance of the Database Engine or Analysis Services.
Database Engine Tuning Advisor
Database Engine Tuning Advisor helps create optimal sets of indexes, indexed views, and partitions.
Business Intelligence Development Studio (BIDS) – deprecated
The Business Intelligence Development Studio is an IDE for creating Analysis Services, Reporting Services, and Integration Services solutions.
Connectivity Components
Installs components for communication between clients and servers, and network libraries for DB-Library, ODBC, and OLE DB.
SQL Server Database Objects
Tables
Tables are the main form for collection of information. Tables are objects that contain all the data in SQL Server databases. Each table represents a type of object that is meaningful to its users.
Views
A view can be thought of as either a virtual table or a stored query. The data accessible through a view is not stored in the database as a distinct object. What is stored in the database is a SELECT statement. The result set of the SELECT statement forms the virtual table returned by the view. A user can use this virtual table by referencing the view name in Transact-SQL statements the same way a table is referenced.
Indexes
An index is an on-disk structure associated with a table or view that speeds retrieval of rows from the table or view. An index contains keys built from one or more columns in the table or view. These keys are stored in a B-tree structure that enables SQL Server to find the row or rows associated with the key values quickly and efficiently. There are clustered and non-clustered indexes.
Triggers
A trigger is a database object that is attached to a table. In many aspects it is similar to a stored procedure. As a matter of fact, triggers are often referred to as a “special kind of stored procedure.”The main difference between a trigger and a stored procedure is that the former is attached to a table and is only fired when an INSERT, UPDATE or DELETE occurs. You specify the modification action(s) that fire the trigger when it is created.
Procedures
Stored procedures in Microsoft SQL Server are similar to procedures in other programming languages in that they can: Accept input parameters and return multiple values in the form of output parameters to the calling procedure or batch. Contain programming statements that perform operations in the database, including calling other procedures. Return a status value to a calling procedure or batch to indicate success or failure (and the reason for failure). You can use the Transact-SQL EXECUTE statement to run a stored procedure. Stored procedures are different from functions in that they do not return values in place of their names and they cannot be used directly in an expression.
Constraints
The primary job of a constraint is to enforce a rule in the database. Together, the constraints in a database maintain the integrity of the database. For instance, we have foreign key constraints to ensure all orders reference existing products. You cannot enter an order for a product the database does not know about. Maintaining integrity is of utmost importance for a database, so much so that we cannot trust users and applications to enforce these rules by themselves. Once integrity is lost, you may find customers are double billed, payments to the supplier are missing, and everyone loses faith in your application.
Rules
A rule specifies the acceptable values that can be inserted into that column.
