Offers in your region
Turn notifications on desktop
Back on top

What a programmer should know about databases

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.

Basic knowledge

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.

Intermediate knowledge

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.

Advanced knowledge

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.

Comments

Contact us

By browsing this website you grant your consent to have your personal data which you entered while using the Service, and other parameters recorded in cookie files. stored on the device used for browsing the website, processed for marketing purposes, including profiling, and analytical purposes by “IT KONTRAKT” Sp. Z. o.o., with its registered office in Wrocław, ul. Gwiaździsta 66, 53-413 Wrocław, and Trusted Partners of IT KONTRAKT Sp. z o.o.