In this post, I will tell you about the LAG and LEAD functions in SQL Server. These two functions are analytical functions in SQL Server. In actual scenarios we need to analyze the data, for example, comparing previous sales data.

The Lag and Lead functions support the window partitioning and ordering clauses in SQL Server. The Lag and Lead functions do not support the window frame clause.

LAG
The Lag function gives the previous column values based on ordering.

LEAD
The Lead function gives the next column values based on ordering.

Demo
    CREATE TABLE DBO.SALES 
    ( 
    PROD_ID INT , 
    SALES_YEAR INT, 
    SALES_AMOUNT INT  
    ) 

    INSERT INTO DBO.SALES(PROD_ID,SALES_YEAR,SALES_AMOUNT) VALUES(1,2009,10000) 
    INSERT INTO DBO.SALES(PROD_ID,SALES_YEAR,SALES_AMOUNT) VALUES(1,2010,9000) 
    INSERT INTO DBO.SALES(PROD_ID,SALES_YEAR,SALES_AMOUNT) VALUES(1,2011,8000) 
    INSERT INTO DBO.SALES(PROD_ID,SALES_YEAR,SALES_AMOUNT) VALUES(1,2012,7000) 
    INSERT INTO DBO.SALES(PROD_ID,SALES_YEAR,SALES_AMOUNT) VALUES(1,2013,14000) 
    INSERT INTO DBO.SALES(PROD_ID,SALES_YEAR,SALES_AMOUNT) VALUES(1,2014,18000) 
    INSERT INTO DBO.SALES(PROD_ID,SALES_YEAR,SALES_AMOUNT) VALUES(1,2015,15000) 
    INSERT INTO DBO.SALES(PROD_ID,SALES_YEAR,SALES_AMOUNT) VALUES(2,2013,12000) 
    INSERT INTO DBO.SALES(PROD_ID,SALES_YEAR,SALES_AMOUNT) VALUES(2,2014,8000) 
    INSERT INTO DBO.SALES(PROD_ID,SALES_YEAR,SALES_AMOUNT) VALUES(2,2015,16000) 
    INSERT INTO DBO.SALES(PROD_ID,SALES_YEAR,SALES_AMOUNT) VALUES(3,2012,7000) 
    INSERT INTO DBO.SALES(PROD_ID,SALES_YEAR,SALES_AMOUNT) VALUES(3,2013,8000) 
    INSERT INTO DBO.SALES(PROD_ID,SALES_YEAR,SALES_AMOUNT) VALUES(3,2014,9700) 
    INSERT INTO DBO.SALES(PROD_ID,SALES_YEAR,SALES_AMOUNT) VALUES(3,2015,12500) 

    SELECT * FROM DBO.SALES

The following example shows the Previous Year Sales Amount.
SELECT *, LAG(SALES_AMOUNT) OVER(ORDER BY PROD_ID ,SALES_YEAR) [Prevoius Year Sales] FROM DBO.SALES

The following example shows the Next Year Sales Amount.
    SELECT * , 
    LEAD(SALES_AMOUNT) OVER(ORDER BY PROD_ID ,SALES_YEAR) [Next Year Sales]  
    FROM DBO.SALES 

The following example shows the Previous Year Next Year Sales Amount using the partition by clause.
    SELECT *, 
     
       LAG(SALES_AMOUNT) OVER(PARTITION BY PROD_ID ORDER BY PROD_ID ,SALES_YEAR) [PREVOIUS YEAR SALES] , 
       LEAD(SALES_AMOUNT) OVER(PARTITION BY PROD_ID ORDER BY PROD_ID ,SALES_YEAR) [NEXT YEAR SALES]  
    FROM DBO.SALES     

The following example shows an offset other than 1.

The offset is by default 1. If we want an offset other than 1 then we need to provide 2 argument values in the Lag and Lead functions.
    SELECT * , 
       LAG(SALES_AMOUNT,2)  OVER(ORDER BY PROD_ID ,SALES_YEAR) [PREVOIUS YEAR SALES] , 
       LEAD(SALES_AMOUNT,2)  OVER( ORDER BY PROD_ID ,SALES_YEAR) [NEXT YEAR SALES]  
    FROM DBO.SALES 

The following example shows replacing the null with various values:

    SELECT * , 
       LAG(SALES_AMOUNT,2,0)  OVER(ORDER BY PROD_ID ,SALES_YEAR) [PREVOIUS YEAR SALES] , 
       LEAD(SALES_AMOUNT,2,0)  OVER( ORDER BY PROD_ID ,SALES_YEAR) [NEXT YEAR SALES]  
    FROM DBO.SALES 

