SQL Server – Page 7 – SQLXpertise

CONCAT_WS() – Concatenate with Separator–New String Function in SQL Server 2017

SQL Server 2017 has a new String function which supports concatenating multiple values with separator, ex. Comma (,) or Pipe (|)

This function will come handy, if you want to generate a CSV file quickly

Syntax for the function is as follows

CONCAT_WS ( separator, argument1, argument1 [, argumentN]… )

Example Usage

CREATE TABLE [dbo].[Test](
[intPK] [int] IDENTITY(1,1) NOT NULL,
[strData] [varchar](50) NOT NULL,
PRIMARY KEY CLUSTERED
(
[intPK] ASC
)
)
GO

INSERT INTO dbo.test (strData) VALUES (‘Test1’)
INSERT INTO dbo.test (strData) VALUES (‘Test2’)
INSERT INTO dbo.test (strData) VALUES (‘Test3’)
INSERT INTO dbo.test (strData) VALUES (‘Test4’)
INSERT INTO dbo.test (strData) VALUES (‘Test5’)

SELECT CONCAT_WS(‘|’, intPK, strData) AS Result FROM dbo.Test

In above example we are concatenating 2 fields using pipe as separator

Output

NULL Handling

If any of the data fields has NULL, then we need to use ISNULL() or COALESCE() to handle this, else, the structure of the output will change

ALTER TABLE dbo.Test ADD strNullData VARCHAR(50) NULL

UPDATE dbo.Test SET strNullData=’A’ WHERE intPK=1
UPDATE dbo.Test SET strNullData=’B’ WHERE intPK=3
UPDATE dbo.Test SET strNullData=’C’ WHERE intPK=5

SELECT CONCAT_WS(‘|’, intPK, strNullData, strData) Result FROM dbo.Test

In the below output, rows 1,3 and 5 has 3 fields and rows 2 and 4 has only 2 fields

Query with ISNULL() Handling

SELECT CONCAT_WS(‘|’, intPK, ISNULL(strNullData, ”), strData) Result FROM dbo.Test

Now the below output will have consistent list of fields

Hope you find this helpful !!!

Upgrading SQL Server 2017 on Ubuntu Linux to RTM in 4 steps

SQL Server 2017 RTM is released and available on Windows, Linux and Docker, in this article, we will see the steps for upgrading SQL Server 2017 CTM Release on Ubuntu to RTM Version

Step 1: Connect to Ubuntu Server using your favorite Shell, I use Bash Shell on Windows, since its built-in Windows 10 now

Step 2: Remove old repository

sudo add-apt-repository "$(curl https://packages.microsoft.com/config/ubuntu/16.04/mssql-server-2017-gdr.list)"

Step 3: Add new repository

sudo add-apt-repository "$(curl https://packages.microsoft.com/config/ubuntu/16.04/mssql-server-2017.list)"

Step 4: Upgrade SQL Server

sudo apt-get update

sudo apt-get install mssql-server

If you are using Availability groups in Linux, Please upgrade the primary machine first and then secondary replica

Have fun with SQL Server 2017

ApexSQL Complete–Advanced SQL Server Intellisense for Free !!!

SQL Server 2012 has built-in Intellisense, ApexSQL Complete takes Intellisense to next level.

It available as a Free Tool and its one of the best Free Tools available for SQL Server in market.

Its available for download in this below link

http://www.apexsql.com/sql_tools_complete.aspx

The download also includes all the tools from ApexSQL Developer Studio (15 Developer Tools, 3 of them are Freely available)

The installation process is quite simple

You can by default install all the products and try them for Free

or pick ApexSQL Complete 2012

Once the install is complete, Please launch SQL Server 2012 Management Studio,

ApexSQL Complete menu has the following options

Please click on “Enable Auto-complete” to use ApexSQL Complete instead of built-in Intellisense

You can open a new Query window and start typing the query and you can activate the Intellisense by pressing Ctrl + Space

You can choose the columns which you want to be included in Query and If you noticed, It automatically add Alias to the Table name automatically as well, which will be very helpful for complex queries and using in joins.

Also when you select a table, you can see the CREATE Script for the table as well and you have option to “Copy” the script as well

ApexSQL Complete also check your query as you type and shows them in “Error List” window

ApexSQL Complete Error List window works similar to Visual Studio Error List window, double clicking on the Error takes you to the exact Line and Column so that you can easily fix it

