Joins

In the Current Blog I will Talk about joins

what are joins ?
Types of Joins ?

So lets get started

Join is keyword in SQL Server, which helps to join records from more than one table

Their are 6 types of joins

• Inner join
• Left Join  also called Left outer Join
• Right join also called right outer Join
• Full outer join
• self join
• cross join / Cartesian product join

Inner join : Inner join gets the records from both the tables which has common elements
 syntax   Select a.column1, a.column2, b.column3
               From Tablea as a
               Inner join Tableb  as b on a.column1 = b.column4

Example

Employee

Emp_ID	Emp_name	Emp_salary	Dept_ID
1	Sandeep	1200	10
2	Alan	1300	20
3	Kalid	480	30
4	David	678	60
5	Pradeep	899	10
6	Javid	2300	40

Dept_ID	Dept_name
10	Computer Science
20	Agricultural Science
30	Commercial Studies
40	Technical Drawing
50	Mathematic

Select e.* ,d.Dept_name
From dbo.Employee e
inner join dbo.Dept d on e.dept_ID = d.Dept_ID

Emp_Id	Emp_name	Emp_salary	dept_ID	Dept_name
1	sandeep	1200	10	Computer Science
5	pradeep	899	10	Computer Science
2	Alan	1300	20	Agricultural Science
3	Kalid	480	30	Commercial Studies
6	Javid	2300	40	Technical Drawing

Left join/Left outer join : Left join will write down whole left table and the matching information from the right table
and not matching records will have null's joined

Select e.* ,d.Dept_name
From dbo.Employee e
Left  join dbo.Dept d on e.dept_ID = d.Dept_ID

Emp_Id	Emp_name	Emp_salary	dept_ID	Dept_name
1	sandeep	1200	10	Computer Science
2	Alan	1300	20	Agricultural Science
3	Kalid	480	30	Commercial Studies
4	David	678	60	NULL
5	pradeep	899	10	Computer Science
6	Javid	2300	40	Technical Drawing

Right join/Right outer join : Right join will write down whole right table and the matching information from the Left table and not matching records will have null's joined

Example

Select e.* ,d.Dept_name

From dbo.Employee e

Right  join dbo.Dept d on e.dept_ID = d.Dept_ID

Emp_Id	Emp_name	Emp_salary	dept_ID	Dept_name
1	sandeep	1200	10	Computer Science
5	pradeep	899	10	Computer Science
2	Alan	1300	20	Agricultural Science
3	Kalid	480	30	Commercial Studies
6	Javid	2300	40	Technical Drawing
NULL	NULL	NULL	NULL	Mathematics

Full Outer join :  Full outer join is the combination of left join and right join  with distinct information 

Example

Select e.* ,d.Dept_name

From dbo.Employee e

Full outer  join dbo.Dept d on e.dept_ID = d.Dept_ID

Emp_Id	Emp_name	Emp_salary	dept_ID	Dept_name
1	sandeep	1200	10	Computer Science
2	Alan	1300	20	Agricultural Science
3	Kalid	480	30	Commercial Studies
4	David	678	60	NULL
5	pradeep	899	10	Computer Science
6	Javid	2300	40	Technical Drawing
NULL	NULL	NULL	NULL	Mathematics

Self Join : Self Join is joining of table by itself  

Example

Emp_Id	Emp_name	Emp_salary	dept_ID	Manager_ID
1	sandeep	1200	10	2
2	Alan	1300	20	4
3	Kalid	480	30	6
4	David	678	60	2
5	pradeep	899	10	4
6	Javid	2300	40	6

Select e.* ,e1.Emp_name as manager_name

From dbo.Employee e ,dbo.Employee e1

where e.Manager_id = e1.Emp_Id

Emp_Id	Emp_name	Emp_salary	dept_ID	Manager_ID	manager_name
1	sandeep	1200	10	2	Alan
4	David	678	60	2	Alan
2	Alan	1300	20	4	David
5	pradeep	899	10	4	David
3	Kalid	480	30	6	Javid
6	Javid	2300	40	6	Javid

Cross join/Cartesian product join : cross join is a kind of join that would get all possible combinations of rows as the result set from tables

In other words its the multiplication of rows from both the tables 

Example

 First table has 6 rows and second table has 5 rows 

