What to unit test itself is a long learning process, much of it determined by anticipating problems. They depend on existing state of the app, frequency of upgrades, hardware capacities, software fixes, expected loads, and so on.

Both correctness of SQL and efficiency of SQL in development code can suffer from inappropriate unit tests. Developing the right combination for a given app is the crux of the problem in my experience. Otherwise unit tests can give another false sense of comfort.

The easiest unit tests and the most common ones I see are for user defined functions or relational features, such as referential integrity or check constraints. Beyond that, for me, it was entirely case-by-case basis. In almost all cases, we added the most interesting unit tests only after the application was in production for some time. We still keep adding more tests as we learn more ways it could fail or discover new edge conditions.