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.

Posted in Blog admin | Leave a comment

tim.edlund:

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.

Originally posted on 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. Suddenly it dawned on me that a lot of people still have no clue what a “numbers” or “Tally” table is, never mind how it actually works.

There are dozens of things we can do in SQL that require some type of iteration. “Iteration” means “counters” and “loops” to most people and recursion to others. To those well familiar in the techniques of “Set-based” programming, it means a “Numbers” or “Tally” table, instead. I like the name “Tally” table because, well, it just sounds cooler and there’s…

View original 97 more words

Posted in Uncategorized | Leave a comment

Originally posted on 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)

View original

Posted in Uncategorized | Leave a comment

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)

Link | Posted on by | Leave a comment