I was interested reading a post on the MVP group about DISTINCT FROM by Erland who was championing the IS DISTINCT FROM clause but had found a work around using INTERSECT.

That led me to I dig into ansi and non ansi_null comparisons and what operators honour the setting and what ignore it.

 

To drive this I was doing a full join to find different rows between two tables. I always thought that setting ansi nulls would solve the problem of comparisons on A.Column = B.column

i.e. you could just say

A.Column <> B.Column

rather than having to do something like

ISNULL(A.Column,<some default obscure value>) <> ISNULL(B.<the same obscure value>)

to ensure that NULL values on both side are treated as the same. However I find that is not the case

But found that what you want to do is do something is Not (A.Column=B.Column), if you do this the optimiser changes this to A.Column <> B.Column which is not the same as Not (A.Column=B.Column), because if either are NULL then the result in both cases is unknown, which isn’t “True” and thus False. Subtlty of unknowns existing in an Boolean expression. Check out the table below. by effective result I mean if you use the expression in a CASE WHEN clause or a WHERE clause, it only really cares about TRUE thats what causes values to use the THEN clause, everything else uses the ELSE clause.

A B Result Effective Result NOT Result Effective Result
1 1 True True False False
2 1 False False True True
NULL NULL Unknown False Unknown False
1 NULL Unknown False Unknown False

With ANSI NULLS OFF

A B Result Effective Result NOT Result Effective Result
1 1 True True False False
2 1 False False True True
NULL NULL True False False True
1 NULL False True True False

That’s what one would like for ANSI NULLS but you don’t get that. It appears that you only get a behaviour change when you are comparing an expression with one or more variables on the left of right.

However if you are comparing columns directly it doesn’t honour the ANSI NULL setting unless you do use a case expression with like this

CASE <Expression> WHEN <result> THEN …..

