MS Sql

List of all index & index columns in SQL Server DB

SELECT
TableName = t.name,
IndexName = ind.name,
IndexId = ind.index_id,
ColumnId = ic.index_column_id,
ColumnName = col.name,
ind.*,
ic.*,
col.*
FROM
sys.indexes ind
INNER JOIN
sys.index_columns ic ON ind.object_id = ic.object_id and ind.index_id = ic.index_id
INNER JOIN
sys.columns col ON ic.object_id = col.object_id and ic.column_id = col.column_id
INNER JOIN
sys.tables t ON ind.object_id = t.object_id
WHERE
ind.is_primary_key = 0
AND ind.is_unique = 0
AND ind.is_unique_constraint = 0
AND t.is_ms_shipped = 0
ORDER BY
t.name, ind.name, ind.index_id, ic.index_column_id

Latitude/Longitude Distance Calculation in SQL Server

Here’s the SQL function:

CREATE FUNCTION CoordinateDistanceMiles(
@Latitude1 float,
@Longitude1 float,
@Latitude2 float,
@Longitude2 float
)
RETURNS float 
AS  
BEGIN 
-- CONSTANTS
DECLARE @EarthRadiusInMiles float;
SET @EarthRadiusInMiles = 3963.1
DECLARE @PI  float;
SET @PI = PI();
-- RADIANS conversion
DECLARE @lat1Radians float;
DECLARE @long1Radians float;
DECLARE @lat2Radians float;
DECLARE @long2Radians float;
SET @lat1Radians = @Latitude1 * @PI / 180;
SET @long1Radians = @Longitude1 * @PI / 180;
SET @lat2Radians = @Latitude2 * @PI / 180;
SET @long2Radians = @Longitude2 * @PI / 180;
RETURN Acos(
Cos(@lat1Radians) * Cos(@long1Radians) * Cos(@lat2Radians) * Cos(@long2Radians) + 
Cos(@lat1Radians) * Sin(@long1Radians) * Cos(@lat2Radians) * Sin(@long2Radians) + 
Sin(@lat1Radians) * Sin(@lat2Radians)
) * @EarthRadiusInMiles;
END

Here is how to use it.

SELECT * FROM Hotels
WHERE dbo.CoordinateDistanceMiles(Latitude, Longitude, @ZipLatitude, @ZipLongitude)  @Radius 
--@Radius is miles.

Shrink sql database transaction log file

--Database Name
USE Database_Name;
GO
-- Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE Database_Name
SET RECOVERY SIMPLE;
GO
-- Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (Database_Name_Log, 1);
GO
-- Reset the database recovery model.
ALTER DATABASE Database_Name
SET RECOVERY FULL;
GO

When your sql database transaction log file is too big than shrink it . It will take about 30 second. You can run this script any time you want.

Sql row number usage

USE AdventureWorks2012;
GO
WITH OrderedOrders AS
(
    SELECT SalesOrderID, OrderDate,
    ROW_NUMBER() OVER (ORDER BY OrderDate) AS RowNumber
    FROM Sales.SalesOrderHeader 
) 
SELECT SalesOrderID, OrderDate, RowNumber  
FROM OrderedOrders 
WHERE RowNumber BETWEEN 50 AND 60;

PS: When you do not have increase identity number . You can use ms sql row number feature.

Query from different sql server


EXEC sp_configure 'show advanced options', 1
RECONFIGURE
GO
EXEC sp_configure 'ad hoc distributed queries', 1
RECONFIGURE
GO

SELECT  *
FROM OPENDATASOURCE('SQLNCLI',
 ' Data Source=******;User ID=******;Password=****** ')
 .[databseName].[dbo].[tableName]
GO

PS: First ‘show advanced options’ and ‘Ad Hoc Distributed Queries’ turn on and then use sql OPENDATASOURCE command first parameter provider and second parameter is connection string that you want to connect.

Sql server remote connection

# Check right click sql server instance Properties>Connections Allow remote connection to this server .

# Open sql server configuration manager > SqlServer Network Configuration > Protocols For Sql Server  then enable TCP/IP and TCP/IP >Ip Address Tcp port must be 1433.

# Go to windows firewall >  Advanced Settings  > Inbounds Rules and add new rule  add tcp port 1433 then enable rule.