Pagination in SQL server

Earlier version
================================
DECLARE @PageNumber AS INT=111
DECLARE @NumInPage AS INT=10

SELECT * FROM
(SELECT  ROW_NUMBER() OVER (ORDER BY StoreOrderID) [RANK],*
FROM dbo.StoreOrder )A
WHERE
A.[RANK] BETWEEN  (@PageNumber-1)*@NumInPage+1
AND (@PageNumber*@NumInPage)




SQL server 2012
====================================
DECLARE @PageNumber AS INT=111
DECLARE @NumInPage AS INT=10

SELECT  * FROM dbo.StoreOrder
ORDER BY StoreOrderID
OFFSET ((@PageNumber - 1) * @NumInPage) ROWS
FETCH NEXT @NumInPage ROWS ONLY;

XML representation of data

XML RAW

SELECT * FROM Sales.Customer A
INNER JOIN Sales.MyOrders B ON A.CustomerID = B.customerId
FOR XML RAW

<row CustomerID="100" StoreID="1252" TerritoryID="6" AccountNumber="AW00000100" rowguid="E789F6DD-3159-4DDC-81A8-8571C571656E" ModifiedDate="2008-10-13T11:15:07.263" orderid="1" customerId="100"/>

<row CustomerID="200" StoreID="1186" TerritoryID="1" AccountNumber="AW00000200" rowguid="7C96C878-A038-4B2F-825F-016C922D6407" ModifiedDate="2008-10-13T11:15:07.263" orderid="2" customerId="200"/>

<row CustomerID="200" StoreID="1186" TerritoryID="1" AccountNumber="AW00000200" rowguid="7C96C878-A038-4B2F-825F-016C922D6407" ModifiedDate="2008-10-13T11:15:07.263" orderid="3" customerId="200"/>

<row CustomerID="200" StoreID="1186" TerritoryID="1" AccountNumber="AW00000200" rowguid="7C96C878-A038-4B2F-825F-016C922D6407" ModifiedDate="2008-10-13T11:15:07.263" orderid="4" customerId="200"/>


XML AUTO

SELECT * FROM Sales.Customer A
INNER JOIN Sales.MyOrders B ON A.CustomerID = B.customerId
FOR XML AUTO

<A CustomerID="100" StoreID="1252" TerritoryID="6" AccountNumber="AW00000100" rowguid="E789F6DD-3159-4DDC-81A8-8571C571656E" ModifiedDate="2008-10-13T11:15:07.263">
<B orderid="1" customerId="100"/>
</A>
<A CustomerID="200" StoreID="1186" TerritoryID="1" AccountNumber="AW00000200" rowguid="7C96C878-A038-4B2F-825F-016C922D6407" ModifiedDate="2008-10-13T11:15:07.263">
<B orderid="2" customerId="200"/>
<B orderid="3" customerId="200"/>
<B orderid="4" customerId="200"/>
</A>

XML AUTO, ELEMENTS

SELECT * FROM Sales.Customer A

INNER JOIN Sales.MyOrders B ON A.CustomerID = B.customerId

FOR XML RAW

--FOR XML AUTO

FOR XML AUTO,Elements

<A>

<CustomerID>100</CustomerID>

<StoreID>1252</StoreID>

<TerritoryID>6</TerritoryID>

<AccountNumber>AW00000100</AccountNumber>

<rowguid>E789F6DD-3159-4DDC-81A8-8571C571656E</rowguid>

<ModifiedDate>2008-10-13T11:15:07.263</ModifiedDate>

<B>

<orderid>1</orderid>

<customerId>100</customerId>

</B>

</A>

<A>

<CustomerID>200</CustomerID>

<StoreID>1186</StoreID>

<TerritoryID>1</TerritoryID>

<AccountNumber>AW00000200</AccountNumber>

<rowguid>7C96C878-A038-4B2F-825F-016C922D6407</rowguid>

<ModifiedDate>2008-10-13T11:15:07.263</ModifiedDate>

<B>

<orderid>2</orderid>

<customerId>200</customerId>

</B>

<B>

<orderid>3</orderid>

<customerId>200</customerId>

</B>

<B>

<orderid>4</orderid>

<customerId>200</customerId>

</B>

</A>

Difference between temporary table and table variable

1. Creation 

Temporary tables are created.

There are 2 types of temp tables
          1. local  - prefix with #
          2. global - prefix with ##

    create table #Temp1
    (
      column1 int not null
    )

   create table ##Temp1
    (
      column1 int not null
    )

Table variable are decarled as opposed to being created. They are name with the sign @ as a prefix.
 
    declare @TableTemp as table
      (
        column1 int not null
     )



2. Scope

It is possible to create temporary local table in separate session with same name.
But it is not possible to create temporary global table in separate session with same name, there will be only one global temporary table in the whole database instance.

