Click here to monitor SSC
  • Av rating:
  • Total votes: 122
  • Total comments: 10
Robert Sheldon

SQL Server APPLY Basics

24 May 2010

 One of the most interesting additions to SQL Server syntax in SQL Server 2005 was the APPLY operator. It allows several queries that were previously impossible. It is surprisingly difficult to find a simple explanation of what APPLY actually does. Rob Sheldon is the specialist in simple explanations, so we asked him.

Starting with SQL Server 2005, you can use the APPLY operator in a Transact-SQL query to join a table to a table-valued function so the function is evoked for each row returned from the table. For example, you might create a query that returns a list of employees from the Employee table. For each employee, you also want to return a list of the individual job positions that person has held in the company.

You can create a function that retrieves the employees’ positions and then evoke that function for each row returned from the Employee table. Your result set will then include a row for each position that an employee has held. For instance, if John has worked as a salesman and a regional supervisor, the result set will include two rows for John, one for each position. Any data returned from the Employee table will be repeated for each row, but the data returned by the function will be specific to each row in the function’s results.

The APPLY operator can take one of two forms: CROSS APPLY or OUTER APPLY. The CROSS APPLY operator returns rows from the primary (outer) table only if the table-value function produces a result set. That means, in the example above, an employee would be included in the returned data only if that employee has held a specific position within the company. The OUTER APPLY form, on the other hand, returns all rows from the outer table, even if the function produces no results. So an employee would be listed even if that employee held no specific position.

In this article, I demonstrate how to work with both forms of the APPLY operator. The examples I show you were created on a local instance of SQL Server 2008 and the AdventureWorks2008 sample database. If you want to run these examples against the AdventureWorks database on an instance of either SQL Server 2005 or 2008, you must change references to the BusinessEntityID column to SalesPersonID column, where appropriate. In addition, you’ll find that your results might vary slightly from those shown here because the values between the databases are slightly different, particularly primary key values.

Using the CROSS APPLY Operator

As I mentioned, CROSS APPLY returns only those rows in the outer table for which the table value function returns data. Let’s look at an example to demonstrate how this works. Fist, we’ll create a function that returns the top three sales generated by a salesperson, as those sales appear in the Sales.SalesOrderHeader table in the AdventureWorks2008 database:

USE AdventureWorks2008

GO

IF OBJECT_ID (N'fn_sales', N'IF') IS NOT NULL

  DROP FUNCTION dbo.fn_sales

GO

CREATE FUNCTION fn_sales (@SalesPersonID int)

RETURNS TABLE

AS

RETURN

(

  SELECT TOP 3

    SalesPersonID,

    ROUND(TotalDue, 2) AS SalesAmount

  FROM

    Sales.SalesOrderHeader

  WHERE

    SalesPersonID = @SalesPersonID

  ORDER BY

    TotalDue DESC

)

GO

As you can see, the fn_sales function takes one parameter, @SalesPersonID, which is configured with the int data type. The function returns the three highest sales for the specified salesperson. Note that this is a table-valued function, which means that it returns the entire result set generated by the SELECT statement. For the fn_sales function, the result set includes the SalesPersonID and SalesAmount columns.

After you create your function, you can test it by running a SELECT statement that retrieves data from the function. For example, the following SELECT statement returns the SalesAmount column for salesperson ID 285:

SELECT SalesAmount FROM fn_sales(285)

The following table shows the results returned by the statement. As you would expect, three rows have been returned—the three highest sales for this salesperson.

SalesAmount

85652.33

45338.76

36317.54

After you’ve verified that the function is returning the correct results, you can use the function within a statement that includes the CROSS APPLY operator. In the following example, I retrieve data from the Sales.vSalesPerson view and join it to the fn_sales function:

SELECT

  sp.FirstName + ' ' + sp.LastName AS FullName,

  fn.SalesAmount

FROM

  Sales.vSalesPerson AS sp

CROSS APPLY

  fn_sales(sp.BusinessEntityID) AS fn

ORDER BY

  sp.LastName, fn.SalesAmount DESC

As you can see, I use the CROSS APPLY operator in the FROM clause by first specifying the outer table (vSalesPerson), then the CROSS APPLY operator, and finally the fn_sales function. Notice that I pass in the BusinessEntityID value as the function’s parameter. This value is based on the BusinessEntityID value as it appears in the current row that is being returned from the outer table.

