The power of firstOnly keyword

Sometimes only one record from a table is required, but firstOnly keyword is not used by a developer. For example:

select AccountNum from custTable where custTable.CustGroup == '10'; if (custTable.AccountNum) { ... }

What difference does it make?

Problem description
Compare select statements with and without firstOnly keyword.

Hints
Use SQL Server Profiler for analysis and trace RPC:Starting and RPC:Completed events:

Solution
The first test is the select statement with firstOnly keyword.
RPC:Starting event is below:

declare @p1 int
set @p1=NULL
declare @p2 int
set @p2=0
declare @p5 int
set @p5=28688
declare @p6 int
set @p6=8193
declare @p7 int
set @p7=2
exec sp_cursorprepexec @p1 output,@p2 output,N'@P1 bigint,@P2 nvarchar(5),@P3 nvarchar(11)',N'SELECT TOP 1 T1.ACCOUNTNUM,T1.RECID FROM CUSTTABLE T1 WHERE (((PARTITION=@P1) AND (DATAAREAID=@P2)) AND (CUSTGROUP=@P3))',@p5 output,@p6 output,@p7 output,5637144576,N'usmf',N'10'
select @p1, @p2, @p5, @p6, @p7

The most interesting parameter here is @p7. It is the number of fetch buffer rows (more details are on msdn and blog posts). I do not know why 2 rows are requested, but based on RPC:Completed event only 1 row is fetched:

declare @p1 int
set @p1=1073741872
declare @p2 int
set @p2=180150427
declare @p5 int
set @p5=16
declare @p6 int
set @p6=1
declare @p7 int
set @p7=1
exec sp_cursorprepexec @p1 output,@p2 output,N'@P1 bigint,@P2 nvarchar(5),@P3 nvarchar(11)',N'SELECT TOP 1 T1.ACCOUNTNUM,T1.RECID FROM CUSTTABLE T1 WHERE (((PARTITION=@P1) AND (DATAAREAID=@P2)) AND (CUSTGROUP=@P3))',@p5 output,@p6 output,@p7 output,5637144576,N'usmf',N'10'
select @p1, @p2, @p5, @p6, @p7

The second test is the select statement without firstOnly keyword.
RPC:Starting event is below:

declare @p1 int
set @p1=NULL
declare @p2 int
set @p2=0
declare @p5 int
set @p5=28688
declare @p6 int
set @p6=8193
declare @p7 int
set @p7=10
exec sp_cursorprepexec @p1 output,@p2 output,N'@P1 bigint,@P2 nvarchar(5),@P3 nvarchar(11)',N'SELECT T1.ACCOUNTNUM,T1.RECID FROM CUSTTABLE T1 WHERE (((PARTITION=@P1) AND (DATAAREAID=@P2)) AND (CUSTGROUP=@P3))',@p5 output,@p6 output,@p7 output,5637144576,N'usmf',N'10'
select @p1, @p2, @p5, @p6, @p7

RPC:Completed event:

declare @p1 int
set @p1=1073741868
declare @p2 int
set @p2=180150467
declare @p5 int
set @p5=16
declare @p6 int
set @p6=1
declare @p7 int
set @p7=10
exec sp_cursorprepexec @p1 output,@p2 output,N'@P1 bigint,@P2 nvarchar(5),@P3 nvarchar(11)',N'SELECT T1.ACCOUNTNUM,T1.RECID FROM CUSTTABLE T1 WHERE (((PARTITION=@P1) AND (DATAAREAID=@P2)) AND (CUSTGROUP=@P3))',@p5 output,@p6 output,@p7 output,5637144576,N'usmf',N'10'
select @p1, @p2, @p5, @p6, @p7

It means that 10 rows are fetched into buffer instead of 1 row required in X++ code.
Why?
It is specifics of a cursor statement. In X++ it is possible to use next statement after a select statement to fetch an additional record.
Why 10?
The default is 20 rows, but the actual value is calculated as Maximum Buffer Size / Row Length.

Conclusion
It seems to be not a big deal, but small problems usually escalate. Using firstOnly keyword definitely improves performance.