I imagine that calling that multi-line UDF for each update/insert operation on the table will be a pretty horrifying overhead, but it's easy enough to rewrite it as a single statement UDF. Another issue is storage: if several employees shares a status, the old model stores that status just once, while your new model stores it several times; that may not matter is the number of employees isn't large. In addition, either there's going to be a search on status for each update/insert operation, which may need to be supported by an index if there are enough distinct statuses; the old model has a index on statusId, which is an int, which suggests a lot of distinct statuses - otherwise why isn't it smallint (or even tinyint)? - so indexing a 20-bye status as required in the new model will cost some extra storage. But it could improve the performance overall provided the employee table is read far more often than it is updated, and usually has to be joined with the status table every time it's read, and there aren't too many employees, and not too many employees have the same status as each other, and the size of the status isn't going to grow too much in future.

But there are considerations other than the performance of employee table manipulations. Is an individual status value something that applies to a class of employees, rather than to a single employee - ie is an employee status a business-world entity in its own right, that may change wile remaining applicable to the same group of employees, or are statuses just an attribute of an employee, ie something which, when it changes, normally affects just one employee? That may be a more important consideration than performance, because getting the table structure to reflect the business world is generally a good thing, and if status changes that affect many employees simultaneously are common that will be a performance hit against the new model. If in fact the status changes always affect only a single employee the status is an attribute of an employee so it's certainly better design to hold the status in the main table, and the only reason for splitting it out (which is something that people like me regard as dangerous, because someone may carelessly update something that affects many employees when they intended to affect only one) would be to improve performance. On the other hand, if it's a separate entity in its own right, it's certainly better design to put it into a separate table, and the only reason for pulling it into the employee table (which is something everyone calls denormalization) would be to improve performance.

At the end of the day it comes back to one thing: what are the business considerations - all the business considerations, not just performance, which makes quite a long list. Those include flexibility, ease of development, support cost, and a lot things which at first site aren't schema design issues but on proper consideration are; performance is somewhere on that list, but it usually isn't top by any means.

edit: your check constraint confuses a parameter's name with its value, so it wont work unless that is fixed.