NOTE: The BusinessEntityID column in the vSalesPerson view uses the same IDs that are used in the SalesPersonID column in the SalesOrderHeader table. In the original version of the AdventureWorks database, the column name in vSalesPerson is SalesPersonID, just like it is in the SalesOrderHeader table.

Also notice that the columns in the SELECT list reference the source table and function as they would if I were joining two tables. I assign an alias to the table (sp) and one to the function (fn) and then reference the columns accordingly. For example, because the SalesAmount column in the SELECT list is returned by the function, I qualify the column name as fn.SalesAmount.

The following table shows part of the results returned by the SELECT statement above. Notice that each salesperson is listed three times, once for each result returned by the fn_sales function.

FullName

SalesAmount

Syed Abbas

85652.33

Syed Abbas

45338.76

Syed Abbas

36317.54

Amy Alberts

98405.08

Amy Alberts

96243.80

Amy Alberts

95193.67

Pamela Ansman-Wolfe

125254.49

Pamela Ansman-Wolfe

125144.01

Pamela Ansman-Wolfe

118284.78

Michael Blythe

198628.31

Michael Blythe

142942.01

Michael Blythe

139659.67

David Campbell

149897.36

David Campbell

136046.44

David Campbell

125068.34

Jillian Carson

162629.75

Jillian Carson

154912.07

Jillian Carson

144355.88

Shu Ito

247913.91

Shu Ito

227737.72

Shu Ito

189198.62

Stephen Jiang

149861.07

Stephen Jiang

114361.94

Stephen Jiang

112733.70

Tete Mensa-Annan

140734.49

Tete Mensa-Annan

137108.39

Tete Mensa-Annan

115068.64

That’s all there is to using the CROSS APPLY operator. Not let’s look at another example that uses the operator with a common table expression (CTE).

Using the CROSS APPLY Operator with a CTE

To demonstrate how you can use the APPLY operator with a CTE, I created the following function, which returns the product model associated with the specified product:

USE AdventureWorks2008

GO

IF OBJECT_ID (N'fn_products', N'IF') IS NOT NULL

  DROP FUNCTION dbo.fn_products

GO

CREATE FUNCTION fn_products (@ProductID int)

RETURNS TABLE

AS

RETURN

(

  SELECT

    p.Name AS ProductName,

    pm.Name AS ProductModel

  FROM

    Production.Product AS p

    LEFT OUTER JOIN Production.ProductModel AS pm

      ON p.ProductModelID = pm.ProductModelID

  WHERE

    p.ProductID = @ProductID

)

GO

The function joins the Production.Product and Production.ProductModel tables to return the product name and model name for the specified product ID. I then used the following SELECT statement to verify that the function works as I expect:

SELECT ProductName, ProductModel

FROM fn_products(707)

The statement returns the product name and model for product 707 (shown in the following table). As you can see, the product Sport-100 Helmet, Red is associated with the model Sport-100.

ProductName

ModelName

Sport-100 Helmet, Red

Sport-100

You can then use the CROSS APPLY operator to join a CTE to the function, as shown in the following example:

WITH

  ProductSales (ProductID, TotalSales)

  AS

  (

    SELECT

      ProductID,

      SUM(LineTotal)

    FROM Sales.SalesOrderDetail

    GROUP BY ProductID

  )

SELECT

  ps.ProductID,

  ps.TotalSales,

  fn.ProductModel

FROM

  ProductSales AS ps

CROSS APPLY

  fn_products(ps.ProductID) AS fn

ORDER BY

  ps.ProductID

Notice that I first define a CTE named ProductSales. The CTE returns the total sales for each product as they appear in the Sales.SalesOrderDetail table. I then use the CTE in the main SELECT statement, along with the fn_products function. Notice that I use the CROSS APPLY operator in the FROM clause to join the CTE to the function, as I would use the operator to join a table or view to the function. The following table shows part of the results returned by the SELECT statement.

ProductID

TotalSales

ProductModel

707

157772.394392

Sport-100

708

160869.517836

Sport-100

709

6060.388200

Mountain Bike Socks

710

513.000000

Mountain Bike Socks

711

165406.617049

Sport-100

712

51229.445623

Cycling Cap

713

21445.710000

Long-Sleeve Logo Jersey

714

115249.214976

Long-Sleeve Logo Jersey

715

198754.975360

Long-Sleeve Logo Jersey

