Using OFFSET and FETCH

 

Introduction

Many times while developing our applications we feel the need of pagination, where our User Interface (UI) has to list a number of records and fetching them all at once and listing is not a feasible option because of the following reasons -

1. High utilization of the network bandwidth which if on a higher side might even choke up the bandwidth.
2. User is not guaranteed to see the latest details in a multi user environment.
3. High need of RAM on local machine for caching/processing.

So, the solution which is generally implemented in this situation was to fetch only the relevant records from the backend. Until Denali the following were the options used to counter this situation -

1. Before SQL 2005 – ORDER BY clause in combination with TOP
2. From SQL 2005 onwards – ROW_NUMBER() function with a WHERE clause

And from Denali, we can use ORDER BY clause in combination with OFFSET and FETCH

Implementation

Let’s see how we can get the same output using all of the 3 ways explained above and try to fetch records from 3 to 4 assuming page size to be 2.

Total Records are as under

1. ORDER BY + TOP

DECLARE @PageNo AS INT

DECLARE @PageSize AS INT

SET @PageNo = 2

SET @PageSize = 2

SELECT

  * 

FROM (SELECT

        TOP (@PageSize) *

      FROM (SELECT 

              TOP (@PageNo * @PageSize) *

            FROM 

              dbo.DemoTable DT

            ORDER BY

              ID ASC) X

      ORDER BY

        X.ID DESC) Y

ORDER BY

  Y.ID ASC

Output

 

2. ROW_NUMBER() + WHERE

DECLARE @PageNo AS INT

DECLARE @PageSize AS INT

SET @PageNo = 2

SET @PageSize = 2

;WITH Data AS (  

SELECT

  *,

  ROW_NUMBER()OVER(ORDER BY DT.ID ASC) Rno

FROM

  dbo.DemoTable DT

)

SELECT 

  ID,NAME,CITY

FROM

  Data

WHERE

  Rno BETWEEN ((@PageNo - 1) * @PageSize) + 1 AND ((@PageNo - 1) * @PageSize) + @PageSize

Output

 

3. FETCH + OFFSET

DECLARE @PageNo AS INT

DECLARE @PageSize AS INT

SET @PageNo = 2

SET @PageSize = 2

SELECT 

  *

FROM 

  dbo.DemoTable DT

ORDER BY 

  DT.ID

OFFSET ((@PageNo - 1) * @PageSize)) ROWS

FETCH NEXT @PageSize ROWS ONLY

Output

Performance

I did a small test using all the 3 ways and have found the Denali (OFFSET and FETCH) way the best performing one followed by the ROW_NUMBER().

 Conclusion

I would prefer using the Denali way just for 2 simple reasons -

• Simplicity of code
  
• Better performance

Remarks

1. The Denali code is based on SQL Server Denali CTP 1 and might change after further releases.

How to find out which Table is not having rows in SQL Server?

Introduction

Many times while tuning our production databases we might try to find out the list of tables not having even a single row of data. Today, I am going to show a simple script which could be used to get a list of tables having ZERO rows.

Script

USE DBName --Change this to the DB Name you want to script for.

GO

DECLARE @TableRowCount TABLE

( 

    TableName VARCHAR(255), 

    RowCnt INT 

) 

INSERT @TableRowCount 

  EXEC sp_msForEachTable 'SELECT PARSENAME(''?'', 1),COUNT(*) FROM ?' 

SELECT 

    * 

FROM 

  @TableRowCount 

WHERE

    RowCnt = 0     

ORDER BY 

  RowCnt 

How to find out which Table is not having rows in SQL Server?

Introduction

Many times while tuning our production databases we might try to find out the list of tables not having even a single row of data. Today, I am going to show a simple script which could be used to get a list of tables having ZERO rows.

Script

USE DBName --Change this to the DB Name you want to script for.

GO

DECLARE @TableRowCount TABLE

( 

    TableName VARCHAR(255), 

    RowCnt INT 

) 

INSERT @TableRowCount 

  EXEC sp_msForEachTable 'SELECT PARSENAME(''?'', 1),COUNT(*) FROM ?' 

SELECT 

    * 

FROM 

  @TableRowCount 

WHERE

    RowCnt = 0     

ORDER BY 

  RowCnt 

How to find a string value in all the string columns of a table/view in SQL Server ?

 

Introduction

I am sure many times we all might have come across situations where we need to search/find a string value in all the string columns of a given table/view in SQL Server and return the matching rows from that table.

Unfortunately, we do not have any straight forward way to do this till date AFAIK. Hence, the below script can prove to be quite handy in this situation -

USE DBName --Replace with the DB in which the table resides

GO

--Declare variables and initialize them

DECLARE @TableSchema AS VARCHAR(50) = 'SchemaName' --Replace this with the name of Schema of the Table/View

DECLARE @TableName AS VARCHAR(50) = 'TableName' --Replace this with the name of the Table/View to search

DECLARE @SearchString AS VARCHAR(50) = 'SearchString' --Replace this with actual SearchString

DECLARE @Qry AS NVARCHAR(MAX)

DECLARE @Columns AS VARCHAR(MAX)

--Prepare the columns

