0

I'm normally using: 

UPDATE table1 SET field1='test' WHERE ID=10

But will it be more efficiently to use the following statement: 

UPDATE TOP (1) table1 SET field1='test' WHERE ID=10

if I have a lots of records?

The ID Column is a primary key and autoincremented too.

Radu Gheorghiu
  • 20,049
  • 16
  • 72
  • 107
Michael Eriksen
  • 167
  • 1
  • 3
  • 13
  • Using `TOP` in `UPDATE` is a deprecated feature. Don't worry about performance (there will be none in any case if `ID` is indexed, which it is in your case), just don't use it. – Jeroen Mostert Dec 02 '19 at 13:24
  • 1
    If the column is a primary key, SQL Server 'knows' there's only zero or one matching key and will not cause a table scan. You should be able to see this in the query plan. – Caramiriel Dec 02 '19 at 13:24

1 Answers1

1

If the ID column is a Primary Key, then there will be at most a single record affected by your UPDATE query.

If your Primary Key is by default a Clustered Index, then the performance should be similar in both cases.

Even if when creating your PK, you specify it as non-clustered, then you still get a performance boost when searching / selecting / identifying / filtering records (because you're using WHERE). This might not be as fast as the clustered index PK, but the performance difference should be negligible.

When creating a PK, you're forced to pick one of the two indexing types for your key, as mentioned and explained here in more detail.

Hence, both versions of the UPDATE query should have similar performance (possibly small differences when running on different occasions due to other ancillary operations).

In conclusion:

If you have a Primary Key on your ID column, and you're using it in the FILTERING part of the query (WHERE), then you should be fine when you're querying thousands, millions and possibly even up to billions of records.

Disclaimer:

The performance / speed of the UPDATE query also depends on what other indexes need to be updated, due to the changing values (indexes which contain the field1 as their key), triggers on your table, cascading rules for foreign keys etc.

Radu Gheorghiu
  • 20,049
  • 16
  • 72
  • 107