New In SQL Server 2012: OFFSET FETCH

As I was saying in a previous post, I started learning for the SQL Server 2012 certifications. I decided to start with reading Training Kit (Exam 70-461): Querying Microsoft® SQL Server® 2012 , although I was sure that there will not be so many things that I don't already know. I have to say that the book is great and is a lot better than the 2008 or 2005 ones.

The first thing that I didn't knew and that I have run into is the OFFSET FETCH clause. This clause can only be used with order by. It's role is to make it easier to select the first n rows after skipping x rows, or just select all rows after skipping x rows from a set.

For example, if you want to select all rows from an ordered set, but skip the first 10 rows, you would use
SELECT * FROM #employee ORDER BY id OFFSET 10 ROWS

If you need only 5 rows from an ordered result set, but you want to skip the first 10 rows, you can use this :
SELECT * FROM #employee ORDER BY id OFFSET 10 ROWS FETCH NEXT 5 ROWS ONLY

Concatenate Values From One Column Based on Common Value From Another Column

The goal of this demo code is to concatenate the names of all employees from each department and to display the department and the concatenated value.

The code is straight forward. First you use a CTE to apply a row_number, partitioning by department. The second step is to create a recursive CTE that concatenates the names starting from the records with the row_num equal to 1. This way the names will be added one by one until the maximum row_num for each department is reached.

For this demo I will use the #employee table. The code to create and populate this table is available here.

This is the content of the #Employee table:

id	Name	IsManager	SuperiorID	Department
1	Stan Palmer	1	NULL	NULL
2	John Sander	1	1	First level management
3	Jan Claud	0	2	Second level management
4	Christine Lehman	0	2	Second level management
5	Nadia Seres	1	1	Sales
6	Neil Armstrong	1	5	Sales
7	Paolo Williams	0	6	Sales
8	Robbie Diamond	0	6	Sales
9	Zakk Steward	1	5	Sales
10	Duffy Wylde	0	9	Sales
11	Rod Chapman	0	9	Sales
12	Tracy Durst	1	5	Sales
13	Iywoka Cohen	1	2	IT
14	Leonard Nutini	1	1	Second level management
15	CEO	1	NULL	NULL

Here is the code that concatenates the names based on the department :

;with CTE1 as

