Re: can't force index

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Britney (britneychen_2001_at_yahoo.com)
Date: 02/18/05 

Date: Fri, 18 Feb 2005 18:49:57 -0500

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:
> >
> > 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?

Relevant Pages

  • Re: For Jeff
    ... a reason to think God doesn't want it. ... a stupid idea but turn out not to be. ... Preaching is a ministry of the Holy Spirit, ...
    (uk.religion.christian)
  • Re: If I was...
    ... >>>You cannot seriously be this stupid. ... >>Dude, your ignorance regarding racism is beginning to astound. ... would vote for a black candidate for the same reason. ... you have no understanding that it isn't relieved by voting for a ...
    (misc.writing)
  • Re: For Jeff
    ... a reason to think God doesn't want it. ... I'm assuming for the sake of argument that God is competent. ... a stupid idea but turn out not to be. ... woman he's talking about *is* a good preacher. ...
    (uk.religion.christian)
  • Re: Reclaiming Atheism
    ... ways of talking about God.] ... My point is that what I am doing, and what others are doing today, is simply one more example of the age old process of trying to translate first century events and ideas into contemporary language. ... I also have every reason to think that contemporary Methodism stands within the fairly broad mainstream of worldwide Christian thinking. ... I don't think you're stupid. ...
    (uk.religion.christian)
  • Re: If I was...
    ... >>You cannot seriously be this stupid. ... >>>as well as whites are. ... >Dude, your ignorance regarding racism is beginning to astound. ... You haven't given one good reason for considering black racism to be ...
    (misc.writing)