Calculate the distance a line intersects lines

I have created a table containing Geometry Lines in a column. The table is enormous containing millions of lines, but simplified we can populate it with: INSERT INTO tblLines ("geomLines", "elev") VALUES (geometry::STGeomFromText('LINESTRING (10 15, 100 100, 120 79, 90 40, 80 5)', 0), 20); INSERT INTO tblLines ("geomLines") VALUES (geometry::STGeomFromText('LINESTRING (75 25, 50 150, 120 120, 135 40)', 0), 40); Resulting in table tblLines looking like: fidID geomLines elev 1 wkt 20 2 wkt 40 The table tblLines will probably need to be spatially indexed as my actual table is enormous. My tables are stored on Azure SQL Server if this makes any difference? I would like you to write some SQL which I can run in SSMS to get the below table of results. The SQL will include the line: DECLARE @AB GEOMETRY = geometry::STGeomFromText('LINESTRING (60 40, 100 140)', 0); Line AB is the variable I will change. Sometimes it may be just a few meters long, other times it may be hundreds of KM long. I would like the query to report: [elev] [Distance along AB] 40 18 20 25 40 60 The above distances are an approximation. The query should return exact values.