Few Points in SQL Server

on VarChar and NVarchar

First of all, the basic difference between a varchar and a nvarchar datatype is that for each variable the latter one takes double the space needed by the former one. Now why double? Because of the extra "n", which means that it *can* store Unicode characters as well.


Unicode characters represent a wide variety of foreign locales and many times they need an extra byte for the same char storage. Hence nvarchar can store an extended character set.

In SQL Server 2000, varchar has the maximum limit of 8000 characters (when you need to give a particular size), and nvarchar has 4000 characters only (remember it needs double the space needed by a varchar, hence the storage capacity becomes half). Infact a given row in a SQL Server 2000 table cannot exceed 8000 characters in size.

But if you need to store more than 8000 characters? There was no option other than to use TEXT or the NTEXT datattypes, common called as BLOBS (Binary Large Objects), both of which have their own limitations.

Hence in SQL Server 2005, the MAX identifier was introduced, which allows us to go beyond 8000 characters and store upto 2^31- 1 bytes,which comes around 2 GB! But note that you still cannnot specify a size greater than 8000 characters in n/varchar (like varchar(12000) is still not allowed, need to use MAX for data greater than 8000 bytes).

We can use them as: varchar (MAX) / nvarchar (MAX)

So as a general practice, avoid using TEXT/NTEXT datattypes and use varchar(MAX) and nvarchar(MAX) instead.

just watch out for the + (plus) operator (or string concatination operator) when using varchar(max) or nvarchar(max). it truncates ones data after the total number of page size bytes. rather use the replace function;


               declare @result nvarchar(max)
               set @result = N'the text which would have been used as the first operand before the plus operator that also exceeds 4000 characters FANCYPLACEHOLDER'
               set @result = replace(@result, N'FANCYPLACEHOLDER', ' ...some more text which may also exceed 4000 characters...')

Language Intregrated Query (LINQ)

At the heart of LINQ is a framework defined under the Systme.LINQ namespace, which when coupled with the new language constructs of C# 3.0 provides the ability to write LINQ queries, either over objects held locally or over remote data sources.

The key features of C# 3.0 that are important to writing LINQ queries are extension methods and lambda expressions.

• Extension methods enable you to extend the methods of an existing type. For example, you could create an extension method to the String class called ToStringArray(), which when called turns a string variable into a string array.
• Lambda expressions enable you to write an expression within a method signature that is evaluated at runtime. Lambda expressions are a shorthand way of writing a delegate.

• LINQ provides classes that contain extension methods commonly referred to as query operators.

Examples of LINQ query operators are Select, Where, and GroupBy. Many of these methods will feel familiar to SQL developers as much of the set- based logic in LINQ follows the same semantics as SQL. Query operators accept lambda expressions as a way of evaluating the input to the query operator to return an output.