There are no mods on official servers. If you are playing single player or have your own private server, you can use any mods you want. They are downloaded from the Steam workshop. If you play on someone else’s private server you will need to download the mods they have chosen to play with (if any). There is a setting that allows the server’s mods to be downloaded/updated automatically so you don’t have to hunt down the exact mods a server is using. Hope this helps.
You cannot use mods on official servers. Both the client and the server need to be running the mods in order for them to be used meaning you have the following options:
Single-player where you decide what mods to use
Private servers where the server owner has decided which mods are going to be used. In order for you to connect to it, you will need to run the same mods in the same load order.
The mods are downloaded from Steam’s workshop. Some mod authors may provide also alternative sources via a link either on their steam mod page or in their respective discords.
About usefulness, it depends … you have a great variety of mods from simple quality of life mods to complete game overhaul and new maps.