Brief Introduction to SQL Server 2005

To verify if SQL Server Express is working on our machine: Control Panel -> Adminitrative Tools -> Services -> SQL Server (SQLEXPRESS) -> Start if not already started.

Types of Authentication in SQL Server are:

Windows Authentication: The identity of the client on the Domain of the OS / Network is used by SQL Server to allow or deny access to the resources in the database
SQL Server Authentication: The permissions to the client are granted based on the identity which was created and stored in SQL Server database.

Note: The default installation of Express Edition only Supports Windows Authentication.

Steps for Configuring SQL Server to support both the types of Authentication:

Start -> Programs -> Microsoft SQL Server 2005 -> SQL Server Management Studio -> Connect ->

Right-click on Root of the Tree -> Properties -> Select Security -> Check SQL Server and Windows Authentication Mode.
Expand Security -> Logins -> Select User "sa" -> Right Click - Properties -> Set Password ->
Also Select Status (on left side) -> Check Login Enabled.
Disconnect and Connect again with SQL Server Authentication so that we are sure the above steps were performed correctly.

Note: SQL Server Management must be already installed on the machine.

System databases in SQL Server

Master

The Master database holds information for all databases located on the SQL Server instance and is the glue that holds the engine together. Because SQL Server cannot start without a functioning master database, you must administer this database with care. For this reason, it is vital to make regular backups of this database.

This database includes information such as system logins, configuration settings, linked servers, and general information regarding the other system and user databases for the instance. The master database also holds extended stored procedures, which access external processes, allowing you to interact with features such as the disk subsystem and system API calls.
Model: Model is essentially a template database used in the creation of any new user database created in the instance. You can place any stored procedures, views, users, etc. in the model database so that when a new database is created, the database will contain the objects you have placed in the model database.

Tempdb

As its name implies, tempdb holds temporary objects such as global and local temporary tables and stored procedures.

This database is recreated every time SQL Server starts, and the objects contained in it will be based upon the objects defined in the model database. In addition to these objects, tempdb also houses other objects such as table variables, results sets from table-valued functions, and temporary table indexes. Because tempdb will hold these types of objects for all of the databases on the SQL Server instance, it is important that the database is configured for optimal performance.

Msdb

The msdb database stores information regarding database backups, SQL Agent information, DTS packages, SQL Server jobs, and some replication information such as for log shipping.

Types of SQL Statements

Data Definition Language (DDL) : Create, Alter, Drop, Truncate
Data Manipulation Language (DML) : Insert , Update , Delete
Data Query Language(DQL) : Select
Transaction Control Language (TCL) : Commit , RollBack , SavePoint
Data Control Language (DCL) : Grant , Revoke

CREATE DATABASE

Create Database DemoDb
Use DemoDb (To use the newly created database)
sp_helpdb DemoDb (Describes the structure of a database)

Data types in SQL Server

Numeric: TinyInt, SmallInt, Int, BigInt, Decimal, Numeric, Float, Real, Bit
String : Char, Varchar, NChar, NVarChar, Text, NText, Varchar(Max)
Currency: Money , SmallMoney
Date and Time : DateTime, SmallDateTime
Binary: Binary, VarBinary, VarBinary(Max)
Miscellaneous : Table , Cursor, Sql_Variant, TimeStamp, Image , Xml

CREATE TABLE

CREATE TABLE [dbo].[Department](
[DeptId] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
[DeptName] [varchar](50) NOT NULL,
[DateOfFormation] [datetime] NULL
)
CREATE TABLE [dbo].[Employee](
[EmpId] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
[EmpName] [varchar](50) NOT NULL,
[BasicSalary] [money] NOT NULL,
[Allowances] [money] NOT NULL,
[Deductions] [money] NOT NULL,
[DeptId] [int] NOT NULL,
[DateOfBirth] [datetime] NOT NULL,
)

ALTER TABLE

Alter Table <table name > Add <column name> <data type>
Alter Table <table name> Alter Column <column name> <data type>
Alter Table <table name> Drop Column <column name>

Ex:
Alter table Employee add Designation varchar(20)
Alter table Employee alter column Designation varchar(50) NOT NULL
Alter table Employee drop column Designation

DROP TABLE

Drop Table <TableName>

*DDL (Create, Alter, Drop) Statements cannot be rolledback.

INSERT

Syntax:
Insert into <table name> (column list) values (value1,value2...)Ex: INSERT INTO [Employee](EmpName,BasicSalary,Allowances,Deductions,DeptId,DateOfBirth,Location,Designation)VALUES ('E1',10000,1000,200,1,'1/1/1980','Hyderabad','Manager')

Note- For inserting NULL value into a column either column name can be skipped or NULL can be used for value

Set IDENTITY_INSERT <tablename> ON : Allows to insert value for Identity column - This can be done on only one table at a time

UPDATE

Syntax:
update <table name> set col1 = val1 , col2 = val2 Ex: update Employee Set EmpSalary = EmpSalary + 100 where EmpId=1

DELETE

Syntax:
delete from <table name> [where expression]
Delete from Employee where EmpId=4 -Deletes only one record
Delete from Employee -Deletes all the records from the Employee table

TRUNCATE

It is functionally identical to the Delete statement but is much faster when compared to delete as Delete removes rows one at a time and records an entry in the transaction log for each deleted row so the delete execution is slower when compared to truncate.

Records deleted using truncate cannot be rolled back. Also the identity column value is reset.
Syntax: truncate table <table name>

