Yet another blog

Category Archives: Database

What is PIVOT and UNPIVOT?

You can use the PIVOT and UNPIVOT relational operators to change a table-valued expression into another table. PIVOT rotates a table-valued expression by turning the unique values from one column in the expression into multiple columns in the output, and performs aggregations where they are required on any remaining column values that are wanted in the final output. UNPIVOT performs the opposite operation to PIVOT by rotating columns of a table-valued expression into column values.

The following example returns the number of purchase orders placed by employee IDs 164, 198, 223, 231, and 233, categorized by vendor ID.

USE AdventureWorks
SELECT VendorID, [164] AS Emp1, [198] AS Emp2, [223] AS Emp3, [231] AS Emp4, [233] AS Emp5
(SELECT PurchaseOrderID, EmployeeID, VendorID
FROM Purchasing.PurchaseOrderHeader) AS p
COUNT (PurchaseOrderID)
FOR EmployeeID IN
( [164], [198], [223], [231], [233] )
) AS pvt

Here is a partial result set:

VendorID Emp1 Emp2 Emp3 Emp4 Emp5
1 4 3 5 4 4
2 4 1 5 5 5
3 4 3 5 4 4
4 4 2 5 5 4
5 5 1 5 5 5

To unpivot the table, assume the result set produced in the previous example is stored as pvt. The query looks like the following.

–Create the table and insert values as portrayed in the previous example.
CREATE TABLE pvt (VendorID int, Emp1 int, Emp2 int,
Emp3 int, Emp4 int, Emp5 int);
INSERT INTO pvt VALUES (1,4,3,5,4,4);
INSERT INTO pvt VALUES (2,4,1,5,5,5);
INSERT INTO pvt VALUES (3,4,3,5,4,4);
INSERT INTO pvt VALUES (4,4,2,5,5,4);
INSERT INTO pvt VALUES (5,5,1,5,5,5);
–Unpivot the table.
SELECT VendorID, Employee, Orders
(SELECT VendorID, Emp1, Emp2, Emp3, Emp4, Emp5
FROM pvt) AS p
(Orders FOR Employee IN
(Emp1, Emp2, Emp3, Emp4, Emp5)
)AS unpvt

Here is a partial result set:

VendorID Employee Orders
1 Emp1 4
1 Emp2 3
1 Emp3 5
1 Emp4 4
1 Emp5 4
2 Emp1 4
2 Emp2 1
2 Emp3 5
2 Emp4 5
2 Emp5 5


What does COLLATE Keyword in SQL signify?

Collation refers to a set of rules that determine how data is sorted and compared.
Character data is sorted using rules that define the correct character sequence,
with options for specifying
1) case-sensitivity,
2) accent marks,
3) kana character types and
4)character width.

Case sensitivity

If A and a, B and b, etc. are treated in the same way then it is case-insensitive. A computer treats A and a differently because it uses ASCII code to differentiate the input. The ASCII value of A is 65, while a is 97. The ASCII value of B is 66 and b is 98.

Accent sensitivity

If a and á, o and ó are treated in the same way, then it is accent-insensitive. A computer treats a and á differently because it uses ASCII code for differentiating the input. The ASCII value of a is 97 and áis 225. The ASCII value of o is 111 and ó is 243.

Kana Sensitivity

When Japanese kana characters Hiragana and Katakana are treated differently, it is called Kana sensitive.

Width sensitivity

When a single-byte character (half-width) and the same character when represented as a double-byte character (full-width) are treated differently then it is width sensitive.

What are “Global” and “Local” cursors?


Specifies that the scope of the cursor is local to the batch, stored procedure, or trigger in which the cursor was created. The cursor name is only valid within this scope. The cursor can be referenced by local cursor variables in the batch, stored procedure, or trigger, or a stored procedure OUTPUT parameter. An OUTPUT parameter is used to pass the local cursor back to the calling batch, stored procedure, or trigger, which can assign the parameter to a cursor variable to reference the cursor after the stored procedure terminates. The cursor is implicitly deallocated when the batch, stored procedure, or trigger terminates, unless the cursor was passed back in an OUTPUT parameter. If it is passed back in an OUTPUT parameter, the cursor is deallocated when the last variable referencing it is deallocated or goes out of scope.


Specifies that the scope of the cursor is global to the connection. The cursor name can be referenced in any stored procedure or batch executed by the connection. The cursor is only implicitly deallocated at disconnect.

If neither GLOBAL or LOCAL is specified, the default is controlled by the setting of the default to local cursor database option. In SQL Server version 7.0, this option defaults to FALSE to match earlier versions of SQL Server, in which all cursors were global. The default of this option may change in future versions of SQL Server.

