Overcoming delegation with SQL server

[coder57]

Hi There

I have read the docs for delegation (delegable-functions ) and my understanding is with SQL server delegation can be achieve using the function FILTER with the operator > (greater than). First question is that correct?

My SQL table has the following structure

CREATE TABLE [dbo].[TestTable](
	[ID] [int] NOT NULL,
	[Row] [nvarchar](50) NOT NULL,
	[Status] [nvarchar](50) NOT NULL,
	[StatusCompleteNo] [nvarchar](50) NOT NULL,
	[SortOrder] [int] NOT NULL,
 CONSTRAINT [PK_TestTable] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

There are over 3000 rows in the table for testing

In my test app I have the following statement for the "on visible" event

ClearCollect(colQC13List,Sort(Filter('[dbo].[TestTable]',ID>0),ID));

However in monitor I get the following

"info": "Partial dataset. 500 rows collected.",
    "issue": "Data from [dbo].[TestTable] is/are truncated to 500 (respectively). The function was applied against incomplete dataset and may return incorrect results."
  }

Can any point out the issue please

Please note the final aim is to apply this to a SQL view but I am having issues with view currently

Many Thanks

[Pstork1 · Accepted solution]

I think you misunderstand what delegation really means.  Delegation means that selection of records is done on the server.  But that doesn't mean that the data set returned can be bigger than the data row limit.  The data row limit is a limit on how big a data set can be retrieved.  So if you are using a non-delegable function the data is retrieved up to the limit and then the function is applied.  With a delegable function the selection is done on the server and the resulting data set is retrieved up to the size imposed by the data row limit.  To collect all 3,000 rows, even using a delegable function, you would need to issue 6 Collect() commands.

ClearCollect(colQC13List,Sort(Filter('[dbo].[TestTable]',ID>0),ID));
Collect(colQC13List,Sort(Filter('[dbo].[TestTable]',ID>=501 && ID <1001),ID));
Collect(colQC13List,Sort(Filter('[dbo].[TestTable]',ID>=1001 && ID <1501),ID));
Collect(colQC13List,Sort(Filter('[dbo].[TestTable]',ID>=1501 && ID <2001),ID));
Collect(colQC13List,Sort(Filter('[dbo].[TestTable]',ID>=2001 && ID <2501),ID));
Collect(colQC13List,Sort(Filter('[dbo].[TestTable]',ID>=2501 && ID <3001),ID));

  But I don't recommend that approach since it can be slow.  Use delegable functions to retrieve only the records you need, not the entire data set.  If you need to work on 3,000 records con-currently consider writing a stored procedure and invoking it through Power Automate.