Click here to Skip to main content
Click here to Skip to main content

APPLY Operator of SQL Server 2005

, 3 May 2007
Rate this:
Please Sign up or sign in to vote.
APPLY operator is a new feature in SQL Server 2005 used in 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.

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

Points of Interest

For more articles on SQL Server, visit www.geocities.com/ProgrammersVision.

History

  • 4th May, 2007: Initial post

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

About the Author

Mohammed Ghouse Barq
Web Developer
India India
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.

Comments and Discussions

 
GeneralMy vote of 1 Pinmemberpartik jain2-Mar-10 8:45 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web01 | 2.8.140721.1 | Last Updated 4 May 2007
Article Copyright 2007 by Mohammed Ghouse Barq
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid