SQL Training and SQL Server Reporting Services 2012 training in Honolulu

These are the training schedules for the2nd Quarter of 2014 at the Pacific Center for Advanced Technical Training (PCATT). There are two courses that you may be interested in, it is recommended that they are taken in the order shown to reap maximum benefit.

You can also access the reviews of the previous session in 2013 at the PCATT site.

Introduction to Structured Query Language (SQL)

 

SQL Server Reporting Services 2012: Native Mode Implementation

Please register with  http://pcatt.org/registration    or send an email to gcerny@hawaii.edu

March update of SQL Server Data Tools to support SQL Server 2014

On the heels of the new release date for SQL Server 2014 RTM, SQL Server Data Tools' (SSDT) availability was announced two days ago. It is fully compatible with SQL Server 2014.

Apart from bug fixes in the previous version, the following are the enhancements:

• Static code analysis extensibility
• Filter capabilities in the editable data grid
• Saving data compare settings to file (.dcmp)
• Additional TSQL Editor connection actions

With this all database tools are fully integrated with,

• Microsoft Visual Studio 2013 Express for web
• Microsoft Visual Studio 2013 for Windows Desktop
• Professional
• Premium
• Ultimate

These will be pushed via VS Updates. You could also bring it in using Tools | Extensions and Updates menu.

Visual Studio 2013 with SQL Server tools can be downloaded from here:
http://www.visualstudio.com/downloads/download-visual-studio-vs

Note that both SSDT VS 2010 and SSDT VS 2012 are available stand alone.
Review what's new in this update here:
http://blogs.msdn.com/b/ssdt/archive/2014/03/25/sql-server-data-tools-for-sql-server-2014-is-available.aspx

Review the posts for earlier version:
http://hodentek.blogspot.com/2012/12/sql-server-data-tools-gets-updated.html

http://hodentek.blogspot.com/2013/02/about-ssdt-and-vs-2012.html

Follow link for more:
http://msdn.microsoft.com/en-us/data/hh297027

In SQL Server 2014 you can use Multidimensional model to create Power View

This feature is new in SQL Server 2014.

In SQL Server 2012 Reporting Services you had the option to create Power Views using the Reporting Services Service in SharePoint 2010. In order to create the Power Views you needed to create tabular data model in SQL Server Analysis Services 2012. You needed a separate instance of SQL Server Analysis Services installed to support Tabular Data Model as the default was Multi-dimensional.

With SQL Server 2014 Power Views can be created using Multidimensional models, the same model used in OLAP Cubes. Just as Tabular Data Models supported Data Analysis Expressions(DAX), multi-dimensional models also support querying with DAX.

Power View is not exclusive to SharePoint but can be created using Excel 2013 although somewhat limited in features compared to those created using SharePoint.

Chapter 6: Power View and Reporting Services

This chapter in my book describes how to create Power Views from scratch.

What are 'views' in SQL Server?

Views are virtual tables created based on existing table(s). Views can have generally fewer columns than the underlying table(s). Views can be based on a single table or on multiple tables. Since you can pick and choose the columns, you can hide columns that you do not want to be seen and display only those you want. Since a view can choose from different tables (that can participate in a join), it is useful in front end design to query all the relevant tables and create a view.

For example the following query returns 91 rows of Customer data from Northwind database in SQL Server 2012 Express:

SELECT [CustomerID]
      ,[CompanyName]
      ,[ContactName]
      ,[ContactTitle]
      ,[Address]
      ,[City]
      ,[Region]
      ,[PostalCode]
      ,[Country]
      ,[Phone]
      ,[Fax]
  FROM [NORTHWND].[dbo].[Customers]

Let us say your front end application needs just CompanyName, Address, City, State, Country. Then you create a query as shown:

SELECT
      [CompanyName]
      ,[Address]
      ,[City]
      ,[PostalCode]
      ,[Country]+', '+[PostalCode]
     
  FROM [NORTHWND].[dbo].[Customers]

