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 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:
- CROSS APPLY, and
- 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
CREATE FUNCTION fnGetCustomerInfo (@custid int)
SELECT * FROM Customer cust
ORDER BY cust.CustName
In SQL 2005, TOP is used to restrict the number of rows returned as a number or percentage in
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
Points of Interest
For more articles on SQL Server, visit www.geocities.com/ProgrammersVision.
- 4th May, 2007: Initial post
Mohammed Ghouse Barq, Son of renowned urdu poet, Barq Kadapavi. A Software Engineer by proffession. I received a Masters of Computer Applications degree from University of Bangalore, Have worked in Microsoft Technologies since the DOS days.
Apart from the work I developed a knowledge base website www.geocities.com/ProgrammersVision for helping the .net community with articles, code snippets and much more. Developed and maintaining the site of a eminent urdu poet, Hazrath-e-Barq Kadapavi - www.geocities.com/BarqKadapavi
Hobbies includes not just coding but reading Deeniyat, writing Urdu Poetry with `nom de plume' as Aandaaz Kadapavi.