can't force index

Hi,
I have a table called "users"
basically, it has a column called "user_id",
and there is a clustered index called "user_id_index",
so everytimes I try to execute
select * from users
I look at the execution plan, it's always used user_id_index, which is
fine.
however, I added another index called "user_name_index"
then I execute:
select * from users (index=user_name_index)
now I look at the execution plan, it is still using "user_id_index" ,
obviously,
it didn't force the index. what happen? why sql server ignore my index
hint?
how do you solve this problem?Recall that the clustered index holds the data as well as the key
columns. Unless the index "user_name_index" contains all the columns of
the table the server still has to read the clustered index to retrieve
the data. You should see a bookmark lookup on the cluster key.
Why do you see this as a problem? Why are you attempting to force an
index hint? Why are you using SELECT *, which potentially hinders index
optimization and shouldn't be used at all in production code.
David Portas
SQL Server MVP
--|||Hi
An not supplying a WHERE clause results in SQL server doing a table scan so
indexes may not be used (why use an index when you are returning all the
data?).
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1108762629.179991.114690@.o13g2000cwo.googlegroups.com...
> Recall that the clustered index holds the data as well as the key
> columns. Unless the index "user_name_index" contains all the columns of
> the table the server still has to read the clustered index to retrieve
> the data. You should see a bookmark lookup on the cluster key.
> Why do you see this as a problem? Why are you attempting to force an
> index hint? Why are you using SELECT *, which potentially hinders index
> optimization and shouldn't be used at all in production code.
> --
> David Portas
> SQL Server MVP
> --
>|||user_name_index is used on "user_name" column,
but even when I execute
select user_name from users (index=user_name_index)
where user_name='Joe'
I still see the execution plan is using clustered index "user_id_index".
don't you think it's weird?
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1108762629.179991.114690@.o13g2000cwo.googlegroups.com...
> Recall that the clustered index holds the data as well as the key
> columns. Unless the index "user_name_index" contains all the columns of
> the table the server still has to read the clustered index to retrieve
> the data. You should see a bookmark lookup on the cluster key.
> Why do you see this as a problem? Why are you attempting to force an
> index hint? Why are you using SELECT *, which potentially hinders index
> optimization and shouldn't be used at all in production code.
> --
> David Portas
> SQL Server MVP
> --
>|||> don't you think it's weird?
No. Did you read the replies from Mike and myself?
If you explain what you want to achieve maybe we can help you better.
If you just want to understand indexes and hints then I recommend Kalen
Delaney's book "Inside SQL Server". Hints are an advanced feature and
should be used only when essential and with an understanding of their
effects on the query plan.
David Portas
SQL Server MVP
--|||> select user_name from users (index=user_name_index)
> where user_name='Joe'
> I still see the execution plan is using clustered index
"user_id_index".
Maybe you could post some runnable code to reproduce that behaviour
(CREATE..., INSERT..., SELECT...). I don't see that myself. I get
user_name_index used with or without the hint. Also tell us your
edition, version and SP level.
David Portas
SQL Server MVP
--|||1) How big is the table? How many pages (blocks of 8 Kilobyte) does the
table use? You can check this with "sp_spaceused".
2) What version of SQL-Server are you using
3) Please post (simplified) DDL and some sample rows, and preferably a
script to reproduce the behavior.
Gert-Jan
Britney wrote:
> user_name_index is used on "user_name" column,
> but even when I execute
> select user_name from users (index=user_name_index)
> where user_name='Joe'
> I still see the execution plan is using clustered index "user_id_index".
> don't you think it's weird?|||Oh my god, sorry guys.
I was wrong about it, "users" is not a table, but it's a view.
I just found out.
In case you ask me why i'm doing this stupid view:
the reason we create a view for this is because I want to do snapshot
isolation for read and write.
if there are data coming in to [2users] table, then I alter view to use
[1users] table. So users table have 2 tables:
Read and write. This way I don't worry about locking.
CREATE VIEW users
AS
select * from [2users]
--
Now We know what is happening...
IF I select from actual table ,
select user_name from [2users] (index=user_name_index)
where user_name='Joe'
I see that it 's using forced index.
I guess view doesn't work correctly for some reason.
---
sp_spaceused [2users]
result:
name rows reserved data index_size unused
[2users] 41892 13952 KB 6616 KB 7144 KB 192 KB
---
select @.@.version
result:
Microsoft SQL Server 2000 - 8.00.780 (Intel X86) Mar 3 2003 10:28:28
Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on Windows
NT 5.0 (Build 2195: Service Pack 4)
----

