How do I make it use my index?

Hello!

I have two tables

users and pictures.

table users have a clustered (PK) index on userid
table pictures have a clustered (PK) index on userid

when I do this query:

"select userid from pictures where userid=123"

then It will do a clustered index seek

But If I do any of those:

"select t2.userid from users t1 left join t2 on t1.userid = t2.userid"
or
"select (select userid from pictures where usedid = t1.userid) from users t1"

It will do a clustered index scan.

How can I force it to seek my index instead of scan?

Thanks!do you have some where clause at the end of the query? when not, then the plan created by sql server is perhaps really the best!
if the where clause shrinks the number of rows in t1 to a fraction of total table row count, you could use

option(loop join)

at the end of the query. this should solve your problem

How do I insert text into empty xml element in sql server 2005?

Hello
I have the following xml data stored in an xml datatype colmun in sql
server 2005:
<webpage id="935623B3-F72D-45EE-AF88-47022F101184">
<createdate>Sep 6 2005 11:04AM</createdate>
<title>Themes</title>
<icon>app/16/p/text_align_left</icon>
<pagetext />
</webpage>
I'd like to be able to update the the xml so that there is text within
the pagetext element, i.e.
...
<pagetext>
Some content goes here.
</pagetext>
...
I've tried to achieve this using the query below:
update tbl_tree SET theTree.modify(' replace value of
(//*[@.id=sql:variable("@.focusID")][1]/title/text())[1] with
sql:variable("@.someContent")')
but it doesn't work. Is this because you can't 'modify' an empty
element such as '<pagetext/>'
I thought about inserting a node such as '<pagetext>Some content goes
here.</pagetext>'- this would solve the problem only until such time as
the element has its contents removed at which point it will become
<pagetext/> again.
Is there a way to do this?
Any help very much appreciated.
PeterYou would need an IF_DML statement that unfortunately did not make it into
SQL Server 2005. Please send an email to sqlwish at microsoft.com with your
use case and request.
Here is a workaround: You first update all those pagetext elements that have
no content with an insert and then do your normal replace value of.
declare @.x xml;
set @.x = N'<webpage id="935623B3-F72D-45EE-AF88-47022F101184">
<createdate>Sep 6 2005 11:04AM</createdate>
<title>Themes</title>
<icon>app/16/p/text_align_left</icon>
<pagetext />
</webpage>';
declare @.focusID uniqueidentifier;
set @.focusID = '935623B3-F72D-45EE-AF88-47022F101184';
declare @.someContent nvarchar(50);
set @.someContent= N'this is a test';
--I'd like to be able to update the the xml so that there is text within
--the pagetext element, i.e.
SET @.x.modify('insert text {"x"}
into /webpage[@.id=sql:variable("@.focusID")][1]/pagetext[not(text())][1]');
select @.x;
SET @.x.modify('replace value of
(/webpage[@.id=sql:variable("@.focusID")][1]/pagetext/text())[1] with
sql:variable("@.someContent")');
select @.x
Best regards
Michael
<firechaser@.talk21.com> wrote in message
news:1127893569.480367.18870@.g14g2000cwa.googlegroups.com...
> Hello
> I have the following xml data stored in an xml datatype colmun in sql
> server 2005:
> <webpage id="935623B3-F72D-45EE-AF88-47022F101184">
> <createdate>Sep 6 2005 11:04AM</createdate>
> <title>Themes</title>
> <icon>app/16/p/text_align_left</icon>
> <pagetext />
> </webpage>
> I'd like to be able to update the the xml so that there is text within
> the pagetext element, i.e.
> ...
> <pagetext>
> Some content goes here.
> </pagetext>
> ...
> I've tried to achieve this using the query below:
> update tbl_tree SET theTree.modify(' replace value of
> (//*[@.id=sql:variable("@.focusID")][1]/title/text())[1] with
> sql:variable("@.someContent")')
> but it doesn't work. Is this because you can't 'modify' an empty
> element such as '<pagetext/>'
> I thought about inserting a node such as '<pagetext>Some content goes
> here.</pagetext>'- this would solve the problem only until such time as
> the element has its contents removed at which point it will become
> <pagetext/> again.
> Is there a way to do this?
> Any help very much appreciated.
> Peter
>|||Thanks Michael - I appreciate your help.sql

How do I insert text into empty xml element in sql server 2005?

Hello
I have the following xml data stored in an xml datatype colmun in sql
server 2005:
<webpage id="935623B3-F72D-45EE-AF88-47022F101184">
<createdate>Sep 6 2005 11:04AM</createdate>
<title>Themes</title>
<icon>app/16/p/text_align_left</icon>
<pagetext />
</webpage>
I'd like to be able to update the the xml so that there is text within
the pagetext element, i.e.
...
<pagetext>
Some content goes here.
</pagetext>
...
I've tried to achieve this using the query below:
update tbl_tree SET theTree.modify(' replace value of
(//*[@.id=sql:variable("@.focusID")][1]/title/text())[1] with
sql:variable("@.someContent")')
but it doesn't work. Is this because you can't 'modify' an empty
element such as '<pagetext/>'
I thought about inserting a node such as '<pagetext>Some content goes
here.</pagetext>'- this would solve the problem only until such time as
the element has its contents removed at which point it will become
<pagetext/> again.
Is there a way to do this?
Any help very much appreciated.
Peter
You would need an IF_DML statement that unfortunately did not make it into
SQL Server 2005. Please send an email to sqlwish at microsoft.com with your
use case and request.
Here is a workaround: You first update all those pagetext elements that have
no content with an insert and then do your normal replace value of.
declare @.x xml;
set @.x = N'<webpage id="935623B3-F72D-45EE-AF88-47022F101184">
<createdate>Sep 6 2005 11:04AM</createdate>
<title>Themes</title>
<icon>app/16/p/text_align_left</icon>
<pagetext />
</webpage>';
declare @.focusID uniqueidentifier;
set @.focusID = '935623B3-F72D-45EE-AF88-47022F101184';
declare @.someContent nvarchar(50);
set @.someContent= N'this is a test';
--I'd like to be able to update the the xml so that there is text within
--the pagetext element, i.e.
SET @.x.modify('insert text {"x"}
into /webpage[@.id=sql:variable("@.focusID")][1]/pagetext[not(text())][1]');
select @.x;
SET @.x.modify('replace value of
(/webpage[@.id=sql:variable("@.focusID")][1]/pagetext/text())[1] with
sql:variable("@.someContent")');
select @.x
Best regards
Michael
<firechaser@.talk21.com> wrote in message
news:1127893569.480367.18870@.g14g2000cwa.googlegro ups.com...
> Hello
> I have the following xml data stored in an xml datatype colmun in sql
> server 2005:
> <webpage id="935623B3-F72D-45EE-AF88-47022F101184">
> <createdate>Sep 6 2005 11:04AM</createdate>
> <title>Themes</title>
> <icon>app/16/p/text_align_left</icon>
> <pagetext />
> </webpage>
> I'd like to be able to update the the xml so that there is text within
> the pagetext element, i.e.
> ...
> <pagetext>
> Some content goes here.
> </pagetext>
> ...
> I've tried to achieve this using the query below:
> update tbl_tree SET theTree.modify(' replace value of
> (//*[@.id=sql:variable("@.focusID")][1]/title/text())[1] with
> sql:variable("@.someContent")')
> but it doesn't work. Is this because you can't 'modify' an empty
> element such as '<pagetext/>'
> I thought about inserting a node such as '<pagetext>Some content goes
> here.</pagetext>'- this would solve the problem only until such time as
> the element has its contents removed at which point it will become
> <pagetext/> again.
> Is there a way to do this?
> Any help very much appreciated.
> Peter
>
|||Thanks Michael - I appreciate your help.

How do I find what version of SQL Server is installed?

Hello:
I know SQL Server 2000 come in Developer Edition, Standard and Enterprise
editions.
Can you tell me how I can determine which flavor is installed?
I thought I had Standard Edition but when I tried to install Standard
Edition of the Reporting services, it said that it was a wrong type.
Thanks.
Venki
Sorry, I should have looked up the helpfile. I used the @.@.Version to realize
that I was running the developer's edition.
Venki
"vvenk" wrote:

> Hello:
> I know SQL Server 2000 come in Developer Edition, Standard and Enterprise
> editions.
> Can you tell me how I can determine which flavor is installed?
> I thought I had Standard Edition but when I tried to install Standard
> Edition of the Reporting services, it said that it was a wrong type.
> Thanks.
> Venki
|||use master
select @.@.version
"vvenk" wrote:

> Hello:
> I know SQL Server 2000 come in Developer Edition, Standard and Enterprise
> editions.
> Can you tell me how I can determine which flavor is installed?
> I thought I had Standard Edition but when I tried to install Standard
> Edition of the Reporting services, it said that it was a wrong type.
> Thanks.
> Venki
|||Hi,
@.@.Version will serve you requirement. But have a look into the below link to
get all the versions for each patch level sql server.
http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=37
Thanks
Hari
SQL Server MVP
"vvenk" <vvenk@.discussions.microsoft.com> wrote in message
news:049E3C1C-ACF1-4D28-B659-475767027800@.microsoft.com...[vbcol=seagreen]
> Sorry, I should have looked up the helpfile. I used the @.@.Version to
> realize
> that I was running the developer's edition.
> Venki
> "vvenk" wrote:

How do I find what version of SQL Server is installed?

Hello:
I know SQL Server 2000 come in Developer Edition, Standard and Enterprise
editions.
Can you tell me how I can determine which flavor is installed?
I thought I had Standard Edition but when I tried to install Standard
Edition of the Reporting services, it said that it was a wrong type.
Thanks.
VenkiSorry, I should have looked up the helpfile. I used the @.@.Version to realize
that I was running the developer's edition.
Venki
"vvenk" wrote:

> Hello:
> I know SQL Server 2000 come in Developer Edition, Standard and Enterprise
> editions.
> Can you tell me how I can determine which flavor is installed?
> I thought I had Standard Edition but when I tried to install Standard
> Edition of the Reporting services, it said that it was a wrong type.
> Thanks.
> Venki|||use master
select @.@.version
"vvenk" wrote:

> Hello:
> I know SQL Server 2000 come in Developer Edition, Standard and Enterprise
> editions.
> Can you tell me how I can determine which flavor is installed?
> I thought I had Standard Edition but when I tried to install Standard
> Edition of the Reporting services, it said that it was a wrong type.
> Thanks.
> Venki|||Hi,
@.@.Version will serve you requirement. But have a look into the below link to
get all the versions for each patch level sql server.
http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=37
Thanks
Hari
SQL Server MVP
"vvenk" <vvenk@.discussions.microsoft.com> wrote in message
news:049E3C1C-ACF1-4D28-B659-475767027800@.microsoft.com...[vbcol=seagreen]
> Sorry, I should have looked up the helpfile. I used the @.@.Version to
> realize
> that I was running the developer's edition.
> Venki
> "vvenk" wrote:
>