Emp_Id	Emp_name	Emp_salary	dept_ID	Manager_ID	Dept_ID	Dept_name
1	sandeep	1200	10	2	10	Computer Science
2	Alan	1300	20	4	10	Computer Science
3	Kalid	480	30	6	10	Computer Science
4	David	678	60	2	10	Computer Science
5	pradeep	899	10	4	10	Computer Science
6	Javid	2300	40	6	10	Computer Science
1	sandeep	1200	10	2	20	Agricultural Science
2	Alan	1300	20	4	20	Agricultural Science
3	Kalid	480	30	6	20	Agricultural Science
4	David	678	60	2	20	Agricultural Science
5	pradeep	899	10	4	20	Agricultural Science
6	Javid	2300	40	6	20	Agricultural Science
1	sandeep	1200	10	2	30	Commercial Studies
2	Alan	1300	20	4	30	Commercial Studies
3	Kalid	480	30	6	30	Commercial Studies
4	David	678	60	2	30	Commercial Studies
5	pradeep	899	10	4	30	Commercial Studies
6	Javid	2300	40	6	30	Commercial Studies
1	sandeep	1200	10	2	40	Technical Drawing
2	Alan	1300	20	4	40	Technical Drawing
3	Kalid	480	30	6	40	Technical Drawing
4	David	678	60	2	40	Technical Drawing
5	pradeep	899	10	4	40	Technical Drawing
6	Javid	2300	40	6	40	Technical Drawing
1	sandeep	1200	10	2	50	Mathematics
2	Alan	1300	20	4	50	Mathematics
3	Kalid	480	30	6	50	Mathematics
4	David	678	60	2	50	Mathematics
5	pradeep	899	10	4	50	Mathematics
6	Javid	2300	40	6	50	Mathematics

Reasons for changing the SQL Server port number and how to change it

In my last post, I have shared the information on how to specify the port number to connect to the SQL Server Instance but, now I would like to share some information on 

Why should we change the SQL Server port number? Where and How to change the SQL Server port?

Why should we change the SQL Server port number?

The SQL Server instance can have a static or dynamic port allocation.

By default when SQL server is installed , it installs the default instance, unless you provide a different instance name. So, if it’s a default instance then its given Static port 1433.

But if the SQL Server is a named instance then it will give a Dynamic port the SQL Server which may change when the SQL Services are restarted.

As the default port 1433 of SQL is well known , it’s the security best practice to change the port number to minimize the potential attacks. The other reason is if other application is using that particular port or the administrator can’t open the port then we need to change the SQL Server port number.

Where and how to change the SQL Server port?

To change the SQL Server port and to change the static to dynamic port allocation follow the steps below:

1. Open SQL Server Configuration Manager 

2.  Then click SQL Server Network Configuration

3.  On the right side, select the instance you want to configure to listen on a specific port. In the below screenshot I have just one instance so selecting that.

4.   To change the port, double click the instance and select the TCP/IP protocol  right click TCP/IP protocol and select Properties.

5. Click on the properties and once clicking the properties TCP/IP Properties dialog box appears, in that box click the IP Addresses tab.

 If we see the above screenshot, the TCP Dynamic Ports is not set to any value which means the database engine is listening on static ports. We can change this to Dynamic port by setting it to “0” in the TCP Dynamic Ports field.

If you see the TCP Port field it is set to 1433, this is where we need to change the SQL Server Port value. For example, if you want to change the value to 4141 then in place of 1433 change it to 4141 and click on OK.

6.  Once you set the new value select SQL Server Services in the SQL Server Configuration Manager and in that right click SQL Server and restart it to see the change.

How to specify a port number to connect to SQL Server Instance

How to connect to a different port with SQL Server Management Studio

Microsoft SQL Server Management Studio will connect by default to port 1433 and there is nowhere in the connect dialog box below to specify a different port from the default. So, if you want to connect using different port then specify the Server name or IP , a comma and then the port number.

Connect using a different port using a comma:

The connection dialog in the SQL Server Management Studio is shown below.

Normally, you would just type the server name into the field but, to specify a different port number use a comma after the server name then enter the port number.

          

The above example shows the “Rain-sql” as the server name and “port” where the port would be. 

If you want to connect to a server named Shine-sql on port 4321 then set the Server name to

Example: Shine-sql,4321

If you know the IP of the server then in place of the server name you can use the IP.

Example: 192.168.1.22,6789

For named instances, you usually connect by specifying the server name and instance name (in place of port number).

Example: Shine-sql, instance name

If you specify the port the named instance is using then there is no need to specify the instance name because each instance on a server uses a different port.

