Re: Conditional full-text indexing possible in 2000?

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

Simon Sabin wrote:

Hello Daniel,


Data is stored in a table

A view is a way of representing the joining of multiple tables i.e. a
piece of SQL using a name, a shorthand. The engine effectivley puts the
SQL from your view into the SQL it isbeing used. in.

An indexed view actually stores the data that the view would return, this
way the data can be returned from this set rather than all the base
tables. That means your data that is returned by an indexed view will be
stored in the indexed view AND the original table.



Hi Simon, so so sorry for not replying earlier!

Thanks for the explanation. It sounds like a great way to seriously optimize
your DB for specific uses at the expense of storage space (which is
plentiful anyway ;). We will do this most definitely!

Thanks :)

Daniel

Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons


Simon Sabin wrote:

Hello Daniel,

Hi Simon, thanks for writing :)

Indexed views will store a complete copy of the data.

Is this different from "regular" full-text indexes?

Standard edition doesn't do partitioning you will need to use a
partitioned view which adds a level of complexity because of the
restrictions on their design. You could add the extra column as a
token so that you don't get those rows back from the full text search
i.e.

select *
from containstable (yourtable, yourcolumn,'(IWantThisValue AND
ThisValue)
AND NOT ISDELETED'
so when you update your isdeleted flag to 1 you add ISDELETED to the
column being full text indexed

Argh... You'll have to excuse me, I'm not really a DB designer myself
in general, and definitely not MS SQL savvy in particular, I'm merely
going to use this DB in PHP, so my knowledge is somewhat reduced...
I'm not quite sure what you're telling me? Sorry :(

Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons

--
http://www.rhesusb.dk
.

Relevant Pages

  • Re: SQL
    ... > If you design right, you can *shift* much behavior to being data and ... > SQL is close to being Turing Complete. ... It is a bad habit of yours. ... If you're seriously suggesting that CRUD applications are equal ...
    (comp.object)
  • Re: Object-oriented thinking in SQL context?
    ... away from arrays and other non-OO data structures associated ... SQL is constrained to 'trivial' arrays. ... Design: Logical Design", 4th edition. ... Something analogous happens with database design. ...
    (comp.databases.theory)
  • Re: Why does 1:3 relationsihp require another table?
    ... Pro SQL Server 2000 Database Design ... > there can be a relation with NULLs in its tuples (hence 1NF models with ... > can only conclude that you do not believe Louis and Lewis are correctly ...
    (microsoft.public.sqlserver.programming)
  • Re: Why does 1:3 relationsihp require another table?
    ... correct design for the new real-world model. ... there can be a relation with NULLs in its tuples (hence 1NF models with ... Here's a good argument for not letting me represent Louis and Lewis by ... SQL is an attempt to implement relational algebra ...
    (microsoft.public.sqlserver.programming)
  • Re: bypassing some of the parameters in a parameter query
    ... >> Well, WHERE does work in design view, but having a lot of ANDs and ORs ... what is simple and direct logic in SQL View ... >> query to design view and see if it makes sense to you. ... >> Tom Ellison ...
    (microsoft.public.access.queries)