Updateable Function Based SQL Index

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Updateable Function Based SQL Index

Updateable Function Based SQL Index

Rate Topic

Display Mode

Topic Options

Author

Message

stanlyn

Posted Thursday, September 13, 2012 2:42 AM

SSC 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

HowardW

Posted Thursday, September 13, 2012 2:52 AM

Ten Centuries

Group: General Forum Members
Last Login: Today @ 10:16 AM
Points: 1,092, Visits: 7,931

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

stanlyn

Posted Thursday, September 13, 2012 3:17 AM

SSC 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 »

Permissions