SQLcommitted

Committed with SQL

SQL Server – Index Hint

SQL Server query optimizer decides which index to use, when a query is requested. SQL Server optimizer is a cost based optimizer so it picks the index which has low cost based on performance. When we design an index for a query, optimizer will utilize the defined index to generate the plan for requested query if the index has low cost based on performance of query. But for some special scenarios or very rare scenarios where SQL Server will not use the most appropriate index for a given query and impact query performance.

If SQL Server optimizer is not using the expected index and you wanted your query to use that specific index then it can be done using Index hint. Be aware most of the time Query Optimizer chooses the right index out of available indexes.

I usually use Index hint while designing index to compare between indexes to know which one is best for my query.

Demo1: Use Index hint to force SQL server optimizer to use a specific Index

USE [AdventureWorks]

GO

SELECT ProductID, ReviewerName, [Comments]

FROM [Production].[ProductReview] WITH (INDEX = IX_ProductReview_ProductID_Name)

 

image

 

Demo2: Use multiple Index hint for multiple tables

USE [AdventureWorks]

GO

SELECT PR.ProductID, PR.ReviewerName, PR.Comments, PP.Name

FROM [Production].[ProductReview] PR

WITH (INDEX = IX_ProductReview_ProductID_Name)

INNER JOIN [Production].[Product] PP

WITH (INDEX = [AK_Product_Name]) ON PR.ProductID = PP.ProductID

image

 

NOTE: Hints can prevent the query optimizer from choosing a better execution plan

 

If you liked this post, do like on Facebook at https://www.facebook.com/s4sql

January 1, 2014 Posted by | Index | , , , | 2 Comments

SQLCommitted 2013 in review

SQLCommitted 2013 annual report prepared by wordpress.

Here’s an excerpt:

The concert hall at the Sydney Opera House holds 2,700 people. This blog was viewed about 11,000 times in 2013. If it were a concert at Sydney Opera House, it would take about 4 sold-out performances for that many people to see it.

Click here to see the complete report.

December 31, 2013 Posted by | SQL Info | Leave a comment

SQL Server – Identity Property Part – 3

This is third part of this blog series. You can read the 2nd part here (SQL Server: Identity Property Part – 2).

In this blog post we’ll learn how to retrieve last generated identity value. There are scenarios where we need to fetch the last inserted identity value, for example in a transaction we may first generate OrderID for order header table and then enter item details for that order in OrderItem table.

SCOPE_IDENTITY, IDENT_CURRENT, and @@IDENTITY all of them are used to retrieve last inserted identity value but all of them return value depending on scope and session. Let us see how can we fetch last inserted identity value in SQL Server.

SCOPE_IDENTITY

It returns the last Identity value inserted within the current scope for any table. A scope is a module that can be a stored procedure or trigger or function or batch. It returns identity value. This function will return NULL if it is invoked before any insert statements into an identity column occurs in the scope.

Example 1:

IF(Object_ID(‘Employee’) IS NOT NULL)

DROP TABLE Employee

GO

CREATE TABLE Employee (EMPID int IDENTITY, EMPNAME varchar(50) NOT NULL)

GO

INSERT Employee (EMPNAME ) VALUES (‘Sandip’),(‘Amit’)

GO

SELECT EMPID , EMPNAME FROM Employee

SELECT SCOPE_IDENTITY() IDENTITY_VALUE

image

 

Example 2:

IF(Object_ID(‘Employee’) IS NOT NULL)

DROP TABLE Employee

GO

CREATE TABLE Employee (EMPID int IDENTITY, EMPNAME varchar(50) NOT NULL)

GO

Create an INSERT trigger on Employee

IF OBJECT_ID (‘TRG_Employee’,‘TR’) IS NOT NULL

DROP TRIGGER TRG_Employee

GO

CREATE TRIGGER TRG_Employee

ON Employee

AFTER INSERT

AS

INSERT Employee (EMPNAME ) VALUES (‘Sunil’)

SELECT SCOPE_IDENTITY() WithinTrigger

GO

–Insert in current scope

INSERT Employee (EMPNAME ) VALUES (‘Sandip’)

GO

SELECT SCOPE_IDENTITY() InCurrentScope

SELECT EMPID, EMPNAME FROM Employee

image

 

IDENT_CURRENT

It returns the last IDENTITY value produced in a table, irrespective of the connection and the scope of the statement. It is not dependent on any scope and session. This function takes table name as parameter.

Example 1:

IF(Object_ID(‘Employee’) IS NOT NULL)

DROP TABLE Employee

GO

