Here's a quickie. If you need to have a new random number on a column, this works really well.rand(cast(cast(NEWID() as varbinary) as int)) number Of course NEWID generates a random guid. Casting it as a varbinary first, then as an int gives you a random number which can then be used as a seed for the random number generator.
Showing posts from February, 2010
Here is a nice use for Common Table Expressions (CTEs) to generate a dates table or other list of stuff. I won't take credit for this technique since I saw it someplace else (but can't remember where). Of course my blog is for stuff I want to remember, so thanks to whoever you are...declare @loops int = 1000 declare @StartDate date = '1/1/2000'; WITH CountTable( RowNumber, [Month], [Year], FirstDay ) AS ( SELECT 1 RowNumber, MONTH(@StartDate) [Month], YEAR(@StartDate) [Year], -- compute the first day of the month from whatever day was provided DATEADD(dd,-(DAY(@StartDate)-1),@StartDate) FirstDay UNION ALL SELECT RowNumber + 1 RowNumber, MONTH(DATEADD(MONTH, 1, FirstDay)), YEAR(DATEADD(MONTH, 1, FirstDay)), DATEADD(MONTH, 1, FirstDay) FirstDay FROM CountTable t WHERE RowNumber < @loops ) SELECT * FROM CountTable OPTION (maxrecursion 32767) The results look like this: 1 1 2000 2000-01-01 2 2 2000 2000-02-01 3 3 2000 2000-03-01 4…