716

95611.197080

Long-Sleeve Logo Jersey

717

394255.572400

HL Road Frame

718

395182.699300

HL Road Frame

719

89872.173600

HL Road Frame

722

177635.904000

LL Road Frame

723

24844.692200

LL Road Frame

725

194692.599104

LL Road Frame

726

132125.252200

LL Road Frame

727

20104.443400

LL Road Frame

729

195933.409400

LL Road Frame

730

137213.485128

LL Road Frame

732

89224.500000

ML Road Frame

733

32120.820000

ML Road Frame

736

45164.684600

LL Road Frame

738

299595.522966

LL Road Frame

739

269874.009600

HL Mountain Frame

741

141635.100000

HL Mountain Frame

742

499556.572400

HL Mountain Frame

743

901590.233600

HL Mountain Frame

744

13765.920000

HL Mountain Frame

745

106078.560000

HL Mountain Frame

One thing you might notice about the results shown here, compared to the results shown in the preceding example, is that the function returns only one row for each product. Because a product is associated with only one product model in the SalesOrderDetail table, there will never be more than one row per product.

Using the OUTER APPLY Operator

In the examples above, the CROSS APPLY operator returns the rows in the outer table for which the table value function returns data. As it turns out, the functions used in both examples return data for all rows in the outer tables. However, in some cases, the function will not return data for a specific row. If you still want the row from the outer table to be included in the result set, you should use the OUTER APPLY operator.

The OUTER APPLY operator returns all rows from the outer table, whether or not the function returns data for a specific row. You use the OUTER APPLY operator just as you would CROSS APPLY; the difference is in the results. Let’s look at an example that demonstrates how this works.

In the following statement, I create a function that returns data about product inventory:

USE AdventureWorks2008

GO

IF OBJECT_ID (N'fn_inventory', N'IF') IS NOT NULL

  DROP FUNCTION dbo.fn_inventory

GO

CREATE FUNCTION fn_inventory (@ProductID int)

RETURNS TABLE

AS

RETURN

(

  SELECT

    ProductID,

    LocationID,

    Quantity

  FROM

    Production.ProductInventory

  WHERE

    ProductID = @ProductID

)

GO

This statement, just like the CREATE FUNCTION statements used in the preceding examples, creates a table-valued function. The function returns three columns: ProductID, LocationID, and Quantity. I verified the function by using the following SELECT statement:

SELECT LocationID, Quantity

FROM fn_inventory(915)

In this case, I specified the product ID of 915 as the function’s parameter. The following table shows the results returned by the statement.

LocationID

Quantity

6

161

50

83

60

158

As you can see, product inventory exists in three locations, with different quantities at each location. Once the function has been verified, you can use it with a CROSS APPLY operator, as shown in the following statement:

SELECT

  p.ProductID,

  p.Name,

  fn.Quantity

FROM

  Production.Product AS p

CROSS APPLY

  fn_inventory(p.ProductID) AS fn

ORDER BY p.ProductID

Notice that this statement is similar to the previous examples. I’ve simply used the CROSS APPLY operator to join the Product table to the fn_inventory function. The following table shows part of the data returned by the statement. As the results indicate, there are multiple rows for each product, one for each quantity.

ProductID

Name

Quantity

915

ML Touring Seat/Saddle

161

915

ML Touring Seat/Saddle

83

915

ML Touring Seat/Saddle

158

916

HL Touring Seat/Saddle

425

916

HL Touring Seat/Saddle

288

916

HL Touring Seat/Saddle

276

921

Mountain Tire Tube

286

921

Mountain Tire Tube

243

922

Road Tire Tube

264

922

Road Tire Tube

241

923

Touring Tire Tube

262

923

Touring Tire Tube

240

928

LL Mountain Tire

240

928

LL Mountain Tire

369

929

ML Mountain Tire

385

929

ML Mountain Tire

284

930

HL Mountain Tire

267

930

HL Mountain Tire

232

The results shown in the table are those returned for product IDs 915 through 930. As you would expect, each row includes data returned from both the table and the function. Now let’s look at an example that uses OUTER APPLY, rather than CROSS APPLY:

SELECT

  p.ProductID,

  p.Name,

  fn.Quantity

FROM

  Production.Product AS p

OUTER APPLY

  fn_inventory(p.ProductID) AS fn

ORDER BY p.ProductID

