This is a metadata change only: it is quick.

An observation: specify NULL or NOT NULL explicitly to avoid &quot;accidents&quot; if one of the SET ANSI_xx settings are different eg run in osql not SSMS for some reason

Just wanted to add my 2 cents, since I googled this question b/c I found myself in a similar situation...

**BE AWARE** that while changing from `varchar(xxx)` to `varchar(yyy)` is a meta-data change indeed, but changing to **`varchar(max)`** is not. Because `varchar(max)` values (aka BLOB values - image/text etc) are stored differently on the disk, not within a table row, but &quot;out of row&quot;. So the server will go nuts on a big table and become unresponsive for minutes (hours).

&lt;!-- language: lang-sql --&gt;

    --no downtime
    ALTER TABLE MyTable ALTER COLUMN [MyColumn] VARCHAR(1200)
    
    --huge downtime
    ALTER TABLE MyTable ALTER COLUMN [MyColumn] VARCHAR(max)

PS. same applies to `nvarchar` or course.

Changing to Varchar(1200) from Varchar(200) should cause you no issue as it is only a metadata change and as SQL server 2008 truncates excesive blank spaces you should see no performance differences either so in short there should be no issues with making the change.

Another reason why you should avoid converting the column to varchar(max) is because you cannot create an index on a varchar(max) column.

In my case alter column was not working so one can use &#39;Modify&#39; command, like:

alter table [table_name] MODIFY column [column_name] varchar(1200);

Is there a way to copy a `TreeSet`? That is, is it possible to go

    Set &lt;Item&gt; itemList;
    Set &lt;Item&gt; tempList;
    
    tempList = itemList;


or do you have to physically iterate through the sets and copy them one by one?

Copying sets Java

It would be helpful if someone can illustrate this with a simple example?

Also, where would it be useful to use `parent.frame()` instead of `parent.env()` and vice versa.



What is the difference between parent.frame() and parent.env() in R; how do they differ in call by reference?

I&#39;m using SQL Server 2008 and I need to make a VARCHAR field bigger, from (200 to 1200) on a table with about 500k rows.  What I need to know is if there are any issues I have not considered. 

I will be using this TSQL statement:

    ALTER TABLE MyTable
    ALTER COLUMN [MyColumn] VARCHAR(1200)

I&#39;ve already tried it on a copy of the data and this statement had no ill effects that I could see. 

So are there any possible problems from doing this that I may not have considered? 

By the way, the column is not indexed. 

When increasing the size of VARCHAR column on a large table could there be any problems?

I'm using SQL Server 2008 and I need to make a VARCHAR field bigger, from (200 to 1200) on a table with about 500k rows. What I need to know is if there are any issues I have not considered.
I will be using this TSQL statement:
<pre><code class="hljs language-sql">ALTER TABLE MyTable
ALTER COLUMN [MyColumn] VARCHAR(1200)
</code></pre>
I've already tried it on a copy of the data and this statement had no ill effects that I could see.
So are there any possible problems from doing this that I may not have considered?
By the way, the column is not indexed.

I&#39;m able to delete, insert and update in my program and I try to do an insert by calling a created stored procedure from my database.  

This button insert I made works well.

    private void btnAdd_Click(object sender, EventArgs e)
    {
            SqlConnection con = new SqlConnection(dc.Con);
            SqlCommand cmd = new SqlCommand(&quot;Command String&quot;, con);
            
            da.InsertCommand = new SqlCommand(&quot;INSERT INTO tblContacts VALUES (@FirstName, @LastName)&quot;, con);
            da.InsertCommand.Parameters.Add(&quot;@FirstName&quot;, SqlDbType.VarChar).Value = txtFirstName.Text;
            da.InsertCommand.Parameters.Add(&quot;@LastName&quot;, SqlDbType.VarChar).Value = txtLastName.Text;

            con.Open();
            da.InsertCommand.ExecuteNonQuery();
            con.Close();

            dt.Clear();
            da.Fill(dt);
        } 

This is the start of the button that calls the procedure named `sp_Add_contact` to add a contact. The two parameters for `sp_Add_contact(@FirstName,@LastName)`. I searched on google for some good examples but found nothing interesting.

    private void button1_Click(object sender, EventArgs e)
    {
            SqlConnection con = new SqlConnection(dc.Con);
            SqlCommand cmd = new SqlCommand(&quot;Command String&quot;, con);
            cmd.CommandType = CommandType.StoredProcedure;

            ???
            
            con.Open();
            da. ???.ExecuteNonQuery();
            con.Close();

            dt.Clear();
            da.Fill(dt);
        }


Call a stored procedure with parameter in c#

Please clarify two things for me:

1. Can a Foreign key be NULL?
2. Can a Foreign key be duplicate?

As fair as I know, `NULL` shouldn&#39;t be used in foreign keys, but in some application of mine I&#39;m able to input `NULL` in both Oracle and SQL Server, and I don&#39;t know why.

Can a foreign key be NULL and/or duplicate?

At any time in the past, if someone had asked me the maximum size for a `varchar(max)`, I&#39;d have said 2GB, or looked up a more exact [figure][1] (2^31-1, or 2147483647).

However, in some recent testing, I discovered that `varchar(max)` variables can apparently exceed this size:

    create table T (
    	Val1 varchar(max) not null
    )
    go
    declare @KMsg varchar(max) = REPLICATE(&#39;a&#39;,1024);
    declare @MMsg varchar(max) = REPLICATE(@KMsg,1024);
    declare @GMsg varchar(max) = REPLICATE(@MMsg,1024);
    declare @GGMMsg varchar(max) = @GMsg + @GMsg + @MMsg;
    select LEN(@GGMMsg)
    insert into T(Val1) select @GGMMsg
    select LEN(Val1) from T

Results:

    (no column name)
    2148532224
    (1 row(s) affected)
    Msg 7119, Level 16, State 1, Line 6
    Attempting to grow LOB beyond maximum allowed size of 2147483647 bytes.
    The statement has been terminated.
    
    (no column name)
    (0 row(s) affected)

So, given that I now know that a *variable* can exceed the 2GB barrier - does anyone know what the actual limit is for a `varchar(max)` variable?

---

(Above test completed on SQL Server 2008 (not R2). I&#39;d be interested to know whether it applies to other versions)

  [1]: http://msdn.microsoft.com/en-us/library/ms176089.aspx


Maximum size of a varchar(max) variable

I&#39;m trying to create upgrade and backout scripts in SQL.  The upgrade script adds a column like so:

    IF NOT EXISTS (SELECT * FROM sys.columns WHERE Name = N&#39;ColumnName&#39; 
        AND object_id = OBJECT_ID(N&#39;[dbo].[TableName]&#39;))

    ALTER TABLE TableName
        ADD ColumnName bit NOT NULL DEFAULT(0) 

The backout script removes the column like so:

    IF EXISTS (SELECT * FROM sys.columns WHERE Name = N&#39;ColumnName&#39; 
        AND object_id = OBJECT_ID(N&#39;[dbo].[TableName]&#39;))

    ALTER TABLE TableName
        DROP COLUMN ColumnName

However, the backout script throws this error:

    Msg 5074, Level 16, State 1, Line 5
        The object &#39;DF__TableName__ColumnName__1BF3D5BD&#39; is dependent on column &#39;ColumnName&#39;.
    Msg 4922, Level 16, State 9, Line 5
        ALTER TABLE DROP COLUMN ColumnName failed because one or more objects access this column.

I know how to drop the constraint, but the constraint&#39;s name changes everytime (the suffix changes).  I either need SQL Server to stop creating this randomly-named constraint OR I need to be able to remove the constraint in my script using wild-card characters, since the name changes.

Why does SQL Server keep creating a DF constraint?

How does one cause a delay in execution for a specified number of seconds?

This doesn&#39;t do it:

    WAITFOR DELAY &#39;00:02&#39;;

What is the correct format?


How to wait for 2 seconds?

I need to create a INSERT script in order to insert in another database the same data.&lt;br/&gt; If in SQL Server I select &quot;Script table as &gt; INSERT To&quot; I can easily recreate the skeleton for the INSERT statement. However since I have several records to migrate, I would prefer to avoid having to insert the values manually.

**Therefore is there any way to &quot;automatically&quot; get the INSERT script with also the values (coming from the target table) filled in?** 

I know this could be done with SSIS, but I am wondering whether it would be possible as well with a quicker solution.

Create SQL INSERT Script with values gathered from table

My lower level knowledge of SQL (Server 2008) is limited, and is now being challanged by our DBAs. Let me explain (I have mentioned obvious statements in the hope that I am right, but if you see something wrong, please tell me) the scenario:

We have a table which holds &#39;Court Orders&#39; for people. When I created the table, (Name: CourtOrder), I created it like:

    CREATE TABLE dbo.CourtOrder
    (
      CourtOrderID INT NOT NULL IDENTITY(1,1), (Primary Key)
      PersonId INT NOT NULL,
      + around 20 other fields of different types.
    )

I then applied a non-clustered index to the primary key (for efficiency). My reasons is that it is a unique field (primary key), and should be indexed, mainly for selection purposes, as we often `Select from table where primary key = ...`

I then applied a CLUSTERED index on PersonId. The reason was to group orders for a particular person physically, as the vast majority of work is getting orders for a person. So, `select from mytable where personId = ...`

I have been pulled up on this now. I have been told that we should put the clustered index on the primary key, and the normal index on the personId. That seems very strange to me. First off, why would you put a clustered index on a unique column? what is it clustering? Surely that&#39;s a waste of the clustered index? I&#39;d have believed a normal index would be used on a unique column. Also, clustering the index would mean we can&#39;t cluster a different column (One per table, right?).

The reasoning for me being told I have made a mistake is that they believe putting a clustered index on the PersonId would make inserts slow. For the 5% gain in speed of a select, we would be getting a 95% degradation in speed on inserts and updates. Is that correct and valid?

They say that because we cluster the personId, SQL Server has to rearrange data when ever we insert or make a change to the PersonId.

So then I have asked, why would SQL have the concept of a CLUSTERED INDEX, if it&#39;s so slow? Is it as slow as they&#39;re saying? How should I have setup my indexes to achieve optimum performance? I&#39;d have thought SELECT is used more than INSERT... but they say that we&#39;re having locking issues on INSERTS...

Hope someone can help me.

Clustered vs Non-Clustered

It would be nice to know how to create a trigger that auto-updates the `modifiedDate` column in my SQL Server table:

Table **TimeEntry**
    
    Id (PK)
    UserId (FK)
    Description
    Time
    GenDate
    ModDate
    
Trigger code:

    +   TR_TimeEntry_UpdateModDate()
    +   TR_TimeEntry_InsertGenDate()

An example for update `ModDate` would be nice.


How to: Create trigger for auto update modified date with SQL Server 2008

I have a question about the `ALTER TABLE` command on a really large table (almost 30 millions rows).
One of its columns is a `varchar(255)` and I would like to resize it to a `varchar(40)`.
Basically, I would like to change my column by running the following command:

```sql
ALTER TABLE mytable ALTER COLUMN mycolumn TYPE varchar(40);
```

I have no problem if the process is very long but it seems my table is no more readable during the `ALTER TABLE` command.
Is there a smarter way? Maybe add a new column, copy values from the old column, drop the old column and finally rename the new one?

**Note:** I use PostgreSQL 9.0.

Postgresql - change the size of a varchar column to lower length

I&#39;m trying to update the length of a varchar column from 255 characters to 500 without losing the contents. I&#39;ve dropped and re-created tables before but I&#39;ve never been exposed to the alter statement which is what I believe I need to use to do this. I found the documentation here: [ALTER TABLE (Transfact-SQL)][1] however I can&#39;t make heads or tails of it.

I have the following so far (essentially nothing unfortunately):

    alter table [progennet_dev].PROGEN.LE
  	alter column UR_VALUE_3

How do I approach this? Is there better documentation for this statement out there (I did some searches for an example statement but came up empty)?


  [1]: http://msdn.microsoft.com/en-us/library/ms190273.aspx

Changing the maximum length of a varchar column?

I have to get last 5 numbers using mysql.

My values are like `YOT-A78514`,`LOP-C4521` ...

I have to get only last five char . How can I do this in query?

Getting last 5 char of string with mysql query

I have this date format: 2011-09-28 18:01:00 (in varchar), and I want to convert it to datetime changing to this format 28-09-2011 18:01:00. How can I do it?

SQL Server Convert Varchar to Datetime

I would like to know what the max size is for a MySQL VARCHAR type. 

I read that the max size is limited by the row size which is about 65k. I tried setting the field to `varchar(20000)` but it says that that&#39;s too large. 

I could set it to `varchar(10000)`.  What is the exact max I can set it to?

What is the MySQL VARCHAR max size?

I am trying to alter column datatype of a primary key to tinyint from int.This column is a foreign key in other tables.So,I get the following error:


----------

&gt; Msg 5074, Level 16, State 1, Line 1 The object &#39;PK_User_tbl&#39; is
&gt; dependent on column &#39;appId&#39;. Msg 5074, Level 16, State 1, Line 1 The
&gt; object &#39;FK_Details_tbl_User_tbl&#39; is dependent on column &#39;appId&#39;. Msg
&gt; 5074, Level 16, State 1, Line 1 The object &#39;FK_Log_tbl_User_tbl&#39; is
&gt; dependent on column &#39;appId&#39;. Msg 4922, Level 16, State 9, Line 1 ALTER
&gt; TABLE ALTER COLUMN appId failed because one or more objects access
&gt; this column.


----------
Is there any other way other than to delete dependencies and recreate them?

Content Type	Original Author	Original Content on Stackoverflow
Question	Paul T Davies	View Question on Stackoverflow
Solution 1 - Sql Server	gbn	View Answer on Stackoverflow
Solution 2 - Sql Server	jazzcat	View Answer on Stackoverflow
Solution 3 - Sql Server	Kprice84	View Answer on Stackoverflow
Solution 4 - Sql Server	psikorski	View Answer on Stackoverflow
Solution 5 - Sql Server	Gourav Singla	View Answer on Stackoverflow

When increasing the size of VARCHAR column on a large table could there be any problems?

Sql Server Problem Overview

Sql Server Solutions

Solution 1 - Sql Server

Solution 2 - Sql Server

Solution 3 - Sql Server

Solution 4 - Sql Server

Solution 5 - Sql Server

What is the difference between parent.frame() and parent.env() in R; how do they differ in call by reference?

Copying sets Java

Attributions