qry wont use index

I've got a query which just won't use an index, instead, it does a table scan on a specific column. Basically the query is:

select count(*)
from table1 t1
inner join table2 t2 on (t1.myref = t2.myref)
where myint2 in (1,2,3)
and (myval between -1 and -2 or myint1 = 1)

In actual work, the 'myval between' uses variables which could be null, same goes for myint1. The values above are the values that I use to examine. According to the analyzer, a table scan is performed on myint2 (the in stuff), however, there's an index on myint2 also in combination with myint1.
I've tried to re-create the setup by creating the table1 and table2, including the indexes. Unfortunately, in the re-created setup, the index is used.

EDIT: Oddly, the OR ruins the plan to use an index: when leaving out the 'and (myval...)', the index is used.

What's going on?The first OR operation in a query (in this case, the IN clause) makes an index scan difficult. The second OR operation makes the index scan impossible (at least using present technology).

-PatP|||how's that? when changing
where myint2 in (1,2,3)
into
where myint2 = 1
I still get a table scan.

Besides, I do get an index-scan in the re-created setup.
I tried a defrag, reindex AND recompute statistics. It just wont show up.
I really don't get it.|||Hogtie the optimizer and try it. Use SET FORCEPLAN ON (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_set-set_8mni.asp) and hint the index. See if you get a result set while you are young enough to still care.

Let me know what you find out, I'm curious now!

-PatP|||hah! now your stuck! :>
the optimizer came up empty and even with the index hint (0) it still does a full table scan. The trouble is that the column resides in the facts-table (its a warehouse db) and in production it gets a 80% hit according to the exection plan (76% in dev). I'm beginning to wonder if the amount of indexes specified on the facts is too much (47 columns, 26 indexes).|||oh btw: it takes 14 minutes to come back with a rowcount of 85.
The forceplan option and index(0) hint does take the percentage down to 28% but it's still a table scan (forcedindex). btw: in the analyzer, execution plan, I've got these little round yellow circles on the tablescan, index, nested loops etc. I don't see 'em in BOL. Happen to know what their about?|||Unfortunately, nothing changed over the weekend.
I even changed the complete query to a simple count(*) with a single where-clause in which a single value is specified.
Still a full table scan, even with index-hint and forceplan set.

QA vs sproc mystery

Here's a poser (at least to me!)
When I run a select statement in QA the statement uses available indexes
(Index Scan) and is quite fast, but when I take the same script, place it in
a sproc, and execute the sproc, it uses a Table Scan rather than an Index
S on the Juvenile table - killing performance.
Any pointers would be appreciated
here's the script:
Select
J.JuvenileID,
(J.JuvLName + ', ' + J.JuvFName + ' (' + convert(varchar(10), J.juvDOB, 1)
+ ')' ) as JuvFullName,
Convert(varchar(10), J.JuvDOB, 1) as JuvDOB
From Juvenile J
Inner Join Placement P on J.JuvenileID = P.JuvenileID
Where juvPlaSite = @.SitLocationCode
and juvPlaEndDate IS NULL
and J.JuvLName LIKE CASE When (@.JuvenileLNameBeginsWith = '' OR
@.JuvenileLNameBeginsWith = 'ALL') then J.JuvLName ELSE
@.JuvenileLNameBeginsWith + '%' END
Order By JuvFullNameWhat column(s) is indexed in Juvenile?|||Can you post the sp?
AMB
"Dazed and Confused" wrote:

> Here's a poser (at least to me!)
> When I run a select statement in QA the statement uses available indexes
> (Index Scan) and is quite fast, but when I take the same script, place it
in
> a sproc, and execute the sproc, it uses a Table Scan rather than an Index
> S on the Juvenile table - killing performance.
> Any pointers would be appreciated
> here's the script:
> Select
> J.JuvenileID,
> (J.JuvLName + ', ' + J.JuvFName + ' (' + convert(varchar(10), J.juvDOB, 1
)
> + ')' ) as JuvFullName,
> Convert(varchar(10), J.JuvDOB, 1) as JuvDOB
> From Juvenile J
> Inner Join Placement P on J.JuvenileID = P.JuvenileID
> Where juvPlaSite = @.SitLocationCode
> and juvPlaEndDate IS NULL
> and J.JuvLName LIKE CASE When (@.JuvenileLNameBeginsWith = '' OR
> @.JuvenileLNameBeginsWith = 'ALL') then J.JuvLName ELSE
> @.JuvenileLNameBeginsWith + '%' END
> Order By JuvFullName
>|||"Dazed and Confused" <Dazed and Confused@.discussions.microsoft.com> wrote in
message news:C09CDD00-02A2-4199-9A1B-8420771CDF43@.microsoft.com...
> Here's a poser (at least to me!)
> When I run a select statement in QA the statement uses available indexes
> (Index Scan) and is quite fast, but when I take the same script, place it
in
> a sproc, and execute the sproc, it uses a Table Scan rather than an Index
> S on the Juvenile table - killing performance.
Obviate parameter sniffing by assigning parameters to local variables and
using the local variables in the query.