SELECT

SELECT select_list [ INTO new_table ]
FROM table_source
[ WHERE search_condition ]
[ GROUP BY group_by_expression ]
[ HAVING search_condition ]
[ ORDER BY order_expression [ ASC | DESC ]

Examples:

Select * From Employee
Select EmpId,EmpName,BasicSalary From Employee]

It is recommended to always replace "*" with column names in the select statement. By doing this we can provide only the required columns and this optimizes the performance of the query.

Select EmpId AS ID, EName = EmpName From Employee – Aliasing a Column
Select DISTINCT BasicSalary From Employee
Select IDENTITYCOL from Employee
Select TOP 3 * From Employee
Select TOP 50 PERCENT * From Employee
Select cast(EmpName as varchar(5)) as ShortName From Employee
Select * From Employee Order By EmpName, BasicSalary DESC.

Note ntext, text, or image columns cannot be used in an ORDER BY clause.
Null values are treated as the lowest possible values.

Operators:

Arithmetic operators	+ , - , * , / , %
Basic Relational operators	= , > , < , >= , <= , ! , != , <> , !> , !<
Logical operators	And , or , Not
Advanced Relational operators	In , Not In , Is Null , Is Not Null , Between , Not Between , Like , Not Like, Any, ALL

update <table name> set col1 = val1 , col2 = val2

Ex:

update Employee Set EmpSalary = EmpSalary + 100 where EmpId=1
Select EmpName, BasicSalary + Allowances - Deductions As Salary from Employee
Select * from Employee where BasicSalary > 2000 and Designation = 'clerk'
Select * from Employee where Designation In ('clerk', 'manager') - case insensitive comparison is done
Select * from Employee where DateOfBirth between '12-10-1975' and '12-10
1995'
Select * from Employee where BasicSalary not between 10000 and 20000
Select * from Employee where BasicSalary is null
Select * from Employee where BasicSalary is not null
Select * From Employee where BasicSalary > SOME (Select BasicSalary from
Employee where DeptId=2)
Select * From Employee where BasicSalary > ALL (Select BasicSalary from Employee where DeptId=2)

Wild card	Matches
%	Represents a set of characters
_	Represents any one character
[ ]	Any single character within the specified range
[^]	Any single character not within the specified range

Select * from Employee where EmpName like 'B%'
Select * from Employee where EmpName like '___'
Select * from Employee where EmpName like 'B__'
Select * from Employee where EmpName like '[a-c]%'
Select * from Employee where EmpName like '[^a-c]%'
Select * from Employee where EmpName like 'A[a-c]%'
Select * from Employee where EmpName like '%\_%' escape '\'

Aggregate Functions

These functions ignore NULL values

Select AVG(BasicSalary), Count(*), MAX(BasicSalary), MIN(BasicSalary), SUM(BasicSalary) From Employee

Compute and Compute by

Select * From Employee Compute Sum(BasicSalary), SUM(Allowances)
Select EmpId, EmpName, Location, DeptId, BasicSalary From Employee order by Location, DeptId
Compute Sum(BasicSalary) by Location, DeptId
Compute Sum(BasicSalary) by Location

To Create another table with data from existing table

EXEC sp_dboption 'ForDemos', 'select into/bulkcopy', 'true'
Select * INTO Managers From Employee where Designation='Manager'
EXEC sp_dboption 'ForDemos', 'select into/bulkcopy', 'false'
SET IDENTITY_INSERT ON
Insert into Managers Select * From Employee
--The above statement works only if the Mangers table is already existing.

Correlated Sub Queries

Select * From Employee Where EmpSalary <(Select Max(EmpSalary) from Employee)

Group By

Every Column in the select list must be either in group by or must be an aggregate function.
Select DeptID, Sum(BasicSalary) From Employee Group By Deptid
Select DeptID, Max(BasicSalary) From Employee Group By DeptID
Select DeptID, Max(BasicSalary) From Employee where allowances > 500 Group By ALL DeptID

Note: ALL is meaningful only when the SELECT statement also includes a WHERE clause.
Select DeptID, Count(*) as EmpCount From Employee Group By ALL DeptID Having Count(*) > 2

Note: With HAVING clause condition can have aggregate function
Select SubString(EmpName,1,1) as FirstCharName, Count(*) From Employee Group by SubString(EmpName,1,1)

Select DeptID, AVG(BasicSalary) as AverageSalary, SUM(BasicSalary) as TotalSalary From Employee Where BasicSalary > 10000 Group By DeptID Having AVG(BasicSalary) >= 30000

Note: Having is applied to aggregated value for that group and where is applied to each row .

Select Location, DeptId, SUM(BasicSalary) as TotalDeptSalary From Employee Group By Location, DeptId Compute Count(DeptId)

Select Location, DeptId, SUM(BasicSalary) as TotalDeptSalary From Employee Group By Location, DeptId order by Location Compute Count(DeptId) By Location

CUBE

Select Location, DeptId, Sum(BasicSalary) From Employee Group by Location, DeptId with CUBE
Select DeptId, Location, Sum(BasicSalary) From Employee Group by DeptId, Location with CUBE
Select CASE WHEN (GROUPING(Location) = 1) THEN 'ALL'
ELSE ISNULL(Location, 'UNKNOWN')END AS Location,
CASE WHEN (GROUPING(DeptId) = 1) THEN 0
ELSE ISNULL(DeptId, -1)END AS DeptId, Sum(BasicSalary) From Employee Group by Location,DeptId with

