Paging re-imagined in SQL Server

When we have huge records in database, it is always wise to implement paging instead of querying all the data at once. Gone are the days were we wrote long line of code to implement paging. With SQL Server 2012, we can do that in just few lines. Here is a quick sample script to query 10 records after 50th record.

Declare @PageIndex Int    -- current page index
Declare @PageSize Int    -- fetch this much record
Declare @Offset Int    -- number of rows to skip
Set @PageIndex=5
Set @PageSize=10
Set @Offset = @PageIndex * @PageSize
Select * From DemoUser
    Order By Id Offset @Offset Rows
    Fetch Next @PageSize Rows Only


Reference: OFFSET FETCH Clause

Hope this helps.

Populate dummy records in SQL Server

Last day, I was playing with paging in SQL Server and had to create some test data to test the same. Here goes the script to generate test data.

DECLARE @UserTable TABLE (Id int, Name nvarchar(150))
;WITH cte AS
    SELECT 1 Id
    UNION all
    SELECT Id + 1
    FROM cte WHERE   Id + 1 <= 100000
INSERT INTO @UserTable(Id,Name)
    SELECT Id, Concat('Name_', convert(int, convert (varbinary(4), NEWID(), 1))) AS Name FROM cte c 
SELECT u.Id, u.Name
    Into DemoUser
    FROM    @UserTable u


Hope this helps.

Fix: Login failed for user IFC\WIN5177$ in ASP.NET MVC

We recently moved an ASP.NET MVC website to a new provider and found out that the MVC application is no longer connecting to SQL Server database. That was surprising because it was up and running with previous provider without any issues.

Below is the exception I got while connecting to SQL Server:

Login failed for user 'IFC\WIN5177$'.

That took quite sometime to figure out what was the issue. And, I finally fixed by setting Integrated Security=False; in the connection string,

Hope this helps.