T-SQL Tuesday #56: Assumptions

This month’s T-SQL Tuesday is on Assumptions and hosted by Dev Nambi (@DevNambi | Blog). Thank you Dev Nambi for hosting T-SQL Tuesday #56!

It’s hard to rock the boat. 
It’s hard to ask the basic questions that everybody knows. 
It’s hard to slow down and ask for clarification.

So, we improvise. We guess: things that are accepted as true, without proof. We often forget our assumptions, or make them instinctively.

For this T-SQL Tuesday, the topic is assumptions.

How I Learned About Assumptions

When I first started working with SQL Server, one of the first things I learnt from my mentor was about expectation that the code would manipulate data in with a degree of certainty. The ETL process that we had in T-SQL was laid out in a way that we minimise assumptions. Obviously, if you know roughly what you expect how the code should behave then it would be easy to minimise assumptions.

Here are two simple and common examples of minimising assumptions. Please note that there are many ways to handle assumptions – it really depends on what has been agreed on.

1. Throw an error when things don’t seem right

As an example, the code is expected to process at least one record. If the code doesn’t do that, then there must be something wrong, and it should thrown an error.

As an example:

UPDATE [dbo].[DimCustomer]	
SET [CommuteDistance] = REPLACE([CommuteDistance], 'Miles', 'Kilometres')
WHERE [CommuteDistance] LIKE '%Miles' ;

IF @@ROWCOUNT < 1
BEGIN
	THROW 51000, 'Commute Distance update is expected to update at least 1 record', 1;
END;

2. Catch the expected (but unlikely) issues

There are times where we hope that the rate of the issues occurring is near 0%, and that if when they come up, it is safe enough for the code to continue with the next batch.

As an example, the following excerpt from a stored proc catches the unlikely issue and allows the rest of the code to continue.

CREATE PROCEDURE [dbo].[usp_CalcuLateCallCentreOperators]
AS
BEGIN
	....
	-- Excerpt starts here

	BEGIN TRY		
		INSERT INTO [dbo].[FactCallCenterSummary]
		(
			 [FactCallCenterID]
			,[OperatorsPerIssues]
		)
		SELECT 
			 [FactCallCenterID]
			,[TotalOperators] / [IssuesRaised]		
		FROM [dbo].[FactCallCenter]

	END TRY
	BEGIN CATCH

		INSERT INTO [dbo].[AuditLog]
		(
			[CapturedDate]
			,[StoredProcedureName]
			,[ErrorMessage]
			,[ErrorLine]
			,[ErrorSeverity]
			,[ErrorNumber]
			,[CustomMessage]
		)
		SELECT 
			GETDATE()
			,'[dbo].[usp_CalcuLateCallCentreOperators]'
			,ERROR_MESSAGE()
			,ERROR_LINE()
			,ERROR_SEVERITY()
			,ERROR_NUMBER()
			,'Error ignored, and process continued'
	END CATCH

	UPDATE [dbo].[FactCallCenterClosed]
	...
	-- Excerpt Ends Here

END
GO

What Can We Assume?

Nothing. You hear me – we should not assume anything (not in code, and hopefully not in life). We need to establish premises and to ensure that the premises are made known / documented, instead of making assumptions that are kept to ourselves.

Having premises established, means we also have determined a contract on what the code expects and produces.

About T-SQL Tuesday

T-SQL Tuesday was started by Adam Machanic ( Blog | @AdamMachanic ) in 2009. It’s a monthly blog party with a rotating host, who is responsible for providing a new topic each month. In case you’ve missed a month or two, Steve Jones ( Blog | @way0utwest ) maintains a complete list for your reading enjoyment.

Further Readings

THROW (Transact-SQL) by MSDN

TRY…CATCH (Transact-SQL) by MSDN

Follow #TSQL2sDay on Twitter