Get Tesla Stock worth ₹1000. Enter code UMU2BOEITSL while signing up 🥳
Click to claim today 👉🏼
On Web https://www.indmoney.com

## Jun 15, 2012 ## SQL Calculate Distance between Two Points

In this post, I am sharing how to calculate distance between two GEO locations given geographical points (latitude and longitude) of the locations.

Below is a function that takes latitude and longitude of the locations and returns the distance between the locations in miles

Parameters:
• lat1: Latitude of Source Location
• long1: Longitude of Destination Locations
• lat2: Latitude of Source Location
• long2: Longitude of Destination Locations

```CREATE FUNCTION dbo.udf_GetDistance(@lat1 float, @long1 float, @lat2 float, @long2 float)
RETURNS FLOAT
AS
BEGIN
DECLARE @DToR as float
DECLARE @Ans as float
DECLARE @Miles as float

SET @DToR= 57.29577951
SET @Ans = 0
SET @Miles = 0

IF @lat1 IS NULL OR @lat1 = 0 OR @long1 IS NULL OR @long1 = 0 OR @lat2 IS NULL OR @lat2 = 0 OR @long2 IS NULL OR @long2 = 0
BEGIN
RETURN ( @Miles )
END
SET @Ans = SIN(@lat1 / @DToR) * SIN(@lat2 / @DToR) + COS(@lat1 / @DToR) * COS( @lat2 / @DToR) * COS(ABS(@long2 - @long1 )/@DToR)
SET @Miles = 3959 * ATAN(SQRT(1 - SQUARE(@Ans)) / @Ans)
RETURN ( @Miles )
END```

To calculate distance we need geographical points of the locations that we can get from google API's.

Using below link, we would be taking Geo points of North-East and South-West of Delhi and will pass to the function to get the distance between two.

Let's pass the latitude and longitude of both that we get using above link to the function and see the result.

`SELECT dbo.udf_GetDistance(28.88225340, 77.34169940, 28.42219750, 76.85382840) as Miles`
RESULT

## Jun 10, 2012 ## Sequence in SQL Server 2012

Microsoft introduced Sequence object in SQL Server 2012 release.

A Sequence is a user defined, schema bound object that will generate a sequence of numeric values (in ascending or descending order) according to specification. Unlike identity columns, a sequence is created independent of any table. A few interesting differences between the two are;
• A Sequence object is independent of any table, whereas the Identity column property is table specific
• Unlike Identity, you can generate a new sequence value before using it in an insert Statement
• You can define both the minimum & maximum values, as well as cycling & cache size options for a sequence
• Unlike Identity, a sequence object will increment its value only when it is explicitly called

Let's create a sequence object and see how it works.
```CREATE SEQUENCE dbo.emp_sequence AS INT
INCREMENT BY 1```

We have created a sequence with name emp_sequence where the starting value will be 1 and next value will be incremented by 1.

Now let's fetch first value for the sequence.
`SELECT NEXT VALUE FOR dbo.emp_sequence as first_emp`
RESULT

Check the output where first value is fetched as 1.

Now let's fetch next value for the sequence
`SELECT NEXT VALUE FOR dbo.emp_sequence as next_emp`
RESULT

The next value is fetched as 2 which is incremented by 1 from the previous value.

Now let's see how a sequence object can be used with table and how can act as alternative to identity.

For this, we are going to use the same sequence we created in the earlier example, so lets first reset the initial value of sequence
```ALTER SEQUENCE dbo.emp_sequence
INCREMENT BY 1;```

Now lets create a temp table employee where we will insert the data in emp_no column using sequence
```CREATE TABLE #employee (emp_no int, emp_name VARCHAR(10));
INSERT INTO #employee VALUES
(NEXT VALUE FOR dbo.emp_sequence, 'Ajay')
, (NEXT VALUE FOR dbo.emp_sequence, 'Vijay')
, (NEXT VALUE FOR dbo.emp_sequence, 'Sanjay')
SELECT * FROM #employee
DROP TABLE #employee```
RESULT

Check the output where emp_no is generated sequentially as 1,2,3 using sequence.

In the script we have also dropped the temp table, but the current value of sequence has been incremented to 3.

Let's run the same script again
```CREATE TABLE #employee (emp_no int, emp_name VARCHAR(10));
INSERT INTO #employee VALUES
(NEXT VALUE FOR dbo.emp_sequence, 'Ajay')
, (NEXT VALUE FOR dbo.emp_sequence, 'Vijay')
, (NEXT VALUE FOR dbo.emp_sequence, 'Sanjay')
SELECT * FROM #employee
DROP TABLE #employee```
RESULT

Check the output where emp_no is generated as 4,5,6 using the same script. This is because the value of sequence was 3.