Check out the Wikipedia article, it is a short summary. In a nutshell: System Integrity Protection adds a new restricted flag to files and folders on the system that prevents even root from writing to it. Apple has added it to /System, /bin and /sbin as well as several stock applications, among other things. This means that root can no longer modify system files and common Apple applications are protected from code injection and runtime attachment.
If that is the risk you are worried about then SIP mitigates or potentially eliminates this. However, root does still exist beyond SIP and it will prevent write operations to common files on your system that surpass regular user access. Even though malware can probably not infect your core system anymore, it can still wreak havoc in your local library.
If you are already used to having two accounts and not using sudo, then I recommend you uphold that practice. SIP is just an additional protection layer, but there is no guarantee that it is impenetrable. Regular users don’t need sudo.
It also occurred to me the other day that SIP can be disabled easily by anyone who has access to your Mac (unless you have a firmware password). All it takes is a boot into Recovery and a simple Terminal command. If you are not aware that SIP has been turned off, you will be vulnerable.