Archive for the ‘SQL’ Category

Select Find Nth Highest Salary Record In Sql Server

1st Method :

SELECT TOP 1 [Salary]
FROM [dbo].[Employee]
) temp
ORDER BY [Salary]

2nd Method :

FROM [dbo].[Employee]
) AS e1
WHERE e1.RowId = N
Categories: SQL Tags:

Database Normalization






The following descriptions include examples.

First Normal Form

  • Eliminate repeating groups in individual tables.
  • Create a separate table for each set of related data.
  • Identify each set of related data with a primary key.



Second Normal Form

  • Relate these tables with a foreign key.


Third Normal Form

  • Eliminate fields that do not depend on the key.





Other Normalization Forms


Normalizing an Example Table

These steps demonstrate the process of normalizing a fictitious student table.

  1. Unnormalized table:
    Student# Advisor Adv-Room Class1 Class2 Class3
    1022 Jones 412 101-07 143-01 159-02
    4123 Smith 216 201-01 211-02 214-01


  2. First Normal Form: No Repeating GroupsTables should have only two dimensions. Since one student has several classes, these classes should be listed in a separate table. Fields Class1, Class2, and Class3 in the above records are indications of design trouble.Spreadsheets often use the third dimension, but tables should not. Another way to look at this problem is with a one-to-many relationship, do not put the one side and the many side in the same table. Instead, create another table in first normal form by eliminating the repeating group (Class#), as shown below:
    Student# Advisor Adv-Room Class#
    1022 Jones 412 101-07
    1022 Jones 412 143-01
    1022 Jones 412 159-02
    4123 Smith 216 201-01
    4123 Smith 216 211-02
    4123 Smith 216 214-01


  3. Second Normal Form: Eliminate Redundant DataNote the multiple Class# values for each Student# value in the above table. Class# is not functionally dependent on Student# (primary key), so this relationship is not in second normal form.The following two tables demonstrate second normal form:Students:
    Student# Advisor Adv-Room
    1022 Jones 412
    4123 Smith 216


    Student# Class#
    1022 101-07
    1022 143-01
    1022 159-02
    4123 201-01
    4123 211-02
    4123 214-01


  4. Third Normal Form: Eliminate Data Not Dependent On KeyIn the last example, Adv-Room (the advisor’s office number) is functionally dependent on the Advisor attribute. The solution is to move that attribute from the Students table to the Faculty table, as shown below:Students:
    Student# Advisor
    1022 Jones
    4123 Smith


    Name Room Dept
    Jones 412 42
    Smith 216 42
Categories: SQL Tags:

Optimize SQL Queries (Theory an Practice)


The reasons to optimize

Time is money and people don’t like to wait so programs are expected to be fast.

In Internet time and client/server programming, it’s even more true because suddenly a lot of people are waiting for the DB to give them an answer which makes response time even longer.


Theory of optimization


Look at the DB Schema and see if it makes sense


What I suggest is to stick to the 3rd normal form except if you are a DBA (which means you know subsequent forms and know what you’re doing). Normalization after the 3rd NF is often done at a later time, not during design.

 Only query what you really need

Filter as much as possible

Your Where Clause is the most important part for optimization.

Select only the fields you need

Never use “Select *” — Specify only the fields you need; it will be faster and will use less bandwidth.

Be careful with joins

Joins are expensive in terms of time. Make sure that you use all the keys that relate the two tables together and don’t join to unused tables — always try to join on indexed fields. The join type is important as well (INNER, OUTER,… ).

Optimize queries and stored procedures (Most Run First)



Add, remove or modify indexes


You especially want to use Indexes on Integers, Booleans, and Numbers. On the other hand, you probably don’t want to use indexes on Blobs, VarChars and Long Strings.


In the Internet world, read-only tables are very common. When a table is read-only, you can add indexes with less negative impact because indexes don’t need to be maintained (or only rarely need maintenance).

Move Queries to Stored Procedures (SP)


Calculations in code (VB, Java, C++, …) are not as fast as SP in most cases.

Remove unneeded Views

Views are a special type of Query — they are not tables. They are logical and not physical so every time you run select * from MyView, you run the query that makes the view and your query on the view.


If you have to filter the View, it’s like running a query on a query — it’s slower.

Tune DB settings

You can tune the DB in many ways. Update statistics used by the optimizer, run optimization options, make the DB read-only, etc… That takes a broader knowledge of the DB you work with and is mostly done by the DBA.

Using Query Analysers



Optimization in Practice


Example 1:



Query : Select * From Employees


Corrected :

Select Name, Salary From Employees Where Dept = R&D–




Example 2 (Sorting):


Select Name, Salary

From Employees

Where Dept = ‘R&D’

Order By Salary

Do you need that Order By Clause? Often, people use Order By in development to make sure returned data are ok; remove it if you don’t need it.


Example 3:


For i = 1 to 2000







Sometimes, the processing is so great that you think it’s better to do it in the code but it’s probably not.


Example 4 (Weak Joins):



Select O.ItemPrice, C.Name

From Orders O, Customers C


Select O.ItemPrice, C.Name

From Orders O, Customers C

Where O.CustomerID = C.CustomerID

In that case, the join was not there at all or was not there on all keys. That would return so many records that your query might take hours. It’s a common mistake for beginners.

Corrected 2:



Select O.ItemPrice, C.Name

From Orders O INNER JOIN Customers C ON O.CustomerID = C.CustomerID

Choose the good join type (INNER, OUTER, LEFT, …).


Example 5 (Weak Filters):


We have two tables — Products (ProductID, DescID, Price) and Description(DescID, LanguageID, Text). There are 100,000 Products and unfortunately we need them all.


We are expecting 100 000 Products (ProductName, Price).

First try:

Select D.Text As ProductName, P.Price

From Products P INNER JOIN Description D On P.DescID = D.DescID

Where D.LangID = 1




Select D.Text As ProductName, P.Price


INNER JOIN Products P On D.DescID = P.DescID


Example 6 (Views):

Create View v_Employees AS

Select * From Employees

Select * From v_Employees

This is just like running Select * From Employees twice.

You should not use the view in that case.


Create View v_R&DEmployees AS

Select Name, Salary From Employees Where Dept = 1

(Dept 1 is R&D).


That would be a possibly good use of views.





SQL Tuning/SQL Optimization Techniques:

1) The sql query becomes faster if you use the actual columns names in SELECT statement instead of than ‘*’.

