SQL Server 2014 – In Memory OLTP (Project Hekaton)

Have you been wondering what SQL Server 2014 has in store for you? Memory optimized tables are in, slow running transactions are out! With multi-version optimistic concurrency, a completely new data structure optimized for memory and a brand new set of memory optimized indexes in the form of Hash indexes and Range indexes, SQL Server 2014 comes with a bang.

With the introduction of In Memory OLTP, SQL Server 2014 also introduces a new type of compiled stored procedures. These procedures are literally compiled to machine code and run in memory accessing memory optimized tables in an efficient manner.

In-memory OLTP SQL Server 2014

In-memory OLTP Architecture

Where SQL Server really excels in its delivery of Memory optimized tables is that it really doesn’t change much of how you’re used to interacting with SQL Server. For the most part, the introduction of memory optimized tables can a seamless operation to your applications. You can be the hero without causing too much pain. There are a few restrictions you’ll need to take care of but as you can see from the image below, T-SQL and client applications work as expected and see your tables just as any other table in the system.

Check out our new Pluralsight course on Optimizing OLTP and Data Warehousing with SQL Server 2014 to learn more about how you can utilize memory optimized tables for OLTP and Data Warehousing type applications. The course covers design principles, index types, examples of loading data, pitfalls and best practices along with how to use natively-compiled stored procedures. In the next post we’ll discuss how Clustered ColumnStore indexes in SQL Server 2014 can help with your Data Warehousing workloads. Also part of the same course. Happy coding!

 

 

Posted in SQL Server by Ahmad Alkilani. No Comments

Real-Time Insights & Complex Event Processing – SQL Server StreamInsight & the Real-Time Web

Thanks for everyone who attended the Malibu SQL Server User Group

The slides are here. I will be posting some of the source code and video if the quality is descent enough.

Presentation:
Real-Time Insights & Complex Event Processing – SQL Server StreamInsight & the Real-Time Web

Discover the power of SQL Server StreamInsight for processing continuously arriving data and providing analytics and insight at near-real-time. Real-time operational intelligence provides a real-time view of business operations. Imagine being able to recommend or adjust the content of your website based on what you know about your user, in real-time. In this session you’ll learn the basics of StreamInsight’s architecture and we’ll walk through an example of how it can be applied. If time permits, we’ll also look at an application of StreamInsight with other cutting-edge Microsoft technologies that empower the Real-Time Web, namely, SignalR.


Posted in SQL Server by Ahmad Alkilani. No Comments

Determine page number in SSRS 2005/2008

This method takes advantage of SQL Server’s new ranking functions and can be used in your SQL Reports to determine the page number in the body of a report and also possibly act upon that information to hide/display columns.

Use this method if:

  1. You have a preset limit of how many records display on a page
  2. You have grouping and each group shows on its own page
  3. You can accurately calculate the number of records on each page
  4. You have a dynamic range of columns and/or rows and you need to repeat rows when the page cannot accommodate for all  your columns. When your columns move to the next printable page, you want your rows to show too. I’ll tackle this in another article.

 

The solution:

Note: You can download the code for the article from the attachments at the end of the article

I’ll give a quick overview of the ranking functions for clarity, for a more detailed dive check out my article on Ranking Functions.

If you don’t need the review, skip to the good part:

  • Row_Numer() OVER (ORDER BY x) : Returns the row number of the result set based on the order provided in the order by clause.

 

  • Rank() OVER (ORDER BY x) : Similar to Row_Number() only Rank determines the position, or lack for a better word, ranking of each row based on the Order By clause. Rank is usually used with the Partition clause to cluster your result sets. Rank also skips numbers, if 2 or more records tie in value, they will receive the same rank. The following rank would be 1+ the total number of records in the same partition so for example (1, 2, 2, 2, 5, 6)

 

  • Dense_Rank() OVER (ORDER BY x) : Same as Rank() only guarantees consecutive integers (No skipping) (1, 2, 2, 2, 2, 3). If a tie occurs, it will sort arbitrarily (based on the execution plan and indexes used) and continue.

 

  • NTile()  OVER (ORDER BY x) : Used to distribute the rows in an ordered partition into x number of groups. Each row receives the group number it belongs to.

 

So I mentioned there were 3 scenarios where this could work for you. I’ll give an example on how to do each and let your creativity do the rest. This will server as our sample sales table:

employee:

