Constants (Database Engine)

A constant is a symbol that represents a specific data value. The format of a constant depends on the data type of the value it represents. Constants are also called literals. The following tables shows some examples of how constants are used.

Constants used in Example

Character strings


'The level for job_id: %d should be between %d and %d.'

Unicode strings


Binary string constants



bit constants

0 or 1

datetime constants

'April 15, 1998'



'04:24 PM'

integer constants



decimal constants



float and real constants



money constants



uniqueidentifier constants



For numeric constants, to specify the sign of the numeric value use the unary + and - operators:


Character and Unicode constants are assigned the default collation of the current database, unless you assign a specific collation by using the COLLATE clause, for example:

'abc' COLLATE French_CI_AI
N'lustig' COLLATE German_Phonebook_CS_AS

Using Constants in Transact-SQL

In Transact-SQL, constants can be used in many ways. The following are some examples:

  • As a constant value in an arithmetic expression:

    SELECT Price + $.10
    FROM MyTable
  • As the data value of a column that is compared in a WHERE clause:

    SELECT *
    FROM MyTable
    WHERE LastName = 'O''Brien'
  • As the data value to be put in a variable:

    SET @DecimalVar = -1200.02
  • As the data value that should be put in a column of the current row. This is specified with the SET clause of the UPDATE statement or the VALUES clause of an INSERT statement:

    UPDATE MyTable
    SET Price = $99.99
    WHERE PartNmbr = 1234
    INSERT INTO MyTable VALUES (1235, $88.88)
  • As the character string that specifies the text of the message issued by a PRINT or RAISERROR statement:

    PRINT 'This is a message.'
  • As the value to test for in a conditional statement such as an IF statement or CASE functions:

    IF (@@SALESTOTAL > $100000.00)
       EXECUTE Give_Bonus_Procedure

See Also

Other Resources

Constants (Transact-SQL)
INSERT (Transact-SQL)
Expressions (Transact-SQL)
LIKE (Transact-SQL)
Operators (Transact-SQL)
PRINT (Transact-SQL)
UPDATE (Transact-SQL)
DELETE (Transact-SQL)
WHERE (Transact-SQL)

Help and Information

Getting SQL Server 2005 Assistance