How to get the maximum memory consumed by all data types on a SQL server?

Question

Is it possible to get the memory consumed by a data type in a SQL server? I want to get memory 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 creating such a table?

I need this table so that I can use it to find out the maximum memory that one row of ANY sql server can consume.

If there is no SQL query to get my table, I can create the table manually for it. But, is my table the right way?

Answer 1

The DATALENGTH (ColumnName) function will help. It returns the data length in bytes.

Request -

 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.

The maximum size of one table row is

 SELECT SUM(DataSize) AS MaxRowSize FROM ( SELECT COLUMN_NAME, DATALENGTH(COLUMN_NAME) as DataSize from information_schema.columns where table_name = 'shop' ) AS SCHEM 

The maximum size of the dataset that interests you is

 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 

Information about the scheme of the table stores -

 COLUMN_NAME DataSize id 4 date 8 sales 10 

Test Example with AdventureWorks2008R2 Database -

Save the query results in a text file. See Text File Size. If this is approximately equal to the value returned by my request, then I think we should be good to go.

 select * from person.Person where FirstName = 'crystal' -- Records = 22 

File: Size = 15.2 KB Request: 6116 bytes

Result - does not work for this table! : (

Test 2 -

Salary table -

 [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 Request: Size = 72 bytes.

Result - Great Success

Answer 2

You may run into problems if you use the varchar (max) type, text, image, etc., since they can be a huge amount of memory. However, what you probably want to use as a starting point is view sys.columns, i.e.

 select top 1000 * from information_schema.columns select top 1000 * from information_schema.columns where table_name = 'mytable' 

Look at the results, some interesting columns: character_octet_length and character_maximum_length. But you will see that the type of lifetime integer will have a null value, although these are values ​​that lend themselves to the approach you started with.

ADD

Also note that text / image, etc. are not taken into account when limiting 8K to row size, so depending on why you are calculating the size of the swarm, you may exclude it. This does not apply to some additional databases that SQL Server uses to store rows.

ADD

Fixed size elements such as integer will be displayed as null. You can get the correct size using the approach you started with, or use the equivalent case statement to get the column size in bytes. I do not remember that sql provided this data exactly in the right format.

ADDED Recalled this later

The main types sizes are defined in sys.types - I don’t think I have ever used this, it's easy to forget.