SQL random number column

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.

Comments

Post a Comment

Popular posts from this blog

MSSQL Statistical Z-Score

Computing the z-score of individual values in SQL Server is not a built in function (although average and standard deviation are). The z-score tells us how far off from the average value the individual values are. The following function computes the z-score: for a table named [data] having an [objectId] and [score] fields. SELECT [D].[objectId], [D].[score], [E].[avg_score], [E].[stdev_score], ([D].[score] - [E].[avg_score]) / [E].[stdev_score] as zvalue FROM [data] D CROSS JOIN (SELECT Avg(CAST([score] as float)) as [avg_score], StDevP([score]) as [stdev_score] FROM [data] ) E ;
Read more

IBM x335 and Windows 2008 installation

I love the fact that IBM stops supporting its hardware long before Microsoft does; this means that lots of top-tier companies have to decommission their *somewhat* old hardware and you can pick up a server cheap (or free) to play with at home. It may mean, however, that your installation mileage may vary. Such has been the case for my IBM x335 (of which I have three). I've been on a quest to do an installation of Windows Server 2008 on said boxen. (plural of ox is oxen, plural of box is...) How fickle a lover are antique hardware and modern operating systems? How doest thou vex me, let me count the ways... Windows 2008 only comes on DVD while an IBM x335 has only a CD and will not boot from USB. Windows 2008 will not install from any sort of DOS utility disk. So Bart's Network boot disk is out, likewise a network boot to anything based on DOS. The IBM x335 has an internal IDE that is used by its CD Rom. You can crack the case and use an internal DVD drive from some other comput
Read more

Database Projects, SQL Unit Tests, and TeamCity

Image
Continuous Integration  is a really good idea, especially when you are working with a team of developers. There are lots of tools out there that can help your team build better quality into your application. TeamCity by JetBrains is one such tool that has been around for awhile; specifically, it is the one that my company uses. Visual Studio has Database Projects   SQL Server Data Tools  to address some of the inconsistencies that developers face when using a database as a storage layer; Things like version control and consistent deployment. It also includes SQL unit tests , which fills a huge gap if any  of your logic is in the database. Many organizations moved away from logic in the database years ago because it was tricky (at best) to verify logic on the data tier, of course, they were also making changes directly to the database itself... but I digress. If you are using SQL Server Data Tools, and run your unit tests locally, it is a joy to behold. Your database is automatical
Read more