q; unique which allows multiple nulls

Hi,
I have a non-cluster index on the filed, I need to make it non-cluster index
and unique which allows multiple nulls, how should I do this with a sql
statement?Check constraint? Trigger?
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:1D7BAC44-AFE0-409D-8446-57179013153E@.microsoft.com...
> Hi,
> I have a non-cluster index on the filed, I need to make it non-cluster
> index
> and unique which allows multiple nulls, how should I do this with a sql
> statement?
>
>|||How should I write that check contraint? Any example?
"Mike C#" wrote:

> Check constraint? Trigger?
> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
> news:1D7BAC44-AFE0-409D-8446-57179013153E@.microsoft.com...
>
>|||Hi Jim
What is your goal? If it is primarily to enforce uniqueness of all non-NULL
values you can create a view that excludes the NULLs, and then build a
unique index on the view.
CREATE TABLE T1
(
key_col INT NULL,
data_col CHAR(1) NOT NULL
)
GO
CREATE VIEW V1 WITH SCHEMABINDING
AS
SELECT key_col FROM dbo.T1 WHERE key_col IS NOT NULL
GO
CREATE UNIQUE CLUSTERED INDEX idx_uc_key_col ON V1(key_col)
INSERT INTO T1 VALUES(1, 'a')
INSERT INTO T1 VALUES(1, 'b')
INSERT INTO T1 VALUES(2, 'a')
INSERT INTO T1 VALUES(NULL, 'a')
INSERT INTO T1 VALUES(NULL, 'a')
INSERT INTO T1 VALUES(NULL, 'b')
SELECT * FROM T1
GO
HTH
Kalen Delaney, SQL Server MVP
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:756F641A-0B2B-4AB6-8BDE-CB9F38372886@.microsoft.com...[vbcol=seagreen]
> How should I write that check contraint? Any example?
> "Mike C#" wrote:
>|||A UNIQUE index (CONSTRAINT) will only allow 1 NULL value per field
combination included in the index.
One field index = one NULL value, two field index = 3 rows with NULL index
values, etc.
Arnie Rowland
"To be successful, your heart must accompany your knowledge."
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:1D7BAC44-AFE0-409D-8446-57179013153E@.microsoft.com...
> Hi,
> I have a non-cluster index on the filed, I need to make it non-cluster
> index
> and unique which allows multiple nulls, how should I do this with a sql
> statement?
>
>|||>> two field index = 3 rows with NULL index <<
Maybe I'm not understanding what you're saying here, but by my understanding
of your statement, this is not true at all.
For a two-column unique index, there can be only one row where both columns
are null, but there is no limit to the number of nulls in either one of the
columns.
The following code will only give you one error, and all the other rows will
be inserted just fine:
create table two_column_index (col1 int null, col2 int null, unique (col1,
col2))
go
insert into two_column_index select null, null
insert into two_column_index select null, null -- error
insert into two_column_index select 1, null
insert into two_column_index select 2, null
insert into two_column_index select 3, null
insert into two_column_index select 4, null
insert into two_column_index select null, 1
insert into two_column_index select null, 2
insert into two_column_index select null, 3
insert into two_column_index select null, 4
go
select * from two_column_index
HTH
Kalen Delaney, SQL Server MVP
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:OqC977TqGHA.928@.TK2MSFTNGP04.phx.gbl...
>A UNIQUE index (CONSTRAINT) will only allow 1 NULL value per field
>combination included in the index.
> One field index = one NULL value, two field index = 3 rows with NULL index
> values, etc.
> --
> Arnie Rowland
> "To be successful, your heart must accompany your knowledge."
>
> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
> news:1D7BAC44-AFE0-409D-8446-57179013153E@.microsoft.com...
>|||You're right Kalen.
I sat down after coming in from a wonderful hike in the Cascades, and my
brain was perhaps a bit fried. Mr. Henry (a representative of the Full Sail
Ale Company) and I did not explain ourselves well.
What I meant to convey is that given the same set of values (i.e., NULL and
[1], NULL and [2], etc.) that there are 3 possible combinations that
would
be allowed.
I see how my comment indicated something quite untoward. I did NOT intend to
convey that only 3 rows would be allowed to contain NULL values in the
entire table -that would be quite unfortunate if it were the reality.
Thanks for calling it to attention lest anyone become confused from my
comment.
--
Arnie Rowland
"To be successful, your heart must accompany your knowledge."
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:e1TEB9UqGHA.4208@.TK2MSFTNGP04.phx.gbl...
> Maybe I'm not understanding what you're saying here, but by my
> understanding of your statement, this is not true at all.
> For a two-column unique index, there can be only one row where both
> columns are null, but there is no limit to the number of nulls in either
> one of the columns.
> The following code will only give you one error, and all the other rows
> will be inserted just fine:
> create table two_column_index (col1 int null, col2 int null, unique (col1,
> col2))
> go
> insert into two_column_index select null, null
> insert into two_column_index select null, null -- error
> insert into two_column_index select 1, null
> insert into two_column_index select 2, null
> insert into two_column_index select 3, null
> insert into two_column_index select 4, null
> insert into two_column_index select null, 1
> insert into two_column_index select null, 2
> insert into two_column_index select null, 3
> insert into two_column_index select null, 4
> go
> select * from two_column_index
> --
> HTH
> Kalen Delaney, SQL Server MVP
>
> "Arnie Rowland" <arnie@.1568.com> wrote in message
> news:OqC977TqGHA.928@.TK2MSFTNGP04.phx.gbl...
>

