Explicit Conversion (Convert vs Cast ) in SQL Server

For years I have been using Convert and Cast function in T-SQL with out giving much thought. Recently I was asked about the difference, I knew Cast was ANSI complaint and Convert was more specific to SQL Server beside that I was stumped. So I decided to do little digging, my first concern was to see if there are any Performance differences..
I found this article by Nakul where he tried to test performance between cast and convert.
http://beyondrelational.com/blogs/nakul/archive/2011/01/03/cast-v-s-convert.aspx
It becomes clear from the test that there is not much performance difference between the two functions but there few more things you could do with Convert which Cast cannot. Especially in a reporting scenarios where Datetime/Money types needs to be formatted, for such cases convert function proves handy. Convert also can convert string types to datetime this becomes useful when parsing through text files.
So for me I would choose convert over cast beacause of extra formatting flexibility it provides.

Large Value Data Types (Varchar(max) vs Text)

Have you wondered which data type to use when you are assigned to store large size data in a table (larger than 8000 characters). Physical limitation for SQL Server is 8 K per row. To store anything larger than 8 K you will need to choose between Text and varchar(max). On the surface both look same but there is a fundamental difference.
First of all Text is legacy type and existed before SQL 2005 release. Text are always stored outside the table as LOB object. It can store up to 2 GB of data. But it has few shortcoming listed below..

- Cannot join on text column
- Cannot be used on where clause
- String manipulation is limited (sub string functions)

With SQL 2005 Varchar(max) and nVarchar(max) was introduced. It addresses the limitation of text in terms of join, search and string manipulation with out compromising on the size limit ( 2 GB).Also varchar(max) is stored in the table space when size is below 8 K, if size exceeds 8 K it is stored outside table space. Data retrieval from tablespace is much faster.According to Microsoft Text data types will be depricated from future releases. Avoid using this feature in new development work, and plan to modify appliation that currently use Text and nText types.
Naturally Varchar(max) is a better option and should be used when ever possible. Text data types should only be for backward compatibility.
For additional reading on Large Value Data Types
http://msdn.microsoft.com/en-us/library/ms17815(v=SQL.110).aspx

When to use varchar(max) in sql server?

When length is greater than 4000 character then it is recommended to use varchar(max) because from storage prespective there is no difference between varchar(max) and fixed lenght varchar(n) when n > 4000.In both the cases when length exceeds more than 4000 data row is replaced with a pointer to a seprate page. So it is recommended to store data as varchar(max) for larger values.