Sql Server Notes

SQL SERVER NOTES / 1

Sql Server Contents

Introduction

WHAT IS DATABASE

What is Table

DataTypes in Sql Server

DDL & DML

CONSTRAINTS

JOINS

STORED PROCEDURES

ERROR HANDLING

TRANSACTIONS

TRIGGERS

FUNCTIONS

VIEWS

EXECUTION PLAN

INDEXES

CLAUSES & FUNCTIONS

OPERATORS

SECURITY

Introduction

SQL SERVER IS A DATABASE MANAGEMENT SYSTEM WHICH IS USED TO STORE AND RETRIEVE DATA.

Why Database Management System.

• It stores data in centralized server. [ eg : single data server]
• Data is being updated instantly.
• Client – Server architecture – single server – multiple clients.
• It supports disaster recovery. We can back up the data in remote locations.
• We can backup and restore the data instantly.
  Security : we can give access to different users depending upon roles.
• Transactions : data can be rolled back.

Either all the tasks should be completed or it should be rolled back to the original position.

Examples of Database Management System :-> Oracle, My-SQL, SQL SERVER,MangoDB

SQL SERVER IS A DBMS.

SQL SERVER Architecture

Like all database systems, the SQL Server performs extremely complex tasks like memory management, optimum CPU utilization, query optimization, execution planning etc. at a lightning fast speed.

The diagram above gives you an overview of what the SQL Server architecture looks like. There are 3 important items here:

1. Database Engine:Performs the storage and query processing tasks
2. SQL Operating System:Works on I/O and other higher level management tasks
3. Interfaces:The External Protocols and SQL Operating System API connect the dots

The Database engine is the heart of Microsoft SQL Server. Think of it as the engine of a motorcycle, it is the part where the real stuff happens. It contains 2 major components:

• Query processor:Parses, optimizes and executes the query
• Storage engine:Manages buffer and access methods

Going with the motorcycle analogy, the SQL Operating System API and External Protocols are the wires and dials. They connect the speedometer to the front wheel, show you the speed. This speed can then be reduced or increased using the accelerator. They simply provide ways to connect internal and external components together and manipulate the operations.

The SQL Operating System is everything else, it is the frame, the seat, the tires. It is the structure that gets everything else going. You can have an engine and some wires, but unless you have the frame they will never mate together.

Each one of these items will get a separate post of their own, some of them are too vast to cover in one article and will stretch significantly! I hope my analogies, if somewhat weird, help you understand the concept easily. See you next time when we dive deep inside the Database Engine!

The major components of SQL Server are:

1. Relational Engine
2. Storage Engine
3. SQL OS

Now we will discuss and understand each one of them.

1) Relational Engine: Also called as the query processor, Relational Engine includes the components of SQL Server that determine what your query exactly needs to do and the best way to do it. It manages the execution of queries as it requests data from the storage engine and processes the results returned.

Different Tasks of Relational Engine:

1. Query Processing
2. Memory Management
3. Thread and Task Management
4. Buffer Management
5. Distributed Query Processing

2) Storage Engine: Storage Engine is responsible for storage and retrieval of the dataon tothe storage system (Disk, SAN etc.).tounderstand more, let’s focus on the concepts.

When we talk about any database in SQL server, there are 2 types of files that are created at the disk level –Data file and Log file. Data file physically stores the data in data pages. Log files that are also known as write ahead logs, are used for storing transactions performed on the database.

The DBMS system stored data in a database in the form of tables.

WHAT IS DATABASE

A database stores the data in a file.

.MDF(MASTER DATA FILE) : FILENAME, SIZE, LOCATION [ This is where the actual data is stored]

.LDF(LOG DATA FILE): FILENAME, SIZE, LOCATION [ This stores the user queries, logs]
Note : While creating database,we have to specify the name of the file,location and size of the file.

The MDF file is the heart of the database. All the data is stored in the mdf file.

The mdf file is divided into no of pages, each page size is of 8 KB. 1MB : 128 PAGES.

The fundamental unit of data storage in SQL Server is the page. The disk space allocated to a data file (.mdf or .ndf) in a database is logically divided into pages numbered contiguously from 0 to n. Disk I/O operations are performed at the page level. That is, SQL Server reads or writes whole data pages.

Extents are a collection of eight physically contiguous pages and are used to efficiently manage the pages. All pages are stored in extents.

Pages

In SQL Server, the page size is 8 KB. This means SQL Server databases have 128 pages per megabyte.

Each page begins with a 96-byte header that is used to store system information about the page.

This information includes the page number, page type, the amount of free space on the page, and the allocation unit ID of the object that owns the page.

The following table shows the page types used in the data files of a SQL Server database.

Page type / Contents
Data / Data rows with all data, except text,ntext, image, nvarchar(max), varchar(max), varbinary(max), and xml data, when text in row is set to ON.
Index / Index entries.
Text/Image / Large object data types:

• text,ntext, image, nvarchar(max), varchar(max), varbinary(max), and xml data

Variable length columns when the data row exceeds 8 KB:

• varchar, nvarchar, varbinary, and sql_variant

Global Allocation Map, Shared Global Allocation Map / Information about whether extents are allocated.
Page Free Space / Information about page allocation and free space available on pages.
Index Allocation Map / Information about extents used by a table or index per allocation unit.
Bulk Changed Map / Information about extents modified by bulk operations since the last BACKUP LOG statement per allocation unit.
Differential Changed Map / Information about extents that have changed since the last BACKUP DATABASE statement per allocation unit.
Note
Log files do not contain pages; they contain a series of log records.

Data rows are put on the page serially, starting immediately after the header.

A row offset table starts at the end of the page, and each row offset table contains one entry for each row on the page. Each entry records how far the first byte of the row is from the start of the page.

The array has a 2-byte slot for each row in the table. The slot contains the byte offset on the page in which you can locate each row. The array starts with slot 0 at the end of the page, and slot numbers increase as the array grows inward from the bottom of the page. Each row has a row number on the page, and that number is the index in the array for the start of the row. For example, if you need to retrieve row 2 from a data page, SQL Server looks in the third 2-byte slot from the end of the page and finds a byte offset. That offset is the page position of the data row.

The entries in the row offset table are in reverse sequence from the sequence of the rows on the page.

A database page is an 8192-byte (8KB) chunk of a database data file. They are aligned on 8KB boundaries within the data files, starting at byte-offset 0 in the file. Here’s a picture of the basic structure:

Header

The page header is 96 bytes long. What I’d like to do in this section is take an example page header dump from DBCC PAGE and explain what all the fields mean. I’m using the database from the page split postand I’ve snipped off the rest of the DBCC PAGE output.

User will store the data in a table inside the database

SQL SERVER IS A COLLECTION OF DATABASES.

A DATABASE IS A COLLECTION OF TABLES.

A TABLE IS A COLLECTION OF ROWS & COLUMNS.

A TABLE STRUCTURE IS DEFINED IN THE FORM OF COLUMNS.

THE DATA IS DEFINED IN THE FORM OF ROWS.

What is Table

A TABLE REPRESENTS THE STRUCTURE OF THE DATA.

A Table represents a real world objects.

IN SQL SERVER, FIRST WE HAVE TO DEFINE THE STRUCTURE. (First we have to create the table and then we can insert values into the table.)

//EntityName

Create table table_name

(

Col_name1 datatype, //attributes

Col_name2 datatype,

Col_name3 datatype

…..

)

DataTypes in Sql Server

Datatype define the type of data to be used for the columns of the table.

Datatypes are predefined in SQL SERVER

Popular Data Type

INT : INTEGER DATA

VARCHAR(SIZE) : STRING DATA

FLOAT : DECIMAL DATA

DATETIME : DATES

BIT: 0 OR 1

Example : STUDENT Table With its attributes

COLUMNS / DATATYPE
ROLLNO / INT
NAME / VARCHAR(100)
COURSE / VARCHAR(100)
EMAIL / VARCHAR(100)

CREATE TABLE STUDENT

(

ROLLNO INT,

NAME VARCHAR(100),

COURSE VARCHAR(100),

EMAIL VARCHAR(100),

)

Complete DataType List in Sql Server

SQL Server / Data Precision
BIT / Integer: 0 or 1
TINYINT / Positive Integer 0 -> 255
SMALLINT / Signed Integer -32,768 -> 32,767
INT / Signed Integer -2^31 -> 2^31-1
BIGINT / Signed Integer -2^63 -> 2^63-1
REAL / Floating precision -1.79E + 308 -> 1.79E + 308
FLOAT / Floating precision -3.40E + 38 -> 3.40E + 38
MONEY / 4 decimal places, -2^63/10000 -> 2^63-1/10000
SMALLMONEY / 4 decimal places, -214,748.3648 -> 214,748.3647
DECIMAL / Fixed precision -10^38 + 1 -> 10^38 – 1
NUMERIC / Fixed precision -10^38 + 1 -> 10^38 – 1
DATETIME / Date+Time 1753-01-01 -> 9999-12-31, accuracy of 3.33 ms
SMALLDATETIME / Date+Time 1900-01-01 -> 2079-06-06, accuracy of one minute
CHARn / Fixed-length non-Unicode string to 8,000 characters
NCHARn / Fixed-length Unicode string to 4,000 characters
VARCHARn / Variable-length non-Unicode string to 8,000 characters
NVARCHARn / Variable-length Unicode string to 4,000 characters
TEXT / Variable-length non-Unicode string to 2,147,483,647 characters
NTEXT / Variable-length Unicode string to 1,073,741,823 characters
BINARY / Fixed-length binary data up to 8,000 characters
VARBINARY / Variable-length binary data up to 8,000 characters
IMAGE / Variable-length binary data up to 2,147,483,647 characters

