I have a table in SQL Server with around 400,000 rows. Unfortunately, this table has many duplicate rows, as there isn't a primary key. I'm using SELECT DISTINCT to get all unique rows, but as there's so many of them, I keep getting timeout errors. What's the best way to approach this problem?

  • 1
    belongs on stackoverflow?
    – gbn
    Apr 4, 2010 at 7:44
  • You wont get a proper answer unless you post your table definition (script it out in SSMS with right-click script table as... CREATE), any indexes defined against the table and your query. Best guess if that you should create a clustered index on the column your distinct is on. Apr 6, 2010 at 2:59
  • @Nick I'll do that, but, fyi, my distinct is on all the columns, not just one. Apr 6, 2010 at 4:50

2 Answers 2


Good question. Improove timeout - raise the value. Or throw in more power. Technically 400.000 rows is not a lot, so it would go very fast for a decent server. Otherwise - if you have IO problems because your "server" has only one (pair of) hard discs.... then your IO Will kill you.

Basically, DISTINCT triggers a temporary result creation in the tempdb - so it is tempdb heavy.

But at the end, I would increase timeout, especially if this is a one time operation only.

Btw., to put that into perspective - where is the HUGH table you talk about? 400.000 is trivial in size.

  • How do I raise the timeout value? Thanks for your answer! About the 400,000 = huge thing, it feels huge for me, but it's subjective. Apr 4, 2010 at 8:27
  • Actually it is not subjective - pretty much standard anything below one million is trivial (not even small). 1.000.000 records where small 10 yars ago (Foxpro) ;) TImeout - check command, connection object. CHeck connection string parameters.
    – TomTom
    Apr 4, 2010 at 9:15
  • For the timeout thing, I'm running the query directly from SQL Server Management Server. Is there a way to set it there? Apr 4, 2010 at 19:21

The best solution for speeding this up is to add an index on the field that you need to select against. Using a select statement where the database engine has to filter results without an index essentially requires the server to walk through every row in the table.

  • Will not help. A distinct NOT On only the key will trigger a table scan anyway, index or not.
    – TomTom
    Apr 4, 2010 at 5:28
  • True, I should clarify that to be effective, the index would have to be on all fields which are the target of the DISTINCT. Re-reading the question though I'm curious what the structure of this table looks like and the exact query that's being run against it. The timeout could be a symptom of a bigger issue with the overall design of the database.
    – Ron
    Apr 4, 2010 at 5:43
  • 1
    Most likely a bigger issue with the hardware. Unless the table is unusualle wide - 400.000 rows are comically low a size. That is unless this is a one disc system that overloads the IO. I have distincted 130 million record tables without problems in a minute or so - 400.000 is nothing for even a small but properly set up server.
    – TomTom
    Apr 4, 2010 at 6:16
  • 1
    DISTINCT is a form of aggregate... an index will help if it's not SELECT * and you can limit it to keys. Otherwise an index won't help if it has to over all columns = table scan anyway
    – gbn
    Apr 4, 2010 at 7:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.