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