SQL Server 2016 Data Types

Based on the table design using SQL Server Management Studio, v17.7, the following data types can be identified. These are the ones you find in SQL Server 17 as well.

Data types (text,ntext, image) continues to be present although Microsoft has been saying that they will be deprecated in a future version of SQL Server. 

Finding a masked column in a table

If the table belongs to a database, then in the context of the database run the query:

SELECT * FROM sys.columns

If the column(s) is masked you should look for the column 'is_masked' in the response as shown.

Here is an example. The FirstName column in the Employees table in Northwind database is masked using the default masking function. When you run the above query this is what you will see.

PowerShell commands using SQL Server Management Studio v17.7

PowerShell is very well supported in SQL Server and it is true with the latest version as well.

What you begin with in Power Shell depends on from where it is invoked in the SQL Server Management Studio.

You can invoke (or start) PowerShell from various objects and it will be contextual in relation to the object.

If you right click the Server as shown you poup-up a mneu from which you can access PowerShell as shown.

PS_0

When you click Start PowerShell, the PowerShell window gets displayed as shown.

PS_1

Similarly the next image shows PowerShell being invoked in other contexts.

PS_2

These are not the only objects to invoke PowerShell, many more objects can be used to invoke PowerShell contextually.

Once you get to the object you want to review, the database can be accessed like folder/files using windows commands as shown.

PS_3

Read many more related PowerShell posts here.

Get the latest version of SQL Server and latest version of SSMS

There have been many versions of SQL Server released over a long period of time and of course these are followed up by Service Packs (SP). It is hard to know which version is going to be installed.

There is an unofficial chart lists of all the known SP;s, Cumulative Updates(CU), patches and hot-fixes from SQL Server 7.0 to SQL Server 2017.

SQL Versions.png

Read here: https://sqlserverbuilds.blogspot.com/

I have a reasonably latest version of SQL Server 2016 Developer edition:

Microsoft SQL Server 2016 (SP1-GDR) (KB4019089) - 13.0.4206.0 (X64)   Jul  6 2017 07:55:03   Copyright (c) Microsoft Corporation  Developer Edition (64-bit) on Windows 10 Pro 6.3 (Build 17134: ) (Hypervisor) 

The following version download is here:

13.0.5206.0 4/9/2018; SQL Server 2016 SP2
https://www.microsoft.com/en-us/download/details.aspx?id=56836

Download details for SQL Server 2016 SP1 with the following details:

https://www.microsoft.com/en-us/download/details.aspx?id=54276

---------------

Version:            13.0.4001.0
File Name:          ENU\SQLServer2016SP1-KB3182545-x64-ENU.exe
Date Published:     11/15/2016
File Size:          551.8 MB

----------------

For SQL Server 2016 SP1 and above you do not install SQL Server Management Studio. You need to install it separately.

https://youtu.be/Hc1cXzdjPGQ

What is wrong with this image?

I am not sure where Google gets it data for the analytics of its own pages on the Blogger but I fail to see why it is so differently displayed. Is there some kind of filtering at some point.

Here is an example from my site http://hodetenk.blogspot.com

Read more here:
http://hodentekhelp.blogspot.com/2018/05/are-these-blogger-stats-to-be-believed.html

Upgrade your Visual Studio 2017 Community

I had the earlier version 15.5.6. In a way, I was forced to upgrade to the next higher 15.5.7.

The Visual Studio 2017 community has the link to Visual Studio Installer and makes it very easy to install.

This is the program I have after upgrade;

The installer presents you with number of options. I do not work with Games and some other stuff. But these are the ones I have chosen to install.

Microsoft Visual Studio Community 2017
Version 15.5.7
VisualStudio.15.Release/15.5.7+27130.2036
Microsoft .NET Framework
Version 4.7.03056
Installed Version: Community
Visual Basic 2017   00369-60000-00001-AA719
Microsoft Visual Basic 2017
Visual C# 2017   00369-60000-00001-AA719
Microsoft Visual C# 2017
Visual C++ 2017   00369-60000-00001-AA719
Microsoft Visual C++ 2017
Visual F# 4.1   00369-60000-00001-AA719
Microsoft Visual F# 4.1
Application Insights Tools for Visual Studio Package   8.10.01106.1
Application Insights Tools for Visual Studio
ASP.NET and Web Tools 2017   15.0.31129.0
ASP.NET and Web Tools 2017
ASP.NET Core Razor Language Services   1.0
Provides languages services for ASP.NET Core Razor.
ASP.NET Web Frameworks and Tools 2017   5.2.51007.0
For additional information, visit https://www.asp.net/
Azure App Service Tools v3.0.0   15.0.31106.0
Azure App Service Tools v3.0.0
Azure Data Lake Node   1.0
This package contains the Data Lake integration nodes for Server Explorer.
Azure Data Lake Tools for Visual Studio   2.2.9000.1
Microsoft Azure Data Lake Tools for Visual Studio
Azure Data Lake Tools for Visual Studio   2.2.9000.1
Microsoft Azure Data Lake Tools for Visual Studio
Common Azure Tools   1.10
Provides common services for use by Azure Mobile Services and Microsoft Azure Tools.
Cookiecutter   3.0.17320.1
Provides tools for finding, instantiating and customizing templates in cookiecutter format.
JavaScript Language Service   2.0
JavaScript Language Service
JavaScript Project System   2.0
JavaScript Project System
JavaScript UWP Project System   2.0
JavaScript UWP Project System
Merq   1.1.17-rc (cba4571)
Command Bus, Event Stream and Async Manager for Visual Studio extensions.
Microsoft Azure HDInsight Azure Node   2.2.9000.1
HDInsight Node under Azure Node
Microsoft Azure Hive Query Language Service   2.2.9000.1
Language service for Hive query
Microsoft Azure Stream Analytics Language Service   2.2.9000.1
Language service for Azure Stream Analytics
Microsoft Azure Stream Analytics Node   1.0
Azure Stream Analytics Node under Azure Node
Microsoft Azure Tools   2.9
Microsoft Azure Tools for Microsoft Visual Studio 2017 - v2.9.51120.3
Microsoft Continuous Delivery Tools for Visual Studio   0.3
Simplifying the configuration of continuous build integration and continuous build delivery from within the Visual Studio IDE.
Microsoft JVM Debugger   1.0
Provides support for connecting the Visual Studio debugger to JDWP compatible Java Virtual Machines
Microsoft MI-Based Debugger   1.0
Provides support for connecting Visual Studio to MI compatible debuggers
Microsoft Visual C++ Wizards   1.0
Microsoft Visual C++ Wizards
Microsoft Visual Studio Tools for Containers   1.1
Develop, run, validate your ASP.NET Core applications in the target environment. F5 your application directly into a container with debugging, or CTRL + F5 to edit & refresh your app without having to rebuild the container.
Microsoft Visual Studio VC Package   1.0
Microsoft Visual Studio VC Package
Mono Debugging for Visual Studio   4.8.4-pre (3fe64e3)
Support for debugging Mono processes with Visual Studio.
Node.js Tools   1.4.11025.7
Adds support for developing and debugging Node.js apps in Visual Studio
NuGet Package Manager   4.5.0
NuGet Package Manager in Visual Studio. For more information about NuGet, visit http://docs.nuget.org/.
Python   3.0.17320.1
Provides IntelliSense, projects, templates, debugging, interactive windows, and other support for Python developers.
Python - Django support   3.0.17320.1
Provides templates and integration for the Django web framework.
Python - IronPython support   3.0.17320.1
Provides templates and integration for IronPython-based projects.
Python - Profiling support   3.0.17320.1
Profiling support for Python projects.
R Tools for Visual Studio   1.3.31108.1213
Provides project system, R Interactive window, plotting, and more for the R programming language.
SQL Server Data Tools   15.1.61710.120
Microsoft SQL Server Data Tools
ToolWindowHostedEditor   1.0
Hosting json editor into a tool window
TypeScript Tools   15.5.11025.1
TypeScript Tools for Microsoft Visual Studio
Visual Studio Code Debug Adapter Host Package   1.0
Interop layer for hosting Visual Studio Code debug adapters in Visual Studio
Visual Studio Tools for Apache Cordova   15.123.6829.2
Visual Studio Tools for Apache Cordova
Visual Studio Tools for Universal Windows Apps   15.0.27130.2036
The Visual Studio Tools for Universal Windows apps allow you to build a single universal app experience that can reach every device running Windows 10: phone, tablet, PC, and more. It includes the Microsoft Windows 10 Software Development Kit.
VisualStudio.Mac   1.0
Mac Extension for Visual Studio
Xamarin   4.8.0.760 (fc93f3f5b)
Visual Studio extension to enable development for Xamarin.iOS and Xamarin.Android.
Xamarin Designer   4.8.188 (c5813fa34)
Visual Studio extension to enable Xamarin Designer tools in Visual Studio.
Xamarin.Android SDK   8.1.5.0 (HEAD/75f8c6838)
Xamarin.Android Reference Assemblies and MSBuild support.
Xamarin.iOS and Xamarin.Mac SDK   11.6.1.4 (db807ec)
Xamarin.iOS and Xamarin.Mac Reference Assemblies and MSBuild support.

