SQL-Variant Data Type in SQL Servers

Introduced in SQL Server 2008 and now available in all recent versions including Azure SQL Database; sql_variant is a data type that allows a column to have other data type elements. A single column can store int,char and binary data types. It can be used not only for columns but also for parameters, variables and return values of user defined functions.

These are some of the details you should know about:
sql_variant can have a maximum length of 8016 bytes.
sql_variant data type must first be cast to its base data type before participating in operations
sql_variant can be assigned a default value. This data type can have Null but they have no associated data type.
sql_variant cannot have sql_variant

Here is an example of a query run on a table that has a column (2nd) with sql_variant data type:

 

Hierarchical data in SQL Servers

Hierarchical data in SQL Servers was first introduced in SQL Server 2008 by providing the hierarchy data type. This data type allows you to store and query hierarchical data. Hierarchical data is a set of data related to each other by hierarchical relationships. Here are some examples:

• Parent ->Children->Grand children
• File system
• Projects tasks
• Links between web pages
• Organizational Structures

You create table in SQL Server with hierarchyID data type to describe data with hierarchical data structure.

Hierarchical Data           Level in Hierarchy
-----------------------------------------------------
John/Mary Parents             /
Their children                    /1/
Their grand children          /1/2/

A slash separates a parents from their children schematically shown for a family relationship:

SQL Server Managemnt Studio supports designing tabels with hierarchical data using hierarchical data type.

Stored Procedure using Common Table Expression

You can use the Common Table Expression (CTE) to create a Stored procedure. While CTE is only in memory the Stored Procedure is persisted.
In order to create a stored procedure I will be using the same Common Table Expression used in my two earlier posts here and here.

Run the following in the query pane of SQL Server Management Studio (herein SSMS 2012).
----
USE Northwind
GO
CREATE PROCEDURE dbo.GetName
@city varchar(10)
AS
WITH NCTE(FirstName, LastName,City)
  as

 (SELECT FirstName,LastName,City
 FROM Employees
 )
