Custom Paging In SQL Server 2012 (Denali)

SQL Server 2012 solved big problem in custom paging with easy way. lets start.

The paging now can be done using ORDER clause.

  • Return all rows sorted by FirstName
SELECT[Title]
      ,[FirstName]
      ,[MiddleName]
      ,[LastName]

  FROM [AdventureWorks2012].[Person].[Person]
  ORDER BY FirstName ASC
  • Skip the top 10 rows and return all the next rows, OFFSET <int> ROWS (NEW)
SELECT[Title]
      ,[FirstName]
      ,[MiddleName]
      ,[LastName]

  FROM [AdventureWorks2012].[Person].[Person]
  ORDER BY FirstName ASC
  OFFSET 10 ROWS
  • Paging (NEW)
DECLARE @PageSize int;
SET @PageSize=10;

DECLARE @CurrentPage int;
SET @CurrentPage=0;

SELECT[Title]
      ,[FirstName]
      ,[MiddleName]
      ,[LastName]

  FROM [AdventureWorks2012].[Person].[Person]
  ORDER BY FirstName ASC

  OFFSET (@PageSize * @CurrentPage) ROWS FETCH NEXT @PageSize ROWS ONLY 

Advertisements

Prevent saving changes that require the table to be re-created in SQL Server 2012

Problem:

When you try to save a table after you did modifications on this table you got a message:

Saving Changes is not permitted. The changes you have made require the follwoing tables to be dropped and re-created. You have either made changes to a table that can’t be re-created or enabled the option Prevent saving changes that require the table to be re-created.

 

 

Solution: