more notes from the 1/12/2015 talk

• foo = N'foo' is an example of a Unicode string and I get the impression that these are more in tune with being used with nvarchar columns than regular strings (foo = 'foo') which fit well with varchar types. The speaker said not use nvarchar columns if you can help it. He also warned of the performance hit of implicit castings, not just from strings to numeric types but also from varchar into nvarchar!
• CHECKPOINT;
  DBCC DROPCLEANBUFFERS;
  DBCC FREEPROCCACHE;
  ...in advance of a query will drop any sort of caching of the query. The caching of a query may give you warped performance metrics when you attempt to use extended events to analyze how long things took. The caching will speed things up which is normally a good thing.
• COALESCE does scanning while ISNULL does seeking. http://msdn.microsoft.com/en-us/library/ms190349.aspx suggests COALESCE will find data in the first not null column in a select like so:
  SELECT Name, Class, Color, ProductNumber,
  COALESCE(Class, Color, ProductNumber) AS FirstNotNull
  FROM Production.Product;
• rowbar means row-by-row processing.
• RECOMPILE as a keyword may be thrown into the SQL of sprocs to help optimize their execution plans. I don't get it yet. http://msdn.microsoft.com/en-us/library/ms190439.aspx has more.

The talk all this was from is detailed here.

Addendum 2/8/2015: I think "rowbar" is better spelled: rbar