How can I create a CHECK constraint on a VARCHAR column in SQL Server specifying a minimum data length?
use:
ALTER TABLE [dbo].[YOUR_TABLE]
ADD CONSTRAINT [MinLengthConstraint] CHECK (DATALENGTH([your_column]) > 2)
Reference:
- DATALENGTH vs LEN
- SQL SERVER – 2005 Constraint on VARCHAR(MAX) Field To Limit It Certain Length
How can I create a CHECK constraint on a VARCHAR column in MSSQL specifying a valid set of characters that may appear in the data?
create table t (
a varchar(30) check (
a like replicate('[a-zA-Z\_-]', len(a)) escape '\'));
If your collation is not case sensitive then you don't need both [a-z]
and [A-Z]
.
Minimum length constraint on a column
DATALENGTH()
returns the length in bytes in SQL Server. The equivalent Oracle function is LENGTHB()
(documented here):
ALTER TABLE my_table
ADD CONSTRAINT MY_TABLE_PASSWORD_CK CHECK (LENGTHB(password) >= 4)
However, for your purposes, I think the string length would be appropriate in both databases, LENGTH()
in Oracle (or LEN()
in SQL Server).
Best practices for SQL varchar column length
No DBMS I know of has any "optimization" that will make a VARCHAR
with a 2^n
length perform better than one with a max
length that is not a power of 2.
I think early SQL Server versions actually treated a VARCHAR
with length 255 differently than one with a higher maximum length. I don't know if this is still the case.
For almost all DBMS, the actual storage that is required is only determined by the number of characters you put into it, not the max
length you define. So from a storage point of view (and most probably a performance one as well), it does not make any difference whether you declare a column as VARCHAR(100)
or VARCHAR(500)
.
You should see the max
length provided for a VARCHAR
column as a kind of constraint (or business rule) rather than a technical/physical thing.
For PostgreSQL the best setup is to use text
without a length restriction and a CHECK CONSTRAINT
that limits the number of characters to whatever your business requires.
If that requirement changes, altering the check constraint is much faster than altering the table (because the table does not need to be re-written)
The same can be applied for Oracle and others - in Oracle it would be VARCHAR(4000)
instead of text
though.
I don't know if there is a physical storage difference between VARCHAR(max)
and e.g. VARCHAR(500)
in SQL Server. But apparently there is a performance impact when using varchar(max)
as compared to varchar(8000)
.
See this link (posted by Erwin Brandstetter as a comment)
Edit 2013-09-22
Regarding bigown's comment:
In Postgres versions before 9.2 (which was not available when I wrote the initial answer) a change to the column definition did rewrite the whole table, see e.g. here. Since 9.2 this is no longer the case and a quick test confirmed that increasing the column size for a table with 1.2 million rows indeed only took 0.5 seconds.
For Oracle this seems to be true as well, judging by the time it takes to alter a big table's varchar
column. But I could not find any reference for that.
For MySQL the manual says "In most cases, ALTER TABLE
makes a temporary copy of the original table". And my own tests confirm that: running an ALTER TABLE
on a table with 1.2 million rows (the same as in my test with Postgres) to increase the size of a column took 1.5 minutes. In MySQL however you can not use the "workaround" to use a check constraint to limit the number of characters in a column.
For SQL Server I could not find a clear statement on this but the execution time to increase the size of a varchar
column (again the 1.2 million rows table from above) indicates that no rewrite takes place.
Edit 2017-01-24
Seems I was (at least partially) wrong about SQL Server. See this answer from Aaron Bertrand that shows that the declared length of a nvarchar
or varchar
columns makes a huge difference for the performance.
How to add a length constraint to a text field
When you create the table you can do something of this sort,
CREATE TABLE names (
name text CONSTRAINT namechk CHECK (char_length(name) <= 255)
)
(namechk
is just a name for the constraint)
Same goes for ALTER TABLE
for example:
ALTER TABLE names
ADD CONSTRAINT namechk CHECK (char_length(name) <= 255);
SQL Server : Check Constraint expression
You basically need to first drop the old check constraint:
ALTER TABLE dbo.YourTable
DROP CONSTRAINT CHK_YourTable_ReadWriteValues;
(and fill in whatever actual names you have for your table and the check constraint on it), and then you need to create the new one:
ALTER TABLE dbo.YourTable
ADD CONSTRAINT CHK_YourTable_NewReadWriteValues
CHECK ([READ_WRITE] IN ('X', 'Y', 'Z'));
Set minimum and exact length in bytes of column values in SQL Server
For character datatypes (char/nchar/varchar/nvarchar), you set the length:
Remember that for char
and nchar
, you'll ALWAYS be allocated the x bytes space, and your string will be padded. nchar
always has 2 x allocated, but the string length will be as you specify.
For string-length minimums, that really should be left to the application.
SQL Server does allow you to put a constraint on a column.
In this example, the constraint is that the column value be >= 10.
Simply write a TSQL expression in the Expression field.
LEN(myColumn) = 20
- the value being inserted/updated in the column is length of 20.LEN(RTRIM(LRTIM(myColumn))) = 20
- the value being inserted/updated in the column is length of 20, with all leading/trailing whitespace removed.
How to restrict the length of INTEGER when creating a table in SQL Server?
If you want to limit the range of an integer column you can use a check constraint:
create table some_table
(
phone_number integer not null check (phone_number between 0 and 9999999999)
);
But as R.T. and huMpty duMpty have pointed out: a phone number is usually better stored in a varchar
column.
Exact length column in SQL Server
Add a check constraint which asserts that the length of the incoming string is exactly 152 characters:
ALTER TABLE [dbo].[YourTable] WITH CHECK
ADD CONSTRAINT [cnstr] CHECK (LEN(LTRIM([col])) = 152);
Related Topics
SQL Count to Include Zero Values
Oracle SQL Developer "String Literal Too Long" Error
Linq to SQL "Not Like" Operator
Detecting Circular References in SQL
Listagg Query "Ora-00937: Not a Single-Group Group Function"
How to Specify Table Name as a String
Inserting Multiple Rows in Sybase Ase
Xquery - How to Use the SQL:Variable in 'Value()' Function
How to Cancel a SQL Server Execution Process Programmatically
How to Make a Stored Procedure Return a "Dataset" Using a Parameter I Pass
Parsing Openxml with Multiple Elements of the Same Name
Correct Way to Take a Exclusive Lock
Oracle Pl/SQL Results into One String
Can Scalar Functions Be Applied Before Filtering When Executing a SQL Statement
Does SQL Server Optimize Dateadd Calculation in Select Query