Share via

APPLY Operator in SQL Server

  • Article

Introduction

APPLY operator is a new feature in SQL Server 2005 and TOP has some new enhancements in SQL 2005. We will discuss these two operators in this article.

APPLY Operators

APPLY operator is a new feature in SQL Server 2005 used in the FROM clause of a query. It allows you to call a function-returning TABLE for each row of your outer TABLE. We can pass outer table's columns as function arguments.

It has two options:
1.CROSS APPLY and
2.OUTER APPLY
CROSS APPLY will not return the outer tables row if function table has no row corresponding to it, whereas OUTER APPLY returns NULL values instead of function columns.

The below query returns all the records of the customer table matching with cust.CustomerID. To execute the code below, you need to have two database tables listed below with some data in it.

CREATE TABLE  Customer(CustomerID INT, CustName VARCHAR(max))
 
CREATE TABLE  Orders(OrderID int  IDENTITY(1,1) NOT  NULL, CustomerID int, SalesPersonID int, OrderDate datetime, Amount int)

Using the Code

--Function returning an OUTER query result in a table
CREATE FUNCTION  fnGetCustomerInfo (@custid int)
RETURNS TABLE
AS
RETURN
(
--Outer Query
SELECT * 
FROM Orders 
WHERE customerid = @custid)
 
--Use APPLY
SELECT * FROM Customer cust
CROSS APPLY
fnGetCustomerInfo(cust.CustomerID)
ORDER BY  cust.CustName

TOP Operator

In SQL 2005, TOP is used to restrict the number of rows returned as a number or percentage in SELECT, UPDATE, DELETE or INSERT statements. Earlier, this was possible only with SELECT query. This enhanced feature replaces SET ROW COUNT which had performance issues.

Syntax: TOP <literal> or <expression> [PERCENT]

Note: Expression should be of bigint for literal option and float for expression option.

SELECT TOP  10 * FROM  Orders
SELECT TOP  (SELECT  count(*) FROM Customer) * FROM Orders
 
DECLARE @NOROWS AS FLOAT
SET @NOROWS = 70
SELECT TOP  (@NOROWS) PERCENT * FROM Orders

See Also

  • [[SQL Server Query Language - Transact-SQL]]
  • [[T-SQL Useful Links]]
  • [[TechNet Guru Contributions - Transact SQL]]
  • [[SQL Server 2012]]
  • [[SQL Server Developer Resources]]