CUBE

Note: If the row is added because of CUBE then Grouping(<ColName>) returns 1 else return 0

ROLLUP

Try the same examples as above and replace CUBE with ROLLUP and observe the difference

UNION / INTERSECT / EXCEPT

Select EmpName,BasicSalary from Employee where BasicSalary > 20000

UNION ALL

Select EmpName,BasicSalary from Employee where BasicSalary between 5000 and 42000
Select EmpName,BasicSalary from Employee where BasicSalary > 20000

INTERSECT

Select EmpName,BasicSalary from Employee where BasicSalary between 5000 and 42000
Select EmpName,BasicSalary from Employee where BasicSalary between 5000 and 42000

EXCEPT

Select EmpName,BasicSalary from Employee where BasicSalary > 20000

Note: All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.

Working with CONSTRAINTS

A Constraint is a check or a rule that is applied on the data in the table.

Types of Constraint

Not Null
Unique
Primary Key
Default
Check
Foreign key

1. NOT NULL CONSTRAINT

When a table's column is applied with the Not Null constraint then that column will not accept any null value.
Syntax: Create table <table name>(column1 datatype not null,column2 datatype not null)

2. UNIQUE CONSTRAINT

A unique key ensures that no two rows have the same value in a column or set of columns.

Note: It allows one row in the specified column to contain a Null.

Column level:

Syntax

Create table <table name> (column1 datatype1 Unique, column2 datatype2 Unique)
Create table <table name> (column1 datatype1 Constraint <constraint name> Unique)

Ex:

Create table Department (DeptId int UNIQUE, DeptName varchar(20))
Create table Department (DeptId int constraint UQ_DeptId UNIQUE, DeptName varchar(20))

Table level:

Create table <table name> (column1 datatype1, column2 datatype2, UNIQUE(colname))
Create table <table name> (column1 datatype1, Constraint <constraint name> UNIQUE (colname))

Ex:

Create table Department (DeptId int, DeptName varchar (20), UNIQUE (DeptId))
Create table Department (DeptId int, DeptName varchar (20), constraint UQ_DeptId UNIQUE(DeptId))

Composite Unique Key

Syntax:

Create table <table name> (column1 datatype1, column2 datatype2, Unique (column1, column2))
Create table <table name> (column1 datatype1, column2 datatype2, Constraint <constraint name> Unique (column1, column2))

Ex:

Create table Department (DeptId int, DeptName varchar (20), unique (DeptId, DeptName))
Create table Department (DeptId int, DeptName varchar (20), constraint UQ_DeptId_DeptName unique (DeptId, DeptName))

3. PRIMARY KEY CONSTRAINT

Primary key constraints are similar to unique constraints except that they do not permit the associated column to contain a null.

Column level:

Create table <table name> (column1 datatype1 Primary key, column2 datatype2)
Create table <table name> (column1 datatype1 Constraint <constraint name> Primary key, column2 datatype2)

Ex:

Create table Department (DeptId int primary key, DeptName varchar (20))
Create table Department (DeptId int constraint PK_DeptId primary key, DeptName varchar (20))

Table level:

Create table <table name> (column1 datatype1, column2 datatype2, Primary key (column))
Create table <table name> (column1 datatype1, column2 datatype2, Constraint <constraint name> Primary key (column name))

Ex:

Create table Department (DeptId int, DeptName varchar (20), Primary key (DeptId))
Create table Department (DeptId int, DeptName varchar (20), constraint PK_DeptId primary key (DeptId))

Composite Primary Key

Syntax:

Create table <table name> (column1 datatype1, column2 datatype2, Primary key (column1, column2))
Create table <table name> (column1 datatype1, column2 datatype2, Constraint <constraint name> Primary key (column1, column2))

Ex:

Create table Department (DeptId int, DeptName varchar (20), primary key (DeptId, DeptName))
Create table Department (DeptId int, DeptName varchar (20), constraint PK_DeptId_DeptName primary key (DeptId, DeptName))

4. FOREIGN KEY CONSTRAINT

It is used to establish a parent / child or master / dependent relationship between the tables. Foreign key columns of the child table is always linked to either primary key or unique column of the parent table. It can be used only when:

The referenced table has a unique or primary key constraint defined on the appropriate column
Data types of the referencing table columns exactly match the data types of the referenced table columns.

Purpose of Creating ForeignKeyConstraint

To make the column of the child table dependent upon a column of the parent table. Ex: The DeptId of Employee table cannot have a value which is not present in DeptId of Department table.
If the row in the parent table is deleted, it should either delete all the dependent rows of the child table or set the foreignkey field of the child rows to NULL. By default the row in the parent table cannot be deleted if it has dependent rows in the child table.

To Add Foreign Key

Step 1: Create tables

Department(DeptId,DeptName) - DeptId is Primary Key / Identity Column
Emp(EmpId,EmpName,EmpSalary,DeptId) - EmpId is Primary Key / Identity Column

Step 2: Table Designer -> RelationShips -> Add -> Expand Tables and Columns Specification -> Click->

Primary Key Table = Department, Foreign Key Table = Employee -> Select "DeptId" under both the tables -> OK

Step 3: Table Designer -> RelationShips -> Add -> Expand Insert and Update Specification -> Set Delete Rule / Update Rule

Syntax:

