How to Find Outliers in SQL Server

Problem

Outliers can significantly distort statistical analysis and lead to incorrect conclusions when interpreting data. In this article, we will look at how to find outliers in SQL Server using various T-SQL queries. Understanding how to find outliers in SQL is crucial for accurate data analysis.

Solution

Data often contains observations that do not fit the rest of the dataset. It is best practice to regularly check for outliers, providing useful takeaways about the data. Outliers occur for various reasons. However, each occurrence offers a chance to improve, like collecting the data using alternate methods or choosing more suitable models for statistical inferences.

The approaches used to identify outliers must be thoroughly evaluated; discordant random observations can be easily mistaken for outliers. Examples of sources of outliers include:

• Significant errors from transposing data points, generally human oversight.
• Misspecified distribution.
• A data point that falls considerably far from the rest of the dataset.

The importance of detecting outliers is to obtain data integrity, avoid statistical analysis distortion, improve model accuracy, and identify anomalies.

Terms and Definitions

Outlier: A data point that is inconsistent with the rest of the data points.

Masking: When the inconsistent data point is hidden or not accounted for, therefore resulting in a distorted relationship among the variables.

Interquartile Range (IQR): A measure of statistical dispersion that spreads data and is defined as the difference between the 75th and 25th percentiles of the data.

Sample Data

Create a table to hold our data:

CREATE TABLE [dbo].[Outlier](
      [ObsItem] [int] IDENTITY(1,1) NOT NULL,
      [ObsValue] [decimal](18, 4) NULL,
 CONSTRAINT [PK_Outlier] PRIMARY KEY CLUSTERED 
(
      [ObsItem] 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

Insert some data to work with:

INSERT INTO  [dbo].[Outlier] ([ObsValue])
   VALUES (2.1),(2.6),(2.4),(2.5),(2.3),(8.2),(2.1),(2.3),(2.6),(8.3);

How to find outliers in SQL with Interquartile Range (IQR) Method

We know that for a set of ordered numbers, the median Q2, is the middle number that divides the data into two halves. Similarly, the lower quartile Q1 divides the bottom half of the data into two halves, and the upper quartile Q3 also divides the upper half of the data into two halves. The interquartile range is the difference between the upper quartile and lower quartile.

To calculate the interquartile range (IQR) = Q3 – Q1

Outliers using this method are values outside the lowest value Q1 -1.5 * IQR and the highest value Q3 + 1.5 IQR, both indicated by whiskers of the box, as shown below.

Source: https://commons.wikimedia.org/w/index.php?curid=14524285

This query will display the outlier values using our data table.

WITH Quartiles AS 
   (SELECT PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY [ObsValue]) OVER() AS Q1
         ,PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY [ObsValue]) OVER() AS Q3
      FROM [dbo].[Outlier])
   ,Outliers AS 
   (SELECT t.*
         ,q.Q1
         ,q.Q3
         ,(q.Q3 - q.Q1) AS IQR
      FROM [dbo].[Outlier] t CROSS JOIN 
          Quartiles q)
SELECT DISTINCT *
   FROM Outliers
   WHERE [ObsValue] < (Q1 - 1.5 * IQR) OR [ObsValue] > (Q3 + 1.5 * IQR);

Running the query will display the two values to be considered outliers using this method.

How to find outliers in SQL with Z-Scores Method

The Z-score of an observation is defined as data given in units of how many standard deviations it is from the mean. Although it is common practice to use Z-scores to identify possible outliers, this can be misleading, particularly for small sample sizes and the masking effect.

Source: https://www.itl.nist.gov/div898/handbook/eda/section3/eda35h.htm

The following query will calculate the Z-score:

WITH Stt AS 
   (SELECT AVG([ObsValue]) AS Mean
         ,STDEV([ObsValue]) AS StdDev
      FROM [dbo].[Outlier])
   ,ZScores AS 
   (SELECT [ObsValue]
         ,([ObsValue] - Stt.Mean) / Stt.StdDev AS ZScore
      FROM [dbo].[Outlier]
         ,Stt)
SELECT [ObsValue]
         ,ROUND(ZScore,4) AS ZScore
         ,CASE WHEN ZScore > 3 OR ZScore < -3 
               THEN 'Outlier'
               ELSE 'Not Outlier'
          END AS OutlierStatus
   FROM ZScores;

Running the above query results in no values as outliers.

This occurs because of the masking effect, caused by the value 8.2 that masks the 8.3 value. The statistics of our data show how significant the impact of the outliers can be in the dataset.

Because of this, it is recommended to use the following modified Z-Score method:

Source: https://www.itl.nist.gov/div898/handbook/eda/section3/eda35h.htm

Run the following query:

DECLARE @Median decimal(18,4) = 
   (SELECT DISTINCT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY [ObsValue]) OVER () AS Median
      FROM [dbo].[Outlier]);
 
DECLARE @MAD decimal(18,4);
 
WITH AbsoluteDeviations AS 
   (SELECT [ObsValue]
         ,ABS([ObsValue] - @Median) AS AbsoluteDeviation
      FROM [dbo].[Outlier])
SELECT DISTINCT  @MAD = PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY AbsoluteDeviation) OVER() 
   FROM AbsoluteDeviations; 
 
SELECT [ObsValue]
      ,FORMAT(0.6745 * ([ObsValue] - @Median) / @MAD, '0.0000') AS zMod
      ,CASE WHEN ABS(0.6745 * ([ObsValue] - @Median) / @MAD) > 3.5 
           THEN 'Outlier'
           ELSE 'Not Outlier'
       END AS OutlierStatus
    FROM [dbo].[Outlier];

Using this method avoids the masking effect.

Next Steps

• Much more can be explored about outlier detection, like multivariate outliers, which is based on the relationships between two or more variables.
• Review these related articles:
  • WIKIPEDIA – Interquartile Range (IQR)
  • NIST – Detection of Outliers
  • NIST – Measures of Scale
  • SQL Server Video to Remove Outliers from Data Sets
  • SQL Server Stored Procedure to remove outliers from data sets

Sebastiao Pereira

Sebastião Pereira has over 40 years of experience in database development including T-SQL, algorithm design, machine learning and bringing innovative mathematical formulas to SQL Server. He started his career at a transnational fast-moving consumer goods (FMCG) company as an employee then later transitioning into a consultant role. He eventually founded his own company to develop software solutions for the healthcare industry.  Sebastião is a respected award-winning author on MSSQLTips.com extending SQL Server capabilities beyond traditional workloads.

• MSSQLTips Awards
  • Author of the Year – 2025
  • Trendsetter (25+ tips) – 2025
  • Rookie of the Year – 2024