DDL & DML

DDL (Data Defination Language) is used to create, alter , drop tables(structures)

DML (DATA MANIPULATION LANGUAGE) is used to insert, update, delete, select data.

COLUMNS / DESCRIPTION
INSERT / To add a new record into the table
INSERT INTO table_name values(value1, value2, value3) //To insert values into all the columns
INSERT INTO table_name(colname1, colname2, colname3) values(value1, value2, value3)
//To insert values into selected columns
INSERT INTO STUDENTS VALUES(101,’AMIT’,’SQL’,’’)
UPDATE / To update the values in the table
UPDATE table_name set col_name1=newvalue1,col_name2=newvalue2 where condition
UPDATE STUDENTS SET EMAIL=’’ where rollno=101
Update Employee set Salary= 50000 where EMPID=1 (IF WE SKIP WHERE CONDITION THEN ALL THE RECORDS WILL BE UPDATED)
DELETE / To remove the records from the table
Delete from table_name where condition : It will delete those records which matches the condtion.
Delete from table_name :It will delete all the records.
Delete from STUDENTS where rollno=101
Delete from employee where EmpId = 1
Delete from STUDENTS [ IF WE SKIP WHERE CONDITION ,IT will delete all the records in the table]
SELECT / To view the records
Select * from table_name where condition
Select colname1, colname2, colname3 from table_name where condition
Select * from students
Select * from students where rollno> 100

[EXAMPLE COUNTRY TABLE]

CREATETABLE COUNTRY

(

COUNTRY_IDINT,-- 4 BYTES

COUNTRY_NAMENVARCHAR(100),

COUNTRY_CAPITALNVARCHAR(100)

)

ALTER command is used to make changes to the structures in the existing table.

ALTER TABLE:

ALTER TABLE TABLE_NAME ADDCOLUMN_NAME DATATYPE CONSTRAINTS

COMMANDS / SYNTAX & Example / Description
ADD COLUMN / ALTER TABLE TABLE_NAME ADD COLUMN_NAME DATATYPE
ALTER TABLE COUNTRY ADD CONTINENT NVARCHAR(100) / Adding a new column to the existing table
DROP COLUMN / ALTER TABLE TABLE_NAME DROP COLUMN COLUMN_NAME
ALTER TABLE COUNTRY DROP COLUMN COUNTRY_CAPITAL / Drop a column from the table
ALTER COLUMN / ALTER TABLE COUNTRY ALTER COLUMN COUNTRY_NAME VARCHAR(100)
ALTER TABLE COUNTRY ALTER COLUMN CONTINENT VARCHAR(50) / ALTER COLUMN - MAKING CHANGES INTO THE EXISTING COLUMNS
COMMAND / SYNTAX
DELETE : TO REMOVE RECORDS WITH CONDITIONS / DELETE FROM TABLE_NAME WHERE CONDITION
TRUNCATE : WILL REMOVE ALL THE RECORDS IN THE TABLE / TRUNCATE TABLE TABLE_NAME
TRUNCATETABLECOUNTRY
DROP : DROP WILL REMOVE THE RECORDS ALONG WITH THE STRUCTURE / DROP TABLE TABLE_NAME
DROP TABLECOUNTRY

DIFFERENCE BETWEEN DELETE, TRUNCATE & DROP

DELETE / TRUNCATE / DROP
IN DELETE WE CAN DELETE A SINGLE RECORD OR MULTIPLE RECORDS BASED UPON CONDITIONS.
Eg : delete from table_name / TRUNCATE IS USED TO DELETE THE COMPLETE DATA, WE CANNOT SPECIFY ANY CONDITIONS FOR IT.
Eg : truncate table table_name
Truncate table emp / Drop will remove the structures along with the data.
Eg : drop table table_name
Eg : drop table emp

RENAME: COMMAND IS USED TO RENAME A TABLE OR A COLUMN.

1. RENAME A TABLE

sp_RENAME'[OldTableName]','[NewTableName]'
Example :sp_RENAME'Table_First','Table_Last'

