Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Covering Index Question Expand / Collapse
Author
Message
Posted Thursday, July 25, 2013 3:40 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, July 21, 2014 3:43 PM
Points: 1,274, Visits: 2,936

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?
Post #1477770
Posted Friday, July 26, 2013 8:48 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, May 30, 2014 8:15 AM
Points: 1,056, Visits: 2,687
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
Post #1478052
Posted Friday, July 26, 2013 8:57 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, July 24, 2014 9:56 AM
Points: 7,120, Visits: 13,491
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
Post #1478057
Posted Monday, August 12, 2013 9:53 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:39 PM
Points: 36,764, Visits: 31,220
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1483547
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse