ASP.NET, SQL Server, WPF, C#, VB.NET

"Leila" <Leilas@hotpop.com> wrote in news:#6l#pXhKGHA.3272  
  
@tk2msftngp13.phx.gbl:  
  
You can't. If you want to have an index over one specific property of your  
  
UDT, you need to have aseparate column in your table and store that value  
  
in there.  
  
Niels  
  
--  
  
**************************************************  
  
* Niels Berglund  
  
* http://staff.develop.com/nielsb  
  
* nielsb@no-spam.develop.com  
  
* "A First Look at SQL Server 2005 for Developers"  
  
* http://www.awprofessional.com/title/0321180593  
  
**************************************************

Hi Leila,  
  
If I understand what you mean, you can't have a separate index on property  
  
of the UDT in a table directly. The UDT has a single order if its either  
  
declared as "IsByteOrdered=true" in UDT attribute or you use Format.Native.  
  
If either of these conditions are true you can put an index over a UDT  
  
column in a table. Depending on how your Point is structured (for example,  
  
its Format.Native and X is the first field declared) that may be almost the  
  
same as putting an index on a separate property.  
  
There's a backhanded way to put an index on a specific property. That is to  
  
define it as a persisted computed column and index that. It would look like  
  
this:  
  
CREATE TABLE pointtab (  
  
id INT PRIMARY KEY IDENTITY,  
  
thepoint point,  
  
thepointx as thepoint.X persisted  
  
)  
  
go  
  
CREATE INDEX foo ON pointtab(thepointx) -- index on the persisted computed  
  
column  
  
go  
  
But you are then storing the X coordinate twice in the table.  
  
Hope this helps,  
  
Bob Beauchemin  
  
http://www.SQLskills.com/blogs/bobb  
  
"Leila" <Leilas@hotpop.com> wrote in message  
  
news:%236l%23pXhKGHA.3272@tk2msftngp13.phx.gbl...

"Leila" <Leilas@hotpop.com> wrote in  
  
news:uGWGe8iKGHA.668@TK2MSFTNGP11.phx.gbl:  
  
You have to use that column manually.  
  
Niels  
  
--  
  
**************************************************  
  
* Niels Berglund  
  
* http://staff.develop.com/nielsb  
  
* nielsb@no-spam.develop.com  
  
* "A First Look at SQL Server 2005 for Developers"  
  
* http://www.awprofessional.com/title/0321180593  
  
**************************************************

Hi,  
  
How can I create index on X property of my Point UDT?  
  
Thanks in advance,  
  
Leila

Hi Niels,  
  
You mean I must use a computed column? Then when I use X property of Point  
  
UDT in WHERE condition, will the query optimizer use that column or  
  
necessarily I must use that column in where condition manually?  
  
"Niels Berglund" <nielsb@develop.com> wrote in message  
  
news:Xns97614EFAFD789nielsbdevelopcom@207.46.248.16...

Thanks Bob :-)  
  
"Bob Beauchemin" <no_bobb_spam@sqlskills.com> wrote in message  
  
news:OEnrHqiKGHA.3492@TK2MSFTNGP09.phx.gbl...

"Leila" <Leilas@hotpop.com> wrote in  
  
news:##01czGMGHA.4064@TK2MSFTNGP10.phx.gbl:  
  
Having re-read your question. looked at your example and re-read my  
  
answer I am not entirely sure what you asked and what I answered, sorry.  
  
Looking at your example above, to me it seems you have an index (ix1) on  
  
the computed column for the x property. You then use that particular  
  
column in your query - so the index is used. Isn't that correct?  
  
Niels  
  
--  
  
**************************************************  
  
* Niels Berglund  
  
* http://staff.develop.com/nielsb  
  
* nielsb@no-spam.develop.com  
  
* "A First Look at SQL Server 2005 for Developers"  
  
* http://www.awprofessional.com/title/0321180593  
  
**************************************************

Hi Niels,  
  
It seems Query Optimizer is intelligent enough to use my index without  
  
manually specifying computed column in WHERE clause!  
  
CREATE TABLE T1(  
  
c1 INT IDENTITY PRIMARY KEY,  
  
c2 point,  
  
cx AS c2.X PERSISTED,  
  
cy AS c2.Y PERSISTED)  
  
-- Insert some values....  
  
CREATE INDEX ix1 ON t1(cx)  
  
SELECT C1,C2.X FROM t1 WHERE C2.X=6 -- This query uses ix1  
  
CREATE INDEX ix3 ON t1(cx,cy)  
  
SELECT C1,C2.Y FROM t1 WHERE C2.X=6  -- This does too  
  
"Niels Berglund" <nielsb@develop.com> wrote in message  
  
news:Xns9761709A545D1nielsbdevelopcom@207.46.248.16...

Yes,  
  
I searched on the property of UDT but query optimizer used the index which I  
  
had created on the computed column related to that property.  
  
"Niels Berglund" <nielsb@develop.com> wrote in message  
  
news:Xns9769B58A6B74Enielsbdevelopcom@207.46.248.16...