search

yochananrachamim / AzureSQL

129 stars 61 forks source link

Handle table-valued functions #19

Closed TheAceMan closed 2 years ago

TheAceMan commented 3 years ago

The procedure attempts to rebuild indexes on table-valued function. These should be ignored or skipped.

Attempting to run: ALTER INDEX [PK_indexName] ON [dbo].[functionName] REBUILD WITH(ONLINE=ON,MAXDOP=1);

Error: FAILED : 1914 Index cannot be created on object 'dbo.stfGetOrgUnitIdsBySchedGroupOrOrgUnit' because the object is not a user table or view.

yochananrachamim commented 3 years ago

Quote from the discussion on the post itself: We have a few Table Valued functions in which return an "at"TableName table variable with a PRIMARY KEY defined in it. This script will try to include these, and then fails...

"FAILED : 1914Index cannot be created on object 'dbo.functionnamehere' because the object is not a user table or view."

CREATE FUNCTION [dbo].[xxxxxxxxxxxxxxx] () RETURNS @yyyyyyyyyyyyyyyyyyyyyyyyyyyy TABLE ([id] INT IDENTITY(1, 1) PRIMARY KEY NOT NULL,

) I added the following to address this: into #idxBefore from sys.indexes idxs inner join sys.objects [SO] on [SO].object_id = [idxs].object_id AND [SO].[type] IN ('U', 'V') left join sys.dm_db_index_physical_stats(DB_ID(),NULL, NULL, NULL ,@indexStatsMode) i on i.object_id = idxs.object_id and i.index_id = idxs.index_id
yochananrachamim commented 2 years ago

This issue fixed. I changed the join between sys.indexes and index_physical_stats to inner join instead of left join while the index is on function it will not have physical stats information therefore those indexes will not be listed.

fix committed to main branch