Where to find the size of SQL Server data types

Question

I am trying to calculate the size of my database. I will have a table with 3 columns (id, int, money) I will have 26 million rows with all columns being occupied. How big will my database be? Also, where can I find the size of all SQL Server data types?

Answer 1

Your can use below query :

SELECT * FROM sys.types

result of above query is below :

name                 system_type_id user_type_id schema_id principal_id max_length precision scale collation_name    is_nullable is_user_defined is_assembly_type default_object_id rule_object_id is_table_type
-------------------- -------------- ------------ --------- ------------ ---------- --------- ----- ----------------- ----------- --------------- ---------------- ----------------- -------------- -------------
image                34             34           4         NULL         16         0         0     NULL              1           0               0                0                 0              0
text                 35             35           4         NULL         16         0         0     Persian_100_CI_AI 1           0               0                0                 0              0
uniqueidentifier     36             36           4         NULL         16         0         0     NULL              1           0               0                0                 0              0
date                 40             40           4         NULL         3          10        0     NULL              1           0               0                0                 0              0
time                 41             41           4         NULL         5          16        7     NULL              1           0               0                0                 0              0
datetime2            42             42           4         NULL         8          27        7     NULL              1           0               0                0                 0              0
datetimeoffset       43             43           4         NULL         10         34        7     NULL              1           0               0                0                 0              0
tinyint              48             48           4         NULL         1          3         0     NULL              1           0               0                0                 0              0
smallint             52             52           4         NULL         2          5         0     NULL              1           0               0                0                 0              0
int                  56             56           4         NULL         4          10        0     NULL              1           0               0                0                 0              0
smalldatetime        58             58           4         NULL         4          16        0     NULL              1           0               0                0                 0              0
real                 59             59           4         NULL         4          24        0     NULL              1           0               0                0                 0              0
money                60             60           4         NULL         8          19        4     NULL              1           0               0                0                 0              0
datetime             61             61           4         NULL         8          23        3     NULL              1           0               0                0                 0              0
float                62             62           4         NULL         8          53        0     NULL              1           0               0                0                 0              0
sql_variant          98             98           4         NULL         8016       0         0     NULL              1           0               0                0                 0              0
ntext                99             99           4         NULL         16         0         0     Persian_100_CI_AI 1           0               0                0                 0              0
bit                  104            104          4         NULL         1          1         0     NULL              1           0               0                0                 0              0
decimal              106            106          4         NULL         17         38        38    NULL              1           0               0                0                 0              0
numeric              108            108          4         NULL         17         38        38    NULL              1           0               0                0                 0              0
smallmoney           122            122          4         NULL         4          10        4     NULL              1           0               0                0                 0              0
bigint               127            127          4         NULL         8          19        0     NULL              1           0               0                0                 0              0
hierarchyid          240            128          4         NULL         892        0         0     NULL              1           0               1                0                 0              0
geometry             240            129          4         NULL         -1         0         0     NULL              1           0               1                0                 0              0
geography            240            130          4         NULL         -1         0         0     NULL              1           0               1                0                 0              0
varbinary            165            165          4         NULL         8000       0         0     NULL              1           0               0                0                 0              0
varchar              167            167          4         NULL         8000       0         0     Persian_100_CI_AI 1           0               0                0                 0              0
binary               173            173          4         NULL         8000       0         0     NULL              1           0               0                0                 0              0
char                 175            175          4         NULL         8000       0         0     Persian_100_CI_AI 1           0               0                0                 0              0
timestamp            189            189          4         NULL         8          0         0     NULL              0           0               0                0                 0              0
nvarchar             231            231          4         NULL         8000       0         0     Persian_100_CI_AI 1           0               0                0                 0              0
nchar                239            239          4         NULL         8000       0         0     Persian_100_CI_AI 1           0               0                0                 0              0
xml                  241            241          4         NULL         -1         0         0     NULL              1           0               0                0                 0              0
sysname              231            256          4         NULL         256        0         0     Persian_100_CI_AI 0           0               0                0                 0              0
CalculatedCreditInfo 243            257          9         NULL         -1         0         0     NULL              0           1               0                0                 0              1
udt_QoutaDetail      243            258          21        NULL         -1         0         0     NULL              0           1               0                0                 0              1
BeforeUpdate         243            259          22        NULL         -1         0         0     NULL              0           1               0                0                 0              1
udt_StoreInventory   243            260          26        NULL         -1         0         0     NULL              0           1               0                0                 0              1
udt_WKFHistory       243            261          32        NULL         -1         0         0     NULL              0           1               0                0                 0              1
IDTable              243            262          1         NULL         -1         0         0     NULL    

you can use max_length for size of each data type.

Answer 2

T-SQL has a function for that: DATALENGTH for all SQL Server versions.

Example:

DECLARE @lat DECIMAL(10, 7) = 3.14151415141514151415;

SELECT @lat, DATALENGTH(@lat);

Result: 3.1415142 and 5 (because DECIMAL(10,7) uses 5 bytes to be stored).

Documentation: https://docs.microsoft.com/en-us/sql/t-sql/functions/datalength-transact-sql?view=sql-server-ver15

For example, I have a table called Applications with these columns: (id VARCHAR(32), debug BIT, connectionString VARCHAR(2048), firebaseKey VARCHAR(4096)). As we know, VARCHAR doesn't allocate all the space (just what you need, so 'A' is 1 byte in VARCHAR).

These queries:

SELECT 
  SUM(DATALENGTH(id)) AS idSize,
  SUM(DATALENGTH(debug)) AS debugSize,
  SUM(DATALENGTH(connectionString)) AS connectionStringSize,
  SUM(DATALENGTH(firebaseKey)) AS firebaseKeySize
FROM Applications;

SELECT 
  SUM(
    DATALENGTH(id) +
    DATALENGTH(debug) + 
    DATALENGTH(connectionString) +
    DATALENGTH(firebaseKey)
  ) AS totalSize
FROM Applications;

will return my data size (in my case, with my rows, is 8, 2, 366, 4698 (total: 5074). There are 2 rows in that table.

Notice that this does NOT represent the total size of my database (there are pages, descriptors, indexes, etc. involved.)*

MSSQL has internal stored procedures to tell you the exactly size of your database in disk:

• EXEC sp_spaceused; for all database;
• EXEC sp_spaceused N'schema.TableName'; for a specific table;
• EXEC sp_helpdb N'DatabaseName'; if you want details from each file.