When working with databases, it's often necessary to limit the number of rows returned from a query — for example, to preview data, implement pagination, or optimize performance. In MySQL, you'd typically use the LIMIT clause, but Microsoft SQL Server (MSSQL) doesn't support LIMIT. Instead, it uses the TOP keyword or the OFFSET-FETCH clause.

In this guide, you'll learn how to retrieve limited rows from a SQL Server database using Python and the pyodbc library, including examples of TOP, OFFSET, and ORDER BY.

✅ Prerequisites

Tools Required

• Python 3.7 or newer

• Microsoft SQL Server (any edition)

• ODBC Driver 17 or 18 for SQL Server

Python Library

Install pyodbc if you haven’t already:

pip install pyodbc

Sample Table

We'll use this Employees table for demonstration:

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FirstName NVARCHAR(50),
    LastName NVARCHAR(50),
    Salary DECIMAL(10, 2),
    HireDate DATE
);

Step 1: Connect to the Database

import pyodbc

conn = pyodbc.connect(
    "DRIVER={ODBC Driver 17 for SQL Server};"
    "SERVER=localhost;"
    "DATABASE=EmployeeDB;"
    "UID=your_username;"
    "PWD=your_password;"
)
cursor = conn.cursor()

Step 2: Use TOP to Limit Results

SQL Server uses TOP to return a limited number of rows.

Example: Get the first 5 employees

cursor.execute("SELECT TOP 5 * FROM Employees")
rows = cursor.fetchall()

for row in rows:
    print(row.FirstName, row.LastName, row.Salary)

✅ TOP is simple and works in all SQL Server versions.

Step 3: Use ORDER BY with TOP

To get the top 5 highest-paid employees:

cursor.execute("""
    SELECT TOP 5 * FROM Employees
    ORDER BY Salary DESC
""")
rows = cursor.fetchall()

for row in rows:
    print(row.FirstName, row.Salary)

⚠️ Without ORDER BY, the result may be unpredictable.

Step 4: Use OFFSET-FETCH for Pagination (SQL Server 2012+)

This is SQL Server’s version of LIMIT and OFFSET.

Example: Skip 5 and get next 5 employees (rows 6–10)

cursor.execute("""
    SELECT * FROM Employees
    ORDER BY EmployeeID
    OFFSET 5 ROWS FETCH NEXT 5 ROWS ONLY
""")
rows = cursor.fetchall()

for row in rows:
    print(row.EmployeeID, row.FirstName)

Use this pattern for pagination: OFFSET X ROWS FETCH NEXT Y ROWS ONLY.

Full Working Example: Paginated Fetch

import pyodbc

def get_paginated_employees(offset, limit):
    conn = pyodbc.connect(
        "DRIVER={ODBC Driver 17 for SQL Server};"
        "SERVER=localhost;"
        "DATABASE=EmployeeDB;"
        "UID=your_username;"
        "PWD=your_password;"
    )
    cursor = conn.cursor()

    cursor.execute(f"""
        SELECT * FROM Employees
        ORDER BY EmployeeID
        OFFSET ? ROWS FETCH NEXT ? ROWS ONLY
    """, (offset, limit))

    rows = cursor.fetchall()
    for row in rows:
        print(f"{row.EmployeeID}: {row.FirstName} {row.LastName}")

    cursor.close()
    conn.close()

# Usage: Get employees 11–20
get_paginated_employees(10, 10)

Tips for Using LIMIT in MSSQL with Python

⚠️ Common Pitfalls

Best Practices

• Prefer OFFSET-FETCH for pagination in modern applications.

• Combine LIMIT-style queries with indexed ORDER BY to maintain performance.

• If using Django, SQLAlchemy, or another ORM, use built-in pagination features to abstract this logic.

Conclusion

While SQL Server doesn't use the LIMIT keyword like MySQL, it offers powerful alternatives: TOP and OFFSET-FETCH. These can be easily integrated into your Python application using pyodbc. For paginated APIs or data views, OFFSET-FETCH is the preferred modern approach. Just be sure to always use ORDER BY and test for performance and correctness.