Category Archives: SQL

SARGable Query

Just by adding an index to a table doesn’t mean you get performance improvement.
To make query sargable by not altering the column side of Where predicate or join clause so that SQL Server can utilize the index on that column whithout having to do any extra work.
Non-Sargable Query example on dbo.Invetory table with 100 records:

</pre>
Select Name FROM dbo.Inventory WHERE CONVERT(CHAR(10), CreateDate, 121) = '2018-06-28' 

Here Sql Server will perform Index Scan on each row

Sargable Query example:

SELECT Name
FROM dbo.Inventory
WHERE CreateDate = CAST('2018-06-28' as datetime2)

Here Sql Server will perform Index Seek.

Reference
https://blog.bertwagner.com/how-to-search-and-destroy-non-sargable-queries-on-your-server-ff9f57c7268e

Advertisements

Leave a comment

Filed under SQL

Referential Integrity

Referential Integrity is a database concept which ensures that relationship between tables remain constant.

When one table has a foreign key to another table, the concept of referential integrity states that you may not add a record to the table that contains the foreign key unless there is a corresponding record in the linked table.
There are 3 rules:
1) Insert
2) Cascading delete
3) Cascading update

Reference
http://www.programmerinterview.com/index.php/database-sql/what-is-referential-integrity/

Leave a comment

Filed under SQL

Can foreign Key be NULL and Duplicate?

Yes, Foreign Key can be NULL as well as Duplicate (duplicate in one to many relationship).

Justification
http://stackoverflow.com/questions/7573590/can-foreign-key-null-and-duplicate

Leave a comment

Filed under SQL

SQL

Referential Inegrity
http://www.programmerinterview.com/index.php/database-sql/what-is-referential-integrity/

Having clause & Where clause
http://www.programmerinterview.com/index.php/database-sql/having-vs-where-clause/

Inner Join vs Outer Join
http://www.programmerinterview.com/index.php/database-sql/inner-vs-outer-joins/

Self Join
http://www.programmerinterview.com/index.php/database-sql/what-is-a-self-join/

Correlated vs Uncorrelated query
http://www.programmerinterview.com/index.php/database-sql/correlated-vs-uncorrelated-subquery/

Index
http://www.programmerinterview.com/index.php/database-sql/what-is-an-index/

SELECT b.Emp_id, b.Emp_name,e.emp_id as managerID, e.emp_name as managerName
FROM Employee b
JOIN Employee e ON b.Emp_ID = e.emp_mgr_id

This is a classic self-join, try the following:

http://blog.sqlauthority.com/2010/07/08/sql-server-the-self-join-inner-join-and-outer-join/

SELECT e.ename, e.empno, m.ename as manager, e.mgr
FROM
emp e, emp m
WHERE e.mgr = m.empno

And if you want to include the president which has no manager then instead of an inner join use an outer join in Oracle syntax:

SELECT e.ename, e.empno, m.ename as manager, e.mgr
FROM
emp e, emp m
WHERE e.mgr = m.empno(+)
Or in ANSI SQL syntax:

SELECT e.ename, e.empno, m.ename as manager, e.mgr
FROM
emp e
LEFT OUTER JOIN emp m
ON e.mgr = m.empno

Leave a comment

Filed under SQL

GROUP BY, Where vs Having

GROUP BY is actually used to group the result-set by using aggregate functions.

Let’s say we have a table Student with columns:

Name Subject Score
Mahesh Math 80
Mahesh Eng 70
Ramesh Eng 70
Somesh Math 65
Ramesh Math 75
Somesh Eng 65
Mahesh Hindi 60
Ramesh Hindi 75

Now to get each student total marks:

SELECT Name, Sum(Score)

FROM Student

GROUP BY Name

Name Score
Mahesh 210
Ramesh 220
Somesh 130

Without GROUP BY,

SELECT Name, Sum(Score)

FROM Student

Name Score
Mahesh 560
Mahesh 560
Ramesh 560
Somesh 560
Ramesh 560
Somesh 560
Mahesh 560
Ramesh 560

WHERE vs HAVING

WHERE HAVING
WHERE is basically used to filter before the Group BY. HAVING is used to filter on GROUP BY result-set.
WHERE can’t be used with aggregate functions. Having is applied with aggregate functions only.
WHERE doesn’t need Group By. HAVING is used with Group By only.

Now for Query to get all students who have appeared in three subject exam:

SELECT Name, Count (Name)

FROM Student

GROUP BY Name

HAVING Count (Name) =3

To get all students who have scored more then 70 & appeared in three subject exam:

SELECT Name, Count(Name)

FROM Student

Where Score>70

GROUP BY Name

HAVING Count(Name)=3

 References:

http://www.programmerinterview.com/index.php/database-sql/having-vs-where-clause/

Leave a comment

Filed under SQL

Nth Highest salary & Nth Lowest salary of employee

There are two approaches for the same, I will show the example with N=2
1 using CTE

Highest
With cteH
{
   Select Emp_Name, Emp_Salary, Row_Number() over (Order By Emp_Salary Desc) as Rn
   From Emp
}

Select Emp_Name, Emp_Salary
From cteH
Where Rn=2

Lowest
With cteL
{
   Select Emp_Name, Emp_Salary, Row_Number() over (Order By Emp_Salary Asec) as Rn
   From Emp
}
Select Emp_Name, Emp_Salary
From cteL
Where Rn=2

2 using Distinct

Highest
Select e1.Emp_Name, e1.Emp_Salary
From Emp e1
Where 2=(Select Count(DISTINCT(e2.Emp_Salary)) From Emp e2
  Where e2.Emp_Salary>=e1.Emp_Salary)

Lowest
Select e1.Emp_Name, e1.Emp_Salary
From Emp e1
Where 2=(Select Count(DISTINCT(e2.Emp_Salary)) From Emp e2
  Where e2.Emp_Salary<=e1.Emp_Salary)

Leave a comment

Filed under SQL

Returning Data

Returning Data from Strored Procedure
There are three common ways to return data from stored procedures: returning result sets (SELECT statements), using output variables and using the RETURN statement.

Result Sets
Like the procedure to return a list of all the people with a given last name

Create Procedure dbo.GetPeopleByLastName(@LastName varcgar(200))
As
Select FirstName, LastName
From Person
Where
LastName=@LastName

Using OUTPUT variables
To return a single value (or a couple of values) you can use output variables.

Create Procedure dbo.GetCountByLastName(@LastName varchar(200), @LNameCount int OUTPUT)
As
Select @LNameCount=  Count(*)
From Person
Where
LastName=@LastName
And how to execute it :
DECLARE @TheCount INT

EXEC dbo.GetCountByLastName 
    @LastName = ‘Alexander’,
    @LastNameCount = @TheCount OUTPUT

Using Return

It only returns a single numeric value. This is most commonly used to return a status result or error code from a procedure.

Create Procedure TestReturn(@IntValue int)
As
RETURN @IntValue
Go

Note that executing a RETURN statement causes a stored procedure to stop executing and return control back to the calling program. This is often used to test for error conditions and stop processing if one is found.

Leave a comment

Filed under SQL