CODEWITHSUNDEEP
sqlsql-server
Dhanesh
Dhanesh

Reputation: 33

Any Difference between Regarding Performance predicate Column is Not a "Primary Key Column"

select  * from empmas where ECode='IC114' --Query:1

select  top 1 * from empmas where ECode='IC114' --Query:2**

Is there any performance related difference , Ecode Predicate column is not a primary key Column

Upvotes: 0

Views: 62

Answers (2)

Thom A
Thom A

Reputation: 96003

The "problem" here isn't a question of performance, but of consistency, which in my view is in some way far more important.

The first query will return all the rows that have a value of 'IC114' for the column ECode. Considering you state that it is not the Primary key, and make no mention of a unique constraint or index, I would suggest it can be safe to assume that multiple rows with the same value for ECode can be in the table. Thus the first query can return 0+ rows (zero as there may be no rows with value), with no upper limit.

The second query implements TOP, however, does not have an ORDER BY. This means that any arbitrary row can be returned from the data engine. What row this is could, technically, be any row in your table that has the value 'IC114' for ECode, and most importantly that row can change every time the query is run. For small tables, it can often be expected that it would be the first row that fulfils the required when the table is scanned in it's Clustered Index's order, but that is still not guaranteed. When you start introducing JOINs, heaped tables, windowed functions, parallelism, shared index scans, etc, etc then (honestly) it's anyone's guess what row you might get. It is not predictable, or reliable at that stage.

Due to the TOP 1, the second query might be faster; likely SQL Server will stop processing once it has found the first arbitrary row. As, however, that could be any row, the results are not reliable, as each run could return different values for every other column.

If you are running the query and expect consistent results, you would need to an ORDER BY, perhaps on the Primary Key. This may well mean that the query is still faster than the original, as for the former the entire table would need to be scanned, or the seek completed, where as for the TOP 1 with an ORDER BY the data engine might be able to scan along an index and stop "early".

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271003

If ecode has no index on it, then the two are different. Both result in table scans but the top (1) should short-circuit one of those scans, stopping at the first record that matches.

If ecode has an index -- or is declared unique -- there is probably no discernible difference. If there are duplicates, then the top (1) should stop at the first match. In that case, though, the queries would be different and you should choose the version you want.

If this code is in an exists or not exists, there is no difference, because those operators stop at the first matching row.

This would be true as well if ecode were a primary key.

Primary keys are implemented (mostly) by using unique indexes. The where clause will use the index to fetch the result. Explicitly limiting the results to a single row is not needed because the index has only one row for the matching value.

Upvotes: 1

Related Questions