CREATE TABLE Employee (EMPID int IDENTITY, EMPNAME varchar(50) NOT NULL)

GO

Create an INSERT trigger on Employee

IF OBJECT_ID (‘TRG_Employee’,‘TR’) IS NOT NULL

DROP TRIGGER TRG_Employee

GO

CREATE TRIGGER TRG_Employee

ON Employee

AFTER INSERT

AS

INSERT Employee (EMPNAME ) VALUES (‘Sunil’)

SELECT IDENT_CURRENT() WithinTrigger

GO

–Insert in current scope

INSERT Employee (EMPNAME ) VALUES (‘Sandip’)

GO

SELECT IDENT_CURRENT() InCurrentScope

SELECT EMPID, EMPNAME FROM Employee

image

 

@@IDENTITY

This system function returns the last generated identity value for any table in the current session and is not limited to any specific scope.

If any insert statement on a table causes a trigger to fire on another table which cause to generate an identity value, you will get the last identity value that was created last.

Example 1:

IF(Object_ID(‘Employee’) IS NOT NULL)

DROP TABLE Employee

GO

CREATE TABLE Employee (EMPID int IDENTITY, EMPNAME varchar(50) NOT NULL)

GO

Create an INSERT trigger on Employee

IF OBJECT_ID (‘TRG_Employee’,‘TR’) IS NOT NULL

DROP TRIGGER TRG_Employee

GO

CREATE TRIGGER TRG_Employee

ON Employee

AFTER INSERT

AS

INSERT Employee (EMPNAME ) VALUES (‘Sunil’)

GO

INSERT Employee (EMPNAME ) VALUES (‘Sandip’)

GO

SELECT @@IDENTITY() LastIdentityvalue

SELECT EMPID, EMPNAME FROM Employee

image

 

If you liked this post, do like on Facebook at https://www.facebook.com/s4sql

December 7, 2013 Posted by | Identity, Transact-SQL | , , , , , , , | Leave a comment

SQL Server: Identity Property Part – 2

In this blog series, I’m covering all about Identity property. This is the second part of this series. You can read the first part here (SQL Server: Identity Property Part – 1).

After reading this post you will know 5 facts about Identity column.

Fact 1- By default both Identity seed and increment value will set to 1.

