TOP PERCENT

We have a multi-threaded process that grabs it’s fair share records and then processes them. It basically looks at the total number of records to process, and takes the top 10% of the records and marks them so that no other process will grab those records.

If you haven’t done a lot with TOP, then you might not know that you can actually request the TOP x% of records using nothing but T-SQL. It turns out my developers didn’t know that. So they were counting the records, then doing a client side computation, then requesting that number of records.

So I spent a few minutes sharing the PERCENT option! Now I’m going to share it with you.

First we’re going to set up a test table.

CREATE TABLE test (
	  ID INT IDENTITY(1,1)
	, CharacterData CHAR(1) )

Next we’re going to fill that table with 100 rows. I don’t really care about what data is in there, so I just put in a character.

DECLARE @counter INT
SET @counter = 1

WHILE (@counter < 100)
BEGIN
	INSERT INTO test
	SELECT CHAR(@counter)

	SET @counter = @counter + 1
END

Now that we have filled in 100 records, let’s look at what we have.

SELECT * FROM test

Say we want the top 10 percent, it would be easy to compute how many records we want now. But if this table grows, we’d have to compute the number each time. With TOP PERCENT, it can change based on the number of records in your table.

SELECT TOP 10 PERCENT
	*
FROM test

By adding PERCENT, you’ll get a variable number of records.

Just a simple little tweak that can save you from having to compute counts on the client side!  If you have any questions, please let me know!

By Shannon Lowder

Shannon Lowder is the Database Engineer you've been looking for! Look no further for expertise in: Business Analysis to gather the business requirements for the database; Database Architecting to design the logical design of the database; Database Development to actually build the objects needed by the business logic; finally, Database Administration to keep the database running in top form, and making sure there is a disaster recovery plan.

1 comment

Leave a comment

Your email address will not be published. Required fields are marked *