T-SQL – replace TOP 1 field with MAX(field) to gain speed, cure errors

Say,  you need to get a single top order number from a related table.

You might use this TOP 1 syntax:

, ISNULL((SELECT TOP 1 D1.CDDOCO FROM CRPDTA.F1721 D1
WHERE D1.CDNUMB = FANUMB AND D1.CDCDTE > @TODAY
ORDER BY D1.CDDOCO DESC), '') [Top contract]

However, if your output returns over 200K rows, you might experience a significant delay in execution and eventually see this error message:

Could not allocate space for object 'dbo.SORT temporary run storage:  140878793605120' in database 'tempdb' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.

To fix this problem temporary, you simply can restrict the data selection to return smaller chunks of data at one time.

To permanently fix this problem  there is a simple solution.  Replace TOP 1 field syntax with MAX(field) syntax:

, ISNULL((SELECT MAX(D1.CDDOCO) FROM CRPDTA.F1721 D1
WHERE D1.CDNUMB = FANUMB AND D1.CDCDTE > @TODAY), '') [Top contract]

After the fix, the query finishes in minutes, returns proper results, and confirms that SELECT TOP 1 field logic is significantly more taxing than SELECT MAX(field).

Many happy data returns!