INTRODUCTION TO SQLSERVER:
SQLSERVER IS A RELATIONAL DATABASE AN RDBMS PRODUCT FROM
MICROSOFT IN [Link] IS USED TO STORE DATA (OR) INFORMATION
PERMANANTLY i.e. IN HARD DISK ALONG WITH SECURITY.
SQLSERVER IS A PLATFORM INDEPENDENT AN RDBMS [Link]
MEANS THAT IT CAN DEPLOYEE (INSTALL) IN ANY OS LIKE WINDOWS, LINUX,
SOLARIES, MAC.....etc.
PLATFORM:
- IT A COMBINATION OF OPERATING SYSTEM AND MICRO
[Link] ARE AGAIN CLASSIFIED INTO TWO TYPES.
1) PLATFORM INDEPENDENT:
- IT SUPPORTS ANY OS WITH THE COMBINATION OF ANY MICRO
PROCESSOR.
EX: SQLSERVER, ORACLE, MYSQL, JAVA, .NET....etc
2) PLATFORM DEPENDENT:
- IT SUPPORTS ONLY ONE OS WITH COMBINATION OF ANY MICRO
PROCESSOR.
EX: C - LANGUAGE.
Versions of SQLSERVER:
VERSIONS YEAR RELEASE NAME CODE NAME
1.0 1989 SQL Server 1.0 -
1.1 1991 SQL Server 1.1 -
4.21 1993 SQL Server 4.21 SQLNT
6.0 1995 SQL Server 6.0 SQL95
6.5 1996 SQL Server 6.5 Hydra
7.0 1998 SQL Server 7.0 Sphinx
8.0 2000 SQL Server 2000 Shiloh
9.0 2005 SQL Server 2005 Yukon
10.0 2008W SQL Server 2008 Katmai
10.5 2010 SQL Server 2008 R2 Kilimanjaro
11.0 2012 SQL Server 2012 Denali
12.0 2014 SQL Server 2014 SQL14
13.0 2016 SQL Server 2016 SQL16
WORKING WITH SQLSERVER:
WHEN WE INSTALL SQLSERVER SOFTWARE INTERNALLY TWO
COMPONENTS ARE [Link] ARE,
1. SQLSERVER CLIENT
2. SQLSERVER DATABASE SERVER
1. SQLSERVER CLIENT:
BY USING SQLSERVER CLIENT TOOL USER CAN PERFORM THE FOLLOWING
THREE OPERATIONS ARE
USER CAN CONNECT TO SQLSERVER SERVER
USER CAN SEND REQUEST TO SQLSERVER SERVER
USER CAN RECEIVE RESPONSE FROM SQLSERVER SERVER.
Ex: SQLSERVER MANAGEMENT STUDIO (SSMS).
2. SQLSERVER DATABASE SERVER:
SQLSERVER DATABASE SERVER MANAGE TWO MORE SUB COMPONENTS
INTERNALLY THOSE ARE,
INSTANCE
DATABASE
INSTANCE WILL ACT AS TEMPORARY MEMORY WHICH WILL ALLOCATE FROM
RAM AND STORED DATA / INFORMATION TEMPORARY WHERE AS DATABASE IS
A PERMANENT MEMORY WHICH WILL ALLOCATE FROM HARDDISK AND STORED
DATA PERMANENTLY.
NOTE: WHEN WE WANT TO WORK ON SQLSERVER DATABASE THEN WE
FOLLOW THE FOLLOWING TWO STEPS PROCEDURE
1) CONNECT TO SQLSERVER:
IF USER WANTS TO CONNECT TO SQLSERVER THEN WE REQUIRED A
DATABASE TOOL IS CALLED AS “SQLSERVER MANAGEMENT
STUDIO” WHICH WAS INBUILTED IN SQLSERVER SOFTWARE.
2) COMMUNICATE WITH DATABASE:
IF USER WANTS TO COMMUNICATE WITH DATABASE THEN WE
NEED A DATABASE COMMUNICATION LANGUAGE IS CALLED AS
“SQL”.
HOW TO CONNECT TO SQLSERVER:
WHEN WE WANT TO CONNECT TO SQLSERVER THEN WE NEED TO SET THE
FOLLOWING OPTIONS IN "CONNECT TO SERVER" WINDOW.
Server Type
Server Name
Authentication
Username & Password
Server Type: SQL server contains four 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.
Server name: In which system we installed SQLSERVER software that system
name will use as a server name for connecting to SQLSERVER.
Ex: System name (or) Local host (or) .
Authentication: We have two types of authentications are
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).
Step To Connect To SQL SERVER:
Go to startGo to programsGo to Microsoft SQL Server 20014/16Click on
SQL server management studioClick on connect button.
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.
Temp db: When ever SQL server is started temp db will be created
in SQL server. It is used to store temporary tables once we restart
the server the temp db database is destroyed.
User Database: These databases are created and manage by the user for
storing their objects like tables, views, procedure etc.
TYPE -1:
SYNTAX:
CREATE DATABASE <DB NAME>;
EX:
CREATE DATABASE MYDEMO1;
NOTE: WHEN WE WANT TO CREATE A NEW DATABASE IN SQLSERVER BY USING
"CUI" THEN WE NEED AN EDITOR IS NAMED AS "QUERY EDITOR" WHICH WAS
IN-BUILTED IN SSMS TOOL.
TYPE-2:
WHEN WE WANT TO CREATE A NEW DATABASE IN SQLSERVER BY USING
"GUI" THEN WE NEED TO GO TO "OBJECT EXPLORER WINDOW" WHICH WAS IN-
BUILTED IN SSMS TOOL.
Steps to Create User Database:
Go to open SQL server management studioClick on Connect button to
connect serverGo to Object Explorer windowSelect Database and click
on right mouse button Click on new database option Type database
name in database name textbox controlClick on Ok button
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\[Link]\MSSQL\DATA
