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

SQL Wetonan Cycle

29-03-2024 - Software Development

In this Blogpost I want to combine some of the new SQL Server 2022 Functions with my other hobby: Indonesian Art and Culture. Now that might seem a bit weird combination, but I stumbled upon a nice case that makes a great example.

In this case I will take you along in the process of solving an ancient problem with modern technology. You might not use this exact case in daily practices, but I think it was both fun and educational to do the Wetonan Cycle.

SQL Coalesce

02-03-2024 - Software Development

The SQL IsNull() Function has a sibling that looks identical but that is certainly different. Although their use can be mixed, their behavior is different. And you should be aware of those differences, or you might end up with unexpected results.

So in this Post we are going to look at that sibling and we will look at the similarities and differences between the two.

New in SQL 2022 (2)

16-02-2024 - Software Development

A few weeks ago I talked about some nice new features in SQL Server 2022. As promissed I'm going to dive into some features that might be useful to you.

Since these features are new in the 2022 version, be sure to use that version. And make sure you use the most recent version since some of these features were not included in the pre-release version.

Try Catch in SQL

09-02-2024 - Software Development

When a Query fails in SQL, unexpected or unwanted things might happen. That is, ifyou don't have any error handling in place to prevent that. And for that you can use a very useful function.

In this Post we are going to look at the Try/Catch function. And I will show you some examples of how to use it.

Offline, Detach, Drop

02-02-2024 - Software Development

So you want to get rid of a Database on your SQL Server that is no longer in use. As is often the case, there are different ways to do it.

In this Post I will talk about the different ways to do this and which to use for different scenarios. So you can decide which way is best for your situation.

Chartering BarCharts

24-01-2024 - Business & Management

In my Post about 3D PieCharts I talked about how they can make the same numbers look different. And since a good report should be about the numbers, not about nice visuals, I tend to avoid them whenever I can.

With BarCharts you can also manipulate how the numbers are viewed. But this is not a Post on how to manipulate your audience. It is about how you should be aware of this when creating or viewing reports.

SQL Date_Bucket()

19-01-2024 - Software Development

As promissed I would get back at you with the new SQL 2022 Functions once I had found a good use for it. Well, date_bucket() is such a Function that was not real clear to me at first glance. Not one that was specifically on my Bucketlist, so to say.

So in this post, let's see what's in the bucket, and what we can get out of it. It is a Function that takes a bit to master, but once you get the hang of it, it is pretty useful.

SQL IsNull Behavior

12-01-2024 - Software Development

Even with almost 25 years of experience in writing Queries, even I sometimes encounter things I cannot explain right away. So the make things work as intended, a bit of experimenting is necessary. But what is the approach to solve something that cannot be explained?

Recently I encountered a case like this that puzzled me for quite some time. In this post you can read about that issue and how I got it solved.

New in SQL 2022 (1)

28-12-2023 - Software Development

With SQL Server 2022 available since May 2023, it is time to dive into what is new. With every update, the SQL Server engine itself gets improved. But besides that, there are also some new and useful functions.

In this post we will have a look at what's new in SQL Server 2022. And we will check some examples of why and when these new features are useful.

Recursive CTE's

05-12-2023 - Software Development

One of the things you might encounter every once in a while is the need to diplay a hierarchy. Like Managers and Employees within a company, or a Project with Child Projects and even Grandchild Projects.

At first this might seem difficult, especially if you don't know how many levels deep to go. But luckily there is an easy way to do this, using Common Table Expressions, or CTE's for short.

SQL Implicit Conversion

14-09-2023 - Software Development

As a Database Consultant, many of the questions I get are about performance. Or better said: questions that are about queries that lack performance.

Of course there are many reasons why a query is running slow. But there is one thing that I see regularly and this one is easy to avoid. In this post we will have a look at that one and I'll explain why this is bad for performance.

SQL DateDiff

01-09-2023 - Software Development

With the DateDiff Function you can determine the interval between two given dates. Seems pretty simple, but there is more to it than you might think. How can such a simple Function be the cause of errors in a Query?

Turns out that the basic understanding of how this Function works is the root cause of the problem. So in this post we will solve the mysterious case of the DateDiff.

Do we have a Date?

04-08-2023 - Software Development

Working with Dates is easy when writing T-SQL Queries. But every now and then it can become a bit tricky. And when you don't fully understand what is going on, your Query might contain errors.

In this Post I want to get more into the details of the DateTime DataType. To help you understand why it sometimes might behave a little different than you expect. So, do we have a Date here?

An Unknown DB

28-07-2023 - Software Development

Every once in a while you will come across a Database that you have never seen before. Maybe a different ERP System, or even a Custom Made Database. If you are lucky, there is documentation or the Database Tables and Columns are self-explanatory.

But what if you are unlucky? How to start to explore the Database? That is what we will be looking at in this post.

SQL Functions

21-07-2023 - Software Development

When you are a bit familiar with SQL Queries, you came across SQL Functions most likely. Maybe without realising this yet. Did you ever use Replace(), GetDate(), NewID() of Format()? Those are just a few of the SQL Functions that are build in with SQL Server.

But did you know you can add your own SQL Functions as well? In this Post we will have a look at why and how you can add them.

SQL Shortcuts 4

14-07-2023 - Software Development

Back by popular demand. A collection of SQL Server tips that are by itself not enough for a single Post. But put together, it makes for a Post with some useful tips and tricks.

This time we will dive into some less known constraints, less known operators. And to top it of a little bit of information on locking problems. Let's see what we have today!

SQL NTile()

07-07-2023 - Software Development

Some of the common questions I get when building reports have to do with ranking. For example, show me a Top 10 list of the Customers with the most Revenue. If you are a bit familiar with SQL Queries, that is an easy one.

But what if a more complex ranking is required? Like split the Customers into 5 Categories based on Revenue and then rank the Categories. SQL has a great build in Function for that. Let's have a look at NTile.

SQL Varchar Length

30-06-2023 - Software Development

Someone asked me a question on SQL Columns about the ideal Varchar length. He had read that for diskspace it did not matter if you use varchar(10) or varchar(1000). So it doesn't matter what size you use. SQL will only store the value as is, so diskspace used is exactly the same. Logical conclusion is that using varchar(8000) from each and every column would be a smart way to avoid truncate errors.

And while what he read is absolutely true and the conclusion makes sense, there are several reasons not to do this. And here is why.

SQL Formatting

23-06-2023 - Software Development

Formatting data already in a Query is often not recommended. This should better be done in the application or reporting software you use to present the data. And to be honest, most of the times this is the best way to do it.

But as with many things, there are always some exceptions. So in this Post we are going to look at the different ways to format your data.

Dynamic SQL

16-06-2023 - Software Development

Sometimes your query inside a Stored Procedure or Function should be slightly different, based on an incoming Parameter. For example, based on the Parameter, the query should be executed against a different database. Or it should use a different Column Name.

Since you cannot predict the Parameter value, you cannot write the Query in advance. But there is a solution to that. Please enter: Dynamic SQL.

SQL Smarter 1

09-06-2023 - Software Development

Some software companies are still proud of the number of lines in their code. Some development managers even judge their developers productivity based on the number of lines they produce. I would say that is nonsense. Less lines of code is often better, unless more lines of code makes it easier to read.

If you work for a company that judges you by the number of lines of code, feel free to skip this one. It is all about smarter code and less lines.

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