Archive for the ‘SQL Server Versions’ Category

Connect SQL Server on Linux with SSMS from a Windows machine

January 6, 2017 Leave a comment

In [previous post] we saw how to install & setup SQL Server on Linux. We used PuTTY to connect to the Linux Azure VM and query SQL Server there.

Here in this post we will try to connect to SQL Server on Linux via SSMS from a Windows machine.

So to connect remotely to SQL Server on an Azure VM, you have to configure an inbound rule on the network security group. The rule allows traffic on the port on which SQL Server listens (default is 1433).

–> Setup Inbound Rule:

1. You need to go to and login with your credentials. On the Dashboard you will see your Linux VM as shown below:


2. Click on the Linux VM widget, it will open up and list out the Settings, click on Network Interfaces, and select the available Network Interface from the list.


3. Now click on the Network Security Group:


4. On the Network Security Group page, select the Inbound security rules, and click on the “+” button create new for SQL Server:


5. On the Add inbound security rule page, provide a name to this rule, select “MS SQL” under Service drop-down, and click OK:


–> Connect using SSMS:

6. Now from any Remote machine or even you PC which has SSMS installed and have internet connection, try connecting the SQL Server Linux Instance:

– Server Name: provide the IP address of the Linux VM (you can get it by opening the “Linux VM widget” and clicking on “Connect” button at top)

– Authentication: should be SQL Server Authentication

– Login: “SA”

– Password: apply the same password that you set it up while Configuring SQL Server on Linux.


7. On successful authentication you will see the Object Explorer showing the SQL instance that’s running on a Linux machine. On a glance you can’t make out any difference b/w a Linux SQL instance or a Windows SQL instance.

Thus I executed the SELECT @@version statement which shows the SQL Server version and on which Operating system its running.

You can even see the Database SQLdbOnLinux that we created by connecting from the PuTTY client.


That’s it for now, will write more about SQL on Linux on coming updates from Microsoft !!!

Install and Configure SQL Server vNext on Linux Azure VM

January 4, 2017 2 comments


In my [previous blog] post we saw how to Create a new Linux VM on Azure. After creating it we connected it from a Windows workstation by using PuYYT tool and tested one Unix command.

Here in this post we will see how we can install SQL Server on Linux.

Microsoft is working on a new SQL Server vNext version that will work both on Windows as well as Linux. So with the recent [CTP 1.x] release we can see how does it look like on both the Operating Systems and get some hands on with it.

–> Get the Repository and Install SQL Server:

1. Import the public repository GPG keys:

curl | sudo apt-key add –

2. Register the Microsoft SQL Server Ubuntu repository:

curl | sudo tee /etc/apt/sources.list.d/mssql-server.list

3. Re-synchronize the Package index files from their sources, to get information of the newest versions of packages and their dependencies, and finally Install MSSQL-Server package:

sudo apt-get update
sudo apt-get install -y mssql-server

After Install it will ask you to run SQL Server setup by following message:

Please run /opt/mssql/bin/sqlservr-setup to complete the setup of Microsoft(R) SQL Server(R)

4. Now post install, we need to configure SQL Server and set the SA password, and SQL services.

sudo /opt/mssql/bin/sqlservr-setup

The Configuration gives following prompts in between:

Please enter a password for the system administrator (SA) account:
Please confirm the password for the system administrator (SA) account:

Setting system administrator (SA) account password…

Do you wish to start the SQL Server service now? [y/n]: y
Do you wish to enable SQL Server to start on boot? [y/n]: y

5. Post Configuration you can verify that the service is running:

systemctl status mssql-server


–> Install Tools: This will install the command-line tools (sqlcmd & BCP), Microsoft ODBC drivers, and their dependencies.

6. Re-synchronize the Package index files, and Install MSSQL-Tools:

sudo apt-get update
sudo apt-get install mssql-tools


–> Connect to SQL instance:

9. We will use sqlcmd to connect to SQL Server vNext on our Linux VM. Run the sqlcmd command with following params:
– S: Server Name
– U: User Name
– P: Password

sqlcmd -S localhost -U SA -P ”


–> Run SQL Queries:

10. Now after connecting to the SQL Linux instance, let’s test come queries:

We will check the Version of SQL Server and list out system Databases:

select @@version

select database_id, name, create_date from sys.databases

Output of version statement:
Microsoft SQL Server vNext (CTP1.1) – (X64)
Dec 10 2016 02:51:11
Copyright (C) 2016 Microsoft Corporation. All rights reserved.
on Linux (Ubuntu 16.04.1 LTS)

11. Let’s do more and Create a new Database, new Table and insert some sample records:


use SQLdbOnLinux
--Changed database context to 'SQLdbOnLinux'.
CREATE TABLE Test (i int, name varchar(25))
insert into Test values (1, 'Manoj')
insert into Test values (2, 'Saurabh')

--(1 rows affected)

--(1 rows affected)
select * from Test
--i           name
------------- -------------------------
--          1 Manoj
--          2 Saurabh

--(2 rows affected)

I think you would get amazed like me after using SQL Server on a Linux environment.

In my [next post] I’ll show how we can connect this SQL Linux instance from a Windows workstation via SSMS !!!

New built-in function TRANSLATE() in SQL Server vNext (2018)

December 29, 2016 1 comment

Microsoft looks very serious this time to move people from other databases to SQL Server. As with SQL Server 2016 & vNext (2018) you can see lot of Built-in function added, which were present in other databases from long back, will ease database development in SQL Server.

One of this function is TRANSLATE() function, which can be used like a REPLACE() function, and would avoid using REPLACE() function multiple times in a query.


TRANSLATE ( inputString, characters, translations)

Note: characters and translations params should have same length.

–> Consider this example I’ve taken from MSDN:

SELECT TRANSLATE('2*[3+4]/{7-2}', '[]{}', '()()');


Input Output
2*[3+4]/{7-2} 2*(3+4)/(7-2)


–> If you had to do same with REPLACE() function then you would end up writing multiple & nested REPLACE() function, like:

				REPLACE('2*[3+4]/{7-2}', '[', '('), 
			']', ')'), 
		'{', '('), 
	'}', ')');


After working with this new feature it reminds me of IIF vs CASE statement. The IIF() function also works as a shortcut of CASE statement and cuts lot of clutter and gives you clean code.

Hope you find this small utility very handy while developing complex queries, will post more scenarios if I came across going forward, thanks !!!

New built-in function TRIM() in SQL Server vNext (2018)

December 26, 2016 Leave a comment

If you are thinking the new TRIM() function in SQL Server vNext is just a combination of LTRIM() & RTRIM() functions, then you are wrong :). It’s more than that and we will check it today !

– LTRIM() function is used to truncate all leading blanks, or white-spaces from the left side of the string.

– RTRIM() function is used to truncate all trailing blanks, or white-spaces from the right side of the string.

–> Now, with teh new TRIM() function you can do both, but more than that.

Usage #1: TRIM() function will truncate all leading & trailing blanks from a String:

	TRIM ('  Manoj Pandey     ') as col1,
	LTRIM(RTRIM('  Manoj Pandey     ')) as col2


Usage #2: Plus it can be used to remove specific characters from both sides of a String, like below:

	TRIM ( 'm,y' FROM 'Manoj Pandey') as col1,
	TRIM ( 'ma,ey' FROM 'Manoj Pandey') as col2,
	TRIM ( 'm,a,e,y' FROM 'Manoj Pandey') as col3


Thus with the above query you can see that you can trim characters too, by providing leading & trailing characters, but should be in same sequence as your string is.

Also for Col2 & Col3 we have provided Trimming Characters in 2 different ways, but got the same output.

–> Note: I just mentioned above that the leading & trailing characters should be in same sequence. If you provide in different sequence like below you won’t get desired results.

SELECT 'Manoj Pandey' as st,
	TRIM ( 'a,n' FROM 'Manoj Pandey') as Col1,
	TRIM ( 'm,e' FROM 'Manoj Pandey') as Col2,
	TRIM ( 'm,o,y,e' FROM 'Manoj Pandey') as Col3


Like for Col3 you cannot get rid of middle characters (like ‘o’ and ‘n’) until and unless they become leading or trailing characters.

New built-in function STRING_AGG() with option “WITHIN GROUP” – SQL Server vNext (2018)

December 23, 2016 Leave a comment

In one of my [previous post] I discussed about a new function STRING_SPLIT() introduced in SQL Server 2016, which splits a Sentence or CSV String to multiple values or rows.

Now with the latest CTP 1.x version of SQL Server vNext (I’m calling it “SQL Server 2018”) a new function is introduced to just do its reverse, i.e. Concatenate string values or expressions separated by any character.

The STRING_AGG() aggregate function takes all expressions from rows and concatenates them into a single string in a single row.

It implicitly converts all expressions to String type and then concatenates with the separator defined.

–> In the example below I’ll populate a table with States and Cities in separate rows, and then try to Concatenate Cities belonging to same States:

USE tempdb

CREATE TABLE #tempCityState (
    [State] VARCHAR(5), 
    [Cities] VARCHAR(50)

INSERT INTO #tempCityState
SELECT 'AK', 'Wynne'
SELECT 'AK', 'Nashville'
SELECT 'CA', 'Hanford'
SELECT 'CA', 'Fremont'
SELECT 'CA', 'Los Anggeles'
SELECT 'CO', 'Denver'
SELECT 'CO', 'Aspen'
SELECT 'CO', 'Vail'
SELECT 'CO', 'Teluride'

SELECT * FROM #tempCityState


–> To comma separate values under a single row you just need to apply the STRING_AGG() function:

	STRING_AGG(Cities, ', ') as AllCities
FROM #tempCityState

-- Use "WITHIN GROUP (ORDER BY ...)" clause to concatenate them in an Order:
	STRING_AGG(Cities, ', ') WITHIN GROUP (ORDER BY Cities) as CitiesByStates
FROM #tempCityState


–> Now to Concatenate them by States, you just need to group them by the State, like any other aggregate function:

	STRING_AGG(Cities, ', ') as CitiesByStates
FROM #tempCityState

-- Use "WITHIN GROUP (ORDER BY ...)" clause to concatenate them in an Order:
	STRING_AGG(Cities, ', ') WITHIN GROUP (ORDER BY Cities) as CitiesByStates
FROM #tempCityState


You can check more about STRING_AGG() on MSDN.