Your Visual Studio 2017 Community may have all items missing

I tried to create a UWP project in Visual Studio 2017 and I got this surprise.

empty_0
I got this message when I tried to open an existing Project.

empty_1
Looks like Microsoft forced an update. Anyway, I needed one and clicked Update.

The Visual Studio Installer came up and this takes some solid time to complete.

empty_2

Well after the installation (version 15.5.7) most of the components I had earlier did comeback, except for one.
This did not allow me to create a UWP project as there were assemblies missing.

I had to get them back as in the following working with Manage NuGet Packages.

Finally the references are complete.

AfterRestoringReference

The Microsoft.NETCore.UniversalWindowsPlatform was the missing reference.

On displaying a number formatted for currency in a UWP app

The entry you make in a text box control is of data type text and sometimes you need to format it as a currency with thousands separator while displaying.

Also, if the number of decimal places you enter is more than 2, you need to round it up to 2 in the output.

How do you code this in a UWP app?

Create a UWP page starting from a Blank UWP project with three controls as shown.

There are two text boxes and a button. You enter a number in the top textbox (txt1) and click the button, the formatted currency will be displayed in the bottom textbox(txt2).

The app displays as shown.

Now provide for a click event for the button and insert code as shown:

Convert_1

The string entered in txt1 is converted to data-type double, it is then 'rounded'. The rounded number is then formatted to show the currency symbol and the 1000's place delimiter you find, for example in Excel formatting.

Here is the app after entering a number and clicking the button.

Using basic analytics using Power BI - Part 1

In an earlier post getting data from WEB  was described which used Indian demographics data.

In this post some analytics information will be added to the display using built-in analytic capabilities of Power BI.

Here is the WEB retrieved data from the link mentioned earlier.

Analytics_5_7_2018_0.png

Here is a visual of male, female and total populations from the Southern states Andhra Pradesh, Tamil Nadu, Karnataka and Kerala.

Analytics_5_7_2018_1.png

If you are not on a 'visual', you will not see the icon for the Analytics in the Visualizations section as shown. I am showing a visual, but I am not on it.

Here we have Page Level filter as shown

Analytics_5_7_2018_2.png

The total population is less than 10M in each state as you can see by adding data.

Once you are on a visual you can access the 'Analytics' as shown here.

Analytics_5_7_2018_4

Now click on 'Analytics' and you can add a number of analytics markers as shown here.

Analytics_5_7_2018_5

Now you will add a line showing average (Blue line)  of population of Females as shown here.

Here are few more settings for 'average line' that was not captured in the previous image. A zoom control would have captured the whole page of interest.

Similarly you add the Min Line, Max Line, Media Line and Percentile lines to the data.