Most of us freequently used NVARCHAR data type for storing normal alpha numeric characters, But we can simply use VARCHAR data type instead.
We can use VARCHAR data type insteadof NVARCHAR. Correct ?
A) When we go for NVarchar data type ?
B) Is there any internal differences between Varchar and NVarchar data types ?
According to BOL
varchar [ ( n max ) ]
1. Variable-length, non-Unicode character data. n can be a value from 1 through 8,000.
2. MAX indicates that the maximum storage size is 2^31-1 bytes.
3. The storage size is the actual length of data entered + 2 bytes.
4. The ISO synonyms for varchar are char varying or character varying.
5. When n is not specified in a data definition or variable declaration statement, the default length is 1.
6. When n is not specified when using the CAST and CONVERT functions, the default length is 30.
nvarchar [ ( n max ) ]
1. Variable-length Unicode character data. n can be a value from 1 through 4,000.
2. MAX indicates that the maximum storage size is 2^31-1 bytes.
3. The storage size, in bytes, is two times the number of characters entered + 2 bytes.
4. The ISO synonyms for nvarchar are national char varying and national character varying.
5. When n is not specified in a data definition or variable declaration statement, the default length is 1. When n is not specified with the CAST function, the default length is 30.
A) When we go for NVarchar data type ?
If we have sites that support multiple languages (Multi-lingual), consider using the Unicode nchar or nvarchar data types to minimize character conversion issues. Except that, we have to use Varchar data type.
B) Is there any internal differences between Varchar and NVarchar data types ?
YES.
Actually NVARCHAR data type uses Bytes in two times the number of characters used. But, In VARCHAR uses only the number of characters used.
NVARCHAR data type uses more memory spaces than VARCHAR data types. How ?
NVARCHAR data type requires more Subtree Cost and I/O than the VARCHAR data types. How ?
Creating Sample Tables and Records :
IF OBJECT_ID('Tb_SQLServer_VAR','U') IS NOT NULL
DROP TABLE Tb_SQLServer_VAR
GO
CREATE TABLE Tb_SQLServer_VAR
(
Column1 VARCHAR(100)
)
GO
IF OBJECT_ID('Tb_SQLServer_NVAR','U') IS NOT NULL
DROP TABLE Tb_SQLServer_NVAR
GO
CREATE TABLE Tb_SQLServer_NVAR
(
Column1 NVARCHAR(100)
)
GO
We have created two tables. Each table has only one column.
INSERT Tb_SQLServer_VAR VALUES('SQL Server Buddy')
GO 1000
Go
INSERT Tb_SQLServer_NVAR VALUES('SQL Server Buddy')
GO 1000
We have Inserted 1000 records in two tables (Tb_SQLServer_VAR, Tb_SQLServer_NVAR)
Identifying Space allocated on each Tables for VARCHAR, NVARCHAR data types:
SELECT OBJECT_NAME(p.[object_id]) 'Table Name',
p.[rows] 'Record(s)',
a.total_pages*8 'Total Pages(KB)',
a.used_pages*8 'Used Pages(KB)',
((a.total_pages*8)- (a.used_pages*8)) 'Unused Pages(KB)',
a.type_desc
FROM sys.allocation_units A JOIN sys.partitions P
ON (A.container_id = P.hobt_id)
where Object_Name(p.[object_id]) in('Tb_SQLServer_VAR','Tb_SQLServer_NVAR')
Result:
Tb_SQLServer_VAR table has one VARCHAR data type column.
Tb_SQLServer_NVAR has one NVARCHAR data type column.
Both table have 1000 Records. But the Size allocated for table differs.
40 KB for VARCHAR data type table - Tb_SQLServer_VAR
56 KB for NVARCHAR data type table - Tb_SQLServer_NVAR
So, NVARCHAR data type occupies more spaces than the VARCHAR data types. Correct.
Identifying Plan/Optimizer activity on each Tables:
SET STATISTICS PROFILE,TIME,IO ON
SELECT Column1 FROM Tb_SQLServer_VAR
GO
SELECT Column1 FROM Tb_SQLServer_NVAR
SET STATISTICS PROFILE,TIME,IO OFF
Result:
Logical reads from both table differs.
6 Logical reads performed on NVARCHAR data type table.
4 Logical reads performed on VARCHAR data type table.
Conclusion:
NVARCHAR data types can be used only when really needed. Not for normal storage.
No comments:
Post a Comment