Create table <table name> (column1 data type, column2 data type, constraint <constraint name> references <table name> (column name) on delete [no action | cascade | set null] on update [no action | cascade | set null)

Ex:
CREATE TABLE Employee (EmpId int primary key, EmpName varchar (20),
DeptId int constraint FK_DeptId references Department (DeptId) on delete no action on update cascade)
ALTER TABLE Employee ADD CONSTRAINT FK_Department_Employee FOREIGN KEY(DeptId) REFERENCES Department(DeptId) ON UPDATE NO ACTION ON DELETE SET NULL

5. DEFAULT CONSTRAINT

A default value can be specified for a column using the default constraint. When a user does not enter a value for the column SQL Server inserts the default value automatically.
Syntax: Create table <table name> (column data type default ['value' | null])

6. CHECK CONSTRAINT

The check constraint is used to validate simple conditions on columns while data is being updated or inserted into the table.

Syntax: Create table <table name> (column data type Check (condition))

ALTER TABLE Employee ADD CONSTRAINT CK_Employee CHECK (([BasicSalary]>(1000)))
ALTER TABLE Employee ADD CONSTRAINT CK_Employee_1 CHECK (BasicSalary > Allowances)

Assignment:

Category(PKCategoryID, CategoryName, Description, IsActive, FKCategoryID)
Product(PKProductID, ProductName,QuantityInStock,Price)
MMCategoryProduct(FKCategoryID,FKProductID)
Customer(PKCustomerID, CustomerName, Address, OtherInfo)
Order (PKOrderID, FKCustomerID, OrderDate)
OrderDetail(PKOrderDetailsID,FKOrderID,FKProductId,Quantity)

JOINS

Join conditions can be specified in either the FROM or WHERE clauses; specifying them in the FROM clause is recommended. WHERE and HAVING clauses can also contain search conditions to further filter the rows selected by the join conditions.

Joins can be categorized as:

Cross joins

Cross joins return all rows from the left table; each row from the left table is combined with all rows from the right table. Cross joins are also called Cartesian Products.
This is generally useful when the tables joining don't have any relationship between them (no common column).

Example: Assuming that we have two independent tables: Student and Subject, we can use cross join to get all subjects for every student - Select * From Student, Subject

Inner joins

Inner joins use a comparison operator to match rows from two tables based on the values in common columns from each table.

Outer joins

Outer joins can be a left, a right, or full outer join.

LEFT JOIN or LEFT OUTER JOIN
The result set of a left outer join includes all the rows from the left table specified in the LEFT OUTER clause, not just the ones in which the joined columns match. When a row in the left table has no matching rows in the right table, the associated result set row contains null values for all select list columns coming from the right table.
RIGHT JOIN or RIGHT OUTER JOIN.
A right outer join is the reverse of a left outer join. All rows from the right table are returned. Null values are returned for the left table any time a right table row has no matching row in the left table.
FULL JOIN or FULL OUTER JOIN.
A full outer join returns all rows in both the left and right tables. Any time a row has no match in the other table, the select list columns from the other table contain null values. When there is a match between the tables, the entire result set row contains data values from the base tables.

Examples:

To retrieve only the information about those employees who are assigned to a department.
Select EmpID,EmpName,DeptName From Employee e INNER JOIN Department d on e.DeptID = d.DeptID
Retrieve only the information about departments to which atleast one employee is assigned
Select distinct d.DeptId, DeptName from Department d INNER JOIN Employee e on e.DeptID = d.DeptID
Retrieve information about all Employees irrespective of department assigned to them or not
Select EmpID,EmpName,DeptName From Employee e LEFT OUTER JOIN Department d on e.DeptID = d.DeptID
Select EmpID,EmpName,DeptName From Department d RIGHT OUTER JOIN Employee e on e.DeptID = d.DeptID
Retrieve information about all Department irrespective of employees assigned to them or not
Select EmpID,EmpName,DeptName From Employee e RIGHT OUTER JOIN Department d on e.DeptID = d.DeptID
Get the DeptName and Number of Employees in that department
Select Count(EmpName) as EmpCount, DeptName From Employee e RIGHT OUTER JOIN Department d on e.DeptID = d.DeptID GROUP BY d.DeptName
Retrive all employees and all departments
Select * From Employee e FULL OUTER JOIN Department d on e.DeptID = d.DeptID

VIEWS

A SQL View is a virtual table, which is based on a SQL SELECT query. Essentially a view is very close to a real database table (it has columns and rows just like a regular table), except for the fact that the real tables store data, while the views don't. The view's data is generated dynamically when the view is referenced. A view references one or more existing database tables or other views.

Advantages of Views

Views provide a security mechanism by subsetting the data by rows (All Active Customers, all customers in a certain state) and by columns (Payroll fields not shown in the Employee Phone List View).
Views can simplify complex queries into a single reference. Complex Join operations that can make a normalized database design of several tables into a single row in the result set of the view. This is great for reporting tools like Crystal and Cognos.
Views give us aggregation capabilities (Min, Max, Count, Sum) where the data is not stored but calculated.
Views can create other calculated fields based on values in the real underlying tables.
Views can hide the complexity of partitioned data (Sales from 1998 are in the 1998 table, Sales from 1999 are in the 1999 table, Sales from 2000 are in the Current Table) .
Views can be updateable in certain situations
Views do not incur overhead of additional permanent storage.

Creating a View

Create View EmpView
WITH ENCRYPTION | SCHEMABINDING
SELECT EmpName, Salary = BasicSalary+Allowances-Deductions FROM Employee
WITH CHECK OPTION

Note: Views cannot include ORDER BY clause and Cannot include the INTO keyword

If "with Encryption" is used - View cannot be modified or viewed only result can be seen
If WITH SCHEMABINDING clause is used then the base table cannot be dropped or modified in a way that would affect the view
If WITH CHECK OPTION clause is then row cannot be updated through a view if it would no longer be included in the view.

Modifying Data Through Views (Updatable Views)

Cannot modify views with more than one base tables.
The select list cant include a DISTINCT or TOP clause or an aggregate function or a calculated value.
The select statement cant include a GROUP BY or HAVING clause or UNION operator.
Can cause errors if they affect columns that are not referenced in the View
If the WITH CHECK OPTION has been specified, makes sure that inserted or updated row meets the select condition.

CREATE TABLE Invoice ( InvoiceNumber int NOT NULL IDENTITY (1, 1),InvoiceDate datetime NOT NULL,
InvoiceTotal money NULL,
Vendor varchar(50) NULL,
Buyer varchar(50) NULL,
Comment nchar(10) NULL )

INSERT INTO Invoice(InvoiceDate,InvoiceTotal,Vendor,Buyer,Comment)
VALUES (1/1/2009,10000,'IBM','Deccansoft','Laptop')
INSERT INTO Invoice(InvoiceDate,InvoiceTotal,Vendor,Buyer,Comment)
VALUES (1/5/2009,30000,'HP','Deccansoft','Laptop')
INSERT INTO Invoice(InvoiceDate,InvoiceTotal,Vendor,Buyer,Comment)
VALUES (1/5/2009,30000,'IBM','Deccansoft','Desktop')
INSERT INTO Invoice(InvoiceDate,InvoiceTotal,Vendor,Buyer,Comment)
VALUES (1/7/2009,30000,'HP','Deccansoft','Desktop')Create View IBM_Invoice as Select * from Invoice where Vendor='IBM'INSERT INTO IBM_Invoice(InvoiceDate,InvoiceTotal,Vendor,Buyer,Comment)VALUES (1/9/2009,30000,'HP','Deccansoft','Laptop') - Doesn't give error
Alter View IBM_Invoice as Select * from Invoice where Vendor='IBM' WITH CHECK OPTION
INSERT INTO IBM_Invoice(InvoiceDate,InvoiceTotal,Vendor,Buyer,Comment)VALUES (1/9/2009,30000,'HP','Deccansoft','Laptop') - Gives error
INSERT INTO IBM_Invoice(InvoiceTotal,Vendor,Buyer,Comment)
VALUES (30000,'IBM','Deccansoft','Laptop') -- Error because InvoiceDate is required field

SQL Server Catalog Views

sys.tables,
sys.views,
sys.columns,
sys.key_constraints,
sys.foreign_keys,
sys.foreign_key_columns,
sys.objects

INDEXES

There are only two ways in which SQL Server retrieves the data you request:

Using a table scan
Using an index

When a SQL Server has no index to use for searching, the result is similar to the reader who looks at every page in a book to find a word: the SQL engine needs to visit every row in a table. In database terminology we call this behavior a table scan, or just scan.

Indexes in databases are very similar to indexes in libraries. Indexes allow locating information within a database quickly, much like they do in libraries. If all books in a library are indexed alphabetically then you don't need to browse the whole library to find a particular book. Instead you'll simply get the first letter from the book title and you'll find this letter's section in the library starting your search from there, which will narrow down your search significantly.

An Index can be created on a single column or a combination of columns in a database table. A table index is a database structure that arranges the values of one or more columns in a database table in specific order. The table index has pointers to the values stored in a specified column or combination of columns of the table. These pointers are ordered depending on the sort order specified in the index.

There are some general rules which describe when to use indexes.

When dealing with relatively small tables, indexes do not improve performance. In general indexes improve performance when they are created on fields used in table joins.
Use indexes when most of your database queries retrieve relatively small datasets, because if your queries retrieve most of the data most of the time, the indexes will actually slow the data retrieval.
Use indexes for columns that have many different values (there are not many repeated values within the column).
Although indexes improve search performance, they slow the updates, and this might be something worth considering.

Types of Index:

Clustered Index
Non - Clustered

On Heap
On a Clustered Index

CREATE CLUSTERED INDEX IDX_SupplierID ON Products(SupplierID)

A clustered table is any table which has a clustered index on it. A table with clustered index means data is physically stored in a designated order and individual rows are uniquely identified through the use of a cluster key i.e the column that defines the clustered key.

What makes a clustered index special is that the leaf level of a clustered index is the actual data; that is, the data is re-sorted to be stored in the same physical order that the index sort criteria state. This means that, once you get to the leaf level of the index, you're done; you're at the data.

As a general rule of thumb, every table should have a clustered index. If you create only one index for a table, use a clustered index. Not only is a clustered index more efficient than other indexes for retrieval operations, a clustered index also helps the database efficiently manage the space required to store the table. In SQL Server, creating a primary key constraint will automatically create a clustered index (if none exists) using the primary key column as the index key.

Sometimes it is better to use a unique nonclustered index on the primary key column, and place the clustered index on a column used by more queries. For example, if the majority of searches are for the price of a product instead of the primary key of a product, the clustered index could be more effective if used on the price field. A clustered index can also be a UNIQUE index.

A Disadvantage to Clustered Indexes

If we update a record and change the value of an indexed column in a clustered index, the database might need to move the entire row into a new position to keep the rows in sorted order. This behavior essentially turns an update query into a DELETE followed by an INSERT, with an obvious decrease in performance. A table's clustered index can often be found on the primary key or a foreign key column, because key values generally do not change once a record is inserted into the database.

Non-clustered indexes on a heap work very similarly to clustered indexes in most ways. They do, however, have a few notable differences:

The leaf level is not the data-instead, it is the level at which you are able to obtain a pointer to that data.

This pointer comes in the form of the RID, which, as we described earlier, is made up of the extent, page, and row offset for the particular row being pointed to by the index. Even though the leaf level is not the actual data (instead, it has the RID), we only have one more step than with a clustered index; because the RID has the full information on the location of the row, we can go directly to the data.

With non-clustered indexes on a clustered table at the leaf level we find the cluster-key. That is, we find enough information to go and make use of the clustered index.

To View Indexes associated with a given table: EXEC sp_helpindex Customers

Good article on index: http://www.odetocode.com/Articles/70.aspx

Working with Batches

Batches are a group of SQL statements submitted together to the SQL Server for execution.

Batches are of two types:

Anonymous Batches
Named Batches

a) Stored Procedures
b) User Defined Functions
c) Triggers

