The points are being pushed into the MS SQL database via python script as they are pulled from the REST endpoints.
OK. But who created the table and specified the 0,0,1,1 bounds for the spatial index? If the table and the index were created by 9, I will check why we ended up with such bounds.
In general, you can drop and re-create the spatial index specifying tighter bounds and this should fix the zoom to fit. To compute the exact bounds, use something like (on SQL Server, you may connect to it in 9, open the command window for the SQL Server data source and run the query in that window):
...then increase the bounds somewhat to allow inserting new geometry, and use them when creating a new index:
CREATE SPATIAL INDEX table_geom ON table(geom) WITH (
BOUNDING_BOX = (0, 0, 100, 200)
The above assumes, however, that the reason you cannot see much when trying to zoom to fit is that the bounding box of the index is too big. There might be a second reason which I thought about after hearing that the table was populated by a script: most of the geometry lives someplace reasonable, but some of the geometry values refused to be read correctly or whatever and ended up with default coordinates far away from the real values. This happens sometimes: the real objects are, say, in the right top corner, and the fake objects that must have been NULLs but ended up with some default geometry because of the conversion process are in the opposite corner. So when you zoom to fit, you see nothing except two points - one in the right top and another in the left bottom. If this is the case with your data, you might see that from the bounds computed using STEnvelope, one pair of XY values will look weird, that's where the fake geometry might be.
Also, regarding selecting objects and zooming to the selection: I get that selecting using Ctrl-drag didn't result in much, but if you select everything by pressing Ctrl-A, and then zoom to the selection using the layer context menu, do you zoom to a reasonable view? If yes, we are in problem 1 (the data is fine, but the bounding box of the index is too big), otherwise we might be in problem 2 (some of the data is not fine, possibly due to geometry that should have been NULL converting to some default coordinates).