If finding rows by birth month without regard to year or day is something important, it's probably worthwhile to materialize the birth month in the table using a persisted computed column. Like this....
CREATE TABLE dbo.birthdays
(
cust_id INTEGER NOT NULL PRIMARY KEY
,cust_fname NVARCHAR(50) NOT NULL
,cust_lname NVARCHAR(50) NOT NULL
,cust_dob DATETIME NOT NULL
,cust_dobmo AS DATEPART(mm,cust_dob) PERSISTED --<----<<< Added this column
)
;
Then the code become trivial for such things. For example...
DECLARE @pSomeDate DATETIME = GETDATE(); --Could be a parameter for a Stored Procedure or iTVF
SELECT *
FROM dbo.birthdays
WHERE cust_dobmo = DATEPART(mm,@pSomeDate)
;
I don't think adding a persistent column makes much sense in this case.
There are only 12 month, so cardinality will be always low.
Which means - scanning a cluisterd index will be usually a preferred choice for optimizer.
Clustering by [cust_dobmo] does not look like a good idea, so even if the column would be indexed the corresponding bookmark lookup would be more expensive than a simple table scan (remember - we return about 1/12 of the whole table anyway).
So, simple
SELECT *
FROM dbo.birthdays
WHERE DATEPART(mm,cust_dob) = DATEPART(mm,@pSomeDate)
would do, and the performance would be noth worse than of the persistent column.
Just to be clear, the is no place in my post that I suggested clustering on cust_dobmo. As you say, the cardinality is way too low for such a thing. A PERSISTED computed column in no way requires than any index be imparted to it never mind a CI. If you do add an NCI to the column and the table does have a CI on it then, of course, the key(s) to the CI would automatically be added to the NCI key column(s) and to the leaf level of the index.