July | 2011 | SQL with Manoj

SQL Server 2012 (DENALI) TSQL – New feature | OFFSET FETCH Clause (for paging/pagination)

July 28, 2011 3 comments

As per MS BOL, the new Denali’s OFFSET-FETCH Clause provides an option to fetch only a window or page of a fix set of results from the result set. OFFSET-FETCH can be used only with the ORDER BY clause.

This was the most awated feature for the frontend/GUI developers to display volumnous data in a small grid, page by page. Prior to this they used to devise not so complex but a bit complex SQL logic to display records page by page. Introduction of this feature has limited the complex logic to a few lines of single SQL statement which is much more optimized.

Let’s see how can we use this feature:

--// Example #1:

-- This will skip first 100 records and show next 20 records
SELECT BusinessEntityID,  LoginID, JobTitle
FROM HumanResources.Employee
ORDER BY BusinessEntityID
OFFSET 100 ROWS
FETCH NEXT 20 ROWS ONLY;
GO

--// Example #2:

-- Start from first record and show next 10 records
SELECT BusinessEntityID,  LoginID, JobTitle
FROM HumanResources.Employee
ORDER BY BusinessEntityID
OFFSET 0 ROWS
FETCH NEXT 10 ROWS ONLY;

-- Skip first 10 records and show next 10 records
SELECT BusinessEntityID,  LoginID, JobTitle
FROM HumanResources.Employee
ORDER BY BusinessEntityID
OFFSET 10 ROWS -- To show next page of 10 records, just change the offset by adding the page size, i.e 10.
FETCH NEXT 10 ROWS ONLY;
GO

--// Example #3:

-- To use this in front-end, the above logic can be made dynamic by using a few variables, like:
DECLARE @StartRec INT
DECLARE @PageSize INT
DECLARE @RecordEnd INT

SET @StartRec = 0
SET @PageSize = 10
SET @RecordEnd = @PageSize

WHILE @RecordEnd <> 0 -- I'm using WHILE loop to simulate it here
BEGIN
	SELECT BusinessEntityID,  LoginID, JobTitle
	FROM HumanResources.Employee
	ORDER BY BusinessEntityID
	OFFSET @StartRec ROWS
	FETCH NEXT @PageSize ROWS ONLY

	SET @RecordEnd = @@ROWCOUNT -- Exit loop at 0

	SET @StartRec = @StartRec + @PageSize
END
GO

Rules to use OFFSET FETCH (via MSDN):
1. ORDER BY is mandatory to use OFFSET and FETCH clause.
2. OFFSET clause is mandatory with FETCH. You can never use, ORDER BY … FETCH.
3. TOP cannot be combined with OFFSET and FETCH in the same query expression.
4. The OFFSET/FETCH rowcount expression can be any arithmetic, constant, or parameter expression that will return an integer value. The rowcount expression does not support scalar sub-queries.

More on MSDN, link: http://msdn.microsoft.com/en-us/library/gg699618%28v=sql.110%29.aspx

Categories: SQL Server 2012 Tags: Denali, OFFSET FETCH, SQL Pagination, SQL Paging, SQL Server 2012

SQL Server 2012 (DENALI) TSQL – New features | Built-in Functions

July 27, 2011 6 comments

Here is a list of some new function introduced in SQL Server Denali, some examples are taken from MSDN and here is the link for more details.

Logical Functions:

-- CHOOSE()
SELECT CHOOSE ( 3, 'Manager', 'Director', 'Developer', 'Tester' ) AS Result; -- Developer
SELECT CHOOSE ( 2, 'Manoj', 'Saurabh', 'Andy', 'Dave' ) AS Result; -- Saurabh
GO

-- IIF()
DECLARE @a int = 45;
DECLARE @b int = 40;
SELECT IIF ( @a > @b, 'TRUE', 'FALSE' ) AS Result; -- TRUE
GO

Note: The IIF() function was important for the developers that migrated from ACCESS to SQL Server. Introduction of these 2 functions will make their life easy.

Check the video Tutorial on CHOOSE() and IIF() functions.

Conversion Functions:

