Category : SQL Server


It’s time to install SQL Server 2016

Microsoft has launched the SQL Server 2016. We can download and install it from here. However, when we start installation of SQL Server 2016, we can see some installation changes there. Prior to SQL Server 2016, “SQL Server Management Studio (SSMS)” was a part of the feature list of main SQL Server installation (which starts after clicking on “New SQL Server stand-alone installation or add features to an existing installation“) but now it is available on a separate install. Below is the screen shot of the feature selection list of SQL Server 2016 and SQL Server 2014:

SQL Server 2016 Feature list 2016 vs SQL Server 2014 Feature List

SQL Server 2016 Feature list 2016 vs SQL Server 2014 Feature List

In above image, we can see that the management tools was a feature of the core SQL Server installation process in SQL Server 2014 (right side image) but in SQL Server 2016 (left side image), it is not … More


Set vs Select – Assigning variables

SET and SELECT both can be used to assign values to the local variables in SQL Server. However, SET is an ANSI standard and also recommended by Microsoft to be used for variable assignment. In this post, “Set vs Select – Assigning variables“, we will explore the behavior of SET and SELECT statement during variable assignment from a query.

When we use a scalar subquery (which returns a single value) to assign a local variable using SET or SELECT, both statements behave similar. It means, if subquery returns no rows, they set variable to NULL during variable assignment. But when a column name from a query (which is not a scalar subquery) directly assigns to a local variable using SELECT statement and the query returns 0 rows, then the variable value remains unchanged.

As per its nature, SET statement uses a scalar subquery to assign a local variable … More


Change stored procedure output column names and data types 2

Can we change the output column name and their data type being received from a stored procedure during execution?

Yes, SQL Server 2012 introduced “WITH RESULT SETS” option which can be used with EXECUTE command to redefine the metadata of result sets being received from stored procedures. This option can handle single as well as multiple result sets.

WITH RESULT SETS” option can be useful if we want to reuse an existing stored procedure’s result set(s) with different column name and / or data type. Lets understand this with the help of an example. Below is the stored procedure named “uspGetEmployeeManagers” created in “AdventureWorks2014” datababse. This procedure return the list of managers for a given employee as below:

EXEC [dbo].[uspGetEmployeeManagers] @BusinessEntityID = 101
Current output columns

Current output columns

Imagine, this stored procedure is being used in many applications throughout organization. Now, in a new … More


The join order has been enforced because a local join hint is used – Warning in SQL Server 3

When we use a local JOIN hint in T-SQL query, sometimes, SQL Server throws a warning message as “Warning: The join order has been enforced because a local join hint is used.“. For example, below is a query with a local join hint:

SELECT * FROM Person.Person A 
INNER MERGE JOIN HumanResources.Employee B 
ON A.BusinessEntityID = B.BusinessEntityID

After execution, we will get below warning message with result set:

Warning Message

Warning Message

In above code, we are using a local JOIN hint “INNER MERGE JOIN” which forces this query to override the physical join (which could be nested loop or merge or hash join) chosen by SQL Server with the physical join given in the query hint (which is MERGE join in this case). If you want to read more about the physical joins, visit my post “Nested loop, Merge, and Hash Joins in SQL Server“.… More


Dynamic PIVOT query in SQL Server

PIVOT clause is used to generate cross tab outputs in SQL Server. We put unique data values from a column in the PIVOT clause to render them as multiple columns in aggregation with other columns required in the output. With the help of PIVOT clause, we can transpose the distinct values of a column into multiple columns. However, syntax of PIVOT clause requires these distinct values to be known at query design time. This kind of query can be considered as a static pivot query. We can hard code these distinct values in the PIVOT clause only if these values are fixed and static in nature.

However, when it comes to pivot uncertain values from a column, we would’t be able to cater these values in the PIVOT clause at query design time. Also, SQL Server does not provide any built in functionality which can be used in this scenario. … More


Convert number into year, month, day, hour, minute and second

We have an integer number which represents seconds (secs) part of the time. We need to convert this integer number into year, month, days, hour, minute and second parts. Let me explain with the help of the examples to make it clear.

