Search This Blog

Loading...

Thursday, July 21, 2016

MS SQL Server - Returning the Inserted Rows

In this post, we will understand how to return the inserted rows. For example, we are inserting records in a table from Stored Procedure, then we want to return the resulting inserted values to the calling application.

For this we use the OUTPUT clause. OUTPUT clause adds a result set (in a virtual table INSERTED) to the INSERT statement, which contains specified set of columns and the set of rows that were inserted. Let see an example to understand this concept.

Let create a sample table and insert some records.

declare @tblCustomerOrders table (
 CustomerCode varchar(50),
 CustomerName varchar(50),
 OrderAmount decimal(18, 2)
)

insert into @tblCustomerOrders (CustomerCode, CustomerName, OrderAmount)
values 
('C001', 'Imran', 100),
('C002', 'Salman', 40),
('C003', 'Kamran', 500)

Here is nothing special, we just created a table variable and inserted 3 rows. But it will only display a message showing 3 row(s) affected as below, but do not return any data to the calling application.

Now, rewrite the above insert statement and add the OUTPUT clause to return inserted rows. In the following example, the OUTPUT clause will return CustomerCode, CustomerName and OrderAmount.

insert into @tblCustomerOrders (CustomerCode, CustomerName, OrderAmount)
OUTPUT INSERTED.CustomerCode, INSERTED.CustomerName, INSERTED.OrderAmount
values 
('C001', 'Imran', 100),
('C002', 'Salman', 40),
('C003', 'Kamran', 500)

It will output the following result set:

You can also output only selected columns, like you specify in regular SELECT clause. In the following exmaple, the OUTPUT clause is only returning CustomerCode and OrderAmount, CustomerName column will not be returned in the resulting table.

insert into @tblCustomerOrders (CustomerCode, CustomerName, OrderAmount)
OUTPUT INSERTED.CustomerCode, INSERTED.OrderAmount
values 
('C001', 'Imran', 100),
('C002', 'Salman', 40),
('C003', 'Kamran', 500)

The output will be:

Also for simplicity, you can use * to return all columns. In the following exmaple, the OUTPUT clause will return all the columns that will be used in insert statement.

insert into @tblCustomerOrders (CustomerCode, CustomerName, OrderAmount)
OUTPUT INSERTED.*
values 
('C001', 'Imran', 100),
('C002', 'Salman', 40),
('C003', 'Kamran', 500)

The output will return all columns:


Thursday, June 23, 2016

MS SQL Server - Window Functions - Aggregate Functions

Aggregate functions perform a calculation on a set of values and return a single value, frequently used with the GROUP BY clause of the SELECT statement.

In this post, we will try using aggregate functions with and without OVER clause. I recommend you to read the previous post on Window Ranking Functions

Let create a sample table and insert some records.

declare @tblCustomerOrders table (
 CustomerName varchar(50),
 OrderAmount decimal(18, 2)
)

insert into @tblCustomerOrders (CustomerName, OrderAmount)
values 
('Imran', 100),
('Imran', 40),
('Kamran', 500),
('Salman', 350),
('Kamran', 430),
('Salman', 210),
('Imran', 70),
('Salman', 105),
('Imran', 750)

In this table, if we want to see the sum of OrderAmount for each customer, we use the following query.

SELECT 
  CustomerName
 ,SUM(OrderAmount) AS 'Sum'
FROM @tblCustomerOrders

But when you run this query you will get error:

Column '@tblCustomerOrders.CustomerName' is invalid in the select list 
because it is not contained in either an aggregate function or the 
GROUP BY clause.

Since we are missing the GROUP BY clause, we got this error. To work it as expected, we have to add GROUP BY clause with CustomerName field.

SELECT
  CustomerName
 ,SUM(OrderAmount) AS 'Sum'
FROM @tblCustomerOrders
GROUP BY CustomerName

Now you will get the expected result.

Lets see another scenario, if we want to list all customers with their SUM() of OrderAmounts, but we also need to list individual order amounts in separate column. Try this query:

SELECT
  CustomerName
 ,OrderAmount -- (individual OrderAmount)
 ,SUM(OrderAmount) AS 'Sum'
FROM @tblCustomerOrders
GROUP BY CustomerName

Since, OrderAmount field is not mentioned in GROUP BY clause, so you will get error message display:

Column '@tblCustomerOrders.OrderAmount' is invalid in the select list 
because it is not contained in either an aggregate function or the 
GROUP BY clause.

Now add OrderAmount field in GROUP BY clause along-with CustomerName field.

SELECT 
  CustomerName
 ,OrderAmount -- (individual OrderAmount)
 ,SUM(OrderAmount) AS 'Sum'
FROM @tblCustomerOrders
GROUP BY CustomerName, OrderAmount

