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
As
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
As
return (Select * from Employee)
To Execute:
Select * from dbo.GetEmployees()
|
|
CREATE FUNCTION GetTotalSalaryOfDepartment
(@DeptId int) returns Money
AS
BEGIN
RETURN(Select Sum(BasicSalary+Allowances-Deductions)
From Employee where DeptId=@DeptId)
END
To Execute:
Select dbo.GetTotalSalaryOfDepartment(2) |
CREATE Function GetDeptsWithTotalSalary() returns TABLE
AS
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
![Sql.gif]()