The &quot;length&quot; of the VARCHAR is not the length of the contents, it is the maximum length of the contents.

The max length of a VARCHAR is not dynamic, it is fixed and therefore has to be specified.

If you don&#39;t want to define a maximum size for it then use VARCHAR(MAX).


First off, it does not needed it in all databases. Look at [SQL Server][1], where it is optional.

Regardless, it defines a maximum size for the content of the field. Not a bad thing in itself, and it conveys meaning (for example - phone numbers, where you do not want international numbers in the field).


  [1]: http://msdn.microsoft.com/en-us/library/ms176089.aspx

You can see it as a constraint on your data. It ensures that you don&#39;t store data that violates your constraint. It is conceptionally similar to e.g. a check constraint on a integer column that ensure that only positive values are entered. 

The more the database knows about the data it is storing, the more optimisations it can make when searching/adding/updating data with requests.

The answer is you *don&#39;t* need to, it&#39;s optional.

It&#39;s there if you want to ensure that strings do not exceed a certain length.

From [Wikipedia][1]:

&gt; Varchar fields can be of any size up
&gt; to the limit. The limit differs from
&gt; types of databases, an Oracle 9i
&gt; Database has a limit of 4000 bytes, a
&gt; MySQL Database has a limit of 65,535
&gt; bytes (for the entire row) and
&gt; Microsoft SQL Server 2005 8000 bytes
&gt; (unless varchar(max) is used, which
&gt; has a maximum storage capacity of
&gt; 2,147,483,648 bytes).


  [1]: http://en.wikipedia.org/wiki/Varchar

The most dangerous thing for programmers, as @DimaFomin pointed out in comments, is the **default length** enforced, if there is no length specified.

How SQL Server enforces the default length:

    declare @v varchar = &#39;123&#39;
    select @v

result:

    1

There is an almost identical, but not really answered question [here][1].

I am migrating an application from MS SQL Server to PostgreSQL. In many places in code I use local variables so I would like to go for the change that requires less work, so could you please tell me which is the best way to translate the following code?

    -- MS SQL Syntax: declare 2 variables, assign value and return the sum of the two 
    declare @One integer = 1
    declare @Two integer = 2
    select @One + @Two as SUM

this returns:

    SUM
    -----------
    3
    
    (1 row(s) affected)

I will use Postgresql 8.4 or even 9.0 if it contains significant fetaures that will simplify the translation.

  [1]: https://stackoverflow.com/questions/1490942/how-to-declare-a-variable-in-a-postgresql-query


How to declare local variables in postgresql?

I need to calculate the standard deviation of a generic list. I will try to include my code. Its a generic list with data in it. The data is mostly floats and ints. Here is my code that is relative to it without getting into to much detail: 

    namespace ValveTesterInterface
    {
        public class ValveDataResults
        {
            private List&lt;ValveData&gt; m_ValveResults;
    
            public ValveDataResults()
            {
                if (m_ValveResults == null)
                {
                    m_ValveResults = new List&lt;ValveData&gt;();
                }
            }
    
            public void AddValveData(ValveData valve)
            {
                m_ValveResults.Add(valve);
            }

**Here is the function where the standard deviation needs to be calculated:**

            public float LatchStdev()
            {
                
                float sumOfSqrs = 0;
                float meanValue = 0;
                foreach (ValveData value in m_ValveResults)
                {
                    meanValue += value.LatchTime;
                }
                meanValue = (meanValue / m_ValveResults.Count) * 0.02f;
                
                for (int i = 0; i &lt;= m_ValveResults.Count; i++) 
                {   
                    sumOfSqrs += Math.Pow((m_ValveResults - meanValue), 2);  
                }
                return Math.Sqrt(sumOfSqrs /(m_ValveResults.Count - 1));
                
            }
        }
    }

Ignore whats inside the LatchStdev() function because I&#39;m sure its not right. Its just my poor attempt to calculate the st dev. I know how to do it of a list of doubles, however not of a list of generic data list. If someone had experience in this, please help. 



Standard deviation of generic list?

Why do we always need to specify `VARCHAR(length)` instead of just `VARCHAR`? It is dynamic anyway.

UPD: I&#39;m puzzled specifically by the fact that it is mandatory (e.g. in MySQL).

Why does VARCHAR need length specification?

