SQL Server 2014
Ramifications of using fields in the same table, with many being null, versus putting them in a relational table and only storing when there is data.
And what are the ramifications between using nvarchar(300) versus nvarchar(4000) as far as space differences?
I have a table to store optional data text fields. The length of most of these fields will not exceed 300. However, I have a description field that can be up to 4000 characters. I could create a separate table to store that field as an nvarchar(4000), but if I just create the ONE table and store them all as nvarchar(4000) are there any performance or size issues if I also use this field length for my 300 max strings?
I did have ONE table with these fields:
[Label] [nvarchar](300) NULL,
[Tooltip] [nvarchar](300) NULL,
[ValidationMessage] [nvarchar](300) NULL,
[Description] [nvarchar](1000) NULL,
But since from another post, I was told it's not good to store null columns, and many of these columns might be null, I'm going to put them in a separate table and use a code to indicate which one of the fields it will represent:
[Data] [nvarchar](300) NULL,
[FieldTypeCode] [int] NULL,
My query will ultimately look like this to pull them all together into one row as though they're in the same table.
label.Data as label,
tooltip.Data as tooltip,
validationMessage.Data as validationMessage,
Localization.* from Localization
left outer join LocalizationData as label on localization.localizationid = label.localizationId and label.localizationfieldtypecode = 1
left outer join LocalizationData as tooltip on localization.localizationid = tooltip.localizationId and tooltip.localizationfieldtypecode = 2
left outer join LocalizationData as validationMessage on localization.localizationid = validationMessage.localizationId and validationMessage.localizationfieldtypecode = 3
This isn't overkill is it? For instance, if the validation message doesn't exist, it will be null. So with the second table, it will never get stored as null and will never take up that space. With the first table design, many of the rows would be null. Possibly same with tooltip, and definitely the same for descriptions.