SQL Server varchar best practices

I have a database table, sql server 2016, that is where we will save our dynamic content.

Fields are:
nvarchar(300)
nvarchar(1000)
nvarchar(max)

since they are varchar, does it really matter if they are empty or full? if empty they won't take up anymore space than if it weren't in the table at all will it?

Reason I ask is, the nvarcchar(max) won't be used as often as the others, and I thought about putting it in a separate table with a relation. But if having it in there empty doesn't add any extra space, I'd prefer doing that.

Thanks!
LVL 2
Starr DuskkASP.NET VB.NET DeveloperAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ste5anSenior DeveloperCommented:
We cannot give you a good answer here, cause the data types depend on the model. But as you're seem to create systematic NULL's, this indicates a model/normalization issues.

Even a NULL column requires space. Thus the hint about systematic NULL. A database should store data, not NULL. (I consider this to be 0NF).

Reason I ask is, the nvarcchar(max) won't be used as often as the others, and I thought about putting it in a separate table with a relation. But if having it in there empty doesn't add any extra space, I'd prefer doing that.
The data model and the normalization process defines this. Not the usage.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>since they are varchar, does it really matter if they are empty or full?
The significance of varchar as opposed to nvarchar is that nvarchar supports Unicode characters (aka a bigger alphabet) and takes 2 bytes of storage in memory.  varchar does not and only takes up 1 byte.

The significance of 'empty or full' is nothing in storage, as the 'varchar' part will take up (1 or 2 bytes per character + 1 bytes in memory.  The significance is that when queries execute it has to (haven't run a complete test on this so might not be 100% sure) reserve the full space per the column data type, which means queries will run slower if for example you have a nvarchar(100) where the values inside are really two-character state codes as opposed to if it were a char(2).  I think it affects page splits too.  You'll have to test to be sure.
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>Reason I ask is, the nvarcchar(max) won't be used as often as the others, and I thought about putting it in a separate table with a relation. But if having it in there empty doesn't add any extra space, I'd prefer doing that.
Hmm... wonder if this is a truly excellent idea as far as execution speed.  My company stores a lot of API call request/responses as xml, so some of the requests can be {expletives deleted} huge.  Again, it's worth a test to find out.
0
Starr DuskkASP.NET VB.NET DeveloperAuthor Commented:
I guess what I'm hearing here is, Yes, put the field in a separate table because if it's empty, it will still take up space. But if it doesn't exist in the other table, it will take up no space.

I would use a left outer join to see if there's data in it.

Does that sound best and what you're trying to tell me?
0
Dale FyeOwner, Developing Solutions LLCCommented:
I'm not a SQL Server expert, so don't take my reply as gospel, but I've been using this technique (splitting memo / nvarchar(Max) fields off into their own tables) for many years.  It always seemed to make more sense to explicitly do so.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.