How To Rename A Column Name Or Table Name In SQL Server

I have seen a lot of times beginners seeking a request to rename either the column name or table name in SQL Server. For some reason, the tables and columns created in the production servers cannot be dropped and recreated with the custom structure as the prior table could be consisted of data. In this case, we can either use the stored procedure or use the GUI to rename either the Table name or Column name.

Let’s create the tables and insert some data into the table.

CREATE TABLE [dbo].[SQLArena](

       [Column1] [nvarchar](300) NULL,

       [Column2] [nvarchar](300) NULL

) ON [PRIMARY]

INSERT INTO SQLArena(column1, column2) values(1,'value1')

SELECT * FROM SQLArena

Case 1: Using the GUI to rename the Table Name or Column Name

Open the object explorer, and expand the respective database. Under the respective database, expand once again the table and columns that need to be edited. To rename the table name or column name, a single left mouse key button on the table name or the column name turns to an edit box. Here, you will be able to rename the desired name needed. Let’s look at the same. 

TableName Change:

ColumnName Change:

Case 2: Using the system stored procedure to rename the Table Name or Column Name

Format for TableName Change:  EXEC sp_RENAME '[OldTabName]' , '[NewTabName]'

EXEC sp_RENAME '[SQLArena]' , '[SQLArena_TEST]'

Format for ColumnName Change: EXEC sp_RENAME 'TableName.OldColName' , 'NewColName', 'COLUMN'

EXEC sp_RENAME 'SQLArena.Column1' , 'ID', 'COLUMN'

EXEC sp_RENAME 'SQLArena.Column2' , 'Value', 'COLUMN'

Doing the system stored procedure to change the Table Name or Column Name can pop a warning message in the result window, which is not to be considered a major concern.

Warning message: “Caution: Changing any part of an object name could break scripts and stored procedures.”

Difference Between Shared Lock, Exclusive Lock And Update Lock In SQL Sever

Most often I have come across these locks while experiencing deadlocks. And there had to be a lot of time to understand what is happening exactly at the row-level while fetching the data. Let’s look at the concept and difference between a Shared Lock, an Exclusive Lock and an Update Lock in SQL Server.

Shared Lock (S)

A Shared Lock is basically a read-only lock for a row-level. Any number of resources can fetch the data to read when the shared lock is present on the resource. That means that many process IDs can have a shared lock on the same resource to read the respective data.

Exclusive Lock (X)

The Exclusive Lock is used and valid on a single transaction, that locks either row or a page depending on the data. The mechanism for understanding is simple, where an exclusive lock can be applied only on a single resource. There cannot be more than one exclusive lock on the same resource. Either Insert, Update or Delete commands happen over with the Exclusive lock, and these commands will not be in effect until the exclusive lock is released from the resource.

Update Lock (U)

An Update Lock is used and valid when there is a shared lock applicable for a resource. In other words, the update lock cannot be placed until there are no other offending exclusive or update locks for the fetching resource. Additionally, the update lock happens to be acquiring an exclusive when all other locks are released from a resource. 

You can find these interview topics interesting too:

"Difference Between CURRENT_TIMESTAMP and GETDATE() in SQL Server"

"What is ACID Properties in SQL Server"

How to Find Particular Column from All Tables in SQL Server

It is always too hard to use the Alt + F1 to check and review if there is a column present in the created table in SQL Server. Further, it would be much easier if there was a query to find the required column was present in all tables are not. An easy query for you to find a particular column from all tables in a database.

USE SQLArena_Test

GO

SELECT t.name AS TABLE_NAME,

SCHEMA_NAME(schema_id) AS SCHEMA_NAME,

c.name AS COLUMN_NAME

FROM sys.tables AS t

INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID

WHERE c.name LIKE 'Date' --Replace Date with your own column name

ORDER BY schema_name, table_name;

Now some of them would seek for the same column whether present or not across all tables and across the database from a server. We can do that as well by using stored procedure sp_msforeachdb.

EXEC sp_MSForeachdb

' USE [?]

IF ''?'' <> ''master''

   AND ''?'' <> ''model''

   AND ''?'' <> ''msdb''

   AND ''?'' <> ''tempdb''

   AND ''?'' like ''database name''    

BEGIN

   SELECT ''?''

 Use the above SELECT statement here to fetch across all your databases

  

END'

How to Resolve Error - The log or differential backup cannot be restored because no files are ready to rollforward

It was when one of my friend who got paused at his work when he received an error message as

Msg 8766, Level 16, State 1, Line 1

The log or differential backup cannot be restored because no files are ready to roll forward.
Msg 4523, Level 16, State 1, Line 1
RESTORE LOG is terminating abnormally.

Before moving on to resolve the issue, lets first check the status of the database from which the error is being generated. The database status could be either in ONLINE, OFFLINE, RECOVERY, RECOVERY PENDING, RESTORING, SUSPECT or EMERGENCY (Limited Availability).

Resolution:

In almost all the status of the database mentioned, there could be two possible scenarios of the database, where we can experience this error. While I was checking with my friend regarding the issue, he was stuck at a point when the database is live and running with critical data in the production server. However, nothing to panic at this stage. Let’s check for the two scenarios to resolve the issue.

 Solution 1:

In case, if you are trying to restore on a live database, which is already restored with “WITH RECOVERY” then there is no point of either applying Differential backup or T-LOG backup on an existing live database. If your latest Full and Differential backups are ready on your disk for the concerned database, then proceed to restore the Full backup “WITH NORECOVERY” and then rest data with the Differential backup “WITH RECOVERY”.

RESTORE DATABASE SQLArena_TEST

FROM DISK = 'C:\SQLArena_TEST_Full_Temp1.bak'

WITH NORECOVERY; -- with NORECOVERY means still, there are backups to be restored

RESTORE DATABASE SQLArena_TEST

FROM DISK = 'C:\SQLArena_TEST_Full_Temp1.bak'

WITH RECOVERY; -- with RECOVERY means there are no backups pending to be restored

NOTE: This can resolve only if your backups are consistent. 

Solution 2:

In case, you try the Solution 1 and fail to succeed, then try to take a full backup of the concerned database for both Full backup and Differential backup. Once, the Full and Differential backups are ready, try to restore Full backup with “NORECOVERY” and Differential with “RECOVERY”. This can for sure resolve your issue from erroring out.

BACKUP DATABASE SQLARENA_TEST

TO DISK = N'C:\SQLARENA\SQLArena_TEST_Full_Temp1.trn'

WITH NOFORMAT, STATS = 10 -- full backup

BACKUP DATABASE SQLARENA_TEST

TO DISK = N'C:\SQLARENA\SQLArena_TEST_Full_Temp1.trn'

WITH NOFORMAT, STATS = 10 -- differential backup

RESTORE DATABASE SQLArena_TEST

FROM DISK = 'C:\SQLArena_TEST_Full_Temp1.bak'

WITH NORECOVERY; -- with NORECOVERY for full backup

RESTORE DATABASE SQLArena_TEST

FROM DISK = 'C:\SQLArena_TEST_Full_Temp1.bak'

WITH RECOVERY; -- with RECOVERY for differential backup

Please do let me know if this doesn’t work as there are many other possible situations that could be solved in a different way.

Important Note: Please try not to test these on production databases, which can probably mess around. For testing purposed please try in test or development servers. 

You may also refer:
"Creating a COPY_ONLY Backups in SQL server"

LCK_M_XXX Wait Type in SQL Server

Locking is a structure used in the SQL Server Database Engine to retrieve and access a piece of data by multiple users at the same time. Using this mechanism can help the morality of the data being retrieved from the database.

Let’s look at some of the LCK_M_XXX types present in SQL Server. These are some of the basic lock operations encountered by me and these types of locks are probably handled by either DBAs or Database Developers. 

1. LCK_M_BU

Encountered when a request is waiting to acquire a Bulk Update(BU) lock. Basically, occurs when there is a need to protect database objects during bulk insertions.

2. LCK_M_IS

Encountered when a request is waiting to acquire an Intent Shared(IS) lock. Basically, occurs when there is a need to modify a database object the user, who will have the access to read but denied on updates.

3. LCK_M_IU

Encountered when a request is waiting to acquire an Intent Update(IU) lock. Basically, occurs when there is a need to write on a database object, which is already either in reading or modifying operation.

4. LCK_M_IX

Encountered when a request is waiting to acquire an Intent Exclusive(IX) lock. Basically, this is something like Intent Update, where there is a need to write on a database object, which is already either in reading or modifying operation.

5. LCK_M_S

Encountered when a request is waiting to acquire a Shared(S) lock. Basically, occurs when there is a need to read a request on a database object when already a write operation is on. 