name  sales_amount  group_name  Scenario #1  Scenario #2  Scenario #3
 A  10  A1  1  1  1
 B  20  A1  1  1  1
 C  30  A1  2  1  1
 D  30  A2  2  2  1
 E  40  A3  3  3  2
 F  40  A3  3  3  2
 G  50  A4  4  4  2
 H  60  A5  4  5  2

 

Scenario #1:

You have a preset # records to be places on each page:
Here’s what your SQL Query would look like:

DECLARE @rows_per_page INT
SET @rows_per_page = 2
SELECT
 employee.name
 ,page = (Row_Number() OVER ( ORDER BY employee.sales_amount ) / @rows_per_page)
FROM
 employee

In SSRS you can then create a parent group in your tablix (SSRS 2008) and set the option to break “Between each instance of a group”. (Page Breaks tab in SSRS 2008)

Scenario #2:

You have grouping and each group shows on its own page

SELECT
 employee.name
 ,page = Dense_Rank() OVER ( ORDER BY employee.group_name )
FROM
 employee

On your report in SSRS, on the group properties for the table/matrix/tablix set the option to break “Between each instance of a group”. (Page Breaks tab in SSRS 2008)

Scenario #3:

You can accurately calculate the number of records on each page
If you can accurately calculate the number of records on each page, it becomes an easy task to use a similar methods as in #1. In the example, the number was  4 records per page.

More Examples:
Reporting Services Paging By Group

Let’s say you wanted to number pages and reset page numbering for each group of data you have. You also want to display the total number of pages as in Page x of y per group. This example was inspired by this post
http://darkthread.blogspot.com/2006/12/kb-reporting-service-paging-by-group.html

Assuming you fall under scenario #1 where you know the number of records per page (2 in our example)

  • We first need to figure out the total number of pages per group (count of records per group/records per page)

 

SELECT
 group_name
 ,total_number_of_pages_per_group = CEILING(COUNT (*) / @rows_per_page)
 FROM
 employee
 GROUP BY
 group_name
  • We then use this information to join it with our original query. I’m using a CTE (Common table expression) for simplicity but you can just as easily have made this an inner query

 

DECLARE @rows_per_page INT
SET @rows_per_page = 2

;WITH total_pages -- CTE
AS
(
 SELECT
 group_name
 ,total_number_of_pages_per_group = CEILING(COUNT (*) / @rows_per_page)
 FROM
 employee
 GROUP BY
 group_name
)
SELECT
 employee.name
 ,employee.group_name
 ,page = (Row_Number() OVER (PARTITION BY employee.group_name ORDER BY employee.sales_amount ) / @rows_per_page) + 1 -- page x
 ,total_pages.total_number_of_pages_per_group -- of y
FROM
 employee
INNER JOIN -- Join to get page x of y
 total_pages ON total_pages.group_name = employee.group_name

The results

name group_name page    total_number_of_pages_per_group
A         A1                 1     2
B         A1                 2     2
C         A1                 2     2
D         A2                 1     3
E         A2                 2     3
F         A2                 2     3
G         A2                 3     3
H         A2                 3     3
I         A3                 1     1
J         A3                 2     1

Note: You can download the code for the article from the attachments

Tags: , ,
Posted in Reporting Services (SSRS) by Ahmad Alkilani. 3 Comments

FTP Task ++

Download FTP Task++ FTP Task++ is a free SSIS Task that among other functionality gives you SFTP for SSIS. FTP Task ++ created to overcome both limitations of Microsoft’s stock FTP Task and erroneous behavior specifically with (Unix) non-Windows FTP Servers.

What makes FTP Task ++ so special?

  • Native to SSIS
  • Secure FTP (SFTP) and FTP
  • Delete from non-Windows FTP Servers (Unix)
  • Wildcard support (*.pdf, log*.*, download??.txt etc..)
  • Combine operations like (Download and delete remote files, overwrite destination) in 1 step instead of using multiple FTP tasks
  • Support for SSIS Logging providers
  • Reports FTP Progress
  • Reports Sent Commands
  • Auto Resume on Failure
  • Graphical User Interface
  • Did we mention it was completely free!
Reporting features support display in the command output window and the SSIS progress window and support standard SSIS log providers.

I’ve read enough, take me to the download page

How to use

FTP Task++ is used in the control flow as any other stock control flow task.

Double click on the task to bring up its properties and customize its behavior.

Fig 1: Connection Tab

