Watch Out Pt 1 - Ensure you utilise appropriate data types and use explicit CAST/CONVERT in queries
I will use the title of "Watch Out" to write about things I see customers implement "incorrectly" or not according to best practices.
During a delivery of the SQL Server 2005 Performance Tuning and Optimisation course one of the attendees showed me an example of a simple query and he was wondering why the query was utilising a table scan as opposed to an index seek operation as the column had an index.
The following example illustrates the issue:
-- Create a simple table with id field using char data type instead of int
CREATE TABLE [dbo].[Table_1](
[id] [char]
(10) NOT NULL,
[fname] [varchar](50) NOT NULL,
[lname] [varchar](50) NOT NULL,
CONSTRAINT [PK_Table_1] PRIMARY KEY CLUSTERED
([id] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]
-- Populate table with some data
INSERT
INTO Table_1(id,fname,lname) VALUES ('1','Test','Testing')
INSERT
INTO Table_1(id,fname,lname) VALUES ('2','Test','Testing')
INSERT
INTO Table_1(id,fname,lname) VALUES ('3','Test','Testing')
INSERT
INTO Table_1(id,fname,lname) VALUES ('4','Test','Testing')
INSERT
INTO Table_1(id,fname,lname) VALUES ('5','Test','Testing')
INSERT
INTO Table_1(id,fname,lname) VALUES ('6','Test','Testing')
INSERT
INTO Table_1(id,fname,lname) VALUES ('7','Test','Testing')
INSERT
INTO Table_1(id,fname,lname) VALUES ('8','Test','Testing')
INSERT
INTO Table_1(id,fname,lname) VALUES ('9','Test','Testing')
INSERT
INTO Table_1(id,fname,lname) VALUES ('10','Test','Testing')
INSERT
INTO Table_1(id,fname,lname) VALUES ('11','Test','Testing')
INSERT
INTO Table_1(id,fname,lname) VALUES ('12','Test','Testing')
INSERT
INTO Table_1(id,fname,lname) VALUES ('13','Test','Testing')
INSERT
INTO Table_1(id,fname,lname) VALUES ('14','Test','Testing')
INSERT
INTO Table_1(id,fname,lname) VALUES ('15','Test','Testing')
-- Now write a query as one may do to retrieve data based on particular ID
-- A query developer may assume the field of id to be an int and may write the query as below
-- This will result in poor execution plan as it will use a clustered index scan as opposed to a index seek operation
-- Select the below query and press Ctrl + L
select
id,fname,lname
from Table_1
where id = 10
To get the above query to utilise the index seek operation you can do the following:
- Put quotes around the 10 e.g.:
select
id,fname,lname
from Table_1
where id = '10'
- Use CAST/CONVERT operator to ensure it is sent to SQL Server as appropriate data type.
select
id,fname,lname
from Table_1
where id = CONVERT(char,10)
- Modify the id field data type in the table to be int instead of char(10).
So as can be seen from the above simple example it is important to utilise appropriate data types and ensure your queries utilise the matching data type as the base tables they are querying.
Happy SQLing.