For Example: Write the query as


Instead of:

SELECT * FROM student_details;


2) HAVING clause is used to filter the rows after all the rows are selected. It is just like a filter. Do not use HAVING clause for any other purposes.
For Example: Write the query as

SELECT subject, count(subject)
FROM student_details
WHERE subject != ‘Science’
AND subject != ‘Maths’
GROUP BY subject;

Instead of:

SELECT subject, count(subject)
FROM student_details
GROUP BY subject
HAVING subject!= ‘Vancouver’ AND subject!= ‘Toronto’;


For Example: Write the query as

FROM employee
FROM employee_details)
AND dept = ‘Electronics’;

Instead of:

FROM employee
WHERE salary = (SELECT MAX(salary) FROM employee_details)
AND age = (SELECT MAX(age) FROM employee_details)
AND emp_dept = ‘Electronics’;


4) Use operator EXISTS, IN and table joins appropriately in your query.
a) Usually IN has the slowest performance.

For Example: Write the query as

Select * from product p
where EXISTS (select * from order_items o
where o.product_id = p.product_id)

Instead of:

Select * from product p
where product_id IN
(select product_id from order_items


5) Use EXISTS instead of DISTINCT when using joins which involves tables having one-to-many relationship.
For Example: Write the query as

SELECT d.dept_id, d.dept
FROM dept d
WHERE EXISTS ( SELECT ‘X’ FROM employee e WHERE e.dept = d.dept);

Instead of:

SELECT DISTINCT d.dept_id, d.dept
FROM dept d,employee e
WHERE e.dept = e.dept;


6) Try to use UNION ALL in place of UNION.
For Example: Write the query as

SELECT id, first_name
FROM student_details_class10
SELECT id, first_name
FROM sports_team;

Instead of:

SELECT id, first_name, subject
FROM student_details_class10
SELECT id, first_name
FROM sports_team;


7) Be careful while using conditions in WHERE clause.
For Example: Write the query as


