Avoid using varchar or char data type


     Today,  I like to put one suggestion for choosing the data type of string column.  My recommendation is always go with nvarchar type. You will not be regret if your database always using nvarchar type.  For example, our SharePoint backend database always use nvarchar type, and they don't have any issue related to store characters in different language.  And the nvarchar types has better support in .Net, ODBC, JDBC, and Windows. 

  Varchar or Char types can only support a very limited range of characters, and the client or tools support is not so great comparing with nvarchar type.

It is true that using nvarchar type will double the data size if most of your data is Latin character.  in SQL Server 2008 R2, our Data Compression Feature's page compression option allow you to compress such database to more than half of the size with less performance impact.  So I suggest that you using nvarchar type with page compression together to achieve both small disk size and better platform support.   Our SharePoint database use exactly the same technique.

 

 

 

Comments (5)

  1. Why do I set the string is always wrong?

  2. QingsongYao says:

    Please provide detail information about your issue.  I can help you to reslove this.

  3. Adnan says:

    So I suggest that you using nvarchar type with page compression together to achieve both small disk size and better platform support.

    From where i can set page compression ??

  4. QingsongYao says:

    You can set the page compression on table level, index level. However, page compression can only available  on Enterprise Edition.  Please search for Create Table syntax for this. You can also achieve it by using SQL Server Management Studio => Select a Table => Storage => Manage Compression

  5. Anuj says:

    Hi QingsongYao,

    Could you please help me to solve below problem ?

    social.msdn.microsoft.com/…/sql-server-2008-r2-native-replication-with-column-level-collation

    Regards,

    -Anuj

Skip to main content