Clustered Index Scan vs Index Seek

Question

Will including more columns in the clustered index for a table increase the chance of turning index scans into index seeks? I'm trying to increase responsiveness with operations involved with a heavily read table. It has 199 columns and currently 65k rows, is it even worth it?

Here's the query:

SELECT T1.SALESID,T1.SALESNAME,T1.RESERVATION,T1.CUSTACCOUNT,T1.INVOICEACCOUNT,T1.DELIVERYDATE,T1.URL,T1.PURCHORDERFORMNUM,T1.SALESGROUP,T1.FREIGHTSLIPTYPE,T1.DOCUMENTSTATUS,T1.INTERCOMPANYORIGINALSALESID,T1.CURRENCYCODE,T1.PAYMENT,T1.CASHDISC,T1.TAXGROUP,T1.LINEDISC,T1.CUSTGROUP,T1.DISCPERCENT,T1.INTERCOMPANYORIGINALCUSTACCOUNT,T1.PRICEGROUPID,T1.MULTILINEDISC,T1.ENDDISC,T1.CUSTOMERREF,T1.LISTCODE,T1.DLVTERM,T1.DLVMODE,T1.PURCHID,T1.SALESSTATUS,T1.MARKUPGROUP,T1.SALESTYPE,T1.SALESPOOLID,T1.POSTINGPROFILE,T1.TRANSACTIONCODE,T1.INTERCOMPANYAUTOCREATEORDERS,T1.INTERCOMPANYDIRECTDELIVERY,T1.INTERCOMPANYDIRECTDELIVERYORIG,T1.SETTLEVOUCHER,T1.INTERCOMPANYALLOWINDIRECTCREATION,T1.INTERCOMPANYALLOWINDIRECTCREATIONORIG,T1.DELIVERYNAME,T1.ONETIMECUSTOMER,T1.COVSTATUS,T1.COMMISSIONGROUP,T1.PAYMENTSCHED,T1.INTERCOMPANYORIGIN,T1.EMAIL,T1.FREIGHTZONE,T1.RETURNITEMNUM,T1.CASHDISCPERCENT,T1.CONTACTPERSONID,T1.DEADLINE,T1.PROJID,T1.INVENTLOCATIONID,T1.ADDRESSREFTABLEID,T1.VATNUM,T1.PORT,T1.INCLTAX,T1.NUMBERSEQUENCEGROUP,T1.FIXEDEXCHRATE,T1.LANGUAGEID,T1.AUTOSUMMARYMODULETYPE,T1.SALESORIGINID,T1.ESTIMATE,T1.TRANSPORT,T1.PAYMMODE,T1.PAYMSPEC,T1.FIXEDDUEDATE,T1.EXPORTREASON,T1.STATPROCID,T1.INTERCOMPANYCOMPANYID,T1.INTERCOMPANYPURCHID,T1.INTERCOMPANYORDER,T1.DLVREASON,T1.QUOTATIONID,T1.RECEIPTDATEREQUESTED,T1.RECEIPTDATECONFIRMED,T1.SHIPPINGDATEREQUESTED,T1.SHIPPINGDATECONFIRMED,T1.ITEMTAGGING,T1.CASETAGGING,T1.PALLETTAGGING,T1.ADDRESSREFRECID,T1.CUSTINVOICEID,T1.INVENTSITEID,T1.DEFAULTDIMENSION,T1.CREDITCARDCUSTREFID,T1.SHIPCARRIERACCOUNT,T1.SHIPCARRIERID,T1.SHIPCARRIERFUELSURCHARGE,T1.SHIPCARRIERBLINDSHIPMENT,T1.SHIPCARRIERDELIVERYCONTACT,T1.CREDITCARDAPPROVALAMOUNT,T1.CREDITCARDAUTHORIZATION,T1.RETURNDEADLINE,T1.RETURNREPLACEMENTID,T1.RETURNSTATUS,T1.RETURNREASONCODEID,T1.CREDITCARDAUTHORIZATIONERROR,T1.SHIPCARRIERACCOUNTCODE,T1.RETURNREPLACEMENTCREATED,T1.SHIPCARRIERDLVTYPE,T1.DELIVERYDATECONTROLTYPE,T1.SHIPCARRIEREXPEDITEDSHIPMENT,T1.SHIPCARRIERRESIDENTIAL,T1.MATCHINGAGREEMENT,T1.SYSTEMENTRYSOURCE,T1.SYSTEMENTRYCHANGEPOLICY,T1.MANUALENTRYCHANGEPOLICY,T1.DELIVERYPOSTALADDRESS,T1.SHIPCARRIERPOSTALADDRESS,T1.SHIPCARRIERNAME,T1.WORKERSALESTAKER,T1.SOURCEDOCUMENTHEADER,T1.BANKDOCUMENTTYPE,T1.SALESUNITID,T1.SMMSALESAMOUNTTOTAL,T1.SMMCAMPAIGNID,T1.CASHDISCBASEDATE,T1.CASHDISCBASEDAYS,T1.PDSBATCHATTRIBAUTORES,T1.PDSCUSTREBATEGROUPID,T1.PDSREBATEPROGRAMTMAGROUP,T1.WORKERSALESRESPONSIBLE,T1.AA_INITIALCONDITION,T1.AA_MATERIAL,T1.AA_PONUMBER,T1.AA_REFERENCENUMBER,T1.AA_FINALCONDITION,T1.AA_TEMPLATEID,T1.AA_PRIME,T1.AA_CONTAINER,T1.AA_ENTEREDDATE,T1.AA_ENTEREDDATETZID,T1.AA_EXPEDITE,T1.AA_PROMISEDDATE,T1.AA_PROMISEDDATETZID,T1.AA_GROSSWEIGHT,T1.AA_ALLOY,T1.AA_ITAR,T1.AA_EAR,T1.AA_GSI,T1.AA_SOLUTIONTREAT,T1.AA_BASICFORMID,T1.AA_PLANNEDBY,T1.AA_CLASSIFICATION,T1.INVENTPACKINGMATERIALCODE,T1.AA_CSI,T1.AA_QTY,T1.AA_BILLINGADDRESS,T1.AA_PROCESSMASTER,T1.AA_BILLINGNAME,T1.AA_PACKAGING,T1.AA_HIDECERTMEASURE,T1.AA_INVOICEAPPROVED,T1.AA_PROSHIPMEMOPRINTED,T1.AA_CERTIFIEDDATE,T1.TRI_OPSTATUS,T1.MODIFIEDDATETIME,T1.MODIFIEDBY,T1.CREATEDDATETIME,T1.CREATEDBY,T1.RECVERSION,T1.PARTITION,T1.RECID,T2.PERSON,T2.RECVERSION,T2.RECID,T3.LOCATION,T3.ADDRESS,T3.VALIDFROM,T3.VALIDFROMTZID,T3.VALIDTO,T3.VALIDTOTZID,T3.COUNTRYREGIONID,T3.RECVERSION,T3.RECID,T4.ADDRESS,T4.COUNTRYREGIONID,T4.LOCATION,T4.VALIDFROM,T4.VALIDFROMTZID,T4.RECVERSION,T4.RECID,T5.LOCATION,T5.ADDRESS,T5.VALIDFROM,T5.VALIDFROMTZID,T5.RECVERSION,T5.RECID,T6.NAME,T6.RECID,T6.RECVERSION,T6.INSTANCERELATIONTYPE,T6.NAMESEQUENCE,T6.RECVERSION,T6.RECID,T7.DESCRIPTION,T7.LOCATIONID,T7.RECVERSION,T7.RECID,T8.DESCRIPTION,T8.LOCATIONID,T8.RECVERSION,T8.RECID,T1.AA_COMMENTS FROM  SALESTABLE T1 LEFT OUTER JOIN HCMWORKER T2 ON ((T2.PARTITION=@P1) AND ((T1.WORKERSALESTAKER=T2.RECID) AND (T1.WORKERSALESTAKER=T2.RECID))) LEFT OUTER JOIN LOGISTICSPOSTALADDRESS T3 ON ((T3.PARTITION=@P2) AND ((T1.DELIVERYPOSTALADDRESS=T3.RECID) AND ((1=@P3 OR (T3.ISPRIVATE=@P4)) OR (T3.PRIVATEFORPARTY=@P5)))) LEFT OUTER JOIN LOGISTICSPOSTALADDRESS T4 ON ((T4.PARTITION=@P6) AND ((T1.SHIPCARRIERPOSTALADDRESS=T4.RECID) AND ((1=@P7 OR (T4.ISPRIVATE=@P8)) OR (T4.PRIVATEFORPARTY=@P9)))) LEFT OUTER JOIN LOGISTICSPOSTALADDRESS T5 ON ((T5.PARTITION=@P10) AND ((T1.AA_BILLINGADDRESS=T5.RECID) AND ((1=@P11 OR (T5.ISPRIVATE=@P12)) OR (T5.PRIVATEFORPARTY=@P13)))) LEFT OUTER JOIN DIRPARTYTABLE T6 ON ((((T6.PARTITION=@P14) AND (T6.INSTANCERELATIONTYPE IN (@P15,@P16,@P17,@P18,@P19,@P20,@P21) )) AND ((T2.PERSON=T6.RECID) AND (T2.PERSON=T6.RECID))) AND (T6.INSTANCERELATIONTYPE IN (2975) )) LEFT OUTER JOIN LOGISTICSLOCATION T7 ON ((T7.PARTITION=@P22) AND (T3.LOCATION=T7.RECID)) LEFT OUTER JOIN LOGISTICSLOCATION T8 ON ((T8.PARTITION=@P23) AND (T5.LOCATION=T8.RECID)) WHERE (((T1.PARTITION=@P24) AND (T1.DATAAREAID=@P25)) AND (( NOT ((T1.RETURNSTATUS=@P26)) AND  NOT ((T1.RETURNSTATUS=@P27))) AND (((((((((((((((((((((((((T1.SALESNAME=@P28) AND (T1.SALESID=@P29)) AND (T2.PERSON IS NULL)) AND (T3.LOCATION=@P30)) AND (T3.VALIDFROM=@P31)) AND (T4.LOCATION IS NULL)) AND (T5.LOCATION=@P32)) AND (T5.VALIDFROM=@P33)) AND (T6.NAME IS NULL)) AND (T6.NAMESEQUENCE IS NULL)) AND (T7.LOCATIONID=@P34)) AND (T8.LOCATIONID>=@P35)) OR (((((((((((T1.SALESNAME=@P36) AND (T1.SALESID=@P37)) AND (T2.PERSON IS NULL)) AND (T3.LOCATION=@P38)) AND (T3.VALIDFROM=@P39)) AND (T4.LOCATION IS NULL)) AND (T5.LOCATION=@P40)) AND (T5.VALIDFROM=@P41)) AND (T6.NAME IS NULL)) AND (T6.NAMESEQUENCE IS NULL)) AND (T7.LOCATIONID>@P42))) OR ((((((((((T1.SALESNAME=@P43) AND (T1.SALESID=@P44)) AND (T2.PERSON IS NULL)) AND (T3.LOCATION=@P45)) AND (T3.VALIDFROM=@P46)) AND (T4.LOCATION IS NULL)) AND (T5.LOCATION=@P47)) AND (T5.VALIDFROM=@P48)) AND (T6.NAME IS NULL)) AND  NOT ((T6.NAMESEQUENCE IS NULL)))) OR ((((((((((T1.SALESNAME=@P49) AND (T1.SALESID=@P50)) AND (T2.PERSON IS NULL)) AND (T3.LOCATION=@P51)) AND (T3.VALIDFROM=@P52)) AND (T4.LOCATION IS NULL)) AND (T5.LOCATION=@P53)) AND (T5.VALIDFROM=@P54)) AND (T6.NAME IS NULL)) AND  NOT ((T6.RECID IS NULL)))) OR (((((((((T1.SALESNAME=@P55) AND (T1.SALESID=@P56)) AND (T2.PERSON IS NULL)) AND (T3.LOCATION=@P57)) AND (T3.VALIDFROM=@P58)) AND (T4.LOCATION IS NULL)) AND (T5.LOCATION=@P59)) AND (T5.VALIDFROM=@P60)) AND  NOT ((T6.NAME IS NULL)))) OR ((((((((T1.SALESNAME=@P61) AND (T1.SALESID=@P62)) AND (T2.PERSON IS NULL)) AND (T3.LOCATION=@P63)) AND (T3.VALIDFROM=@P64)) AND (T4.LOCATION IS NULL)) AND (T5.LOCATION=@P65)) AND (T5.VALIDFROM>@P66))) OR (((((((T1.SALESNAME=@P67) AND (T1.SALESID=@P68)) AND (T2.PERSON IS NULL)) AND (T3.LOCATION=@P69)) AND (T3.VALIDFROM=@P70)) AND (T4.LOCATION IS NULL)) AND (T5.LOCATION>@P71))) OR (((((((T1.SALESNAME=@P72) AND (T1.SALESID=@P73)) AND (T2.PERSON IS NULL)) AND (T3.LOCATION=@P74)) AND (T3.VALIDFROM=@P75)) AND (T4.LOCATION IS NULL)) AND  NOT ((T4.VALIDFROM IS NULL)))) OR ((((((T1.SALESNAME=@P76) AND (T1.SALESID=@P77)) AND (T2.PERSON IS NULL)) AND (T3.LOCATION=@P78)) AND (T3.VALIDFROM=@P79)) AND  NOT ((T4.LOCATION IS NULL)))) OR (((((T1.SALESNAME=@P80) AND (T1.SALESID=@P81)) AND (T2.PERSON IS NULL)) AND (T3.LOCATION=@P82)) AND (T3.VALIDFROM>@P83))) OR ((((T1.SALESNAME=@P84) AND (T1.SALESID=@P85)) AND (T2.PERSON IS NULL)) AND (T3.LOCATION>@P86))) OR (((T1.SALESNAME=@P87) AND (T1.SALESID=@P88)) AND  NOT ((T2.PERSON IS NULL)))) OR ((T1.SALESNAME=@P89) AND (T1.SALESID>@P90))) OR (T1.SALESNAME>@P91)))) ORDER BY T1.SALESNAME,T1.SALESID,T2.PERSON,T3.LOCATION,T3.VALIDFROM,T4.LOCATION,T4.VALIDFROM,T5.LOCATION,T5.VALIDFROM,T6.NAME,T6.RECID,T6.NAMESEQUENCE,T7.LOCATIONID,T8.LOCATIONID OPTION(FAST 2)

Answer 1

Will adding more columns to the clustered index key, aka Primary Key, possibly turn Index Scans into Index Seeks? Yes, but query tuning via Primary Key can create other complications. It may be less painful to just add a Non Clustered Index on the columns that you want "seeked".

Can you provide the problem query?

Answer 2

It is not possible to pick the columns in a Clustered Index. It always includes all columns.

In case you meant key columns: There is no "chance" here. It depends on the query whether an index is a good fit or not. Without schema and query nothing can be said. Refer to pretty much any indexing tutorial to answer this yourself.