Local temporary table are visible throughout the level that created them, across batches, and in all inner levels of the call stack. If you dont drop the temporary table explicitly, it is destroyed when the level that created it terminates. But table variable is only visible to only the batch that declared them and are destroyed automatically at the end of the batch.

select column1 from #Temp1; -- it works from same session
select column1 from @Temp1; -- it works from same batch and same session

exec ('select column1 from #Temp1'); -- it works from same session

exec('select column1 from @Temp1'); -- it wont works even if it is in the same batch and session


3. Physical allocation

Both temporary table and table variable are created in tempdb. Here to be noted that, common table expression (CTE) is not a temp table, so you will not find it in tempdb.

select * from tempdb.sys.objects where name like '%#%'

4. Transaction

Temporary tables are similar to regular tables. Changes applied to a temporary table are undone if the transaction rolls back. But transaction are not undone if the transaction rolls back to a table variable.

5. Index

For temp table, if you define a constraint without naming, SQL server creates constraint with unique name. That's why it is recommended to use unnamed constraint in temporary table. But if you use constraint with a name, constraint will be created in tempdb. In that case, if you try to create a same named constraint from another session, it will be failed because of duplicate constraint name.

For table variable, SQL server doesn't allow explicit naming  of constranint - not even in a single session.

5. Statistics

SQL server maintains distribution statistics (histograms) fro temporary tables but not for table variables.

Error messages

All the error messages are found by

SELECT * FROM sys.messages

Error number: 1-49999 are sql server error messages
50000 is reserved for a custom message

Severity level: 16-25 are logged automatically in SQL server log and windows application log
0-10 are information only.

State:


Custom error can be added by sp_addmessage.

Transactions

Get all the transactions


SELECT * FROM sys.dm_tran_active_transactions

@@TRANCOUNT shows number of active transaction.

There are three kinds of transactions

1. Auto transaction has no 'Begin Tran' and no 'Rollback Tran' or 'Commit Tran'.

2. Implicit transaction has no 'Begin Tran' but you have to 'Rollback Tran' or 'Commit Tran'.

    To enable implicit transaction

    SET IMPLICIT_TRANSACTIONS ON

    To disable implicit transaction

    SET IMPLICIT_TRANSACTIONS OFF

3. Explicit transaction has both 'Begin tran' and 'Rollback tran' or 'Commit Tran'.

    

    Begin Tran T1

    Print @@Trancount   --1

    Begin Tran T2

    Print @@Trancount --2

    Commit Tran

    Print @@Trancount   --1

    Commit Tran

    Print @@Trancount   --0

    Begin Tran T1

    Print @@Trancount   --1

    Begin Tran T2

    Print @@Trancount --2

    Commit Tran

    Print @@Trancount   --1

    Rollback Tran

    Print @@Trancount   --0

    Begin Tran T1

    Print @@Trancount   --1

    Begin Tran T2

    Print @@Trancount --2

    Rollback Tran

    Print @@Trancount   --0

List all the constraints

SELECT * FROM sys.key_constraints

Cross apply vs Outer apply

The APPLY operator allows you to join two table expressions; the right table expression is processed every time for each row from the left table expression. As you might have guessed, the left table expression is evaluated first and then right table expression is evaluated against each row of the left table expression for final result-set. The final result-set contains all the selected columns from the left table expression followed by all the columns of right table expression.

The APPLY operator comes in two variants, the CROSS APPLY and the OUTER APPLY. The CROSS APPLY operator returns only those rows from left table expression (in its final output) if it matches with right table expression. In other words, the right table expression returns rows for left table expression match only.  Whereas the OUTER APPLY operator returns all the rows from left table expression irrespective of its match with the right table expression.  For those rows for which there are no corresponding matches in right table expression, it contains NULL values in columns of right table expression. So you might now conclude, the CROSS APPLY is semantically equivalent to INNER JOIN (or to be more precise its like a CROSS JOIN with a correlated sub-query) with a implicit join condition of 1=1 whereas OUTER APPLY is semantically equivalent to LEFT OUTER JOIN.

You might be wondering if the same can be achieved with regular JOIN clause then why and when to use APPLY operator? Though the same can be achieved with normal JOIN, the need of APPLY arises if you have table-valued expression on right part and also in some cases use of APPLY operator boost the performance of your query. Let me explain you with help of some examples.

Script #1 creates a Department table to hold information about departments. Then it creates an Employee table which hold information about the employees. Please note, each employee belongs to a department, hence the Employee table has referential integrity with the Department table.

Script #1 - Creating some temporary objects to work on...