Date Time functions

Date and Time Functions are the part of SQL server System functions, They can be found under the programmability folder -->Functions--> System Functions -->Date and Time Functions

Date time in general represented as YYYY-MM-DD hh:mm:ss.nn

List function of Date Time functions

• Current_Timestamp : This is an ANSI function , that will get you he current date and time.It doesn't  take any arguments

Example:

Print CURRENT_TIMESTAMP

Jul 10 2017 12:30PM

       

• Dateadd()  : Dateadd function helps you to build a date and time, by adding interval value to the specified date part , interval number is signed number

               

syntax  DATEADD (datepart , number , date ) 

Example

print dateadd(dd,1,'07/10/2017')

print dateadd(dd,-1,'07/10/2017')

Jul 11 2017 12:00AM

Jul 9 2017 12:00AM 

• Datediff() : Datediff  functions helps you to find the difference between two given date times in the requested datepart format  

  syntax DATEDIFF ( datepart , startdate , enddate ) 

Example

print datediff(dd,'07/10/2017','07/11/2017')

print datediff(hh,'07/10/2017','07/11/2017')

1

24

• Datename(): Datename function helps you to print the name of the date part for the given datetime

syntax DATENAME ( datepart , date )

Example
print Datename (mm,'07/11/2017 23:00:03')

print Datename (yyyy,'07/11/2017 23:00:03')

print Datename (hh,'07/11/2017 23:00:03')

July

2017

23 

• Datepart() : Datename function helps you to print the number of the date part for the given datetime

 syntax DATEPART ( datepart , date ) 

Example
 print Datepart (mm,'07/11/2017 23:00:03')

print Datepart (yyyy,'07/11/2017 23:00:03')

print datepart (hh,'07/11/2017 23:00:03')

7

2017

23

• Day() : Day function returns you the no of day from the give date time input

syntax Day(Date)

Example
print Day ('07/10/2017')
10 

• Getdate() : Getdate function returns date and time off today

syntax Getdate()

Example

print getdate()

Jul 10 2017 3:38PM

•  Getutcdate() : Getutcdate function returns date time off today

syntax Getutcdate()

Example

select getdate() as Todays_date,Getutcdate() as Today_UTC

Todays_date                       Today_UTC

2017-07-10 15:47:50.500   2017-07-10 22:47:50.500

• Isdate(): Isdate function helps us to identify wheather the input values is date or not, if it is a date it would return 1

syntax Isdate(string)

Example

print isdate('07/10/2017')

print isdate('cat')

1

0

• Month() : Month function returns integer value of the month from the input date time provided

Syntax Month(date)

Example
print Month('07/10/2017')
7

• Sysdatetime(): Sysdatetime function returns date time of the time it was executed, it difference from getdate only to the level of granularity  

Syntax sysdatetime()

Example
print getdate()

print Sysdatetime()

Jul 10 2017 4:14PM

2017-07-10 16:14:24.8253614

• Sysdatetimeoffset(): Sysdatetimeoffset function returns date time of the time it was executed along with offset value

For example server in which we executing the command is in PST/PDT it would get -7 value where as in ESDT/EDT it would be -4


syntax Sysdatetimeoffset()

Example
print Sysdatetimeoffset()
2017-07-10 16:29:26.6988611 -07:00

• sysutcdatetime(): sysutcdatetime function returns sysdatetime in utc formate

syntax sysutcdatetime()

Example

print Sysutcdatetime()

2017-07-10 23:36:27.1041777
  

• Switchoffset() : Switchoffset function returns a datetimeoffset value that is changed from the stored time zone offset to a specified new time zone offset. 

syntax Switchoffset ( dattimeoffset, time_zone )

Example

print Switchoffset('07/10/2017 7:45:50.71345 -5:00', -7)

2017-07-10 12:38:50.7134500 -00:07

• Todatetimeoffset() : Function requires 2 input parameters first one is the date time with offset value second parameter is the new offset value which you would like the original to be changed too

syntax TODATETIMEOFFSET ( expression , time_zone )

Example
DECLARE @todaysDateTime datetime2;

SET @todaysDateTime = '2017-07-10 16:51:42.8066667 -04:00'

SELECT TODATETIMEOFFSET (@todaysDateTime, '-07:00');

2017-07-10 16:51:42.8066667 -07:00

• Year() : year function returns the year of the input date to the function

syntax Year(string)
Example

print year(getdate())
2017