How to setup solution for multi-dimensional distance search with SQL Server spatial data types
There might be some spatial methods that are useful to some degree, but your biggest problem is going to be the Z value:


  Z-coordinates are not used in any calculations made by the library and are not carried through any library calculations.


This is by design. Don't think of SQL spatial objects as "true" 3D-geometric objects, think of them as map coordinates with an "elevation tag" that isn't factored into any calculations. So the distance function STDistance() works great for "map distances", but not true 3D distances.

You can define the Z value (and the "M" value) of a point, but it isn't used by any calculations. 

To demonstrate:

DECLARE @a geometry = geometry::Parse('POINT(0 0 0 2)');
DECLARE @b geometry = geometry::Parse('POINT(1 0 5 1)');
SELECT @a.Z, @b.Z, @a.STDistance(@b)


will return the values

0   5   1


Clearly the distance between these two points should be more than 1, if it used the Z properly. So, they may not work like you hope. 

But if you're ok with ignoring the Z value, then performing these calculations will be pretty straight-forward. You could read these values from your existing table like so:

SELECT geometry::Point(X1,Y1,0).STDistance(geometry::Point(X2,Y2,0))
From myTable;  


(That 0 in this use is not a Z value, it's an SRID.)

or, if you take the time to change how they're actually stored:

CREATE TABLE myPoints (ID INT, P1 geometry, P2 geometry)

INSERT myPoints 
SELECT 1, geometry::Point(X1, Y1, 0), geometry::Point(X2, Y2, 0)
FROM oldTable

SELECT ID, P1.STDistance(P2)
FROM myPoints


There is some related discussion in one of my older answers here.

EDIT: Some further thoughts:


Regarding performance, I have absolutely no clue how geometry objects will compare, performance wise, to you doing the math manually. I imagine converting to Geometry objects on the fly will have a certain degree of overhead, but perhaps converting how your raw data is stored will do better. Testing will be key here.
The Z parameter might not be used by the STDistance() function, but clearly you can manually query Z, and use it to do that part of the math yourself, just use Pythagoras: True3D_Distance^2 = STDistance^2 + (a.Z-b.Z)^2
But note that if your goal is to simply rank the points by closeness, and you don't need to calculate the actual distance, you can simplify your calculation. I'm thinking something like WHERE PointA.STDistance(PointB) + ABS(PointA.Z-PointB.Z) &lt; tolerance or perhaps WHERE PointA.STDistance(PointB) &lt; tolerance AND ABS(PointA.Z-PointB.Z) &lt; tolerance