USE [tempdb]  GO IF EXISTS (SELECT * FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(N'[Employee]') AND type IN (N'U')) BEGIN     DROP TABLE [Employee] END GO IF EXISTS (SELECT * FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(N'[Department]') AND type IN (N'U'))BEGIN     DROP TABLE [Department] END  CREATE TABLE [Department](     [DepartmentID] [int] NOT NULL PRIMARY KEY,     [Name] VARCHAR(250) NOT NULL,  ) ON [PRIMARY] INSERT [Department] ([DepartmentID], [Name])  VALUES (1, N'Engineering') INSERT [Department] ([DepartmentID], [Name])  VALUES (2, N'Administration') INSERT [Department] ([DepartmentID], [Name])  VALUES (3, N'Sales') INSERT [Department] ([DepartmentID], [Name])  VALUES (4, N'Marketing') INSERT [Department] ([DepartmentID], [Name])  VALUES (5, N'Finance') GO CREATE TABLE [Employee](     [EmployeeID] [int] NOT NULL PRIMARY KEY,     [FirstName] VARCHAR(250) NOT NULL,     [LastName] VARCHAR(250) NOT NULL,     [DepartmentID] [int] NOT NULL REFERENCES [Department](DepartmentID),  ) ON [PRIMARY]  GO INSERT [Employee] ([EmployeeID], [FirstName], [LastName], [DepartmentID]) VALUES (1, N'Orlando', N'Gee', 1 ) INSERT [Employee] ([EmployeeID], [FirstName], [LastName], [DepartmentID]) VALUES (2, N'Keith', N'Harris', 2 ) INSERT [Employee] ([EmployeeID], [FirstName], [LastName], [DepartmentID]) VALUES (3, N'Donna', N'Carreras', 3 ) INSERT [Employee] ([EmployeeID], [FirstName], [LastName], [DepartmentID]) VALUES (4, N'Janet', N'Gates', 3 )

First query in Script #2 selects data from Department table and uses CROSS APPLY to evaluate the Employee table for each record of the Department table. Second query simply joins the Department table with the Employee table and all the matching records are produced.

Script #2 - CROSS APPLY and INNER JOIN

SELECT * FROM Department D CROSS APPLY     (     SELECT * FROM Employee E     WHERE E.DepartmentID = D.DepartmentID     ) A  GO SELECT * FROM Department D INNER JOIN Employee E ON D.DepartmentID = E.DepartmentID  GO

If you look at the results they produced, it is the exact same result-set; not only that even the execution plan for these queries are similar to each other and has equal query cost, as you can see in the image below. So what is the use of APPLY operator?  How does it differ from a JOIN and how does it help in writing more efficient queries. I will discuss this later, but first let me show you an example of OUTER APPLY also.

The first query in Script #3 selects data from Department table and uses OUTER APPLY to evaluate the Employee table for each record of the Department table.  For those rows for which there is not a match in Employee table, those rows contains NULL values as you can see in case of row 5 and 6. The second query simply uses a LEFT OUTER JOIN between the Department table and the Employee table.  As expected the query returns all rows from Department table; even for those rows for which there is no match in the Employee table.

Script #3 - OUTER APPLY and LEFT OUTER JOIN

SELECT * FROM Department D OUTER APPLY     (     SELECT * FROM Employee E     WHERE E.DepartmentID = D.DepartmentID     ) A  GO SELECT * FROM Department D LEFT OUTER JOIN Employee E ON D.DepartmentID = E.DepartmentID  GO

Even though the above two queries return the same information, the execution plan is a bit different. Although cost wise there is not much difference, the query with the OUTER APPLY uses a Compute Scalar operator (which has an estimated operator cost of 0.0000103 or almost 0% of total query cost) before Nested Loops operator to evaluate and produce the columns of Employee table.

Now comes the time to see where the APPLY operator is really required. In Script #4, I am creating a table-valued function which accepts DepartmentID as its parameter and returns all the employees who belong to this department. The next query selects data from Department table and uses CROSS APPLY to join with the function we created.  It passes the DepartmentID for each row from the outer table expression (in our case Department table) and evaluates the function for each row similar to a correlated subquery. The next query uses the OUTER APPLY in place of CROSS APPLY and hence unlike CROSS APPLY which returned only correlated data, the OUTER APPLY returns non-correlated data as well, placing NULLs into the missing columns.

Script #4 - APPLY with table-valued function

IF EXISTS (SELECT * FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(N'[fn_GetAllEmployeeOfADepartment]') AND type IN (N'IF'))BEGIN     DROP FUNCTION dbo.fn_GetAllEmployeeOfADepartment END GO CREATE FUNCTION dbo.fn_GetAllEmployeeOfADepartment(@DeptID AS INT)  RETURNS TABLE  AS  RETURN     (     SELECT * FROM Employee E     WHERE E.DepartmentID = @DeptID     ) GO SELECT * FROM Department D CROSS APPLY dbo.fn_GetAllEmployeeOfADepartment(D.DepartmentID) GO SELECT * FROM Department D OUTER APPLY dbo.fn_GetAllEmployeeOfADepartment(D.DepartmentID) GO

So now if you are wondering, can we use a simple join in place of the above queries? Then the answer is NO, if you replace CROSS/OUTER APPLY in the above queries with INNER JOIN/LEFT OUTER JOIN, specify ON clause (something as 1=1) and run the query, you will get "The multi-part identifier "D.DepartmentID" could not be bound."  error. This is because with JOINs the execution context of outer query is different from the execution context of the function (or a derived table), and you can not bind a value/variable from the outer query to the function as a parameter.  Hence the APPLY operator is required for such queries.

So in summary the APPLY operator is required when you have to use table-valued function in the query, but it can also be used with an inline SELECT statements.

Correlated Subqueries

Correlated subqueries are subqueries where the iner query has a reference to a column from the table in the outer query.


Find out products with the minimum unit price per category.

select categoryid,productid, productname,unitprice
from production.products as p1
where unitprice=
(select min(unitprice)
 from production.products as p2
 where p2.categoryid=p1.categoryid);

COALESCE

Evaluates the arguments in order and returns the current value of the first expression that initially does not evaluate to NULL.


The following example shows how COALESCE selects the data from the first column that has a nonnull value. This example uses the AdventureWorks2012 database.


SELECT Name, Class, Color, ProductNumber, COALESCE(Class, Color, ProductNumber) AS FirstNotNull FROM Production.Product;

Finding out Null valued columns in a database

USE [Database name]

DECLARE @colName VARCHAR(200)
DECLARE @tabName VARCHAR(200)

DECLARE @nullTable  TABLE
(TABLE_NAME  varchar(200),
COLUMN_NAME  varchar(200),
NOTNULLCOUNT  int)

DECLARE @MyCursor CURSOR
SET @MyCursor = CURSOR FAST_FORWARD
FOR
SELECT  T.NAME , C.NAME
FROM SYS.OBJECTS AS T
JOIN SYS.COLUMNS AS C
ON T.OBJECT_ID=C.OBJECT_ID
JOIN SYS.TYPES AS P
ON C.SYSTEM_TYPE_ID=P.SYSTEM_TYPE_ID
WHERE T.TYPE_DESC='USER_TABLE';

OPEN @MyCursor
FETCH NEXT FROM @MyCursor
INTO @tabName,@colName
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @Count AS INT
DECLARE @SQLString nvarchar(500)
SET @SQLString =N'SELECT '''+@tabName+''','''+@colName+''',count(*) FROM dbo.['+@tabName+'] WHERE  ['+@colName+'] IS NOT NULL'
PRINT @SQLString
INSERT @nullTable EXEC (@SQLString)


FETCH NEXT FROM @MyCursor
INTO @tabName,@colName
END
CLOSE @MyCursor
DEALLOCATE @MyCursor


SELECT * FROM @nullTable WHERE NOTNULLCOUNT=0 ORDER BY TABLE_NAME,COLUMN_NAME

Downloading SSRS report from command line

rs Utility is used to download SSRS report from command line. rs.exe is normally found under program folder.

It requires a script to download file. Lets make the name of the script download.rss. It is vb.net and it should be in .rss extension file.

Public Sub Main()
  Dim format as string = "EXCELOPENXML"
  Dim fileName as String = "E:\DownloadReport\RfsReport\Major Projects for CSOC Reporting - "+Date.Now.ToString("MM-dd-yy")+".xlsx"
  Dim reportPath as String = "/RFS Reports/Major Projects - Singleview CSOC"

  'Delete file if exists
  If System.IO.File.Exists( fileName ) = True Then
System.IO.File.Delete( fileName )
  End If

  ' Prepare Render arguments
  Dim historyID as string = Nothing
  Dim deviceInfo as string = Nothing
  Dim extension as string = Nothing
  Dim encoding as string
  Dim mimeType as string
  Dim warnings() AS Warning = Nothing
  Dim streamIDs() as string = Nothing
  Dim results() as Byte

  rs.LoadReport(reportPath, historyID)

  results = rs.Render(format,  deviceInfo, extension, _
   mimeType, encoding,  warnings, streamIDs)

  ' Open a file stream and write out the report
  Dim stream  As FileStream = File.OpenWrite(fileName)
  stream.Write(results, 0, results.Length)
  stream.Close()
End Sub

Now run the following command

c:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\rs.exe -i c:\download.rss -s http://[Report server name]/Reportserver -e Exec2005'

Creating a task in windows scheduler

1. Create a user 'testuser' in local user2. Type in secpol.msc /s3. Select "Local Policies" in MSC snap in4. Select "User Rights Assignment"5. Right click on "Log on as batch job" and select Properties6. Click "Add User or Group", and include the relevant user.7. Add testuser in database. Server Roles will be public and sysadmin