What are the different Cursor Types?


Specifies that the cursor can only be scrolled from the first to the last row. FETCH NEXT is the only supported fetch option. If FORWARD_ONLY is specified without the STATIC, KEYSET, or DYNAMIC keywords, the cursor operates as a DYNAMIC cursor. When neither FORWARD_ONLY nor SCROLL is specified, FORWARD_ONLY is the default, unless the keywords STATIC, KEYSET, or DYNAMIC are specified. STATIC, KEYSET, and DYNAMIC cursors default to SCROLL. Unlike database APIs such as ODBC and ADO, FORWARD_ONLY is supported with STATIC, KEYSET, and DYNAMIC Transact-SQL cursors.


Defines a cursor that makes a temporary copy of the data to be used by the cursor. All requests to the cursor are answered from this temporary table in tempdb; therefore, modifications made to base tables are not reflected in the data returned by fetches made to this cursor, and this cursor does not allow modifications.


Specifies that the membership and order of rows in the cursor are fixed when the cursor is opened. The set of keys that uniquely identify the rows is built into a table in tempdb known as the keyset.


Defines a cursor that reflects all data changes made to the rows in its result set as you scroll around the cursor. The data values, order, and membership of the rows can change on each fetch. The ABSOLUTE fetch option is not supported with dynamic cursors.


Specifies a FORWARD_ONLY, READ_ONLY cursor with performance optimizations enabled. FAST_FORWARD cannot be specified if SCROLL or FOR_UPDATE is also specified.


Prevents updates made through this cursor. The cursor cannot be referenced in a WHERE CURRENT OF clause in an UPDATE or DELETE statement. This option overrides the default capability of a cursor to be updated.

What are the steps to create a cursor?

Steps for creating a cursor are

1.Declare a cursor

     FOR select_statement
     [ FOR UPDATE [ OF column_name [ ,...n ] ] ]

2. Open the cursor

OPEN cursor_name

3. Fetch the cursor

FETCH cursor_name INTO ...

4. Perform the operation required

5. Close the cursor

 CLOSE cursor_name

What are cursors and what are the situations you will use them

Cursors provide a mechanism by which a database client iterates over the records in a database.

Cursor are for keeping a stable resultset which you can retrieve row-by-row. They are implicitly created when your query is run, and closed when it’s finished.

Of course keeping such a resultset requires some resources: locks, latches, memory, even disk space.

Keeping a cursor open is like keeping a fridge door open

You don’t do it for hours without necessity, but it does not mean you should never open your fridge.

That means that:

  • You don’t get your results row-by-row and sum them: you call the SQL‘s SUM instead.
  • You don’t execute whole query and get the first results from the cursor: you append a rownum <= 10 condition to your query

Cursors are useful when

1) you need to do something that you cannot do with a set operation, or

2) it doesn’t make sense to do the same work by making iterative calls from the application layer.

3) Or sometimes you have a procedure that must remain on the database layer, and you simply can’t break back out to the app layer midstream to iterate over some result set.

The bottom line is, avoid them if you possibly can, and if you can’t, use them minimally and wisely.

select the first record in a given set of rows

If you need to select top 1 row or select only the first record from a SQL Server database table,

ou can use the following SQL query

To select first 10 or top 10 just replace the 1 with 10

What is crossjoin

CROSS JOIN will return all records where each row from the first table is combined with each row from the second table. Which also mean CROSS JOIN returns the Cartesian product of the sets of rows from the joined tables.

A CROSS JOIN can be specified in two ways: using the JOIN syntax or by listing the tables in the FROM clause separated by commas without using a WHERE clause to supply join criteria.

CROSS JOIN syntax:




Different types of join in SQL

The SQL JOIN clause is used whenever we have to select data from 2 or more tables.

To be able to use SQL JOIN clause to extract data from 2 (or more) tables, we need a relationship between certain columns in these tables.

We are going to illustrate our SQL JOIN example with the following 2 tables:


CustomerID FirstName LastName Email DOB Phone
1 John Smith 2/4/1968 626 222-2222
2 Steven Goldfish 4/4/1974 323 455-4545
3 Paula Brown 5/24/1978 416 323-3232
4 James Smith 20/10/1980 416 323-8888


CustomerID Date SaleAmount
2 5/6/2004 $100.22
1 5/7/2004 $99.95
3 5/7/2004 $122.95
3 5/13/2004 $100.00
4 5/22/2004 $555.55

