Maximum Value in Identity Column

One fine day, there was panic in the department when I reached office. All the ETL jobs had failed and the data warehouse was not refreshed overnight. Looking at the logs, the support team told that the max value of the integer identity column has been reached in one of the main fact tables and hence the failure. Lots of solutions were proposed that would result in downtime of minimum of 6 hours - the fact table in that system had 250 million records.

I then suggested a simple solution that could be implemented in a second and the systems could be up and running from the next minute. The solution was to 'Reseed' the identity column to minimum negative value allowed for integer value.

DBCC CHECKIDENT (MyTable, reseed, -2,147,483,648)


This was implemented and the jobs ran fine. The key to this solution is the initial seed value of the integer column in the table - if it had started with the above negative value, this would not have been possible. 

SQL Server - Random Number

Frequently I get asked by my colleagues on random number generation in SQL Server. The novices are puzzled why the Rand() function returns the same number when used in a select statement to generate data.

To generate random number in a single select statement within a range, I always use the below method.

 CREATE VIEW vwRandNumber  
 AS  
      SELECT RAND() as RandNumber  
 GO  
 CREATE function dbo.fnRand() RETURNS FLOAT   
 AS   
 BEGIN   
      RETURN (SELECT RandNumber FROM vwRandNumber)   
 END  
 GO  
 CREATE FUNCTION fn_RandNumber_Int(@Min int, @Max int)  
 RETURNS INT  
 AS  
 BEGIN  
      RETURN @Min + (select dbo.fnRand()) * (@Max-@Min)  
 END  
 GO  
 CREATE FUNCTION fn_RandNumber_Float(@Min float, @Max float)  
 RETURNS float  
 AS  
 BEGIN  
      RETURN @Min + (select dbo.fnRand()) * (@Max-@Min)  
 END  
 GO  
 SELECT TOP 10 dbo.fn_RandNumber_Int (10, 200) FROM sys.objects  
 SELECT TOP 10 dbo.fn_RandNumber_Float (10.1, 200.7) FROM sys.objects  
 GO