This statement is exactly like the previous one, except for the APPLY operator. However, the results are slightly different, as shown in the following table:

ProductID

Name

Quantity

915

ML Touring Seat/Saddle

161

915

ML Touring Seat/Saddle

83

915

ML Touring Seat/Saddle

158

916

HL Touring Seat/Saddle

425

916

HL Touring Seat/Saddle

288

916

HL Touring Seat/Saddle

276

917

LL Mountain Frame - Silver, 42

NULL

918

LL Mountain Frame - Silver, 44

NULL

919

LL Mountain Frame - Silver, 48

NULL

920

LL Mountain Frame - Silver, 52

NULL

921

Mountain Tire Tube

286

921

Mountain Tire Tube

243

922

Road Tire Tube

264

922

Road Tire Tube

241

923

Touring Tire Tube

262

923

Touring Tire Tube

240

924

LL Mountain Frame - Black, 42

NULL

925

LL Mountain Frame - Black, 44

NULL

926

LL Mountain Frame - Black, 48

NULL

927

LL Mountain Frame - Black, 52

NULL

928

LL Mountain Tire

240

928

LL Mountain Tire

369

929

ML Mountain Tire

385

929

ML Mountain Tire

284

930

HL Mountain Tire

267

930

HL Mountain Tire

232

The result set now includes rows for products 917 through 920 and products 924 through 927, which were not included in the previous results. Notice that the Quantity column shows a NULL value for each of the new rows. By using the OUTER APPLY operator, we’re able to return all rows from the outer table, whether or not the function returns any rows.

As you can see, the APPLY operator can be a useful tool when you want to evoke a table-valued function for each row returned by a table expression (the outer table). You simply use the operator to join the outer table to the function. If you want to include only those rows from the outer table for which the function returns data, use the CROSS APPLY operator. If you want to return all rows from the outer table, regardless of whether or not the function returns data for a row, use the OUTER APPLY operator. For more details about either form of the APPLY operator and to see additional examples, check out the topic “Using APPLY” in SQL Server Books Online.

Robert Sheldon

Author profile:

After being dropped 35 feet from a helicopter and spending the next year recovering, Robert Sheldon left the Colorado Rockies and emergency rescue work to pursue safer and less painful interests—thus his entry into the world of technology. He is now a technical consultant and the author of numerous books, articles, and training material related to Microsoft Windows, various relational database management systems, and business intelligence design and implementation. He has also written news stories, feature articles, restaurant reviews, legal summaries, and the novel 'Dancing the River Lightly'. You can find more information at http://www.rhsheldon.com.

Search for other articles by Robert Sheldon

Rate this article:   Avg rating: from a total of 122 votes.


Poor

OK

Good

Great

Must read
Have Your Say
Do you have an opinion on this article? Then add your comment below:
You must be logged in to post to this forum

Click here to log in.


Subject: SQL Server APPLY Basics
Posted by: mbuning (view profile)
Posted on: Tuesday, June 01, 2010 at 7:20 AM
Message: Robert,
Can you explain the differences between using this function and simply using a join in a sql statement extracting data from multiple tables?

Subject: APPLY basics
Posted by: nick duckstein (not signed in)
Posted on: Tuesday, June 01, 2010 at 9:27 AM
Message: I have the same question about using the function vs inner and outer joins. APPLY and function don't seem to do anything I couldn't do in a subquery using join statments.

Nick

Subject: I wonder ...
Posted by: Anonymous (not signed in)
Posted on: Tuesday, June 01, 2010 at 9:30 AM
Message: Rob / Mbuning,

This possibly answers a question I have had for a couple of years now.

A couple of years ago I had a problem when I migrated a SQL 2000 database to SQL 2005. For reasons best known to the people who designed the database they had not use an IDENTITY(1,1) column, but instead had created a function GetNextID() that returned MAX(ID) + 1. (... no, I could never really work out why either).

The problem was that a data load proc used the function GetNextID() when inserting multiple rows. In SQL2000 this worked fine, evaluating the func for each insert. In SQL2005, the database engine evaluates the function once at the start of the proc, and then tries to use this value again and again. You can see the problem under SQL2005 - suppose GetNextID returns 1001, the proc attempts to INSERT 1001 for every row. The first insert will work, but subsequent inserts fail on the Primary Key constraint.
At the time I rewrote the Proc (I confess that I used a WHILE loop) but I now realise that I could possibly have solved the problem with an APPLY.

