September 13, 2012 at 2:42 am
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
September 13, 2012 at 2:52 am
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?
September 13, 2012 at 3:17 am
Thanks Howard,
I believe this is what I need, I'll go and test now...
Thanks, Stanley
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply