Saturday, August 12, 2017

Hands-on learning in Honolulu: COM2037 - Introduction to Structured Query Language (SQL)

The next session will be starting October 12, 2017. Register early.

Explore funding opportunity here:

Sunday, August 6, 2017

Encrypting email password in SQL Server

There is a simple but not most secure way of encrypting a password in SQL Server. It uses the T-SQL function EncryptByPassPhrase().

This function takes two arguments: PassPhrase and ClearText. You provide both the PassPhrase and the ClearText(this is your email password that needs to be encrypted using this function). The PassPhrase generates a TripleDes algorithm with a 128 Key bit length.

ClearText can be a variable of the following types:
or nchar

PassPhrase can be of the following types:
or nchar

The following code uses the following variables:
PassPhrase -> "Happy birthday to you"
ClearText->   "$#MyPassword#$"
CREATE TABLE ENCRPTPSWD (EncryptedText varbinary(200))

declare @pswrd nvarchar(15)
set @pswrd = '$#MyPassword#$'--ClearText
values (EncryptByPassPhrase('Happy birthday to you', @pswrd))

The column ENCRPTPSWD contains the result of encrypting and is therefore of type varbinary. The phrase to de-encrypt is the phrase 'Happy birth day to you'.

Now if we access that column we see the encrypted value as shown.

SQL Server and two-digit year cut-off

Remember the millenium bug fiasco. It happenned in Year 2000.
This is what chronicled in Wikipedia:

"The Year 2000 problem is also known as the Y2K problem, the Millennium bug, the Y2K bug, or Y2K. Problems resulted because people, including programmers, reduced the four-digit year to two digits. This made the year 2000 indistinguishable from 1900"

Now we have a better interpretation for people using two digit years as far as SQL Server is concerned.

The default time span for two digit years in SQL Server is 1950-2049.

Two-digit year 49 is 2049, but two digit year 50 is back to 1950.

Probably you will start seeing it in your credit cards (may be?)

You may configure it in SQL Server 2017 using,

Exec Sp_Configure 'two digit year cutoff', 2038

In SQL Server 2012 Express it is enabled by default:

Read more here:

Friday, August 4, 2017

Adding Excel Application to SQL Server Management Studio

I am using SQL Server Management Studio Version 17.1. You may user another version.

Launch SSMS 17.1

Click Tools | External Tools...

External Tools window is displayed as shown.

Replace the Title with a name of your own, herein Excel Application. The menu contents also
changes to the one you provided, herein Excel Application.

You need to provide the location of the executable in the Command box. In the present case the
executable is here:

"C:\Program Files (x86)\Microsoft Office\root\Office16\EXCEL.EXE"

Now you External Tools window should appear as shown.

Click OK.

Now you will find 'Excel Application' as shown in the Tools menu.

Now when you click this menu item, Excel Application is launched.

Tuesday, August 1, 2017

Usage of math operator Modulo in SQL Server

x%y is defined as the remainder of the division x/y. You can use modulo to find the minutes and hours in so many minutes.

Let us say we have a cconversion exercise to convert 123490 minutes into minutes and hours.
By long hand we would do this:

123490/60 and find the remainder
We get 2058.1666666667 with 0.1666666667 hours converts to 10
That is 2058 hours and 10 minutes.

We can do the same using modulo as shown.


You can use a much shorter code as shown here: (note input data has changed to 1234901)

Monday, July 31, 2017

SQL Server 2012 (x86) and SQL Server 2016 Developer on the same x64 bit computer - Part 2

Earlier in Part 1 of this post we saw that the SQL Server Management Studio 17.1 easily allows you to work with SQL Server 2012 (x32) Express, the SQL Server Management Studio that installs with SQL Server 2012 Express Advanced has many GUI related problems. The problems were neither related to Display resolution nor to resizing of windows.

Laptop: Windows 10 Pro
SQL Server 2012: and SQL Server Express (x32) 2012

The following images require no explanation.

Difference between dbo and db_owner

This question pops up now and then.

dbo is a user and db_owner is a database role. They both are in the Security node in the Object Browser. The SQL Server Management Studio (SSMS) makes it abundantly clear.





It will be instructive to review their properties as shown here.

dbo - Properties


db_owner - Properties


Tuesday, July 25, 2017

Generate database object scripts with mulit-platform tool mssql-scripts

In your SSMS on your computer you can use the Generate Scripts drop-down
menu to create scripts for database objects as shown here for Northwind database on SQL Server 2016 SP1.

The above is for SQL Server on Windows Platform. However, mssql-scripts tool provide the same functionality as Generate Scripts wizard on SQL Servers on Linux and macOS. Of course mssql-scripts would work on Windows as well.

Using mssql-scripts based on Python you can generate T-SQL scripts for objects on SQL Servers, Azure SQL database and Azure SQL Data Warehouse. The generated script is saved to a .sql file or on Linux can be piped to standard Unix utilities (sed, awk and grep). The scripts can be checked into source control systems as well.

The source code for mssql-scripter is found here:

Sunday, July 23, 2017

String function SOUNDEX

Soundex is how a name of a person is coded as used in the census for locating a person. It is based on how a name sounds rather than how it is spelled.

The name of the person is converted into a 4 character code, the first character in the code is the first letter of the person's name capitalized. The next three characters (they are number) that follow the rule stated here.

Rule 1:
For letters B,F,P,V                           use the number 1
For letters C,G,,K,Q,S,X,Z               use the number 2
For letters D,T                                  use the number 3
For letter L                                        use the number 4
For letters M and N                         use the number 5
For letter R                                        use the number 6

Disregard the letters A, E, I, O, U, H, W, Y (unless they are the first character in the name)

If the person's name is johnson, the first character in the soundex code is J

As to the rest of the characters in the code, using the above rules we have for johnson the Soundex code J525

The Soundex for Johnson is reduced to JNSN since O and H are not counted and N=5 and S=2 and J is the first character.

By the same rule, the Soundex code for Iowa is I000
Get more info from here:

and here:


Testing a comparison operator in an SQL Query?

Let us say we want to know if a variable x is greater or less than another variable y. How do we write a query to test it?

One way to do this is as follows:

declare @x int
set @x=5
declare @y int
set @y=10
    IF @x < @y
 print 1
 print 0

Wednesday, July 19, 2017

Regarding ntext, nchar and nvarchar

These are basically used for textual information related data types. There is a strong recommendation not to use ntext as it is not going to be supported.

These are their definitions from Microsoft Documentation.

ntext (National Text):
Variable-length Unicode data with a maximum string length of 2^30 - 1 (1,073,741,823) bytes. Storage size, in bytes, is two times the string length that is entered

nchar [ ( n ) ]
Fixed-length Unicode string data. n defines the string length and must be a value from 1 through 4,000. The storage size is two times n bytes. When the collation code page uses double-byte characters, the storage size is still n bytes. Depending on the string, the storage size of n bytes can be less than the value specified for n. The ISO synonyms for nchar are national char and national character..
nvarchar [ ( n | max ) ]
Variable-length Unicode string data. n defines the string length and can be a value from 1 through 4,000. max indicates that the maximum storage size is 2^31-1 bytes (2 GB). The storage size, in bytes, is two times the actual length of data entered + 2 bytes. The ISO synonyms for nvarchar are national char varying and national character varying.

ntext is supposed to be discontinued, but in actuality it is still present as a choice in data types listing even in SQL Server 16.

Sunday, July 16, 2017

Restoring the Northwind database from its backup

A backup of Northwind database was obtained from the Codeplex site and was saved to one of the folders on a Dell computer with Windows 10 OS. The computer also has SQL Server Management Studio (v 17.1). You should be able to restore using the SQL Server Management Studio installed when you installed the SQL Server 2012 Database engine.

Follow these steps to restore the Northwind database to an instance of SQL Server 2012 (x86) installed on the same computer.

Step 1. Start SQL Server Management Studio v17.1 (Run as administrator)

The SSMS is version 17.1 and Hodentek9\PCATT is a SQL Server 2012 Express

Step 2. Right click the Databases node highlighted in the PCATT isntnace as shown.


Step 3: Click Restore Database...

Restore Database window is displayed as shown.


Step 4: The Default Source is Database and it is greyed out as shown. Chnage it to Device. The Restore Database gets changed as shown.


Step 5: Click the ellipsis button along 'Device' in the above image.

Select backup devices window shows on top of Restore Database window as shown.


Step 6: Click Add button in Select backup devices window.
Locate Backup File window gets displayed as shown.


Usually the 'backup files with extension .bak' are found in the following directory in the case of x32 bit SQL Server.
C:\Program Files (x86)\Microsoft SQL Server\MSSQL11.PCATT\MSSQL\Backup

However, for this exercise it is stored in a different location.

Step 7: Now browse to that location and highlight the Northwind.bak (A backup file which came from a Microsoft site) as shown.


Step 8: Click OK. The file path is entered in the Select backup devices window as shown.


Step 9: Click OK
You are returned to the Restore Database - Northwind as shown.


Step 10: Click OK in the above.

Microsoft SQL Server Managment Studio message reports that the database
'Northwind' restored successfully.


Step 11: Click OK to the message. Verify that Northwind database is in the SQL Server 2012 instance Hodentek9\PCATT



Saturday, July 15, 2017

Microsoft SQL Server Management Studio 17.1 is generally available

SSMS Version 17.1 became available in June 2017 and you may want to downladd and install. This version will work with all editions of SQL Server so far (2018 to 2017). However some of the older applications written using older versions may still need older versions of SQL Server Management Studio.

Download the latest version of SQL Server Management Studio (version 17.1) here:

If you had had SSMS version 17.0, you may get a notification as to availability of version 17.1 which should facilitate installing the new version.

You need to restart after installing.

