Syntax:
ALTER TABLE {TABLENAME}
ADD {COLUMNNAME} {TYPE} {NULL|NOT NULL}
CONSTRAINT {CONSTRAINT_NAME} DEFAULT {DEFAULT_VALUE}
WITH VALUES
Example:
ALTER TABLE SomeTable
ADD SomeCol Bit NULL --Or NOT NULL.
CONSTRAINT D_SomeTable_SomeCol --When Omitted a Default-Constraint Name is autogenerated.
DEFAULT (0)--Optional Default-Constraint.
WITH VALUES --Add if Column is Nullable and you want the Default Value for Existing Records.
Notes:
Optional Constraint Name:
If you leave out CONSTRAINT D_SomeTable_SomeCol
then SQL Server will autogenerate
a Default-Contraint with a funny Name like: DF__SomeTa__SomeC__4FB7FEF6
Optional With-Values Statement:
The WITH VALUES
is only needed when your Column is Nullable
and you want the Default Value used for Existing Records.
If your Column is NOT NULL
, then it will automatically use the Default Value
for all Existing Records, whether you specify WITH VALUES
or not.
How Inserts work with a Default-Constraint:
If you insert a Record into SomeTable
and do not Specify SomeCol
's value, then it will Default to 0
.
If you insert a Record and Specify SomeCol
's value as NULL
(and your column allows nulls),
then the Default-Constraint will not be used and NULL
will be inserted as the Value.
Notes were based on everyone's great feedback below.
Special Thanks to:
@Yatrix, @WalterStabosz, @YahooSerious, and @StackMan for their Comments.
In Java 1.7 or later, the standard way to do this is as follows:
import java.util.concurrent.ThreadLocalRandom;
// nextInt is normally exclusive of the top value,
// so add 1 to make it inclusive
int randomNum = ThreadLocalRandom.current().nextInt(min, max + 1);
See the relevant JavaDoc. This approach has the advantage of not needing to explicitly initialize a java.util.Random instance, which can be a source of confusion and error if used inappropriately.
However, conversely there is no way to explicitly set the seed so it can be difficult to reproduce results in situations where that is useful such as testing or saving game states or similar. In those situations, the pre-Java 1.7 technique shown below can be used.
Before Java 1.7, the standard way to do this is as follows:
import java.util.Random;
/**
* Returns a pseudo-random number between min and max, inclusive.
* The difference between min and max can be at most
* <code>Integer.MAX_VALUE - 1</code>.
*
* @param min Minimum value
* @param max Maximum value. Must be greater than min.
* @return Integer between min and max, inclusive.
* @see java.util.Random#nextInt(int)
*/
public static int randInt(int min, int max) {
// NOTE: This will (intentionally) not run as written so that folks
// copy-pasting have to think about how to initialize their
// Random instance. Initialization of the Random instance is outside
// the main scope of the question, but some decent options are to have
// a field that is initialized once and then re-used as needed or to
// use ThreadLocalRandom (if using at least Java 1.7).
//
// In particular, do NOT do 'Random rand = new Random()' here or you
// will get not very good / not very random results.
Random rand;
// nextInt is normally exclusive of the top value,
// so add 1 to make it inclusive
int randomNum = rand.nextInt((max - min) + 1) + min;
return randomNum;
}
See the relevant JavaDoc. In practice, the java.util.Random class is often preferable to java.lang.Math.random().
In particular, there is no need to reinvent the random integer generation wheel when there is a straightforward API within the standard library to accomplish the task.
Best Answer
See this post: SQL to Select a random row from a database table. It goes through methods for doing this in MySQL, PostgreSQL, Microsoft SQL Server, IBM DB2 and Oracle (the following is copied from that link):
Select a random row with MySQL:
Select a random row with PostgreSQL:
Select a random row with Microsoft SQL Server:
Select a random row with IBM DB2
Select a random record with Oracle: