Search

sqldose

Daily dose to keep your SQL fit and fine.

Month

October 2015

Consequences of Timeout/Error on a Delete statement

Hello Guys,

Lately, one of my friend asked me a Question, “If Delete is in progress and query get erred out or Timeout, will already deleted data will be rolled back, considering we do not have any transaction? “

My previous Answer was NO, After I executed the below statements.

I will create two tables,

PkTable – Having only one primary column.

FkTable – Have one column acting as Foreign key.

CreatingTable_With_Pkey_FKey

CASE 1 – If Query Get erred out

So the idea is, If I try to delete an id from PkTable which is also present in FkTable. Then SQL Statement will erred out. I will check if SQL Deletes all the rows other than the row having Foreign key relationship.

Let us first insert some data in my table.

Insert Records in Pkey Table

Now only one id in my FKTable, to have at-least one Foreign key relationship.

Insert Records in Fkey Table

We are ready to execute delete statement.

Delete Records in Pkey Table

From the above snapshot it is clear that our delete statement was completed with error.(Which was FKey constraint error). So let us check what we have remaining in our PKTable.

Select Records in Pkey Table After Delete

From above image, it is clear that DELETE will remove all the other records except the one for which SQL throws an exception.

CASE 2 – If Query Get Time out

What will happen if query does not get error out but got Time out due to huge number of records or some other reason.

Before i continue, I have to make some configurations. So that i can deliberately make my query to get timeout.

Choose QueryOptions as shown below.

QueryOptions

Configure the highlighted option. I have changed it to 5 seconds.

QueryOptions_ChangeTimeout

Let’s make a quick check if our configuration is working correct or not.

Check For Query Timeout

Great!.

I have repopulated my table. Now, let us do a delete.

Delete Records in Pkey Table With Timeout

Select Records in Pkey Table After Delete with timeout

As expected, all the records before timeout has been deleted.

But here is the Catch. The above Statements may seems to be correct but does not hold true. If you notice I am not deleting a batch of rows. It is just one row at a single time. It was a blunder at my side that I missed to explain (Thanks to Vikas and Chris for pointing this out).

So what I did wrong?

While statement actually making the each DELETE statement as an individual statement. So it is actually a 10000 Delete rather than one single DELETE.

Now, if we run the Delete as a batch it actually Rollback all the deleted rows if an error is encountered. Please see below snapshot.

Total number of rows before delete.

New_Select

Deleting in a batch.

New1

Let us check if we have actually deleted something?

New_Select

No change. This implies Deletion does not happened if it encounters any error.

