Must see Video: SQL Database for Developers

SQL Server 2016 is advertised to be the most significant SQL Server in recent times and has a plethora of new ideas and concepts. This presentation (over an hour long) really provides a great background for SQL Server 2016 which is in RC3 presently but appears to be on Azure (preview?) already.

This is a video that you must see if you are a SQL Database Developer. The presentation (nearly an hour, March 30, 2016 Build event) by Lindsey Allen and Tony Petrossian is really informative, helpful and well made.

The stated overview of the presentation was:
"Microsoft offers SQL Server and Azure SQL Database to help you develop great relational database applications. In this session you will learn about the top developer features coming in SQL Server 2016 which are already in Azure SQL Database. Additionally, you will see the latest investments in Azure SQL Database that enable you to easily manage thousands of databases and get the performance and security insight needed to build robust and secure applications in the cloud.
"
You can hear the full presentation with all the demos here:
(Day 1 of the Microsoft Build meeting. Lindsey Allen and Tony Petrossian March 30, 2016)

The following demos were presented to deliver the 'wow' impact (it is possible that I might have omitted some).

• In-memory OLTP Demo nicely presented.
• Always encrypted demo(Security schema, security policy  etc)
• Dynamic data matching
• SQL Injection/Auditing
• Demo with Python script
• Demo with Ruby

The video presentation is copied over here as well:



However, if you are short on time and just would like to glance over the slides they used, you may review the following images captured from the video as screen shots.

 !!!!

 !!!!

 Comparison

Common Table Expression (CTE) simply explained

Microsoft documentation defines Common Table Expression thus,

"A common table expression (CTE) can be thought of as a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. A CTE is similar to a derived table in that it is not stored as an object and lasts only for the duration of the query. Unlike a derived table, a CTE can be self-referencing and can be referenced multiple times in the same query.

It is unlike a temporary table (persisted) during a connection or a view persisted in the database. CTE is only in memory.

We will consider CTE's many use case scenarios in later posts and the objective of this post is to give a simple example using the Northwind database.

A CTE should have the following:

• CTE with a CTE Expression name
• An Optional Column list
• A query defining the CTE

After a CTE is defined it can be referenced like a table or view and you can do all of the following statements:

• Select
• Insert
• Update
• Delete

It can also be used in defining a view by providing the columns.

Now let me define CTE using the Employees table in Northwind. The example is just to show the syntax as the result can be obtained from a single query without using CTE.

USE Northwind
Go
---define the common table expression
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 ('Tacoma','London')
)
---Use it in a Select Query
SELECT LastName,FirstName,CITY
From NEmp_CTE
where LastName <>'Buchanan'
go

The response to this query is:

Note: The response such as the above does not really need a Common Table Expression but the point was to show the construction of the Syntax used for CTE. In fact, it is to make complex queries more readable.

Download SQL Server 2016 Release Candidate 3 and evaluate

SQL2K16RC3.png
You can download the 120 day edition from here after registering.

Three types of download (only 64-bit):

ISO
CAB
Azure

Hardware and software requirements (abbreviated):

• .NET Framework 4.6, Installation setup installs.NET Framework
• Network Software: Shared memory, Named Pipes, TCP/IP and VIA
• Hard disk space: Minimum of 6GB
• Drive/Monitor/Internet required.
• Memory: 1GB
• Processor: x64 bit 1.4GHZ
• Processor type: x64 Processor: AMD Opteron, AMD Athlon, Intel Xeon with Intel EM64T Support and Intel Premium with EM64T support
• No support for WOW 64

Here are the benefits of SQL Server 2016:
Enhanced in-memory performance provide up to 30x faster transactions, more than 100x faster queries than disk based relational databases and real-time operational analytics
◾New Always Encrypted technology helps protect your data at rest and in motion, on-premises and in the cloud, with master keys sitting with the application, without application changes
◾Built-in advanced analytics– provide the scalability and performance benefits of building and running your advanced analytics algorithms directly in the core SQL Server transactional database
◾Business insights through rich visualizations on mobile devices with native apps for Windows, iOS and Android
◾Simplify management of relational and non-relational data with ability to query both through standard T-SQL using PolyBase technology
◾Stretch Database technology keeps more of your customer’s historical data at your fingertips by transparently stretching your warm and cold OLTP data to Microsoft Azure in a secure manner  without application changes
◾Faster hybrid backups, high availability and disaster recovery scenarios to backup and restore your on-premises databases to Microsoft Azure and place your SQL Server AlwaysOn secondaries in Azure

There are several different types of SQL Server 2014 available and detials are here:
https://msdn.microsoft.com/en-us/library/ms143506(v=sql.130).aspx#hwswr

How do you explain this correlated subquery?

This post is to explain the processing of a correlated subquery as this type of subquery is  not so straight forward.

Non-correlated subquery is easy to understand as the inner query is evaluated first and then the outer query is evaluated using the value(s) returned by the inner query.

In the case of correlated subquery, the outer query is run first and then the inner query is run for each value of outer query to find the match for the value returned.

This example is chosen to explain the processing of a correlated subquery as the number of outer evaluations is quite small (only 9 employees). This is a query to the Employees table in the Northwind database in SQL Server.

This query is trying to find the employees living in a city from where the orders were also shipped.



This returns the following response:

Of the 9 employees only 6 of the above employees lived in the same city where the orders were shipped.

Now review this tutorial created using Sway to follow the processing:
https://docs.com/jayaram-krishnaswamy/4697/undestanding-the-processing of a correlated

Get Microsoft SQL Server Developer Edition for FREE

Not long ago I paid 60+ dollars to buy SQL Server Developer Edition from Microsoft Store. Since end of March 2016, you can get it for free. That's very generous.  It is a free download but you may have to sign-in to Visual Studio Dev Essentials before you can download SQL Server 2014 Developer Edition. It is assumed that you have a licensed Windows 10 or Windows Server.

SQL Server Developer edition has all the capabilities of the Enterprise Edition, however it is only for development and testing. Of course you can do BI on it. You may not run a business with it.

In Microsoft's own language,
"Visual Studio Dev Essentials is Microsoft’s most comprehensive free developer program ever, with everything you need to build and deploy your app on any platform, including state-of-the-art tools, the power of the cloud, training, and support."

This is great stuff. If you are serious you will not let go of this opportunity.

Go this URL to download SQL Server 2014 Developer Edition with Service Pack 1 (x86) or (x64)
https://myprodscussu1.app.vssubscriptions.visualstudio.com/Downloads?pid=1682









More on this news here:
https://blogs.technet.microsoft.com/dataplatforminsider/2016/03/31/microsoft-sql-server-developer-edition-is-now-free/

Creating a Scalar-valued function in SQL Server 2014

First of all what is a Scalar-valued function?

A scalar function in SQL Server is similar to the generally accepted meaning function, a function is defined by the variables (parameters) with some operation on them and the resulting value is the return value of the function.

• If x and y are the variables(parameters) than the
  function(x,y)=x+y defines a scalar function that returns a value which is the sum of x and y.
• The scalar function is not limited to just two parameters and can have more.

Where do you find the Scalar-valued function in the Object Explorer?

You will find the Scalar-valued function node in all the databases in the Programmability node as shown here for AdventureWorks2012 database.

Sway5_01

How do you create a Scalar-Valued Function in SQL Server Management Studio?

Watch this Swaytorial.

https://docs.com/jayaram-krishnaswamy/9721/creating-a-scalar-valued-function-in-sql-server

Creating a Scalar-valued function in SQL Server—Jayaram Krishnaswamy

Docs.com