This query will not generate error, but you get the following output, which is not the desired one.

Because each row is also grouped by OrderAmount, so the SUM(OrderAmount) is same as of OrderAmount.

How to fix this, there comes the OVER clause. Although there may be some other workarounds you can use to get the desired output, but the simpler one is using the OVER clause. You can put your aggregate functions in conjunction with OVER clause and there is no need to add GROUP BY clause.

Try getting SUM with OVER clause:

SELECT
  CustomerName
 ,SUM(OrderAmount) OVER() AS 'Sum'
FROM @tblCustomerOrders

This will generate the output:

But this is not the expected result, it is calculating the SUM of all records and not calculating SUM for individual Customers. As I also mentioned in my last post on Window Functions, add PARTITION BY clause in OVER clause.

The output will be:

As you might noticed, unlike the working query with GROUP BY clause discussed above, this query is displaying the list of all records along-with SUM, i.e. CustomerNames are repeating with no of occurrences exists in the table. So you can also add OrderAmount field to display along-with SUM filed.

SELECT
  CustomerName
 ,OrderAmount
 ,SUM(OrderAmount) OVER(PARTITION BY CustomerName) AS 'Sum'

FROM @tblCustomerOrders

Generates the output:

This gives us the solution that we can list down all customer records with SUM of OrderAmount and also showing individual OrderAmounts.

Similarly we can use other aggregate functions like COUNT(), AVG(), MIN(), MAX()

SELECT
  CustomerName
 ,OrderAmount
 ,COUNT(OrderAmount) OVER(PARTITION BY CustomerName) AS 'Count'   
 ,SUM(OrderAmount) OVER(PARTITION BY CustomerName) AS 'Sum'
 ,AVG(OrderAmount) OVER(PARTITION BY CustomerName) AS 'Avg'
 ,MIN(OrderAmount) OVER(PARTITION BY CustomerName) AS 'Min'
 ,MAX(OrderAmount) OVER(PARTITION BY CustomerName) AS 'Max'
 
FROM @tblCustomerOrders

Will generate the output as expected:

References:

Thursday, May 26, 2016

MS SQL Server - Window Functions - Ranking Functions

A window function is one that can be applied to a set of rows, The word ‘window’ is used to refer to the set of rows that the function works on. It provides a way to work with data grouped into logical windows.

The OVER clause used to define a window or set of rows within a query result set. Then we use a window function to computes a value for each row in the subset. OVER clause can be used to compute aggregated values such as average, sum, min, max, or a top N per group results.

The window function can be of two types:

  • Ranking function
  • Aggregate function.

In this post, I will discuss Ranking Window Functions.

Depending of on whether you’re using a ranking window function or aggregate window function, the OVER clause will need to be implemented accordingly. If we use the OVER clause with a ranking function, we can specify an optional PARTITION BY clause and a required ORDER BY clause. However, if we are working with with an aggregate function, then a PARTITION BY clause must be used, and should not have an ORDER BY clause.

Working with Ranking Window Functions

Ranking functions return an integer ranking value for each row or group in a partition/window.

Transact-SQL provides the following ranking functions:

  • ROW_NUMBER
  • RANK
  • DENSE_RANK
  • NTILE