Examples:

100 Seconds – 0 year(s) 0 month(s) 0 day(s) 0 hour(s) 1 minute(s) 40 second(s)

3600 Seconds – 0 year(s) 0 month(s) 0 day(s) 1 hour(s) 0 minute(s) 0 second(s)

86400 Seconds – 0 year(s) 0 month(s) 1 day(s) 0 hour(s) 0 minute(s) 0 second(s)

2678400 Seconds – 0 year(s) 1 month(s) 0 day(s) 0 hour(s) 0 minute(s) 0 second(s)

35110011 Seconds – 1 year(s) 1 month(s) 10 day(s) 8 hour(s) 46 minute(s) 51 second(s)

etc.

Convert number into year, month, day, hour, minute and second

Below is the code to convert the input integer number into year, month, day, hour, minute and second as above:

DECLARE 
More

Query Performance Tuning – General Guidelines

Query performance tuning – Introduction

Query performance tuning is a process of improving system performance to make it faster and more scalable. It is a creative and iterative process which has a clear and well-defined objective. To achieve this goal, we have to follow certain defined steps.

Why we need this

Enterprise data changes frequently over time, which may impact the performance of existing queries. In most of the cases, a slight change in the index or in the query can improve the performance of the query significantly. In such cases, the improvement in performance can be much better than a CPU or memory upgrade.

How to tune a query

Performance tuning process has certain steps to follow in a defined way to achieve the clearly defined objective.

Query Performace Tuning - General Guidelines - Flow chart

Query performance tuning

In the above flow chart, you see that we begin by setting objective, followed by getting the current performance … More


Order max and min value rows alternatively in SQL Server

In this blog post “Order max and min value rows alternatively in SQL Server”, we are going to learn how we can order the rows of a table by combining max and min value rows alternatively. For example, from an employee table based on the salary column, we need to extract the records ordered as;

First row: First highest salary

Second row: First lowest salary

Third row: Second highest salary

Fourth row: Second lowest salary

Fifth row: Third highest salary

Sixth row: Third lowest salary

and so on…….

We know that the SQL Server database engine is optimized to operate in a set oriented way instead of RBAR (Row By Agonizing Row, processing row by row in a loop). We are going to achieve the desired output in a set based approach, unlike loops or recursive CTEs. Let’s have this tutorial in action now.

Create the dummy employee table and … More


Add constraint without checking existing data

In this post “Add constraint without checking existing data” we are going to learn how we can add a constraint on a column which already has invalid data. We create constraints to automatically enforce the integrity of the database. Using constraints we can define the rules for valid set of values for a given column. Let’s learn how we can instruct SQL Server to ignore check on the existing data during constraint creation on a column.

We have an employee table with columns EmpId, EmpName and ContactNumber. Column ContactNumber holds few invalid values, as we don’t had a constraint on this column. Now, we want to accept only valid values in the contact number column being validated with the custom business logic, but we don’t want to remove or modify the already existing data in the column. We need to put a constraint for coming data and not for existing … More


Index on computed column 2

Today, we are going to learn how we can create index on a computed column which improves the performance of SELECT queries. To create a computed column, we will use an user defined function instead of inline code. Also to know about computed columns, refer my previous blog post “What is Computed Column in sql server“.

Create index on computed column

Let’s create a function, “dbo.fn_GetTotalCourseFee“, which accepts @CourseFee and @TaxPercentage as input parameters and returns the computed @TotalFee amount. To highlight some important prerequisites related to index creation on computed column, I have used a function, otherwise we can also use the inline code in column definition.

Below is the function being used to create a computed column:

CREATE FUNCTION dbo.fn_GetTotalCourseFee
(
 @CourseFee NUMERIC(9, 2),
 @TaxPercentage NUMERIC(9, 2)
)
RETURNS NUMERIC(9, 2)
WITH SCHEMABINDING
AS
BEGIN
 DECLARE @TotalFee NUMERIC(9, 2) = 0.0
 SET @TotalFee = 
More