sql - What is the most efficient way to process rows in a table? -

i teaching myself basic , intermediate sql concepts project working on.

i have lot of data needs undergo processing can presented in different ways. right using scalar functions calls in select statement process data.

a simple example, lets have attribute in table called fun data type int. want process table rows fun < 10 'foo' , rows fun > 10 'faa'.

so write sql function like

create function dbo.fooorfaa (     @fun int ) returns varchar(3) begin      if (@fun < 10)          return 'foo'      return 'faa'  end 

then use function in select statement

select dbo.fooorfaa([mytable].[fun]) 'blah' mytable 

this example trivial, in real code need perform involved logic against 1 or more columns, , selecting sub results procedures , joining tables , other things need in data base.

i have process lots of records in reasonable time span. method efficient way tackle problem? there technique should using instead?

for use case, need case construct.

select   case     when t.fun < 10 'foo'     else 'faa'   end foo_faa   mytable t 

always try use set-based operations. user-defined functions (mostly) kill performance, , should last resort.

see: case (transact-sql)