1. RENAME A COLUMN

sp_RENAME'TableName.[OldColumnName]', ‘[NewColumnName]','COLUMN'
Example :sp_RENAME'Table_First.Name','NameChange','COLUMN'

SELECT * INTO NEW_TABLE_NAME FROM TABLE_NAME

COPY THE DATA FROM THE EXISTING TABLE AND CREATE A NEW TABLE WITH ALL THE DATA.

HOW TO ACCESS TABLES FROM ANOTHER DATABASE

SELECT * FROM DATABASE.dbo.TABLENAME

IDENTITY COLUMNS

IDENTITY [AUTO-INCREMENT COLUMNS]
IF WE DEFINE A COLUMN AS IDENTITY THEN VALUES INTO THE COLUMN WILL BE INSERTED AUTOMATICALLY BY SQL- SERVER. [Identity only works with integer columns]
Identity(start_value,increment_value)
Column_namedatatypeidentity(1,1)
Column_namedatatypeidentity(1000,10)
CREATE TABLE EMPS
(
EMPID INT IDENTITY(1,1), EMPID INT IDENTITY(100,200), 100 , 300, 500,700, 900
NAME VARCHAR(100),
SALARY FLOAT
)
INSERT INTO EMPS VALUES (‘AMIT’, 20000)
INSERT INTO EMPS VALUES (‘KAPIL’,30000)

AUTOINCREMENTED COLUMNS :

AUTOINCREMENTED COLUMNS are the type of columns in which the value is inserted automatically.

Column_namedatatypeIDENTITY(initialvalue,incrementvalue)

EMPID INT IDENTITY(1,1)

CONSTRAINTS

NULL | NOT NULL COLUMN

COLUMN BY DEFAULT NULL

CONSTRAINTS : Putting Limitations/CONDITIONS into the values inserted into the columns

Column_namedatatype constraint

EMPID INT IDENTITY(1,1) CONSTRAINT PK_EMPID PRIMARY KEY

Constraint name should be unique in a database.

