function

Get Filestream Storage Directory

by
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. But what if you want to get access to these files or if you want to know where they are stored. SQL Server wants you to access these files through a network share managed by SQL Server. They provide a function called PathName that you can call to get this path. You will call this function like you would call an extension method in .Net. In the example below, we will assume that the column InvoiceFile is a Filestream datatype. We call the PathName() function to get the share name to where the files are stored.

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

If Statement In An Update

by
Facebooktwitterredditpinterestlinkedinmail

If you are updating multiple rows in one statement, but only want to update a value in a column if a condition is satisfied, then you need to perform some sort of if logic inside your update. Here’s a straight forward way of doing this.

In the example below, I am just going to use 2 temp table variables. I’ll update the @SourceTable with the data from the @UpdateTable… but only where the UpdateFlag field is set to 1.

DECLARE @SourceTable TABLE
(AnimalID INT,
 AnimalName VARCHAR(255))
 
DECLARE @UpdateTable TABLE
(AnimalID INT,
 UpdateToValue VARCHAR(255),
 UpdateFlag  BIT)
 
INSERT
INTO   @SourceTable
VALUES (1, 'Dog'),
       (2, 'Cat'),
       (3, 'Snake')
 
INSERT
INTO    @UpdateTable
VALUES  (1, 'Pig', 1),
        (2, 'Cow', 0),
        (3, 'Turkey', 1)

UPDATE  S
SET     S.AnimalName =	CASE WHEN U.UpdateFlag = 1 THEN U.UpdateToValue 
							 ELSE S.AnimalName 
						END
FROM    @SourceTable S
INNER   JOIN @UpdateTable U ON U.AnimalID = S.AnimalID

SELECT *
FROM   @SourceTable S

 
In the example above, you can see that the AnimalName is only updated when the UpdateFlag column is set to 1. The 2nd record “Cat” is not updated with the value “Cow” because it has a 0 for the UpdateFlag. This is done using a case statement on the SET operation. The example above is very simple. This case logic in the SET operation becomes much more helpful in more complex queries.

Get Filestream Storage Directory

by
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

SQL Server Replace

by
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

by
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