q; unique which allows multiple nulls

Hi,
I have a non-cluster index on the filed, I need to make it non-cluster index
and unique which allows multiple nulls, how should I do this with a sql
statement?Check constraint? Trigger?
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:1D7BAC44-AFE0-409D-8446-57179013153E@.microsoft.com...
> Hi,
> I have a non-cluster index on the filed, I need to make it non-cluster
> index
> and unique which allows multiple nulls, how should I do this with a sql
> statement?
>
>|||How should I write that check contraint? Any example?
"Mike C#" wrote:
> Check constraint? Trigger?
> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
> news:1D7BAC44-AFE0-409D-8446-57179013153E@.microsoft.com...
> > Hi,
> >
> > I have a non-cluster index on the filed, I need to make it non-cluster
> > index
> > and unique which allows multiple nulls, how should I do this with a sql
> > statement?
> >
> >
> >
>
>|||Hi Jim
What is your goal? If it is primarily to enforce uniqueness of all non-NULL
values you can create a view that excludes the NULLs, and then build a
unique index on the view.
CREATE TABLE T1
(
key_col INT NULL,
data_col CHAR(1) NOT NULL
)
GO
CREATE VIEW V1 WITH SCHEMABINDING
AS
SELECT key_col FROM dbo.T1 WHERE key_col IS NOT NULL
GO
CREATE UNIQUE CLUSTERED INDEX idx_uc_key_col ON V1(key_col)
INSERT INTO T1 VALUES(1, 'a')
INSERT INTO T1 VALUES(1, 'b')
INSERT INTO T1 VALUES(2, 'a')
INSERT INTO T1 VALUES(NULL, 'a')
INSERT INTO T1 VALUES(NULL, 'a')
INSERT INTO T1 VALUES(NULL, 'b')
SELECT * FROM T1
GO
--
HTH
Kalen Delaney, SQL Server MVP
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:756F641A-0B2B-4AB6-8BDE-CB9F38372886@.microsoft.com...
> How should I write that check contraint? Any example?
> "Mike C#" wrote:
>> Check constraint? Trigger?
>> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
>> news:1D7BAC44-AFE0-409D-8446-57179013153E@.microsoft.com...
>> > Hi,
>> >
>> > I have a non-cluster index on the filed, I need to make it non-cluster
>> > index
>> > and unique which allows multiple nulls, how should I do this with a sql
>> > statement?
>> >
>> >
>> >
>>|||A UNIQUE index (CONSTRAINT) will only allow 1 NULL value per field
combination included in the index.
One field index = one NULL value, two field index = 3 rows with NULL index
values, etc.
--
Arnie Rowland
"To be successful, your heart must accompany your knowledge."
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:1D7BAC44-AFE0-409D-8446-57179013153E@.microsoft.com...
> Hi,
> I have a non-cluster index on the filed, I need to make it non-cluster
> index
> and unique which allows multiple nulls, how should I do this with a sql
> statement?
>
>|||>> two field index = 3 rows with NULL index <<
Maybe I'm not understanding what you're saying here, but by my understanding
of your statement, this is not true at all.
For a two-column unique index, there can be only one row where both columns
are null, but there is no limit to the number of nulls in either one of the
columns.
The following code will only give you one error, and all the other rows will
be inserted just fine:
create table two_column_index (col1 int null, col2 int null, unique (col1,
col2))
go
insert into two_column_index select null, null
insert into two_column_index select null, null -- error
insert into two_column_index select 1, null
insert into two_column_index select 2, null
insert into two_column_index select 3, null
insert into two_column_index select 4, null
insert into two_column_index select null, 1
insert into two_column_index select null, 2
insert into two_column_index select null, 3
insert into two_column_index select null, 4
go
select * from two_column_index
--
HTH
Kalen Delaney, SQL Server MVP
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:OqC977TqGHA.928@.TK2MSFTNGP04.phx.gbl...
>A UNIQUE index (CONSTRAINT) will only allow 1 NULL value per field
>combination included in the index.
> One field index = one NULL value, two field index = 3 rows with NULL index
> values, etc.
> --
> Arnie Rowland
> "To be successful, your heart must accompany your knowledge."
>
> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
> news:1D7BAC44-AFE0-409D-8446-57179013153E@.microsoft.com...
>> Hi,
>> I have a non-cluster index on the filed, I need to make it non-cluster
>> index
>> and unique which allows multiple nulls, how should I do this with a sql
>> statement?
>>
>|||You're right Kalen.
I sat down after coming in from a wonderful hike in the Cascades, and my
brain was perhaps a bit fried. Mr. Henry (a representative of the Full Sail
Ale Company) and I did not explain ourselves well.
What I meant to convey is that given the same set of values (i.e., NULL and
[1], NULL and [2], etc.) that there are 3 possible combinations that would
be allowed.
I see how my comment indicated something quite untoward. I did NOT intend to
convey that only 3 rows would be allowed to contain NULL values in the
entire table -that would be quite unfortunate if it were the reality.
Thanks for calling it to attention lest anyone become confused from my
comment.
--
Arnie Rowland
"To be successful, your heart must accompany your knowledge."
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:e1TEB9UqGHA.4208@.TK2MSFTNGP04.phx.gbl...
>> two field index = 3 rows with NULL index <<
> Maybe I'm not understanding what you're saying here, but by my
> understanding of your statement, this is not true at all.
> For a two-column unique index, there can be only one row where both
> columns are null, but there is no limit to the number of nulls in either
> one of the columns.
> The following code will only give you one error, and all the other rows
> will be inserted just fine:
> create table two_column_index (col1 int null, col2 int null, unique (col1,
> col2))
> go
> insert into two_column_index select null, null
> insert into two_column_index select null, null -- error
> insert into two_column_index select 1, null
> insert into two_column_index select 2, null
> insert into two_column_index select 3, null
> insert into two_column_index select 4, null
> insert into two_column_index select null, 1
> insert into two_column_index select null, 2
> insert into two_column_index select null, 3
> insert into two_column_index select null, 4
> go
> select * from two_column_index
> --
> HTH
> Kalen Delaney, SQL Server MVP
>
> "Arnie Rowland" <arnie@.1568.com> wrote in message
> news:OqC977TqGHA.928@.TK2MSFTNGP04.phx.gbl...
>>A UNIQUE index (CONSTRAINT) will only allow 1 NULL value per field
>>combination included in the index.
>> One field index = one NULL value, two field index = 3 rows with NULL
>> index values, etc.
>> --
>> Arnie Rowland
>> "To be successful, your heart must accompany your knowledge."
>>
>> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
>> news:1D7BAC44-AFE0-409D-8446-57179013153E@.microsoft.com...
>> Hi,
>> I have a non-cluster index on the filed, I need to make it non-cluster
>> index
>> and unique which allows multiple nulls, how should I do this with a sql
>> statement?
>>
>>
>

