Few words about: APPLY operator

APPLY operator is very useful feature that is not very common, so I would like to introduce you into one of the most powerful tools in TSQL.

So let’s begin by showing some theory about JOIN operators. INNER JOIN and OUTER JOINS (RIGHT,LEFT, FULL) are very common and they work in very similar manner, as you can see in the picture below, JOINS must have two sets that are predefined – query engine have to know about rows from both sides before it executes the query.

Image

APPLY operator doesn’t need to have predefined table from both sides! Image below can help you to understand how it works:

Image

Continue reading

Advertisements

Few words about: LAG and LEAD TSQL functions

In SQL SERVER 2012 we’ve got some new functions that can be useful in our daily work, today I would like to present my two favorite ones: LAG and LEAD.

LAG function is working only with OVER( ORDER BY) clause and access data from previous row. LEAD functions works in very similar way – it’s return data from next row. I hope next example will be good way to understand how it works.

Continue reading

Few words about SQL Server services

Today I would like to write few words about SQL Servier Services. This topic is quite easy but some people doesn’t know basic things about services. We can manage services with two simple administration tools:

  • Windows Services
  • SQL Server Configuration Manager

Microsoft recommend to use second option – Configuration Manager (CM) – and if we can we should use this tool but not in every situation (as I will show lately).

Continue reading

Few words about: TSQL analytical functions

Transact-SQL give us possibility to automatically create aggregates based on a query. In this article I will show shortly how to use ROLLUP,CUBE and GROUPING SETS.

ROLLUP

ROLLUP is a part of GROUP BY clause and we use it like this

SELECT a,b,c, sum(d)

FROM Table_name

GROUP BY ROLLUP(a,b,c)

ROLLUP calculate sum of d attribute of few levels: ABC,AB,A and additionally sum of everything.

Continue reading