However, please note that the function in this particular instance wasn't joined in the FROM clause, but called in the projection / SELECT clause.

I wonder if these two things are linked. I.e. I wonder if this change was part of an attempt to make function evaluation more efficient on the part of the SQL Server team, by giving a developer a means of controlling how functions are evaluated in the FROM clause, but which also affects how they are evaluated in the projection.

I'd love to hear what you think.

Regards,
Eric.

Subject: I wonder ...
Posted by: Anonymous (not signed in)
Posted on: Tuesday, June 01, 2010 at 9:38 AM
Message: Rob / Mbuning,

This possibly answers a question I have had for a couple of years now.

A couple of years ago I had a problem when I migrated a SQL 2000 database to SQL 2005. For reasons best known to the people who designed the database they had not use an IDENTITY(1,1) column, but instead had created a function GetNextID() that returned MAX(ID) + 1. (... no, I could never really work out why either).

The problem was that a data load proc used the function GetNextID() when inserting multiple rows. In SQL2000 this worked fine, evaluating the func for each insert. In SQL2005, the database engine evaluates the function once at the start of the proc, and then tries to use this value again and again. You can see the problem under SQL2005 - suppose GetNextID returns 1001, the proc attempts to INSERT 1001 for every row. The first insert will work, but subsequent inserts fail on the Primary Key constraint.
At the time I rewrote the Proc (I confess that I used a WHILE loop) but I now realise that I could possibly have solved the problem with an APPLY.

However, please note that the function in this particular instance wasn't joined in the FROM clause, but called in the projection / SELECT clause.

I wonder if these two things are linked. I.e. I wonder if this change was part of an attempt to make function evaluation more efficient on the part of the SQL Server team, by giving a developer a means of controlling how functions are evaluated in the FROM clause, but which also affects how they are evaluated in the projection.

I'd love to hear what you think.

Regards,
Eric.

Subject: Evoke
Posted by: Emtucifor (view profile)
Posted on: Tuesday, June 01, 2010 at 3:42 PM
Message: You don't evoke a function. You invoke it.

Main Entry: evoke
Pronunciation: \i-ˈvōk\
Function: transitive verb
Inflected Form(s): evoked; evok·ing
Etymology: French évoquer, from Latin evocare, from e- + vocare to call — more at vocation
Date: circa 1622
1 : to call forth or up: as a : conjure 2a <evoke evil spirits> b : to cite especially with approval or for support : invoke c : to bring to mind or recollection <this place evokes memories>
2 : to re-create imaginatively
synonyms see educe

Subject: Queries that were previously impossible?
Posted by: Vincenzo (not signed in)
Posted on: Wednesday, June 02, 2010 at 4:57 AM
Message: It was promised at the beginning of the article that APPLY "allows several queries that were previously impossible".
But then we see examples of queries that were not previously impossible (or even particularly difficult). For this article to have any credence whatsoever it should contain an example of using APPLY to solve a problem that is impossible (or at least very difficult) to solve without it.

Regards,

Vincenzo

Subject: Re: Evoke, evoking a function.
Posted by: pedant (not signed in)
Posted on: Wednesday, June 02, 2010 at 5:13 AM
Message: I see no harm in evoking a function rather than invoking a function. We are having to use existing words in unusual ways and although Fowler's English Usage argues that the writer should always incline towards the more familiar usage, they should avoid cliché. We talk about calling a function, so evoking a function is perfectly acceptable usage.

Subject: no need of function in APPLY
Posted by: Anonymous (not signed in)
Posted on: Wednesday, June 02, 2010 at 10:31 AM
Message: You can do it without using function in APPLY.

SELECT
  
sp.FirstName + ' ' + sp.LastName AS FullName,
  
fn.SalesAmount
FROM
  
Sales.vSalesPerson AS sp
CROSS APPLY
  
(
  
SELECT TOP 3
    SalesPersonID
,
    
ROUND(TotalDue, 2) AS SalesAmount
  
FROM
    
Sales.SalesOrderHeader
  
WHERE
    
SalesPersonID = sp.BusinessEntityID
  
ORDER BY
    
TotalDue DESC
) AS fn
ORDER BY
  
sp.LastName, fn.SalesAmount DESC

