T-SQL Ninja #12

RANK

What does RANK do? Like the ROW_NUMBER function introduced last week, RANK is also a window function, i.e. a function that processes the data sorted in so-called “windows” or “partitions”. You define the sorting and the partitions by using the keywords ORDER BY and PARTITION BY after the function call. The RANK function then numbers all the rows in the result set, starting at 1 […]

T-SQL Ninja #11

ROW_NUMBER

What does ROW_NUMBER do? The ROW_NUMBER function is a window function, i.e. a function that processes the data sorted in so-called “windows” or “partitions”. You define the sorting and the partitions by using the keywords ORDER BY and PARTITION BY after the function call. The ROW_NUMBER function then numbers all rows in the result set, […]

T-SQL Ninja #10

STATISTICS TIME

What is tSTATISTICS TIME? Last week you got to know STATISTICS IO, a tool that tells you how much data a query needs to read from disk to answer a query. How does STATISTICS TIME differ from STATISTICS IO? While STATISTICS IO logs disk accesses, STATISTICS TIME returns the CPU time required for a query. That is, the time that was used to […]

T-SQL Ninja #09

STATISTICS IO

What is STATISTICS IO? “Yoo-hoo, ninja, I have this query, it’s really slow, it wasn’t like that yesterday, for sure”. With queries like this one every ninja will be surprised sooner or later and this proves that the other users have certain ninja skills, too, because you are certainly never prepared for this situation. So […]

T-SQL Ninja #08

sp_MSforeachtable

What is sp_MSforeachtable? The stored procedure sp_MSforeachtable is one of the system procedures defined in SQL Server. You can find it when you connect to a server with Management Studio or Data Studio and then look under “System Databases” in the “master” database under “Programmability” in the “System Stored Procedures”. There you will find a whole bunch […]

T-SQL Ninja #07

OUTER APPLY

What is OUTER APPLY? OUTER APPLY is a means of first formulating subqueries, which are executed for each line in the result, and is then joined to the result. This is, of course, a means to be used with extreme caution, because since an OUTER APPLY is executed for every row in the result, even a cheap (sub-)query […]

T-SQL Ninja #06

OFFSET FETCH

What is OFFSET FETCH? Almost everyone limits their query results by adding the TOP keyword. But what if my application doesn’t want to query the first 1000 records, but always tens of blocks, for example to display them sorted in pages? Since SQL Server 2012 there is OFFSET FETCH for this purpose. How can you use OFFSET FETCH? To […]

T-SQL Ninja #05

Optimizer Hints

What are Optimizer Hints? Normally, when you submit a query, the SQL Server will try to interpret the query and find the best possible execution plan for your query based on statistics, among other things. Normally this works very well. Occasionally, however, there are cases where the SQL Server miscalculates, either because of missing or […]

T-SQL Ninja #04

SELECT FOR JSON

What is SELECT FOR JSON? In almost every new Web application, JSON is the file format for data exchange. JSON is readable, clearer than XML, for example, and has a smaller data to noise ratio, which is why it is becoming increasingly common in the Web world. JSON stands for JavaScript Object Notation. Now – […]

T-SQL Ninja #03

TRY CATCH

What is try-catch? Those of you who write code know try–catch blocks for exception handling. Constructs like this one, by which you can react to exceptions instead of simply running on errors and aborting the code, are common in the programming world, but not provided for in the SQL standard. What can you use try-catch for? Try–catch blocks […]