We help IT Professionals succeed at work.

Row size based on datatypes and NULL fields

569 Views
Last Modified: 2008-09-08
Hi

We have a system that sends out emails for our newsletters, the system is recording the emails that were sent into a SQL table.

This has resulted in a VERY large database being created, our database is currently sitting at 75gb, when it really should be between 2 and 5 gb's in size.

We have determined that the table that is using up this space, is the table that records what emails were sent. We are now looking to cut down on the data being stored in this table, though we need some help to determine the best way to reduce its size.

We are using nvarchar(MAX), (nullable) to store some of the information.
Question 1: If this field is set to null, how much disk space does it take up?

We are using varbinary(MAX), (nullable) to store other byte data,
Question 2: if this field is set to null, how much disk space does it take up?

Question 3: If we set the nvarchar to 500 in size, how much space does this take up if we add 100 characters to the field, and how much will it take up on the drive if it is set to null ?

Question 4:How much space does a uniqueidentifier take up on the hard drive when used, and when its null?

Question 5:
Below you will see our table structure, I need to know how we can change its structure so that the row size is less than 8kb's (i have marked the rows as REQ, where we absolutly need the rows, other rows which are not marked can be set to null, or removed from the table.)

Please advise on datatypes that should be used AND the size of the types so that a row in this table would be less than 8kb's:

[COLUMN NAME]            [DATATYPE]
ID                  uniqueidentifier (REQ)
EmailCampaignID            uniqueidentifier (REQ)
EmailSubscriberID            uniqueidentifier (REQ)
OutboundQStatusID            int (REQ)
obtainDetailsFromCampaign      bit
FromName                  nvarchar(500)
FromEmail                  nvarchar(500)
ReplyEmail                  nvarchar(500)
Subject                  nvarchar(500)
ToEmail                  nvarchar(500) (REQ)
ToName                  nvarchar(500) (REQ)
HTMLBody                  varbinary(MAX)
TXTBody                  varbinary(MAX)
RCF822                  varbinary(MAX)
PriorityLevel                  int
SMTPSendingLog            nvarchar(MAX)
SMTPReference1            nvarchar(MAX)
SMTPReference2            nvarchar(MAX)
SMTPReference3            nvarchar(MAX)
SentDT                  datetime (REQ)
FailCount                  int (REQ)
dt                  datetime (REQ)
msrepl_tran_version            uniqueidentifier
Comment
Watch Question

Commented:
>>nvarchar(max) Question 1: If this field is set to null, how much disk space does it take up?
As much as it needs, up to 2GB. The storage size is two times the number of characters entered + 2 bytes. For example, if you have entered 1000 characters, that is 2002 bytes.

>>nvarbinary(max)
As much as it needs, up to 2GB. Same issue as nvarchar(max).

>> If we set the nvarchar to 500 in size, how much space does this take up if we add 100 characters to the field, and how much will it take up on the drive if it is set to null ?

202 bytes

>> How much space does a uniqueidentifier take up on the hard drive when used, and when its null?

The uniqueidentifier stores 16-byte binary values. If the column is NULL, then it should be 0.

You can check the actual size of any of these columns with

SELECT datalength(columnName)
FROM t1

There is also overhead for having a row.

To get a better estimate - use

GO
EXEC sp_spaceused N'tableName';
GO
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Commented:
Also there is overhead for each row.

FROM http://support.microsoft.com/kb/827968

When you estimate the size of a table or of a database, consider the following information:
"      Each row in the table has an overhead of 6 bytes.
"      Each column in the table has an overhead of 1 byte, plus 1 byte for every 256 bytes of row storage.
"      Fixed-length data types have an overhead of 1 byte for each column, and the overhead is rounded to the next higher byte.
"      Zero-length string columns occupy 1 byte in the row.

***
I realize that this is for SQL Server CE, but the same values apply to any SQL Server.

Scott PletcherSenior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
Do you *really* need unicode chars?  Just changing all the nvarchar cols to varchar will save you 1/2 the space they take up.

Also, normalize it a bit so that the email content is stored in a separate table, then assign an int value as a content code.  The way it is now, if 10 (or 100 or 1000) people are all sent the same email, the content is stored 10/100/1000 times, when it only needs to be stored once.
Scott PletcherSenior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
Thank you all for your feedback, you have all helped with questions 1 to 4, ptjcb thank you for the datalength note that has helped me see the actual usage on the drive.

I still need some help with question 5, how will i get this to be under 8kb, the reason i want it under 8kb is that i have read up on the net, that a row being less than 8kb's in size has a huge performance advantage, not to mention it will be the RIGHT size for my database file size.

Another thing im looking at on sql, i run the SP sp_spaceused , which gives me a reserved value, what is this reserved value? how can i reclaim this space, should i reclaim it? why is it reserved? If you could give me an explanation on these results returned it will help allot.

Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Commented:
ScottPletcher gave you two suggestions that should make your table size smaller.

I would check the actual length of every column and, as Scott points out, change the data type from nvarchar to varchar, and consider breaking the table into smaller tables.

It is difficult to restructure your table because I do not know your requirements, your table structure, your actual data, your documentation or your application. I don't know the difference between HTMLBody or TXTBody, for example.

Sp_spacedused - the reserved value is the total amount of space allocated to the objects in your database. You cannot reclaim that space, it should be the sum of the data, index_size, and unused.

http://msdn2.microsoft.com/en-us/library/ms188776.aspx

For example, I have a small test database
Reserved                             data        index    unallocated
1368 KB                                    632 KB      648 KB      88 KB

If you add the data, index, and unallocated you have the reserved size.








Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.