Subject: Knee jerk commenting
Posted by: JS (not signed in)
Posted on: Thursday, June 03, 2010 at 11:55 AM
Message: I am amused by the disparaging commentators here.
The real power of the APPLY functionality is allowing use of *existing udfs* in the fashion described.
If course, the simple example here could have been accomplished without an APPLY, but the point is that if you have to/need to reuse code in a udf, APPLY gives you power you never had before.
@pedant - No, the usage was wrong here. Stop trying to change the language.
However,
@Emtucifor - you really don't need to post language corrections on a technical article. I think we all know what he was getting at.

Subject: About the GetNextID example from post #3
Posted by: MarcosDiez (view profile)
Posted on: Saturday, May 04, 2013 at 7:08 AM
Message: Altough the APPLY may solve the problem of using the GetNextID() function in certain cases, the APPLY isn't needed at all. You're missing a concept that would be usefull even for engines that doesn't prvide an APPLY but has DETERMINISTIC functions (that you cannot declare as "nondeterministic"):

A deterministic function is one that provides the same result every time you invoke it passing the same set of parameters. This is the mathematical concept of a function that relates many elements of a source set to one element in the target set.

A nondeterministic function is one that may provide different results disregarding which set of parameters you've passed when invoking it. This is a computational concept of a "function", a convenient tool for programmers.

By assuming that all the funtions are deterministic, the engine may save processing time by caching the function results in a table that maps the parameters received every time it was called with the function's result. Hence when you call a function from within a SQL statement, the result is sought in this cached table for the parameters passed, and the function is invoked only when the result wasn't found (then a new row mapping the parameters to the result is added to the cache).

Because of this you shouldn't use nondeterministic functions (like getdate) within deterministic functions, nor operations whose result relies on the side effect from an external entity (your counter).

The APPLY as a resource to solve the problem may backfire if you use a nondeterministic function in a SQL sentence involving GROUP BY or other aggregations, since while you may need the function to be called for every row up to be aggregated, the query plan may deem the function to be called just once anyway (as happens if you were using an VIEW instead of the APPLY).

You have to turn you nondeterministic function into a deterministic one by means of forcing the correlation between it results with any subset of coumns elegible to build an unique key for the table for which the function was devised. Hence you would declare one function parameter for every column in this subset, disregarding if it has no use within the function, and when calling it in the SQL statement you would pass the related columns as the parameters.

This way, for every row of the table the engine will look for a result in the cache mapped to that unique key made of the parameters received, and it would force the function to be executed as many times as you expected (but no more times than needed): just once per each row.

Hence, if your table have the columns A and B in its PK, you'd be calling a function GetNextID(A,B), which will work as expected no matter how complex the SQL sentence could be.


 

Phil Factor
Searching for Strings in SQL Server Databases

Sometimes, you just want to do a search in a SQL Server database as if you were using a search engine like Google.... Read more...

 View the blog

Top Rated

SQL Server XML Questions You Were Too Shy To Ask
 Sometimes, XML seems a bewildering convention that offers solutions to problems that the average... Read more...

Continuous Delivery and the Database
 Continuous Delivery is fairly generally understood to be an effective way of tackling the problems of... Read more...

The SQL Server Sqlio Utility
 If, before deployment, you need to push the limits of your disk subsystem in order to determine whether... Read more...

The PoSh DBA - Reading and Filtering Errors
 DBAs regularly need to keep an eye on the error logs of all their SQL Servers, and the event logs of... Read more...

MySQL Compare: The Manual That Time Forgot, Part 1
 Although SQL Compare, for SQL Server, is one of Red Gate's best-known products, there are also 'sister'... Read more...

Most Viewed

Beginning SQL Server 2005 Reporting Services Part 1
 Steve Joubert begins an in-depth tour of SQL Server 2005 Reporting Services with a step-by-step guide... Read more...

Ten Common Database Design Mistakes
 If database design is done right, then the development, deployment and subsequent performance in... Read more...

SQL Server Index Basics
 Given the fundamental importance of indexes in databases, it always comes as a surprise how often the... Read more...

Reading and Writing Files in SQL Server using T-SQL
 SQL Server provides several "standard" techniques by which to read and write to files but, just... Read more...

Concatenating Row Values in Transact-SQL
 It is an interesting problem in Transact SQL, for which there are a number of solutions and... Read more...

Why Join

Over 400,000 Microsoft professionals subscribe to the Simple-Talk technical journal. Join today, it's fast, simple, free and secure.