T-SQL Ninja #27

Temporary Objects

What are temporary objects? Basically SQL Server allows you to create temporary objects that you can work with. This is convenient because you don’t necessarily want to persist every intermediate result or help table in your database when working with large amounts of data. Temporary means that the tables are stored in the tempdb and are automatically […]

T-SQL Ninja #26

Sequence

Week 26: SEQUENCE What are SEQUENCEs? One of the most important SEQUENCEs we already introduced to you in the last Randori. In general, a SEQUENCE object is a generalization of the IDENTITY attribute. While the IDENTITY attribute ensures that every new row is assigned a new number, the SEQUENCE object is an object that creates rows of numbers. Unlike the IDENTITY attribute, it is not bound […]

T-SQL Ninja #25

IDENTITY

What is IDENTITY? IDENTITY is a property for columns in a table. It can be used to generate keys, i.e. unique, consecutive values in a column. The SQL Server guarantees that several parallel running transactions receive different values for the column. How can you work with IDENTITY? The IDENTITY property is passed two values, the SEED, which is the […]

T-SQL Ninja #24

SELECT TOP 0 INTO

SELECT TOP 0 INTO Occasionally you may want to create an empty table to store the data from a query. Of course, you can now check the metadata of all columns in the query from all source tables and write a DDL statement. But this can be quite complicated with queries with multiple joins and […]

T-SQL Ninja #23

TRIGGER

What are TRIGGERS? Now, you’ve heard many times in the Dojo that SQL is always effective when operating on records, and that SQL is a language that allows set operations. That’s why the possibilities for flow control in SQL are rather sparse compared to other programming languages, some of which you have already got to […]

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 […]