Stuart McLachlan
stuart at lexacorp.com.pg
Mon Oct 6 17:25:37 CDT 2003
On 6 Oct 2003 at 13:18, John Colby wrote: > >It does if you use varchar (pads the rest with spaces) but it does not if > you use nvarchar. > > Which begs the question, why use varchar? Faster than Nvarchar? > As Charlotte pointed out, char is fixed length, varchar is variable The base data type is "char" a fixed length string The prefix "var" is used for a variable length version ie "varchar" The prefix "n" is used for the Unicode versions, hence "nvarchar" similarly "varbinary" is a variable length version of the fixed length "binary" type and "nchar", "ntext" are unicode versions of "char" and "text" Char is faster to access than Varchar because of the way it is stored, A fixed length variable is stored in a single location so it can be accessed directly. A variable length variable such as a varchar is stored as a pointer to a start position and a length descriptor with the actual string stored elsewhere so it required two accesses to retrieve. >From V.7 BOL: "Microsoft® SQL Server™ version 6.5 and earlier automatically implemented fixed-length columns that allow null values as variable- length columns of the same data type. For example, a char column that allowed null values was treated as a varchar column. This resulted in less space being used when the length of the value stored in the column was less than the maximum column length. SQL Server version 7.0 treats any fixed-length column that allows null values as fixed- length. Therefore, a char column that allows null values is treated as a fixed-length char column. As a result, the same data now takes more disk space to store and can require more I/O and other processing operations in SQL Server 7.0 compared to earlier versions of SQL Server. To resolve this issue and achieve the same results as SQL Server 6.5 and earlier, use variable- length columns rather than fixed-length columns. For example, use a varchar data type instead of a char data type. However, if all the values in a column are the same length or the lengths of the values do not vary by much, it is more efficient to use a fixed-length column." -- Lexacorp Ltd http://www.lexacorp.com.pg Information Technology Consultancy, Software Development,System Support.