CONSTRAINTS / Definitions
PRIMARY KEY / This will make the column as unique and not null.
Column_namedatatype constraint constraint_name PRIMARY KEY
Constraint is a keyword.
Constraint_name is the name of the constraint
PRIMARY KEY
There can only be one primary key on a table.
EMPID INT CONSTRAINT pk_empid PRIMARY KEY
UNIQUE / This will make the column as unique and can accept one null value.
In a table there be multiple unique columns.
Column_namedatatype constraint constraint_name UNIQUE
EMPNAME NVARCHAR(100) constraint u_name UNIQUE
CHECK / Check will put a limit/condition on the column that it should accept only those values defined in the check condition
Column_namedatatype constraint constraint_name CHECK(condition)
GENDER NVARCHAR(100) constraint C_GENDER CHECK(GENDER IN(‘M’,’F’))
SALARY FLOAT constraint C_SAL CHECK(SALARY > 10000)
DEFAULT / Default will put a default value on the column if user is not inserting any values.
Column_namedatatype constraint constraint_name DEFAULT(value)
Joining_DateDateTime constraint d_def default(‘10/10/2014’)
CREATE TABLE EMPLOYEE
(
EMPID INT CONSTRAINT pk_empid PRIMARY KEY,
EMPNAME NVARCHAR(100) constraint u_name UNIQUE,
GENDER NVARCHAR(100) constraint C_GENDER CHECK(GENDER IN(‘M’,’F’)),
SALARY FLOAT constraint C_SAL CHECK(SALARY > 10000)
)
FOREIGN KEY / RELATIONSHIP BETWEEN TWO TABLES.
Sql server is a relational database management system
We can have relation between two tables.
That relation is done by putting a common column between two tables.
Syntax :
Column_namedatatype constraint constraint_name FOREIGN KEY references
PRIMARY TABLE(colname)(

WHAT IS RELATIONAL DATABASE

COUNTRYDETAILS

COUNTRYID / COUNTRY_NAME / COUNTRY_CAP / CONTINENT / STATEID / STATE_NAME / STATE_CAPITAL
1 / INDIA / DELHI / ASIA / 1 / MH / MUMBAI
1 / IND / DELHI / ASIA / 2 / UP / LUCKNOW
1 / IND / DELHI / ASIA / 1 / GUJURAT / AHMEDABAD

Advantages of Relational Database

• Saving of Data
• Consistency of Data

Parent table

COUNTRYID / COUNTRY_NAME / COUNTRY_CAP / CONTINENT
1(PRIMARY KEY / INDIA / DELHI / ASIA

Child table

STATEID / STATE_NAME / STATE_CAPITAL / COUNTRY_ID(FOREIGNKEY)
1 / MH / MUMBAI / 1
2 / UP / LUCKNOW / 1
3 / GUJURAT / AHMEDABAD / 1

FOREIGN KEY

COLUMN_NAME DATATYPE CONSTRAINT CONSTRAINT_NAME FOREIGN KEY MASTER_TABLE(COLUMN_NAME)

Syntax :

column_namedatatype constraint constraint_name FOREIGN KEY references table_name(PrimaryKeyColumn_Name)

Examples of Primary Key & Foreign Key

CREATETABLECOUNTRYDETAILS
(
COUNTRY_IDINTCONSTRAINTpk_countryPRIMARYKEY,
COUNTRY_NAMENVARCHAR(100),
COUNTRY_CAPNVARCHAR(100),
CONTINENTNVARCHAR(100)
)
CREATETABLESTATEDETAILS
(
STATE_IDINTCONSTRAINTpk_statePRIMARYKEY,
STATE_NAMENVARCHAR(100),
STATE_CAPNVARCHAR(100),
COUNTRY_IDINTCONSTRAINTFK_STATE
FOREIGNKEYREFERENCESCOUNTRYDETAILS(COUNTRY_ID)
)
Createtablecity
(
CITY_IDINTPRIMARYKEY,
CITY_NAMENVARCHAR(100),
STATE_IDINTCONSTRAINTFK_STATEFOREIGNKEYREFERENCESSTATE(STATE_ID)
)
CREATETABLESCHOOL
(
SCHOOLIDINTCONSTRAINTPK_SCHOOLIDPRIMARYKEY,
SCHOOLNAMENVARCHAR(100),
LOCATIONNVARCHAR(100),
WEBSITENVARCHAR(100)
)
--A PARENT CHILD RELATIONSHIP IS CREATED BETWEEN TWO TABLES
--BY HAVING THE PRIMARY KEY IN THE PARENT TABLES AS
--FOREIGN KEY IN CHILD TABLE.
CREATETABLECLASS
(
CLASSIDINTCONSTRAINTPK_CLASSIDPRIMARYKEY,
CLASSNAMENVARCHAR(100),
SCHOOLIDINTCONSTRAINTFK_SCHOOLIDFOREIGNKEYREFERENCESSCHOOL(SCHOOLID)
)
Rules of Foreign Key

• only those values which are there in parent table can be inserted in child table.
• the parent record cannot be deleted until and unless all its children have been deleted.

InsertintoSCHOOLvalues(1,'JOSEPH SCHOOL','PUNE','
InsertintoCLASSvalues(1,'CLASS-I',NULL)
InsertintoCLASSvalues(2,'CLASS-II',1)
DELETEFROMSCHOOLWHERESCHOOLID=1

TO ADD AND DROP A CONSTRAINTS WE HAVE THE FOLLOWING SYNTAX

1. ALTER TABLE TABLE_NAME ADD CONSTRAINT

Add a constraint to the table , if the table is already defined.

Alter table table_name add constraint constraint_name CONSTRAINT_TYPE(colname)

Alter table STUDENT add constraint pk_rollno PRIMARY KEY(ROLLNO)

1. ALTER TABLE TABLE_NAME DROP CONSTRAINT

Drop a constraint from the table.

ALTER TABLE TABLE_NAME DROP CONSTRAINT constraint_name

Alter table STUDENT drop constraint pk_rollno

JOINS

JOINS ARE USED TO GET DATA FROM MULTIPLE TABLES.

SYNTAX :

SELECT * FROM TABLE1INNER JOIN TABLE2 ON TABLE1.COLNAME(P.k) = TABLE2.COLNAME(f.K)

SELECT *FROM COUNTRY INNER JOIN STATE ON COUNTRY.COUNTRYID = STATE.COUNTRYID

SELECT * FROM TABLE1 INNER JOIN TABLE2 ON TABLE1.COLNAME(P.k) = TABLE2.COLNAME(f.K)

INNER JOIN TABLE3 ON TABLE2.COLNAME = TABLE3.COLNAME

TYPES OF JOINS

INNER JOIN / LEFT OUTER JOIN / RIGHT OUTER JOIN / FULL OUTER JOIN
Common records from both the tables. / Common records from both the tables + all records from left tables. / Common records from both the tables + all records from right tables. / Common records from both the tables + all records from left tables and all records from right table.

SELECT * FROM TABLE1(left) INNER JOIN TABLE2(right) ON TABLE1.COLNAME(P.k) = TABLE2.COLNAME(f.K)