Sorteringsindex på et converted memofelt

Er der ingen der ved, hvordan man delvist sorterer på ntext felter via et koverteret felt ??

If you must sort on a memo field then wouldnt it be an idea to have an extra field which contains the characters which you sort by? This would obviously meen having to update this field too but that could be done in an Update/Insert trigger. This field could also be indexed which would speed things up I'm sure.

Hi Terry

Might be an idea. I just thought that I could make a non clustred index on a calculated field or something like that.

Noo idea if you can use this.

I have had a play around with making a view with an index on the calculated field. I have no idea if it helps performance.

Reading Books Online it is necessary to create the view with schema binding


CREATE VIEW vieIndexed (NewFieldToIndex)
WITH SCHEMABINDING
AS
SELECT    CONVERT(varchar(4), LongField)
FROM        dbo.tbl1
GO


If I try creating an index on the calcualted field it say that I firsnt need to create a UNIQUE CLUSTERED INDEX.

So I do that for another field where I get a warning>
Warning: The optimizer cannot use the index because the select list of the view contains a non-aggregate expression.


CREATE UNIQUE CLUSTERED INDEX UCI
ON vieIndexed (fld1)

Continue with the index on the calculated field

CREATE INDEX NewIX
ON vieIndexed (NewFieldToIndex)

I will try it out but found this on the inernet:
Improving Text and Image Column Performance

TEXT IN ROW to the rescue! The TEXT IN ROW feature allows you to store smaller amounts of text and image data onto the data page. The great thing about this feature is it lets you have your cake and eat it to. It will store data less than a preset amount on the data page and anything above is still done the traditional way. It's a great way of solving the issue of an analyst coming to you and saying they "may" someday need all those text columns, even though they're only using 200 characters at the most.

To enable the feature, use the SP_TABLEOPTION system stored procedure and specify the table as shown below:

SP_TABLEOPTION 'FAQ2',
'TEXT IN ROW', 'ON'

Now the feature is turned on and has defaulted to anything less than or equal to 256 bytes will be stored on the data page. This 256 byte setting is the optimal setting for the feature. You may want to set this higher nonetheless. For example, I noticed by running the below query that the average column was about 600 bytes.

SELECT AVG(DATALENGTH(FAQBODY)) FROM FAQ2

I also determined from the following query that the maximum that this column was storing was 9,243, a little too much for a varchar field.

SELECT MAX(DATALENGTH(FAQBODY)) FROM FAQ2

So I took the average column length (600) and decided to store all columns equal to or less than 600 bytes in the data row. You can do this by using the SP_TABLEOPTION syntax again as shown below:

SP_TABLEOPTION 'FAQ2',
'TEXT IN ROW', '600'

As soon as you do enable, you will see no effect until new rows are inserted into the table. Most people can't wait this long however so the workaround would be to run a quick UPDATE statement like the one shown below to move the eligible items back to the data page:

UPDATE FAQ2
SET FAQBODY = FAQBODY

Any new items are automatically moved if their eligible. You will also know when you go to Table Properties (shown below), that the table shows the FAQBODY text column as 600 bytes. Data stored in the data page operates much like a varchar field. For example, if you try to insert 280 characters of data into the FAQBODY column, only 280 bytes of space will be used in the row. Anything greater than 600 bytes stores the 16 byte pointer in the row and the remainder is stored off the data page.

Terry - TEXT IN ROW seems to have done the job and speeded up the on the fly sorting.
Thanks for your digging

and thank you too