SET @Columns = STUFF((

SELECT 

  '+' + CASE WHEN IS_NULLABLE = 'YES' THEN 'ISNULL(' + C.COLUMN_NAME + ','''')' ELSE C.COLUMN_NAME END

FROM 

  INFORMATION_SCHEMA.COLUMNS C

WHERE

  C.TABLE_SCHEMA = COALESCE(@TableSchema,C.TABLE_SCHEMA)

  AND C.TABLE_NAME = COALESCE(@TableName,C.TABLE_NAME)

  AND C.DATA_TYPE IN ('CHAR','NCHAR','NTEXT','NVARCHAR','TEXT','VARCHAR')

FOR XML PATH('')),1,1,'')

--Prepare the Query

SET @Qry = N' SELECT ' +

            '  * ' +

            ' FROM ' +

            @TableSchema + '.' + @TableName +

            ' WHERE  ' +

              @Columns + ' LIKE ''%' + @SearchString + '%'''  

--Execute the Query

EXEC SP_EXECUTESQL @Qry

Please note that the above script works only for the following column types - CHAR,NCHAR,NTEXT,NVARCHAR,TEXT,VARCHAR

Njoy searching….

T-SQL to find Fragmented Indexes

Fragmentation of Indexes is one of the reason for low performing queries resulting in a poor application performance.

Today, I will present a simple script which will help in identifying the level of fragmentation in a Database.

--Replace this with the name of the Database for which we want to find the fragmentation.

USE <DBName> 

GO

DECLARE @DBName AS VARCHAR(10) = 'DBName'

DECLARE @DBID AS INT = DB_ID(@DBName)

DECLARE @AllowedFragmentation AS INT = 70 --A acceptable value in Percent(%) for fragmentation.

DECLARE @Qry AS VARCHAR(MAX)

SELECT

  --@DBID [DBID],

  --@DBName DBName,

  PS.OBJECT_ID ObjectID,

  COALESCE(T.name,V.name) ObjectName,

  PS.index_id,

  I.name IndexName,

  PS.page_count AS TotalPages,

  (PS.page_count * 8)/1024.0 as TotalMB,

  ((PS.page_count * 8)/1024.0) * (PS.avg_fragmentation_in_percent/100) as ReclaimableMB,

  PS.avg_fragmentation_in_percent AvgFragmentationPercent

FROM

  sys.dm_db_index_physical_stats (@DBID, NULL, NULL, NULL, NULL) AS PS

INNER JOIN sys.indexes AS I

  ON PS.OBJECT_ID = I.OBJECT_ID

    AND PS.index_id = I.index_id  

LEFT JOIN sys.tables T

  ON T.object_id = I.object_id

LEFT JOIN sys.views V

  ON V.object_id = I.object_id

WHERE

  PS.database_id = @DBID

  AND PS.avg_fragmentation_in_percent > @AllowedFragmentation 

ORDER BY    

  PS.avg_fragmentation_in_percent DESC 

Here, I have considered 70% fragmentation as an acceptable level of fragmentation.

Hope, this helps.

SHRINKFILE and TRUNCATE Log File in SQL Server 2008

Introduction

 

You know there is always an issue - the log file growing very fast and big.  If you have plenty of storage, then this might not be a problem for you.  Anyway, this is no exception in the latest version of SQL, we still have to do something to truncate and shrink these files.

 

Implementation

 

1)   Let’s first check the log file size.

 

SELECT

  --DB_NAME(database_id) AS DatabaseName,

  --Physical_Name,

  Name AS Logical_Name,

  (size*8)/1024 SizeMB

FROM

  sys.master_files

WHERE

  DB_NAME(database_id) = 'tempdb'

GO

 

Output

 

 

2)   Now truncate the log file.

 

USE tempdb;

GO

-- Truncate the log by changing the database recovery model to SIMPLE.

ALTER DATABASE tempdb

SET RECOVERY SIMPLE WITH NO_WAIT;

GO

-- Shrink the truncated log file to 1 MB.

DBCC SHRINKFILE(tempdb_log, 1);  --file_name is the logical name of the file to be shrink

GO

-- Reset the database recovery model.

ALTER DATABASE tempdb

SET RECOVERY FULL WITH NO_WAIT;

GO

 

3)   Let’s check the log file size.

 

SELECT

  --DB_NAME(database_id) AS DatabaseName,

  --Physical_Name,

  Name AS Logical_Name,

  (size*8)/1024 SizeMB

FROM

  sys.master_files

WHERE

  DB_NAME(database_id) = 'tempdb'

GO

 

Output

 

 

Consider the following information when you plan to shrink a file:

 

• Make a full backup of your database before shrink the database file.
• From setting the database to simple recovery, shrinking the file and once again setting in full recovery, you are in fact losing your valuable log data and will be not able to restore point in time. Not only that, you will also not able to use subsequent log files.
• A shrink operation is most effective after an operation that creates lots of unused space, such as a truncate table or a drop table operation.
• Most databases require some free space to be available for regular day-to-day operations. If you shrink a database repeatedly and notice that the database size grows again, this indicates that the space that was shrunk is required for regular operations. In these cases, repeatedly shrinking the database is a wasted operation. In this case, you should consider increasing the Growth Rate of your Database to keep the performance under control.
• A shrink operation does not preserve the fragmentation state of indexes in the database, and generally increases fragmentation to a degree. This is another reason not to repeatedly shrink the database.

Reference: http://technet.microsoft.com/en-us/library/ms189493.aspx