Performance comparison of varchar(max) vs. varchar(N)

The question of comparing the MAX types (VARCHAR, NVARCHAR, VARBINARY) with their non-max counterparts is often asked, but the answer usually gravitates around the storage differences. But I’d like to address the point that these types have inherent, intrinsic performance differences that are not driven by different storage characteristics. In other words, simply comparing and manipulating variables and columns in T-SQL can yield different performance when VARCHAR(MAX) is used vs. VARCHAR(N).

The code path that handles the MAX types (varchar, nvarchar and varbinary) is different from the code path that handles their equivalent non-max length types. The non-max types can internally be represented as an ordinary pointer-and-length structure. But the max types cannot be stored internally as a contiguous memory area since they can possibly grow up to 2Gb. So they have to be represented by a streaming interface, similar to COM's IStream. This carries over to every operation that involves the max types, including simple assignment and comparison, since these operations are more complicated over a streaming interface.

Reference - http://rusanu.com

Should I use user schema seperation in SQL Server

User Schema

Introduction

Prior to SQL Server 2005, a database object  is owned by a user. That user could be DBO or any valid user account. That table is now directly linked to that user– the user cannot be deleted without removing the table or changing the owner of the table. The table can only ever be owned by one user. User-schema separation, introduced in SQL Server 2005, means that the table is no longer owned by any user; it belongs to a schema. In turn, the schema is owned by a user.

A schema is separate entity within the database. It is created by using the CREATE SCHEMA statement. A schema can be owned by a user, a role, or a group. A user executing CREATE SCHEMA can be the owner of the schema or it can allocate another user as the schema owner with appropriate IMPERSONATE permissions. A schema only has one owner, but a user can own many schema. Schema ownership is transferable.

Pros

1. Schema allows much more control of access, and levels of access for the administrator
2. Ownership of schema and the database objects within them is transferable
3. Objects can be moved between schema
4. Multiple database users can share a single schema
5. A database user can be dropped without dropping objects in a corresponding schema
6. Schema logically separates objects in a database. 

Cons

1. If not used wisely, (considering dependencies and commonality of objects), user schema can be quiet confusing.
2. Schema organization must be considered during the design phase to avoid problems with the code. Changing schema design late in the fame could cause many changes in the code.
3. For Programmers who use C# code, although, in most of the cases, user schema works like Namespaces in C#, note that the notations like HR.People schema will be converted to HR_People class in C#.