Link to home
Create AccountLog in
Avatar of Starr Duskk
Starr DuskkFlag for United States of America

asked on

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!
ASKER CERTIFIED SOLUTION
Avatar of ste5an
ste5an
Flag of Germany image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
>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.
Avatar of Starr Duskk

ASKER

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?
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.