HostForLIFE.eu SQL Server 2016 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.

In this article, we will focus on Json support in SQL Server 2016. We will take a look how Json will impact data warehouse solutions

Since the advent of EXtensible Markup Language (XML) many modern web applications have focused on providing data that is both human-readable and machine-readable. From a relational database perspective, SQL Server kept up with these modern web applications by providing support for XML data in a form of an XML data type and several functions that could be used to parse, query and manipulate XML formatted data.

As a result of being supported in SQL Server, data warehouse solutions based off SQL Server were then able to source XML-based OLTP data into a data mart. To illustrate this point, let’s take a look at the XML representation of our fictitious Fruit Sales data shown in figure below.

To process this data in data warehouse, we would first have to convert it into relational format of rows and columns using T-SQL XML built-in functions such as the nodes() function. 

The results of the above script are shown below in a recognisable format for data warehouse.

Soon after XML became a dominant language for data interchange for many modern web applications, JavaScript Object Notation (JSON) was introduced as a lightweight data-interchange format that is more convenient for web applications to process than XML. Likewise most relational database vendors released newer versions of their database systems that included the support for JSON formatted data. Unfortunately, Microsoft SQL Server was not one of those vendors and up until SQL Server 2014, JSON data was not supported. Obviously this lack of support for JSON, created challenges for data warehouse environments that are based off SQL Server.

Although there were workarounds (i.e. using Json.Net) to addressing the lack of JSON support in SQL Server, there was always sense that these workarounds were inadequate, time-wasting, and were forcing data warehouse development teams to pick up a new skill (i.e. learn .Net). Fortunately, the release of SQL Server 2016 has ensured that development teams can throw away their JSON workarounds as JSON is supported in SQL Server 2016.

Parsing JSON Data into Data Warehouse

Similarly to XML support in SQL Server, SQL Server supports of JSON can be classified into two ways:

1. Converting Relational dataset into JSON format
2. Converting JSON dataset into relational format

However, for the purposes of this discussion we are focusing primarily on the second part – which is converting a JSON formatted data (retrieved from OLTP sources) into a relational format of rows and columns. To illustrate our discussion points we once again make use of the fictitious fruit sales dataset. This time around the fictitious dataset has been converted into a JSON format as shown in below.

ISJSON function

As part of supporting JSON formatted data in other relational databases such as MySQL and PostgreSQL 9.2, there is a separate JSON data type that has been introduced by these vendors. Amongst other things, JSON data type conducts validation checks to ensure that values being stored are indeed of valid JSON format.

Unfortunately, SQL Server 2016 (and ORACLE 12c) do not have a special data type for storing JSON data instead a variable character (varchar/nvarchar) data type is used. Therefore, a recommended practice to dealing with JSON data in SQL Server 2016 is to firstly ensure that you are indeed dealing with a valid JSON data. The simplest way to do so is to use the ISJSON function. This is a built-in T-SQL function that returns 1 for a valid JSON dataset and 0 for invalids.

OPENJSON function

Now that we have confirmed that we are working with a valid JSON dataset, the next step is to convert the data into a table format. Again, we have a built-in T-SQL function to do this in a form of OPENJSON. OPENJSON works similar to OPENXML in that it takes in an object and convert its data into rows and columns.

Below shows a complete T-SQL script for converting JSON object into rows and columns.

Once we execute the above script, we get relational output shown below

Now that we have our relational dataset, we can process this data into data warehouse.

JSON_VALUE function

Prior to concluding our discussion of JSON in SQL Server 2016, it is worth mentioning that in addition to OPENJSON, you have other functions such as JSON_VALUE that could be used to query JSON data. However this function returns a scalar value which means that unlike the multiple rows and columns returned using OPENJSON, JSON_VALUE returns a single value as shown in Figure below.

If you the JSON object that you are querying doesn’t have multiple elements, than you don’t have to specify the row index (i.e. [0]) 

Conclusion

The long wait is finally over and with the release of SQL Server 2016, JSON is now supported. Similarly to XML, T-SQL support the conversion of JSON object to relational format as well the conversion of relational tables to a JSON object. This support is implemented via built-in T-SQL functions such as OPENJSON and JSON_VALUE. In spite of all the excitement with the support of JSON is SQL Server 2016, we still don’t have a JSON data type. The ISJSON function can then be used to validate JSON text.

In this post I will show you how to Clearing Down A Database Full Of Constraints In SQL Server. Have you ever been in a scenario where you have to clear down some data within a database that is chock full of constraints but don't want to wipe out your precious relationships, indices and all that other jazz?