> 1) How big is the table? How many pages (blocks of 8 Kilobyte) does the
> table use? You can check this with "sp_spaceused".
>
"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:42166EA6.EA6900C3@.toomuchspamalready.nl...
> 1) How big is the table? How many pages (blocks of 8 Kilobyte) does the
> table use? You can check this with "sp_spaceused".
> 2) What version of SQL-Server are you using
> 3) Please post (simplified) DDL and some sample rows, and preferably a
> script to reproduce the behavior.
> Gert-Jan
>
> Britney wrote:

Cant create index on view

Hi,

SQL Server 2005 (SP2) Developer edition on XP Pro

Can anyone help with setting up an indexed view? Part of the select statement calls a couple of very simple functions which are deterministic. The functions which are shown below do not perform any aggregation.

This is the error message.

Msg 8668, Level 16, State 0, Line 2
Cannot create the clustered index 'IX3_SA' on view 'XPS.dbo.SA_INDEXED' because the select list of the view contains an expression on result of aggregate function or grouping column. Consider removing expression on result of aggregate function or grouping column from select list.

Function definitions:

create FUNCTION [dbo].[MonthName](@.MonthNo TINYINT)
RETURNS CHAR(3)
with schemabinding
AS
BEGIN
DECLARE @.MonthName CHAR(3)
IF @.MonthNo BETWEEN 1 AND 12 SET @.MonthName = SUBSTRING('JanFebMarAprMayJunJulAugSepOctNovDec',((@.MonthNo*3) -2),3)
RETURN ISNULL(@.MonthName,'')
END

create FUNCTION [dbo].[FinancialYear](
@.Year INT,
@.MonthNo TINYINT,
@.YearEndMonthNo TINYINT)
RETURNS INT
with schemabinding
AS
BEGIN
DECLARE @.FinancialYear INT
IF @.MonthNo <= @.YearEndMonthNo
SET @.FinancialYear = @.Year
ELSE
SET @.FinancialYear = @.Year + 1
RETURN @.FinancialYear
END

The first part of the select statement is as follows:

