curious_sqldba (7/25/2013)
If i have a query which is doing a search on column patientnumber, and my covering index has patientname in key columns and patientnumber,address and SSN in include columns. Would this index be as good as having a index with patientnumber in key column?
If you have SSN in plain text, especially with all that other sensitive information, your company may be breaking several regulations. At the very least, it could be a serious violation of 2 PCI regulations and you're probably subject to PCI regulations because you allow patients and other customers to pay with credit cards.
Please see items #3 and #10 in the following for the regulation areas that most offenders end up breaking. No slight meant to your personal integrity but a programmer or even a DBA shouldn't have general access to such information. The easiest way to comply is to encrypt the SSN with a decent one way hashing algorithm that has lots of "salt" added.
https://en.wikipedia.org/wiki/Payment_Card_Industry_Data_Security_Standard
--Jeff Moden
Change is inevitable... Change for the better is not.