In
sql sever following are major differences between char and varchar
1.
Data type char is example of fixed length memory allocation. For
example:
DECLARE @Value AS CHAR(10) = 'Exact'
SELECT DATALENGTH(@Value)
Output: 10
So, Sql sever has
allocated 10 byte memory spaces while total number of characters in the constant
'Exact' is only 5.
Data type varchar is example of variable
length memory allocation. For example:
DECLARE @Value AS VARCHAR(10) = 'Exact'
SELECT DATALENGTH(@Value)
Output: 5
So in case of
varchar sqlsever use dynamic allocation.
2. If total
numbers of characters is less than size of char data type then sql sever embeds
that number of space characters at the end but not in varchar data type. For
example:
What will be
output of following sql query ?
DECLARE @First AS CHAR(10) = 'Exact'
DECLARE @Last AS CHAR(10) = ' Help'
IF (@First + @Last = 'Exact Help')
SELECT 'Equal'
ELSE
SELECT 'Not Equal'
Output: Not Equal
Explanation: In memory variable
@First has stored like 'Exact ' and
variable @Second ' Help '
So, @First + @Last = 'Exact Help
'
It is obvious that is not equal to
'Exact Help'
3. When ANSI_PADDING is off then a
column of any table of type CHAR NULL is same as VARCHAR NULL. For example:
Example 1:
SET ANSI_PADDING OFF
CREATE TABLE CharTest_1(
Value CHAR(10) NULL
)
INSERT INTO CharTest_1 VALUES ('Exact'),('Help')
SELECT DATALENGTH(Value) FROM
CharTest_1
Output:
5
4
Explanation: Here sql server is
treating CHAR(10) NULL as VARCHAR(10) NULL.
Example 2:
SET ANSI_PADDING ON
CREATE TABLE CharTest_1(
Value CHAR(10) NULL
)
INSERT INTO CharTest_1 VALUES ('Exact'),('Help')
SELECT DATALENGTH(Value) FROM
CharTest_1
Output:
10
10
Example 2:
SET ANSI_PADDING OFF
CREATE TABLE CharTest_2(
Value CHAR(10) NOT
NULL
)
INSERT INTO CharTest_2 VALUES ('Exact'),('Help')
SELECT DATALENGTH(Value) FROM
CharTest_2
Output:
10
10
4. VARCHAR keep
additional two bytes at the end to keep the length of the string while CHAR is
fixed length so it doesn't keep the length information.
5. When we perform update query on
CHAR column is doesn't move that row since data always fits in the allocated space
while when we update VARCHAR column then there is need to move the row.
Advantage
of using CHAR data type in sql server:
1. Query performance is better
since no need to move the column while updating.
2. No need to store the length of
string in last two bytes.
Disadvantage
of using CHAR data type in sql server:
1. It takes too much more spaces
than varchar since it is fixed length and we don't know the length of string
which to be store.
2. It is not good for compression operation
since it embeds space characters at the end.
Advantage
of using VARCHAR data type in sql server:
1. Since it is variable length so
it takes less memory spaces.
Disadvantage
of using VARCHAR data type in sql server:
1. Decrease the performance of some
sql queries.
So, we should use CHAR data type of
a column only if we known the length of the string is fixed otherwise we should
always use varchar.
How to perform case sensitive comparison in sql server
Sql server string comparison is case sensitive or not
Insert default value when data or parameter is null in sql server
SQL SERVER PPT
How to perform case sensitive comparison in sql server
Sql server string comparison is case sensitive or not
Insert default value when data or parameter is null in sql server
SQL SERVER PPT
No comments:
Post a Comment