Cross Apply In SQL

Facebooktwitterredditpinterestlinkedinmail

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 simplify 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.

SELECT	A.AnimalName,
		H.HabitatName
FROM	Animal A
CROSS	APPLY GetAnimalHabitat(A.AnimalTypeCode) H

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.

Get Filestream Storage Directory

Facebooktwitterredditpinterestlinkedinmail

Filestreams are a great way to store files in SQL Server. SQL Server makes this super easy by managing the storage of the physical files on the file system somewhere. But what if you want to get access to these files or if you just want to know where they’re stored. SQL Server provides a function called PathName that you can call to get this path. You call this function like you would call an extension method in .Net. In the example below, we’ll assume that the column InvoiceFile is a Filestream datatype.

SELECT	TOP 1
		InvoiceFile.PathName() AS DirectoryOfFile
FROM	Invoice

Estimate Backup Size In Sql Server

Facebooktwitterredditpinterestlinkedinmail

When you’re creating your database backup maintenance plans, you need to choose a drive that has enough space for your backup. So how much space will you actually need to backup your database? Figuring this out is actually a lot easier than you would expect.

Although it won’t be 100% accurate, SQL Server has a built in stored procedure that will get you pretty close. You can use the sp_spaceused stored procedure to show you the amount of space used in your database. Now keep in mind that a full database backup only stores the actual data/objects in the database. The unused space is not stored in the backup.

USE zoobase
GO
EXEC sp_spaceused @updateusage = N'TRUE'
GO

Estimate Backup Size In SQL Server

In the example above we call the sp_spaceused stored procedure from the database that we want to get the backup size for. This stored procedure will return a couple datasets with multiple columns. The column that you will look at is named reserved. This will show you a good estimate of how large your full backup file will be.

 
More Info
The @updateusage parameter, in the example above, tells the stored procedure to update the space usage statistics before returning the database size information. Passing TRUE in to this parameter will give us the most accurate size estimate.

SQL Server Replace

Facebooktwitterredditpinterestlinkedinmail

Replacing text in SQL Server is easy. SQL Server has the REPLACE function to perform this task.

SQL Server REPLACE
The REPLACE function in SQL Server has 3 parameters.

  1. Text to search
  2. Text to find
  3. Text to replace with
SELECT	REPLACE('Full text to search in', 'search', 'replace') AS ReplacedText

The output is: Full text to replace in

In the above example, we are searching “Text to search in” for the word “search” and we are replacing it with the word “replace”. Pretty simple, right? Now let’s take it a step further.

SQL Server Case Sensitive Replace
Above we went over how to do a case insensitive replace on a string. (The REPLACE function actually uses the default collation of the input text that it is searching). To turn it in to a SQL Server case sensitive replace, we just need to add one small thing to the end. We need to change the collation of the text we are searching. Learn more about text collation here.

SELECT REPLACE('Full text to search in' COLLATE SQL_Latin1_General_CP1_CS_AS, 'SEARCH', 'replace') AS ReplacedText

The output is: Full text to search in

This example shows how the “SEARCH” text was not found because we are changing the input text to search collation to SQL_Latin1_General_CP1_CS_AS (which is case sensitive). The input string has “search” in lower case and we were searching for an upper case “SEARCH”.

 
Reference: http://msdn.microsoft.com/en-us/library/ms186862(v=sql.120).aspx

SQL Rank Vs Row Number

Facebooktwitterredditpinterestlinkedinmail

SQL Rank Vs Row Number is a common question because the two functions are very similar. SQL Server has the two similar built-in functions to assign numbers to the order of the data in the result set. The SQL Server RANK function and the ROW_NUMBER function both can accomplish this, but they do something slightly different. You can follow these links see details on how to use this functions at RANK function and ROW_NUMBER function.

ROW_NUMBER: Returns a sequential number starting at 1 in the order specified. If there are duplicate records, SQL Server will continue the sequence on the duplicated record… ensuring that the row number sequence is never duplicated.

RANK: Returns a sequential number starting at 1 in the order specified. If there are duplicate records, SQL Server will use the same number for the duplicate records. There will then be a gap in the sequence for the duplicated records.

 
Reference:
    RANK: http://msdn.microsoft.com/en-us/library/ms176102.aspx
    ROW_NUMBER: http://msdn.microsoft.com/en-us/library/ms186734.aspx