SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Covering Index Question


Covering Index Question

Author
Message
curious_sqldba
curious_sqldba
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2894 Visits: 3636
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?
durai nagarajan
durai nagarajan
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1603 Visits: 2775
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
ChrisM@Work
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16016 Visits: 19524
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
Exploring Recursive CTEs by Example Dwain Camps
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)

Group: General Forum Members
Points: 84677 Visits: 41069
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search