Anonymous Batches: A collection of SQL statements with out any name is called an anonymous batch.

Variables: Variables are used to store temporary values. Variables are of two types.

Global variables: These are also called public variables which are declared by the server and assigned values typically by the server. The names of global variables begin with two @ symbols.

Variable Name	Returns
@@ Version	Date of the current version of SQL Server
@@ Servername	Name of the SQL Server
@@ Error	0 if last transaction succeeded, else last error no
@@ Max_Connections	Max number of simultaneous connections
@@ Total_Errors	Contains the total number of errors that have occurred while the current SQL Server session is on.
@@Datefirst	What is the first day of the week, we can also change this (Set datefirst 4)
@@Fetch_Status	Status of Last Cursor fetch Operation 0 - Success, 1 - Failed because of enf of rows or beginning of the row 2 - Failed because rows wasn't found.
@@Identity	Last set identity column value on current connection otherwise NULL
Ident_Current('tablename')	Same as ##IDENTITY but return for a specified table
@@NestLevel	Current nesting levels for Stored Procedures i.e one calling another.
@@RowCount	No of rows affected by last statement.

Local variable: These are also called user variables which are declared using a single @.

Syntax: Declare @ <variable name> [ As] <data type>

Set @<variable name> = <value>

Ex: Declare @a as int

