The more efficient of the two queries?

Question

I have a table with columns user_id, email, default. By default, "Y" or "N" is stored if the email is the default email. Each user can have only one default message.

When a user performs an update or inserts into a table, in my SP I check to see if the user passed isDefault as "Y". If so, I need to update all entries for this user to make default "N". My question: considering that there are no problems with blocking (no more than one thread requests data from the table for a specific user), which one of the following queries takes the least time:

update table
set default = 'N'
where user_id = 'abc'
and default = 'Y'

(Overhead where default = 'Y' check)

OR

update table
set default = 'N'
where user_id = 'abc'

( )

Answer 1

. , = 'N' . , = 'Y' .

, 1 , . 2, .

Answer 2

99% , .

, - user_id, UPDATE , . SQL Server , , ( ).

, , UPDATE , 1.

Answer 3

. SQL Management Studio " ". .

Answer 4

Oracle 1. .

, , , . , . , , , , .

drop table user_email;

create table user_email 
(userid varchar2(4) not null, default_ind varchar2(1) not null, 
email varchar2(30));

create unique index ue_x on user_email 
  (userid, decode(default_ind,'Y','Y',email));

insert into user_email (userid, default_ind, email) values ('fred','N','a');
insert into user_email (userid, default_ind, email) values ('fred','N','b');
insert into user_email (userid, default_ind, email) values ('fred','Y','c');

update user_email
set default_ind = 'N'
where userid = 'fred'
and decode(default_ind,'Y','Y',email) = 'Y';

update user_email
set default_ind = 'Y'
where userid = 'fred'
and email = 'a';

PS. " , default =" Y "check", , , .

Answer 5

The query speed depends on several factors, such as the number of rows in the table, indexes, control constraints, and foreign and primary keys.

The best way to determine which is faster, at least in SQL Server, is to use statistics and a graph of how the client is turned on when the query starts. Compare time for each and choose the best.