T-SQL: How to split a path name in the path and file name

A common task in T-SQL is how to split a path name in the path and file name.

The following code will do the task:

DECLARE @file VARCHAR(200) = 'c:\temp\data\log.txt'

SELECT REVERSE(LEFT(REVERSE(@file), CHARINDEX('\', REVERSE(@file), 1) - 1)) AS [FileName]

SELECT LEFT(@file, LEN(@file) - CHARINDEX('\', REVERSE(@file), 1) + 1) AS [Path]

T-SQL: Converting rows to columns

A common task of a database developer is to convert rows to columns from a query result set.

T-SQL has the PIVOT that facilitates the process.

A simple example:

DECLARE @myValues TABLE (Quantity INT, Category VARCHAR(100))

INSERT INTO @myValues (Quantity, Category)
VALUES 
 (10, 'Fruits'),
 (200, 'Vegetables'),
 (40, 'Meats')


SELECT *
FROM @myValues

SELECT [Fruits], [Vegetables], [Meats]
FROM @myValues
PIVOT
(
  MAX(Quantity)
  FOR Category IN ([Fruits], [Vegetables], [Meats])
) Piv

SQL Server Table Value Constructor

The latest versions of Sql Server (SQL Server 2008 or above) implement the Table Value Constructor.

Definition

The Table Value Constructor defines a set of row value expressions to be added to a table.
It allows us to create tables of values and/or expressions.
The Table Value Constructor allows us to simplify the T-SQL syntax.

Syntax

The Table Value Constructor is defined as:

VALUES (<row value expression>),(<row value expression>), (<row value expression>), ...

The Table Value Constructor must start with the VALUE keyword and that it can have one or more row value expression.
The <row value expression> can have one of the following values:
1) NULL
2) DEFAULT. If there isn't a default defined for the column NULL is inserted.
3) A constant, variable or an expression. The expression cannot have the EXECUTE statement.
The expression only allows single scalar values. Sub-queries that return multiple columns are not supported.
An example of an invalid sub-query is:

SELECT ID, Name FROM [User]

Note that the values list can have a maximum of 1000 rows. The sql server error 10738 is thrown if the number of rows is greater than the 1000 rows limit.
To insert more row use the classic approaches like a multiple INSERT statements or a BULK INSERT.

Examples

1) Insert multiple rows

CREATE TABLE Product
(
Id INT IDENTITY(1,1) NOT NULL,
Name VARCHAR(1000)
)
INSERT INTO Product
VALUES ('John Doe'), ('Tim Burton'), ('Tom Thomas')

This example can be used to initialize table values.

2) SELECT using a derived table

SELECT x,y
FROM (VALUES (1, 'row 1'), (2, 'row 2'), (3, 'row 3')) AS myTable(x,y)

This example uses the Table Value Constructor as a derived table, to create a table named myTable with the columns x and y.

3) SELECT the maximum value between two variables

DECLARE @val1 MONEY = 20
DECLARE @val2 MONEY = 5

SELECT MAX(Val)
FROM (VALUES (@val1), (@val2)) AS MyTable(Val)

The query returns the value 20. This can be very useful to easily select the maximum value between two or more variables, since sql server doesn't have a built-in function to do this operation.

Table Value Constructor can also be used with the MERGE statement. CodeProject

Poor Mans T-SQL formatter

A database developer must have a set of SQL coding Standards to avoid each statement being written differently.

Steven Bates has witten the SQL Server 2005 coding Standards in a series of blog posts at the MSDN blogs.
The rules are implemented by a SQL Server Management Studio add-in that is named Poor Mans T-SQL formatter.
The Poor Mans T-SQL formatter is a open-source T-SQL formatter that is available in several distibutions: Stand alone application, Notepad++ add-in, online,...
For more information visit the Poor Mans T-SQL formatter site, by pressing here

As for now there is no support to Management Studio 2014.
To make it work with Management Studio 2014:

1) Run the setup for the SQL Server Management Studio 2012 add-in available at the site.

2) Create the folder
%SystemDrive%\ProgramData\Microsoft\SQL Server Management Studio\12.0\Addins\
if it doesn't already exists.

3) Copy the file from:
%SystemDrive%\ProgramData\Microsoft\SQL Server Management Studio\11.0\Addins\PoorMansTSqlFormatterSSMSAddIn.AddIn
To
%SystemDrive%\ProgramData\Microsoft\SQL Server Management Studio\12.0\Addins\PoorMansTSqlFormatterSSMSAddIn.AddIn

And now the add-in should be available at the Tools menu:
Note: You may need to enable show Hidden Items, in windows explorer View Menu, for the folder ProgramData to be shown.