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

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

SET vs SELECT

SET and SELECT both key words are used to Assign Variables in SQL Server.

SET and SELECT both specifies the columns to be changed and the new values for the columns.
The values in the specified columns are updated with the values specified in the SET and SELECT in all rows that match the WHERE clause search condition.
If no WHERE clause is specified, all rows are updated.

There are some difference based on the Performance, Process like Follows :

1. SET is the ANSI standard for variable assignment, SELECT is not. 

2. SELECT can be used to assign values to more than one variable at a time, Whereas SET allows to assign data to only one variable at a time.

Example :

/* Declaring variables */
DECLARE @Var1 AS int, @Var2 AS int

/* The same can be done using SET, but two SET statements are needed */
SET @Var1 = 1
SET @Var2 = 2  

/* Initializing two variables at once */
SELECT @Var1 = 1, @Var2 = 2

But use SET instead SELECT, for variable initialization, It will throw the following error

SET @Var1 = 1, @Var2 = 2  

Msg 102, Level 15, State 1, Line 10
Incorrect syntax near ‘,’.

3. When assigning from a query if there is no value returned then SET will assign NULL, where SELECT will not make the assignment at all .so the variable will not be changed from it’s previous value.  

Example :

Run it in master Database in SQL Server.

/* Returns NULL */
DECLARE @Title varchar(80)
–SET @Title = ‘Not Found’

SET @Title =
(
SELECT error
FROM SysMessages
WHERE Description = ‘Invalid Description’
)

SELECT @Title
GO

/* Returns the string literal ‘Not Found’ */
DECLARE @Title varchar(80)
SET @Title = ‘Not Found’

SELECT @Title = error
FROM SysMessages
WHERE Description = ‘Invalid Description’

SELECT @Title
GO  

4. Let using a query needs to populate a variable and the Query returns more than one value.
SET will fail with an error in this scenario.
But SELECT will assign one of the returned rows and mask the fact that the query returned more than one row.

As a result, bugs in your the could go unnoticed with SELECT, and this type of bugs is hard to track down too.

Example :

/* Consider the following table with two rows */
SET NOCOUNT ON
CREATE TABLE #Table (i int, j varchar(10))
INSERT INTO #Table (i, j) VALUES (1, ‘Sunday’)
INSERT INTO #Table (i, j) VALUES (1, ‘Monday’)
GO

/* Following SELECT will return two rows, but the variable gets its value from one of those rows, without an error.
you will never know that two rows existed for the condition, WHERE i = 1 */
DECLARE @j varchar(10)
SELECT @j = j FROM #Table WHERE i = 1
SELECT @j
GO

/* If you rewrite the same query, but use SET instead, for variable initialization, you will see the following error */
DECLARE @j varchar(10)
SET @j = (SELECT j FROM #Table WHERE i = 1)
SELECT @j

Msg 512, Level 16, State 1, Line 4
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Based on the above results, when using a query to populate variables, we should always use SET.
If you want to be sure that only one row is returned then only use SELECT, as shown below:

DECLARE @j varchar(10)
SELECT @j = (SELECT j FROM #Table WHERE i = 1)
SELECT @j

5. This very feature of SELECT makes it a winner over SET, when assigning values to multiple variables. A single SELECT statement assigning values to 3 different variables, is much faster than 3 different SET statements assigning values to 3 different variables.
In this scenario, using a SELECT is at least twice as fast, compared to SET.

Leave a comment

Filed under SQL

Temporary Tables VS Table Variables

Temporary Tables VS Table Variables

Temporary tables are like regular tables but stored in the tempdb database. These tables get dropped after they have been used. Temporary tables are of two types,

Local Temporary table – defined using a pound sign (#) and accessible only within the session that created it

Global Temporary table – defined using double pound signs (##) and visible to all users.

Table Variables is a data type that looks similar to a table but has a tighter lifetime scope (i.e within a function, stored procedure or batch that it is declared in) and should be used with small datasets.

Temporary Tables Table Variables
Created and stored in tempdb database Created in Memory (although it can write to tempdb)
They are bound to transactions i.e transaction logs are recorded. They are not bound to any transactions. So no effect of transaction rollbacks
Stored procedure containing temporary tables cannot be pre-compiled Stored procedures containing table variables can be pre-compiled
You can drop a Temporary Table You cannot manually drop a table variable
You can create indexes on them You cannot ‘explicitly’ create Indexes on them (exception is the index created while creating a Primary Key)

Leave a comment

Filed under SQL

Try Catch in SQL

–After the CATCH block handles the exception, control is then transferred to the first Transact-SQL statement that follows the END CATCH statement. If the END CATCH statement is the last statement in a stored procedure or trigger, control is returned to the code that invoked the stored procedure or trigger.
–A TRY block must be followed immediately by a CATCH block.
–TRY…CATCH constructs can be nested.
–Errors that have a severity of 20 or higher that cause the Database Engine to close the connection will not be handled by the TRY…CATCH block.
–Errors that have a severity of 10 or lower are considered warnings or informational messages, and are not handled by TRY…CATCH blocks.

Error Functions
TRY…CATCH uses the following error functions to capture error information:
ERROR_NUMBER() returns the error number.
ERROR_MESSAGE() returns the complete text of the error message. The text includes the values supplied for any substitutable parameters such as lengths, object names, or times.
ERROR_SEVERITY() returns the error severity.
ERROR_STATE() returns the error state number.
ERROR_LINE() returns the line number inside the routine that caused the error.
ERROR_PROCEDURE() returns the name of the stored procedure or trigger where the error occurred.

–The error functions will return NULL if called outside the scope of a CATCH block. Error functions can be referenced inside a stored procedure and can be used to retrieve error information when the stored procedure is executed in the CATCH block.

CREATE PROCEDURE usp_GetErrorInfo
AS
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() as ErrorState,
ERROR_PROCEDURE() as ErrorProcedure,
ERROR_LINE() as ErrorLine,
ERROR_MESSAGE() as ErrorMessage;
GO

BEGIN TRY
— Generate divide-by-zero error.
SELECT 1/0;
END TRY
BEGIN CATCH
— Execute the error retrieval routine.
EXECUTE usp_GetErrorInfo;
END CATCH;

Leave a comment

Filed under SQL