ApexSQL Code Structure

This is one of the most powerful feature I liked in ApexSQL which is not available in any of the other competitive production

It parses your SQL code and display the Code Structure as Tree, so that you can easily navigate the code and click and move to the preferred statement easily.

ApexSQL Complete Options

General Options

The following are the different options available under General section, but I would like to highlight the “Decrypt Encrypted Objects”

The below view “dbo.vwAddress_Enc” is an encrypted view

Since I have enabled the Decrypt option, ApexSQL Complete is able to show the definition of the Encrypted View, so that I can copy the script

Insert Options

Hints Options

If you don’t want to see System tables or views in the Intellisense list, you can deselect it using “System Tables” option, similarly you can customize the other hints too

Aliases Options

ApexSQL Complete by default create aliases for tables or views selected from Intellisense window, but if you want use your own custom defined Alias, you can define them here

I have created an alias named “Emp” for the Employee table, when I write the query, now the ApexSQL Complete Intellisense window displays the Alias on top

When you select the Alias, it automatically fills the referenced object name

Schema Cache Options

Here you can see the currently cached Database and its object counts, you can select them and right click and Delete or Refresh them

Snippet Manager

Using Snippet Manager, You can modify the existing Snippet and customize them or you can add new Snippets as well

Also after doing the customization, You can Export the options and share with your colleagues, and they can import and use the Snippets you created or use the same Aliases you have created.

ApexSQL Developer Studio also has the following products available for free as well

1. ApexSQL Search

2. ApexSQL Refactor

I hope you find this information useful !!! Have fun coding using ApexSQL Complete !!!

How to backup your SQL Database and Upload to FTP or Cloud Automatically ?

Its always a better idea to Keep Offsite backup of databases, “SQL Backup and FTP” allows you to do that freely

SQL Backup and FTP is available for download for free at http://sqlbackupandftp.com

It supports direct backup to

1. FTP

2. Local or Network Folder

3. Drop Box

4. Box

5. Amazon S3

6. SkyDrive

7. Google Drive

Professional Version also supports SFTP and FTPS and AES Encryption of Backup files

Please follow the below steps for installation

The installation has completed successfully, Let us launch SQL Backup and FTP and create a Backup

Select the databases you want to Backup

Click on “Add backup destination” to specify the backup destination

For our demo, Let us select Drop box option

Click on “Authorize…” and Login to your Drop Box account

Click “Allow” to approve the Drop Box access

Once Drop Box access is authorized, Please click on “Finish” to proceed. You can configure automatic cleanup of backups as well.

Specify the Folder to which Backup has to be done

Click “Test” to make sure it works

Once Test is completed, Click “Ok” to proceed

Let us now connect to SQL Server and do a backup, By default, if you have a SQL Server default instance running, it will automatically connect and display the databases, Please select the database which you want to backup

If you want to enable Email notifications, Please select and specify the email address

Click on “Email Settings” icon to Test and configure, You can you use your own SMTP Server, if you prefer

Click “Send Test e-mail” to make sure the notifications are working and click “Ok” to proceed

Scheduling the Backups

If you want the backup to run automatically at a specific time, Please enable the “Schedule this job” option

Please click on “Schedule Settings” to customize the schedule based on your requirement, once finished, Please click on “Save & Close” to proceed

Please click “Run Now” to test the job manually

Once test is completed, Now click on “Save” button on the tool bar to save the job.

Hope this helps !!!

SQL Server Compression Estimator