I found myself in a similar situation earlier this week, and needed a clear-down script that would wipe out all of the data within an entire database, without being bothered by any existing constraints. Here it is.

    USE @YourTable;  
    EXEC sp_MSForEachTable "ALTER TABLE ? NOCHECK CONSTRAINT ALL"  
    EXEC sp_MSForEachTable "DELETE FROM ?"  
    EXEC sp_MSForEachTable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT ALL"  
    GO  

What is this doing?
The script itself takes advantage of an undocumented stored procedure within SQL Server called sp_MSForEachTable that will actually iterate through all of the tables within a given database.

Now that we know we are going to be looping through each of the tables within the specified database, let's see what is going to happen to each of the tables.

    ALTER TABLE ? NOCHECK CONSTRAINT ALL
    This will disable any constraint checking that is present on the table (so, operations like deleting a primary key or a related object won't trigger any errors).

    DELETE FROM ?
    This will delete every record within the table.

    ALTER TABLE ? WITH CHECK CHECK CONSTRAINT ALL
    This re-enables the constraint checking, bringing your table back to its original state, sans data.

Note
It is very important that you properly scope this query to the table that you are targeting to avoid any crazy data loss.

While I don't think that you could just leave that out and execute on master, I wouldn't want to even risk testing that out (although feel free to try it out and let me know if it nukes everything).

HostForLIFE.eu SQL Server 2012 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.

In this article, I am going to tell you how to create a procedure in the real time scenario. A stored procedure is a set of SQL statements, which has been created and stored in the database as an object. Stored procedure will accept the input and output parameters, so that a single procedure can be used over the network by several users, using different input. Stored procedure will reduce the network traffic and increase the performance.

Real time scenario
Step 1: Create a table to describe and create the stored procedure.
    create table Product 
    ( 
          ProductId int primary key, 
          ProductName varchar(20) unique, 
          ProductQty int, 
          ProductPrice float 
    ) 

Step 2: Insert some value to the describe scenario.
    insert product values(1,'Printer',10,4500) 
    insert product values(2,'Scanner',15,3500) 
    insert product values(3,'Mouse',45,500)  

Step 3: Check your table with the inserted value.
    select * from product  

Step 4: Real time scenario is given below:
Create a stored procedure, which is used to perform the requirements, given below:

Before inserting, check the detail about the product name. If the product name is available, update an existing product qty + inserted product qty,

• Before inserting, check the detail about the product name.
  
• If the product name is available, check the product price.
• If the existing product price is less, the inserted product product price replaces the existing product price with the inserted product price.
• If first and second conditions are not satisfied, insert the product information, as new record into the table.

    create procedure prcInsert  
    @id int, 
    @name varchar(40), 
    @qty int, 
    @price float 
    as 
    begin 
     declare @cnt int 
     declare @p float 
     select @cnt=COUNT(ProductId)from Product where pname=@name 
     if(@cnt>0) 
     begin 
      update Product set ProductQty=ProductQty+@qty where ProductName=@name 
      select @p=ProductPrice from Product where ProductName=@name 
      if(@p<@price) 
      begin 
       update Product set ProductPrice=@price where ProductName=@name 
      end 
     end 
     else 
     begin 
      insert Product values(@id,@name,@qty,@price) 
     end 
    end  

HostForLIFE.eu SQL Server 2016 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.

As we’ve come to expect from new versions of Windows Server, Windows Server 2016 arrives packed with a huge array of new features. Many of the new capabilities, such as containers and Nano Server, stem from Microsoft’s focus on the cloud. Others, such as Shielded VMs, illustrate a strong emphasis on security. Still others, like the many added networking and storage capabilities, continue an emphasis on software-defined infrastructure begun in Windows Server 2012.

The GA release of Windows Server 2016 rolls up all of the features introduced in the five Technical Previews we’ve seen along the way, plus a few surprises. Now that Windows Server 2016 is fully baked, we’ll treat you to the new features we like the most.

Here are several features that you can get from Windows Server 2016:

Nano Server

Nano Server boasts a 92 percent smaller installation footprint than the Windows Server graphical user interface (GUI) installation option. Beyond just that, these compelling reasons may make you start running Nano for at least some of your Windows Server workloads:

• Bare-metal OS means far fewer updates and reboots are necessary.
• Because you have to administratively inject any server roles from outside Nano, the server has a much-reduced attack surface when compared to GUI Windows Server.
• Nano is so small that it can be ported easily across servers, data centers and physical sites.
• Nano hosts the most common Windows Server workloads, including Hyper-V host.
• Nano is intended to be managed completely remotely. However, Nano does include a minimal local management UI called "Nano Server Recovery Console," shown in the previous screenshot, that allows you to perform initial configuration tasks.

Containers

Microsoft is working closely with the Docker development team to bring Docker-based containers to Windows Server. Until now, containers have existed almost entirely in the Linux/UNIX open-source world. They allow you to isolate applications and services in an agile, easy-to-administer way. Windows Server 2016 offers two different types of "containerized" Windows Server instances:

• Windows Server Container. This container type is intended for low-trust workloads where you don't mind that container instances running on the same server may share some common resources
• Hyper-V Container. This isn't a Hyper-V host or VM. Instead, its a "super isolated" containerized Windows Server instance that is completely isolated from other containers and potentially from the host server. Hyper-V containers are appropriate for high-trust workloads.

Linux Secure Boot

Secure Boot is part of the Unified Extensible Firmware Interface (UEFI) specification that protects a server's startup environment against the injection of rootkits or other assorted boot-time malware.

The problem with Windows Server-based Secure Boot is that your server would blow up (figuratively speaking) if you tried to create a Linux-based Generation 2 Hyper-V VM because the Linux kernel drivers weren't part of the trusted device store. Technically, the VM's UEFI firmware presents a "Failed Secure Boot Verification" error and stops startup.

Nowadays, the Windows Server and Azure engineering teams seemingly love Linux. Therefore, we can now deploy Linux VMs under Windows Server 2016 Hyper-V with no trouble without having to disable the otherwise stellar Secure Boot feature.

ReFS

The Resilient File System (ReFS) has been a long time coming in Windows Server. In Windows Server 2016, we finally get a stable version. ReFS is intended as a high-performance, high-resiliency file system intended for use with Storage Spaces Direct (discussed next in this article) and Hyper-V workloads.

Storage Spaces Direct

Storage Spaces is a cool Windows Server feature that makes it more affordable for administrators to create redundant and flexible disk storage. Storage Spaces Direct in Windows Server 2016 extends Storage Spaces to allow failover cluster nodes to use their local storage inside this cluster, avoiding the previous necessity of a shared storage fabric.

ADFS v4

Active Directory Federation Services (ADFS) is a Windows Server role that supports claims (token)-based identity. Claims-based identity is crucial thanks to the need for single-sign on (SSO) between on-premises Active Directory and various cloud-based services.

ADFS v4 in Windows Server 2016 finally brings support for OpenID Connect-based authentication, multi-factor authentication (MFA), and what Microsoft calls "hybrid conditional access." This latter technology allows ADFS to respond when user or device attributes fall out of compliance with security policies on either end of the trust relationship.

Nested Virtualization

Nested virtualization refers to the capability of a virtual machine to itself host virtual machines. This has historically been a "no go" in Windows Server Hyper-V, but we finally have that ability in Windows Server 2016.

Nested virtualization makes sense when a business wants to deploy additional Hyper-V hosts and needs to minimize hardware costs.

Hyper-V Server has allowed us to add virtual hardware or adjust the allocated RAM to a virtual machine. However, those changes historically required that we first power down the VM. In Windows Server 2016, we can now "hot add" virtual hardware while VMs are online and running. I was able to add an additional virtual network interface card (NIC) to my running Hyper-V virtual machine.

PowerShell Direct

In Windows Server 2012 R2, Hyper-V administrators ordinarily performed Windows PowerShell-based remote administration of VMs the same way they would with physical hosts. In Windows Server 2016, PowerShell remoting commands now have -VM* parameters that allows us to send PowerShell directly into the Hyper-V host's VMs!

Invoke-Command -VMName 'server2' -ScriptBlock {Stop-Service -Name Spooler} -Credential 'tomsitprotim' -Verbose

We used the new -VMName parameter of the Invoke-Command cmdlet to run the Stop-Service cmdlet on the Hyper-V VM named server2.

Shielded VMs

The new Host Guardian Service server role, which hosts the shielded VM feature, is far too complex to discuss in this limited space. For now, suffice it to say that Windows Server 2016 shielded VMs allow for much deeper, fine-grained control over Hyper-V VM access.

For example, your Hyper-V host may have VMs from more than one tenant, and you need to ensure that different Hyper-V admin groups can access only their designated VMs. By using BitLocker Drive Encryption to encrypt the VM's virtual hard disks, shielded VMs can solve that problem.

Angular js URL:
<script src="http://ajax.googleapis.com/ajax/libs/angularjs/1.3.14/angular.min.js"></script> 

Html tag:
<div ng-app="myApp" ng-controller="myCntrl"> 
    <table> 
        <thead> 
            <tr> 
                <th> 
                    Emp Code. 
                </th> 
                <th> 
                    Employee Name 
                </th> 
                <th> 
                    Pan No 
                </th> 
                  
            </tr> 
        </thead> 
        <tr ng-repeat="student in EmployeeList"> 
            <td ng-bind="student.StudentID"> 
            </td> 
            <td ng-bind="student.StudentName"> 
            </td> 
            <td ng-bind="student.PanNO"> 
            </td> 
              
        </tr> 
    </table> 
</div> 

Angular js Script :

<script> 
   var app = angular.module("myApp", []); 
   app.controller("myCntrl", function ($scope, $http) { 

       $scope.fillList = function () { 
           $scope.EmployeeName = ""; 
           var httpreq = { 
               method: 'POST', 
               url: 'Default2.aspx/GetList', 
               headers: { 
                   'Content-Type': 'application/json; charset=utf-8', 
                   'dataType': 'json' 
               }, 
               data: {} 
           } 
           $http(httpreq).success(function (response) { 
               $scope.EmployeeList = response.d; 
           }) 
       }; 
       $scope.fillList(); 
   }); 
</script> 

Asp.net Cs page code:
using System; 
using System.Collections.Generic; 
using System.Data.SqlClient; 
using System.Data; 

public partial class Default2 : System.Web.UI.Page 
{ 
protected void Page_Load(object sender, EventArgs e) 
{ 

} 
[System.Web.Services.WebMethod()] 
public static List<Employee> GetList() 
{ 
    List<Employee> names = new List<Employee>(); 
    DataSet ds = new DataSet(); 
    using (SqlConnection con = new SqlConnection(@"Data Source=140.175.165.10;Initial Catalog=Payroll_290716;user id=sa;password=Goal@12345;")) 
    { 
        using (SqlCommand cmd = new SqlCommand()) 
        { 
            cmd.Connection = con; 
            cmd.CommandText = "select EmpId,Empcode, name,PanNo from EMPLOYEEMASTER  order by Name;"; 
            using (SqlDataAdapter da = new SqlDataAdapter(cmd)) 
            { 
                da.Fill(ds); 
            } 
        } 
    } 
    if (ds != null && ds.Tables.Count > 0) 
    { 
        foreach (DataRow dr in ds.Tables[0].Rows) 
            names.Add(new Employee(int.Parse(dr["EmpId"].ToString()), dr["name"].ToString(), dr["PanNo"].ToString())); 
    } 
    return names; 
} 
} 
public class Employee 
{ 
public int StudentID; 
public string StudentName; 
public string PanNO; 
public Employee(int _StudentID, string _StudentName, string _PanNO) 
{ 
    StudentID = _StudentID; 
    StudentName = _StudentName; 
    PanNO = _PanNO; 
} 
}  

Whole HTML page:
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default2.aspx.cs" Inherits="Default2" %> 

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> 
<html xmlns="http://www.w3.org/1999/xhtml"> 
<head runat="server"> 
<title></title> 
<script src="http://ajax.googleapis.com/ajax/libs/angularjs/1.3.14/angular.min.js"></script> 
</head> 
<body> 
<form id="form1" runat="server"> 
<div ng-app="myApp" ng-controller="myCntrl"> 
    <table> 
        <thead> 
            <tr> 
                <th> 
                    Emp Code. 
                </th> 
                <th> 
                    Employee Name 
                </th> 
                <th> 
                    Pan No 
                </th> 
                  
            </tr> 
        </thead> 
        <tr ng-repeat="student in EmployeeList"> 
            <td ng-bind="student.StudentID"> 
            </td> 
            <td ng-bind="student.StudentName"> 
            </td> 
            <td ng-bind="student.PanNO"> 
            </td> 
              
        </tr> 
    </table> 
</div> 
<script> 
    var app = angular.module("myApp", []); 
    app.controller("myCntrl", function ($scope, $http) { 

        $scope.fillList = function () { 
            $scope.EmployeeName = ""; 
            var httpreq = { 
                method: 'POST', 
                url: 'Default2.aspx/GetList', 
                headers: { 
                    'Content-Type': 'application/json; charset=utf-8', 
                    'dataType': 'json' 
                }, 
                data: {} 
            } 
            $http(httpreq).success(function (response) { 
                $scope.EmployeeList = response.d; 
            }) 
        }; 
        $scope.fillList(); 
    }); 
</script> 
</form> 
</body> 
</html>  

HostForLIFE.eu AngularJS Hosting

HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.