This sort of makes the ANSI null setting a bit pointless and given the note in BOL  (https://msdn.microsoft.com/en-us/library/ms188048.aspx) about it

image

and if you dig a bit into that BOL page you will see that the behaviour needing an variable or a literal for ANSI NULLS to work is also in there

 

SET ANSI_NULLS ON affects a comparison only if one of the operands of the comparison is either a variable that is NULL or a literal NULL. If

both sides of the comparison are columns or compound expressions, the setting does not affect the comparison.

Summary

 

In summary NULLS suck, and whilst you can avoid them for your tables, when comparing tables using FULL JOINS or LEFT JOINS you can’t and in that sense if you use the INTERSECT method (where two columns with NULL are considered equal, go figure) then you will get the behaviour you want.

 

Paul’s blog goes into this in similar detail and into the different query plan operators

http://sqlblog.com/blogs/paul_white/archive/2011/06/22/undocumented-query-plans-equality-comparisons.aspx

 

The following script highlights the differences between the different comparisons.

 

       declare @null int =null

       set ansi_nulls off

 

       select case when A.Id <>B.Id then 0 else 1 end [A.Id <>B.Id]

               , isnull((select 0 where A.Id <>B.Id ),1) [A.Id <>B.IdSQ]

    

               , case when A.Id =B.Id then 1 else 0 end [A.Id =B.Id]

              , isnull((select 1 where A.Id =B.Id ),0) [A.Id =B.IdSQ]

   

              , case a.id when b.id then 1 else 0 end [a.id when b.id]

     

               , case when a.c <> b.c then 0 else 1 end [a.c <> b.c]

               , isnull((select 0 where A.c <>B.c ),1) [A.c <>B.cSQ]

    

               , case when a.c = b.c then 1 else 0 end [a.c = b.c]

               , isnull((select 1 where a.c = b.c ),0) [a.c = b.cSQ]

 

               , case a.c when b.c then 1 else 0 end [a.c when b.c]

    

               , case when a.c <> @null then 0 else 1 end [a.c <> @null]

               , isnull((select 0 where A.c <>@null ),1) [A.c <> @nullSQ]

    

               , case when a.c = @null then 1 else 0 end [a.c = @null]

               , isnull((select 1 where a.c = @null ),0) [a.c = @nullSQ]

 

               , case a.c when @null then 1 else 0 end [a.c when @null]

               , case when exists (select a.id, a.c intersect select b.id, b.c) then 1 else 0 end [intersect]

               , case when null = null then 1 else 0 end

               , case null when null then 1 else 0 end

               , A.Id, B.Id

               , A.c, B.c

       from (values(1,1),(2,null),(3,null),(4,1)) A(Id,c)

       full join (values(2,null),(3,1),(4,1),(5,null))B(id,c) on A.Id = B.Id

 

       go

       declare @null int =null

       set ansi_nulls on

       select case when A.Id <>B.Id then 0 else 1 end [A.Id <>B.Id]

               , isnull((select 0 where A.Id <>B.Id ),1) [A.Id <>B.IdSQ]

    

               , case when A.Id =B.Id then 1 else 0 end [A.Id =B.Id]

              , isnull((select 1 where A.Id =B.Id ),0) [A.Id =B.IdSQ]

   

              , case a.id when b.id then 1 else 0 end [a.id when b.id]

     

               , case when a.c <> b.c then 0 else 1 end [a.c <> b.c]

               , isnull((select 0 where A.c <>B.c ),1) [A.c <>B.cSQ]

    

               , case when a.c = b.c then 1 else 0 end [a.c = b.c]

               , isnull((select 1 where a.c = b.c ),0) [a.c = b.cSQ]

 

               , case a.c when b.c then 1 else 0 end [a.c when b.c]

    

               , case when a.c <> @null then 0 else 1 end [a.c <> @null]

               , isnull((select 0 where A.c <>@null ),1) [A.c <> @nullSQ]

    

               , case when a.c = @null then 1 else 0 end [a.c = @null]

               , isnull((select 1 where a.c = @null ),0) [a.c = @nullSQ]

 

               , case a.c when @null then 1 else 0 end [a.c when @null]

               , case when exists (select a.id, a.c intersect select b.id, b.c) then 1 else 0 end [intersect]

               , case when null = null then 1 else 0 end

               , case null when null then 1 else 0 end

               , A.Id, B.Id

               , A.c, B.c

       from (values(1,1),(2,null),(3,null),(4,1)) A(Id,c)

       full join (values(2,null),(3,1),(4,1),(5,null))B(id,c) on A.Id = B.Id

 

http://sqlblog.com/blogs/paul_white/archive/2011/06/22/undocumented-query-plans-equality-comparisons.aspx

Posted by simonsabin | with no comments

Are you a SQL Server user and use SQL Server Management Studio and think it could be better?

Are there common tasks that you do that you think SSMS could do something to make easier?

Are you trying to implement SSDT but are hitting problems?

Do you use other tooling and would like some of those features in the core SQL Server tools?

 

If any of those are true or you just want to see what the direction of SQL Server tooling is and what this whole monthly release thing is about, then you need to pop along to our meetup tonight, at Microsoft’s office in Paddington.

https://www.eventbrite.com/e/sql-server-tools-and-ssdt-shape-the-future-tickets-25256718525

 

We have Kevin Cunnane and Eric Kang from the SSDT team and Sanjay Nagamangalam and Ken Van Hyning who will explain the renaissance of the SSDT and SQL Tools, how the team is working, what they are thinking about the future, how you can help them influence that future.

Posted by simonsabin | with no comments

I always am amazed at what can be done on websites and in reports and in general with technology today to make things look visually amazing. You only have to look at the animated movies nowadays to look at the extremes, or the videos of people using photo shop to morph a cat into a dog. Amazing.

 

What also never fails to surprise me is how easy it looks and then when I go and try it looks maybe not so slick. If you want examples of the my awesome design skills one only has to look at the SQLBits.com website.

image

 

Visual data representation whether it be a dashboard or a report is one of these areas. Doing it well takes experience.

 

With the custom visuals in Power BI you can really do some amazing things. (You can do some amazing things in Reporting Services as well if you had enough time)

 

If you are interested in designing awesome PowerBI then you must attend this webinar

https://info.microsoft.com/CO-PowerBI-WBNR-FY16-04Apr-19-Design-Reports-in-PowerBI-Registration.html?ls=social?yammer

 

“Reporting is more than just charts, numbers, and percentages...designing a report is an art form. In this presentation, Miguel Myers will teach you the difference between reports, indicators/gauges, presentations, dashboards, and infographics; including how you can take advantage of each one and how you can design them using Power BI. Miguel will teach you his secrets, such as how using other external tools to understand color, texture, space, balance and saturation can help you to create unique and clean designs.

Using colors, shapes, fonts, and images are just 30% of the labor when you create a report. There are other secrets behind the shiny charts and shapes that make your report complete; secrets that even experts don't want to share at all.

Are you ready to design your reports as never before? Please join us to learn more about data graphic design. Invite your employees, coworkers, bosses, and friends. “

Posted by simonsabin | with no comments

The SQL Server Reporting Services Manager web portal has been re-written for 2016.  and this has provided a number improvements that have made it very quick.

 

In doing that they’ve made some changes to the UI. The first thing that got me was, “where are my data sources”. I had written a littel powershell script to upload reports, data sets and data sources but when I went into the portal they weren’t there.

 

At this point my question were is it an issue with my powershell, my install of SQL Reporting Services 2016 or somethign else.

 

I finally convinced myself that the first two weren’t the issue by looking at what was returned from the webservices I dig some digging. and hey presto.

 

There is a new “View” button in the menu that allows you to display different object types and data sources are off by default. I’m not sure I like the behaviour, if you don’t want them displayed hide them.

image

What I would like to see is that when you have a view that is filtered that there is some indication that there are other objects that have been filtered from the view. I’m not the only one that will wonder where there objects are.

 

To download and try SQL Server 2016 go to http://www.microsoft.com/en-gb/server-cloud/products/sql-server-2016/

For more on the changes in Reporting services go to https://msdn.microsoft.com/en-GB/library/ms170438.aspx

and  read the ssrs blog https://blogs.msdn.microsoft.com/sqlrsteamblog/

Posted by simonsabin | with no comments

I recently installed SQL Server 2016 on my surface to get all our SQLBits reports sorted. What I couldn’t figure out was why it was so quick. I thought it might be because it was a local install and running on an SSD based surface but that couldn’t account for the blazing difference with previous versions.

 

Well the answer is much better.

 

The report manager interface has been re-written from the ground up to make it

1. More performant

2. More functionality

3. Support more browsers

4. Support HTML 5

5. Enable branding.

 

Whilst you can say about last, it shows that SSRS isn’t dead and but has risen from the ashses into a much better product. Whats more the paginate report is front and centre.

 

I also find that Report Builder performs better, which I assume is in part down to the re-writes elsewhere in the stack, and the click once deploy has gone. You now get the consistent download experience for all the MS reporting tooling, from Report Builder to Power BI desktop

 

To download and try SQL Server 2016 go to http://www.microsoft.com/en-gb/server-cloud/products/sql-server-2016/

For more on the changes in Reporting services go to https://msdn.microsoft.com/en-GB/library/ms170438.aspx

and  read the ssrs blog https://blogs.msdn.microsoft.com/sqlrsteamblog/

Posted by simonsabin | 1 comment(s)
Filed under: , ,

I had this from the SQL Team. If you care about documentation please provide feedback.

The SQL Server writers are working to improve the documentation on MSDN/BOL, and we need your help.

Please consider taking this short, 9-question survey (http://www.instant.ly/s/cwef3/nav#p/186a0 ) to send us your thoughts on how we can improve the documentation for you. We are listening to your feedback.

Posted by simonsabin | with no comments

Having recently been having rebuilding my machine I finally decided to automate the process of installing the software I need.

This was a life saver as I was reinstalling a few times to try and figure out why I wasn’t getting sound on my external monitor. So I was gradually uninstalling everything until I found out that it was Hyper-v that was causing the problem.

The outcome meant I was installing PowerBI lots and had to automate it.

The answer was a few simple bits of powershell, that checks, I really should turn this into a powershell DSC definition, that will be next.

I believe this will work moving forward as they release new versions. I’ve been told the fwLink will point to the current version. Time will tell

 

$PBIExe = "C:\Program Files\Microsoft Power BI Desktop\bin\PBIDesktop.exe"

$install = $false

$PBIConfig = Download-Json "http://download.microsoft.com/download/9/B/A/9BAEFFEF-1A68-4102-8CDF-5D28BFFE6A61/PBIDesignerConfig.json"

   

if (test-path $PBIExe) {

    $installedVersion = [version]$(gi $PBIExe).VersionInfo.FileVersion

 

    $currentVersion = [version]$($PBIConfig.release.x64 | where {$_.key -eq "ClientUpdateVersion"}).value

    if ($currentVersion.Major -gt $installedVersion.Major -or$currentVersion.Minor -gt $installedVersion.Minor -or $currentVersion.Build -gt $installedVersion.Build) {$install=$true}

}

else{$install=$true}

 

if ($install){

    $msiPath = "$env:temp\PowerBI.msi"

  

    $download=$true

    if (test-path $msiPath ){

        #Code to check if one needs to download the msi

    }

 

    if ($download){Download-File 'http://go.microsoft.com/fwlink/?LinkID=521662' $msiPath}

   

    msiexec /i $msiPath ACCEPT_EULA=1 /passive }

 

This uses two functions Download-File and Download-Json

 

function Download-Json {

param ([string] $url)

  $downloader = new-object System.Net.WebClient

  , $downloader.DownloadString($url) |ConvertFrom-Json

}

 

function Download-File {

param ([string] $url, [string] $file)

  $downloader = new-object System.Net.WebClient

  , $downloader.DownloadFile($url, $file)

}

Posted by simonsabin | 1 comment(s)
Filed under: ,

This is the set of videos from the SQL Server Engineering Team on SQL Server 2016. This post is covering Upgrade.

For the other groups of videos have a look at.

Videos on You tube.

Upgrade and Migration to SQL Server 2016
Lonny Bastien (LinkedIn|Twitter)

Watch a brief overview of the new SQL Server 2016 Upgrade Advisor and SQL Server Migration Assistant 7.0. Lonny highlights the new assessment and migration features to support SQL Server 2016 upgrades and foreign platform migrations.

This is the set of videos from the SQL Server Engineering Team on SQL Server 2016. This post is covering The SQL Engine, TSQL and High Availability.

For the other groups of videos have a look at.

Videos on You tube.

In-Memory OLTP in SQL Server
Jos de Bruijn (LinkedIn|Twitter)

Learn how In-Memory OLTP delivers performance improvements up to 30X for transactional workloads in SQL Server and Azure SQL Database. See how enhancements made in SQL Server 2016 make it easier to use and adopt in your applications, and improve performance and increase scalability even further.

Stretch Database- Leverage infinite storage and compute capacity in Azure with SQL Server 2016
Joe Yong (LinkedIn)

Are business requirements or industry/legal regulations requiring you to keep data for extended periods of time? Is your database growing to unmanageable sizes and are storage costs dominate your IT budget? On this Data Driven session, learn how SQL Server 2016 Stretch Database allows you to keep as much data as you want while reducing maintenance efforts and overall costs without changing your application.

Always On Availability Groups enhancements in SQL Server 2016
Kevin Farlee (LinkedIn|Twitter)

"On this Data Driven session, we explore Always On Availability Groups, which are extremely popular in enterprise deployments where availability is critical to the success of the application. Learn about the advances being made in SQL Server 2016 which help to unblock even more scenarios and increase performance.

SQL Server 2016 Query Store Simplifying Query Performance Troubleshooting
Mike Weiner (LinkedIn|Twitter)

On this Data Driven session, you'll learn why database query performance is a mission critical aspect of any solution. Introduced in SQL Server 2016, the Query Store gives database administrators and developers a simple tool to help troubleshoot, understand and rectify poor performing queries. The Query Store can also be used to analyze query performance and workload patterns across an extended period of time (from weeks to years) and to de-risk upgrades by allowing for before and after query plans, saving and reverting characteristics.

Dynamic Data Masking SQL Server 2016
Tommy Mullaney (LinkedIn|Twitter)

Sensitive data or personally identifiable information (PII) stored in a database often needs to be obfuscated, so that it's only available to specific people on a need-to-know basis. On this Data Driven session, you'll learn how Dynamic Data Masking in SQL Server 2016 helps you easily limit access to sensitive data fields without requiring any changes to your application code.

Effortlessly Analyze Data History Using Temporal
Jovan Popovic (LinkedIn)

On this Data Driven session, find out how temporal support in SQL Server 2016 enables you to easily keep an entire history of changes in your tables, go back to any point in time and run interesting analyses. See how easily you can use temporal tables in SQL Server 2016.

JSON as a bridge between NoSQL and relational worlds
Jovan Popovic (LinkedIn)

On this Data Driven session, learn how new JSON functions in SQL Server 2016 enable you to load JSON text in tables, query both standard tables and JSON data, or export content of tables as JSON. See how easily you can combine relational and NoSQL concepts in SQL Server 2016.

Azure VM is the best platform for SQL Server 2016
Luis Carlos Vargas Herring (LinkedIn|Twitter)

This Data Driven session discusses why Azure Virtual Machine is the best cloud platform to run SQL Server 2016. It provides an overview of the SQL Server deployment, configuration, management and monitoring.

Full Lifecycle of Financial Data- Using SQL Server 2016
Marko Hotti (LinkedIn)

This Data Driven session shows you how SQL Server 2016 handles payment card transactions in real-time, allows the real-time analysis of the transactions, secures the sensitive card holder´s data using Always Encrypted, analyses anomalies in the payments using R integration and stretches transaction history into Azure with Stretch Database.

This is the set of videos from the SQL Server Engineering Team on SQL Server 2016. This post is covering BI, Reporting and Integration.

For the other groups of videos have a look at.

Videos on You tube.

Hybrid BI- Accessing on-premises data with Power BI
Dimah Zaidalkilani (LinkedIn)

In this Data Driven session, we walk through the various ways to keep your reports and dashboards in Power BI current. If you have on-premises data sources such as SQL Server that you've been waiting to refresh from Power BI – this session is for you!

Design Mobile Reports (SQL Server Mobile Report Publisher)

We review how to create Mobile Reports, which are new to SQL Server Reporting Services 2016. The new Mobile Report Publisher is so simple to use that anyone – even a user with minimal technical skills – can create effective mobile reports that scale perfectly to all screen form-factors, including phones, tablets, and modern web browsers.

Create shared data sources and datasets for your reports
Riccardo Muti (LinkedIn|Twitter)

On this Data Driven session, see how SQL Server 2016 Reporting Services enables you to access both traditional, paginated reports as well as the new mobile reports using modern browsers and mobile devices thanks to a brand-new web portal and Power BI mobile apps.

Monitor critical business metrics and trends at a glance with KPIs
Christopher Finlan (LinkedIn|Twitter)

In this Data Driven session, we'll review the new KPI functionality in Reporting Services, which enables the rapid publishing of key metrics in a way that guarantees premium native user experiences on all device and screen form-factors.

Access reports in modern browsers and mobile devices
Riccardo Muti (LinkedIn|Twitter) and Christopher Finlan (LinkedIn|Twitter)

On this Data Driven session, see how SQL Server 2016 Reporting Services lets you access both traditional, paginated reports as well as the new mobile reports by using modern browsers and a brand-new web portal built on HTML5 technology. Additionally, you can access your KPIs and mobile reports on your smartphone or tablet using the Power BI apps available for all major mobile platforms.

Design modern paginated reports
Riccardo Muti (LinkedIn|Twitter)

On this Data Driven session, find out how we've overhauled Reporting Services in SQL Server 2016 to provide a modern, on-premises solution for deploying and managing reports within your organization. You can continue to create paginated reports - what you traditionally think of as Reporting Services reports - and also create mobile reports which are optimized for smartphones and tablets. In this video, we'll focus on how we've modernized and enhanced paginated reports - both the tools you use to design and view them as well as the reports themselves.

Effortlessly Analyze Data History Using Temporal
Jovan Popovic (LinkedIn)

On this Data Driven session, find out how temporal support in SQL Server 2016 enables you to easily keep an entire history of changes in your tables, go back to any point in time and run interesting analyses. See how easily you can use temporal tables in SQL Server 2016.

Real-Time Operational analytics with SQL Server 2016
Sunil Agarwal (LinkedIn|Twitter)

On this Data Driven session, we'll go over how Real-Time Operational Analytics in SQL Server 2016 combines two industry leading SQL Server technologies–In-memory OLTP and In-memory Analytics–using Columnstore index. Learn how it enables the creation of columnstore indexes directly on transactional tables which eliminates the need for a separate data warehouse and data movement. See how your transactional workload can continue to run with minimal performance impact while colunmstore index speeds up analytics.

SSAS Tabular semantic model updates for SQL Server 2016
Kasper de Jonge (LinkedIn|Twitter)

In this Data Driven session, we take a look at the updates to the Analysis Services Semantic model which now adds over 50 new DAX functions and support for many patterns and translations.

Analysis Services DirectQuery for Tabular models
Kasper de Jonge (LinkedIn|Twitter)

On this Data Driven session, we'll go over updates to Analysis Services DirectQuery and how to build a semantic model over your data for consistency across reporting and analysis without storing any data in Analysis Services itself. The new update enables high performing direct access to data without storing or caching aggregates in Analysis Services which gives you performance enhancements up to 20x faster than in previous versions of SSAS.

SSIS Catalog Custom Logging Levels SQL Server 2016
Matt Masson (LinkedIn|Twitter)

In this Data Driven session, you'll get a glimpse of new functionalities included within SQL Server Integration Services 2016. Learn how to define custom logging levels for package executions within the SSIS Catalog and about the ssis_logreader role for seeing the SSIS logs. The best part is, you don't need to be DBA / Admin anymore to have access to the logs. Lastly, we discuss benefits such as how to capture the exact information you need and the flexibility of roles and reporting, to give you deeper insights into SSIS package catalogs.

Fraud Analysis using a hybrid implementation of Azure SQL Data Warehouse and SQL 2016 Reporting
Matt Goswell (LinkedIn|Twitter) and Sanjay Soni (LinkedIn)

On this Data Driven session, find out how business models are becoming more reactive in order to adapt to changing market conditions and channel opportunities. With the increase in breadth comes an increase of potential fraud, making Microsoft data warehousing capabilities critical in managing large volumes of disparate and relational data. Learn how to combine it for fast, accurate and validated information beautifully visualized through Microsoft Power BI and SQL Server 2016 Reporting Services including Mobile BI. Be connected to your data anywhere in the world, all with the immersive power and experience of touch.

Master Data Services 2016
Rama Raman (LinkedIn)

Master Data Services is a multi-dimensional platform that allows enterprises to manage and curate their master data. Over the past releases of Master Data Services, we received great feedback from our customers and have addressed many of the requests in the new SQL Server 2016 release. This Data Driven session will provide you insights into these new capabilities.

More Posts Next page »