Set @a = 10

Keywords:

DECLARE, IF…ELSE, BEGIN…END, WHILE, BREAK, CONTINUE, GOTO, RETRUN, PRINT, TRY..CATCH

· DECLARE is used for declaring variables

· Variables that's define with standard datatype contains single value and are called as SCALAR variable

· TABLE variables are used to store entire result set.

· The name of variable must always begin with "@"

· We can set the value of variable using SET or alternatively use SELECT to assign value to many variables.

Declare @a as int,@b as int,@c as int

Set @a=10

Set @b=10

Set @c = @a+@b

print @c

Declare @grade varchar(20)

Set @grade='E'

If (@grade='E') Print 'Excellent'

Else If (@grade='G') Print 'Good'

Else Print 'No such Grades'

Declare @count int

Set @count=0

While (@count <= 10)

Begin

Set @count = @count +1

if (@count = 4) Continue

if (@count = 7) Break

Print 'Deccansoft ' + convert(varchar,@Count)

End

Declare @count int

Set @count=1

Restart:

Print 'Deccansoft'

Set @count = @count + 1

If (@count <= 10)

GOTO Restart

To work with TABLE Variables

DECLARE @BigVendors table (VendorID int IDENTITY(1,1), VendorName varchar(50))
Insert @BigVendors SELECT Vendor From Invoice where InvoiceTotal > 30000
Select * From @BigVendors

Temporary Tables

They exist for the duration of the database session in which its created. If you create a temporary table in the Management Studio Query editor it exits as long as the query editor is open and because of this we can refer to the table from more than one script.

These tables are stored in the tempdb system database and

can be dropped using the DROP TABLE statement.

A Local Temporary table begins with "#" and are visible with in the current session.

A Global Temporary table begins with "##" and are visible to all sessions.

Select * INTO #TempManagers from Employee where Designation='Manager'
Select * From #TempManagers
Drop table #TempManagers

To check for existence of a database object:
DB_ID('databasename') -- to check of database is existing
OBJECT_ID('objectname') - to check if table, views, stored procedure etc…are exiting

If (OBJECT_ID('Employee') IS NULL)
Print('Employee table is not exiting')
Else
Print('Employee table is exiting')

ERROR HANDLING:

BEGIN TRY

END TRY

BEGIN CATCH

END CATCH

BEGIN TRY

INSERT INTO Invoice(InvoiceTotal,Vendor,Buyer,Comment)

VALUES (10000,'IBM','Deccansoft','Laptop')

PRINT 'SUCCESS'

END TRY

BEGIN CATCH

PRINT 'Failed'

PRINT 'Number: ' + CONVERT(VARCHAR, ERROR_NUMBER())

PRINT 'Message: ' + ERROR_MESSAGE()

END CATCH

TO EXECUTE DYNAMIC SQL

Declare @tablename varchar(50)
set @tablename = 'Employee'
EXEC ('Select * from ' + @tablename)

Stored Procedures

A procedure is a pre-compiled collection of T-Sql statements processed as a unit in the backend upon request from a front-end.