(     select

            row_number() over(Partition by Department order by IsManager) as row_num

            ,Department

            ,Name from #employee)

,CTE2 as

(

      select row_num,Department, Convert(nvarchar(1000),Name) as Name, convert(varchar(250),Name) as InitialName from CTE1 where row_num= 1

      union all

      select a.row_num,a.Department, Convert(nvarchar(1000),a.Name + ', ' +b.Name) as Name, convert(varchar(250),a.Name) as InitialName

            from        CTE1 a

            inner join  CTE2 b

                  on ((a.Department = b.Department) or (a.Department is null and b.Department is null))

                  and a.row_num  = b.row_num+1

)

--select * from CTE2

Select            ct.Department

                  ,ct.Name

from        Cte2                                            ct

inner join (select      Department

                              ,max(row_num) as row_num

                  from Cte2

                  group by Department

                  )                                                     cb

      on (cb.Department = ct.Department  or  (cb.Department is null and ct.Department is null))

            and cb.row_num = ct.row_num

This is the result :

Department	Name
First level management	John Sander
IT	Iywoka Cohen
Second level management	Leonard Nutini, Christine Lehman, Jan Claud
Sales	Tracy Durst, Neil Armstrong, Nadia Seres, Zakk Steward, Robbie Diamond, Paolo Williams, Rod Chapman, Duffy Wylde
NULL	CEO, Stan Palmer

This is what the CTE2 contains :

row_num	Department	Name
1	NULL	Stan Palmer
1	First level management	John Sander
1	IT	Iywoka Cohen
1	Sales	Duffy Wylde
1	Second level management	Jan Claud
2	Second level management	Christine Lehman, Jan Claud
3	Second level management	Leonard Nutini, Christine Lehman, Jan Claud
2	Sales	Rod Chapman, Duffy Wylde
3	Sales	Paolo Williams, Rod Chapman, Duffy Wylde
4	Sales	Robbie Diamond, Paolo Williams, Rod Chapman, Duffy Wylde
5	Sales	Zakk Steward, Robbie Diamond, Paolo Williams, Rod Chapman, Duffy Wylde
6	Sales	Nadia Seres, Zakk Steward, Robbie Diamond, Paolo Williams, Rod Chapman , Duffy Wylde
7	Sales	Neil Armstrong, Nadia Seres, Zakk Steward, Robbie Diamond, Paolo Williams , Rod Chapman, Duffy Wylde
8	Sales	Tracy Durst, Neil Armstrong, Nadia Seres, Zakk Steward, Robbie Diamond , Paolo Williams, Rod Chapman, Duffy Wylde
2	NULL	CEO, Stan Palmer

Demo Tables: Create and Populate #employee Table

IF OBJECT_ID('tempdb..#employee',N'U') is not null

Drop Table #employee

create table #employee

(

id int       identity(1,1)    not null

,Name        varchar(250)     Not null

,IsManager   bit              not null

,SuperiorID  int              null

,Department  varchar(25)      null       

)

insert into #employee(Name,IsManager,SuperiorID,Department)  select 'Stan Palmer',1,NULL,NULL

insert into #employee(Name,IsManager,SuperiorID,Department)  select 'John Sander',1,1,'First level management'

insert into #employee(Name,IsManager,SuperiorID,Department)  select 'Jan Claud',0,2,'Second level management'

insert into #employee(Name,IsManager,SuperiorID,Department)  select 'Christine Lehman',0,2,'Second level management'

insert into #employee(Name,IsManager,SuperiorID,Department)  select 'Nadia Seres',1,1,'Sales'

insert into #employee(Name,IsManager,SuperiorID,Department)  select 'Neil Armstrong',1,5,'Sales'

insert into #employee(Name,IsManager,SuperiorID,Department)  select 'Paolo Williams',0,6,'Sales'

insert into #employee(Name,IsManager,SuperiorID,Department)  select 'Robbie Diamond',0,6,'Sales'

insert into #employee(Name,IsManager,SuperiorID,Department)  select 'Zakk Steward',1,5,'Sales'

insert into #employee(Name,IsManager,SuperiorID,Department)  select 'Duffy Wylde',0,9,'Sales'

insert into #employee(Name,IsManager,SuperiorID,Department)  select 'Rod Chapman',0,9,'Sales'

insert into #employee(Name,IsManager,SuperiorID,Department)  select 'Tracy Durst',1,5,'Sales'

insert into #employee(Name,IsManager,SuperiorID,Department)  select 'Iywoka Cohen',1,2,'IT'

insert into #employee(Name,IsManager,SuperiorID,Department)  select 'Leonard Nutini',1,1,'Second level management'

insert into #employee(Name,IsManager,SuperiorID,Department)  select 'CEO',1,NULL,NULL

select * from #employee

Out of memory happened while accessing a critical resource.

       These days I run into the error "Out of memory happened while accessing a critical resource.System.Threading. ThreadAbortException: Thread was being aborted." while running a SSIS package in a job. There were no changes in the package ant it was running successfully on other environments.
       The error was appearing on a SQL Server 2005, Enterprise Edition, 32 -bit instance. The weird thing was that there was plenty of memory available, both RAM and hard drive.
       After some searching I have discovered that the error appears because of the insufficient Virtual Address Space(VAS) that is reserved by SQL Server when it starts. This memory, also known as "MemToLeave", is used by the external components called by SQL Server, like .NET CLR, Linked Servers and extended stored procedures. So the next step was to find out how I can increase the MemToLeave. One of the options would be using the -g startup parameter, but I have found on multiple sources that is not such a great idea.

Fortunately I have found a great great article on this topic, Understanding the VAS Reservation (aka MemToLeave) in SQL Server, which explains, among other things, how the SQL determines the size of the reserved VAS.
The formula is : (MaxWorkerThreads * StackSize) + DefautReservationSize
The StackSize is 512KB for a 32-bit system and 2048KB (2MB) for a 64-bit system.

New Goal : The SQL Server 2012 Certifications

Today I have decided to continue learning and upgrade my certifications to 2012. The new target is MCSE: Data Platform and MCSE: Business Intelligence
Right now I am MCITP on Database Developer 2008 (Exams 70-433 - TS: Microsoft SQL Server 2008, Database Development  and 70-451 - PRO: Designing Database Solutions and Data Access Using Microsoft SQL Server 2008) and MCTS on Implementation and Maintanance (Exam 70-432: Microsoft SQL Server 2008, Implementation and Maintenance).
Trying to find which are my options to achieve this goal, I have found that :
1.    for the first step, becoming MCSA , I should pass 2 exams :
              - Exam 70-457 Transition your MCTS on SQL Server 2008 to MCSA: SQL Server 2012 -Part 1
              - Exam 70-458 Transition your MCTS on SQL Server 2008 to MCSA: SQL Server 2012 -Part 2
2.    for the second step, becoming MCSE: Data Platform, I should pass 1 exam :
              - Exam 70-459 Transition your MCITP on SQL Server 2008 to MCSE: Data Platform
3.   for the third step, becoming a MCSE : Business Intelligence, I should pass two exams:
              - Exam 70-466 - Implementing Data Models and Reports with Microsoft SQL Server 2012
              - Exam 70-467 - Designing Business Intelligence Solutions with Microsoft SQL Server 2012

So, there is a lot to learn. Fortunately at my new workplace there are plenty of challenges and a great environment for developing my skills. All seems to be aligned in the right direction.

The target for the first exam : the end of May.

I am sure that during this learning I will run into tons of  interesting things and , for sure, most of them will get on the blog.