As you can see those 2 tables have common field called CustomerID and thanks to that we can extract information from both tables by matching their CustomerID columns.

Consider the following SQL statement:

SELECT Customers.FirstName, Customers.LastName, SUM(Sales.SaleAmount) AS SalesPerCustomer
FROM Customers, Sales
WHERE Customers.CustomerID = Sales.CustomerID
GROUP BY Customers.FirstName, Customers.LastName

The SQL expression above will select all distinct customers (their first and last names) and the total respective amount of dollars they have spent.
The SQL JOIN condition has been specified after the SQL WHERE clause and says that the 2 tables have to be matched by their respective CustomerID columns.

Here is the result of this SQL statement:

FirstName LastName SalesPerCustomers
John Smith $99.95
Steven Goldfish $100.22
Paula Brown $222.95
James Smith $555.55

The SQL statement above can be re-written using the SQL JOIN clause like this:

SELECT Customers.FirstName, Customers.LastName, SUM(Sales.SaleAmount) AS SalesPerCustomer
FROM Customers JOIN Sales
ON Customers.CustomerID = Sales.CustomerID
GROUP BY Customers.FirstName, Customers.LastName

There are 2 types of SQL JOINS – INNER JOINS and OUTER JOINS. If you don’t put INNER or OUTER keywords in front of the SQL JOIN keyword, then INNER JOIN is used. In short “INNER JOIN” = “JOIN” (note that different databases have different syntax for their JOIN clauses).

The INNER JOIN will select all rows from both tables as long as there is a match between the columns we are matching on. In case we have a customer in the Customers table, which still hasn’t made any orders (there are no entries for this customer in the Sales table), this customer will not be listed in the result of our SQL query above.

If the Sales table has the following rows:

CustomerID Date SaleAmount
2 5/6/2004 $100.22
1 5/6/2004 $99.95

And we use the same SQL JOIN statement from above:

SELECT Customers.FirstName, Customers.LastName, SUM(Sales.SaleAmount) AS SalesPerCustomer
FROM Customers JOIN Sales
ON Customers.CustomerID = Sales.CustomerID
GROUP BY Customers.FirstName, Customers.LastName

We’ll get the following result:

FirstName LastName SalesPerCustomers
John Smith $99.95
Steven Goldfish $100.22

Even though Paula and James are listed as customers in the Customers table they won’t be displayed because they haven’t purchased anything yet.

But what if you want to display all the customers and their sales, no matter if they have ordered something or not? We’ll do that with the help of SQL OUTER JOIN clause.

The second type of SQL JOIN is called SQL OUTER JOIN and it has 2 sub-types called LEFT OUTER JOIN and RIGHT OUTER JOIN.

The LEFT OUTER JOIN or simply LEFT JOIN (you can omit the OUTER keyword in most databases), selects all the rows from the first table listed after the FROM clause, no matter if they have matches in the second table.

If we slightly modify our last SQL statement to:

SELECT Customers.FirstName, Customers.LastName, SUM(Sales.SaleAmount) AS SalesPerCustomer
FROM Customers LEFT JOIN Sales
ON Customers.CustomerID = Sales.CustomerID
GROUP BY Customers.FirstName, Customers.LastName

and the Sales table still has the following rows:

CustomerID Date SaleAmount
2 5/6/2004 $100.22
1 5/6/2004 $99.95

The result will be the following:

FirstName LastName SalesPerCustomers
John Smith $99.95
Steven Goldfish $100.22
Paula Brown NULL
James Smith NULL

As you can see we have selected everything from the Customers (first table). For all rows from Customers, which don’t have a match in the Sales (second table), the SalesPerCustomer column has amount NULL (NULL means a column contains nothing).

The RIGHT OUTER JOIN or just RIGHT JOIN behaves exactly as SQL LEFT JOIN, except that it returns all rows from the second table (the right table in our SQL JOIN statement).

What is a DDL, DML and DCL

The Data Definition Language (DDL) is used to create and destroy databases and database objects. These commands will primarily be used by database administrators during the setup and removal phases of a database project. Let’s take a look at the structure and usage of four basic DDL commands:

  1. Create
  2. Drop
  3. Alter
  4. Use

The Data Manipulation Language (DML) is used to retrieve, insert and modify database information. These commands will be used by all database users during the routine operation of the database. The Basic DML commands are

  1. Select
  2. Insert
  3. Delete
  4. Update

The Data Control Language (DCL) is used to  control your  DML and DDL statements so that your data is protected and has consistency. The Basic DCL commands are

  1. Grant
  2. Revoke