SQL Server Cast – Is TEXT to VARCHAR(MAX) an Implicit Conversion?

It was all down to collation of the column. It was different from the database's (and the table's) collation. Now changed the column's collation to database's and no more implicit conversion shows up. Have no idea about the internals and why it caused the problem.

How to avoid implicit conversion for an Integer column

It is the parameter that has been implicitly converted, not the column.

The query has been subject to Simple Parameterization by SQL Server. You have no control over the datatypes used in this process. It uses the smallest datatype that can hold the literal value (5 can fit into a tinyint). The implicit cast of a tinyint parameter to an int won't cause any problems.

However to avoid having multiple plans in cache for int, smallint, tinyint and get rid of the implicit cast you could explicitly parameterize the query yourself - with a parameter of datatype int rather than having it be parameterized automatically.

EXEC sys.sp_executesql
  N'SELECT [BusinessEntityID],[NationalIDNumber],[LoginID],[OrganizationNode]
  FROM [AdventureWorks2014].[HumanResources].[Employee]
  where BusinessEntityID = @BusinessEntityID',
  N'@BusinessEntityID INT',
  @BusinessEntityID = 5; 

One other alternative would be to block simple parameterisation by adding a redundant AND 1=1 as below. But I don't recommend this as then you will get plans compiled and cached for every different literal value that you pass.

SELECT [BusinessEntityID],
       [NationalIDNumber],
       [LoginID],
       [OrganizationNode]
FROM [AdventureWorks2014].[HumanResources].[Employee]
where BusinessEntityID = 5 
      AND 1=1