August 3, 2016 at 4:29 pm
Hi Experts,
Need some help here. How can I make use of my non-clustered index in the below scenario where I have ISNULL() funciton on a column on which non-clustered has been created. How can I go for index seek here if I look into actual execution plan?
SET NOCOUNT ON;
DECLARE @UpperLimit INT;
SET @UpperLimit = 1000000;
WITH n AS
(
SELECT
x = ROW_NUMBER() OVER (ORDER BY s1.[object_id])
FROM sys.all_objects AS s1
CROSS JOIN sys.all_objects AS s2
CROSS JOIN sys.all_objects AS s3
)
SELECT c1 = x, c2=ASCII(x), c3 = CASE WHEN X%10=0 THEN NULL ELSE 'CP-961080' END,c4=2016
INTO dbo.Numbers
FROM n
WHERE x BETWEEN 1 AND @UpperLimit;
GO
--select * From numbers
update numbers set c3 = 'CP-961080' where c3 is not null
UPDATE numbers SET c3 = 'CP-100'
where c1 between 101 and 109
UPDATE numbers SET c3 = 'CP-200'
where c1 between 501 and 509
UPDATE numbers SET c3 = 'CP-300'
where c1 between 1001 and 1009
-- enforce not null on c1 column
ALTER TABLE numbers ALTER COLUMN c1 bigint NOT NULL;
-- create a primary key
ALTER TABLE numbers ADD PRIMARY KEY (c1);
-- create a non-clustered index on c3
create index idx_nc_c3 on numbers(c3) include(c2);
--select * from numbers
select c1,c2,c3 From numbers
where isnull(c3,'<Blank>') <> 'CP-961080'
Thanks,
Sam
August 3, 2016 at 4:45 pm
1) Don't use ISNULL, you don't need it there anyway:
select c1,c2,c3 From numbers
where c3 <> 'CP-961080'
2) A nonclustered index lookup would be very inefficient here, although you might be able to force it using a "WITH ( INDEX(name) )" hint.
3) If the table table is always / almost always searched by c3, then cluster the table on c3. If it's a large table, define the clus index before loading the table.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
August 3, 2016 at 10:06 pm
Thanks Scott.
Below code worked for me
select c1,c2,c3 From numbers
where c3 <> 'CP-961080'
union all
select c1,c2,c3 From numbers
where c3 is null
August 4, 2016 at 7:48 am
What about using an OR?
select c1,c2,c3 From numbers
where c3 <> 'CP-961080'
OR c3 is null
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply