“A night in the life of a SQL Server Consultant”

Preface: Rumour has it that some grand old houses in the British Isles may be haunted. A SQL Server consultant spends a night in such a house musing over the use of T-SQL versus SSIS. The story is entirely fictitious and the article has been written pro bono and dedicated to the SQL Server community. For its interest, amusement and imagination.

*****

The approach to the house was from a narrow lane, off the beaten track. Without any fanfare. The casual passer-by would have missed it completely.

It was a long walk along the meandering drive. Past the old stable blocks. Over an old brick bridge and up to the spacious forecourt. The front door opened without fuss or hindrance; and the first sound that was heard were the six chimes of the grandfather clock in the grand Reception. After the usual exchange of pleasantries, some thought was devoted to the choice of room. Much was available, but some were rarely, if ever, occupied. Finally, Number Nine was chosen. A suite, on the uppermost floor of the Tudor house.

The first place of rest was a welcoming guard's armchair, with a high hooded back, covered in buttoned down leather, and resplendent in a warm shade of burgundy. From deep within it, the outlook was of the grand curving staircase, the high wooden ceiling and the mighty clock. At the top of the staircase was a narrow door, and then another flight of stairs to the uppermost landing. A final long and narrow corridor led to the chosen place of rest from a gathering storm.

The suite comprised a study, bedroom and bathroom, which were linked together by a brightly lit corridor. In the Study stood a captains desk, a royal swivel recliner and a standard lamp with a strawberry coloured shade. Oddly, the chair was lazily revolving to a standstill, coming to rest when it faced the onlooker. At the firm tug of a cord, the motor started and at the same time a bright light illuminated the bathroom, which was austere but immaculate, with white floor to ceiling tiles and a cream tin bath tub. In the bedroom was a Queen's four-poster dressed with satin linen, close to a large sash window with embroidered curtains expertly drawn together.

The bedroom was already lit by a small but exquisitely ornate bedside table lamp, oriental in origin, across which strutted a majestic turquoise peacock. It seemed that the suite was ready and waiting, exuding a warm and somewhat eager welcome, almost as if the arrival had long been desired.

In the warm confines of the suite, there was time enough before supper to reflect upon the day's challenge. Expediency versus Best Practice! Systems Development can pose questions which involve the complex interaction between technical and human factors. Although providing a sound technical foundation for evolving business requirements is key, consideration must be given to the human resources tasked with delivering the technical solution. The Architect ought to demand the delivery of a technical solution that conforms to Best Practice. So, purchase the necessary skills to make up any shortfall. Overcoming opposition to new techniques has always been the challenge facing IT project teams.

With the clock chiming eight times, the evening meal was served. Seated in the conservatory at a table by the window, the meal commenced with a decent measure of Chateaux Margaux. The ambience of the room was quietly warm, due in large part to the conversations having a fairly educated air about them. Each table was properly laid, with fine china crockery and Sheffield steel cutlery. Smoked salmon roulade was served with warm toast and a hint of freshly ground pepper. The shoulder of lamb was boneless, perfect in texture from having been slowly roasted since noon. To finish, the crème brulee was warm, with a sharp coating. Supper was a triumph. A meal befitting the tradition of a Hunting Lodge of the King.

The constitutional walk started slowly, taking the route over the footbridge, around the stable block, with a brief sojourn in the rose garden. Gathering speed, and with long strides across the broad lawn, the walk continued with some exertion along the footpath, coming to an abrupt halt by the water. By now, the menacing dark gigantic clouds, borne by strengthening and swirling winds, were beginning to shed their heavy load. From the distant shelter of the boathouse, through the pouring rain, the lights of the conservatory shone brightly from an otherwise unlit house. Peering through the mist, the suite on the uppermost floor of the grand house was barely discernible, for it lay in complete darkness.

Upon the return, and without any effort, the suite was once again a warmly lit place of refuge. A full measure of Malmsey Rich Madeira stood on the red leather desk. A suitable time and place to reflect upon an often experienced purely technical debate, invariably the scene of a vigorous exchange of opinion. Debating on whether to use SSIS or T-SQL is a typical example of the frequent struggle between Expediency and Best Practice.

T-SQL: Common knowledge from having been tried & tested for over two decades; comprising familiar programming constructs (e.g. IF/ELSE, WHILE); code that can be generic and meta-data driven (using Dynamic SQL); with a degree of parallelism (using a Batch Scheduler).

SSIS: Relatively new software, with rich functionality (e.g. variety of data sources); Better at row-by-row (in-memory) processing; with a Graphical User Interface (making it easier to understand and de-bug); which lends itself to Parallel processing (if only for the orchestration of numerous Stored Procedures).

The twist is that the two are not mutually exclusive! The database engine is still best at Set manipulation. So, it has become common practice to embed "Best SQL" within SSIS Packages. A good example is the use of the T-SQL MERGE TYPE II statement to perform "SCD" processing:

INSERT INTO dbo.DimTarget (columns) 
SELECT (columns, ToDate = NULL) FROM 
....( ........MERGE dbo.DimTarget as t 
........USING dbo.StgSource as s 
........ON t.BusKeyId = s.BusKeyId 
............WHEN NOT MATCHED THEN 
................INSERT (columns, ToDate) VALUES (columns,NULL) 
............WHEN MATCHED t.ToDate IS NULL THEN 
................UPDATE SET t.ToDate = GETDATE() 
................Output $ACTION ActionOut, columns -- for the second insert 
....) as m WHERE 
....m.ActionOut = 'UPDATE' ;

Sleep began to creep in, slowly but surely, leading to a gentle doze for a short while, aided in part by the warm glow of the lamp, until the first awakening.

The phone was ringing. The nostalgic ring of bygone days. The voice at the end of the line was at first exclaiming then immediately questioning. The subject was a Face that had appeared for a few moments. A familiar one. It had happened so quickly that the reason for its appearance had been missed. Why, oh Why, had it been shown?!

The laughter in the conservatory had started to die away. The friendly chatter in the kitchen and the footsteps to and from the scullery and pantry continued for a while longer. Tables were laid for the last time in the day. Slowly and almost methodically, the lights in the conservatory, corridors, and finally the reception areas went out. Darkness had crept in all over to slowly occupy the house.

The phone was ringing once more. The voice at the end of the line was ecstatic: "The answers are out there! Just google the right questions. I know why now!" And with that, deep sleep soon set in.

A single chime was sounding in the distance. The sudden awakening was caused by an urgent, seemingly desperate knocking on the sash window. Sitting stiffly upright, the next sound that could be heard was the motor whirring away in a dark bathroom. Moving to the window, swiftly drawing back the curtains, peering through the rain beating fast against the thick glass, there was nothing to be seen. Then, a brief quiet descended by the window. After returning to bed the knocking came again, growing louder in desperation. To no avail. The window would remain securely fastened for the third and final fruitless attempt. Eventually, sleep returned, again aided in some way by the relief and comfort that was obtained from the warm glow of the lamp.

Three Chimes. The Final Awakening. In a cold room, the sash window was painfully rolling back up by itself; entirely of its own accord. Somehow, the curtains had come away from their tie-backs. In the eye of the storm, a broad shaft of moonlight had outshone the darkness. And so it took a few moments to realise, in the light that there was to discover, that the lamp had been switched off.

*****