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.