SQL Server

December 12, 2023

This describes my experiences with SQL from the earliest stages and explores challenges, variants and my efforts to keep up with the changes introduced over the years. The overview is explored in historical order.

SQL Server 7

My introduction to SQL Server was in 2003 where I discovered SQL Server 7 whilst in training at Brite Software in Hastings. There I learnt the syntax of Transact SQL and got experience in Clustered Indexes, Non Clustered Indexes, Primary Keys, Foreign Keys, data types, cursors, views, Stored Procedures, Triggers, replication, installation, remote server connections, queries, transactions, debugging code,and more.

Scoring high on my exams gave me confidence in SQL and we learnt all the administrative tasks that a DBA might be required to perform. Everything from setting up the server and relational databases to performance monitoring and maintenance tasks.

I finished the course and was sent straight out on a contract to fix a SQL based system called Informix, a product of IBM. I don't remember much about the job. It was a 2 week contract to fix something which I managed to do despite being so green in the programming world and being surrounded by experienced developers somehow I overcome the intimidation and found an acceptable solution that filled me with confidence for my next role at South Trafford College.

SQL Server 6.5

Where's the intelisense? Why is this in black and white? Why am I restricted to just 255 characters in my text field? I couldn't believe it. I'd put so much reliance on the system providing me with options and valuable colour based feedback that 6.5 put me in shock. I had a learning curve to overcome. The lack of colours was an easy one to get used to. I knew the syntax and features available but was new to the limited characters in the text fields that resulted in the database being designed in a different way.

So the task in hand, at South Trafford College, was to prepare the existing data, that comprised of student and teacher reports, and present it in a document format. For each report, the data was spread over multiple columns so the only way to manage the data as a unit was to understand the roles of UPDATETEXT, WRITETEXT and TEXTPTR that were all new to me at the time. Following a bit of research I was able to navigate my way around the database and produce whatever was asked of me. This built my confidence and appreciation for how SQL has developed and improved.

SQL Server 2000 and beyond

My next challenge, at Syntegra, involved a SQL Server 2000 cluster that sat on a windows 2000 cluster. This didn't really pose much of a challenge but was interesting none-the-less. I wasn't responsible for managing the server, just hired to write a multi-threaded classic VB6 automation program that ran as an NT service and interacted with the systems event log. I also wrote an add-in for the MMC (Microsoft Management Console) that performed the neccessary CRUD operations to the database and provided manaagement operations for the running of the system.

Then came Network Rail where I began as a DBA for a SQL server carrying out installations and configurations when I.T. failed. This was to host the data for a GUI that each department had an application for to fulfill their respective needs. Here I took a skills leap from cursors to recursive common table expressions. The server hosted advanced stored procedures, triggers, localised security features, replication and scheduled backups. I later migrated this to an Azure SQL database where we could manage security better and utilise the Active Directory, now called Entra ID.

Whilst there I created a really useful bit of code that can report the max length of each column in a given table that could make clear any adjustments to the schema that might improve performance or take up less storage.