Covering Index Question

Question

Post reply

Covering Index Question

curious_sqldba

SSC-Dedicated

Points: 36494
More actions
July 25, 2013 at 3:40 pm

#296857

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?

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

durai nagarajan SSCoach Points: 15778 More actions · Answer 1

patientnumber looks unique keep this a primary key and create non clustered index as per requirement.

covering index column will work faster than columns not in any columns.

Regards
Durai Nagarajan

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

Columns in the INCLUDE list are not part of the key. Crudely speaking and with the small amount of detail provided, your suggestion of an index with patientnumber as key sounds more likely to be useful for a query which filters rows based on this column, than the "covering index" described (why do you call it a covering index since it doesn't support the WHERE clause of your query?)

If you can post up the Actual execution plan for your query, folks will be able to provide you with a more specific answer.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Jeff Moden SSC Guru Points: 1004434 More actions · Answer 3

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)