SQL Server Compression Estimator is a free tool available through CodePlex (http://ssce.codeplex.com/) which helps you in estimating the best compression for your SQL server objects. It estimates both Row and Page compression and also provides option to generate script for altering your SQL Server objects to implement compression.

My great thanks to MajikbyboB for providing this tool to the community for free.

Let us see how to install and use this tool

Download and extract the setup from the above site and extract the zip file and run setup.exe

Click “Next” to proceed

Select the installation folder and then click “Next” to proceed

Click “Next” to confirm the installation and proceed

Click “Close” to finish the installation

Now we have successfully installed the tool, Let us see how to run and estimate compression on databases

Run “Compression Estimator” and specify your Server name and select authentication method and provide required credentials and click “Connect”

Click on Database combo and select your database, and then specify the savings threshold.

Savings threshold identify what objects to be reports based on the saving in size due to compression

If you want to include index maintenance ratio, select the “Include index maintenance ratio” check box.

Click “Process” to proceed

Once the processing is complete, you can see the list of objects which can be compressed with the following details

1. Type of Compression (Page or Row)

2. Current Size

3. Compressed Size

4. $ of Savings

You can save the estimation results as CSV file by using “Save Results” option for your reference.

You can select the required changes to be made and then click on “Create Script” to generate the SQL Script to make compression changes

Sample Script generated

— This compression script was created by Compression Estimator
 
— By applying PAGE compression setting, you should obtain 
— approximately 53.33% compression
ALTER TABLE [Production].[Product]
REBUILD 
WITH (DATA_COMPRESSION = PAGE);
 
— By applying PAGE compression setting, you should obtain 
— approximately 53.70% compression
ALTER INDEX IX_Address_AddressLine1_AddressLine2_City_StateProvinceID_PostalCode ON [Person].[Address]
REBUILD WITH (DATA_COMPRESSION = PAGE);
 
— By applying PAGE compression setting, you should obtain 
— approximately 63.64% compression
ALTER TABLE [Production].[BillOfMaterials]
REBUILD 
WITH (DATA_COMPRESSION = PAGE);
 
— By applying PAGE compression setting, you should obtain 
— approximately 59.09% compression
ALTER TABLE [Purchasing].[PurchaseOrderHeader]
REBUILD 
WITH (DATA_COMPRESSION = PAGE);
 
— By applying PAGE compression setting, you should obtain 
— approximately 56.25% compression
ALTER INDEX AK_CurrencyRate_CurrencyRateDate_FromCurrencyCode_ToCurrencyCode ON [Sales].[CurrencyRate]
REBUILD WITH (DATA_COMPRESSION = PAGE);
 
— By applying PAGE compression setting, you should obtain 
— approximately 60.00% compression
ALTER TABLE [Production].[ProductListPriceHistory]
REBUILD 
WITH (DATA_COMPRESSION = PAGE);
 
— By applying PAGE compression setting, you should obtain 
— approximately 60.00% compression
ALTER INDEX IX_ProductReview_ProductID_Name ON [Production].[ProductReview]
REBUILD WITH (DATA_COMPRESSION = PAGE);
 
— By applying PAGE compression setting, you should obtain 
— approximately 60.00% compression
ALTER TABLE [Production].[ProductCostHistory]
REBUILD 
WITH (DATA_COMPRESSION = PAGE);
 
— By applying PAGE compression setting, you should obtain 
— approximately 60.00% compression
ALTER INDEX AK_Employee_LoginID ON [HumanResources].[Employee]
REBUILD WITH (DATA_COMPRESSION = PAGE);
 
— By applying ROW compression setting, you should obtain 
— approximately 60.00% compression
ALTER INDEX IX_ProductReview_ProductID_Name ON [Production].[ProductReview]
REBUILD WITH (DATA_COMPRESSION = ROW);
 
— By applying PAGE compression setting, you should obtain 
— approximately 69.62% compression
ALTER TABLE [Production].[WorkOrder]
REBUILD 
WITH (DATA_COMPRESSION = PAGE);
 
— By applying PAGE compression setting, you should obtain 
— approximately 67.25% compression
ALTER TABLE [Production].[TransactionHistoryArchive]
REBUILD 
WITH (DATA_COMPRESSION = PAGE);
 
— By applying PAGE compression setting, you should obtain 
— approximately 57.58% compression
ALTER TABLE [Purchasing].[PurchaseOrderDetail]
REBUILD 
WITH (DATA_COMPRESSION = PAGE);
 
— By applying PAGE compression setting, you should obtain 
— approximately 73.25% compression
ALTER TABLE [Production].[WorkOrderRouting]
REBUILD 
WITH (DATA_COMPRESSION = PAGE);
 
— By applying PAGE compression setting, you should obtain 
— approximately 64.86% compression
ALTER TABLE [Production].[TransactionHistory]
REBUILD 
WITH (DATA_COMPRESSION = PAGE);
 
— By applying PAGE compression setting, you should obtain 
— approximately 65.31% compression
ALTER TABLE [Sales].[CurrencyRate]
REBUILD 
WITH (DATA_COMPRESSION = PAGE);

I hope you all find this information useful. Please post your comments !!!

Share this:

Like this:

Share this:

Like this:

Share this:

Like this:

Share this:

Like this:

Share this:

Like this: