0

I have 2 large tables( millions of rows) having similar columns ( one staging , one master) querying on all most all columns .

all columns are VARCHAR(200)

MERGE
INTO    [dbo].[TABLE1] [t1]
USING   [dbo].[TABLE2] [t2]
ON      [t1].[COL1] = [t2].[COL1]
        AND [t1].[COL2] = [t2].[COL2]
        AND [t1].[COL3] = [t2].[COL3]
        AND [t1].[COL4] = [t2].[COL4]

What is the correct index strategy. Covering index wont work , as they are mostly used in predicates , rather than in Select . Clustered index can be identity , which is not used at all. Then Nonclustered index having 4 columns will be too large .. 800 bytes.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
OglaString
  • 1
  • 7
  • 3
    I might suggest that you make a computed column that is a hash of the column values and then use this for an index. – Gordon Linoff Oct 28 '15 at 21:36
  • 1
    Most frequently the indexes will be used for inserting and updating . Using Computing index on some hashes, will complicate – OglaString Oct 28 '15 at 21:42
  • 2
    . . You have specified that you cannot create an index on all the columns needed by the comparison. Hence, an index on a hashed value would be better than no index at all. – Gordon Linoff Oct 29 '15 at 01:39

0 Answers0