WikiDBA

Sometimes it happened with a developer or even with a DBA, a query seems to be a very perfect over time and time, having appropriate index, but taking long time to show result. As per my understanding in multiple scenario there may be various reasons. But one very common and widely happened error is data type conversion issue. As we know Data types can be converted either implicitly or explicitly. Implicit conversions are not visible to the user. SQL Server automatically converts the data from one data type to another while Explicit conversions use the CAST or CONVERT functions. The CAST and CONVERT functions convert a value (a local variable, a column, or another expression) from one data type to another.

Here, I am pointing about Implicit conversions, it’s a silent killer and it has been seen this literally bring a system to its knees by causing deadlocks during high load, CPU at maximum utilization, performance issues and people started blaming on server, but reason is something different. Let see below example,

Create Procedure usp_VirendraTest @ID VarChar(10)
As
Select ID,Name,Fname,BloodGroup,Dept,Desig From tblVirendraTest Where ID = @ID

The code looks very adequate and works good at initial stage but it starts show their own color when table’s rows count become multi millions. The reason is ID column belongs to INT data type and Developers and even DBAs get confused and write a procedure with the wrong data type in this scenario. Now problem started, every time SQL Server has to look for a ID values and it has to convert @ID from a VarChar to an Int. This is an implicit conversion of data type. Internally, SQL Server uses a convert function perform this operation. When this happens, SQL Server cannot use an index effectively. it has to convert the value for each and every row and as a resultant SQL Server scans the entire table for the value. This takes time and, under default locking modes, places a share lock on the entire table preventing other processes from updating/Inserting/Deleting records while the scan is taking place.

The solution for this type of problem is use of CAST or CONVERT.

Here is the Data type conversions chart from Microsoft

Thanks for happy reading, Please comment and suggest the better ways. JJ