Windows 2012 Hosting - MVC 6 and SQL 2014 BLOG

Tutorial and Articles about Windows Hosting, SQL Hosting, MVC Hosting, and Silverlight Hosting

SQL 2012 Hosting - ASPHostPortal :: SSRS with SQL Server 2012 Denali and Sharepoint 2010

clock July 27, 2012 06:20 by author Jervis

In the new SQl server 2012 Denali SSRS is integrated in SharePoint 2010 as a Service application. This has few benefits over the old SSRS

- Easy deployment of SSRS reports in SharePoint 2010 server.

- Since SSRS is an Service application and now has its own database (like any other service application database).
- Administrators can now perform all the day-to-day activities like backup\restore of SSRS applictaion database, checking uls logs using Powershell etc.
- Supports WCF and Claims based Communication, built-in Scale with load balancing, PowerShell commandlets and cross-farm report consumption.
- Alerting is a new capability that has been introduced to Denali Reporting Services. You can now setup alert rules and be alerted when report data changes occur that match a set of rules.
- Denali is using Visual Studio 2010 so new reports can be build in Visual studio 2010 and published to SharePoint

Once you have configured SSRS service application you can use various available tools to create and Publish interactive reports in SharePoint 2010. Lets look at some of these tools


Report Builder 3.0
- Report Builder 3.0 is a tool which is Integrated in Denali and is used for creating and Publishing Interactive reports to a Reporting server like SharePoint.You can quickly start building reports in Report Builder by creating and publishing report parts,shared dataset, Creating Table, Matrix, Chart wizard or using Map wizard to create aggregated data reports into the SharePoint server.

Report Builder and SharePoint 2010
- When SSRS is integrated with SharePoint “Report Builder” is available as a Content type. You can create a library and start using Report Builder as a content type in your library.

Creating a new report with Report builder Content Type
– Once you add the “Report builder” content type to your library you can simple launch the Report builder tool\wizard by New Document item menu on the Ribbon and select “Report Builder Report”.

Power View or Project Crescent
- Power View is feature of Microsoft SQL Server 2012 Denali which serves as an Add-in for Microsoft SharePoint Server 2010 Enterprise Edition to build reports with an excellent data exploration, visualization, and presentation. Project Crescent runs in the browser and is working against a BI Semantic Model (BISM). BI Semantic Model is available on the client through the Excel based Power pivot modeling tool or on the server through Analysis Services Tabular project with SQL Server Denali.

Check Out the System Requirements for Power View Here.

Lets look at some of the advantages of using this new tool -


- Power View is a thin web client that launches right in the browser from a tabular model in SharePoint Server 2010.

- In Power View you’re working with the real data.
- You can simply drag-drop various controls to Power View designer and create presentable reports in minutes.
- In Power View, you can quickly create a variety of visualizations, from tables and matrices to bubble charts and sets of small multiple charts.
- Project Crescent is optimized for design and online screen consumption and interactivity.

Now, you can get all new features in SQL 2012 Hosting with us. Click
here to visit our site.



SQL 2012 Hosting - ASPHostPortal :: New T-SQL Features in SQL Server 2012

clock July 17, 2012 09:17 by author Jervis

Paging Data

Paging is a big issue for developers as it is required for many applications but entails many performance problems. Developers have used different workarounds to support paging.

For example, assume we need to display several pages of the HumanResource.Employee object with 5 rows per page. Below is the query you need to execute this in SQL Server 2012.

DECLARE @page_index SMALLINT ,
@page_Size SMALLINT = 5,
@offset INT
SET @page_index = 2
SET @offset = ( (@page_index - 1) * @page_Size)
SELECT   BusinessEntityID,
         NationalIDNumber,
         JobTitle
FROM HumanResources.Employee
ORDER BY BusinessEntityID

OFFSET @offset ROWS
FETCH NEXT @page_Size ROWS ONLY

In this query you will note that there are two additional syntaxes added to the well-known SELECT query.

OFFSET <offset> ROWS

The number of rows to exclude from the top.

FETCH NEXT <page_size> ROWS ONLY

The number of rows to display.

So in the results you will only receive the data you need.



