Calculate max lenth of a database row. 1

[EM1107]

Good day everyone.

I do not know if anyone can help me on this but here is my question.

Does anyone know of a way that you can calculate the maximum number of character being use on a database row in SQL Server?

For instance let say I have a table with 5 columns.

COL1, col2, col3, col4,col5.

All of the columns above would be different type but I want to know how many character these 5 rows is using compare to the maximum number of character SQL Server allow.

I hope that I am clear enough.
Thanks in advance.

 

[SgtJarrow]

How about using the LEN function of each column and summing them up?

SELECT
    LEN(Col1) + LEN(Col2) + LEN(Col3) + LEN(Col4) + LEN(Col5) 'TextLengthOfAllColumns'
FROM Table;

Of course, this only gives you the TEXT length of the row. But that is what you asked for...a character count of the row. This will not provide the "size" of the row as far as database storage is concerned.

Now you might also be asking for the MAXIMUM of each of those summed rows. Maybe you are creating a new column to hold the combined data.

You could always set your new column to VARCHAR(MAX). This column is for all concerns that matter is only limited by the size of the hard drive space allocated to the database.

But if you want a hard number, then something like this will tell you the MAXIMUM text length of all rows.

WITH cteRowLengths (TextLength) AS
(
     SELECT
          LEN(Col1) + LEN(Col2) + LEN(Col3) + LEN(Col4) + LEN(Col5) 'TextLength'
     FROM Table
)
SELECT
     MAX(TextLength) 'LongestTextLength'
FROM cteRowLengths;

Robert "Wizard" Johnson III
U.S. Military Vets MC
CSM, CSPO, MCPD, CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
Senior Database Developer

 

[Andrzejek]

I don't know if that's the right question.
Let's say your Col2 is a Date, the value the data base keeps is 42543.5 (noon today)
When you look at this data, you may see: June 22, 2016, 12:00:00pm, or 6/22/2016 12:00:00 PM, or Wednesday, June 22nd, 2016, or just 6/22/2016, depending of how you want to Format the date.

So, how many "characters" this one field is using?

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[SgtJarrow]

All of the columns above would be different type but I want to know how many character these 5 rows is using compare to the maximum number of character SQL Server allow.

I completely missed this line. Thanks for pointing that out Andrzejek. My examples above assumed TEXT-based fields. This changes completely when looking at date and numeric columns. More info would help. Provide some examples of all the different columns, what the "display" of each should look like (date format, numeric precision, etc) and a rough idea of what you expect the output to look like please.

Robert "Wizard" Johnson III
U.S. Military Vets MC
CSM, CSPO, MCPD, CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
Senior Database Developer

 

[EM1107]

Thank you for the update but this is not exactly what I am looking for.

Microsoft state that the maximum number of bites that can be inserted in a row is (8,060 bytes)

What I want to find is what is the maximum number of bytes all my columns are using on my table in order to figure out how close I am to the maximum.

 

[SgtJarrow]

That's a completely different question but that's ok. Now we are on the right track to helping you find the answer to your question.

Try using this:

DBCC SHOWCONTIG WITH TABLERESULTS

Don't change anything...just run this statement on your server. Then find your tablename in the results and you can see all kinds fo stats about your table, including MIN, MAX, AVG length of rows, Fragmentation, etc.

Robert "Wizard" Johnson III
U.S. Military Vets MC
CSM, CSPO, MCPD, CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
Senior Database Developer

 

[SgtJarrow]

One quick follow-up. To filter the results to only show the table you are interested in, use:

DBCC SHOWCONTIG ('Schema.Table') WITH TABLERESULTS

As normal, Schema can be left off if the Table is located in the default Schema (usually dbo).

Robert "Wizard" Johnson III
U.S. Military Vets MC
CSM, CSPO, MCPD, CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
Senior Database Developer

 

[EM1107]

Thanks SgtJarrow, this is what I was looking for.

One more question. Microsoft says that each rows should not be more than 8,069. I can add multiple varchar column with 8000 bits each on that same row and SQL Servers seems to take it.
What will be the down side to doing that?
I have a database that include a table that already have 94 columns and my client want to add an other 25 varchar columns on the same tables.
What I am trying to do here is convince my client that adding anymore varchar columns on the tables would not be a good thing.

 

[Olaf Doschke]

If you design a table which would have a structure exceeding that limit, you'd not be able to create it.

Check the following for a table of interest:

SELECT type_desc, total_pages, used_pages,data_pages 
FROM sys.allocation_units
WHERE container_id = (SELECT partition_id FROM sys.partitions 
WHERE OBJECT_ID = OBJECT_ID('YourTable'))

See

https://sqlserverzest.com/2013/10/1...units-in-row-data-lob-data-row-overflow-data/

Bye, Olaf.

 

[SgtJarrow]

You are diving more into DBA-type questions...Not programming questions. This is not my strongest area and I'd recommend you try the Microsoft SQL Server: Setup and Administration Forum (

http://www.tek-tips.com/threadminder.cfm?pid=962).

Based on my limited DBA knowledge, you might be mixing a couple concepts. I can say for sure that having a table with some 125 columns is not unheard of. I tend to break my tables into smaller column lengths (try to stay less than 50 when possible) that are all related values and create PK/FK relationships to manage the connections.

This usually lines directly up with business needs...I work for a University and we have a master student table, then a table for finance values, one for academic values, one for contacts, etc. Each table has the same primary key we can join to when we need to include data from that set.

But I believe the maximum number of columns in a table is close to 255. May have changed with newer versions of SQL but that's what I know off the top of my head.

I can also confirm that you can have tables with all kinds of VARCHAR(MAX) columns. I have one table I use to bulk import a 500Mb flat file daily. The first step is to import it into the table that has 143 VARCHAR(MAX) columns...just bring the raw data into the table so I can begin to manipulate it. Using the DBCC command above, my average row size is only showing 249.479. Way below the limit you are referring to.

I also think the size limit you are referring to may be related to keeping all data for the record on the same page file. That would be a performance thing. I might be wrong about that, but I can find all kinds of article about how to "circumvent" this limit anyway so there are ways around it.

Robert "Wizard" Johnson III
U.S. Military Vets MC
CSM, CSPO, MCPD, CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
Senior Database Developer

 

[EM1107]

Thanks guys for all your comments.

I think you have all answered my questions.

SGTJarrow you answered my question on the scripting side and OlafDoschke the link you provided me did answer my question on the space allocation and how it can be managed.

From that point I think that I can get thing going without having issues.

Thank you all for your answers, I really appreciate

 

[Andrzejek]

I see it way too often: data base allows me to create up to 255 columns so I do it. In Access, you can create field names with reserved words, spaces, special characters and people do it. Later on they discover whole bunch of problems because of that. Just because something is allowed, does not mean you should do it.

>I have a database that include a table that already have 94 columns and my client want to add an other 25 varchar columns on the same tables.

I would say: this is not a normalized way to keep the data (I may be wrong)

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[SgtJarrow]

I always forgot to stress those points Andrzejek. Thanks for reminding everyone that its not always best to do something just because you can.

Robert "Wizard" Johnson III
U.S. Military Vets MC
CSM, CSPO, MCPD, CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
Senior Database Developer