This article is half-done without your Comment! *** Please share your thoughts via Comment ***
In this post, I am sharing performance test of the problem of mismatched column data type in SQL Server.
The Database Designer is also defining a wrong mismatched column data type by giving different data types to the same column for many tables.
For example, empid column -> assigned INT in tbl_emp table and -> assigned BIGINT in tbl_dept table. Because of this kind of problem, your query will execute slowly, and it requires implicit data conversion for each record.
Another example is, your column data type is VARCHAR, and you are checking N’DATA in WHERE clause which is NVARCHAR, so this is also required implicit data conversion.
Please check below full demonstration and, test it yourself:
Create a sample table, where [id] column type is varchar:
|
1 2 3 4 5 6 |
CREATE TABLE tbl_DiffDataTypes ( id varchar(25) ,name VARCHAR(25) ,constraint pk_tbl_DiffDataTypes_id primary key (id) ) GO |
Insert few dummy records:
|
1 2 3 4 5 6 7 8 |
DECLARE @i INT,@max INT SET @i =1 SET @max =99999 WHILE @i<=@max BEGIN INSERT INTO tbl_DiffDataTypes VALUES(@i,'dbrnd.com') SET @i=@i+1 END |
Please enable STATISTICS:
|
1 2 |
SET STATISTICS IO ON SET STATISTICS TIME ON |
Test WHERE filter with VARCHAR value: (Only 3 logical reads)
|
1 |
SELECT * FROM tbl_DiffDataTypes WHERE id = '1' |
Result of Statistics:
|
1 |
Table 'tbl_DiffDataTypes'. Scan count 0, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. |
Test WHERE filter with NVARCHAR value: (62 logical reads)
|
1 |
SELECT * FROM tbl_DiffDataTypes WHERE id = N'1' |
Result of Statistics:
|
1 |
Table 'tbl_DiffDataTypes'. Scan count 1, logical reads 62, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. |
Test WHERE filter with INTEGER value: (621 logical reads)
|
1 |
SELECT * FROM tbl_DiffDataTypes WHERE id = 1 |
Result of Statistics:
|
1 |
Table 'tbl_DiffDataTypes'. Scan count 1, logical reads 621, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. |
Leave a Reply