SQL Server Introduction

Introduction

MS SQL Server is database management and analysis system for e-commerce, line of business and data warehousing solutions.

SQL Server Management Studio Overview

SQL Server contains “studios” to help you with development and management tasks: SQL Server Management Studio and Business Intelligence Development Studio. In Management Studio, you develop and manage SQL Server Database Engine and notification solutions, manage deployed Analysis Services solutions, manage and run Integration Services packages, and manage report servers and Reporting Services reports and report models.

In BI Development Studio, you develop business intelligence solutions using Analysis Services projects to develop cubes, dimensions, and mining structures; Reporting Services projects to create reports; the Report project to define models for reports; and Integration Services projects to create packages

Integration services

Integration Services is a platform for building high-performance data integration solutions, including packages that provide extract, transform, and load (ETL) processing for data warehousing.

Analysis Services

Analysis Services supports OLAP by allowing you to design, create, and manage multidimensional structures that contain data aggregated from other data sources, such as relational databases.

Reporting services

Reporting ServicesReporting Services delivers enterprise, Web-enabled reporting functionality so you can create reports that draw content from a variety of data sources, publish reports in various formats, and centrally manage security and subscriptions.

Following are the link to install samples

http://technet.microsoft.com/en-us/library/ms161556(v=sql.100).aspx

 

How to open SQL server

After install the SQl server, you will find SQL Server in “All Programs”. Find below screenshot to open SQL Server:

Master database

The master database records all the server specific configuration information, including authorizes users, databases, system configuration settings and remote servers

The master database contains critical data that controls the SQL Server operations

The master database also stores the initialization information of the SQl Server. Therefore if the master database is unavailable, the SQL Server database engine will not be started. Find below screenshot:

system-databases
system-databases

Tempddb database

Temp database is temporary database and holds all temporary tables and stored procedures All the temporary tables and results generated by GROUP BY, ORDER BY, and DISTINCT

Model database

Model database use as a template for all databases created on an instance of SQl server. Newly created User databases use the same recovery model as the model database.

MSdb database

It is used by SQl server agent

 

SQL (Structured Query Language)

As a database developer, you need to manage the database to store, access and modify data. SQl is the core language used to perform these operations on the data

SQL statements can be categorized as:

DDL(Data definition language):

  • Create
  • Alter
  • Drop

Data Manipulation language(DML)

  • Insert
  • Update
  • Delete

Data Control language

  • Grant: assign permissions
  • Revoke: deny permissions
  • DQL(Data Query Language)
  • Slect is DQL command

Data types

Data type specifies the type of data that an object can contain. Such as character data or Integer data. SQL server support following data types:

  • Int
  • Smallint
  • bigint
  • tinyint
  • char
  • varchar
  • decimal
  • image
  • float
  • XML
  • table