MS SQL Server

ByHariharan Rajendran

Generate SELECT Statement

This article explains that how to generate SELECT statement with all the table columns.

In the Production environment, most of the tables will have more than 100 columns. If those tables are used in a report with all the columns then it will be difficult to type all the columns.

To ease the process, we can generate the SELECT statement and can use that for any purpose.

Use the below code and pass the table which you want to use in the SELECT statement.

In this example, I used FactInternetSales table.

—–Code——-

Declare @Column varchar(500), @tableName varchar(25)

SET @tableName =’FactInternetSales’

SET @Column = ”

select @Column = @Column + Column_Name + ‘, ‘ from [AdventureWorksDW2014].INFORMATION_SCHEMA.COLUMNS

where table_name=@tableName

select ‘SELECT ‘ + SUBSTRING(@Column, 0, LEN(@Column)) + ‘ From ‘+@tableName as SELECTScript

—–Code———-

SQL1

Copy and paste the result and execute the script.

SQL2

ByHariharan Rajendran

Avoid Using Dataset Filters in SSRS

If you are Microsoft SQL Server Report Developer, then you have a chance to notice the dataset properties as it is the place where we supply the data subsets to the report.

Let us understand the filters in SSRS first and analyse why dataset filters should be avoided.

We have three different main places to filter the data in SSRS

  1. T-SQL Script
  2. Dataset Filters
  3. Run-time Filter using Parameters

Filter in T-SQL Script

When we understand the business requirements, need to categories what are the filters static and dynamic.

Always use the static filters in T-SQL script. Dynamic filters can be done through parameters but still need to add the filter in T-SQL Script with parameters.

For example,

If you want to filter country values as the US on your country table.

SELECT State, City, Country FROM dbo.COUNTRY

WHERE Country=’US’

Note: In this case, we are restricting data in database server itself.

Dataset Filters

You can filter in SSRS report dataset.

Go to Dataset -> Filters

RD

Note: In this case, we are taking all the data to report server and filtering the data in the report.

Run-time filter using Parameter

It is a special kind of filter because this will be the requirement from business in most of the cases.

First 2 filters won’t be visible to end users but this filter will be visible and user needs to filter the value while running the report.

This can be achieved by adding the parameter in T-SQL script,

SELECT State, City, Country FROM dbo.COUNTRY

WHERE Country=@Country

RD2

Why should we avoid dataset filters?

The main reason is to improve the performance of the report.

In case 1 (Filter in  T-SQL Script), we are filtering the data in database layer itself. It means we are taking only required data to the reporting layer and showing the result. The performance of the report will be good as it is processing only fewer records.

In case 2 (Dataset filter), we are taking all the records from table database and filtering in the report server. It means report server has to make process and filter the data and show the result. We are giving burden to report server so it will affect the performance.

 

ByHariharan Rajendran

T-SQL Basics – Day 1

If you are reading this article then you are looking an very easy way to understand the T-SQL Scripts.

Let us learn T-SQL with a scenario, consider you have a sheet with some employee information. Your boss asking questions from that sheet and you need to answer him. In simple, your task is “Read from Sheet”.

Convert the general words into technical terms,

Sheet – Table (Employee) in a Database

Questions – T-SQL Statement

Answer – Output of T-SQL

Read – Select

Read from Sheet – Select from Table

If you are familiar with above terms then you can easily learn T-SQL Statements.

Let’s start,

Boss: What are the information available of an employee?

You: Read all the field name from Sheet

Tab1

T-SQL: SELECT * FROM Employee

Tab17

Note: “*” denotes all the fields from a table.

Boss: Do we have employee Name, Age & Country?

You: Yes

Tab2

T-SQL: SELECT Name, Age, Country FROM Employee

Tab18

Boss: What is the age of Richard? Or what is the country name of Richard?

You: 42 or UK

Tab3

T-SQL: SELECT * FROM Employee Where Name=’Richard’

Tab13

Note: Need to use WHERE keyword when we say specific data.

