Mark Mintoff My superpower is common sense


Simple Way To Check For Rows

I would like to share a very simple technique for checking for rows in your table. Before developing this, I used to make the mistake of doing a SELECT COUNT(1) script.

On tables with a large number of rows (millions), this proved to be inefficient (even with indexing). So instead, I developed this efficient way to check for rows, which makes a difference:

It's very simple. All it does is select the top one row from your table, based on your condition in a subquery (X). Then select count on that subquery and convert the result to a bit. The result will always be 1 or 0, which translates to True and False respectively!

VN:F [1.9.22_1171]
Rating: 0.0/5 (0 votes cast)
Comments (2) Trackbacks (0)
  1. Is this faster than using EXISTS?

    • That depends! If you are doing the following:

      IF EXISTS(Select * From [TABLE]) WHERE PRIMARY_KEY = 123)

      Then it might be faster.
      If however, your selection will return a large result set (ie: not selecting by a unique primary key)

      IF EXISTS(Select * From [TABLE]) WHERE DateField BETWEEN ‘date1′ AND ‘date2′)

      Then I believe the technique in my post will be faster yes.

Leave a comment

No trackbacks yet.