The SQL Server Management Studio (SSMS) has a simple way to increase the font size of your query. (You can select a value between 20% and 400% in the bottom of that window). But when you try to increase the font size in the result grid, you find nothing. We always have to guess what it says when we share our screens in a Teams meeting. Luckily, someone in my team found the way to fix that problem.
Naming is important and when you find a more precise one, you not only should update your code but your database as well. SQL Server has the function sp_rename to rename objects in your database.
So far, I used separate statements to add a new column to a table, set a default value and add a foreign key relation to another table. However, we can do these 3 steps in one single command:
Running your database with a case-insensitive collation is often a great help. If you search for 'de' you find 'de', 'De', 'dE' and 'DE' as well. However, if you need to make a case-sensitive search, that benefit is now a liability and all the other cases clutter your result.
Getting parts of a string is usually a job for SUBSTRING. However, when you only care about the last two characters of a string, save yourself the troubles of calculating the starting point for the SUBSTRING function. A much simpler approach is to use the RIGHT function:
I had to check some inconsistencies in our database naming convention and needed a list of all column names inside a database. While you can find multiple solutions with Google, it took me a while until I found one that worked and was flexible enough for all my needs.
When we tried our first attempt to disable unsecure protocols, we run into an annoying problem on the servers that run SQL Server. After disabling all the outdated security protocols, we could no longer connect to our databases. The error message was no help at all:
When you let SQL Server create the primary keys for a table and try to insert a specific key, you will get this error message:
Cannot insert explicit value for identity column in table 'MyTable' when IDENTITY_INSERT is set to OFF.
I use DbUp for all my database migrations. It is super simple and when something goes wrong, you can fix it without much effort. So far, I never had any problems with the embedded scripts approach as it is explained in the Getting Started section of the official documentation and in my blog post.
However, while trying to get Docker working with DbUp I noticed that I must do a lot of extra work when I need to create a separate DbUp image for every migration project I use. It would be much simpler when I could reuse my DbUp project and only replace the SQL scripts. Unfortunately, the embedded scripts approach requires a rebuild of the project and I am back at the beginning.
Some tasks I do in SQL Server would be much simpler if I could get a sequence or a range of numbers. I could create users#1 to user#1000 without any programming – just by using plain old SQL. PostgreSQL offers the generate_series function for such use cases. Unfortunately, there is nothing comparable in SQL Server. All you can find on Google are solutions that are complicated, long and impossible to remember.