SQL Paging - ROW_NUMBER function

With the introduction of ROW_NUMBER function in SQL 2005 life is a lot easier.

DECLARE @PageNum AS INT;
DECLARE @PageSize AS INT;
SET @PageNum = 2;
SET @PageSize = 10;

WITH tblOrders AS
(
SELECT ROW_NUMBER() OVER(ORDER BY OrderDate, OrderID) AS RowNum
,OrderID
,OrderDate
,CustomerID
,EmployeeID
FROM dbo.Orders
)

SELECT *
FROM tblOrders
WHERE RowNum BETWEEN (@PageNum - 1) * @PageSize + 1
AND @PageNum * @PageSize
ORDER BY OrderDate
,OrderID;

TIPS for writing good optimized SQL Query

Here are some of the TIPS for writing good optimized
SQL Query (apart from analyzing through EXPLAIN PLAN):

## If you specify 2 tables in the FROM clause of a
SELECT statement, the parser will process the tables
from right to left, so the table name you specify last
will be processed first. In this case you have to
choose one table as driving table. Always choose the
table with less number of records as the driving
table.

## If three tables are being joined, select the
intersection tables as the driving table. The
intersection table is the table that has many tables
dependent on it.

## Never compare NULL to anything else. All
expressions return NULL if one of the operands is
NULL. This is applicable for all operators except
Concatenation operator (||).

## Use DECODE when you want to scan same rows
repetitively or join the same table repetitively.

## Always use table alias and prefix all column
names with the aliases when you are using more than
one table.

## Use NOT EXISTS in place of NOT IN.

## Join Tables in place of using EXISTS through
Sub-Query.

## Use EXISTS in place of DISTINCT.

## Never use NOT on an indexed column. Whenever
Oracle encounters a NOT on an index column, it will
perform full-table scan.

## Never use a function / calculation on an
indexed column. If there is any function is used on
an index column, optimizer will not use index. Use
some other alternative.

## There are three good reasons why it is better
to name the columns in a query rather than to use
"select * from ...".

· Network Traffic is reduced

· The Code is easier to understand

· It could save the need for changes in
future.

## It is also better to use bind variables in
queries. That way the query becomes generic and
therefore re-usable.

## It is a good practice to use a standard syntax
for wiring SQL queries. Incase, the same query has
different standard then Oracle will re-parse the
statement again.
## Combine Multiples Scans with CASE Statements.

SQL Server Convert Datetime function

MS SQL Server 2005 provides CONVERT function to change or convert the DateTime formats.

CONVERT Function syntax:

Convert (datatype, datetime string, date style)

DateType parameter accepts the following types:
nchar, nvarchar, char, varchar, binary or varbinary
DataType length is optional.

DateTime String parameter accepts the datatime variable, datetime type column name or getdate() function to convert the datetime format.

Date Style parameter accepts the style number in which you want to convert the datetime.


Following are the SQL query Syntax to convert DateTime format:

SQL query to convert datetime into mon dd yyyy hh:miAM (or PM) format:

Select convert(varchar, getdate(), 100)
SQL query to convert datetime into Default + milliseconds mon dd yyyy hh:mi:ss:mmmAM (or PM) format:

Select convert(varchar, getdate(), 101)
SQL query to convert datetime into Default mon dd yyyy format:

Select convert(varchar, getdate(), 109)
SQL query to convert datetime into Europe default + milliseconds dd mon yyyy hh:mm:ss:mmm(24h) format:

Select convert(varchar, getdate(), 113)
SQL query to convert datetime into ODBC canonical yyyy-mm-dd hh:mi:ss(24h) format:

Select convert(varchar, getdate(), 120)
SQL query to convert datetime into ODBC canonical (with milliseconds) yyyy-mm-dd hh:mi:ss.mmm(24h) format:

Select convert(varchar, getdate(), 121)
SQL query to convert datetime into ISO8601 yyyy-mm-dd Thh:mm:ss:mmm(no spaces) format:

Examples -

SELECT convert(datetime, '10/23/2016', 101) -- mm/dd/yyyy

SELECT convert(datetime, '2016.10.23', 102) -- yyyy.mm.dd

SELECT convert(datetime, '23/10/2016', 103) -- dd/mm/yyyy

SELECT convert(datetime, '23.10.2016', 104) -- dd.mm.yyyy

SELECT convert(datetime, '23-10-2016', 105) -- dd-mm-yyyy

SELECT convert(datetime, '23 OCT 2016', 106) -- dd mon yyyy

SELECT convert(datetime, 'Oct 23, 2016', 107) -- mon dd, yyyy

SELECT convert(datetime, '20:10:44', 108) -- hh:mm:ss

SELECT convert(datetime, 'Oct 23 2016 11:02:44:013AM', 109)
-- 2016-10-23 11:02:44.013

SELECT convert(datetime, '10-23-2016', 110) -- mm-dd-yyyy

SELECT convert(datetime, '2016/10/23', 111) -- yyyy/mm/dd

SELECT convert(datetime, '20161023', 112) -- yyyymmdd

SELECT convert(datetime, '23 Oct 2016 11:02:07:577', 113)
-- dd mon yyyy hh:mm:ss:mmm

SELECT convert(datetime, '20:10:25:300', 114) -- hh:mm:ss:mmm(24h)

SELECT convert(datetime, '2016-10-23 20:44:11', 120)
-- yyyy-mm-dd hh:mm:ss(24h)

SELECT convert(datetime, '2016-10-23 20:44:11.500', 121)
-- yyyy-mm-dd hh:mm:ss.mmm

Style 126 is ISO 8601 format: international standard - works
with any language setting

SELECT convert(datetime, '2008-10-23T18:52:47.513', 126)
-- yyyy-mm-ddThh:mm:ss(.mmm)

SSRS - Unwanted repeatation of text in other columns if the text is too long

I don't know if anyone else has run into this problem. I have a report that I designed in a table format with several groups. The detail row has a field that has no size limits. The field could basically contain pages of text. It only seems to be a problem when the size of of the field is larger than the size of a page when it is
exported to a PDF.

For some reason when the text is too large and field cannot fit on one page nicely it causes the other fields to repeat its text on the next page.

After hours of googling, I figured out a solution which I thought I should share it. I have used rectangles. I have put in a rectangle where I have placed my field and in that rectange I further placed a textbox and in that I have further pasted my field. This is how it stopped repeatation of text of other columns in case the text is too long to fit in one page.