Introduction

Sampling data is a common requirement in many real-world SQL Server workloads. Whether you are trying to test a subset of data, preview records before an export, or build a small development copy of a table, sampling becomes a go-to tool. SQL Server offers an operator called TABLESAMPLE, which looks simple and promising at first. When someone writes a query like select top 100 from Orders tablesample 10 percent, the natural expectation is that SQL Server will return a random 10 percent of the rows. Unfortunately, that’s not how it works.

The TABLESAMPLE clause does not operate on individual rows but rather on the physical data pages. This means SQL Server tries to return rows from approximately 10 percent of the total pages, not rows. If your data is evenly distributed and each page is full, it might give you close to 10 percent. But in reality, due to fragmentation, updates, and deletes, most pages contain varying number of rows. That is where TABLESAMPLE becomes highly unpredictable. Let us simulate this with a quick example to demonstrate the behavior in practice.

Step-by-Step Simulation of TABLESAMPLE

Create a table named SampleTest with three columns — an identity column called ID, a default name column, and a datetime column that records the current timestamp.

create table SampleTest
(
ID int identity(1,1),
Name varchar(100) default 'Test',
CreatedDate datetime default getdate()
)

Step 2: Insert some rows to make the table large enough to sample from.

declare @i int = 0
while @i < 10000
begin
insert into SampleTest default values
set @i = @i + 1
end

Now we run the sampling.

select count(*) from SampleTest tablesample (10 percent)

Try running this query multiple times. In the first run, I got a row count of 1408. I ran it again, and got 895. Third execution gave 1253.

First Run

Second Run

Third Run

This happens because SQL Server chooses a different set of pages each time. It does not guarantee which rows will be chosen. Even worse, two consecutive runs can give two sets of results with no overlap. You might get different date ranges, different IDs, and even different value distributions.

If you had a clustered index on CreatedDate, for example, one sample might have mostly older rows and the next might have mostly recent ones. This unpredictability makes TABLESAMPLE unsuitable for use cases where uniformity or fairness of sample matters. For example, in financial reporting, fraud detection, or AI training datasets, using TABLESAMPLE could introduce hidden bias. You might think you’re working with a fair representation of the data, but SQL Server might have skipped entire chunks of it.

TABLESAMPLE with ROWS Still Works on Pages

Here’s a lesser-known twist with TABLESAMPLE. If you use the syntax TABLESAMPLE (100 ROWS), expecting exactly 100 rows, you will be surprised. SQL Server does not read 100 individual rows. It internally estimates how many pages would roughly contain 100 rows based on current statistics, and then it reads those pages instead. Since some pages might be half full and others might be over-packed, the actual number of rows returned can vary widely. One run might give you 52 rows and another run might return 135 rows — even though you requested exactly 100, and next run may give zero(as shown in below screenshot).

The core reason is that all TABLESAMPLE operations, whether you specify ROWS or PERCENT, are page-based. SQL Server always maps your request to an approximate number of pages. So even though you are writing a row-level intention, the engine executes a page-level action. That’s why you should never trust TABLESAMPLE for fixed-size output.

For demonstration run this query as below:

select count(*) from SampleTest tablesample (100 rows)

To confirm this, you can open the actual execution plan in SSMS and hover over the Table Scan operator. In the execution plan output, you will notice that SQL Server shows "Estimated Number of Rows for All Executions = 100" and also "Estimated Number of Rows Per Execution = 100". This proves that the optimizer planned for 100 rows. However, the actual number of rows retrieved was "0" in the screenshot shown here, which means that in that run, even though SQL Server aimed to retrieve 100 rows by estimating page counts, the actual rows returned were zero. This is direct evidence that TABLESAMPLE with 100 ROWS does not literally scan for 100 rows. It uses metadata and statistics to estimate how many data pages might contain that many rows, and those pages are scanned — but the actual contents depend on page density, fragmentation, and internal row layout.

This mismatch between estimated and actual rows reinforces the fact that TABLESAMPLE, even in ROW mode, is not a row-level operator. It behaves entirely based on pages. And the inconsistency shown in the execution plan confirms why this operator is unpredictable in scenarios that expect accuracy or fixed-size samples. If your use case demands exactly 100 rows, you must not rely on TABLESAMPLE. Use row-level methods instead, such as order by newid or deterministic checksum filters. TABLESAMPLE is fast, but only suitable for approximate sampling, not guaranteed outcomes.

