How to maintain Azure SQL Indexes and Statistics

Hello Yochanan_Rachamim 

Thanks for your code.

We tested your script for our  Azure DBs.  One of our databases contains table functions with the primary key.   For example

CREATE FUNCTION [dbo].[Function]
(
@Parametr1 CHAR,
@Parametr2 TEXT
)
RETURNS @Result TABLE (Column1 SMALLINT IDENTITY(1, 1) PRIMARY KEY, Coulmn2 VARCHAR(8000))
AS
--Calculation

When we ran your script on this DB we had the next error:

Alter index [PK_Function_FFEE232423697868] on [dbo].[Function] REBUILD  WITH (ONLINE=ON,MAXDOP=1,RESUMABLE=ON);

Failed: 1914 Index cannot be created on object '[dbo].[Function]' because the object is not  a user table or view

After this error, I checked the code of procedure and  found the next  actions of procedure:

1 Procedure found this index in the table sys.indexes, but this index doesn't have any properties from sys.dm_db_index_physical_stats. I mean this index has all properties with value NULL( for example page_count, percent_fragmentationa and etc)

2 Next code should set SkipIndex=1 for  it, but it doesn't work  because page_count= null

-- set SkipIndex=1 if conditions for maintenance are not met
		-- this is used to idntify if stats need to be updated or not. 
		-- Check#1 - if table is too small
		update #idxBefore set SkipIndex=1,SkipReason='Maintenance is not needed as table is too small'
		where (
					/*Table is small*/
					(page_count<=@minPageCountForIndex)
				)
				and @mode != 'dummy' /*for Dummy mode we do not want to skip anything */
		

Is this mistake or am I wrong? Also, could you explain why did you use left join in this part of the code? 

from sys.indexes idxs
		left join sys.dm_db_index_physical_stats(DB_ID(),NULL, NULL, NULL ,@indexStatsMode)

 Thanks