Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Updateable Function Based SQL Index Expand / Collapse
Author
Message
Posted Thursday, September 13, 2012 2:42 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, September 19, 2012 3:27 AM
Points: 27, Visits: 53
Hi,

How do I create an updateable index from these index definitions:

CREATE INDEX LibSort ON dbo.Page
(
REPLICATE('0', 5-len(dbo.Page.system_id)) + ltrim(dbo.Page.system_id)+'~'+
REPLICATE('0', 40-len(dbo.Page.Page_Type)) + ltrim(dbo.Page.Page_Type)+'~'+
REPLICATE('0', 24-len(dbo.Page.book_num)) + ltrim(dbo.Page.book_num)+'~'+
REPLICATE('0', 24-len(dbo.Page.Page_Prefix)) + ltrim(dbo.Page.Page_Prefix)+'~'+
REPLICATE('0', 12-len(dbo.Page.Page_num)) + ltrim(dbo.Page.Page_num)+'~'+
REPLICATE('0', 4-len(dbo.Page.SubPage_Num)) + ltrim(dbo.Page.SubPage_Num)
)

By updateable, I mean the index must be automatically updated when crud operations occur.

Thanks, Stanley
Post #1358403
Posted Thursday, September 13, 2012 2:52 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, November 3, 2014 8:05 AM
Points: 1,191, Visits: 9,892
You can't directly create an index on an expression, you can only create indexes on columns.

However, you can achieve something similar using a computed column and indexing it. E.g.:

ALTER TABLE PAGE 
ADD myComputedColumn AS (REPLICATE('0', 5-len(system_id)) + ltrim(system_id)+'~'+
REPLICATE('0', 40-len(Page_Type)) + ltrim(Page_Type)+'~'+
REPLICATE('0', 24-len(book_num)) + ltrim(book_num)+'~'+
REPLICATE('0', 24-len(Page_Prefix)) + ltrim(Page_Prefix)+'~'+
REPLICATE('0', 12-len(Page_num)) + ltrim(Page_num)+'~'+
REPLICATE('0', 4-len(SubPage_Num)) + ltrim(SubPage_Num))


CREATE INDEX IDX_Page_MyComputed_Column ON PAGE(myComputedColumn)

It will then be updated as part of the execution plan for anything that modifies the columns used in the computed column. Is this what you're after?
Post #1358406
Posted Thursday, September 13, 2012 3:17 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, September 19, 2012 3:27 AM
Points: 27, Visits: 53
Thanks Howard,

I believe this is what I need, I'll go and test now...

Thanks, Stanley
Post #1358419
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse