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  

SSRS Export to Excel - Performance

One fine day, we got a call from one of the users complaining that a report was not working - typical user speak. On investigating the issue, we found that it was the way in which the report was being used. We have few parameters in the report and most of the users use the filter to retrieve < 1000 rows of data. But this particular user was generating the report that returns > 30000 rows and trying to export it to Excel. Excel could not open the report and fails with error saying the sheet could be corrupt.

To start with, I found that the size of the Excel file was ~75MB. The reason was the drill-down links that is included in the report that blows up the size of report. So, I included another parameter (cannot think of a cleaner way) based on which I do (/not) provide drill-down capability in the report and hence reducing the size of the exported report. The report had come down to 7 MB now. Even this was not good enough. The report could be opened but Excel took more than 10 minutes to open the report. Even saving the report and reopening it did not help.

On closely looking at the Excel file, I saw that there were lot of merged columns in Excel. I thought it may not be related to this issue but nevertheless decided to fix it. The report layout was very simple - Page Header with few textboxes, a table with data and a Page Footer with few textboxes. The reason for the merged cell was that textboxes in header and footer was not aligned to the table columns in the report body. So, when the report is rendered using Excel renderer, the columns were merged to accommodate the differing column widths. I went ahead and managed to reduce the merged columns from around 5 to 1 (This would be much harder for Matrix reports). Surprisingly, this solved the issue - the size of the exported Excel file went down to < 4 MB and the report now opens in < 1 min.