sql server index

I'm used to creating an index on upper(name). Is there a simple way to do 
something like this in dB+/SQLS using CREATE INDEX...

Andy 

Many thanks, that looks really helpful.

I'm completely new to both dB+ and SQLS and I need to deliver the first stab
at the app in ten days. Fortunately it's a very simple job!

Andy

"Lysander" <nobody@nowhere.de> wrote in message 
news:0lznW1aaIHA.1236@news-server...
> Andy Ellis schrieb:
>> I'm used to creating an index on upper(name). Is there a simple way to
do 
>> something like this in dB+/SQLS using CREATE INDEX...
>
> Indexing on SQL servers work differently from indexing on desktop tables.
>
> I suggest that you do all client-sided search applying an upper() only 
> when searching.
>
> If you need the index on an upper() in the database, I suggest that you 
> add an additional field to the table, and then put a simple index on that 
> extra-field.
>
> For example: U_LastName as the counterpart to LastName
>
> Now you should add a so called trigger to the table.
> A trigger is an automatism for SQL-Server databases that will fire an 
> event whenever a certain action takes place with one row.
>
> In your case, you would need to set up a trigger firing whenever a new 
> record is inserted, or an existing record is changed. The code for the 
> trigger should set the value for the U_LastName based on LastName.
>
> This might sound complicated, but trust me it is not.
>
> I don't know the SQLS syntax, but in Firebird it would be just this:
>
> Create Trigger Make_LastName_Upper for TheTable
> active before Insert or Update Position 0
> as
> begin
>   new.U_LastName = upper(new.LastName);
> end
>
>
> That's all.
> Maybe there's an alternative in using a calculated field; but I don't know

> how that's implemented in MS SQLS.
>
> Attention! A "Default Value" does not work, because it will not
react on 
> updates, only on inserts.
>
>
> ciao,
> André 

Andy Ellis schrieb:
> I'm used to creating an index on upper(name). Is there a simple way to do 
> something like this in dB+/SQLS using CREATE INDEX...

Indexing on SQL servers work differently from indexing on desktop tables.

I suggest that you do all client-sided search applying an upper() only 
when searching.

If you need the index on an upper() in the database, I suggest that you 
add an additional field to the table, and then put a simple index on 
that extra-field.

For example: U_LastName as the counterpart to LastName

Now you should add a so called trigger to the table.
A trigger is an automatism for SQL-Server databases that will fire an 
event whenever a certain action takes place with one row.

In your case, you would need to set up a trigger firing whenever a new 
record is inserted, or an existing record is changed. The code for the 
trigger should set the value for the U_LastName based on LastName.

This might sound complicated, but trust me it is not.

I don't know the SQLS syntax, but in Firebird it would be just this:

Create Trigger Make_LastName_Upper for TheTable
active before Insert or Update Position 0
as
begin
   new.U_LastName = upper(new.LastName);
end


That's all.
Maybe there's an alternative in using a calculated field; but I don't 
know how that's implemented in MS SQLS.

Attention! A "Default Value" does not work, because it will not react
on 
updates, only on inserts.


ciao,

In article <1Ku5YFbaIHA.1240@news-server>, anvyll@gmail.com says...
> Many thanks, that looks really helpful.
> 
> I'm completely new to both dB+ and SQLS and I need to deliver the first
stab
> at the app in ten days. Fortunately it's a very simple job!
> 
> Andy


Andy,

Just to add to what André has said, I would suggest you index ONLY your 
Primary Key field to begin with. When you identify that something is 
slow and would benefit from an index, then add it. dBASE tables easily 
benefit from indexes. SQL tables are often as fast without them, 
especially if your tables are small.

-- 
Geoff Wass [dBVIPS]
Montréal, Québec, Canada

.|.|.|        dBASE info at http://geocities.com/geoff_wass       |.|.|.
.|.|.| ---------------------------------------------------------- |.|.|.

Geoff Wass [dBVIPS] schrieb:

> Just to add to what André has said, I would suggest you index ONLY your 
> Primary Key field to begin with. When you identify that something is 
> slow and would benefit from an index, then add it. dBASE tables easily 
> benefit from indexes. SQL tables are often as fast without them, 
> especially if your tables are small.


Oh! How could I forget that one? Good point.

This is even more true, if your index-tree does not contain too many dupes.

So, if you don't have too many "Miller" or "Smith" in your
adress 
tables, overall performance could even be improved by leaving away the 
index.

Just make sure you query the table using "...order by" instead of
using 
rowset.indexname.