Fix the LIMIT for SQL Server 2011 or earlier

[abydahana]

Since my PR on codeigniter4/CodeIgniter4 didn't responded yet, you need to touch the core class to make the limit working with SQL Server 2011 or earlier.

File need changes
vendor/codeigniter4/framework/system/Database/SQLSRV/Builder.php

Find the _limit() protected method and override with following code:

    /**
     * Local implementation of limit
     */
    protected function _limit(string $sql, bool $offsetIgnore = false): string
    {
        if (empty($this->QBOrderBy))
        {
            $this->QBOrderBy    = ' ORDER BY (SELECT NULL) ';
            $sql               .= $this->QBOrderBy;
        }

        if (version_compare($this->db->getVersion(), '11', '>='))
        {
            if ($offsetIgnore)
            {
                $sql .= ' OFFSET 0 ';
            }
            else
            {
                $sql .= is_int($this->QBOffset) ? ' OFFSET ' . $this->QBOffset : ' OFFSET 0 ';
            }

            return $sql .= ' ROWS FETCH NEXT ' . $this->QBLimit . ' ROWS ONLY ';
        }

        $limit = $this->QBOffset + $this->QBLimit;

        // An ORDER BY clause is required for ROW_NUMBER() to work
        if ($this->QBOffset && ! empty($this->QBOrderBy))
        {
            $orderBy = $this->compileOrderBy();

            // We have to strip the ORDER BY clause
            $sql = trim(substr($sql, 0, strrpos($sql, $orderBy)));

            // Get the fields to select from our subquery, so that we can avoid CI_rownum appearing in the actual results
            if (count($this->QBSelect) === 0 OR strpos(implode(',', $this->QBSelect), '*') !== FALSE)
            {
                $select         = '*'; // Inevitable
            }
            else
            {
                // Use only field names and their aliases, everything else is out of our scope.
                $select         = [];
                $fieldRegexp    = ($this->_quoted_identifier ? '("[^\"]+")' : '(\[[^\]]+\])');

                foreach ($this->QBSelect as $i => $singleQBSelect) {
                    $select[]   = (preg_match('/(?:\s|\.)' . $fieldRegexp . '$/i', $singleQBSelect, $m) ? $m[1] : $singleQBSelect);
                }

                $select         = implode(', ', $select);
            }

            return 'SELECT ' . $select . " FROM (\n\n" . preg_replace('/^(SELECT( DISTINCT)?)/i', '\\1 ROW_NUMBER() OVER(' . trim($orderBy) . ') AS ' . $this->db->escapeIdentifiers('CI_rownum') . ', ', $sql) . "\n\n) " . $this->db->escapeIdentifiers('CI_subquery') . "\nWHERE " . $this->db->escapeIdentifiers('CI_rownum').' BETWEEN ' . ($this->QBOffset + 1) . ' AND ' . $limit;
        }

        return preg_replace('/(^\SELECT (DISTINCT)?)/i','\\1 TOP ' . $limit . ' ', $sql);
    }

[abydahana]

For CodeIgniter 4.3.1

/**
 * Local implementation of limit
 */
protected function _limit(string $sql, bool $offsetIgnore = false): string
{
    if (empty($this->QBOrderBy))
    {
        $this->QBOrderBy    = ' ORDER BY (SELECT NULL) ';
        $sql               .= $this->QBOrderBy;
    }

    if (version_compare($this->db->getVersion(), '11', '>='))
    {
        if ($offsetIgnore)
        {
            $sql .= ' OFFSET 0 ';
        }
        else
        {
            $sql .= is_int($this->QBOffset) ? ' OFFSET ' . $this->QBOffset : ' OFFSET 0 ';
        }

        return $sql .= ' ROWS FETCH NEXT ' . $this->QBLimit . ' ROWS ONLY ';
    }

    $limit = $this->QBOffset + $this->QBLimit;

    // An ORDER BY clause is required for ROW_NUMBER() to work
    if ($this->QBOffset && ! empty($this->QBOrderBy))
    {
        $orderBy = $this->compileOrderBy();

        // We have to strip the ORDER BY clause
        $sql = trim(substr($sql, 0, strrpos($sql, $orderBy)));

        // Get the fields to select from our subquery, so that we can avoid CI_rownum appearing in the actual results
        if (count($this->QBSelect) === 0 OR strpos(implode(',', $this->QBSelect), '*') !== FALSE)
        {
            $select         = '*'; // Inevitable
        }
        else
        {
            // Use only field names and their aliases, everything else is out of our scope.
            $select         = [];
            $fieldRegexp    = ($this->_quoted_identifier ? '("[^\"]+")' : '(\[[^\]]+\])');

            foreach ($this->QBSelect as $i => $singleQBSelect) {
                $select[]   = (preg_match('/(?:\s|\.)' . $fieldRegexp . '$/i', $singleQBSelect, $m) ? $m[1] : $singleQBSelect);
            }

            $select         = implode(', ', $select);
        }

        return "SELECT * FROM (\n\n" . preg_replace('/^(SELECT( DISTINCT)?)/i', '\\1 ROW_NUMBER() OVER(' . trim($orderBy) . ') AS ' . $this->db->escapeIdentifiers('CI_rownum') . ', ', $sql) . "\n\n) " . $this->db->escapeIdentifiers('CI_subquery') . "\nWHERE " . $this->db->escapeIdentifiers('CI_rownum').' BETWEEN ' . ($this->QBOffset + 1) . ' AND ' . $limit;
    }

    return preg_replace('/(^\SELECT (DISTINCT)?)/i','\\1 TOP ' . $limit . ' ', $sql);
}