Query with index

• Helical Johan

  Ten Centuries

  Points: 1033

  More actions

  November 29, 2009 at 5:26 pm

  #213045

  Hi, I have a table that have a index, a field may be null.

  When I am doing a query with this index, it doesn't used, because I cast the null field.

  e.g:

  Persons

  identification varchar(20) NOT NULL,

  name varchar(30) NULL,

  address varchar(100) NULL

  telephone varchar(20) NULL

  Unique Index

  identification, name

  Query

  select *

  from Persons with (nolock)

  where identification = '52801333'

  and isnull(name, '') = ''

  How can I write the query and use my index?

  Thanks.....

• Lowell

  SSC Guru

  Points: 323546

  More actions

  November 29, 2009 at 5:33 pm

  #1085205

  to take advantage of an index, you can't use any functions on the column in the WHERE statement; the isnull(name, '') = '' doesnt allow a Sargable search, so a table scan has to be used.

  i think this will use the index with an index scan; still a scan, bet better than a table scan.

  select *

  from Persons with

  where identification = '52801333'

  and name IS NULL OR name= ''

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• nickmail1109

  Grasshopper

  Points: 11

  More actions

  November 30, 2009 at 12:42 am

  #1085267

  thanks, i have the same problem!

  Công ty Đất Quảng - Cong ty Dat Quang - http://datquangda.com.vn
  Bất động sản, mua bán nhà đất khu đô thị mới Nam An Khánh Hà Nội.
  Trụ sở: Phòng 303 nhà N6E – Trung Hoà Nhân Chính – Thanh Xuân – Hà Nội
  Văn phòng giao dịch: Số nhà 10, ngách 23, ngõ 61, Trần Duy Hưng, Trung Hòa, Cầu Giấy, Hà Nội
  Điện thoai: 04.3556 2516 - 3556 2552 - 2247 5733
  Fax: 04.3556 2566 - 3556 2140
  Email:datquangda@gmail.com
  Website: http://www.datquangda.com.vn