NEWSEQUENTIALID() (Transact-SQL)

Creates a GUID that is greater than any GUID previously generated by this function on a specified computer since Windows was started. After restarting Windows, the GUID can start again from a lower range, but is still globally unique. When a GUID column is used as a row identifier, using NEWSEQUENTIALID can be faster than using the NEWID function. This is because the NEWID function causes random activity and uses fewer cached data pages. Using NEWSEQUENTIALID also helps to completely fill the data and index pages.

Important
If privacy is a concern, do not use this function. It is possible to guess the value of the next generated GUID and, therefore, access data associated with that GUID.

Using STDEVP in TSQL to eliminate outliers

If you have a bell curve distribution of events, then only 68% of values will be within 1 standard deviation away from the mean (95% are covered by 2 standard deviations). Using stdev / stdevp sql function I can limit the original data set and find an average for the values that are within the appropriate number of standard deviations excluding outliers.

declare @stdtest table (colname varchar(20), colvalue int)

insert into @stdtest (colname, colvalue) values ('x', 377942)
insert into @stdtest (colname, colvalue) values ('a', 377943)
insert into @stdtest (colname, colvalue) values ('a', 2041)
insert into @stdtest (colname, colvalue) values ('h', 5)
insert into @stdtest (colname, colvalue) values ('h', 6)
insert into @stdtest (colname, colvalue) values ('b', 5)
insert into @stdtest (colname, colvalue) values ('b', 8)
insert into @stdtest (colname, colvalue) values ('h', 7)

insert into @stdtest (colname, colvalue) values ('z', 377943)
insert into @stdtest (colname, colvalue) values ('z', 50000)
insert into @stdtest (colname, colvalue) values ('z', 2041)
insert into @stdtest (colname, colvalue) values ('z', 524)
insert into @stdtest (colname, colvalue) values ('z', 50)
insert into @stdtest (colname, colvalue) values ('z', 4)
insert into @stdtest (colname, colvalue) values ('z', -100000)


select test.colname, avg(test.colvalue) stat_average, count(*) effective_cnt, min(bounds.cnt) total_cnt
from @stdtest test
inner join
    (
        select colname, STDEVP(colvalue) cstdev, AVG(colvalue) cavg, count(*) cnt
        from @stdtest
        group by colname
    ) bounds on bounds.colname = test.colname
where abs(bounds.cavg - test.colvalue) <= bounds.cstdev * 1   -- 1stdev = 68%   2stdev = 95%
group by test.colname

Regex for TSQL comments

This regular expression will help you to match comments in TSQL code.
Single line comments (--)  and multiline (/**/) are supported. Multiline comments can be nested one into another. Only one level of nesting is supported.

/\*(?>(?:(?!\*/|/\*).)*)(?>(?:/\*(?>(?:(?!\*/|/\*).)*)\*/(?>(?:(?!\*/|/\*).)*))*).*?\*/|--.*?\r?[\n]|--.*?$

We used this regular expression to remove all comments from TSQL before analysis.

Inspired by http://stackoverflow.com/a/7690874/1012986