Monthly Archives: March 2015

Rename Table and Change Schema with SP_RENAME

In this post “SP_RENAME table with schema change” I will share a trick to rename a table using sp_rename and then transfer it schema. As we know that we can use SP_RENAME system stored procedure to rename user created objects like tables, procedures, functions, views, indexes, columns, user defined types, CLR user defined types etc in current database.

For example, if you have a table with name “TestTableA” in schema “dbo” and want to change its name to “TestTableB”, you can use below command;

SP_RENAME 'dbo.TestTableA', 'TestTableB'

But in case you want to change the table name with schema change using procedure “sp_rename”, putting “Schemaname.Tablename” as new name in second parameter, will not work. What will happen if we put “Schemaname.Tablename” as new name and why it will happen, I have explained it in next section of this blog post.

Schema.TableName as New Name in sp_rename will not work

In … More

NOLOCK table hint 9

A lot of people use NOLOCK table hint in their SELECT queries, some of them are expert and well aware of its pros and cons, but some are not. So, In this post we are going to focus on this table hint and its impact.

What is NOLOCK

NOLOCK and READUNCOMMITTED hints used with SELECT queries to get the data without issuing shared lock (which prevents modification of data by other transactions during read operation) on the data, and also allows to read the data acquired with exclusive lock by other transactions. NOLOCK hint is equivalent to READUNCOMMITTED isolation level which is least restrictive in SQL Server. It does not restrict the dirty reads and increases the concurrency by reducing blocking.

It holds a Sch-S (Schema stability) lock during its execution scope which blocks the transactions requesting for Sch-M (Schema modification) lock, required by DDL operations. Reverse is also true … More