Fig 2: Operation Tab

Installation

To install the component, run the installer (MSI)

 

Fig 3: Installation

I’ve read enough, take me to the download page

Setup a development environment to use the component.

To add it to your toolbox in BIDS, right click on the toolbox and click on “Choose Items”

Fig 4: Visual Studio Toolbox, adding FTP Task++

From there, click on the “SSIS Control Flow Items” tab and select FTP Task++ from the list of components.

Fig 5: Visual Studio SSIS Control Flow Items, adding FTP Task++

FTP Task++ will appear in the toolbox when in the control flow tab.

 

Tags: , , ,
Posted in Integration Services (SSIS) by Ahmad Alkilani. 29 Comments

Row Count++

RowCount++ is a free SSIS Data Flow component used to natively log record counts processed. RowCount++ comes in handy because logging record counts is not available out of the box. Microsoft’s stock Row Count component in SSIS allows you to save row counts in a variable but doesn’t have the ability to read custom data from variables and persist it using any of the SSIS logging providers.

The component fires an OnRowCount event that can be consumed by the SSIS engine. When the component finishes counting records from all input buffers it checks if the OnRowCount  event was selected, which is the default behavior. The event is fired and can be captured by SSIS. You can capture the event using any of the log providers as with the OnError event.

I’ve read enough, take me to the download

How to use Row Count++  in a sample data flow

1. Drop RowCount++ in-between you source and destination.

Figure 1: RowCount++ in SSIS Package

2. Use SSIS Logging to capture the OnRowCount event just as you would for a regular OnError event.

Figure 2: RowCount++ SSIS Configuration

I’ve read enough, take me to the download

To install the component, run the installer project (MSI).

How to setup a development environment

1. To add it to your toolbox in BIDS (Visual Studio), right click on the toolbox and click on “Choose Items”

Figure 3: RowCount++ in Visual Studio Toolbox

2. From there, click on the “SSIS Data Flow Items” tab and select RowCount++ from the list of components.

Figure 4: RowCount++ Visual Studio Toolbox: Add Items

3. RowCount++ will appear in the toolbox when in the data flow tab

Download RowCount++ for SSIS

Tags: ,
Posted in Integration Services (SSIS) by Ahmad Alkilani. No Comments

SQL Server Ranking Functions Row_Number(), Rank(), Dense_Rank() and NTIle()

SQL Server 2005 introduced new ranking functions. This article is tailored as an introduction to these functions, difference between them and when to use each and a few examples.

Ranking What:

The new ranking functions are new internal functions to SQL Server 2005/2008.

From MSDN “Ranking functions return a ranking value for each row in a partition. Depending on the function that is used, some rows might receive the same value as other rows. Ranking functions are nondeterministic.”

In simple terms, ranking functions allow you to sequentially number your result set. Your result set can be partitioned so the numbering essentially resets for each partition for example you can get the sales rank of employees partitioned by their department, or manager etc..

What’s worth mentioning is that ranking functions are non-deterministic so you cannot use them in something like an indexed view.

Sample Table:

This will serve as our sample table for all our examples. You can download the script to create the table and populate it with the data used in the examples.

sales_employee

name territory  sales_amount
 A  X  100
 B  X  200
 C  X  200
 D  X  300
 E  X  400
 F  Y  300
 G  Y  300
 H  Y  500
 I  Y  600
 J  Z  200
 K  Z  700

 

Syntax and Examples:

ROW_NUMBER () OVER ( [ <partition_by_clause> ] <order_by_clause> )

Returns the row number of the result set for each row in a partition based on the order provided in the order by clause.

RANK () OVER ( [ <partition_by_clause> ] <order_by_clause> )

Similar to Row_Number() only Rank determines the position, or lack for a better word, ranking of each row based on the Order By clause. Rank is usually used with the Partition clause to cluster your result sets. Rank also skips numbers, if 2 or more records tie in value, they will receive the same rank. The following rank would 1+ the total number of records in the same partition so for example (1,2,2,2,5,6)

DENSE_RANK () OVER ( [ <partition_by_clause> ] <order_by_clause> )

Same as Rank() only guarantees consecutive integers (No skipping) (1, 2, 2, 2, 2, 3). If a tie occurs, it will sort arbitrarily (based on the execution plan and indexes used) and continue.

NTILE (integer_expression) OVER ( [ <partition_by_clause> ] <order_by_clause> )

