>>>>>In a table having about 26 million records and counting, it contains only a few fields however, what would be the impact of using Char(100) field VS Text or NVarChar field, whatever the field type which would be used? The reason I ask is that the first 26 million records won't have a value in this new field. It is just a caption to enter some text about upcoming images. The table takes 30 GB presently totalling the data + index + reserved. Whichever field type I choose, would there be a space advantage of one over the other or this doesn't matter in SQL Server?
>>>>
>>>>with 26 Miliion record with null value the better solution is
>>>>VARCHAR(100) with Sparse attribute
>>>
>>>Right except you will not be able to index that column.
>>
>>I do not own this bad information
>>
>>

>>USE [tempdb]
>>GO
>>
>>CREATE TABLE [dbo].[Table_1](
>>	[id] [int] IDENTITY(1,1) NOT NULL,
>>	[fsparse] [varchar](100) SPARSE  NULL,
>> CONSTRAINT [PK_Table_1] PRIMARY KEY CLUSTERED 
>>(
>>	[id] ASC
>>)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
>>) ON [PRIMARY]
>>
>>GO
>>
>>CREATE NONCLUSTERED INDEX [IX_Table_1] ON [dbo].[Table_1]
>>(
>>	[fsparse] ASC
>>)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
>>GO
>>

>
>Thanks. I remember there was some index related limitation for sparse columns, but I don't recall exactly.

However, the documentation of SQL is inaccurate.

With VARCHAR(NN) or VARCHAR (MAX)
Sparse is useless, the data space occupied by the NULL is the same!

Sparse CHAR () is = VARCHAR().

Then: normal VARCHAR() is the better solution with a very nullable column.