These are the enhancements in this version:

  • Profiler: Help > About now displays release version number (e.g 17.1)
  • Analysis Service users can refresh credentials for their datasources for 1200 TM models and above from the context menu on the datasource
  • Built-in SSIS reports now show logs from SSIS scale-out execution in CTP 2.1
  • SSIS scale-out management application
             View basic information about scale-out master.
             Easily add a Worker to the scale-out deployment.
             View all the scale-out workers and basic information about them, and can also enable or disable them easily.

This version will also have toast notifications when new updates are available. It also has a new stand alone web installer.

Friday, July 14, 2017

New security feature in SQL Server 2016: Dynamic Data Masking

Dynamic Data Masking which is available in SQL Server 2016 allows you provide another level of security to your data, by masking data that you do not want unauthorized (by policies) users to peek into. Data in the database itself is unchanged. SQL Server 2016 has other security features besides dynamic data masking.

Data leaks and hacking has become too common place. SQL Server 2016 provides yet another way to protect data by this new feature, Dynamic Data Masking (DDM).  Developers and administrator should (must) use this feature to mask data from intentional prying eyes.

DDM is a nice feature that you should implement if are dealing with sensitive information (Credit card numbers, Social Security Numbers, etc).

Here is an image of credit card numbers being masked (perhaps not by DDM in this view)

Credit card masking imaged source:

Read here about masking using JavaScript:

Do you need special permission to create a table with a dynamic data mask?

No, you do not. Of course you need standard permissions like Create table, Alter on schema permissions.

The Alter Any Mask permission and Alter permission on  a Table are needed, though.

Read more about Dynamic Data Masking here:

Friday, July 7, 2017

SQL Server 2012 Express(x86) is not registered in the Local Servers Groups by default

Although you can connect to SQL Server 2012 Express (x32bit) from SQL Server Management Studio (Version 14.0.17099.0), the Express Server does not belong to the Local Servers Groups by default as shown.

Thursday, July 6, 2017

SQL Server 2012 (x86) and SQL Server 2016 Developer on the same x64 bit computer - Part 1

The short answer is YES.

A x32 bit SQL Server Express Advanced and a x64 bit SQL Server 2016 SP1 are both installed on a Dell Inspiron laptop.

Here are the responses to the command:

SELECT @@version in a query launched by both of these versions:

(No column name)
Microsoft SQL Server 2012 - 11.0.2100.60 (Intel X86)   Feb 10 2012 19:13:17   Copyright (c) Microsoft Corporation  Express Edition on Windows NT 6.2 (Build 9200: ) (WOW64)

(No column name)
Microsoft SQL Server 2016 (SP1-GDR) (KB3210089) - 13.0.4202.2 (X64)   Dec 13 2016 05:22:44   Copyright (c) Microsoft Corporation  Developer Edition (64-bit) on Windows 10 Pro 6.3 (Build 15063: )

They can co-exist as shown:

Wednesday, July 5, 2017

Get July version of Microsoft Power BI Desktop

The July 5 version of Microsoft Power BI Desktop can be downloaded from here:

The details of this version  are here

Summary of new/improvements:
    New table & matrix visuals are now generally available
    Renaming fields in visuals
    Custom visuals store integration
    Relative date filters
    Responsive layout for visuals (preview)
    New waterfall chart option - breakdown
    Custom visual updates
        Drilldown Choropleth
        Drilldown Cartogram
        Drilldown Player
        Certified custom visuals

Analytics & modeling
    Quick measures from the community
        Star rating
        Concatenated list of values
    Bidirectional cross filtering for DirectQuery is now generally available

Data connectivity
    Snowflake connector general availability

Query editing
    Add Column from Examples enhancements

Watch the video for new feature details:

Thursday, June 29, 2017

Chart formatting options using Power BI

Customers are used to eye candy as most of social media strive to provide. While working with data it is not just eye-candy effects that are the driving force for visualization but controls available to bring out the features of data like its value representation; what it represents; relative magnitiudes; values of each piece of data, etc. Power BI can do a lot of this with simple choices. It is pretty find grained.

Take for example this mundane bar chart of some values in a chart of two axes. It shows a 'bar graph' and the default formatting if any are barely visible.


The formatting icon is where you click and start.


Right now only X-axis, Y-axis and Title are turned all on but you can hardly see any details. Using the drop-down you can control the Data colors; Data labels, background, border and lock aspect (locking the aspect ratio of the chart).

Presently the default color of all the six X-axis values are 'Green'.


However you can individually color them if there is a need. For this you need to turn on 'Show all'.


This image shows each data with a different color. You cal also go to default if you do not like. These changes in formatting renders the visualization as shown which is immediate.


You can also change the title which is barely visible clicking on 'Title' .


These changes in formatting renders the visualization as shown which is immediate.


You can turn on the Data lables turning 'Off' to 'On' by pulling the slider to right.
You will notice that the letters are too small to read (see above image).


You can work all the controls under Data Labels and make it look better as shown. If you click X-Axis or Y-axis then you can modify the formatting as swell.


If you have a saved image or logo or any background image you can add as shown and set its transparency as shown.

Similarly you can set the backgorund color and its tranpareancy as well.


In the 'General' section you can set the settings shown.