6. LCK_M_U

Encountered when a request is waiting to acquire an Update(U) lock. Basically, occurs when there is a need to write on a database object, which already is either in reading or modify operation, something like LCK_M_IU. 

7. LCK_M_X

Encountered when a request is waiting to acquire an Exclusive(X) lock. Basically, occurs when there is a need to read/write on a database object, wherein a write operation is already on.

How to Resolve:

1. Keep all your transactions short and simple.

2. In case taking a backup/restore, try not to take a backup of the same database at the same time.

3. Use EXEC sp_who2 Active to check for blocking in SQL Server.

4. Try not to change the isolation to “Read Uncommitted”, because the data getting generated would be irrelevant. Setting Read Uncommitted can cause other statements to read rows that are modified but still not committed. 

5. Set up a job to monitor the queries that are running on your server for quite a long time. This can help in recognizing and killing the query in case the query is found junk.

6. Carry out an option to generate a timeout mail for the queries exceeded certain allowable limits.

7. Check for network connectivity for the server, as there are chances are transport-level error thereby causing the session to automatically go to rollback state.

8. Monitor the I/O alerts on your server, which could be probably due to the long-duration to fetch the logical file from the physical disk path. 

You may also refer:

"Simple way to identify blocking in SQL Server"

"How to Kill a SPID in SQL Server?"

SET QUOTED IDENTIFIER ON/OFF and ANSI NULLS ON/OFF in SQL Server

The SET QUOTED IDENTIFIER and ANSI NULLS are probably not used while writing a stored procedure, user function or triggers in SQL Server by beginners in DBA or SQL development. However, even without them, the results will be generated, but in case if you specify the SET QUOTED IDENTIFIER and ANSI NULLS, there could be fewer errors relating to quotes and NULL value comparisons respectively. Here are some reasons for setting these before to start to write stored procedures, functions or triggers.

SET QUOTED IDENTIFIER ON/OFF:

Setting the SET QUOTED IDENTIFIER ON can emphasize any data defined in double-quotes as either table name, column name, function name or procedure name. While defining the data in single quotes, it emphasizes data to be literal and can lead to an error. Let’s look at an example.

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE "TEST_TABLE" --Success

(ID int,

"Function_Dummy" varchar(100))

SET QUOTED_IDENTIFIER OFF

GO

CREATE TABLE "TEST_TABLE" -- Fails since it takes the data entered as literal

(ID int,

"Function_Dummy" varchar(100))

SET QUOTED_IDENTIFIER ON

GO

SELECT "Function_Dummy" --Fails since it takes the data entered as literal

SET QUOTED_IDENTIFIER OFF

GO

SELECT "Function_Dummy" --Success

ANSI NULL ON/OFF:

The ANSI NULL ON/OFF emphasizes how SQL Server compares the NULL values.

When set to ON, if the value NULL is compared with a = or <> sign, then the result produced would be false. In other words, any value comparing with NULL returns a zero.

SET ANSI_NULLS ON

IF NULL=NULL

PRINT 'SQL'

ELSE

PRINT 'ARENA' -- ARENA is a false statement if set to ANSI_NULL ON

When set to OFF, if the value NULL is compared with a = or <> sign, then the result produced would be true. In other words, any value comparing with NULL returns a NULL value itself.

SET ANSI_NULLS OFF

IF NULL=NULL

PRINT 'SQL'   -- SQL is a true statement if set to ANSI_NULL OFF

ELSE

PRINT 'ARENA' 

You may also need:
"Advantages of Using UNION ALL Over UNION in SQL Server"
"TRIM Function to Remove Blank Spaces From a String in SQL Server"
"Difference Between Truncate and Delete Command in SQL Server"
"Understanding the Importance of Database Schema in SQL Server"

Rearrange Column of Database Table in SQL Server

Many developers come up with a common question of whether a workaround can be made on changing the column structure. The straight answer to them is a “No” for an immediate fix. However, there are methods to rearrange a newly inserted column for an already existing table.

Probably in big organizations, there would be a requirement to create a new column for an already existing table. This new column could be as per the requirement linked to the applications created by the organization. In general terms, a new column added for an application in the middle of the table obviously makes no sense and is considered unethical for table modification. However, in case the situation is too worse to just proceed and implement a new column to the existing table, then here are some workaround.

Method 1: Drop and Recreate Table If No Data Available in the Table

Chances are high that a table would have been created and flushed with data and truncated. In such cases, if you find no data in the table where you need to add a new column then just drop the table and recreate with the new column added in the table structure.

Warning: Please do not drop the table in case you find data loaded in it. 

Method 2: Create a View for the Table

The simplest form is to create a view for the base table in whichever order you desire.

Method 3: Use GUI by Using Design

1. Expand the database where the table is located.

2. Right-click on “Tables” and select “Filter” to select the desired table.

3. Right-click on your table and click “Design”.

4. Here, you will be able to reorder the table structure based on your requirement.

Method 4: Create a New Table and Rename the Table by Dropping Old One

Create a new table say with “_1” with whichever table order of your choice. Once the table is created, import the entire data present in your table to a newly created “_1” table. After transferring the data to a new table, Rename the table with “exec sp_rename 'schema.old_table_name', 'new_table_name' “ command. This can produce a warning message, but nothing to worry about.

Note: Please do not use this method for critical tables.

Identifying Last Full, Differential and Log Backup Time for All Databases in SQL Server

In a consistent environment to maintain database backups on a regular basis, there is always a need for manual checks. Obvious to the fact that the alerts for failed backups would be implemented on the server. But, to manually check without logging into the server disk drive, here is an easy way to find the last Full, Differential, and Log backups in a single script.

Single script to check All databases backup times:

SELECT @@Servername AS SERVER_NAME,LEFT(D.NAME,20) AS DATABASE_NAME,

CONVERT(VARCHAR(16), MAX(CASE B.[TYPE] WHEN 'D' THEN B.BACKUP_FINISH_DATE END), 120) AS LASTFULLBACKUP

--CONVERT(VARCHAR(16), MAX(CASE B.[TYPE] WHEN 'I' THEN B.BACKUP_FINISH_DATE END), 120) AS LASTDIFFBACKUP

--CONVERT(VARCHAR(16), MAX(CASE B.[TYPE] WHEN 'L' THEN B.BACKUP_FINISH_DATE END), 120) AS LASTLOGBACKUP

--CONVERT(VARCHAR(16), MAX(CASE WHEN B.[TYPE] NOT IN ('D','I','L') THEN B.BACKUP_FINISH_DATE END), 120) AS LASTOTHERBACKUP

FROM SYS.DATABASES D

LEFT OUTER JOIN MSDB.DBO.BACKUPSET B ON D.NAME = B.DATABASE_NAME

WHERE D.NAME NOT IN ( 'TEMPDB','MASTER','MODEL','MSDB')

GROUP BY D.DATABASE_ID, D.NAME

ORDER BY CASE WHEN D.DATABASE_ID <= 4 THEN 0 ELSE 1 END, D.NAME

To check last Full backup time for all databases:

SELECT @@Servername AS SERVER_NAME,LEFT(D.NAME,20) AS DATABASE_NAME,

CONVERT(VARCHAR(16), MAX(CASE B.[TYPE] WHEN 'D' THEN B.BACKUP_FINISH_DATE END), 120) AS LASTFULLBACKUP

FROM SYS.DATABASES D

LEFT OUTER JOIN MSDB.DBO.BACKUPSET B ON D.NAME = B.DATABASE_NAME

WHERE D.NAME NOT IN ( 'TEMPDB','MASTER','MODEL','MSDB')

GROUP BY D.DATABASE_ID, D.NAME

ORDER BY CASE WHEN D.DATABASE_ID <= 4 THEN 0 ELSE 1 END, D.NAME

To check last Differential backup time for all databases:

SELECT @@Servername AS SERVER_NAME,LEFT(D.NAME,20) AS DATABASE_NAME,

CONVERT(VARCHAR(16), MAX(CASE B.[TYPE] WHEN 'I' THEN B.BACKUP_FINISH_DATE END), 120) AS LASTDIFFBACKUP

FROM SYS.DATABASES D

LEFT OUTER JOIN MSDB.DBO.BACKUPSET B ON D.NAME = B.DATABASE_NAME

WHERE D.NAME NOT IN ( 'TEMPDB','MASTER','MODEL','MSDB')

GROUP BY D.DATABASE_ID, D.NAME

ORDER BY CASE WHEN D.DATABASE_ID <= 4 THEN 0 ELSE 1 END, D.NAME

To check last Log backup time for all databases:

SELECT @@Servername AS SERVER_NAME,LEFT(D.NAME,20) AS DATABASE_NAME,

CONVERT(VARCHAR(16), MAX(CASE B.[TYPE] WHEN 'L' THEN B.BACKUP_FINISH_DATE END), 120) AS LASTLOGBACKUP

FROM SYS.DATABASES D

LEFT OUTER JOIN MSDB.DBO.BACKUPSET B ON D.NAME = B.DATABASE_NAME

WHERE D.NAME NOT IN ( 'TEMPDB','MASTER','MODEL','MSDB')

GROUP BY D.DATABASE_ID, D.NAME

ORDER BY CASE WHEN D.DATABASE_ID <= 4 THEN 0 ELSE 1 END, D.NAME

To check last other database backups (apart from Full/Differential/Log) 

SELECT @@Servername AS SERVER_NAME,LEFT(D.NAME,20) AS DATABASE_NAME,

CONVERT(VARCHAR(16), MAX(CASE WHEN B.[TYPE] NOT IN ('D','I','L') THEN B.BACKUP_FINISH_DATE END), 120) AS LASTOTHERBACKUP

FROM SYS.DATABASES D

LEFT OUTER JOIN MSDB.DBO.BACKUPSET B ON D.NAME = B.DATABASE_NAME

WHERE D.NAME NOT IN ( 'TEMPDB','MASTER','MODEL','MSDB')

GROUP BY D.DATABASE_ID, D.NAME

ORDER BY CASE WHEN D.DATABASE_ID <= 4 THEN 0 ELSE 1 END, D.NAME

You may also refer:
"How to create a Full Backup in SQL server"

"How to create a Differential Backup in SQL Server"
"How to create a transaction log backup in SQL server"

Difference Between CURRENT_TIMESTAMP and GETDATE() in SQL Server

Are both CURRENT_TIMESTAMP and GETDATE() the same in SQL Server?

Of course, both CURRENT_TIMESTAMP and the GETDATE() perform a similar functionality, but a lot of time most of the Oracle/MySQL developers feel tricky about it.

Most often while writing any SELECTs or in the Stored Procedures, I often tend to prefer GETDATE() rather than CURRENT_TIMESTAMP. And as far as I have seen, most of the developers and DBAs are much familiar in using the GETDATE().

One Difference between CURRENT_TIMESTAMP and GETDATE()

CURRENT_TIMESTAMP -  CURRENT_TIMESTAMP is an ANSI SQL function, which retrieves the date and time that is set on the system on which SQL instance is running.

SELECT CURRENT_TIMESTAMP

GETDATE() – GETDATE() is the T-SQL genre of the same function, which again retrieves the date and time that is set on the system on which SQL instance is running. 

SELECT GETDATE() 

Further, in case a column in a table is set as CURRENT_TIMESTAMP, then the function gets converted to GETDATE() if the code is generated manually from the SSMS.

Related interview questions to refer:

"Difference Between Clustered Index And Non-Clustered Index In SQL Server"

How to Check Database Usage in SQL Server

Many a times, database developers and DBAs whom I meet, come up with a common question on how to check the databases in use or not. There are many ways to identity the database usage, but one of the best ways to find I felt was to use the system dmv to monitor the database usage in SQL Server.

Most of the times, in big organizations, a lot of databases will be created as part of request. Some of them can probably turn out to be a junk in the server. In case you come across any such instances to clean up the old databases that are not in use from quite long time, then simply use the dmv command as shown:

Select DBName,

MAX(LastAccessDate) DatabaseLastAccessedOn From (

Select DB_NAME(database_id) DBName,

last_user_seek

, last_user_scan

, last_user_lookup

, last_user_update

FROM sys.dm_db_index_usage_stats where DB_NAME(database_id) not in ('Tempdb','msdb','model','system')

) AS Pvt

UNPIVOT

(LastAccessDate FOR last_user_access IN

(last_user_seek

, last_user_scan

, last_user_lookup

, last_user_update)

) AS Unpvt

GROUP BY DBName

Order by 1

From the above SELECT command, the query checks for the last_user_seek, last_user_scan, last_user_lookup and last_userupdate for the respective databases in the server and produces the result.

Note: Use the code in jobs to schedule at different times to dump in a user table and select the distinct unused databases to get the best results. 

You may also need: 

"How to Set a Database to Read-Only in SQL Server"
"SQL Server - How to Shrink Big Log File Size of a Database"