q; create index

I need to check if an index available on table T1 and field F1. If not,
create a non-clustered on F1. How can I do this in a stored procedure?JIM.H. wrote:
> I need to check if an index available on table T1 and field F1. If not,
> create a non-clustered on F1. How can I do this in a stored procedure?
Stuff the output of sp_helpindex into a temp table, then query that temp
table looking for your field name.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||You could check the sysindexes table:
if not exists (select * from sysindexes where name = 'foo')
BEGIN
PRINT 'the index foo does not exist'
END
If you are running SQL Server 2005 you can use sys.indexes:
select * from sys.indexes
--
Keith Kratochvil
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:9ED14F33-8BF2-435C-908D-245065910183@.microsoft.com...
>I need to check if an index available on table T1 and field F1. If not,
> create a non-clustered on F1. How can I do this in a stored procedure?|||=?Utf-8?B?SklNLkgu?= <JIMH@.discussions.microsoft.com> wrote in
news:9ED14F33-8BF2-435C-908D-245065910183@.microsoft.com:
> I need to check if an index available on table T1 and field F1. If
not,
> create a non-clustered on F1. How can I do this in a stored
procedure?
Untested, but would that not be something like:
DECLARE
@.index_num int;
SELECT
@.index_num = count(si.*)
FROM
sys.schemas AS ss INNER JOIN
sys.objects AS so ON (ss.schema_id = so.schema_id) INNER JOIN
sys.indexes AS si ON (so.object_id = si.object_id) INNER JOIN
sys.index_columns AS sic ON ((si.object_id = sic.object_id) AND
(si.index_id = sic.index_id)) INNER JOIN
sys.columns as SC ON ((sic.object_id = sc.object_id) AND
(sic.column_id = sc.column_id))
WHERE
SC.name = 'column_name' AND
SO.name = 'table_name' AND
SS.name = 'schema_name';
IF @.index_num = 0
CREATE INDEX ...
Ole Kristian Bangås
MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging, MCTS, MCITP

