If I want to return the first two characters of a string which is the best / fastest method?
Also if I have a date and want to group by day I will use convert(varchar(10),getdate())
as our db format is in yyyy/MM/dd, which would give '2010/02/10' if I wanted to group by month I would use varchar(7) instead to give '2010/02'
Is there a faster way to achieve the same thing?
The results for the Compute Scalar portion of the query are identical for using LEFT(t.col, 2) and CONVERT(VARCHAR(2), t.col):
Estimated CPU Cost: 0.0283193
Estimated I/O Cost: 0
Estimated Number of Rows: 283193
Estimated Operator Cost: 0.028 (0%)
SQL Server 2005
My assumption is that LEFT is a synonym for CAST/CONVERT...
There's no difference in performance between these two. However - and this is not entirely clear from your question - if you are putting this into a WHERE condition as opposed to the column output of your query, both are very, very bad.
String functions such as LEFT, SUBSTRING and CONVERT (when used for casting) are non-sargable, which means that an ordinarily efficient index seek will be turned into an expensive full index scan. If you want to query on the first two characters of a field, you should write the query as such:
SELECT Col1, Col2, ...
FROM Table
WHERE StringCol LIKE 'AB%'
Again, this only applies for filters; if the LEFT/SUBSTRING is in the column output (i.e. SELECT LEFT(Col1, 2)) then you need not worry about the performance.
1) If I want to return the first two characters of a string which is the best / fastest method?
LEFT('YourString', 2) is IMHO the more natural, more readable in terms of shouting and screaming it's intention. I really don't think this will be a cause of poor performance - much more likely to hit poor performance from a poorly constructed query, missing indexes etc. I'd just stick with what feels natural, and simplest.
2) Also if I have a date and want to group by day I will use convert(varchar(10),getdate()) as our db format is in yyyy/MM/dd, which would give '2010/02/10' if I wanted to group by month I would use varchar(7) instead to give '2010/02'
When converting dates, I'd always specify the last parameter of CONVERT which is the style. e.g.
-- 112 = ISO format. This would give yyyyMM
SELECT CONVERT(VARCHAR(6), GETDATE(), 112)
Note this is a good example of what I said above re: other areas more likely to perform worse. If you apply the CONVERT to a column in a query's WHERE clause, it prevents the index on that column being used. So instead, I'd change the query to have a date range specified with the start date = the start of the month and the end date = the 1st day of the next month:
SELECT *
FROM SomeTable
WHERE DateField >= @StartOfMonth AND DateField < @FirstDayOfNextMonth
This would use an index on DateField giving better performance than a CONVERT approach
A1. LEFT of course, how could you do less work than use a function that explicitly does only that?
A2. Don't store DATEs as varchars, store them as dates and index them, this makes ordering lightening fast, and you can use DATEPART to get at certain components of the date.
test it out:
DECLARE @x int
,@RunDate datetime
,@y char(2)
,@z int
,@total int
,@tries int
SELECT @z=1,@total=0,@tries=20
WHILE @z<=@tries
BEGIN
SELECT @RunDate=GETDATE(),@x=1,@z=@z+1
WHILE @x<500000
BEGIN
SET @x=@x+1
SELECT @x=@x+1,@y=LEFT('Chgffgjjx',2)
END
PRINT RIGHT(' '+CONVERT(varchar(2),@z-1),2)+' - LEFT() Took: '+CONVERT(varchar(20),datediff(ms,@RunDate,GETDATE()))+' milliseconds'
SET @total=@total+CONVERT(varchar(20),datediff(ms,@RunDate,GETDATE()))
END
PRINT 'LEFT() average: '+CONVERT(varchar(20),@total/(1.0+@tries))
SELECT @z=1,@total=0,@tries=20
WHILE @z<=@tries
BEGIN
SELECT @RunDate=GETDATE(),@x=1,@z=@z+1
WHILE @x<500000
BEGIN
SET @x=@x+1
SELECT @x=@x+1,@y=CONVERT(varchar(2),'Chgffgjjx')
END
PRINT RIGHT(' '+CONVERT(varchar(2),@z-1),2)+' - CONVERT() Took: '+CONVERT(varchar(20),datediff(ms,@RunDate,GETDATE()))+' milliseconds'
SET @total=@total+CONVERT(varchar(20),datediff(ms,@RunDate,GETDATE()))
END
PRINT 'CONVERT() average: '+CONVERT(varchar(20),@total/(1.0+@tries))
OUTPUT:
1 - LEFT() Took: 563 milliseconds
2 - LEFT() Took: 390 milliseconds
3 - LEFT() Took: 406 milliseconds
4 - LEFT() Took: 390 milliseconds
5 - LEFT() Took: 390 milliseconds
6 - LEFT() Took: 390 milliseconds
7 - LEFT() Took: 393 milliseconds
8 - LEFT() Took: 390 milliseconds
9 - LEFT() Took: 390 milliseconds
10 - LEFT() Took: 486 milliseconds
11 - LEFT() Took: 686 milliseconds
12 - LEFT() Took: 453 milliseconds
13 - LEFT() Took: 390 milliseconds
14 - LEFT() Took: 406 milliseconds
15 - LEFT() Took: 403 milliseconds
16 - LEFT() Took: 410 milliseconds
17 - LEFT() Took: 560 milliseconds
18 - LEFT() Took: 546 milliseconds
19 - LEFT() Took: 593 milliseconds
20 - LEFT() Took: 390 milliseconds
LEFT() average: 429.7619047619047
1 - CONVERT() Took: 440 milliseconds
2 - CONVERT() Took: 513 milliseconds
3 - CONVERT() Took: 393 milliseconds
4 - CONVERT() Took: 453 milliseconds
5 - CONVERT() Took: 390 milliseconds
6 - CONVERT() Took: 406 milliseconds
7 - CONVERT() Took: 420 milliseconds
8 - CONVERT() Took: 406 milliseconds
9 - CONVERT() Took: 390 milliseconds
10 - CONVERT() Took: 390 milliseconds
11 - CONVERT() Took: 406 milliseconds
12 - CONVERT() Took: 390 milliseconds
13 - CONVERT() Took: 626 milliseconds
14 - CONVERT() Took: 593 milliseconds
15 - CONVERT() Took: 423 milliseconds
16 - CONVERT() Took: 406 milliseconds
17 - CONVERT() Took: 450 milliseconds
18 - CONVERT() Took: 486 milliseconds
19 - CONVERT() Took: 406 milliseconds
20 - CONVERT() Took: 390 milliseconds
CONVERT() average: 417.9523809523809
they seem very close to me
As for your 2nd question: Don't use this CONVERT() method if you're operating on a larger table. A more effective method would to use something like
GROUP BY DATEADD(DAY, DATEDIFF(DAY, 0, < your datetime column >), 0)
to group by day and likewise
GROUP BY DATEADD(MONTH, DATEDIFF(MONTH, 0, < your datetime column >), 0)
to group by month.