You must remember that ORDER BY clause is a must, you can have multiple columns for the ORDER BY clause.


SELECT SOH.SalesOrderID

       ,SOD.SalesOrderDetailID
       ,P.Name As ProductNam
       FROM
Sales.SalesOrderDetail SOD INNER JOIN Sales.SalesOrderHeader SOH

ON SOD.SalesOrderID = SOH.SalesOrderID

INNER JOIN Production.Product P ON P.ProductID = SOD.ProductID

ORDER BY SOH.SalesOrderID,SOD.SalesOrderDetailID DESC

OFFSET 100 ROWS

FETCH NEXT 25 ROWS ONLY


The query plan for this will appear as below:




As you can see, the Top operator will be introduced at an almost zero percent cost.


Lag & Lead

This is another T-SQL feature added to SQL Server 2012 addresses an issue in reporting.


Let me describe this feature with a real world example.

Below are the sales amounts for one month.



The above data was generated from the FactInternetSales table in the AdventureWorksDWDenali database. You can find the same script in the 5528EN _02_03.sql file in the code folder.


For reports you need this month’s sales and last month’s sales along with the difference between the two months.


If you were asked to write a query for this, you query would look like:


SELECT

 t1.Year,
 t1.Month,
 t1.SalesAmount as SalesAmountThisMonth,
 t2.SalesAmount as SalesAmountLastMonth,
 t1.SalesAmount - t2.SalesAmount SalesDifferent
FROM MonthlySales as t1

LEFT OUTER JOIN MonthlySales as t2

ON (t1.Year = t2.Year) AND (t1.Month = t2.Month+1)

ORDER BY Year, Month


You need to use LEFT OUTER JOIN as INNER JOIN will not give any rows for the very first month in the data set.


Obviously, there will be a either an index scan or a table scan (depending whether you have an index or

not) twice for the table which is costly.

With SQL Server 2012, the new T-SQL function LAG is introduced just to address the above scenario:


SELECT

 Year,
 Month,
 SalesAmount as SalesAmountThisMonth,
 LAG(SalesAmount, 1, NULL) OVER(ORDER BY Year, Month) as SalesAmountLastMonth,
 SalesAmount - LAG(SalesAmount, 1, 0) OVER(ORDER BY Year, Month) SalesDifferent
FROM MonthlySales


As shown in the above example, the
LAG analytical function has three parameters. The first parameter is the expression which is the column in this scenario. The second parameter is the offset, since we are looking for last month this parameter is 1. If you want the last year’s comparative month’s data to be displayed then this parameter should be 12. The third parameter is the default, in case there is no match for the previous month, default value will be displayed.

So the output of the above query will be:




So how does this new syntax compare with the pre-2012 syntax? The simple way to compare them is running both queries in one batch and check the execution plan as shown in the below image. In the batch, the first query is the old style query and second query is the one using the
LAGfunction.



Relative cost wise, the first query (old style LEFT OUTER JOIN) cost is 77% whereas the query which contains
LAG cost is 23%. These queries were analyzed for Reads and duration using the SQL Profiler.

Below are the results for both queries executed after clearing the cache.


Reads

Duration (ms)

LEFT OUTER JOIN

125

8

LAG

46

3

Obviously using LAG has a clear performance advantage. However, to achieve better performance you need to cover those columns (in the above scenario, it will be Year and Month).

You can also use the
LAG function for grouping. For example, if you want product model sales differenciated by name you could use the below query.

SELECT

 ModelName,
 Year,
 Month,
 SalesAmount as SalesAmountThisMonth,
 LAG(SalesAmount, 1, NULL) OVER(PARTITION BY ModelName ORDER BY Year, Month) as SalesAmountLastMonth,
 SalesAmount - LAG(SalesAmount, 1, 0) OVER(PARTITION BY ModelName ORDER BY Year, Month) SalesDifferent
FROM ModelNameWiseMonthlySales


And the result is:




From the above results, you will see that the Sales Amount for the previous month is taken only for the model name.


LEAD
is another new function which is similar to LAGbut instead shows the future records.