CREATE TABLE Employee (EMPID int IDENTITY, EMPNAME Varchar(50)

GO

Run the above script  and open design view of Employee table, you can find seed and increment value set to 1 for both.

image

Fact 2- Keyword IDENTITYCOL automatically refers to the specific column in a table that has identity property defined.

 

IF(Object_ID(‘Employee’) IS NOT NULL)

DROP TABLE Employee

GO

CREATE TABLE Employee (EMPID int IDENTITY, EMPNAME varchar(50))

GO

INSERT Employee (EMPNAME) VALUES (‘Sandip’)

GO

SELECT IDENTITYCOL FROM Employee

image

Note: If there is no column defined as Identity then querying IDENTITYCOL will throw an error.

Fact 3- Function IDENT_SEED and IDENT_INCR are used to find the seed and increment value respectively. It returns NULL if identity property not defined for a table.

Syntax: IDENT_SEED(‘Table Name’)

IF(Object_ID(‘Employee’) IS NOT NULL)

DROP TABLE Employee

GO

CREATE TABLE Employee (EMPID int IDENTITY, EMPNAME varchar(50))

GO

SELECT IDENT_SEED(‘Employee’) AS SEED_VALUE, IDENT_INCR(‘Employee’) AS INCREMENT_VALUE

 

image

 

Fact 4- Identity property can’t be defined on a nullable column.

IF(Object_ID(‘Employee’) IS NOT NULL)

DROP TABLE Employee

GO

CREATE TABLE Employee (EMPID int IDENTITY(1,1) NULL , EMPNAME varchar(50))

image

Fact 5- Only one identity column allowed per table

IF(Object_ID(‘Employee’) IS NOT NULL)

DROP TABLE Employee

GO

CREATE TABLE Employee (SNO int IDENTITY(1,1) NULL,EMPID int identity(2,2), EMPNAME varchar(50))

image

 

If you liked this post, do like on Facebook at https://www.facebook.com/s4sql

December 4, 2013 Posted by | Identity | , , , , , | Leave a comment

SQL Server: Identity Property Part-1

Identity property is one of the most frequently used properties to generate auto increment value. I’ve seen sometime developer get confused with it. So this blog series will help developers to know everything about Identity property.

Syntax: IDENTITY [(seed, increment)]

You must specify both the seed and increment or neither. By default the value of seed and increment is (1, 1). Identity column must be of data type int, bigint, smallint, tinyint, or decimal or numeric with a scale of 0 and constrained to be not null.

The identity property on a column guarantees the following:

  • Each new value is generated based on the current seed & increment value.
  • Each new value for a particular transaction is different from other concurrent transactions on the table.

Facts about identity property on a column :image

  • Identity property does not guarantee uniqueness of column value.
  •  A transaction inserting multiple rows is not guaranteed to get consecutive values for the rows because other concurrent inserts might occur on the table. To get consecutive values the transaction should use an exclusive lock on the table or it should use the SERIALIZABLE isolation level.
  • SQL Server might cache identity values for performance reasons and some of the assigned values can be lost during a database failure or server restart. This can result in gaps in the identity value upon insert. If gaps are not acceptable then the application should use a sequence generator with the NOCACHE option or use their own mechanism to generate key values.
  • The identity values are not reused by the engine, If a particular insert statement fails or if the insert statement is rolled back then the consumed identity values are lost and will not be generated again.

Reference: MSDN

If you liked this post, do like on Facebook at https://www.facebook.com/s4sql

 

August 8, 2013 Posted by | Transact-SQL | , , , , , | 3 Comments

SQL Server– How to Encrypt Column Data

Encryption is one of the most secure way to protect your confidential data like Social Security Number, Date Of Birth , Patient clinical information etc. We need to encrypt certain data to meet the business requirements and sometime to meet certain compliance. Here we’ll see how can we encrypt column data using symmetric key encryption in SQL Server 2012.

data-encryption

DEMO

Lets first create a table and insert a record into it. Here in this demo we’ll encrypt the RegdNumber column data.

CREATE TABLE  Employee (EmpID int, EmpName varchar(100), RegdNumber nvarchar(20), EncryptedRegdNumber varbinary(128))
GO

INSERT  Employee (EmpID, EmpName, RegdNumber)
Values (1,‘Jackson’,N’Regd_AA1′)

 

 

 

Steps to encrypt column data                                                                                   

Step 1- Check if master key does not exist then create one

 

IF NOT EXISTS
    (SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id = 101)
    CREATE MASTER KEY ENCRYPTION BY
    PASSWORD = ‘abc12$$^7′
GO

Note : You should back up the master key by using BACKUP MASTER KEY and store the backup in a secure, off-site location.

Step 2 – Create certificate

CREATE CERTIFICATE EmployeeCert
   WITH SUBJECT = ‘Employee RegdNumber’;
GO

Step 3 – Create symmetric key using the certificate

CREATE SYMMETRIC KEY EmployeeRegdNumberKey_1
    WITH ALGORITHM = AES_256
    ENCRYPTION BY CERTIFICATE EmployeeCert;
GO

Step 4 – Open the symmetric key using  which the data will be encrypted.

OPEN SYMMETRIC KEY EmployeeRegdNumber
   DECRYPTION BY CERTIFICATE EmployeeCert;

Step 5 – Encrypt the value in column RegdNumber using the symmetric key EmployeeRegdNumberKey_1.

UPDATE Employee
SET EncryptedRegdNumber = EncryptByKey(Key_GUID(‘EmployeeRegdNumber’)
    ,RegdNumber , 1, HashBytes(‘SHA1′, CONVERT( varbinary , EmpID)));
GO

 

Now query the original RegdNumber, encrypted RegdNumber and decrypted RegdNumber.  If encryption is working fine the the original RegdNumber column value should match with DecryptedRegdNumbers

SELECT RegdNumber ,EncryptedRegdNumber    AS ‘Encrypted RegdNumber’,

CONVERT(nvarchar,   DecryptByKey(EncryptedRegdnumbet, 1 ,HashBytes(‘SHA1′, CONVERT(varbinary, empid))))  AS ‘Decrypted RegdNumber’

FROM Employee;
GO

The above script you can use in your local environment to check how you can encrypt column data.

Hope the above demo will help to understand how to encrypt column data.

 

If you liked this post, do like on Facebook at https://www.facebook.com/s4sql

March 10, 2013 Posted by | Encryption | , , , , , , , , , , , , | Leave a comment

SQL Server – Index Selection Fundamental

Index is one of most widely discussed topic among developers and DBAs. During query optimization Index is one of the important aspects. I came across multiple instances where Developer/ DBA creates Index but they are not sure whether that index is used by the optimizer or not. Also I received emails from developer asking How to design an Index? So Here I’ll try to explain the fundamental of Index Selection.

While designing an Index the basic idea is that the optimizer should use the index so that it will fetch the data using an optimal path with fewer disks I/O operation and less system resource usage.

Below are the important parameters for Optimizer to consider before it selects an Index to use

  • Predicate from the WHERE clause
  • Join Condition
  • Other limiting option used in a query (Ex: Group by, Distinct etc.)

Using Index, optimizer can perform either Seek Operation or Scan Operation.

Seek Operation is used when fetching a single value or a range of value and in Scan operation it traverse through the Index pages either forward or backward.

All predicate used in where clause are not always used for Index selection. Predicate which can be converted into an Index operation are often called SARGgable or Search-ARGument-able and predicate that do not match the selected index are called non-sargable predicates which are used as filter predicate. SQL SERVER usually evaluated non-sargable predicates with in the seek/scan operator in the query tree.

Once you create an Index always checks with your query to determine whether it improves your query performance. If it is not improving your query performance then remove that index.

To understand SARGgable and Non SARGgable predicate,  let me demonstrate it using a simple demo.

For this Demo I’m using AdventureWorks database which can be downloadable from here.

Step 1: create table Product_1 using Product table.

SELECT * INTO Product_1 FROM [Production].[Product]

Step 2: Create an UNIQUE Clustered Index on Product_1 for Column ProductID

CREATE UNIQUE CLUSTERED INDEX CIX_Product ON Product_1 (ProductID)

Step 3: Run the below query and check its actual execution plan. You can enable actual execution plan either by shortcut Ctrl+M or from the Query menu in SSMS.

SELECT * FROM Product_1 WHERE ProductID <100 and Makeflag=1

We can see below Optimizer uses the Index Seek operation to fetch the data. If you mouse over on Clustered Index Seek operator you will get the operator details. Here we can see that ProductID is used for Seek predicate as we have Index on that and column MakeFlag is used for filter predicate.

Actual Execution Plan:

image

If you liked this post, do like on Facebook at https://www.facebook.com/s4sql

January 17, 2013 Posted by | Index, Transact-SQL | , , , , , , , | 2 Comments

SQL Server – New Analytical functions in SQL Server 2012 – Lead and Lag

LAG:

Using this function we can fetch the nth row before (Lag) the Current row without using self-join.

It is useful when we need to compare the current row values with values in a previous row.

Syntax:

LAG (scalar_expression [,offset] [,default])

    OVER ( [ partition_by_clause ] order_by_clause )

EXAMPLE:

Create an Employee table and insert some records to it.

CREATE TABLE  Employee (EmpID int, HikeDate date, Salary money)
GO
INSERT INTO employee values (1, '2009-04-01 00:00:00.000',10000),
(1, '2010-04-01 00:00:00.000',17000),
(1, '2011-04-01 00:00:00.000',23000)
GO


SELECT EmpID, HikeDate, Salary
FROM Employee
ORDER BY HikeDate

Output:

image

 

Now my requirement is to show what the previous Salary before current hike was?

Solution Using Lag function:

image

 

image

LEAD:

Using this function we can fetch the nth row after (LEAD) the current row without using self-join.

It is useful when we need to compare the current row values with values in a following row.

Syntax:

LEAD (scalar_expression [,offset] [,default])

OVER ( [ partition_by_clause ] order_by_clause )


Example:

My requirement is to show what my following year salary is?

 

Solution using Lead function:

image

image

 

Note: Default offset value is 1. We can specify offset value by a column or a sub query, or other expression that evaluates to a positive integer. Negative value or analytical function can’t be used as offset value.

If you liked this post, do like on Facebook at https://www.facebook.com/s4sql

January 11, 2013 Posted by | SQL SERVER 2012, Transact-SQL | , , , , , , , , | 1 Comment

SQL Server – Happy New Year 2013 and Happy First Anniversary to My Blog

Wish you a very Happy New Year 2013. Today is a special day for me to celebrate the New Year because today my blog post turned 1 year old. I can’t believe that I have been blogging for a year now. This blog was just created with an intension to share what I learned during my daily work. I’m happy that I initiated to contribute to the SQL Server community.

Thank you to Pinal Dave (B|T) and Vinod Kumar (B|T) for their guidance and encouragement. Thank you to my Wife for her support. Last but not least I thank you to all my readers.

I posted 24 blog posts in 2012 that is one post in every 2 weeks. Many have their New Year resolutions and I’m not the exception in this case. One of my New Year resolutions is to write at least one blog post per week.

January 1, 2013 Posted by | NEWS, SqlCommitted | , , | 8 Comments

SQL Server – Order of Column and Impact of equality operator on Index operation

Does order of Column in where clause matters?

Before you answer to this question I recommend all to read the blog post SQLAuthority.

Whenever I have any doubt, I always play around with it to clarify my doubt. While doing experiment with index operation, I learnt something and here I’m demonstrating what I learnt?

Let’s do some experiment,

In below experiment I’m using the AdevntureWorks database, which can be downloadable from Codeplex.

Demo – 1 (Order doesn’t matter)

Create Customer1 table from Sales.Customer table.

SELECT * INTO Customer1 FROM Sales.Customer

Now Create an Index on Column CustomerID,TerritoryID

CREATE INDEX IXNC_Customer1_1 ON Customer1 (CustomerID,TerritoryID)

Run below two queries, the only difference in the queries are the order of column in where clause.

–Query – 1

SELECT CustomerID, TerritoryID

FROM Customer1

WHERE CustomerID =1 AND TerritoryID =1

–Query – 2

SELECT CustomerID, TerritoryID

FROM Customer1

WHERE  TerritoryID =1 and CustomerID = 1

Compare Execution plan for both the queries:

image

image

 

We can see in the above Demo (Fig-1) both queries uses Index Seek and query cost is same.

Now compare index seek operation for both of the queries, Seek predicates prefix CustomerID as the first column in both the cases and both CustomerID and TerritoryID used by SQL server as seek predicates.

Before proceed further, from Demo- 1 we can conclude that the order doesn’t matter in where clause.

 

Demo – 2 (It matters if other than first column of the index used in where clause)

Now let’s see what will happen if we use only one column in where clause?

Run below two queries. Here I’ve used CustomerID in first query and TerritoryID in second query respectively in where clause.

–Query – 3

SELECT CustomerID, TerritoryID

FROM Customer1

WHERE  CustomerID = 1

–Query – 4

SELECT CustomerID, TerritoryID

FROM Customer1

WHERE  TerritoryID = 1

image

image

In Demo – 2, We can see that when we use Column CustomerID in Where clause it uses Seek Predicate for CustomerID Column but in Query – 4, there is no seek predicate because it is using Index scan.

In Fig – 4, The actual query plan shows the actual number of row are more in Query – 4, so we may assume that in second query, the number of resultant rows are more so it uses Index Scan and It doesn’t matter whether we use first column or second column of the index in where clause. It does matter if we use only one column of the index.

To prove that, now let’s see if we have only one row for TerritoryID is equal to 1, which operation will be used by SQL Server.

Below query will delete all records from Customer1 table except one row where TerritoryID = 1.

DELETE FROM Customer1 WHERE CustomerID >1 AND TerritoryID = 1

Confirm it by running below query that we have only one record in Customer1 table where TerritoryID = 1

SELECT * FROM Customer1 WHERE TerritoryID = 1

Now let’s run the same query that is Query – 4

–Query – 5

SELECT CustomerID, TerritoryID

FROM Customer1

WHERE  TerritoryID = 1

image

In above example we can see that even though there is only one row for TerritoryID = 1, It is not using TerritoryID column as seek predicate.

It is using Index scan because TerritoryID is not the first column of the index.

Demo – 3: The second column will participate in seek predicate Only If the first column of the index compares with equality operator irrespective of the order in where clause.

 

Note: As I’ve deleted some rows from the table. I’m dropping the table and recreating the Table and Index.

DROP TABLE Customer1

SELECT * INTO Customer1 FROM Sales.Customer

CREATE INDEX IXNC_Customer1_1 ON Customer1 (CustomerID,TerritoryID)

GO

Let’s run below two queries, where in first query, CustomerID is compared with equality operator and second query, CustomerID is compared with non-equality operator.

–Query – 6

SELECT CustomerID, TerritoryID

FROM Customer1

WHERE  TerritoryID >1 AND CustomerID = 1

–Query – 7

SELECT CustomerID, TerritoryID

FROM Customer1

WHERE TerritoryID > 1 AND CustomerID > 1

 

image

 

image

In the above example, we can see that even though both the queries uses Index seek operation but in Query – 5, Both CustomerID and TerritoryID columns are participated in Seek Predicate and in Query – 6 only CustomerID is participated in Seek predicate and TerritoryID is used as filter predicate.

 

Conclusion:

  • Order of column in where clause doesn’t matter.
  • It matters if we use Columns except first column of the Index.
  • If first column of the index compared with equality operator then only second column will participate in Seek predicate.

If you liked this post, do like on Facebook at https://www.facebook.com/s4sql.

December 24, 2012 Posted by | Index | , , , , , , | 1 Comment

Follow

Get every new post delivered to your Inbox.

Join 76 other followers

%d bloggers like this: