When we viewing a large data set in a report the data is split across pages. This is also called pagination where a certain set of rows are displayed for the user. There are lot of techniques available to achieve such a functionality. In SQL Server 2012 there is a feature in T-SQL that would enable the user to achieve such a functionality. The feature is called OFFSET and FETCH, this allows users to build paging applications. Let me explain the use of OFFSET and FETCH with the following Example:
SELECT OrderDetailID
ProductID,
OrderDate,
Quantity,
ActualCost
FROM Products
ORDER BY OrderDate
OFFSET 0 ROWS
FETCH NEXT 15 ROWS ONLY;
In the above Select statement, the OFFSET provides a starting row from which to display the data, FETCH provides the number of rows to displayed in a page/result set. The above statement would be for say the first page of the result set. In order to view say the next page, the OFFSET value would change from 0 to 15, for the next page it would change from 15 to 30. Now in order to make this select more general for paging, the select statement would be modified to the following:
SELECT OrderDetailID
ProductID,
OrderDate,
Quantity,
ActualCost
FROM Products
ORDER BY OrderDate
OFFSET @OffSet ROWS
FETCH NEXT 15 ROWS ONLY;
The Value of a variable can be controlled through a function or a table for the application, like as when the user hits the next page the value being passed to the offset variable will change. Like how variables are being used for OFFSET, the FETCH clause can also take variables. For example:
SELECT OrderDetailID
ProductID,
OrderDate,
Quantity,
ActualCost
FROM Products
ORDER BY OrderDate
OFFSET @OffSet ROWS
FETCH NEXT @FetchRows - @Offset + 1 ROWS ONLY;
Tuesday, May 29, 2012
Tuesday, May 22, 2012
SQL Server 2012-T-SQL
As part of the SQL Server 2012 release there have been several enhancements. One of the most used in the database side of things is T-SQL, it has a very wide application. In oracle there is a feature called sequence which is table independent and the new value can be obtained any time as well as during UPDATE. This is a powerful feature which has been non existent till SQL Server 2008. In SQL Server 2012 the concept of sequence has been introduced. In the SQL Server Management studio one can fin sequences under programmability under the Database. It is illustrated in the image below.
For example in the user database called Features I created a sequence called seq_test where the start value is set to 1 and is incremented by 1. The syntax is given below.
CREATE SEQUENCE dbo.seq_test AS INT
START WITH 1
INCREMENT BY 1;
The statement above will create a sequence seq_test. Now In order to retrieve sequential values from the sequence use the following statement:
SELECT NEXT VALUE FOR dbo.seq_test. This would return the value 1 since the starting value is set to 1. When the select statement is run again the value will be 2.
Now If I were use this sequence in a insert statement where in one of the values is an incremental value I could do the following: Let us assume we have a table called Employee which a id column and a name column.
INSERT INTO dbo.Employee
(ID,Name)
VALUES(NEXT VALUE FOR dbo.seq_test,'James');
In Summary this is a useful feature which can be utilised by developers in situations where a incremental type value is needed.
CREATE SEQUENCE dbo.seq_test AS INT
START WITH 1
INCREMENT BY 1;
The statement above will create a sequence seq_test. Now In order to retrieve sequential values from the sequence use the following statement:
SELECT NEXT VALUE FOR dbo.seq_test. This would return the value 1 since the starting value is set to 1. When the select statement is run again the value will be 2.
Now If I were use this sequence in a insert statement where in one of the values is an incremental value I could do the following: Let us assume we have a table called Employee which a id column and a name column.
INSERT INTO dbo.Employee
(ID,Name)
VALUES(NEXT VALUE FOR dbo.seq_test,'James');
In Summary this is a useful feature which can be utilised by developers in situations where a incremental type value is needed.
Wednesday, May 16, 2012
Expression Task-SSIS 2012
As I Continue to explore the features of SSIS 2012, I have noticed some new components which are available. One of the new tasks which is available in the control flow tab is the Expression task. We are all familiar with expressions which use within each component to evaluate a certain condition or to assign properties with a value. In SSIS 2012 Expression Task does the same thing as Expressions but it is in the form of a task. The Expression task is available in the control flow and one just needs to drag and drop it to the control flow. Once the task is place in the control flow, Right click on the task and choose Edit. This would open up the familiar Expressions window. One of the new things in this window is that Parameters can also be accessed along with the variables. One could check the value of a Parameter in the Expression Task and then in the workflow one can check the value of the Parameter by using expressions in the workflow. I have attached a sample Control flow which utilises the Expression Task.
In a Nutshell the Expression Task can be used to control the workflow and gives Expressions a higher visibility by being a Task. This gives Expressions a higher visibility in SSIS 2012 compared to earlier versions where they were associated with the Component itself.
One can also notice in the image above that there is a slider component on the design surface This gives the developers the ability to zoom in on the flows being developed. In cases where the SSIS flows become very complex, the slider control can be useful to focus in areas that need to understood or edited.
In a Nutshell the Expression Task can be used to control the workflow and gives Expressions a higher visibility by being a Task. This gives Expressions a higher visibility in SSIS 2012 compared to earlier versions where they were associated with the Component itself.
One can also notice in the image above that there is a slider component on the design surface This gives the developers the ability to zoom in on the flows being developed. In cases where the SSIS flows become very complex, the slider control can be useful to focus in areas that need to understood or edited.
Friday, May 11, 2012
PowerView Environment...
Powerview as I already mentioned in my earlier posts is an important addition to SQL Server 2012.
Here is a snapshot of the design enevironment: Courtesy Introduction to SQL Server 2012 (Microsoft Press).
The Screen layout kind of resembles Report Builder 3.0 but it has additional features (As one would expect). The PowerView Tool is available in Sharepoint Server 2010 Enterprise Edition. Rather than working in design mode and then previewing the report, as you do when using Report Designer or Report Builder, you work directly with the data in the presentation layout of Power View.
You start with a tabular view of the data that you can change into various data visualizations
There are some benefits running Reporting Services in Sharepoint Mode.
Scale Reporting Services across web applications and across your SharePoint Server 2010
farms with fewer resources than possible in previous versions.
Use claims-based authentication to control access to Reporting Services reports.
Rely on SharePoint backup and recovery processes for Reporting Services content.
Here is a snapshot of the design enevironment: Courtesy Introduction to SQL Server 2012 (Microsoft Press).
The Screen layout kind of resembles Report Builder 3.0 but it has additional features (As one would expect). The PowerView Tool is available in Sharepoint Server 2010 Enterprise Edition. Rather than working in design mode and then previewing the report, as you do when using Report Designer or Report Builder, you work directly with the data in the presentation layout of Power View.
You start with a tabular view of the data that you can change into various data visualizations
There are some benefits running Reporting Services in Sharepoint Mode.
Scale Reporting Services across web applications and across your SharePoint Server 2010
farms with fewer resources than possible in previous versions.
Use claims-based authentication to control access to Reporting Services reports.
Rely on SharePoint backup and recovery processes for Reporting Services content.
Monday, May 7, 2012
SQL Server 2012 (Ebook)
There are some free ebooks available from Microsoft press, some of the ebooks include SQL Server 2012 Introduction and SQL Server 2008 R2 Introduction. I downloaded the SQL Server 2012 ebook which was around 10 MB pdf file. The content was good in the sense of providing what are the new features available in SQL Server 2012, the book covers from Database Administration to Reporting services. The book is divided into two main parts Database and Administration, the Second part being Business Intelligence. The material is extensive in the sense that the content is not merely a bullet list of issues. Below is the link provided, I would like to that the SQLServerCentral.com for providing the links.
http://blogs.msdn.com/b/microsoft_press/archive/2012/05/04/free-ebooks-great-content-from-microsoft-press-that-won-t-cost-you-a-penny.aspx
Hope you guys enjoy the ebooks.
http://blogs.msdn.com/b/microsoft_press/archive/2012/05/04/free-ebooks-great-content-from-microsoft-press-that-won-t-cost-you-a-penny.aspx
Hope you guys enjoy the ebooks.
Thursday, May 3, 2012
SSIS (Components Vs TSQL)
There is always a lot of debate about how to design SSIS packages. The question always is whether to use components or TSQL in SSIS packages and which would perform better. Recently a discussion in the MSBI groups in Linkedin has started off a very good debate.
Here is the link:
SSIS - TSQL Stored Procedures Vs SSIS inbuilt transformations
http://www.linkedin.com/groupItem?view=&gid=59185&type=member&item=111443713&qid=bb7bcd72-ff00-493a-a853-3fdd61f3d9d9&trk=group_most_recent_rich-0-b-cmr&goback=%2Egmr_59185
There are some very good points for using SP/SQL and Components.
Here is the link:
SSIS - TSQL Stored Procedures Vs SSIS inbuilt transformations
http://www.linkedin.com/groupItem?view=&gid=59185&type=member&item=111443713&qid=bb7bcd72-ff00-493a-a853-3fdd61f3d9d9&trk=group_most_recent_rich-0-b-cmr&goback=%2Egmr_59185
There are some very good points for using SP/SQL and Components.
Tuesday, May 1, 2012
SSIS 2012
Once I had the SQL Server 2012 Bi edition installed I decided to start creating a SSIS package in SQL Server 2012. As mentioned in my previous blog post BIDS has now a new name called SQL Server Data Tools. On launching the SQL Server Data Tools one can customize the setting for Business Intelligence projects. I created a New SSIS project and created a new package. I created a new data flow task with a source has a flat file and destination as a sql server destination. In the Data flow tab I noticed some new things. One of the new things i noticed was Favorites section, this has a Source Assistant and a Destination Assistant. These
two tasks would help the developer with creating a source and destination connection. One of the other new source which i notice in the source connections was the availability of a CDC source. The CDC source reads change data from a SQL Server change table making it available for processing by downstream data-flow components. As I build out package I will be posting blog entries detailing the new features available.
two tasks would help the developer with creating a source and destination connection. One of the other new source which i notice in the source connections was the availability of a CDC source. The CDC source reads change data from a SQL Server change table making it available for processing by downstream data-flow components. As I build out package I will be posting blog entries detailing the new features available.
Subscribe to:
Posts (Atom)