SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE dbo.MyTable
(
  Col_A char(1) NOT NULL,
  Col_B int         NULL
);
INSERT INTO
  dbo.MyTable (Col_A, Col_B)
VALUES
  ('A', 1),
  ('A', 1),
  ('A', 2),
  ('A', 2),
  ('A', 2),
  ('A', 3),
  ('b', 4),
  ('b', 4),
  ('b', 5),
  ('C', 6),
  ('C', 6),
  ('C', NULL)
;
12 rows affected

-- initial version
SELECT
  Col_A,
  Col_B,
  DistinctCount = DENSE_RANK() OVER (PARTITION BY Col_A ORDER BY Col_B ASC )
                + DENSE_RANK() OVER (PARTITION BY Col_A ORDER BY Col_B DESC)
                - 1
                - CASE COUNT(Col_B) OVER (PARTITION BY Col_A)
                  WHEN COUNT(  *  ) OVER (PARTITION BY Col_A)
                  THEN 0
                  ELSE 1
                  END
FROM
  dbo.MyTable
;


Col_A
Col_B
DistinctCount


A
3
3

A
2
3

A
2
3

A
2
3

A
1
3

A
1
3

b
5
2

b
4
2

b
4
2

C
6
1

C
6
1

C
null
1


Warning: Null value is eliminated by an aggregate or other SET operation.


-- "enhanced" version
SELECT
  Col_A,
  Col_B,
  DistinctCount = DENSE_RANK() OVER (PARTITION BY Col_A ORDER BY Col_B ASC )
                + DENSE_RANK() OVER (PARTITION BY Col_A ORDER BY Col_B DESC)
                - CASE COUNT(Col_B) OVER (PARTITION BY Col_A)
                  WHEN COUNT(  *  ) OVER (PARTITION BY Col_A)
                  THEN 1
                  ELSE 2
                  END
FROM
  dbo.MyTable
;


Col_A
Col_B
DistinctCount


A
3
3

A
2
3

A
2
3

A
2
3

A
1
3

A
1
3

b
5
2

b
4
2

b
4
2

C
6
1

C
6
1

C
null
1


Warning: Null value is eliminated by an aggregate or other SET operation.


-- Erik Darling's solution, for verification
SELECT      *
FROM        dbo.MyTable AS mt
CROSS APPLY (   SELECT COUNT(DISTINCT mt2.Col_B) AS dc
                FROM   dbo.MyTable AS mt2
                WHERE  mt2.Col_A = mt.Col_A
                -- GROUP BY mt2.Col_A 
            ) AS ca;


Col_A
Col_B
dc


A
1
3

A
1
3

A
2
3

A
2
3

A
2
3

A
3
3

b
4
2

b
4
2

b
5
2

C
6
1

C
6
1

C
null
1


Warning: Null value is eliminated by an aggregate or other SET operation.