Instead of:


Write the query as

SELECT id, first_name, age
FROM student_details
WHERE first_name LIKE ‘Chan%’;

Instead of:

SELECT id, first_name, age
FROM student_details
WHERE SUBSTR(first_name,1,3) = ‘Cha’;

Write the query as

SELECT id, first_name, age
FROM student_details
WHERE first_name LIKE NVL ( :name, ‘%’);

Instead of:

SELECT id, first_name, age
FROM student_details
WHERE first_name = NVL ( :name, first_name);

Write the query as

SELECT product_id, product_name
FROM product
WHERE unit_price BETWEEN MAX(unit_price) and MIN(unit_price)

Instead of:

SELECT product_id, product_name
FROM product
WHERE unit_price >= MAX(unit_price)
and unit_price <= MIN(unit_price)

Write the query as

SELECT id, name, salary
FROM employee
WHERE dept = ‘Electronics’
AND location = ‘Bangalore’;

Instead of:

SELECT id, name, salary
FROM employee
WHERE dept || location= ‘ElectronicsBangalore’;

Use non-column expression on one side of the query because it will be processed earlier.

Write the query as

SELECT id, name, salary
FROM employee
WHERE salary < 25000;

Instead of:

SELECT id, name, salary
FROM employee
WHERE salary + 10000 < 35000;

Write the query as

SELECT id, first_name, age
FROM student_details
WHERE age > 10;

Instead of:

SELECT id, first_name, age
FROM student_details
WHERE age NOT = 10;

8) Use DECODE to avoid the scanning of same rows or joining the same table repetitively. DECODE can also be made used in place of GROUP BY or ORDER BY clause.
For Example: Write the query as

SELECT id FROM employee
WHERE name LIKE ‘Ramesh%’
and location = ‘Bangalore’;

Instead of:

SELECT DECODE(location,’Bangalore’,id,NULL) id FROM employee
WHERE name LIKE ‘Ramesh%’;

9) To store large binary objects, first place them in the file system and add the file path in the database.

10) To write queries which provide efficient performance follow the general SQL standard rules.

a) Use single case for all SQL verbs
b) Begin all SQL verbs on a new line
c) Separate all words with a single space
d) Right or left aligning verbs within the initial SQL verb

Categories: SQL Tags:

User-defined function in SQL


— Input dimensions in centimeters.
(@CubeLength decimal(4,1), @CubeWidth decimal(4,1),
@CubeHeight decimal(4,1) )
RETURNS decimal(12,3) — Cubic Centimeters.
RETURN ( @CubeLength * @CubeWidth * @CubeHeight )

A user-defined function that returns a table can also replace stored procedures that return a single result set. The table returned by a user-defined function can be referenced in the FROM clause of a Transact-SQL statement, whereas stored procedures that return result sets cannot. For example, fn_EmployeesInDept is a user-defined function that returns a table and can be invoked by a SELECT statement:

FROM tb_Employees AS E,
dbo.fn_EmployeesInDept(‘shipping’) AS EID
WHERE E.EmployeeID = EID.EmployeeID
This is an example of a statement that creates a function in the Northwind database that will return a table:

CREATE FUNCTION LargeOrderShippers ( @FreightParm money )
RETURNS @OrderShipperTab TABLE
ShipperID int,
ShipperName nvarchar(80),
OrderID int,
ShippedDate datetime,
Freight money
INSERT @OrderShipperTab
SELECT S.ShipperID, S.CompanyName,
O.OrderID, O.ShippedDate, O.Freight
FROM Shippers AS S
INNER JOIN Orders AS O ON (S.ShipperID = O.ShipVia)
WHERE O.Freight > @FreightParm

 Difference between Stored Procedure and Functions



4. Stored Procedure retuns more than one value at a time while funtion returns only one value at a time.

5. We can call the functions in sql statements (select max(sal) from emp). where as sp is not so.

6. Function do not return the images,text whereas sp returns all.

7. Function and sp both can return the values. But function returns 1 value only. Procedure can return multiple values(max. 1024) we can select the fields from function. in the case of procdure we cannot select the fields.

8. Functions MUST return a value, procedures need not be.

