I admit I'm confused.

LEN() has always not counted trailing spaces.

When I need to count trailing spaces, I usually just add a non-space and then subtract 1:

SELECT LEN('ABC' + SPACE(3)), LEN('ABC'...

Again, as I noted above, the current leave status (30/365 in this case) should be stored in the main employee "header"/"master" table.  Prior leave status(es) (21/365 in this case) would be...

DATALENGTH() is supposed to return the byte length of the entire value.  Is is not doing that?  MS's doc seem to imply that it should (then again, we've all seen...

Is your db at compatibility 150 level?  I know it almost certainly is, but I'm trying to think of anything that might cause DATALENGTH() to not work correctly (and I...

The employee's current leave status (yes/no, days/year) can go in the main employee table.  It should be encoded, such as a tinyint.  If the leave status changes, you store the...

It would be helpful if you could give us the definitions of all the objects involved.

Yes, I believe you reading of the original plan was correct.

I believe the error was because the first data type returned by the CASE is a varchar -- specifically 'Default Memory Settings - needs to be configured' -- and thus...

You can fix the immediate problem by changing the code like so:

else cast(value_in_use as varchar(50)) end as SQLMemoryInUse

I'd expect maybe a Shared lock and even a Schema lock on the table, but I don't think SQL would need to exclusively lock the table.

If you CAST the value to a different data type, such as decimal, SQL will automatically round the value for that type, so you will have to do a ROUND...

Should I add the Semester_ID Reference here?

I guess.  What you really need to do is step back and do a true logical data model first including normalization.  Your current table structures...

Also, btw, the data model you have is not correct.  You should have a separate "master" Course table, with nothing about semester in it, that contains core info about...

It's likely your PK on Inventory should be ( WarehouseID, ProductID ), at least based on that query (of course if you have a significant number of queries that use...

I'd need directly usable sample data for the holiday table -- CREATE TABLE and INSERT statement(s) -- to correct this for you.