08 July 2014

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.

01 July 2014

SSAS: OLAP PivotTable Extensions

Excel Pivot tables are a reporting tool that makes it easy to extract information from data sets without the use of formulas.
Excel Pivot tables are great for browsing Analysis Services cubes, since they allow to easily move, pivot and analyze data using drag and drop to see the same data in a number of different ways
OLAP PivotTable Extensions add advanced features to the pivot tables.
OLAP PivotTable Extensions are an Excel add-in which extends the functionality of PivotTables on Analysis Services cubes.
It works with Excel 2007, Excel 2010, and Excel 2013.
The Excel API has certain PivotTable functionality which is not exposed in the user interface. OLAP PivotTable Extensions provides an interface for some of this functionality and it also adds some new features like searching cubes, configuring default settings, and filtering to a list in your clipboard.



For more information and to install the OLAP PivotTable Extensions go to the codeplex website:

http://olappivottableextend.codeplex.com