When TOP Meets TABLESAMPLE: Total Random Mess

Now here is where things get unexpectedly chaotic. Try combining TOP (1) with TABLESAMPLE in a single query, like this:

select top 1 from SampleTest tablesample (10 percent)

At first glance, this looks harmless. But try running it five times in a row. You will get five different rows every single time. That is because TABLESAMPLE already introduces random page-level sampling, and then TOP just picks the first row from that unpredictable result set — which makes the final output even more unstable. There is no guarantee which pages will be included in the 10 percent, and there is no ordering within that sample. So the combination leads to what we call total random mess — where neither the sampling nor the row selection is deterministic.

This kind of query is sometimes used in quick demos or testing, but in production or analysis, it can silently break expectations. If you ever need a single random row, you are much better off using select top 1 from SampleTest order by newid. It is slower, but it delivers a true per-row random behavior with consistency.

Fixed-Checksum Sampling: Fast, Repeatable, and Controlled

One bonus trick that is not widely used is fixed-checksum sampling. Instead of relying on randomness, you use a mathematical filter that gives you the same sample every time. For example, this query gives you a repeatable 10 percent slice of your data:

select from SampleTest where abs(checksum(ID)) % 100 < 10

This method uses the ID column as a seed for the checksum function, which produces a consistent numeric value. Applying modulo logic allows you to extract roughly 10 percent of rows based on this pattern. The sample will not be random in the traditional sense, but it will be stable and reproducible. This is very helpful when you want to use the same subset across multiple environments, or want to perform validations that need consistency.

For example, your dev, staging, and production environments can all operate on the same logical sample just by running the same query. You can even use this approach for partitioned test cases, by using different modulus ranges for different teams. Just remember — this works best on integer columns like identity or surrogate keys. If the column is skewed or contains long strings, checksum might not distribute the values evenly, which can cause bias. But with proper usage, this technique gives you the speed of TABLESAMPLE and the control of NEWID, without the sorting cost.

Comparison with ORDER BY NEWID for Row-Level Sampling

Now let us compare it with another technique. Suppose we want to fetch exactly 1000 random rows out of our 10000-row table. One safe and repeatable approach is to use order by newid. Here’s how we write it.

select top 1000 * from SampleTest order by newid()

This query works differently. It generates a random guid for each row using newid, sorts the entire table by those guids, and returns the top 1000. Since the guids are randomly generated at runtime, every run gives a different set of rows. However, unlike TABLESAMPLE, this is row-level randomness. It is slow because sorting 10000 guids takes time, especially on large tables, but the sample is fair and consistent. This is useful when you are testing logic that depends on representative data. You can even store the newid value in a column if you want reproducibility.

Developers often use this technique for training data preparation, UI previews, or staggered processing. In summary, TABLESAMPLE is fast but not reliable. Order by newid is reliable but not fast. You must choose based on what matters more — speed or accuracy.

Summary

Sampling in SQL Server may look simple at first glance, but as we’ve seen, it is full of hidden behaviors and side effects that can silently impact your logic. The TABLESAMPLE operator works at the page level, regardless of whether you use PERCENT or ROWS. Even when you ask for TABLESAMPLE (100 ROWS), SQL Server estimates the number of pages that might contain around 100 rows and returns rows from those pages — meaning the actual number of rows can vary greatly between executions. When combined with TOP, it becomes even more unstable, and the output can differ every single time.

If your requirement is strict control over the number of rows or reproducibility, then TABLESAMPLE is not the tool for the job. Instead, you can use ORDER BY NEWID for true row-level randomness or a checksum-based filter when you need fast, repeatable, and controlled subsets of data. These approaches let you avoid the chaos of page-level sampling and give you full control over the sampling logic.

The bottom line is — don’t use TABLESAMPLE blindly assuming it gives you what it says. Understand how it works internally and when to avoid it. It is great for fast approximations and quick tests, but not for consistent results or sensitive analysis. Knowing when to use TABLESAMPLE, when to avoid it, and what alternatives exist — that’s what separates a cautious SQL developer from one stuck debugging inconsistent results for hours.