Mini Index?

[paulmac106]

I have a table with a few million records and I need to search and
return rows where a varchar(1000) field has the value "Showed"

SQL will not allow me to add an index for a field of this length.

Is it possible to create some kind of index for only the value "Showed"
Or is there some other faster way to search for this value?

thanks,
Paul

[SQL]

Take a look at full-text indexing look it up in BOL

http://sqlservercode.blogspot.com/

[Alexander Kuznetsov]

you can use a computed column
IsShowed as case when yourColumn = 'Showed' then 1 end

and create an index on IsShowed

[paulmac106]

Hi Alexander,

I tried a computed column, but then all the users of the windows
application that is the front end of this database got errors when
trying to insert a record into this table. Is there anything more I
have to do to use a computed column?

thanks,
Paul

Alexander Kuznetsov wrote:

you can use a computed column
IsShowed as case when yourColumn = 'Showed' then 1 end

and create an index on IsShowed

[Hugo Kornelis]

On 5 Dec 2005 08:36:22 -0800, pa********@gmail.com wrote:

Hi Alexander,

I tried a computed column, but then all the users of the windows
application that is the front end of this database got errors when
trying to insert a record into this table. Is there anything more I
have to do to use a computed column?

thanks,
Paul

Hi Paul,

All INSERT statements should always include a column list. There are
many reasons for this - you just found one of them!

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[Erland Sommarskog]

(pa********@gmail.com) writes:

I tried a computed column, but then all the users of the windows
application that is the front end of this database got errors when
trying to insert a record into this table. Is there anything more I
have to do to use a computed column?

Sounds like a case of poor application design.

An alternative is to use an indexed view instead. The view would look
like:

CREATE VIEW showed_view WITH SCHEMABINDING AS
SELECT pkcol
FROM tbl
WHERE yourcolumn = 'Showed'

You would then have to use this view in your queries. (In theory SQL Server
could use the view even if you query the table directly, but it does not
always happen.)

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Alexander Kuznetsov]

Paul,

besides using an indexed view, which may be a little bit tricky, you
could create your own table:

SELECT pkcol
FROM tbl
into side_table
WHERE yourcolumn = 'Showed'

of course you will have to maintain the table manually, via triggers.

[paulmac106]

You know I had tried that seperate table with an insert trigger, but
again the windos app didn't like it, and unfortuanately I don't have
control over the windows app. I didn't see how you can put an index on
a view...am i missing something?

thanks,
paul

[Erland Sommarskog]

(pa********@gmail.com) writes:

You know I had tried that seperate table with an insert trigger, but
again the windos app didn't like it, and unfortuanately I don't have
control over the windows app. I didn't see how you can put an index on
a view...am i missing something?

Read about it under the CREATE VIEW and CREATE INDEX statements in the
T-SQL reference. The view must be created WITH SCHEMABINDING, and all
notations must be two-part: dbo.tbl. There are a couple of more
restrictions, which is why I refer you to the manual.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx