Error creating index on a computed column

[starsky51]

Morning All.
I've set up a table which self joins to form a heirarchical structure.

Simplified example of the table:

CREATE TABLE Assets
(
	AssetID int IDENTITY(0,1) PRIMARY KEY CLUSTERED,
	ParentAssetID int NOT NULL DEFAULT(0),
	CONSTRAINT fk_Assets_ParentAssetID_AssetID FOREIGN KEY(ParentAssetID) REFERENCES Assets(AssetID),
);

I have set up a UDF against the table to display the heirarchy of each record as a string:

CREATE FUNCTION dbo.udfGetAssetHeirarchy(@AssetID int)
RETURNS varchar(100)
WITH SCHEMABINDING
AS
BEGIN
	DECLARE @ParentID int;
	DECLARE @Heirarchy varchar(100) = '';
	
	SELECT @ParentID = ParentAssetID FROM dbo.Assets WHERE AssetID = @AssetID;
	
	WHILE (@ParentID <> 0)
	BEGIN
		SET @Heirarchy = (@Heirarchy + '(' + CAST(@ParentID AS varchar) + ')');
		SELECT @ParentID = ParentAssetID FROM dbo.Assets WHERE AssetID = @ParentID;
	END
	
	RETURN @Heirarchy;
END

and then added a computed column to the Assets table to use the function:

ALTER TABLE dbo.Assets ADD Heirarchy AS dbo.udfGetAssetHeirarchy([AssetID]);

I would now like to index the computed column to save a few IO cycles but when i try the following:

CREATE INDEX idxAssetsHeirarchy ON Assets(Heirarchy);

i receive the error "Column 'Heirarchy' in table 'Assets' cannot be used in an index or statistics or as a partition key because it does user or system data access."

I don't really understand what it's getting at and neither does google.
Anyone got any ideas?

 

[hmckillop]

I am just trying to understand the logic of what it is you are doing and why you need an index on the hiearchy computed column.

Your function assumes that each asset can only have one parent id.
e.g. assetid 1 parentid 2, assetid 2 parentid 3 (but cant have) assetid 1 parentid 3, assetid 2 parentid 3

Everytime you add a row or delete a row you have to recaculate all the rows and then rebuild an index.

I also dont see any benefit you would get by trying to index this field.


As alternative option you mind want to look a recursive CTE at runtime rather than a calculated field.

I may be way off here, but just my 2 cents



"I'm living so far beyond my income that we may almost be said to be living apart

 

[starsky51]

Hi hmckillop,
My idea was to quickly track all decendents of any Asset by doing a LIKE on the computed Heirarchy column
eg. to find all children, grand-children, great-grand... etc.. of the asset with AssetID 113: SELECT AssetID FROM Assets WHERE Heirarchy LIKE '%(113)%'

>> Your function assumes that each asset can only have one parent id.
>> e.g. assetid 1 parentid 2, assetid 2 parentid 3 (but cant have) assetid 1 parentid 3, assetid 2 parentid 3
Not sure why multiple assets can't have the same parentid. I have inserted data into the table and it all seems to link together well.

>> Everytime you add a row or delete a row you have to recaculate all the rows and then rebuild an index.
Rebuilding the index could be a problem, but the assets table won't be updated very often so shouldn't be too bad. My main objective is to speed up the process of reading the data.

I've not looked at CTEs before. I'll be doing some research now though.

Appreciate your advice.

 

[hmckillop]

Have a look at this, which I think is similiar to what you need

http://msdn.microsoft.com/en-us/library/ms186243.aspx

The point on your function I have put in comments in the code

CREATE FUNCTION dbo.udfGetAssetHeirarchy(@AssetID int)
RETURNS varchar(100)
WITH SCHEMABINDING
AS
BEGIN
    DECLARE @ParentID int;
    DECLARE @Heirarchy varchar(100) = '';
    
    SELECT @ParentID = ParentAssetID FROM dbo.Assets WHERE AssetID = @AssetID;
    
    WHILE (@ParentID <> 0)
    BEGIN
        SET @Heirarchy = (@Heirarchy + '(' + CAST(@ParentID AS varchar) + ')');

--this next statement though may have many rows, will only ever set @ParentID to be one value. Therefore if it has siblings then only one row will be populated, thus missing rows. - you can execute this statement outside of the function and see what value @ParentID is when executed.

        SELECT @ParentID = ParentAssetID FROM dbo.Assets WHERE AssetID = @ParentID;
    END
    
    RETURN @Heirarchy;

Any other questions just pop them on this thread and should be able to help.
Good luck

"I'm living so far beyond my income that we may almost be said to be living apart