Advantages of Stored Procedures

Precompiled execution. SQL Server compiles each stored procedure once and then reutilizes the execution plan. This results in tremendous performance boosts when stored procedures are called repeatedly.
Reduced client/server traffic. If network bandwidth is a concern in your environment, you'll be happy to learn that stored procedures can reduce long SQL queries to a single line that is transmitted over the wire.
Efficient reuse of code and programming abstraction. Stored procedures can be used by multiple users and client programs. If you utilize them in a planned manner, you'll find the development cycle takes less time.
Enhanced security controls. You can grant users permission to execute a stored procedure independently of underlying table permissions

Syntax: CREATE PROCEDURE procedure_name
[ { @parameter_name} datatype [= default_value] [OUTPUT]]
[ { WITH [RECOMPILE | ENCRYPTION } ]
AS
[BEGIN]
statements
[END]

Note: Parameters in SP can be provided with a default value (Optional parameters)
Note: If a SP doesn't have parameters, we should not put brackets after the procedure name.

CREATE PROCEDURE GetAllEmp

Select * from Employee

Return

To Execute:

EXECUTE GetAllEmp (or)

EXEC GetAllEmp

CREATE PROCEDURE AddNumbers(@a int,@b int=10,@c int=0) As

Return @a + @b + @c

To Execute:

Declare @r1 int

EXEC @r1 = AddNumbers 10,DEFAULT,20

EXEC @r1 = AddNumbers @a=10,@c=1

Print 'Sum of two numbers is ' + convert(varchar,@r1)

CREATE PROCEDURE InsertEmp (@EmpId int OUTPUT,@EmpName varchar(20),@EmpSalary decimal)
As
Insert into Employee values(@EmpName,@EmpSalary)
Select @EmpId=@@Identity
Return

To Execute:
Declare @Id int
execute InsertEmp @Id output,'AAA',2000
Print 'Employee number is ' + convert(varchar,@No)

Note: If a parameter is declared as "output" it can be also used as "input" parameter for the SP.

Stored Procedures can raise errors to the front end application:

RAISEERROR message, severity, state
Severity must be in the range of 11 to 19.
Eg: RAISEERROR ('Some Message Here'. 11,1)

Predefined Stored Procedures

Ex: SP_HELPDB, SP_HELP, SP_RENAME, SP_RENAMEDB , SP_TABLES, SP_DATABASES,
SP_COLUMNS <TableName>

EXEC SP_HELPTEXT sp_RENAME
EXEC SP_RENAME 'Employee', 'Emp'
EXEC SP_RENAME 'Employee.BasicSalary', 'Salary', 'COLUMN'

User Defined Functions (UDF)

Types of UDF

Scalar Valued Function: Returns a single value of any T-SQL datatype..

CREATE FUNCTION function_name (<parameter>)
RETURNS scalar_return_data_type
WITH ENCRYPTION, SCHEMABINDING
[AS]
BEGIN
function_body
RETURN scalar_expression
END

Inline Table-valued Functions: Returns a table that's based on a single SELECT statement
CREATE FUNCTION function_name ( <parameter> )
RETURNS TABLE
[ AS ]
RETURN select-stmt

Multi-statement Table-valued Functions: Returns a table that's based on multiple statements
CREATE FUNCTION function_name (<parameter>)
RETURNS @return_variable TABLE < table_type_definition >
[ AS ]
BEGIN
function_body
RETURN
END

Comparison between Stored Procedures and Functions
Stored Procedures	Functions
May or may not return a value	Has to return a value
May return more than one parameter	Can return only one parameter
Supports DDL, DML & DCL	Does not support
Accepts large no of parameter 2100	Accepts small number of parameters 1024
Should be executed with EXEC	Should be executed with Select
They can call other Stored Procedures & UDF	UDF can not call Stored Procedures

Examples of UDF

Examples of Scalar functions

Examples of Inline table valued functions

Create Function Addition(@a int,@b int) Returns int

Begin

Declare @sum int

Set @sum =@a + @b

return @sum

End

To Execute: Select dbo.Addition(10,20)

Create Function GetEmployees() Returns TABLE

return (Select * from Employee)

To Execute:

Select * from dbo.GetEmployees()

CREATE FUNCTION GetTotalSalaryOfDepartment

(@DeptId int) returns Money

BEGIN

RETURN(Select Sum(BasicSalary+Allowances-Deductions)

From Employee where DeptId=@DeptId)

END

To Execute:

Select dbo.GetTotalSalaryOfDepartment(2)

CREATE Function GetDeptsWithTotalSalary() returns TABLE

RETURN (Select DeptId, Sum(BasicSalary+Allowances-Deductions) as TotalSalary From Employee group by DeptId)

To Execute:

Select * From dbo. GetDeptsWithTotalSalary()

Note: Unlike other database objects, we must specify the name of the schema (dbo) when invoking the UDF.

Note: IF a table valued function is used in a join operation, we will want to assign a correlation name to it as shown below:

Select * From Department
JOIN dbo.GetDeptsWithTotalSalary() as DeptSalary
ON Department.DeptId = DeptSalary.DeptId

Example of Multi-statement Table-valued Functions

CREATE Function GetEmpsWithBonus() returns @OutTable TABLE
(EmpId int,EmpName varchar(50),Salary money, Bonus money)
AS
BEGIN
Insert into @OutTable
Select EmpId, EmpName, BasicSalary, NULL From Employee
WHILE((Select Sum(Salary) From @OutTable where Bonus is NULL) > 1000)
update @OutTable set Bonus = Salary * 0.1
RETURN
END
To Execute:
Select * From dbo.GetEmpsWithBonus()

TRIGGERS

CREATE TRIGGER trigger_name
ON { table | view }
[ WITH ENCRYPTION ]
{
{ { FOR | AFTER | INSTEAD OF } { [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE
] }
AS
[ { IF UPDATE ( column )
[ { AND | OR } UPDATE ( column ) ] } ]
sql_statement [ ...n ]
}
}

FOR and AFTER are same
INSTEAD OF is used as an alternative to original statement
UPDATE (column) is true if the column is modified and we can use the same to know if Insert is done or update is done
INSERTED table gives NEW data
DELETED table gives OLD data

Example: Add EmpCount column to Department table - ALTER TABLE dbo.Department ADD EmpCount int NULL

CREATE TRIGGER Employee_INSERT_UPDATE_DELETE
ON Employee
AFTER INSERT, UPDATE, DELETE
AS
if ((Select Count(*) From INSERTED) = 0) --True for Delete
BEGIN
Update Department set EmpCount = EmpCount - 1
where DeptID in (Select DeptId from DELETED)
Print 'Deleted'
END

else
BEGIN
if (UPDATE (DeptId) -- True if DeptId is modified
&& (Select count(*) From DELETED) <> 0) --True for Update and false for
Insert
BEGIN
Update Department set EmpCount = EmpCount - 1
where DeptID = (Select DeptId from DELETED)
Print 'Updated'
END
Update Department set EmpCount = EmpCount + 1
where DeptId = (Select DeptId from INSERTED)
Print 'Inserted / Updated'
END
ALTER TRIGGER DEPARTMENT_UPDATE ON Department AFTER UPDATE
AS
BEGIN
DECLARE @DeptId int
Set @DeptID = (Select DeptID from Inserted)
if (UPDATE(EmpCount))
BEGIN
If ((Select EmpCount from Department where DeptId = @DeptID) = 0)
BEGIN
ROLLBACK TRAN
RAISERROR ('Department cannot exists with employees',11,1)
END
END
END

INSTEAD OF Example

Create Trigger DepartmentView_INSERT
on DepartmentView
INSTEAD OF INSERT
AS
Declare @DeptName varchar(50)
if ((Select count(*) from Inserted) > 1)
BEGIN
RAISERROR ('Only One row must be inserted',1,1)
ROLLBACK TRAN
END
Select @DeptName = DeptName from Inserted
Insert Into Department(DeptName,DateOfFormation,EmpCount)
Values(@DeptName,getdate(),0)

TRANSACTIONS

BEGIN TRAN
SAVE TRAN <savepointname>
COMMIT TRAN
ROLLBACK TRAN <savepointname>

Transactions can be nested and using @@TRANCOUNT we can find the level depth of the transaction.

CURSORS

A cursor is a database object that points to a result set.
Data can be held in a memory-resident state called a cursor.
It is like a table, as it has rows and columns of data, but that's where the similarity ends. There are no indexes, for example. A cursor is used to build up a set of data for processing one row at a time.
By default, SQL statements work with all the rows in a resultset. If you need to work with data one row at a time you can use a CURSOR.
They are most often used by application programs that work with the data in a database.
Transact SQL cursors are similar to API cursors.

Types of SQL Server Cursors	Scrollable	Sensitive to db changes
Dynamic	Yes	Yes
Keyset	Yes	Yes, Not for Insert
Static	Yes	No
Forward-only	No	Yes
Forward only keyset*	No	Yes, Not for delete
Forwardonly static *	No	No
Fast Forward.*	No	No

*Not supported by the front end API

Cursor Processing Functions: DECLARE CURSOR, OPEN, FETCH, CLOSE, DEALLOCATE

DECLARE cursor_name CURSOR
[ LOCAL | GLOBAL ]
[ FORWARD_ONLY | SCROLL ]
[ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ]
[ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ] --Concurrency options for update and delete operations.
FOR select_statement
[ FOR UPDATE [ OF column_name [ ,...n ] ] ] --If FOR UPDATE clause is not included then any column can be updated.

FETCH
NEXT | PRIOR | FIRST | LAST | ABSOLUTE { n | @nvar } | RELATIVE { n | @nvar }]
FROM [ GLOBAL ] cursor_name
INTO @variable_name [ ,...n ]

@@FECTCH = 0 Success, -1 EOF, -2 Row was deleted

@@CURSOR_ROWS = n (Number of rows), 0 (Cursor not open or no rows are qualified or -1 Dynamic Cursor

Example:

DECLARE @Id int, @name varchar(50),@designation varchar(50)

DECLARE Emp_Cursor CURSOR

STATIC FOR

SELECT EmpId,EmpName,Designation From Employee

OPEN Emp_Cursor

Print 'No of rows feched by cursor: ' + Convert(varchar,@@CURSOR_ROWS)

FETCH NEXT FROM Emp_Cursor INTO @Id,@name,@designation

WHILE @@FETCH_STATUS = 0

BEGIN

IF (@designation IS NOT NULL)

BEGIN

Print Convert(varchar,@id) + ' ' + @name + ' ' + @Designation

END

FETCH NEXT FROM Emp_Cursor INTO @Id,@name,@designation

END

CLOSE Emp_Cursor

DEALLOCATE Emp_Cursor

PRE DEFINED FUNCTIONS