Select range of names based on first character

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Select range of names based on first character

Author

Topic

nathans
Aged Yak Warrior

938 Posts

Posted - 2006-08-22 : 20:51:45

Guys,
I have the following working, but Im curious if you see any opportunities for performnace increase. You see a better way to achieve the same resultset?

Basically, I just want to take in a start char and end char and return the names where the first char falls within the range.

declare @table table (LastName varchar(25))
insert into @table
    select 'Adams' union all
    select 'Bryant' union all
    select 'Cams' union all
    select 'Daniels' union all
    select 'Edwards'


declare @upper char(1),
        @lower char(1)

select  @upper = 'C',
        @lower = 'A'

select  *
from    @table
where   Lastname like '[' + @Lower + '-' + @Upper + ']%'

Thanks for any input!

Nathan Skerl

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-08-22 : 21:53:32

Unless the table has an index, it will just be a table scan.

The code below shows a different approach after adding an index to the table. The second query below has a better query plan, a clustered index seek, compared to a nested loop for the original query.


declare @table table (LastName varchar(25) primary key clustered )
insert into @table
    select 'Adams' union all
    select 'Bryant' union all
    select 'Cams' union all
    select 'Daniels' union all
    select 'Edwards'

declare @upper char(1),
        @lower char(1)

select  @upper = 'C',
        @lower = 'A'

-- Nested loop scan
select  *
from    @table
where   Lastname like '[' + @Lower + '-' + @Upper + ']%'

-- Clusterd Index Seek
select  *
from    @table
where 
	Lastname> @lower  and
	-- Less than next character after @UPPER
	Lastname < char(ascii(@upper)+1)

CODO ERGO SUM

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2006-08-23 : 02:51:54

Depending on the size of your clustered column you could also add another column with the first letter only from the last name and add a (clustered) index on that one. It might not server your purpose on this exact problem but it's a neat little trick I've used a few times.

declare @table table (LastName varchar(25), Initial varchar(1) primary key clustered)
insert into @table
    select 'Adams', 'A' union all select 'Bryant', 'B' union all
    select 'Cams', 'C' union all  select 'Daniels', 'D' union all
    select 'Edwards', 'E'

declare @upper char(1), @lower char(1)
select  @upper = 'C', @lower = 'A'

select  * from @table
where Initial BETWEEN @Lower AND @Upper

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"

nathans
Aged Yak Warrior

938 Posts

Posted - 2006-08-23 : 12:05:24

Big thanks to both of you. Great suggestions!

Nathan Skerl

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources