Row size based on datatypes and NULL fields
Posted on 2007-10-11
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?
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)
ToEmail nvarchar(500) (REQ)
ToName nvarchar(500) (REQ)
SentDT datetime (REQ)
FailCount int (REQ)
dt datetime (REQ)