"
SQL Triggers
17-06-2021 - Software Development
Triggers. Love them or hate them. But they certainly are usefull. Where Stored Procedures are often used to schedule certain actions at specific times, Triggers are executed direct and on the fly.
So if things need to happen right away to keep your data consistent. Or if users cannot wait for the next run of your Stored Procedure, Triggers might just be the answer.
SQL Server for Free
16-06-2021 - Software Development
If you want to start learning T-SQL, the SQL language, you will need SQL Server. People tend to go to MySQL because that is free to use where the Microsoft version is a paid license.
That is not correct. Microsoft offers the SQL Express Edition which is free to use, even for production. True, it has some limitations but in general that will not really be a problem. Here's more information on that one.
SQL Cursors
11-06-2021 - Software Development
Cursors do not have the best reputation. They are slow, take up a lot of memory and what you can do with a cursor can also be done in different ways.
Personally I don't agree with this all. There are more than enough cases where a cursor works just fine. So here's one that is dedicated to the cursor and why (and when) you should use them. Really, they can be awesome when used properly!
SQL FullTextSearch
01-06-2021 - Software Development
Full Text Search is not a very well known option in SQL Server. And true, its use is intended for specific cases. But in those cases, it can be a powerful tool.
It's also not a part of the SQL Server Default Installation. So it takes a few steps to set up. So in this post, let's have a look at when you could use it and how to work with it. Spoiler Alert: Many Examples included!
SQL What If?
28-05-2021 - Software Development
Every programming language has a form of If-Then-Else statement. A test based on logical conditions where something should happen if it is true, and something else if it's not.
SQL is no exception to this and in fact gives you multiple options to do this. So today I want to talk about the 3 different options and when to use them.
SQL Agent
21-05-2021 - Software Development
Unless you only work with SQL Express, you will be familiar with the SQL Agent. You know, the little guy that lives in your server and performs task when and how you want.
The SQL Agent is great but there are some considerations. So here is my set of Best Practices for working with the SQL Agent. One of them: Avoid using the SQL Agent!
SQL sp_BLitz
19-05-2021 - Software Development
When people ask for advice about optimizing query performance, one of the things I look at is the SQL Environment. Yes, often queries can be improved by adding or removing indexes, but often the cause is pretty obvious: bad server management.
So how to check if a SQL Server is in good shape? For that I use sp_Blitz, written by Brent Ozar.
SQL Database Compare
14-05-2021 - Software Development
Maybe you have also done this before. You made some great things in a test- or copy database. And when you are ready to bring it to production, you realize you did not keep track of all the changes you made.
So how do you find out what you changed or added that should be copied to the production database? Well, there is no 100% fool-proof method. But with these queries you can be pretty sure you will nail it.
Management Studio 1
07-05-2021 - Software Development
When you work with MS SQL Server, the most important tool is the Management Studio. With it, you can create and maintain databases, do back-ups and take care of security. And of course it is the tool to write your queries with.
So when you spend a lot of time in Management Studio, be sure to know these tips and tricks.
Read CSV with SQL
29-04-2021 - Software Development
Last week a customer asked if it was possible to integrate numbers from a CSV file with a report. Now the Reporting Tool they used could only read from an SQL Database. So to do it directly was not possible.
But by importing the CSV file into the datbase, it was definitely possible. Great! But how can you automate this process so a new file can be imported daily?
3Delussional
28-04-2021 - Business & Management
Last week, someone asked me why I never use 3D charts in reporting. It looks quite fancy, I must admit. But I stopped using them many years ago and not without a reason.
Do you want to know why I avoid 3D graphs? Check out this blog post!
SQL Geocodes
21-04-2021 - Software Development
Say, in your CRM system you have the name and address of your customers. Now you also need the geo coordinates: longitude and latitude. For that you can go to Google Maps, but that will be time consuming doing it one by one. Ah, if only you could query the Google Maps API in your SQL statements.
Well, actually. You can. So let's have a look how to work with an API in SQL Queries!
SQL Mistakes 1
15-04-2021 - Software Development
Often when people ask me for help with a Query, it's not because they get errors. If that's the case, the error most often provides a good description on what is wrong and will give quite a good hint on how to solve the problem.
So most questions arise from Queries that give results without any error. But the numbers just don't add up. So what could be the mistake?
SQL Shortcuts 3
08-04-2021 - Software Development
By popular demand: again a collection of small tips and tricks that are not big enough by itself for a full post. But since they can be very useful, I decided to bring these 4 together in one post.
So yeah, it might seem a bit random all together, but I'm sure you will find something useful in here today. This time a smart update, tables with ranges, patindex tricks and a creative use of the cross apply.
SQL Mailer
01-04-2021 - Software Development
There are several tools to send email from SQL Server, from which BPM Software is my favorite. With those tools you can do many wonderful things, but sometimes the budget is limited and purchasing such tools is not an option.
Luckily, SQL Server has build-in Email functionality as well. Maybe not as advanced as such tools, and certainly the layout options are limited. Then again, if you just need notifications and you have no budget for advanced tooling, this is certainly usefull.
Dev Extensions
30-03-2021 - Software Development
As a web developer I mainly use Google Chrome as my default browser. One reason for that is that I have hooked it up to my Google Account so my favorite extensions are always available when I log in.
So which extensions are so essential to me that I have a hard time when I don't have them available? Well, here is a list of the ones I use on a daily basis.
SQL Shortcuts 2
26-03-2021 - Software Development
This time again a collection of small tips and tricks that are not big enough by itself for a full post. But since they can be very useful, I decided to bring these 4 together in one post.
So yeah, it might seem a bit random all together, but I'm sure you will find something useful in here today. Let's have a look at what we've got!
SQL Versioned Tables
19-03-2021 - Software Development
Sometimes you want to keep track of changes made to your data. So you can keep track of who made the changes, when it was changed and which changes were made. One way of doing that is the use of Triggers and store the original values in a copy of the table.
But triggers have an impact on performance and they can be disabled. A better way to do this is to make use of Temporal Tables, also known as Versioned Tables.
Get Strings From Result
12-03-2021 - Software Development
This week I encountered the following case. This application had Projects that where not linked to a single Customer. Each project had multiple Files and those were linked to a Customer. They wanted to be able to search for a Project using the name of a Customer.
For that I needed all the Customer names from the Files associated with a given Project. Here's how I solved this one.
1 Insert into 2 Tables
05-03-2021 - Software Development
With the Insert Statement you can add a record to a table. If you want, you can even insert multiple records into that table in one go. For example by using a Select Statement for the values to be inserted.
But did you know you can also use just one insert statement to insert records into 2 tables? If not, check this one out because this post will show you how to just that!
SQL Shortcuts 1
26-02-2021 - Software Development
This time a collection of small tips and tricks that are not big enough by itself for a full post. But since they can be very useful, I decided to bring these 5 together in one post.
So yeah, it might seem a bit random all together, but I'm sure you will find something useful in here today. Let's have a look at what we've got!
SQL Running Totals
19-02-2021 - Software Development
Let's say you have a table with invoices, containing the invoice amount. Nothing is easier than to put that amount in the result set.
But what if you want a running total? With that I mean that the second record shows the sum of record 1 and 2. And the third record shows the sum of record 1, 2 and 3. And so on.
Well, since you already have the information in the result set, there is an easy way to do that!
Don't document code
18-02-2021 - Software Development
Code should not be documented. There, I've said it. Yes, as a Developer and Product Manager myself I state that code should not need documentation. The reason for that is simple: Code should be self explanatory. If there is a need to document your code, the code itself is simply not good.
So before documenting bad code, improve the quality of the code itself. Here are some tips and tricks on how to do that.
Geek & Poke
17-02-2021 - Just For Fun
Oliver Widder is not just a great IT guy. He is also a great cartoonist. He has done some great cartoons about the weird wide world of IT and they are pretty accurate. You might even say: Painfully accurate! He posts his cartoons on his website Geek and Poke.
If you are not that much into IT, you might not understand all the jokes. But if you are a geek like me, be sure to check them out. You will enjoy them!
SQL Lag & Lead
12-02-2021 - Software Development
Let's say you ran a query on a table containing appointments and you ordered them by the appointment date. In the result set, you can easily see the date of the previous appointment because it is on the previous line.
But what if you want that previous appointment date on the same line? You could do that with a sub-query, but that would really affect the performace. Wouldn't it be nice if you could just grab it from the previous record from the result set? Well, you can by using Lag and Lead.
CSS Variables
10-02-2021 - UX Design
When writing CSS, I prefer to use variables. Or as they are also known: Custom Properties. First of all, it makes it a lot easier to make changes to a CSS file since the change has to be made in one place only.
Additional benefit, it helps you keeping things simple and consistent. How? Just don't create to much variables and stick to the ones you have. Here's how you can use CSS Variables.
SQL Encryption
08-02-2021 - Software Development
So, you have painstakingly written a very complex view or stored procedure. Hours of work went into that job. Now it has to go into the database of the customer. From there on, everyone who has access to that database can see your work. And even worse, they can copy and re-use it in a fraction of the time you spent on it. Ah, if only there was a way to make that impossible.
Well, there is a way, actually. Here's how you can do that!
How to test software?
02-02-2021 - Software Development
Testing Software is easy. You just click around, fill out some stuff and if no errors occur than everything is ok, right? Wrong!Testing Software is much more difficult than you might think.
Testing is a discipline all by itself and good testing is essential for a smooth and successful Software implementation. So, to help you take testing to the next level, here are some tips & tricks that will help you do just that.
The Laws of UX Design
01-12-2020 - UX Design
When designing a User Interface, whether it is for a website or an application, there are many rules that can help you evaluate if the design will work or not. Using these rules of course isn't mandatory, but the principles behind it are proven.
So it's a good thing to know them and use them especially when you are stuck on a design. Or when you are in doubt of what would work best. Jon Yablonski created a website, Laws of UX, where he brings together the most common principles.
Database Design
30-11-2020 - Software Development
A good database design is the essential foundation for every application. But still, from time to time we have to (re)build an application on an existing database and we feel the need to scream. The original title I had in mind for this post therefor was 'How to annoy a Developer'.
But let's stay positive. No one does that on purpose. Often people simply don't realize why some things are important later on in the development process. So here are some tips and tricks to design a database that your developers will love.
Frontend Skills
29-11-2020 - Software Development
Previously I posted about the stuff you need to get started as a Frontend Developer. So once you have that, where to go next ? What skills do you need to acquire and how to go about to get them? Here's a lot of tips on that topic to get you started.
Frontend Tools
27-11-2020 - Software Development
To start doing some Frontend Development used to be quite expensive. With all the tools needed to do just the basics. Fortunately that has changed. Nowadays all the tools you need are absolutely free and easily available. In this post I will explain which tools you need to get started doing Frontend Development using asp.net/C#.