9. You can have DML(insert, update, delete) statements in a function. But, you cannot call such a function in a SQL suppose, if u have a function that is updating a table.. you can’t call that function in any sql query.

10. SP can call function but vice-versa not possible.

Categories: SQL Tags:

Clustered and Non-Clustered Index in SQL

1. Introduction

We all know that data entered in the tables are persisted in the physical drive in the form of database files. Think about a table, say Customer (For any leading bank India), that has around 16 million records. When we try to retrieve records for two or three customers based on their customer id, all 16 million records are taken and comparison is made to get a match on the supplied customer ids. Think about how much time that will take if it is a web application and there are 25 to 30 customers that want to access their data through internet. Does the database server do 16 million x 30 searches? The answer is no because all modern databases use the concept of index.

2. What is an Index


In this article, we will see creating the Index.

3. First Create Two Tables

To explain these constraints, we need two tables. First, let us create these tables. Run the below scripts to create the tables. Copy paste the code on the new Query Editor window, then execute it.

CREATE TABLE Student(StudId smallint, StudName varchar(50), Class tinyint);
CREATE TABLE TotalMarks(StudentId smallint, TotalMarks smallint);

Note that there are no constraints at present on these tables. We will add the constraints one by one.

4. Primary Key Constraint

A table column with this constraint is called as the key column for the table. This constraint helps the table to make sure that the value is not repeated and also no null entries. We will mark the StudId column of the Studenttable as primary key. Follow these steps:

  1. Right click the student table and click on the modify button.
  2. From the displayed layout, select the StudId row by clicking the Small Square like button on the left side of the row.
  3. Click on the Set Primary Key toolbar button to set the StudId column as primary key column.


Now this column does not allow null values and duplicate values. You can try inserting values to violate these conditions and see what happens. A table can have only one Primary key. Multiple columns can participate on the primary key column. Then, the uniqueness is considered among all the participant columns by combining their values.

5. Clustered Index

The primary key created for the StudId column will create a clustered index for the Studid column. A table can have only one clustered index on it.

When creating the clustered index, SQL server 2005 reads the Studid column and forms a Binary tree on it. This binary tree information is then stored separately in the disc. Expand the table Student and then expand theIndexes. You will see the following index created for you when the primary key is created:


With the use of the binary tree, now the search for the student based on the studid decreases the number of comparisons to a large amount. Let us assume that you had entered the following data in the table student:





Select * from student where studid = 103;
Select * from student where studid = 107;



  1. Compare 107 vs 103 : Move to right node
  2. Compare 107 vs 106 : Move to right node
  3. Compare 107 vs 107 : Matched, return the record




6. Non Clustered Index

A non-clustered index is useful for columns that have some repeated values. Say for example, AccountTypecolumn of a bank database may have 10 million rows. But, the distinct values of account type may be 10-15. Aclustered index is automatically created when we create the primary key for the table. We need to take care of the creation of the non-clustered index.

Follow the steps below to create a Non-clustered index on our table Student based on the column class.

  1. After expanding the Student table, right click on the Indexes. And click on the New Index.Pic05.jpg
  2. From the displayed dialog, type the index name as shown below and then click on the Add button to select the column(s) that participate in the index. Make sure the Index type is Non-Clustered.Pic06.jpg
  3. In the select column dialog, place a check mark for the column class. This tells that we need a non-clusteredindex for the column Student.Class. You can also combine more than one column to create the Index. Once the column is selected, click on the OK button. You will return the dialog shown above with the selected column marked in blue. Our index has only one column. If you selected more than one column, using the MoveUpand MoveDown button, you can change order of the indexed columns. When you are using the combination of columns, always use the highly repeated column first and more unique columns down in the list. For example, let use assume the correct order for creating the Non-clustered index is: ClassDateOfBirth,PlaceOfBirth.Pic07.jpg
  4. Click on the Index folder on the right side and you will see the non-clustered index based on the column class is created for you.Pic08.jpg

7. How Does a Non-Clustered Index Work?



  2. Next, you noted down the number displayed next to it which is 407, 816, 1200-1220.



1: 100, 104, 105

So here, you can easily get all the records that have value for class = 1. Map this with the Book index example now.

%d bloggers like this: