Difference between Sql Server Char and Varchar Data Type

Everyone knows about the basic difference between CHAR and VARCHAR data types. In this article apart from the basic difference, will discuss on one more interesting difference.
CHAR Data Type is a Fixed Length Data Type. For example if you declare a variable/column of CHAR (10) data type, then it will always take 10 bytes irrespective of whether you are storing 1 character or 10 character in this variable or column. And in this example as we have declared this variable/column as CHAR(10), so we can store max 10 characters in this column.
On the other hand VARCHAR is a variable length Data Type. For example if you declare a variable/column of VARCHAR (10) data type, it will take the number of bytes equal to the number of characters stored in this column. So, in this variable/column if you are storing only one character then it will take only one byte and if we are storing 10 characters then it will take 10 bytes. And in this example as we have declared this variable/column as VARCHAR (10), so we can store max 10 characters in this column.
Below example illustrates the basic difference explained above:

DECLARE
@CharName Char(20) = 'Biswajeet',
@VarCharName VarChar(20) = 'Biswajeet';
SELECT DATALENGTH(@CharName) CharSpaceUsed,
DATALENGTH(@VarCharName) VarCharSpaceUsed;

Result:
CharSpaceUsed    VarCharSpaceUsed
————-                —————-
20                            9
(1 row(s) affected)

Below is an interesting difference, which I have observed recently while writing some script. Concatenation of CHAR variables:

DECLARE
@FirstName Char(20) = 'Biswajeet',
@LastName Char(20) = 'Samal';
IF (@FirstName + ' ' + @LastName = 'Biswajeet Samal')
   PRINT 'I was Expecting'
ELSE
   PRINT 'Its different'
SELECT @FirstName + ' ' + @LastName AS Name,
LEN(@FirstName + ' ' + @LastName) AS Length

Result:
Its different
Name                           Length
————————–     ———
Biswajeet Samal        26
(1 row(s) affected)

Concatenation of VARCHAR variables:

DECLARE
@FirstName VarChar(20) = 'Biswajeet',
@LastName VarChar(20) = 'Samal';
IF (@FirstName + ' ' + @LastName = 'Biswajeet Samal')
  PRINT 'I was Expecting'
ELSE
  PRINT 'Its nice'
SELECT @FirstName + ' ' + @LastName AS Name,
LEN(@FirstName + ' ' + @LastName) AS Length;

Result:
I was Expecting
Name                                              Length
—————————————–      ———–
Biswajeet Samal                           17
(1 row(s) affected)

So, it is clear from the above examples that during concatenation of CHAR data type variables, it includes space in-place of unused space in the result of concatenation.

How To: Reset Identity column in SQL Server

This is one of those simple tip posts that may seem obvious and taken for granted by those of us who have been working with MS SQL Server for a while now but maybe a newbie or two out there will find this helpful.

I find myself resetting an identity column value back to 0 after I’ve deleted all the existing records so the table gets a fresh start at primary key 1. Yes, I know all about primary keys not changing and how the value in the primary key doesn’t matter and so on. Sometimes I just like the primary keys starting at 1.

The following line resets the Identity value for the Customer table to 0 so that the next record added starts at 1.

DBCC CHECKIDENT(table_name, RESEED, new_reseed_value)

Example:

DBCC CHECKIDENT('Customer', RESEED, 0)

How to hide drives in Windows

download

  • Go to run
  • Type “diskpart”.
  • Type “list volume”.
  • Now select the volume you want to hide by typing “select volume n” (n is the volume number against the drive you want to hide). eg: select volume 2.
  • Type “remove letter x”(x is the drive name). eg: remove letter D.
  • Done, now check my computer.
  • If you want to show the drive again then repeat step 1 to 4 then type “assign letter x”.

Note: Don’t try with drive C. If you have locked something with Folder lock or other software then after assigning the drive everything will be unlocked.

How to show more records in related list in Salesforce?

When viewing a record detail page (e.g. Contact detail) the related lists (e.g. Cases related list) shows a limited number of related records at a time.

download (1)

So, if you have more than 5 (by default) Cases tagged to an Contact, in the Contact detail page, you will see Show m more » | Go to list (n+) ». By default a will be 5 and b is total number of the records.

You can scroll down to the bottom of page, and look for Always show me more records per related list or Always show me fewer / more records per related list if you have click more before (even in previous login).
Always show me more records:

download (2)

Always show me fewer / more records:

download (3)

Clicking more or fewer increases and decreases the default number 5 (by default) of related list records displayed for all object record detail pages for the logged in user. If you notice, when you click more or fewer link, in the URL, Salesforce will add parameters rowsperlist=m (e.g. https://ap1.salesforce.com/0039000000ojedc?rowsperlist=10), this parameter will show related list up to 10 records for all related list.
Note: This change will be permanent for the logged in user for all tab, and can increase the related list records up-to 100.

Multiple email addresses for one gmail account

Let’s say that I have a gmail address: itzbiswajeet@gmail.com.

Now if I am signing up for a newsletter at ABC Corp. I can register with the email address itzbiswajeet+abc@gmail.com. You can put any extra alphanumeric character after the plus sign and it still gets delivered to itzbiswajeet@gmail.com.

One benefit of this feature is to track where emails are coming from.