SQL Server — varchar / nvarchar decreased space, increased performance

Today, I show you, why you should pay attention on table data types. Here is an example.

Both tables contain 0.7 billion rows. The difference is a data type.

First table contains nvarchar data type.

CREATE TABLE [dbo].[GHCN](
[StationID] [nvarchar](50) NOT NULL,
[Date] [date] NOT NULL,
[Type] [nvarchar](50) NOT NULL,
[Value] [int] NOT NULL,
[Alpha2] [varchar](2) NULL
) ON [PRIMARY]

A second one includes varchar data type with decreased amount of characters.

CREATE TABLE [dbo].[GHCN2](
[StationID] [varchar](20) NOT NULL,
[Date] [date] NOT NULL,
[Type] [varchar](10) NOT NULL,
[Value] [int] NOT NULL,
[Alpha2] [varchar](2) NULL
) ON [PRIMARY]

At first glance there is no big difference, especially if a table contains small amount of rows.

Here is comparison:

41GB in camparison with 30GB gives aproximetly 25% difference. Excluding space, you gain higher performance. Engine needs to read less data, engine optimizer reserves less RAM, uses less CPU.

Lets compare query performance.

SELECT 
g.StationID
,gs.StationName
,g.[Date]
,AVG(g.[Value])
,SUM(gs.Elevation)/2
FROM [BigTables].[dbo].[GHCN] g
LEFT JOIN BigTables.dbo.[ghcnd-stations] gs
ON g.StationID = gs.StationID
WHERE [Date] BETWEEN ‘2020–01–01’ AND ‘2020–12–31’
AND Alpha2 IN (‘AU’, ‘US’, ‘PL’)
GROUP BY
g.StationID
,gs.StationName
,g.[Date]

Here is a summary.

GHCN
GHCN2
GHCN
GHCN2

In a case of Hash match (Aggregate) operator, things are more interesting.

GHCN
GHCN2

--

--

--

Data | BI Engineer (Microsoft Stack)

Love podcasts or audiobooks? Learn on the go with our new app.

Recommended from Medium

Need a refresher on DNS concepts

Scale By the Bay 2018: A No BS Hacker Conference

[LeetCode]#175. Combine Two Tables

Alpha Shares — A New Horizon

How to Build Wealth

Modeling Interactions and Behavior

Finding a why in software development

Why do we need a Database to store the data when we can use spreadsheets?

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Michal Molka

Michal Molka

Data | BI Engineer (Microsoft Stack)

More from Medium

T-SQL :: DELETE All Data in Schema

Your SQL Server Colleagues

Power BI and SQL | Date dimension table

Minimal APIs in .NET 6: how to use it to enrich data for analytics — Part1

Process Data from Dirty to Clean: Week3