Finding closest points without duplicates and given distance threshold
Style

Remove unneccessary parentheses

You don't need parentheses around the condition for the WHILE
loop.  You can just write WHILE @counter &gt; 0.

Also, the extra sets of parentheses around the random numbers are
superfluous.  If you leave them off, the line will be a bit less
daunting.

Don't break the line in the middle of a concept

That is, in the SELECT @RandomLatitude line, the statement breaks
just before the * operator.  As it has a higher precedence than
the +, this can be a bit misleading.  Either don't break, use
parentheses (if really unavoidable) or break before the +:

SELECT @RandomLatitude = ( @UpperLatitude - @LowerLatitude - 1 ) * RAND()
                         + @LowerLatitude;
SELECT @RandomLongitude = ( @UpperLongitude - @LowerLongitude - 1 ) * RAND()
                          + @LowerLongitude;


Merge variable declaration and initialisation

Where possible, declare a variable just before it is used, and
initialise it there as well:

DECLARE @LowerLatitude FLOAT = -90;
DECLARE @UpperLatitude FLOAT = 90;
DECLARE @LowerLongitude FLOAT = -180;
DECLARE @UpperLongitude FLOAT = 180;

DECLARE @Counter INT = 1000;
WHILE @Counter &gt; 0
  BEGIN
    DECLARE @RandomLatitude FLOAT = ( @UpperLatitude - @LowerLatitude - 1 ) * RAND()
                                    + @LowerLatitude
    DECLARE @RandomLongitude FLOAT = ( @UpperLongitude - @LowerLongitude - 1 ) * RAND()
                                     + @LowerLongitude
    -- ...


Performance

You mention that the indices are already set. I assume that means that
it is not possible to add a Spatial Index, or a primary key.
Although I would strongly suggest adding a primary key constraint to
the temp table in this example:

CREATE TABLE #Points1
    ( PointId INT IDENTITY(1, 1) PRIMARY KEY
    , Point GEOGRAPHY NULL
    );


Unfortunately, without a Spatial Index or changes to the result
requirements (you'd need a TOP n clause and an ORDER BY on the
STDistance column), there is no choice but to check each point against
all others, which will give a large join of which I don't know how to
improve the performance.

Small point: you may gain a smaller result (only in number of rows) if
you leave out the distance from each point to itself:

INNER JOIN Points1 AS Points2 ON Points1.PointId &lt; Points2.PointId


but that might not fit your requirements.