q; create index

I need to check if an index available on table T1 and field F1. If not,
create a non-clustered on F1. How can I do this in a stored procedure?JIM.H. wrote:
> I need to check if an index available on table T1 and field F1. If not,
> create a non-clustered on F1. How can I do this in a stored procedure?
Stuff the output of sp_helpindex into a temp table, then query that temp
table looking for your field name.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||You could check the sysindexes table:
if not exists (select * from sysindexes where name = 'foo')
BEGIN
PRINT 'the index foo does not exist'
END
If you are running SQL Server 2005 you can use sys.indexes:
select * from sys.indexes
Keith Kratochvil
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:9ED14F33-8BF2-435C-908D-245065910183@.microsoft.com...
>I need to check if an index available on table T1 and field F1. If not,
> create a non-clustered on F1. How can I do this in a stored procedure?|||examnotes <JIMH@.discussions.microsoft.com> wrote in
news:9ED14F33-8BF2-435C-908D-245065910183@.microsoft.com:

> I need to check if an index available on table T1 and field F1. If
not,
> create a non-clustered on F1. How can I do this in a stored
procedure?
Untested, but would that not be something like:
DECLARE
@.index_num int;
SELECT
@.index_num = count(si.*)
FROM
sys.schemas AS ss INNER JOIN
sys.objects AS so ON (ss.schema_id = so.schema_id) INNER JOIN
sys.indexes AS si ON (so.object_id = si.object_id) INNER JOIN
sys.index_columns AS sic ON ((si.object_id = sic.object_id) AND
(si.index_id = sic.index_id)) INNER JOIN
sys.columns as SC ON ((sic.object_id = sc.object_id) AND
(sic.column_id = sc.column_id))
WHERE
SC.name = 'column_name' AND
SO.name = 'table_name' AND
SS.name = 'schema_name';
IF @.index_num = 0
CREATE INDEX ...
Ole Kristian Bangs
MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging, MCTS, MCITP