Boss: Who are all crossed age 40?

You: Read from sheet

Tab4

T-SQL: SELECT * FROM Employee WHERE Age > 40

Tab14

Note: Need to use WHERE keyword when we say specific data.

Boss: Whose names start with letter K?

You: Read from sheet

Tab5

T-SQL: SELECT * FROM Employee WHERE Name LIKE ‘K_%’

Tab15

Note: Need to use WHERE keyword when we say specific data. Use LIKE keyword when you want to play with the letters or pattern in a value. There are few other patterns available for LIKE which will discuss in the next article.

Boss: Convert the values of a Marital Status column into detailed values.

You: Add a new column

Tab6

T-SQL: SELECT *, CASE WHEN [Marital Status] =’M’ THEN ‘Married’ ELSE ‘UnMarried’ End MStatus FROM Employee  

Tab16

Note: Use “CASE WHEN XXXX THEN XXXXX ELSE XXXXX END” Syntax when you want derive something. We can discuss the detailed version above syntax in next article.

 

ByHariharan Rajendran

Limitations of Custom Reports in SQL Server Management Studio

I have discussed Custom Reports in my earlier post, this post highlights the limitation of custom reports in SQL Server Management studio.

Microsoft provided few custom reports for R services. When I use those custom reports, SQL Server Management Studio has stopped working for all the reports.

SSMS_Stopped

Then I started to check the reports in visual studio. When I check the reports, it used some of the expressions which SQL Server Management not support.

Limitations,

  1. The dataset should be embedded in the report.
  2. Expressions – Regular Expression like sum, count will work but report specific expressions will not be supported, it could be an expression to open drill down reports and etc.
  3. Parameters – If the report has a parameter that should have a default value.

Download the modified version of R Services Custom Reports.

Download

ByHariharan Rajendran

SQL Server vNext vs SQL Server – Installation Features

Recently, Microsoft released SQL Server vNext which is a platform that gives us choices of development languages, data-types for on-premises and in the cloud.

It also opens up a channel to bring the power of SQL Server in Linux.

There are many features available in SQL Server vNext, check out Whats’s New in SQL Server vNext.

This post gives you the small comparison on SQL Server and SQL Server vNext in terms of installation.

Screenshot from SQL Server 2016,

SQLServerFeatures

Screenshot from SQL Server vNext,

vNext

As you can see, there is a new update on SQL Server Integration Services.

Integration Services Scale Out gives performance booster for package execution by distributing executions to multiple machines.

New Service account to support SSIS Scale Out Master and Worker

vNext_ServiceAccounts

Scale Out Master Configuration page,

vNext_ScaleOutMaster

Scale Out Worker Configuration page,

vNext_ScaleOutWorker

Start play with latest version of SQL Server vNext.

ByHariharan Rajendran

ODBC Escape Sequences – Date, Time & Timestamp

To convert the data type of the column in SQL Server, we use to follow below options.

  1. CAST
  2. CONVERT

This post explains “ODBC Escape Sequence” to convert the data type of the column or expression.

It also explains that how we can use the ODBC escape Sequence to get same result as CAST and CONVERT.

The syntax of the ODBC escape Sequences

Date – {d ‘Value’}  –Value format is yyy-mm-dd

Time – {t ‘Value’} – Value Format is  hh:mm:ss

Timestamp – {ts ‘Value’} – Value Format is yyyy-mm-dd hh:mm:ss[.f…]

–Create Table

CREATE TABLE Employee (

ID int not null identity (1,1),

Name NVARCHAR(25),

DateofJoin DATETIME

)

GO

INSERT INTO Employee values(‘John’,’2016-10-26′);

INSERT INTO Employee values(‘Richard’,’2016-1-13′);

INSERT INTO Employee values(‘David’,’2016-05-02′);

INSERT INTO Employee values(‘Jacob’,’2016-07-20′);

GO

–CAST

SELECT * FROM Employee

WHERE DateofJoin >= CAST(‘2016-06-02’ AS DATE)

GO

–CONVERT

SELECT * FROM Employee

WHERE DateofJoin >= CONVERT(DATE,’2016-06-02′)

GO

–ODBC date escape sequence

SELECT * FROM Employee

WHERE DateofJoin >= {d’2016-06-02′}

GO

–ODBC time & timestamp escape sequence

SELECT {t’09:00:00’} as ODBCTime

 

ByHariharan Rajendran

Access Linux based SQL Server in SQL Server Management Studio (SSMS)

Check my post on installing SQL Server in Red Hat Linux.

As we try to access the server outside of the network, we ought to configure a setting in firewall.

All the below steps applicable for Azure Virtual Machine with Linux installed on it.

Follow the below steps

  1. Add the TCP 1433 port inbound in firewall (Linux)
  2. Configure the DNS in Azure Portal- This is specific for Azure Virtual Machine.
  3. Add TCP inbound in Portal

Add TCP inbound rule in Linux

Run the bellow command in PuTTY Tool. Check my post to know how to use PuTTY tool to access Linux machine via SSH.

sudo firewall-cmd –zone=public –add-port=1433/tcp –permanent

sudo firewall-cmd –reload

PuTTY

Configure DNS in Azure Portal

It can be easily configured in the portal, check how to configure DNS in Azure.

Add TCP inbound in Portal

Again, this should be configured in network Security group inbound rule.

NSG

Once the above steps are successfully done, then can access the SQL Server in local SQL Server Management Studio or any machine.

Pass the following details to connect in SSMS.

Server: XXXX.southeastasia.cloudapp.azure.com. Here XXX denotes the DNS name which you configured in Portal.

In Linux, when we install SQL Server it will ask for SA account setup so now we can use SA account to login.

SQL

 

 

 

 

 

 

 

Server

ByHariharan Rajendran

SQL Server in Red Hat Linux

This post explains you in detail (step by step) about how to install SQL Server and SQL CMD tools in Red Hat Enterprise Linux 7.2.

Azure Virtual Machine Template “SQL Server vNext on Red Hat Enterprise Linux 7.2” has been used for this demo.

Go to Azure Virtual Machine and click “Add”, it will open a window where you can choose the SQL Server vNext on Linux template.

Linux1

Check the information on the below page as it has the command to configure and start SQL Server.

Linux2

Configure the VM as usual, check the “Authentication type”. It has two options. I am going to choose Password for this demo.

Linux3

Fill the other details and create a virtual machine.

It will take some time to configure the Linux machine. Once the deployment is done, then you can access the machine.

Since this is Linux machine, Azure gave us a connection IP address. I am using “PuTTY” (command line tool) to access this machine.

Linux5

Download and install the PuTTY Software from here.

Once installed PuTTY, open and pass the IP address and click Open. You can save the host as new session and can load and open for next time.

Linux6

Login with the username and password that you defined while creating the virtual machine.

Enter the below command to install and configure SQL Server.

sudo /opt/mssql/bin/sqlservr-setup

It will ask password to proceed further and also need to confirm the license.

Linux9

Next step is to set up the SA admin password for SQL Server. Setup will be completed after this step.

Linux11

Check the status of the service.

systemctl status mssql-server

As we need to run the SQL scripts in command line, we need to install the SQL CMD tools. To install, you need to go to the root directory.

#Install SQL Cmd tools

sudo su

Run the below commands to get the file and exit from root.

curl https://packages.microsoft.com/config/rhel/7/prod.repo > /etc/yum.repos.d/msprod.repo

exit

Run the below command to install

sudo yum install mssql-tools

Once the tool is successfully installed. You can see the success message.

Linux12

Open command to start type the SQL commands.

Pass the SA credentials. We are going to access with SQL Server authentication mode.

sqlcmd -S localhost -U SA –P ‘SA Password’

Type the SQL Script and end with GO.

Linux14

lINUX13

Happy Learning.