Let see each function in action with an example.

  • ROW_NUMBER:

    Returns a sequential integer number for each row in the window.

    Example: Create a sample table and insert some records to query.

    declare @tblItems table (
     ItemCode varchar(5),
     ItemCategory varchar(50),
     StockQty int
    )
    
    insert into @tblItems(ItemCode, ItemCategory, StockQty)
    values 
    ('I001', 'Books', 10),
    ('I002', 'Books', 20),
    ('I003', 'Laptops', 25),
    ('I004', 'Laptops', 30),
    ('I005', 'Laptops', 20),
    ('I006', 'SmartPhones', 50),
    ('I007', 'SmartPhones', 30),
    ('I008', 'Books', 10)
    

    The following example calculates a row number for @tblTest based on ItemCode.

    SELECT 
     Itemcode
    ,ROW_NUMBER() OVER(ORDER BY itemcode) AS 'ROW_NUMBER()'
    FROM @tblItems
    

    Here is the output:

  • RANK:

    Define an integral rank value for each row in the window. It returns a same rank value for repeated column values, however, the next number in the ranking sequence is skipped by the number of repeating rows (also known as gap). It does not always return consecutive integers.

    The following example ranks the @tblItems with sort by ItemCategory. Since the ItemCodes I001, I002, I008 have same ItemCategory, these are tied, these are ranked one, and the next rank for item I003 is returned as 4, i.e. skipped the values 2 and 3 from the previous tied items.

    SELECT 
     ItemCode
    ,ItemCategory
    ,RANK() OVER (ORDER BY ItemCategory) AS 'RANK()'
    FROM @tblItems
    

    Here is the output:

  • DENSE_RANK:

    Define an integral rank value for each row in the window, without any gaps in the ranking. It also returns a same rank value for repeated column values. If values in the ranking column are the same, they receive the same rank. The next number in the ranking sequence is the next number in the sequence. The numbers returned by the this function do not have gaps and always have consecutive ranks.

    The following example ranks the @tblItems with sort by ItemCategory. Since the ItemCodes I001, I002, I008 have same ItemCategory, these are tied, these are ranked one, and the next rank for item I003 is returned as 2, i.e. it does not skipped the gap values (2 and 3) from the previous tied items.

    SELECT 
     ItemCode
    ,ItemCategory
    ,DENSE_RANK() OVER (ORDER BY ItemCategory) AS 'DENSE_RANK()'
    FROM @tblItems
    

    Here is the output:

  • NTILE:

    Divides the result set into the number of groups specified as an argument to the function. The groups are numbered, starting at one. For each row, NTILE returns the number of the group to which the row belongs.

    The following example divides rows into 3 groups of items based on ItemCategory. Because the total number of rows is not divisible by the number of groups, the first two groups have 3 rows and the remaining groups have 2 rows each.

    SELECT 
     ItemCode
    ,ItemCategory
    ,NTILE(3) OVER(ORDER BY ItemCategory) AS 'NTILE(3)' 
    FROM @tblItems
    

    Here is the output:

    Let see another example of NTILE with group argument as 4. The following example divides rows into 4 groups of items based on ItemCategory. Now the total number of rows is divisible by the number of groups, so each group has equal number of rows (2 rows in each group).

    SELECT 
     ItemCode
    ,ItemCategory
    ,NTILE(4) OVER(ORDER BY ItemCategory) AS 'NTILE(4)' 
    FROM @tblItems
    

    Here is the output:

References:

Monday, April 11, 2016

TypeScript - How to import other TypeScript files.

As you proceed using TypeScript in large projects, you will end up with multiple typescript files. Then you may find a problem how we can use/import functions/classes from other files.

For example we have following interface in IShape.ts file.

interface IShape {
        x: number;
        y: number;
        height: number;
        width: number;
    }

And in another file Square.ts we have Square class with one of the constructor overloads is accepting that IShape interface as parameter.

class Square {
        public x: number;
        public y: number;
        public height: number;
        public width: number;

        constructor();
        constructor(obj: IShape);
        constructor(obj?: any) {
            this.x = obj && obj.x || 0
            this.y = obj && obj.y || 0
            this.height = obj && obj.height || 0
            this.width = obj && obj.width || 0;
        }

But if you try to compile this, you may get the following error:

Error TS2304 Cannot find name 'IShape'.

Because Square.ts don't know about ISquare.ts. To fix this error, we have to add reference to ISquare.ts file on the top of Square.ts.

/// 

class Square {
        public x: number;
        public y: number;
        public height: number;
        public width: number;

        constructor();
        constructor(obj: IShape);
        constructor(obj?: any) {
            this.x = obj && obj.x || 0
            this.y = obj && obj.y || 0
            this.height = obj && obj.height || 0
            this.width = obj && obj.width || 0;
        }

Now it will compile successfully.

You can add multiple lines of references to add multiple files. Just remember that path is relative to the current file.

Monday, March 28, 2016

TypeScript - Namespaces

TypeScript allows to define namespaces for logical grouping of code. Older versions of typescript use the keyword module while newer version can also used the keyword namespace which can be used in place of module keyword. Lets see namespaces in action in following example.
We have defined a class MySquare in namespace ShapesNamespace.
namespace ShapesNamespace {
    export class MySquare {
        public x: number;
        public y: number;
        public height: number;
        public width: number;
    }
}
Where namespace keyword defined namespace scope. Also notice the keyword export before the keyword class, it will expose this class to become accessible from outside of ShapesNamespace. If you do not add export keyword, you can not access this class outside of ShapesNamespace.
Now let use this class in another namespace.
namespace AnotherNamespace {
    var square1 = new ShapesNamespace.MySquare();
    square1.height = 10;
}
Here we are accessing MySquare class from AnotherNamespace with fully qualified class name. Intellisense will show available items in the code.
Another way to use code from other namespaces is to use aliases. Let see how we can use short names as aliases instead of typing more text in fully qualified names again and again.
namespace AnotherNamespace {
    import MySquareAlias = ShapesNamespace.MySquare;
    var square1 = new MySquareAlias();
    square1.height = 10;
}
We used the import keyword to set alias of ShapesNamespace.MySquare class, when we need to access that class, we can simply use its alias MySquareAlias instead of typing full name. Also intellisense works as expected.