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.
THROW (Transact-SQL) by MSDN
TRY…CATCH (Transact-SQL) by MSDN
Follow #TSQL2sDay on Twitter