Home Forums SQL Server 2012 SQL Server 2012 - T-SQL What's the best way to get a sample set of a big table without primary key? RE: What's the best way to get a sample set of a big table without primary key?

  • Most typically in SQL Server I see this technique used:

    SELECT TOP ...
    FROM dbo.table_name
    ORDER BY NEWID()

    I presume that gives a roughly random sample.  Of course it likely does require sorting the entire record set.

    If you have an existing index that contains a unique set of columns, you could select just those columns in the "ORDER BY" query, allowing an index scan and sort rather than a full table scan and sort, then join back to the main table after that to get other column(s) from the table.

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".