INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Calculate max lenth of a database row.

Calculate max lenth of a database row.

(OP)
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.

RE: Calculate max lenth of a database row.

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

CODE

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.

CODE

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

RE: Calculate max lenth of a database row.

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.

RE: Calculate max lenth of a database row.

Quote (EM1107)

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

RE: Calculate max lenth of a database row.

(OP)
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.


RE: Calculate max lenth of a database row.

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:

CODE

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

RE: Calculate max lenth of a database row.

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

CODE

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

RE: Calculate max lenth of a database row.

(OP)
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.

RE: Calculate max lenth of a database row.

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:

CODE

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/14/sql-server-un...

Bye, Olaf.

RE: Calculate max lenth of a database row.

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

RE: Calculate max lenth of a database row.

(OP)
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

RE: Calculate max lenth of a database row.

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.

RE: Calculate max lenth of a database row.

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close