SELECT

 Year,
 Month,
 SalesAmount as SalesAmountThisMonth,
 LAG(SalesAmount, 1, NULL) OVER(ORDER BY Year, Month) as SalesAmountLastMonth,
 LEAD(SalesAmount, 1, NULL) OVER(ORDER BY Year, Month) as SalesAmountNextMonth
FROM MonthlySales


As you can see from the below screenshot, the above query will give you the previous month’s and next month’s sales values in a single query.




FIRST_VALUE & LAST_VALUE


FIRST_VALUE
will return the first value while LAST_VALUE will return the last in an ordered set of values in SQL Server 2012.

Take for example, the below query.


SELECT

d.LastName,

d.GroupName,

e.JobTitle,

e.HireDate,

FIRST_VALUE(e.HireDate) OVER (PARTITION BY d.GroupName  ORDER BY e.JobTitle)
AS FirstValue,

LAST_VALUE(e.HireDate) OVER (PARTITION BY d.GroupName  ORDER BY e.JobTitle)
AS LastValue

 FROM HumanResources.Employee e
INNER JOIN HumanResources.vEmployeeDepartmentHistory d

ON e.BusinessEntityID = d.BusinessEntityID


This query will join the HumanResources.Employee table and the HumanResources.vEmployeeDepartmentHistory view together via BusinessEntityID. You can see that LastName, JobTitle, GroupName and Hiredate are selected from either the table or the view.


If you closely look at the
LAST_VALUE and the FIRST_VALUE function, you will observe that the Partition column and the order by column (Which are HireDate and GroupName respectively) are the same.

FIRST_VALUE
will give you the first value of the hiredate for each group when it orders by job title.

The results are given below:




CUME_DIST / PERCENT_RANK

CUME_DIST
and PERCENT_RANK can be used to calculate the relative rank of a row within a group of rows in SQL Server 2012.

Use PERCENT_RANK to evaluate the relative standing of a value within a query result set or partition.

PERCENT_RANK = (RANK() – 1) /

(TotalRows – 1)


CUME_DIST calculates the relative position of a specified value in a group of values.


The below example displays the Rank,
PERCENT_RANK and CUME_DIST

SELECT SalesOrderID,
 RANK() OVER(ORDER BY SalesOrderID) RANK,
 PERCENT_RANK() OVER(ORDER BY SalesOrderID) AS PERCENT_RANK,
 CUME_DIST() OVER(ORDER BY SalesOrderID) AS CUME_DIST
 FROM Sales.SalesOrderDetail
 WHERE SalesOrderID IN (75121,75122,75123 )

The result for the above query is shown below:



IIF

I’m not sure why the
IIF function which is a very simple function has not been included in previous versions of SQL Server. IIF is similar to the CASE function but it is optimized for evaluating two values. For example:

SELECT PersonType,

IIF(PersonType = 'EM','Employee','Other') PersonType

FROM Person.Person


The above query will return an Employee for the rows where PersonType equals EM and Other if not.


CHOOSE

This new simple feature is useful for extracting data elements from a data array:


DECLARE @MonthNumber int = 5
SELECT CHOOSE(@MonthNumber, 'JAN','FEB','MAR','APR','MAY','JUN','JUL','AUG','SEP','OCT','NOV','DEC') AS Month

The above will return the 5th element from the above array which is MAY.

TRY_CONVERT

The CONVERT function was used for earlier versions of SQL Server.

Let us say, we have a table with dates.

INSERT INTO MyData

(MyDate)

VALUES

('2011-01-01'),

('2012-12-31'),

('2012-02-29'),

('2011-02-29')


Note that 2011-02-29 is an invalid date. You could use
CONVERT function to convert varchar to the date data type:

SELECT CONVERT(date,MyDate) Mydate

FROM MyData

However, will return an error since you have an error in the last row. In SQL Server 2012, you can use the new feature,
TRY_CONVERT.

SELECT TRY_CONVERT(date,MyDate) Mydate

FROM MyData

TRY_CONVERT will return all the correct rows and invalid row will return null value. Main important thing is this won’t trigger an exception which will fail.

Apart from this you can use TRY_CONVERT function to retrieve only the error records.

SELECT MyDate