Now create the view based on this query as shown which you want to create a mail label

Create view MailLable
as
SELECT
      [CompanyName]
      ,[Address]
      ,[City]
      ,[PostalCode]
      ,[Country]+', '+[PostalCode] as 'Country'
      FROM [NORTHWND].[dbo].[Customers]

This creates a view object in View node of NORTHWND database called MailLable as shown.

The views in the database are themselves stored in a System View and you can find all existing views using the following query:

SELECT * FROM SYS.VIEWS

There are lot more things related to views we will take them up in another post.

SQL Server 2014 will be available on April 1

SQL Server 2014 RTM, billed as 'Microsoft Cloud First Platform' will be RTM on April 1, 2014
The main feature is the in-memory OLTP processing which will turbo- charge transaction processing making it as much as 30 times faster. In order to use this feature you need memroy-optimized databases and tables declared as memory optimized. These memory-optimized tables are stored in file groups.

In-memory transaction processing appears to be not new, but peer pressure from products already in the market makes it important to highlight this feature in this offering as it is fully integrated with the present version.

The other prominent feature is the enhancements to Microsoft Azure in particular the enhancements leading to integration between on premises and cloud based environments. SQL Server 2014 instances can be backed up to Windows Azure Storage which can then be deployed as Windows Azure VM by just invoking a wizard. This  can then be designated as a high availability node for SQL Server 2014.

Of course to reap all this benefit you need to purchase SQL Server 2014. I know people using SQL Server 2000 even now, I assume it is still supported.

There will be an upgrade path but not sure it will be from CTP1 version to RTM. Remove CTP1 version and clean up.

For more information read these articles:
http://www.infoworld.com/t/dbms/sql-server-2014-supercharged-in-memory-tables-azure-connectivity-238639?source=IFWNLE_nlt_daily_pm_2014-03-18

http://www.zdnet.com/microsofts-sql-server-2014-crowns-redmonds-data-platform-7000027444/

Brief over view of Memory-Optimized tables:
Two types of Memory-Optimized tables in SQL Server 2014
1. SCHEMA_AND_DATA
2. SCHEMA_ONLY

SCHEMA_AND_DATA Table
Remains in memory
Can be accessed after
  Server Crash
  shutdown
  Restart

SCHEMA_ONLY Table
Does not persist data after
  Server Crash
  Shutdown
  Restart
but the table structure is retained

To create these tables you need a database, in this case you need a MEMORY-OPTIMIZED database which supports MEMORY-OPTIMIZED tables. This is where the  file groups came in. You create a MEMORY-OPTIMIZED database with and store in file group.
You can create a MEMORY-OPTIMIZED database using SQL Server Management Studio or using script.

What is table type in SQL Server and how do you use it?

Table type is a special data type. It is used for storing a result set that can be processed later and therefore is a temporary storage of set of rows. This has been available since the release of SQL Serer 2015.

Like you declare, say an integer value by saying, 'declare @ int' you can declare a variable as of table type. One of the best practices is to store not more than 100 rows of data. If you want to store more, use temporary tables.

Table variables can be referenced in a Select Query.

With the above basic information let us see how to create it and use it.

CREATE TYPE MyTableType AS TABLE
(
    Name      varchar(10) NOT NULL,
    ValueDate date        NOT NULL,
    TenorSize smallint    NOT NULL,
    TenorUnit char(1)     NOT NULL,
    Rate      float       NOT NULL
    PRIMARY KEY (Name, ValueDate, TenorSize, TenorUnit)
);

The above code creates a table type called, MyTableType

Let us see how we can use it. Remember as we said earlier it is a data type and can be declared as a variable.

Now we declare it as follows:

Declare @x as MyTableType

Since @s is like a table we can insert values into the columns as shown here:

Insert @x values  ('Jay', '1/1/2014', 6, 'P',2.5); 

The values should match the definition of the table type.

Now we do a selection of columns in the table @x

SELECT * from @x

Here is a screen shot of the T-SQL and the result

That is all there is to it.