(SELECT
count_big(*) as CB,

dbo.FinancialYear(YR.Number,MTH.Number,YEAR_END_MONTH) AS REPORT_FINANCIAL_YEAR,
dbo.MonthName(MTH.Number) + ' ' + CAST(YR.Number AS CHAR(4)) AS REPORT_MONTH,
dbo.MonthName(YEAR_END_MONTH) + ' ' + CAST(dbo.FinancialYear(YR.Number,MTH.Number,YEAR_END_MONTH) AS CHAR(4)) AS REPORT_FINANCIAL_YEAR_END,

I can create the index on this view if I comment out the calls to the functions so I know that is where the problem is. I can't however see why this is erroring as both only return a single value without any agregation.

Anyone got any ideas on this?

Thanks in advance

David.

Well that met with a deafening silence.

The answer was to move these function calls out of the indexed view definition into a standard view that sits above it.

Cant create an index on my view

G'day all.

I am trying to create an index on a view that joins two tables.

I get the classic error of course:
'Cannot index the view 'dbname.dbo.HJC_net'. It contains one or more disallowed constructs.'

Thing that gets me is that it all seems pretty normal stuff and I can't see what is stopping it.

Code is below and any help greatly appreciated.

CREATE VIEW dbo.HJC_net WITH SCHEMABINDING AS
SELECT t_number
FROM dbo.ticket_cancellations RIGHT OUTER JOIN
dbo.tickets ON dbo.ticket_cancellations.tc_system_ref = dbo.tickets.t_number
WHERE dbo.tickets.t_cancelled <> - 1 OR
-- Add all cancellation codes that are to be excluded from the NET view below
(dbo.ticket_cancellations.tc_cancellation_code <> 83943
AND dbo.ticket_cancellations.tc_cancellation_code <> 83946)

GO
-- Create a clustered index, it MUST be unique
CREATE UNIQUE CLUSTERED INDEX t_number_unique ON HJC_net(t_number)BOL:

The SELECT statement in the view cannot contain these Transact-SQL syntax elements:
...
Outer or self joins
...

Can't create a secondary index on a bit column

I am trying to create a secondary index on mult. columns
and one of them is a bit (I know you should not but that
is what they want). Using Enterprise Manager this does not
work, I get the following error:
Primary key or index cannot be created on column '<0s>'
because its data type is 'bit'.
If I use Query Analyzer it works fine.
My question is: Is this a bug in Enterprise Manager that
it does not let me or a bug in Query Analyzer that it does
let me?
Thanks.Don't know where is wrong. But you can check with QA's execution plan if
you have adequate data set -- use a situation that the new index is suitable
and run a query to see whether ther index is used. Or you can also sysindex
and sysindexkeys to look at.
Quentin
"Amy" <anonymous@.discussions.microsoft.com> wrote in message
news:030c01c3daf1$218abda0$a601280a@.phx.gbl...

quote:

---

> I am trying to create a secondary index on mult. columns
> and one of them is a bit (I know you should not but that
> is what they want). Using Enterprise Manager this does not
> work, I get the following error:
> Primary key or index cannot be created on column '<0s>'
> because its data type is 'bit'.
> If I use Query Analyzer it works fine.
> My question is: Is this a bug in Enterprise Manager that
> it does not let me or a bug in Query Analyzer that it does
> let me?
> Thanks.

---

|||Amy
There are some (using your word 'bugs') with EM
It does work with QA so be happy.
Note: You are right that it's a bad idea to have index on 'bit' column.
"Amy" <anonymous@.discussions.microsoft.com> wrote in message
news:030c01c3daf1$218abda0$a601280a@.phx.gbl...

quote:

---

> I am trying to create a secondary index on mult. columns
> and one of them is a bit (I know you should not but that
> is what they want). Using Enterprise Manager this does not
> work, I get the following error:
> Primary key or index cannot be created on column '<0s>'
> because its data type is 'bit'.
> If I use Query Analyzer it works fine.
> My question is: Is this a bug in Enterprise Manager that
> it does not let me or a bug in Query Analyzer that it does
> let me?
> Thanks.

---

Can't create a secondary index on a bit column

I am trying to create a secondary index on mult. columns
and one of them is a bit (I know you should not but that
is what they want). Using Enterprise Manager this does not
work, I get the following error:
Primary key or index cannot be created on column '<0s>'
because its data type is 'bit'.
If I use Query Analyzer it works fine.
My question is: Is this a bug in Enterprise Manager that
it does not let me or a bug in Query Analyzer that it does
let me?
Thanks.Don't know where is wrong. But you can check with QA's execution plan if
you have adequate data set -- use a situation that the new index is suitable
and run a query to see whether ther index is used. Or you can also sysindex
and sysindexkeys to look at.
Quentin
"Amy" <anonymous@.discussions.microsoft.com> wrote in message
news:030c01c3daf1$218abda0$a601280a@.phx.gbl...
> I am trying to create a secondary index on mult. columns
> and one of them is a bit (I know you should not but that
> is what they want). Using Enterprise Manager this does not
> work, I get the following error:
> Primary key or index cannot be created on column '<0s>'
> because its data type is 'bit'.
> If I use Query Analyzer it works fine.
> My question is: Is this a bug in Enterprise Manager that
> it does not let me or a bug in Query Analyzer that it does
> let me?
> Thanks.|||Amy
There are some (using your word 'bugs') with EM
It does work with QA so be happy.
Note: You are right that it's a bad idea to have index on 'bit' column.
"Amy" <anonymous@.discussions.microsoft.com> wrote in message
news:030c01c3daf1$218abda0$a601280a@.phx.gbl...
> I am trying to create a secondary index on mult. columns
> and one of them is a bit (I know you should not but that
> is what they want). Using Enterprise Manager this does not
> work, I get the following error:
> Primary key or index cannot be created on column '<0s>'
> because its data type is 'bit'.
> If I use Query Analyzer it works fine.
> My question is: Is this a bug in Enterprise Manager that
> it does not let me or a bug in Query Analyzer that it does
> let me?
> Thanks.