Cross Apply In SQL

Facebooktwittergoogle_plusredditpinterestlinkedinmail

CROSS APPLY is one of those helpful things in SQL Server that most people don’t think of or may not even know about. In this article I’d like to talk about what the APPLY operator is and how we can use it to simply our sql statements.

APPLY Operator
The APPLY operator allows you to join a table to a table-valued function. A table-valued function is a function that will return a table with one or more columns. With the apply operator, you can pass values from the first table in to the table-valued function.

There are only 2 types of APPLY operators:
CROSS APPLY – Returns records when a value from both sides of the operator match. Like an INNER JOIN.
OUTER APPLY – Returns all rows from the other side of the operator and will return the value or NULL from the table-valued function. This is like an OUTER JOIN.

In the example above, you can see that we join to the GetAnimalHabitat function using the CROSS APPLY. You can imagine that this function does a bunch of logic that is not visible in this query. If the GetAnimalHabitat function had 25 lines of code, you can see how this simplifies the above query dramatically.

Additional Thoughts
The APPLY operator can simplify the code, but could be accomplished by joining to a sub query as well. One difference is that the function in the APPLY operator is being executed for every row in the outer table. If you use the APPLY operator, make sure that you test the speed of your query to make sure that it did not degrade performance.

Case Sensitive Compare

Facebooktwittergoogle_plusredditpinterestlinkedinmail

Occasionally we need to do a string comparison in SQL Server as a case sensitive compare.  By default, case sensitive compare is not turned on for tables in SQL Server.  It’s actually pretty easy to change a string comparison to a case sensitive compare.  You can do this by using the COLLATE clause.

Read below to find out why this works and other tips on doing a case sensitive compare.

 
 
What Is The COLLATE Clause

The COLLATE clause is something that you can add to the end of an expression to set the collation of the operation to a specific collation.  So in the example… the string equal comparison is being changed to a collation of SQL_Latin1_General_CP1_CS_AS.

A collation in SQL Server is something defines how data is compared.  This could be things like case sensitivity, acceptable characters, etc…  Most of the time (for English installations of SQL Server) it will be a collation type of SQL_Latin1_General_CP1_CI_AS.  This is because the Latin character set covers English and other Latin based languages.

The two most common collations that I deal with are SQL_Latin1_General_CP1_CI_AS and SQL_Latin1_General_CP1_CS_AS.  You can see that they are very similar.  In fact… the only difference is the _CI_ and _CS_.  These actually stand for Case-Insensitive and Case-Sensitive.
 
 
Field Level Case Sensitivity

Even better… you don’t need to set the collation on every sql statement that you want to make case sensitive.  There is actually a setting that you can apply to a field in a table.

NOTE: that if you are joining on this table, both collations must be the same or you will need to specify the collation to use.

Create a new table with a case sensitive field

Change a table’s column to be case sensitive

Delete Using Join

Facebooktwittergoogle_plusredditpinterestlinkedinmail

The DELETE statement is one of the most fundamental statements on SQL Server.  Just about everyone knows how to use the delete statement.  But did you know that you can delete using a join?  It’s actually pretty easy.

Delete Using Join

To delete from a table using a join condition, you need to first format your statement like a regular select statement.  Instead of using a SELECT statement, you will use a DELETE statement.  After the delete command you will specify the table that you want to delete from in your join.

In the example below, we want to delete all orders for this employee.  I have aliased the Orders table with the letter O.  So my delete statement will say “DELETE O”… where O is the alias for the Orders table.  This will only delete data from the Orders table and not from the Employees table.

One thing to note is that you cannot delete from multiple tables this way.

Reference: http://technet.microsoft.com/en-us/library/ms189835.aspx