Q:

How do I get the maximum memory consumed by all data types in SQL server?

Is it possible to get the memory consumed by a data type in
SQL server ? I want to get the memory in this way - 
Id, datatypes, base_memory (in bytes), memory_multiplier
1, varchar(x), 2, x
2, int, 2, 1
et cetra.

Are there any problems in making such a table ?
I need this table so that I can use it to find out the
maximum memory that can be consumed by one row of ANY 
sql server table.
If there is no SQL query for getting my table, then 
I can create a table manually for it. But, is my table 
the correct way ? 

A:

The function DATALENGTH(ColumnName) will help. It returns the length
of data in bytes.
Query - 
SELECT COLUMN_NAME, DATALENGTH(COLUMN_NAME) as DataSize
from information_schema.columns where table_name = 'MyTableName'

This works, but I don't know if there are any pitfalls in this approach.
Max size of one row of a table - 
SELECT SUM(DataSize) AS MaxRowSize
FROM
(
SELECT COLUMN_NAME, DATALENGTH(COLUMN_NAME) as DataSize
from information_schema.columns where table_name = 'shop'
) AS SCHEM

Max size of the data set you are interested in - 
DECLARE @maxRowSize int
SET @maxRowSize =
(
SELECT SUM(DataSize) AS MaxRowSize
FROM
(
SELECT COLUMN_NAME, DATALENGTH(COLUMN_NAME) as DataSize
from information_schema.columns 
where table_name = 'shop' -- enter table name only, do NOT enter the schema
) AS SCHEM
)
SELECT COUNT(Id) as NumOfRows, 
@maxRowSize as MaxRowBytes, 
COUNT(id) * @maxRowSize as MaxQueryBytes
FROM Shop -- Enter schema name if needed

Shop table schema information - 
COLUMN_NAME DataSize
id          4
date        8
sales       10

Sample test with AdventureWorks2008R2 Database -
Save the results of a query to a text file. See the size of text file. If it is approximately equal to the value returned by my query, then I think we should be
good to go.
select * 
from person.Person
where FirstName = 'crystal'
-- Records = 22

File: Size = 15.2KB
Query: 6116 bytes
Result - Does not work for this table ! :(
Test 2 - 
Table salaries - 
[id] [int], [name] [varchar](50), [salary] [decimal](18, 2)

Table - 
id  name            salary
1   azamat bagatov  100.50
2   borat           25.00
3   coci buchek     200.50

File: Size = 65 bytes
Query: Size = 72 bytes.
Result - Great success ???