Used to distribute the rows in an ordered partition into x number of groups. Each row receives the group number it belongs to.

Example: Simple ORDER BY clause

SELECT
       name
      ,territory
      ,ROW_NUMBER() OVER ( ORDER BY sales_amount ) AS [row_number]
      ,RANK() OVER ( ORDER BY sales_amount ) AS [rank]
      ,DENSE_RANK() OVER ( ORDER BY sales_amount ) AS [dense_rank]
      ,NTILE(4) OVER ( ORDER BY sales_amount ) AS [ntile]
FROM
      sales_employee

 

 name territory  sales_amount ROW_NUMBER  RANK DENSE_RANK  NTILE
 A  X  100  1  1  1  1
 B  X  200  2  2  2  1
 C  X  200  3  2  2  1
 D  X  300  5  5  3  2
 E  X  400  8  8  4  3
 F  Y  300  6  5  3  2
 G  Y  300  7  5  3  3
 H  Y  500  9  9  5  3
 I  Y  600  10  10  6  4
 J  Z  200  4  2  2  2
 K  Z  700  11  11  7  4

 

Example: Using PARTITION BY

SELECT
      name
     ,territory
     ,ROW_NUMBER() OVER (PARTITION BY territory ORDER BY sales_amount) AS [row_number]
     ,RANK() OVER (PARTITION BY territory ORDER BY sales_amount) AS [rank]
     ,DENSE_RANK() OVER (PARTITION BY territory ORDER BY sales_amount) AS [dense_rank]
     ,NTILE(4) OVER (PARTITION BY territory ORDER BY sales_amount) AS [ntile]
FROM
     sales_employee

 

 name territory  sales_amount ROW_NUMBER  RANK DENSE_RANK  NTILE
 A  X  100  1  1  1  1
 B  X  200  2  2  2  1
 C  X  200  3  2  2  2
 D  X  300  4  4  3  3
 E  X  400  5  5  4  4
 F  Y  300  1  1  1  1
 G  Y  300  2  1  1  2
 H  Y  500  3  3  2  3
 I  Y  600  4  4  3  4
 J  Z  200  1  1  1  1
 K  Z  700  2  2  2  2

 

Conclusion:

Ranking functions can come in very handy when devising groups or sequential numbering of your result sets. Ranking functions are particularly useful in reporting.

Related Articles:

SQL Server Modeling Services – Quadrant and M Overview

Last week I was part of an interesting “call” with Microsoft around SQL Server Modeling Services and what it had to offer. The purpose to get client feedback on the proposition and also provide us with some insight on what it was and where it was heading.

First let me clarify that SQL Server Modeling Services is in fact what used to be known as OSLO or the “repository”. 

What is M?

“M” is the language for EDM (Entity Data Model). Think of it as the textual service syntax for EDM. “M” replaces your XML file that describes your model. “M” is an open data language, anyone can implement it and It is part of SQL Server 2008 R2. 
What you can do in M:
  • Describe it in M (Schema and SQL)
    • You get your database
    • You get your middle tier classes as well as validation on your client tier
“M” is a way for you to logically describe your data models, get TSQL out one end and your classes out another.
As of the CTP for SQL Server 2008 R2, “M” remains to be the unofficial name. 
With the new RC release of SQL Server, “M”, when it compiles, it compiles the TSQL, If you go into Visual Studio and create an M file, it also generates the classes for you. 

What is Quadrant?

Quadrant is a developer tool for SQL Server. I like to think of Quadrant as the SQL Server Navigator. Quadrant has an infinite canvas on which you can explore your database schema, tables, your data within your tables and lined relationships in a seamless and easy to navigate way. With Quadrant you can create views (list, grid, table, graph, master-detail, tuple, filter etc..) in just a few clicks to visualize and drill through your data.
M and Quadrant give you the ability to refer to things by logical names. When you browse you data, Quadrant translates your mostly meaningless Foreign Keys to the name of the entity. It does this by using the value of the first column having “name” in the column name. So if you had a table with columns “product_id” and “product_name”, when you traversed your purchase orders table, it will show you the product_name for the FKs. And of course you can change this behavior.
I’ll drill down into more details in a further post.

Quadrant tips and tricks:

F12, Automatic map. Zooms to extent. view of the canvas.
F10, zooms on the selected work-pad.

Tags: ,
Posted in SQL Server by Ahmad Alkilani. No Comments