question

Well it will depend upon the nature of the queries. But generally, it is better to have low cardinality columns as the last columns of the index and the highest cardinality column at the start of the index. This way the selectivity of the row becomes easier and hence better performance can be gained. So, I would say it is not the matter of performance decrement but it is more the case of there could be room for more performance gain.

Moreover, filtered indexes could be another suitable option depending upon your environment.

Here are two examples where the Low cardinality column could be bad as well as beneficial. You have to run the data population scripts in one batch and the examples in separate batches. You would be able to see the difference of cost in execution plans as well as through STATISTICS.

/*============== DATA POPULATION PORTION ======================================================*/

CREATE TABLE #temp
(
EmployeeId INT IDENTITY PRIMARY KEY,
EmployeeName VARCHAR(20),
EmployeeDepartment INT,
IsDeleted BIT
)

SET NOCOUNT ON;

INSERT [#temp]
        (
         [EmployeeName]
        ,[EmployeeDepartment]
        ,[IsDeleted]
        )
SELECT 'XYZ'+ CAST(ROWNUM AS VARCHAR(20)), [ROWNUM]/50 + 1, CASE WHEN [ROWNUM] % 100 = 0 THEN 1 ELSE 0 END
FROM
(
SELECT TOP (100000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) ROWNUM
FROM sys.all_columns AS AC CROSS JOIN sys.all_objects AS AO
) A

CREATE INDEX IX_BIT ON [#temp]([IsDeleted])
CREATE INDEX IX_Name ON [#temp]([EmployeeName])
CREATE INDEX IX_Name_Covering ON [#temp]([EmployeeName],[IsDeleted])

GO

/*============ FIRST SCENARIO WHEN THE INDEX ON LOW CARDINALITY COLUMN COULD NOT BE THE RIGHT CHOICE ===================*/
/*====== THIS IS JUST TO LET YOU KNOW THAT THERE MAY BE A POSSIBLITY THAT OPTIMIZER MAY HIT THIS INDEX ========================*/

SELECT * FROM [#temp] AS T WITH (INDEX (1))
WHERE [IsDeleted] = 0

SELECT * FROM [#temp] AS T WITH (INDEX(IX_BIT))
WHERE [IsDeleted] = 0


GO



/*===== SECOND SCENARIO WHEN ADDING THE LOW CARDINALITY COLUMN COULD BE BENEFICIAL TO MAKE A COVERING INDEX ========*/
SELECT [EmployeeId]
,       [EmployeeName]
,       [IsDeleted]
FROM [#temp] AS T WITH (INDEX(IX_Name))
WHERE [IsDeleted] = 0 
AND [EmployeeName] = 'XYZ1022'

SELECT [EmployeeId]
,       [EmployeeName]
,       [IsDeleted]
FROM [#temp] AS T WITH (INDEX(IX_Name_Covering))
WHERE [IsDeleted] = 0 
AND [EmployeeName] = 'XYZ1022'


--DROP TABLE [#temp] --CLEANUP

But as I said earlier, everything would depend upon your environment and you have to test whether it suits you or not.