-- PARSE()
SELECT PARSE ('Monday, 13 December 2010' AS datetime2 USING 'en-US') AS Result;
SELECT PARSE ('€345,98' AS money USING 'de-DE') AS Result;
GO

-- TRY_CAST()
SELECT
CASE WHEN TRY_CAST ('test' as float) IS NULL
	THEN 'Cast failed'
	ELSE 'Cast succeeded'
END AS Result;
GO

-- TRY_CONVERT()
SELECT
CASE WHEN TRY_CONVERT (float,'test') IS NULL
THEN 'Cast failed'
ELSE 'Cast succeeded'
END AS Result;
GO

-- TRY_PARSE()
SELECT
CASE WHEN TRY_PARSE ('Aragorn' AS decimal, 'sr-Latn-CS') IS NULL
THEN 'True'
ELSE 'False'
END
AS Result;
GO

Note: The TRY_CAST(), TRY_CONVERT() & TRY_PARSE() functions added exception handling to the existing CAST(), CONVERT() and new PARSE() function respectively. Now developers can fully rely on these 2 functions while type converting values and defaulting them to something else on any execption.

Analytic Functions:

USE AdventureWorks2008R2;
GO

-- FIRST_VALUE() and LAST_VALUE()
SELECT 
	Name, ListPrice, ProductSubcategoryID,
	FIRST_VALUE (Name) OVER (PARTITION BY ProductSubcategoryID ORDER BY ListPrice ASC) AS LeastExpensive,
	LAST_VALUE (Name) OVER (PARTITION BY ProductSubcategoryID ORDER BY ListPrice ASC) AS LeastExpensive
FROM Production.Product;

-- LAG()
SELECT 
	BusinessEntityID, YEAR(QuotaDate) AS SalesYear, SalesQuota AS CurrentQuota,
	LAG (SalesQuota, 1, 0) OVER (ORDER BY YEAR(QuotaDate)) AS PreviousQuota
FROM Sales.SalesPersonQuotaHistory
WHERE BusinessEntityID = 275 and YEAR(QuotaDate) IN ('2005','2006');

-- LEAD()
SELECT 
	TerritoryName, BusinessEntityID, SalesYTD,
	LEAD (SalesYTD, 1, 0) OVER (PARTITION BY TerritoryName ORDER BY SalesYTD DESC) AS PrevRepSales
FROM Sales.vSalesPerson
WHERE TerritoryName IN (N'Northwest', N'Canada')
ORDER BY TerritoryName;

Note: The LAG() and LEAD() functions are already in Oracle and by introducing them in MS SQL Server’s Denali version is very much welcomed by TSQL developers. Plus the addition of FIRST_VALUE(), LAST_VALUE(), PERCENTILE_XXX(), etc will really add value and provide zeal to programmers in TSQL development, just like Ranking functions and Cube/Rollup did in 2005(08).

Some other new functions introduced are:
– CUME_DIST()
– PERCENTILE_CONT()
– PERCENTILE_DISC()
– PERCENT_RANK()

More details here on MS BOL: http://technet.microsoft.com/en-us/library/hh213234%28SQL.110%29.aspx

Categories: SQL Server 2012 Tags: Denali, FIRST_VALUE, LAG, LAST_VALUE, LEAD, SQL Server 2012, TRY_CAST, TRY_CONVERT, TRY_PARSE

DB Basics – Is SELECT a DML (Data Manipulation Language)?

July 26, 2011 1 comment

DML or Data Manipulation Langauge as the term suggest represents those SQL statements that manipulates the data in a database. Thus these langauges allows users to INSERT, UPDATE & DELETE the data in a particular database. Other than this the much debatable SELECT statement may or may not be considered as DML upon its usage.

A simple SELECT statement which fetches data from a table is a read-only language and cannot be called as DML.

But a modified version of SELECT i.e. ‘SELECT INTO’ can fall into the DML segment. The ‘SELECT INTO’ can be used to create a Table and insert records fetched from the SELECT statement.

Also, we can manipulate the data for reporting purpose while retriving by using the SELECT statement. The data in underlying tables is unchanged but on the frontend you get a view of modified data, like:

