Ok .. so SharePoint blogging was driving me crazy

Welcome to our new technology posting blog – now on WordPress.  Wordpress has some nice widgets for blogging (pressing) from pretty much anywhere and subscriptions are more straightforward t0o (you don’t have to subscribe separately to comments).  Please post all your awesome tech article finds here along with other technology ideas that affect our product development and maintenance.  Make the blog more interactive and fun for everyone by commenting on the posts of others.  Got a client deadline coming up?  Let everyone know by posting it on the calendar – use the “countdown” widget for a more dramatic effect.

Try a tally table instead of a loop

Reblogged from synoptec DEV:

Try a tally table instead of a loop

Originally found by Ted, this is a great article on using "tally tables" instead of RBAR loops for better performance and maintainability of TSQL code.  Intro by the author ...

By Jeff Moden

I actually started out writing an article on how to pass 1, 2, and 3 dimensional "arrays" as parameters in stored procedures.

Read more… 191 more words

The less loopy the better! I have been using tally tables for a couple of years now but this article really expanded my understanding of how the technique can be applied more broadly. An excellent article - great post Ted! If you still use a lot of loops or cursors in your TSQL code you're out of touch with best practices. Tally tables, CTE's (common table expressions), MERGE and "update X" statements are well established methods to convert loops to set based operations and are fundamental skills expected of mid-level TSQL developers. If you don't have these in your repertoire now, do some reading and find ways to apply these techniques in your very next TSQL coding assignment.

Avoid function execution when parameters are null

Reblogged from synoptec DEV:

Avoid function execution when parameters are null

There is a specific syntax TSQL provides for skipping function execution when any parameters are NULL.  This often cleans-up code that would otherwise have to use branching logic to avoid the execution.  Fast, simple, and handy.

CREATE FUNCTION dbo.fMyFunction
(    
   @MyParm1    nvarchar(max)  
  ,@MyParm2    datetimeoffset
) returns bit with returns null on null input

(Available on SQL 2005 and later)

Avoid function execution when parameters are null

There is a specific syntax TSQL provides for skipping function execution when any parameters are NULL.  This often cleans-up code that would otherwise have to use branching logic to avoid the execution.  Fast, simple, and handy.

CREATE FUNCTION dbo.fMyFunction
(
   @MyParm1    nvarchar(max)
  ,@MyParm2    datetimeoffset
) returns bit
with returns null on null input

(Available on SQL 2005 and later)