<p>Why do we always need to specify <code>VARCHAR(length)</code> instead of just <code>VARCHAR</code>? It is dynamic anyway.</p>
<p>UPD: I'm puzzled specifically by the fact that it is mandatory (e.g. in MySQL).</p>


The team I&#39;m working with decided to create a table with a varchar primary key. This table is referenced by another table on this primary key.

I&#39;ve the habit to create an integer primary key, following what I learnt at university. I&#39;ve read that there is a performance boost using integer primary key.

The matter is that I don&#39;t know any other reason for creating an integer primary key. Do you have any tips?

SQL primary key: integer vs varchar

In SQL, you can use the following syntax:

    SELECT *
    FROM MY_TABLE
    WHERE VALUE_1 IN (1, 2, 3)

Is there an equivalent in C#?  The IDE seems to recognise &quot;in&quot; as a keyword, but I don&#39;t seem to be able to find any information on it.

So, is it possible to do something like the following:

    int myValue = 1;
    if (myValue in (1, 2, 3))
        // Do something
    
Instead of


    int myValue = 1;
    if (myValue == 1 || myValue == 2 || myValue == 3)
        // Do something


Is there a C# IN operator?

If I just need 2/3 columns and I query `SELECT *` instead of providing those columns in select query, is there any performance degradation regarding more/less I/O or memory?

The network overhead might be present if I do select * without a need.

But in a select operation, does the database engine always pull atomic tuple from the disk, or does it pull only those columns requested in the select operation?

If it always pulls a tuple then I/O overhead is the same.

At the same time, there might be a memory consumption for stripping out the requested columns from the tuple, if it pulls a tuple.

So if that&#39;s the case, select someColumn will have more memory overhead than that of select *

select * vs select column

What is the difference in results between:
 
1. RIGHT JOIN and RIGHT OUTER JOIN 
2. LEFT JOIN and LEFT OUTER JOIN ?

Can you please explain it through some examples?


Difference between RIGHT &amp; LEFT JOIN vs RIGHT &amp; LEFT OUTER JOIN in SQL

I came across an old table today with a datetime column called &#39;Created&#39; which allows nulls.  Now, I&#39;d want to change this so that it is NOT NULL, and also include a constraint to add in a default value (getdate()).

So far I&#39;ve got the following script, which works fine provided that i&#39;ve cleaned up all the nulls beforehand:

    ALTER TABLE dbo.MyTable ALTER COLUMN Created DATETIME NOT NULL 

Is there any way to also specify the default value as well on the ALTER statement?

Change a Nullable column to NOT NULL with Default Value

I am getting the following error while trying to add a NOT NULL column to an existing table. Why is it happening ?. I tried rake db:reset thinking that the existing records are the problem, but even after resetting the DB, the problem persists. Can you please help me figure this out.

&lt;!-- language-all: lang-ruby --&gt;

 **Migration File**

    class AddDivisionIdToProfile &lt; ActiveRecord::Migration
      def self.up
        add_column :profiles, :division_id, :integer, :null =&gt; false
      end
    
      def self.down
        remove_column :profiles, :division_id
      end
    end

**Error Message**

&gt; SQLite3::SQLException: Cannot add a NOT NULL column with default value NULL: ALTER TABLE &quot;profiles&quot; ADD &quot;division_id&quot; integer NOT NULL



Cannot add a NOT NULL column with default value NULL in Sqlite3

I have made some repetitive operations in my application (testing it), and suddenly I’m getting a weird error: 

    OperationalError: database is locked

I&#39;ve restarted the server, but the error persists. What can it be all about?


OperationalError: database is locked

I&#39;ve noticed that a lot of folks here cite tables with 20+ (I&#39;ve seen as much as 55) columns in one table.  Now I don&#39;t pretend to be a database design expert, but I&#39;ve always heard that this is a horrible practice.  When I see this, I usually suggest splitting into two tables with a one to one relationship: one containing the most frequently used data, the other with the least often used data. Though at the same time, there&#39;s the possible issue of performance (less JOINs and such).  So my question is this:

When it comes to really LARGE scale databases, is there actually an advantage to having a large amount of columns, despite the fact that this usually leads to many NULL values?

Which is more of a performance hit:  lots of columns with lots of NULLs, or fewer columns with lots of JOINs?

How many columns is too many columns?

In my MySQL InnoDB database, I have dirty zip code data that I want to clean up.

The clean zip code data is when I have all 5 digits for a zip code (e.g. &quot;90210&quot;).

But for some reason, I noticed in my database that for zipcodes that start with a &quot;0&quot;, the 0 has been dropped.

So &quot;**Holtsville, New York**&quot; with zipcode &quot;`00544`&quot; is stored in my database as &quot;`544`&quot;

and

&quot;**Dedham, MA**&quot; with zipcode &quot;`02026`&quot; is stored in my database as &quot;`2026`&quot;.

What SQL can I run to front pad &quot;0&quot; to any zipcode that is not 5 digits in length? Meaning, if the zipcode is 3 digits in length, front pad &quot;00&quot;. If the zipcode is 4 digits in length, front pad just &quot;0&quot;.

**UPDATE**:

I just changed the zipcode to be datatype VARCHAR(5)

MySQL - how to front pad zip code with &quot;0&quot;?

Where can I find historical raw weather data for a project I am doing with focus on the USA and Canada.  I need temperatures mainly, but other details would be nice. I am having a very hard time finding this data.  I really dont want to have to scrape a weather site.  

Where can I find historical raw weather data?

What&#39;s the difference between the `text` data type and the `character varying` (`varchar`) data types?

According to [the documentation][1]

&gt; If character varying is used without length specifier, the type accepts strings of any size. The latter is a PostgreSQL extension.

and

&gt; In addition, PostgreSQL provides the text type, which stores strings of any length. Although the type text is not in the SQL standard, several other SQL database management systems have it as well.

So what&#39;s the difference?

  [1]: http://www.postgresql.org/docs/8.0/interactive/datatype-character.html

Difference between text and varchar (character varying)

Suppose that I have a SQL table that has a varchar[1000] field called &quot;Remarks&quot;.

I would like to craft a single SQL statement, which when executed, will return 1000, or whatever the size of the varchar field might be changed to in the future.

Something like `SELECT size(Remarks) FROM mytable`.

How do I do this?


How to get the size of a varchar[n] field in one SQL statement?

I&#39;m trying to populate a SQL table with a list of words. The table itself it pretty simple:


    CREATE TABLE WORDS(
      ID BIGINT AUTO_INCREMENT, 
      WORD VARCHAR(128) NOT NULL UNIQUE, 
      PRIMARY KEY(ID)
    );

The problem I&#39;m running into is this: when I do the following inserts back to back

    INSERT INTO WORDS(WORD) VALUES(&#39;Seth&#39;);
    INSERT INTO WORDS(WORD) VALUES(&#39;seth&#39;);

The second insert fails with a constraint violation (&quot;Duplicate entry &#39;seth&#39; for key &#39;WORD&#39;&quot;).

How can I get the `UNIQUE` constraint on `WORD` to be case sensitive?

SQL unique varchar case sensitivity question

If I have a VARCHAR of 200 characters and that I put a string of 100 characters, will it use 200 bytes or it will just use the actual size of the string?

MySQL VARCHAR size?

I need to update a field in my production db from INT to VARCHAR. 

The information in the current INT field is sensitive and I want to ensure that I don&#39;t alter it inadvertently or destroy it while changing the table type. For example, a user may have INT 123456 stored in this column and I want to ensure that is accurately stored once converted to VARCHAR.

What is the recommended process for accomplishing this? 

Is there anything I need to worry about when using something like this?

    ALTER TABLE table_sample CHANGE col_sample col_sample VARCHAR;


Thanks in advance for any help. 

Content Type	Original Author	Original Content on Stackoverflow
Question	Fixpoint	View Question on Stackoverflow
Solution 1 - Sql	Robin Day	View Answer on Stackoverflow
Solution 2 - Sql	Oded	View Answer on Stackoverflow
Solution 3 - Sql	a_horse_with_no_name	View Answer on Stackoverflow
Solution 4 - Sql	Tom Gullen	View Answer on Stackoverflow
Solution 5 - Sql	NibblyPig	View Answer on Stackoverflow
Solution 6 - Sql	Luca Matteis	View Answer on Stackoverflow
Solution 7 - Sql	themefield	View Answer on Stackoverflow

Why does VARCHAR need length specification?

Sql Problem Overview

Sql Solutions

Solution 1 - Sql

Solution 2 - Sql

Solution 3 - Sql

Solution 4 - Sql

Solution 5 - Sql

Solution 6 - Sql

Solution 7 - Sql

Standard deviation of generic list?

How to declare local variables in postgresql?

Attributions