Where to find the size of SQL Server data types
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://learn.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.
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.