(For other T-SQL, XACT_ABORT should also be considered. Check below link for details  https://msdn.microsoft.com/en-us/library/ms188792.aspx)

A Basic Introduction of Database, Tables and SQL.

It seems to be the most basic thing but this post is specifically to those who have just started or want to start their career in Database Domain. When you are a novice. You have only idea about what is a Database. But in practical life how companies are actually managing it?

Let us start with the basic.

What is a Database?

Let me take you to the old days of humanity. When there was no Computer. How companies are managing their data back then. Forget about the company ask your Grand Father, How he use to manage all the accounts. The answer is simply writing it in text book. He comes in every night working hard, and wrote all his spending and earning in a notebook. He must have fully covered 10, 20 or much more notebooks in his entire life. This whole collection of his notebooks is nothing but a database. GrandPa database. Database is a repository of essential information. It can be in any format.

Now your grandpa must have wrote his work in different formats. Like in para. “I have to pay Mr. Mark an amount of $10.” Or he might write the same thing as “Mark – Pay $10”. But what should be the most appropriate way to store your data. It would be.

Name MoneyToPAy MoneyToReceive
Mark $10 0

This is a Table. For me it is much easier to read

This give us a basic idea of what is a Database and what is a table?

Now we will enter to the age where Computers. All of your Grandpa Document can be stored in a computer. He will have its own database where he will write all his daily account in details. But how? What is an interface for him to make database, to store data in it.

This is where huge companies provided their product. ORACLE, IBM, MICROSOFT. Tools which can help you create a database, create table. Store data in it. See the result when required. One of such tool is MS SQL developed by Microsoft. Oracle owns Oracle Database. They all have their own methods to work on databases but ultimately they provide you with one solution which is letting you manage you huge database in table format. Conceptually, MS SQL is no different from ORACLE or MYSQL (etc). But each have their own benefits.

What is SQL?

Structured Query Language. Now, Companies have provided us a method to store your data. But how a user is going to interact with that data. How to read or write into a table. For that purpose Structured Query Language is used(SQL). Because Microsoft named their product as MS SQL, Most people think that that SQL is related to Microsoft only. But that is not correct, even Oracle and other platforms uses SQL to work with database (Yes, a little syntax can vary but ultimately the meaning of keywords remain same for all).

So, SQL is a language which can be used to work with your Databases.

Stay Tuned, In my next post I will explain about Microsoft SQL Server.

Next Continuation(https://sqldose.wordpress.com/2016/04/10/starting-with-ms-sql-server/)

Non-Clustered Index

In my previous blog I explained about clustered index (https://sqldose.wordpress.com/2015/09/20/clustered-index-and-related-interview-questions/ ).

Considering that we are versed with the concept of heap, binary and clustered. Lets move on to non clustered index(NCI).

NCI for me is the most mysterious object in SQL Server. The way SQL Server work with it, sometimes can be very unpredictable. But hey do not worry, that is why we are for. Share the most with you all.

Have you used information at the end of book. Which predict all the page number where a specific topic is referred.

NonClusteredIndex

Above image shows a topic Member Function, Which can be find at the corresponding pages.

Similarly, NCI is a structure ( binary or heap) which holds the pointer to the data rows of table at it leaves.

Confused???

Even i am.  😛

Ok! let me explain this in another way. As soon as I create an NCI on a column, SQL Server will create a separate structure and fill it with data(just one value) and PageID where that data can be find.

So if I have to explain NCI in a plain simple language for the MemberFunction shown in above image. It will be, The Value “MemberFunction” can be find at PageID = 101,108…

NOTE : I mentioned NCI structure as btree or heap. Why? NCI will be a heap if my table on which NCI is created does not have a CI(i.e. A HEAP). NCI will be a btree if my table has a CI. So structure of NCI depends on table on which NCI is created.

The most important point to be noted about NCI is they are stored separately. So every time when you perform any DML on your table, the same statement will be applied to NCI as well.

Now, How about if you can actually see what my NCI is filled with. What values it actually holds.

Lets get started.

Here, I am using the SalesOrderDetail (I call it SOD) table of AdventureWorks Database. (You can download this database for your practice.)

SalesOrderDetail

SOD already have two NCI. We will look for only one NCI i.e. IX_SalesOrderDetailProductID.

SalesOrderDetail_NCI

Let us first check how this Non-Clustered Index looks like physically.

NonClusterIndex_Physically1

First query in above snapshot provide me the index id for my NCI. I then used the  DBCC IND command to find the pages allocated to this table under my NCI. From the result of DBCC IND I will look for maximum value in IndexLevel column. This column help me understand whether this page is part of ROOT node or INTERMEDIATE NODE or LEAF NODE. Highest Value represent Root Node. Lowest represent Leaf and others are intermediate nodes.

In our case we have only two type of values i.e. 0 and 1. It means 1 represent the Root Node, 0 as leaf and we do not have any intermediate nodes.

  • PagePID=24336 is a root node for my NCI.

Now check this page for data. We will use DBCC PAGE command for this.

NonClusterIndex Physically2

Let us understand the above result.

ProductID is my NCI column, while SalesOrderID and SalesOrderDetailID are my CI. These will act as pointer. If  I remove the clustered index these two column will be replaced by RID which is physical location address. See below snapshot.

NonClusterIndex Physically3

(As I have deleted the  CI, Pages have been shuffled so new Root page for my index is 24920).

Now back to NCI ProductID. The above snapshots represent that ProductID=707 can be found at ChildPageID 24857,24858…so on. And the way to reach there is HEAP RID/ClusteredIndex column.

To summarize the above. Value 707 lies at page 24857(and many more) and you can use RID or CI as pointer.

I hope this clears what NCI is. If still in doubt drop me your questions.

Now few questions about NCI.

  1. Which column should be the best candidate for non clustered index?

Ans. Column which is most queried with operator = should be the one.

  1. Can we have more than one nci?

 Ans. Yes, you can. But having many nci on table can hit your performance adversely. With Every INSERT or UPDATE, SQL server will have to perform changes in NCI as well. So if you have many nci. SQL server will actually consume more time in updating indexes rather than just working with your data in table.

In some rare scenario, NCI can even cause deadlocks. So we need to be extra care full with those.

  1. Can nci be added to a column having duplicate or null values?

Ans yes.

  1. Can nci be added to a column having unique value?

Ans  yes

  1. Will SQL Server uses NCI every time for a where clause on NCI column?

Ans. NO, It totally depend on the percentage of data which is being retrieved.

If productID=707 have 90% of rows out of total records. Then SQL Server will prefer to use CI rather then taking a long route of NCI.

Create a free website or blog at WordPress.com.

Up ↑