SQL: Usage of char, varchar and varchar(MAX)

SQL-Tables
SQL-Tables

SQL: Usage of char, varchar and varchar(MAX)

Welcome to CodeSpread!

These are character data types in SQL. We will take an example and describe each type. Later we determine the cases where one of them can be applied.

Create Table testable(
Name char(20),
Email varchar(250),
Address varchar(Max))

CHAR

We usually call it char or character. In the above example, Name is declared with char(20) as type. Here, 20 is the storage size and it does not depend on the actual length of name entered, whether is below 20 or above 20.
It is always fixed length and can have value from from 1 through 8,000. The default length is 1 and can be mentioned as in below example.

DECLARE @testvariable AS char

VARCHAR

We usually call it varchar or varying character. In the above example, Email is declared with varchar(250) as type. Here, 250 is the maximum storage size. The storage size is flexible and dependent on the actual length of data entered so finally storage size is computed as actual length + 2 bytes.
It is always variable length and can have value from 1 through 8,000. Plus point is that the data entered can be 0 characters in length.

VARCHAR(MAX)

The only difference is the max storage limit which in this case is 2^31-1 bytes.

Suggestions:

  • When we are considerably sure about the length of the data in column, we should use char. The main concern is consistency about the column entries.
  • Varchar is exact reverse condition of char. If there is a lack of consistency in column entries or length of the column data vary considerably, use varchar.
  • Varchar(max) is a superlative form and used when the size might exceed 8,000 bytes.

Conclusion:

In the real world, we are more concerned about the storage space and above points, though miniscule, can really help us

Author: sush

Share This Post On

0 Comments

  1. nice superb explaination

    Post a Reply
  2. thanks for the tutorial
    I am having a problem.
    The feature “Create SQL server database” isn’t available , I can’t select it.
    Can you help, please ?

    Post a Reply

Submit a Comment

Your email address will not be published. Required fields are marked *

More from CodeSpread:

  • SQL fry: PIVOT and UNPIVOTSQL fry: PIVOT and UNPIVOTWhat is PIVOT? In our day-to-day SQL server coding, PIVOT and UNPIVOT are not very familiar words but still holds good if we understand them correctly and know when to use it.Web Definition of PIV...
  • SQL:Target a TriggerSQL:Target a TriggerI remember those days when I used to simply fall into the trap set by client requirements which could have been easily resolved by using a trigger. So I can definitely say, trigger is a life saver,...
  • IDENTITY as SQL Property and IDENTITY as SQL FunctionIDENTITY as SQL Property and IDENTITY as SQL FunctionIDENTITY as SQL Property: Almost all the time, We have a table with a primary key and would like to insert a new value automatically in the primary key field. To achieve this, We have IDENTITY ...
  • Styles of Source Code Comments in C#, SQL, XML, HTML, CSS, JavaScriptStyles of Source Code Comments in C#, SQL, XML, HTML, CSS, JavaScriptDevelopers take months to write beautiful pieces of code and reviewer gets only few hours or I say, minutes to provide suggestions and improvisations. Then it becomes a hard deal for a developer to...
  • SQL Bitwise operator can simplify DB designSQL Bitwise operator can simplify DB designFrankly speaking, I never tried SQL Bitwise operator but while doing DB design today for one of the projects, I came to know about this operator from one of my friend and I really liked it. To expl...
  • SQL : Clustered and NonClustered IndexSQL : Clustered and NonClustered IndexWe read about SQL indexes in our previous post, We will dive into the topic and learn the different types of indexing possible in SQL. We have clustered and non-clustered index in SQL, let’s see...
  • VB.Net: Download file from FTP and UnZipVB.Net: Download file from FTP and UnZipExperience: Many application requires data migration tasks to execute on daily basis. To achieve this we generally seek help of SSIS package, if at all we are making use of SQL sever. ...
  • IQueryable: Performance boost over remote Data AccessIQueryable: Performance boost over remote Data AccessIf we also follow the inheritance principle like OOPS programming, we need to extend our previous discussion “IEnumerable: Power behind Collections”. So this will lead us to write something about I...