SELECT *
FROM  NCTE
WHERE city=@city
-----
After the statement is run you will find the stored procedure as shown (you may have to refresh the stored procedures node in the Object Explorer:

---
CTEStoredProc_00

In order to run the stored procedure execute the following statement in the query pane:
Exec GetName 'London'

You get the following query response:

CTEStoredProc_01

Watch out: SQL Server 2016 General Availability Imminent

Get ready to test out the database server deemed to be the ground-breaking version with numerous new features. It will be available in the near future. Get the free Developer's edition.

In Microsoft's own language,

"SQL Server 2016 seamlessly manages both relational and beyond relational (JSON, XML, Hadoop) data, seamlessly integrates structured data with Hadoop data, and seamlessly integrates on-premises data with cloud data"

It has best of both worlds, "relational and beyond relational).

SQL Server is ideally suited for:

•     Mission critical intelligent applications delivering real-time operational intelligence by combining built-in advanced analytics and in-memory technology without having to move the data or impact end user performance. 
•  Enterprise scale data warehousing with enhanced in-memory columnstore that increases query performance by over 100x vs disk based solutions. With SQL Server 2016, you can also access optimized MPP scale out software that can be combined with scale out appliance architecture with our Analytics Platform System (APS).
• Applications requiring the highest levels of security with new Always Encrypted technology that protects your data at rest and in motion without impacting database performance. 
• Comprehensive business intelligence solutions on mobile devices with an end-to-end mobile BI solution built-in that can deliver insights on any device. You can also manage all of your KPIs, mobile reports and traditional reports from a modern web portal. 
• New big data solutions that require combining relational data with non-relational data with PolyBase technology built-in that allows you to query structured and unstructured data with the simplicity of T-SQL. 
• Hybrid cloud solutions that can reduce storage costs, improve high availability and simplify IT operations with new Stretch Database technology. This keeps more of your historical data at your fingertips by transparently and securely stretching warm and cold OLTP data to Microsoft Azure on-demand without application changes. In addition, faster replicas can be placed in Azure to give you cost effective disaster recovery and backups.

Read this original article from Microsoft blog at MSDN.
Source and italicized text taken from the above article.

Install SQL Server Managment Studio 2016 Preview and get ready

This release of SSMS uses the Visual Studio 2015 Isolated shell and available in 11 languages.
The version number for this latest preview is: 13.0.14000.36

Here is the download lnk:
https://msdn.microsoft.com/en-us/library/mt238290.aspx

Here are major highlights including many bug fixes described in download site:

• Improvement in the SSMS installer - to add human-readable error messages.
• Improvement in the Stretch database wizard - to add support for predicates.
• Improvement in the Always Encrypted Powershell commandlet - to add key encryption APIs.
• Bug fix in the SSMS toolbar - to turn off IntelliSense if it has been disabled in the Tools > Options dialog.
• Improvements and bug fixes in the Showplan comparison user interface - to reduce the spacing used by long query plans.
• Numerous bug fixes in SSMS - to fix issues that caused SSMS to crash when exiting.

It takes a quite a bit of time to download (SSMS-Setup-enu, 834992KB) . When you run the application this is the first message you will get

Here are some screen shots of installation:

It takes quite a bit of time.

SSMS 2016 RC is an App in Windows 10

Stretch Database is a nice feature of SQL Server 2016

Source: MSFT Blog

Why do you need this?

Well you want to archive data (historical) but you do not want to store it locally as it is not frequently accessed and your resources are inadequate. However you do want to access. No problem, a query in SSMS can access that data (remotely stored in Azure) as easily as the on premises data. It is possible there may be a bit of latency from the remote site.

SSMS 2016 has a very good wizard to take care of the Stretch Database.  The Wizard is accessible via the Object Explorer. You need to select the database you want to extend using the Stretch Database feature. It is not necessary that you choose an entire database to extend; you can use the filter to include what you need to go into the Stretch database. Of course, since the remote is on Azure, you should have an account and proper credentials. The Stretch database is always online and querying should have no problem.

Stretch database is compatible with the other features; Transparent Data Encryption and Always Encrypted and Row-Level Security features.

You do require a Azure account but a free trial is available. There is a price for everything including the new Stretch database feature.

More here:
https://blogs.technet.microsoft.com/dataplatforminsider/2016/04/13/keep-data-virtually-forever-with-stretch-database-in-sql-server-2016/

Download SQL Server Management Studio 2016 Preview and Get ready

SQL Server Management Studio(SSMS) is an integrated environment consisting of several components made expressely for managing SQL Server infrastructure and Azure SQL Database (Azure Cloud). Using SSMS you can:

• Configure
• Monitor
• Administer instances of SQL Server.

It has full set of  Data-Tier Technology tools for databases and data warehouses

• Deploy
• Monitor
• Upgrade

It can do a lot more than the above that includes,
Download your free copy from here:
https://msdn.microsoft.com/library/mt238290.aspx
http://download.microsoft.com/download/8/6/3/8639523C-7F12-4CC3-8D2F-908C7A78B4C6/SSMS-Setup-ENU.exe

Import/Export Wizard Simply Explained

Import/Export Wizard is an utility program you find in SQL Server (almost all versions) that can be used for Exporting or Importing data between SQL Servers; between SQL Server and a number of other data sources; or import/export data between data formats including databases, spreadsheets and text files.

DTSW_00 and DTSW_01.png

Export/Import utility can be launched from a database node in a SQL Server or from command line by typing in DTSWizard at command prompt.
The Welcome screen of the Wizard is displayed as shown.

DTSW_02

Let us consider a simple case of copying a database from SQL Server 2012 to SQL Server 2014. I am choosing this pair as I have the two servers on my computer.

DTSW_03
In particular, I will be exporting the pubs database in SQL Server 2012 to the named instance of SQL Server 2014.

DTSW_04

In the SQL Server Import Export Wizard (DTSW) I launched from command-line earlier, I click Next.
The Choose a Data Source page is displayed as shown.

DTSW_05

Click on the drop-down handle for Data source: as shown in the next image. You will see the various data sources handled by this wizard.

DTSW_06

We shall use the default data source, in this case the SQL Server Native Client 11.0. Note that the program automatically chose the named instance REGENCYPARK (SQL Server 2012). However, both the SQL Servers are accessible to this program. Since we are exporting from SQL Server 2012 to SQL Server 2014, RegencyPark server is correct. We will export to Everest server.

DTSW_07

Since both servers were originally installed to work with Windows Authentication accept the default. There is a <default> Database. Click on the handle and set it to pubs by choosing it in the drop-down. Do not worry if you do not have pubs. It should work with any other choice.

DTSW_08
With all the choices made so far, the Choose a Data Source window should appear as shown.

DTSW_09

Click Next to display the Choose a Destination screen as shown.

DTSW_10

Enter the following on this screen:
Destination: SQL Server Native Client 11.0
Server Name: Hodentek8\EVEREST
Authentication: Windows
Click on the handle for Database. The following drop-down is displayed.

DTSW_11

Click on the button New... to open the following Create Database window.

DTSW_12

Insert a name at the top. Herein pubs2. Accept all other defaults, and the database files that are going to be created are displayed inside the screen The OK button at the bottom becomes active. Click OK.
 The Choose a Destination window appears as shown.

DTSW_13

Click Next> to display the Specify Table Copy or Query as shown.

DTSW_14

Accept the default, Copy data from one or more tables or views. Click Next> to display the Select Source Tables and Views with check boxes for each of the tables or views in the pubs database.

DTSW_15

Place check mark for the checkbox Source: and all the tables will be selected as shown.

DTSW_16

We will not edit the mappings (how the tables should go into the destination). Of course you can click Help to get to the help menu.

Click Next. The Save and Run Package is displayed. We accept the first option (Default) Run immediately. The other option is to save it as a SQL Server Information Services (SSIS) package.

DTSW_17

Click Finish>>| (you could also click Next> and look at some more details of what you have chosen to do so far). Complete the Wizard screen is displayed as shown:

DTSW_18

Click Finish on this screen. The program enters an execution stage and finally comes up with a screen announcing success and the number of rows transferred etc. as shown.

DTSW_19

Click on the Report button to display this drop-down list. You may choose an action you want to take.

DTSW_20
Now, go to the EVEREST server in SQL Server Management Studio for version 2014 and verify that pubs2 has been created and it has tables and data.

DTSW_21

That's it. Thanks for reviewing this post. 

Create a view using Common Table Expression

You can use CTE to create a view. Although CTE is in memory, the View will be persisted and can be found in the VIEWS folder in Object Explorer.

I will use the same CTE that I used in the previous post to create the view.
Run this script in the query pane of SQL Server Management Studio (herein SQL Server Developer 2012).

USE Northwind
Go
---define common table expression
Create view CityFolk
as
WITH NEmp_CTE (FirstName,LastName,CITY)  /*Name: Nemp_CTE Column List:FirstName, LastName,City*/
AS
---define the CTE query
(SELECT FirstName,LastName,City
from Employees
where City in ('Redmond','London')
)
Select * from NEmp_CTE
go

You will get the 'Command(s) completed successfully response.

Now you can find the view CityFolk in the VIEWS folder as shown.

CTEView