Indexes with low cardinality columns decreases the performance ?

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.

Indexes with low cardinality may be ignored by the optimizer as they offer no assistance in generating a query plan for efficient execution of your query.

Where they can affect performance, is that every update/insert that affects the data in that index is extra overhead in the operation, for very little gain further down the line.

There is also the additional storage space needed.

All, or none, of these may be a concern in your environment - you need to test the different scenarios and determine your own conclusions.

Another thought to keep in mind is whether you are actually using the column as a predicate in your WHERE clause. No use in maintaining it unless it is a needed element.