>>>Use first and last variations as they seem to be the best.
>>
>>The last Variation didn't use the index and when I dropped the primary key just to see what would happen to the other select statements that where using it. The execution plan went from index scan to table scan. So to me this seems indexes are only good if the fields you are pulling out are in the list of the fields you want to filter down on.
>
>Can you please post a table script along with some data? I'm not sure what you're saying is correct.

CREATE TABLE [dbo].[sydays](
	[company] [char](10) NULL DEFAULT (''),
	[fiscal] [char](4) NULL DEFAULT (''),
	[holiday] [datetime] NULL DEFAULT (''),
	[descrip] [char](35) NULL DEFAULT (''),
	[hstatus] [char](1) NULL DEFAULT (''),
	[adduser] [char](16) NULL DEFAULT (''),
	[adddate] [datetime] NULL DEFAULT (''),
	[addtime] [char](8) NULL DEFAULT (''),
	[id_col] [int] IDENTITY(1,1) NOT NULL
 CONSTRAINT [PK_SYDAYS] PRIMARY KEY CLUSTERED 
(
	[id_col] 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 [COMPID1] ON [dbo].[sydays] 
(
	[company] ASC,
	[fiscal] ASC,
	[holiday] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

You don't even need to add any data. When I select to show Actual Execution Plan it shows what I had above.