SQL Server: Random Numbers on a Row by Row basis

March 9, 2011

If you use the standard RAND() function call in SQL, it will execute once for a column in a batch, and that same value will be used for every row in your SQL Query. It is possible to get around this problem.

Everyone knows that NEWID() will generate a new guid for every row in the database. If you pass a NEWID() into the CHECKSUM function, it will return an int value (can be positive or negative) between min and max int.

To return a value equivalent to the RAND() function, I simply execute the following:

SELECT ABS(CHECKSUM(NEWID())) / 2147483647.0

This will return a positive value between 0 and 1. Note that the decimal point in the max int figure is important here because it converts it to a floating type – otherwise the result would be truncated and always be zero.

So all you need to do if you want to return a set of random values, say, between 0 and 100 would be to multiply it by 100 and subtract 1. The cast to int simply truncates the result, so that we don’t have decimal places.

SELECT Id, CAST(ABS(CHECKSUM(NEWID())) / 2147483647.0 * 100 - 1 as int) AS RandomNum
FROM MyTableContainingLotsOfRows

SQL Server: Generating SQL For Missing Foreign Key Indexes

March 9, 2011

One way to improve performance in SQL Server is simply to ensure that there are indexes on all foreign key.  This is NOT done automatically, which is kind of surprising given how much of an impact such a change would have on the performance of a significant install base of SQL Server.

I have a simple stand-alone SQL Script that I execute to generate the SQL for the missing foreign key indexes. Here it is:

SELECT DISTINCT 'CREATE INDEX IX_' + REPLACE(objconstraint.name,'.','_') + ' ON ' + fkcols.name + '.' + objfk.name + '(' +
	STUFF((	select ',' + col.name
			from sys.foreign_key_columns fkcol2
			join sys.columns col
			on fkcol2.parent_object_id = col.object_id
			and fkcol2.parent_column_id = col.column_id
			and fkcol2.constraint_object_id=fkcol.constraint_object_id),1,1,'') + ');'
FROM sys.foreign_key_columns fkcol
	JOIN sys.objects objfk ON fkcol.parent_object_id = objfk.object_id
JOIN sys.schemas as fkcols ON fkcols.schema_id = objfk.schema_id
	JOIN sys.objects objconstraint ON fkcol.constraint_object_id = objconstraint.object_id
LEFT JOIN sys.index_columns ic ON ic.object_id = fkcol.parent_object_id
	AND ic.column_id = fkcol.parent_column_id
AND ic.index_column_id = fkcol.constraint_column_id
WHERE ic.object_id IS NULL

This script outputs the results in the results grid, so you can cut and paste it into a new query window for execution.

I was asked why I didn’t use INFORMATION_SCHEMA views in this bit of SQL. The answer is simple – the standard for INFORMATION_SCHEMA doesn’t include Indexes. Enjoy!