T-SQL Ninja #49

CHOOSE

What is CHOOSE? CHOOSE is certainly one of the less known functions in SQL Server, but this function is wrongly considered a shadowy existence, because it can make many annoying queries much easier. You can often use CHOOSE to simplify long, complex CASE WHEN statements, namely whenever you need to select an element from a list on the basis of […]

T-SQL Ninja #48

DATEDIFF

What does DATEDIFF do? Today we’ll look at one of the most well-known and really frequently used functions in SQL Server: DATEDIFF. The DATEDIFF function belongs to the functions for time objects, with it you can calculate the time between two datetime timestamps. You are free to specify the interval in years, months, days, hours, minutes, seconds, milliseconds […]

T-SQL Ninja #47

COMPRESS

What is COMPRESS? If you have a large amount of data in a database that you no longer need, such as deleted records that you want to archive but are no longer used in your application, then it would be nice if this data was stored in compressed form. How you can achieve this with […]

T-SQL Ninja #46

MASKED WITH FUNCTION

What does MASKED WITH FUNCTION do? Let’s assume that you want to hide the real names of your ninjas from the enemy (as you should, of course). Let’s further assume that you want to know them and store them in a database. I know that this assumption becomes slightly abstruse here, as of course every […]

T-SQL Ninja #45

BULK INSERT

What’s a BULK INSERT? If you want to get larger amounts of data into a database, the wonder weapon in the SQL Server environment is of course SQL Server Integration Services. If you want to do this for a SQL database in the cloud, you don’t have an SSIS engine available for the time being, […]

T-SQL Ninja #44

MAXDOP

What’s MAXDOP? You’ve probably noticed when viewing execution plans in SQL Server that there are often two small arrows on a yellow background (in SQL Server Management Studio) and the operation “Parallelism (Gather Streams)”. What this means, simply put, is that at some points SQL Server decides to execute certain operations in parallel in multiple […]

T-SQL Ninja #43

SP_RENAME

What is SP_RENAME? If you have objects in your database that you want to rename, you can right-click on the object in SQL Server Management Studio to rename it. However, if you want to make the renaming process a little more controlled and preferably distributable, then you can’t avoid scripting the process and don’t need […]

T-SQL Ninja #42

AT TIME ZONE

What is AT TIME ZONE? Who doesn’t know it? You’re in Seeheim Jugendheim and want to call your ninja colleague in Kyoto, but you don’t know what time it is at his place. Of course you can google the local time in Kyoto, but you can also query the time in Tokyo Standard Time in […]

T-SQL Ninja #41

STRING_ESCAPE

What is STRING_ESCAPE? You have already learned about some functions for manipulating strings. STRING_ESCAPE also fits into this series. The STRING_ESCAPE function is a function that replaces special characters in a string so that this string can be used elsewhere. A classic example of string escaping is that to avoid SQL injection attacks, you should not pass strings directly […]

T-SQL Ninja #40

STRING_SPLIT

What does STRING_SPLIT do? Over the weeks you’ve gotten to know some possibilities to create comma-separated lists in SQL, besides STUFF in combination with SELECT FOR XML and STRING_AGG also CONCAT_WS. This week we want to go the opposite way and split a string containing comma-separated values into its constituent parts. Since SQL Server 2016 there is a way to do this […]