PostList

SQL 4 begginers - NULL

SQL 4 begginers - NULL

When talking about values in database world there are three possible states. The three states are: there is a value, there is not a value (empty) and the value is NULL (unknown). Let's find out more about the NULL value....

Read More
SQL 4 begginers - JOIN

SQL 4 begginers - JOIN

Let’s find out what is a JOIN and how to use it. The main difference between JOIN and UNION operator is that union combines results vertically, while the JOIN combines result's horizontally. There are different types of JOIN-s depending on the business need and we will discuss about them. Since the databases should be normalized it is essential to use joins to get details for some records....

Read More
SQL 4 begginers - UNION

SQL 4 begginers - UNION

Let’s find out what is a UNION and what’s the difference between the UNION and the UNION ALL operators. The main difference between UNION and JOIN operator is that union combines results vertically. In simple words, the UNION operator returns results from two or more queries in one result set....

Read More
MS SQL Advice - COUNT(*) vs EXISTS

MS SQL Advice - COUNT(*) vs EXISTS

If you want to check for data existence in a table (e.g. if there are invoices on a concrete date) you could use COUNT(*) or the EXISTS statement. I found various theories on the internet and even in some SQL books what is the best approach, so I decided to test this by myself (spoiler alert: the books are on the side of using EXISTS)....

Read More
MS SQL Advice - Use constraints

MS SQL Advice - Use constraints

Why you should use constraints on your tables? Except the fact that you should use constraints to check your data and the integrity of them (e.g. only allow inserting of numeric value between 1 and 5 for storing vote value) you could use them also for better query executions and get some performance boost. Let’s find how....

Read More
HASHBYTES - Hashing in MS SQL Server

HASHBYTES - Hashing in MS SQL Server

In short words, hashing is a process of generating a value or values from a string of text using a mathematical function. Let's see the usage of the MS SQL function HASHBYTES witch purpose is to hash values. MS SQL function HASHBYTES was introduced in MS SQL version 2005 supporting MD2, MD4, MD5, SHA, SHA1 hashing algorithms. From MS SQL server version 2012 additionally the SHA2_256, SHA2_512 algorithms were introduced. In this article we will discuss about hashing, what's new from SQL 2016 and see some usage examples....

Read More
Historical data with MS SQL System-Versioned (Temporal) Tables

Historical data with MS SQL System-Versioned (Temporal) Tables

Before the existence of system-versioned temporal tables, tracking changes in a database table forced us to implement some custom solutions based on triggers, stored procedures etc. System-versioned temporal tables offer us the possibility to keep a full history of data changes and allowing us easy point in time analysis. Using this solution, we can easily time travel through the data and see what the exact state of the data in a point of time was....

Read More
Modify JSON data using JSON_MODIFY

Modify JSON data using JSON_MODIFY

After discussing about how MS SQL Server can help us to format MS SQL data in JSON format and how to read JSON data in MS SQL Server, in this article we will see how we can modify JSON data in MS SQL Server using JSON_MODIFY function....

Read More
MS SQL JSON functions, tips & tricks

MS SQL JSON functions, tips & tricks

After discussing about how MS SQL Server can help us to format MS SQL data in JSON format and how to read JSON data in MS SQL Server, in this article we will quickly pass over some additional functions, tips and tricks and possibilities handling JSON data in MS SQL Server....

Read More