All Posts Business & Management Food & Drinks Just For Fun Keris Indonesia Software Development Tips & Tricks UX Design

SQL Substring

02-06-2023 - Software Development

There is a nice function in SQL to get a specific part of a string, called Substring. It comes in handy when you need to extract a value that is between 2 pre-defined characters. But the use of the Substrinf function might seem a bit useless at first glance.

In this post I will show you how you can use the Substring function to do just that. All you need to do is make the Substring function a little bit more dynamic.

The Power of Like

26-05-2023 - Software Development

To find specific matches with an SQL Query, you will mostly use the comparison operators. Like Equal To, Not Equal To, Greater Than of Lesser Than. Especially with String Values, comparing might not be enough. In comes the Like Statement.

Now the Like Statement is not that unknown in itself. But it has some nice features you might not be aware of. Here are some examples.

Write SQL Faster

19-05-2023 - Software Development

I must admit that sometimes I can be impatient. Watching other people write a SQL Query can be one of those moments. When watching someone who is not that experienced yet, I just have to suppress that feeling.

But if you write Queries on a daily basis, speed can be important. So in this post I will tell you about some of the tricks I use to speed up writing Queries.

Write Better SQL

12-05-2023 - Software Development

Sometimes people come up to me to ask if I can help them out with a Query. When they show me the Query, I have to say that I cannot help them because the Query is impossible to read. Do me, but most of all yourself a favor: write easy readable code.

Don't forget: The S in SQL stands for Structured. And Structure is what this post is all about.

Fun with SQL part 2

05-05-2023 - Software Development

Last time we looked at some easy tricks to tease your co-worker when you have only a few seconds. Like when he or she is away from his computer and Management Studio is open. In this post we will explore some more.

Again, this is for fun. Never do this on Production!

Fun with SQL part 1

27-04-2023 - Software Development

With great power comes great responsibilities. When you have access to a database, you can do great things. Things that can take users hours or even days, could be done within seconds. The other way around is also true: What users have put in a database can be lost forever within a second.

Wait ? Is this about Fun with SQL ? Yes it is, keep reading

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.

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.

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#.