Hi
When u use Varchar(max), it is 8000 chars for a variable, and around 2^32 for a column, what is COLLATE and how it affects that?
Thanks
+3
A:
Collation deterines how SQL Server sorts and compares string data (which varchar variables and column values are).
See here
Adam Robinson
2010-04-23 18:28:41
A:
When you declare a column or a variable of varchar(max), it can take up to 2 GB of data and 2^31-1 characters. If you declare the column or variable as nvarchar, it can still only take 2 GB of data and (2^31-1) / 2 characters since each character takes up twice as much space. When you declare a varchar column or variable without the use of the COLLATE clause, the collation of the database is used. The ``COLLATE clause does not affect the capacity of the column or variable.
Thomas
2010-04-23 18:43:07
Not quit accuratehttp://msdn.microsoft.com/en-us/library/ms176089%28SQL.90%29.aspx
Tony
2010-04-23 18:50:18
What are you claiming is not accurate?. Both varchar and nvarchar have the same max space. (Ignoring the extra 2 bytes). Since nvarchar takes up twice as much space, you get half as many characters (but the same total max space).
Thomas
2010-04-23 19:38:44
@Tony - Regardless of the collation, you still only get 2 GB of storage. How many characters that translates to can change on the collation but *in general* each character in a varchar takes up a byte per character and two bytes per character in a nvarchar.
Thomas
2010-04-23 19:42:16