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
T-SQL: SELECT * FROM Employee
Note: “*” denotes all the fields from a table.
Boss: Do we have employee Name, Age & Country?
You: Yes
T-SQL: SELECT Name, Age, Country FROM Employee
Boss: What is the age of Richard? Or what is the country name of Richard?
You: 42 or UK
T-SQL: SELECT * FROM Employee Where Name=’Richard’
Note: Need to use WHERE keyword when we say specific data.
Boss: Who are all crossed age 40?
You: Read from sheet
T-SQL: SELECT * FROM Employee WHERE Age > 40
Note: Need to use WHERE keyword when we say specific data.
Boss: Whose names start with letter K?
You: Read from sheet
T-SQL: SELECT * FROM Employee WHERE Name LIKE ‘K_%’
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
T-SQL: SELECT *, CASE WHEN [Marital Status] =’M’ THEN ‘Married’ ELSE ‘UnMarried’ End MStatus FROM Employee
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.
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.
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,
- The dataset should be embedded in the report.
- 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.
- Parameters – If the report has a parameter that should have a default value.
Download the modified version of R Services Custom Reports.
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,
Screenshot from SQL Server 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
Scale Out Master Configuration page,
Scale Out Worker Configuration page,
Start play with latest version of SQL Server vNext.
Cascading Parameter in SSRS – Different Approachs
I saw a query raised by a user in one of the SQL forum. He has explained his query with a scenario.
Scenario: Need to create a simple report with 3 parameters, those parameters are “FromDate”, “ToDate” and “Type”. Two parameters (“FromDate” & “ToDate”) should react when I change the Type parameter value.
The possible values in “Type” field is “Yes” and “No”. If the end user selects “Yes” then “FromDate” and “ToDate” should show one year back from today. i.e. 2016-01-10 to 2017-01-11
If the value is “No” the date should be one month back from today. i.e. 2016-12-10 to 2017-01-11.
By seeing the above requirement, I felt cascading parameter option would be the solution for above requirement but it will not give 100% result because once the value is entered or set on parameter textbox, it won’t be changed until we refresh a page or open the report again. This could be unavoidable.
I thought to document this scenario with possible solutions.
Solution 1:
As usual, create a main query dataset with all the above mentioned three parameters. It could be like below,
Dataset 1
SELECT <column names> FORM <tablename>
WHERE FromDate=@FromDate and ToDate=@ToDate and Type=@Type
The above script in the main dataset will create 3 parameters in parameters folder.
Dataset 2
Create another dataset with following Script,
SELECT
CASE WHEN Type=’Yes’ then DATEADD (d,-365,GETDATE()) ELSE DATEADD (d,-30 ,GETDATE()) END as FromDate,
CASE WHEN Type=’Yes’ then GETDATE() ELSE GETDATE() END as ToDate
FROM tablename
Where Type=@Type
Dataset 3
Create another dataset for Type value “Yes” and “No”.
Values of the parameter,
For “Type” parameter set the available values from above dataset3.
For “FromDate” parameter set the default value from dataset 2 and do the same for “ToDate”
Run the report, you should see the result as like below.
Solution 2:
Follow the same steps as you did with solution 1.
Create Dataset1 & Dataset 3.No need to create dataset 2 as we are going to use an expression in parameter itself.
Values of the parameter,
For “Type” parameter set the available values from above dataset3.
For “FromDate” parameter set the default value with “Specify values” option. Click “Add” and write the below expression.
=IIF(Parameters!Type.Value=”Yes”,DateAdd(“d”,-365,Today), DateAdd(“d”,-30,Today))
For ToDate Parameter,
=IIF(Parameters!Type.Value=”Yes”,Today,Today)
ODBC Escape Sequences – Date, Time & Timestamp
To convert the data type of the column in SQL Server, we use to follow below options.
- CAST
- 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
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
- Add the TCP 1433 port inbound in firewall (Linux)
- Configure the DNS in Azure Portal- This is specific for Azure Virtual Machine.
- 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
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.
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 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.
Check the information on the below page as it has the command to configure and start SQL Server.
Configure the VM as usual, check the “Authentication type”. It has two options. I am going to choose Password for this demo.
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.
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.
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.
Next step is to set up the SA admin password for SQL Server. Setup will be completed after this step.
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.
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.
Happy Learning.
Access Azure Virtual Machine Applications Outside of Network
This article explains that how you can access virtual machine applications outside of network with the help of DNS.
For example,
Consider you have installed SQL Server on Azure Virtual machine and created few databases on the server. If you want to access those databases on your local machine, then you need to follow the below steps.
- DNS Configuration in Azure Portal
- Enable the TCP port with 1433 in Azure portal
- Enable the TCP 1433 in virtual Machine
- Create a SQL Server login and enable the mixed mode authentication
Once you have configured all the above steps, you can access your databases on another machine in SQL Server Management Studio.
Refer the below link to know in detail about the above steps.