Introduction To SQL Server

Definition: SQL Server is a collection of databases where database is a collection of various
 objects like Tables, Views, Procedures and Functions etc.

To work on SQL Server we use SQL Server Management Studio. It is a tool will provide Command
 Based Environment and GUI Based Environment to perform operations in SQL server.
If we connect to server it shows a window with ….
 Server Type
 Server Name
 Server Authentication
 Username & Password

Server Type: SQL server contains five types of servers those are
 Database Engine: The Database Engine is the core service for storing,processing, and securing
data (or) it is used to store, manage and to access the data from the database.

 Analysis Services (SSAS): It is used for data warehouse it will show the data in three dimensions
 (Rows, Columns and New dimension).

 Reporting Services (SSRS): It is a reporting tool used to generate reports in various formats
 such as creating interactive, tabular, graphical,multidimensional, or XML-based data sources.
 Reports can include rich data visualization, including charts, maps etc.

 Integration Services(SSIS): It is used to convert tables from relational database to another relational database for e.g. If we want to convert SQL Server tables to ORACLE tables
 or My SQL tables then will be used.

 SQL Server Compact Edition: It is used to develop mobile application or mobile software.


SQL Server Authentication:SQL Server have two types of authentications

Windows Authentication: Windows Authentication work on the user admin and when we
 work on window authentication there is no required user name and password because
 operating system will generate User Id and Password by default.

SQL Server Authentication: SQL Server will work on the current user and when we work on
 SQL Server authentication then user should enter User Id and Password (These User ID and Password will give at the time of SQL Server installation).

Object Explorer Window: This window contain Database, Security, Server Objects, Replication and
 Management options. SQL Server contains two types of databases these are

 System Database: The system database include the following four databases

 Master: It is used to manage system level information of SQL server.
 Model: It is used as a template for all new creating databases in SQL Server.

 Msdb: It is used to store the alerts and job information contains the SQL commands which
 are executed by user.

 Tempdb: When ever SQL server is started tempdb will be created in SQL server. It is used to
 store temporary tables once we restart the server the tempdb database is destroyed
.
 User Database: These databases are created and manage by the user for
storing their objects like tables, views, procedure etc.

Whenever we create a database on SQL Server, it will generate two
database files are

Primary Data file: It contain the start up information of the database and used
to store database objects like tables, views .This file will saved with an extension
.mdf(Master Data file).

Log File: This file contains transaction query information will saved with an
extension .Ldf (Log Data file).

Root Location for .mdf and .ldf files:
C:\Program Files\Microsoft SQL
Server\MSSQL10.MSSQLSERVER\MSSQL\DATA


Posted By: pankaj_bhakre