I can give you a real world example that has been simplified that you can look at.
How to Design, Build and Test a Dynamic Search Stored Procedure[/url]
There are 2 tables: Customers and Shipments. CustID on the Shipments record is a FK into the Customers table, however it supports a NULL value. This is because, at its initial stage of entry, the Shipments record has not yet been assigned to a specific customer ID. Later, when it is assigned a CustID, that must be for a customer that is on file.
Good or bad practice, it is required in some cases.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St