FROM MyData

WHERE TRY_CONVERT(date,Mydate) IS NULL


Instead of throwing an error TRY_CONVERT returns a NULL value and you can then more easily handle this in your code.


CONCAT

CONCAT is a significant addition for DQL Server developers.


If you have table with Title, FirstName, MiddleName, LastName columns and you may need to combine these columns and display them as a single column as shown below.

SELECT Title,FirstName,MiddleName, LastName ,
Title + ' '+ FirstName+ ' '+MiddleName+ ' '+LastName FullName
FROM Person.Person

Result are shown below:



However, except for three records and all the rows are null. That is because when a NULL value is added to a value the result is NULL.


The solution is to utilize the ISNULL function.


SELECT Title,FirstName,MiddleName, LastName ,

ISNULL(Title,'') + ' '+ FirstName+ ' '+ ISNULL(MiddleName, '' )+ ' '+LastName FullName

FROM Person.Person


However, this might be a tedious task if you have numerous columns to combine. In SQL Server 2012, you have a new function CONCAT which returns a string that is the result of concatenating two or more string values.


SELECT Title,FirstName,MiddleName, LastName ,

CONCAT(Title, ' ',FirstName, ' ',MiddleName, ' ',LastName) FullName

FROM Person.Person

From the below results you can see the null issue is eliminated in the FullName column.




Note that the
CONCAT function has no performance impact compared with the old-style syntax.

PARSE & TRY_PARSE

PARSE is somewhat similar to CONVERT in that it can convert one value to another specified value, however it should only be used for converting from a string to a date/time or number type. Note that since this is a CLR function it may have some performance impact.


SELECT PARSE('11/28/2011' AS datetime)
GO
SELECT PARSE('Monday, 28 November 2011' AS datetime USING 'en-US')
GO

As with TRY_CONVERT there is a function called TRY_PARSE to facilitate errors – ie it returns a NULL instead of throwing an error.

Date Time Functions

There are quite few date time functions introduced as shown in the below code. All the below functions will take parameters and generate datetime values from (except for DATEPART which outputs a part of a date as an integer).

SELECT
  DATEFROMPARTS(2012, 01, 31)   AS DATE_FROM_PARTS,
  DATETIME2FROMPARTS(2012, 01, 31, 15, 24, 5, 2, 7)  AS DATETIME2_FROM_PARTS,
  DATETIMEFROMPARTS(2012, 01, 31, 15, 30, 5, 997)   AS DATETIME_FROM_PARTS,
  DATETIMEOFFSETFROMPARTS(2012, 01, 31, 15, 30, 5, 1, -8, 0, 7)   AS DATETIMEOFFSET_FROM_PARTS,
  SMALLDATETIMEFROMPARTS(2012, 01, 31, 15, 30)   AS SMALLDATETIME_FROM_PARTS,
  TIMEFROMPARTS(15, 30, 5, 1, 7)    AS TIME_FROM_PARTS

All above functions will add different date parts and outputs will be given as a datetime.



EOMonth


In many human resource applications, you are required to get last date of the month. In previous versions of SQL Server, you might need to use following syntax.


SELECT

CAST(DATEADD(d,-1,(DATEADD(mm,DATEDIFF(m,0,GETDATE())+1,0)))AS DATE)

In SQL Server 2012 you can simply use EOMONTH to perform this:


SELECT EOMONTH(GETDATE())

There will not be any performance gain or loss since this function and the old-style function will both have same performance impact.



About ASPHostPortal.com

We’re a company that works differently to most. Value is what we output and help our customers achieve, not how much money we put in the bank. It’s not because we are altruistic. It’s based on an even simpler principle. "Do good things, and good things will come to you".

Success for us is something that is continually experienced, not something that is reached. For us it is all about the experience – more than the journey. Life is a continual experience. We see the Internet as being an incredible amplifier to the experience of life for all of us. It can help humanity come together to explode in knowledge exploration and discussion. It is continual enlightenment of new ideas, experiences, and passions


Author Link


Corporate Address (Location)

ASPHostPortal
170 W 56th Street, Suite 121
New York, NY 10019
United States

Sign in