USE [AdventureWorks]
GO

SELECT ContactID, Title,
	FirstName, MiddleName, LastName,
	FirstName + ' ' + ISNULL(MiddleName,'') + ' ' + LastName as [FullName] -- Name manipulated by joining 3 part names.
FROM Person.Contact

SELECT SalesOrderID, SalesOrderDetailID,
	OrderQty, UnitPrice,
	OrderQty * UnitPrice as [TotalPrice] -- Manipulated cost by calculating it for total items purchased.
FROM Sales.SalesOrderDetail

Thus SELECT also comes under DML and following is the list of all DMLs:
1. SELECT {COLUMN LIST} [INTO {TABLE_NAME}] [WHERE {WHERE condition}]
2. INSERT INTO {TABLE_NAME} VALUES (SET of Values)
3. UPDATE {TABLE_NAME} SET [WHERE {WHERE condition}]
4. DELETE FROM {TABLE_NAME} [WHERE {WHERE condition}]

Categories: DB Concepts Tags: Data Manipulation Langauge, DML, SELECT, SELECT INTO

SQL Server 2012 (DENALI) TSQL – New Feature | SEQUENCES

July 26, 2011 2 comments

As per MS BOL a Sequence is a user-defined schema-bound object that generates a sequence of numeric values according to the specification with which the sequence was created. The sequence of numeric values is generated in an ascending or descending order at a defined interval and may cycle (repeat) as requested.

--// Create a simple Sequence
CREATE SEQUENCE CountBy1
	START WITH 1
	INCREMENT BY 1 ;
GO

-- Assign new ID sorted by First Name:
SELECT BusinessEntityID, FirstName, MiddleName, LastName,
	NEXT VALUE FOR CountBy1 OVER(ORDER BY FirstName) as New_ID
FROM Person.Person;
GO
-- If you run the above query again, it won't start from 1 but starts after the max value it ended in its first run.

--// Create a Cyclic Sequence
CREATE SEQUENCE CountBy5 AS tinyint
	START WITH 1
	INCREMENT BY 1
	MINVALUE 1
	MAXVALUE 5
	CYCLE ;
GO

-- Assign Persons to 5 different sections:
SELECT BusinessEntityID, FirstName, MiddleName, LastName,
	NEXT VALUE FOR CountBy5 OVER(ORDER BY FirstName, LastName) as New_Section
FROM Person.Person;
GO

--// Final cleanup
DROP SEQUENCE CountBy1
DROP SEQUENCE CountBy5
GO

More on MSDN: http://technet.microsoft.com/en-us/library/ff878058%28SQL.110%29.aspx

Categories: SQL Server 2012 Tags: CREATE SEQUENCE, Cyclic Sequence, Denali, NEXT VALUE FOR, OVER, SQL Server 2012

DIRTY reads and PHANTOM reads – SQL Server

July 20, 2011 15 comments

–> DIRTY READS: Reading uncommitted modifications are call Dirty Reads. Values in the data can be changed and rows can appear or disappear in the data set before the end of the transaction, thus getting you incorrect or wrong data.

This happens at READ UNCOMMITTED transaction isolation level, the lowest level. Here transactions running do not issue SHARED locks to prevent other transactions from modifying data read by the current transaction. This also do not prevent from reading rows that have been modified but not yet committed by other transactions.

To prevent Dirty Reads, READ COMMITTED or SNAPSHOT isolation level should be used.

–> PHANTOM READS: Data getting changed in current transaction by other transactions is called Phantom Reads. New rows can be added by other transactions, so you get different number of rows by firing same query in current transaction.

In REPEATABLE READ isolation levels Shared locks are acquired. This prevents data modification when other transaction is reading the rows and also prevents data read when other transaction are modifying the rows. But this does not stop INSERT operation which can add records to a table getting modified or read on another transaction. This leads to PHANTOM reads.

PHANTOM reads can be prevented by using SERIALIZABLE isolation level, the highest level. This level acquires RANGE locks thus preventing READ, Modification and INSERT operation on other transaction until the first transaction gets completed.

>> Check & Subscribe my [YouTube videos] on SQL Server.