T-SQL Ninja #22

CASE WHEN

What is CASE WHEN? Last week you already got to know the IIF-statement and in the course of this you also saw CASE WHEN-statements directly. Today we want to take a closer look at the CASE WHEN statements. Like IIF, CASE WHEN is a construct from the realm of logic functions that allow you to react to certain values. What can you […]

T-SQL Ninja #21

IIF

What is IIF? The function ‘IIF’ is a logical function that is passed three arguments. It evaluates the first argument to a boolean expression and returns the second argument if the first argument is evaluated to ‘TRUE’, otherwise it returns the third. This is similar to the ternary notation in programming languages such as C#, […]

T-SQL Ninja #20

CURSOR

What are CURSORs? In the last two weeks you had learned about the WHILE statement in the Dojo and used it to delete batches of lines. We had given you the warning that the SQL Server (like almost every relational database engine) is primarily designed to operate with data sets, i.e. with multiple rows. But what if […]

T-SQL Ninja #19

Batches

What are batches? Sometimes you may need to do operations on very large tables. In these cases, due to the transactional behavior of your database, there may be no space left in the TempDB. This is because relational databases follow the ACID principles that operations should be atomic, so for example a `DELETE’ statement will […]

T-SQL Ninja #18

WHILE

What’s WHILE? Unlike Data Definition Language statements for creating views, tables or indexes, query statements for inserting and retrieving data into these structures, the WHILE keyword is part of the flow control. This means that this keyword can be used in SQL statements to control processes and execute certain queries multiple times. How can you use WHILE […]

T-SQL Ninja #17

Running Totals

What are Running Totals? A Running Total is a scenario in which each row contains the sum of the values of a specific column of the previous rows. A good example of this is when points from different games are added up. Of course, it is important for a running total that the rows are […]

T-SQL Ninja #16

UPDATE FROM

What does UPDATE FROM do? If you want to UPDATE values in tables, a script often looks like this: you assign a new value (which can be derived from other values) to a column. But how does this work if the other values come from a different table? Then you need an UPDATE FROM. How can you work […]

T-SQL Ninja #15

MERGE

What’s MERGE? You could almost write a book about merge statements. But as real ninjas, we want to use the katana to split the subject cleanly. A MERGE statement performs insert, delete or update operations in a table within a statement. It can be used, for example, to synchronize tables, i.e. to insert the contents […]

T-SQL Ninja #14

GROUP BY … HAVING

What’s GROUP BY? GROUP BY and HAVING belong to the absolute basics of SQL syntax, but every ninja should also know the basics and not only be familiar with Jutsu, so let’s have a look at the construct. A query that aggregates data, for example by making a sum, doesn’t always have to make the sum over all […]

T-SQL Ninja #13

DENSE_RANK

What is DENSE_RANK? In the last two weeks we have already given you “joy” by numbering rows in the result set with the ROW_NUMBER and RANK functions. We want to continue with this today. The DENSE_RANK function is, as the name suggests, closely related to the RANK function from last week. So what is the difference? It’s relatively simple: while the RANK function skips a […]