All geek questions in one place

On SQL Server (2008), if I want to filter a row field that starts with something, which is the best way?

In several SQL queries, I need to check if a field starts with a character. There are several ways to do this, which is better in performance / standard?

I usually use

tb.field LIKE 'C%'

but i can also use

 LEFT(LTRIM(tb.Field),1) = 'C'

I know well the use of each case, but not in terms of performance.

+6
source share
3 answers

I would go with the first LIKE C% , it will use the index in the field, if any, and not for a full table scan.

If you really need to enable LTRIM space trimming in the query, you can create a constant computed column with a LEFT(LTRIM(tb.Field), 1) value LEFT(LTRIM(tb.Field), 1) and put an index on it.

+5
source

LIKE 'C%' will work better than a LEFT(LTRIM()) .

The LIKE predicate can still use a helper index to retrieve the data you are looking for. I

However, when SQL Server encounters LEFT(LTRIM(tb.Field), 1) = 'C' , the database cannot determine what you mean. To match, SQL Server must check each line, LTRIM data, and then check the first character. The end result is most likely a full table scan.

+4
source

The first request is slightly larger than the other. I measured it using a query speed measurement script. Try it yourself:

 DECLARE @Measurements TABLE( MeasuredTime INT NOT NULL ) DECLARE @ExecutionTime INT DECLARE @TimesMeasured INT SET @TimesMeasured = 0 WHILE @TimesMeasured < 1000 BEGIN DECLARE @StartTime DATETIME SET @StartTime = GETDATE() -- your select .. or what every query INSERT INTO @Measurements SELECT DATEDIFF(millisecond, @StartTime, getdate()) SET @TimesMeasured = @TimesMeasured + 1 END SELECT @AvgTime = AVG(MeasuredTime) FROM @Measurements
0
source

Source: https://habr.com/ru/post/902890/

More articles:


All Articles