In this post, I’d like to share my experiences with databases. When still as a student I attended classes delivered by Mr Wilczek, PhD. Eng. and Mr Tuzinkiewicz, PhD, Eng., I was convinced that databases were an area around which my future career would develop. However, it didn’t happen because, as far as my professional life goes, I went in a different direction, and I haven’t had much to do with bases up until recently. One day, though, I felt that it was time I’d got back to databases and backend.
When I was still employed at MSFT, I had a lot of room for action. However, when I decided to come back to Poland and started talking to companies, my insufficient experience and knowledge complicated things for me a little. Having said that, despite the fact that I didn’t do perfectly well in a few interviews, I found a job where today I have an opportunity to solve database related problems. Now, having worked in this field for two years, I’d like to sum up what I think a programmer should know about databases.
Below, I present the scope of subjects about which every programmer should be knowledgeable already at the beginning of their career:
- How to write select/update/delete queries with WHERE, JOIN, GROUP By, and ORDER BY clauses;
- What influence the choice of the main key has on the sorting order;
- How data sorting works and what sorting order depends on;
- How grouping works and how unnecessary duplicated lines can be removed;
- How indexes work, what types of indexes there are, and what are the consequences of using them;
- What are constraints;
- What are stored procedures;
- What are views;
- What is the difference between truncating and deleting;
- What types off joins there are and what are the differences between them;
- How to use AND and OR in WHERE clause.
At a little higher level of advancement, a programmer should know the following:
- How to analyse query plans;
- How COLLATION impacts sorting;
- What are the levels and consequences of using isolations;
- What types of data are most appropriate as the main key;
- What the command SET NO COUNT ON is used for;
- How to handle errors in stored procedures;
- What are the consequences of defining views from SELECT;
- What the command SET XACT_ABORT ON is used for;
- Where to set timeout for commands;
- What is the order of executing clauses;
- How to use a connection pool and how to properly select it for the system;
- What is CROSS JOIN;
- What are the possibilities of searching by a single column and many different values;
- What are temporary tables and table variables;
- What is CTE and what it’s used for;
- How to construct a query to be able to “page” data loading.
To be able to solve more difficult problems, a programmer should know the following:
- How to use SQL profiler for diagnostics;
- How to analyse deadlocks: profiler and traceflag;
- What happens when a timeout occurs (on the side of the client and the server).
That about it, as far as I’m concerned. And what do you think? What skills are indispensable in a good programmer’s work? You’re welcome to contribute to this subject.