MSSQL

type-mapping and EntityGeneratorCustomizer

JAVA Example

DECIMAL(p*,s\*)

Numeric data types that have fixed precision and scale.

Fixed precision and scale numbers. When maximum precision is used, valid values are from - 10^38 +1 through 10^38 - 1.

<sql-type jdbc-type="DECIMAL" hibernate-type="java.math.BigDecimal"></sql-type>

private BigDecimal decimalTest;

@Column(name = "DecimalTest", precision = 20)
public BigDecimal getDecimalTest() {
    return this.decimalTest;
}

private Double decimalTest;

@Column(name = "DecimalTest", precision = 20)
public Double getDecimalTest() {
    return this.decimalTest;
}

NUMERIC(p*,s\*)

Numeric data types that have fixed precision and scale.

Fixed precision and scale numbers. When maximum precision is used, valid values are from - 10^38 +1 through 10^38 - 1.

<sql-type jdbc-type="NUMERIC" hibernate-type="java.math.BigDecimal"></sql-type>

private BigDecimal numericTest;

@Column(name = "NumericTest", precision = 20)
public BigDecimal getNumericTest() {
    return this.numericTest;
}

MONEY

Data types that represent monetary or currency values.

The money and smallmoney data types are accurate to a ten-thousandth of the monetary units that they represent.

Range: -922,337,203,685,477.5808

to 922,337,203,685,477.5807

Storage: 8 bytes

<sql-type jdbc-type="DECIMAL" name="money" hibernate-type="java.math.BigDecimal"></sql-type>

visitor.addPropertyColumnDefinitionFix(
    new PropertyColumnDefinitionFix(new String[]{"MONEY"},
        new String[]{"java.math.BigDecimal"},"money"));

private BigDecimal moneyTest;

@Column(name = "MoneyTest", scale = 4,
            columnDefinition = "money")
public BigDecimal getMoneyTest() {
    return this.moneyTest;
}

SMALLMONEY

Data types that represent monetary or currency values.

The money and smallmoney data types are accurate to a ten-thousandth of the monetary units that they represent.

Range: - 214,748.3648 to 214,748.3647

Storage: 4 bytes

<sql-type jdbc-type="DECIMAL" name="smallmoney" hibernate-type="java.math.BigDecimal"></sql-type>

visitor.addPropertyColumnDefinitionFix(
    new PropertyColumnDefinitionFix(new String[]{"SMALLMONEY"},
        new String[]{"java.math.BigDecimal"},"smallmoney"));

private BigDecimal smallmoneyTest;

@Column(name = "SmallmoneyTest", precision = 10,
            scale = 4, columnDefinition = "smallmoney")
public BigDecimal getSmallmoneyTest() {
    return this.smallmoneyTest;
}

INT

Exact-number data types that use integer data.

The int data type is the primary integer data type in SQL Server.

Range: -2^31 (-2,147,483,648)

to 2^31-1 (2,147,483,647)

Storage: 4 bytes

Hibernate default mapping

private int intTest;

@Column(name = "IntTest", unique = true, nullable = false)
public int getIntTest() {
    return this.intTest;
}

BIGINT

Exact-number data types that use integer data.

The bigint data type is intended for use when integer values might exceed the range that is supported by the int data type.

Range: -2^63 (-9,223,372,036,854,775,808)

to 2^63-1 (9,223,372,036,854,775,807)

Storage: 8 bytes

Hibernate default mapping

private Long bigintTest;

@Column(name = "BigintTest")
public Long getBigintTest() {
    return this.bigintTest;
}

BINARY(n)

Binary data type of fixed length.

Fixed-length binary data with a length of n bytes, where n is a value from 1 through 8,000. The storage size is n bytes.

Use binary when the sizes of the column data entries are consistent.

visitor.addPropertyColumnDefinitionFix(new PropertyColumnDefinitionFix(
    new String[]{"BINARY"},new String[]{"byte[]"},"binary"));

private byte[] binaryTest;

@Column(name = "BinaryTest", columnDefinition = "binary")
public byte[] getBinaryTest() {
    return this.binaryTest;
}

BIT

An integer data type that can take a value of

1, 0, or NULL.

The SQL Server Database Engine optimizes storage of bit columns. If there are 8 or less bit columns in a table, the columns are stored as 1 byte. If there are from 9 up to 16 bit columns, the columns are stored as 2 bytes, and so on. The string values TRUE and FALSE can be converted to bit values:

TRUE is converted to 1 and FALSE is converted to 0.Converting to bit promotes any nonzero value to 1.

Hibernate default mapping

private Boolean bitTest;

@Column(name = "BitTest")
public Boolean getBitTest() {
    return this.bitTest;
}

CHAR(n)

Are string data type of fixed length.

Fixed-length, non-Unicode string data.

n defines the string length and must be a value from 1 through 8,000.

The storage size is n bytes.

Use char when the sizes of the column data entries are consistent.

visitor.addPropertyColumnDefinitionFix(new PropertyColumnDefinitionFix(
    new String[]{"CHAR"},new String[]{"java.lang.String"},"char"));

private String charTest;

@Column(name = "CharTest", length = 10,
            columnDefinition = "char")
public String getCharTest() {
    return this.charTest;
}

DATE

Defines a date in SQL Server.

Default string literal format: YYYY-MM-DD

Range: 0001-01-01 through 9999-12-31

Storage: 3 bytes, fixed

Accuracy: one Day

Default Value: 1900-01-01

Calendar: Gregorian

Hibernate default mapping

private Date dateTest;

@Temporal(TemporalType.DATE)
@Column(name = "DateTest", length = 10)
public Date getDateTest() {
    return this.dateTest;
}

DATETIME

Defines a date that is combined with a time of day with fractional seconds that is based on a 24-hour clock.

Date Range: 1753-01-01 through 9999-12-31

Time Range: 00:00:00 through 23:59:59.997

Storage: 8 bytes

Accuracy: Rounded to increments of .000, .003, or .007 seconds

Default Value: 1900-01-01 00:00:00

Calendar: Gregorian

Hibernate default mapping

private Date datetimeTest;

@Temporal(TemporalType.TIMESTAMP)
@Column(name = "DatetimeTest", length = 23)
public Date getDatetimeTest() {
    return this.datetimeTest;
}

DATETIME2

Defines a date that is combined with a time of day that is based on 24-hour clock.

datetime2 can be considered as an extension of the existingdatetime type that has a larger date range, a larger default fractional precision, and optional user-specified precision.

Date Range: 0001-01-01 through 9999-12-31

Time Range: 00:00:00 through 23:59:59.9999999

Storage: 8 bytes

Accuracy: 100 nanoseconds

Default Value: 1900-01-01 00:00:00

Calendar: Gregorian

Hibernate default mapping

private Date datetime2test;

@Temporal(TemporalType.TIMESTAMP)
@Column(name = "Datetime2Test", length = 27)
public Date getDatetime2test() {
    return this.datetime2test;
}

FLOAT(n)

Approximate-number data types for use with floating point numeric data.

Floating point data is approximate; therefore, not all values in the data type range can be represented exactly.

Where n is the number of bits that are used to store the mantissa of the float number in scientific notation and, therefore, dictates the precision and storage size. If n is specified, it must be a value between 1 and 53.

The default value of n is 53.

Range: - 1.79E+308 to -2.23E-308, 0 and 2.23E-308 to 1.79E+308

Storage with nvalue of 1-24: 4 bytes

Storage with nvalue of 25-53: 8 bytes

Hibernate default mapping

private Double floatTest;

@Column(name = "FloatTest", precision = 53,
            scale = 0)
public Double getFloatTest() {
    return this.floatTest;
}

IMAGE

Variable-length data type for storing large binary data.

Range: Variable-length binary data from 0 through 2^31-1 (2,147,483,647) bytes.

visitor.addPropertyColumnDefinitionFix(new PropertyColumnDefinitionFix(
    new String[]{"IMAGE"},new String[]{"byte[]"},"image"));

private byte[] imageTest;

@Column(name = "ImageTest", columnDefinition = "image")
public byte[] getImageTest() {
    return this.imageTest;
}

NCHAR(n)

Character data type that is fixed-length.

Unicode data and use the UNICODE UCS-2 character set.

Fixed-length Unicode string data. n defines the string length and must be a value from 1 through 4,000. The storage size is two times n bytes. When the collation code page uses double-byte characters, the storage size is still n bytes. Depending on the string, the storage size of n bytes can be less than the value specified for n.

Use nchar when the sizes of the column data entries are probably going to be similar.

<sql-type jdbc-type="NCHAR" hibernate-type="java.lang.String" />

visitor.addPropertyColumnDefinitionFix(new PropertyColumnDefinitionFix(
    new String[]{"NCHAR"},new String[]{"java.lang.String"},"nchar"));

private String ncharTest;

@Column(name = "NcharTest", columnDefinition = "nchar")
public String getNcharTest() {
    return this.ncharTest;
}

NTEXT

Variable-length data type for storing large Unicode character. Unicode data uses the UNICODE UCS-2 character set.

Variable-length Unicode data with a maximum string length of 2^30 - 1 (1,073,741,823) bytes.

Storage size, in bytes, is two times the string length that is entered.

visitor.addPropertyColumnDefinitionFix(new PropertyColumnDefinitionFix(
    new String[]{"NTEXT"},new String[]{"java.lang.String"},"ntext").addLobAnnotation());

private String ntextTest;

@Lob
@Column(name = "NtextTest", columnDefinition = "ntext")
public String getNtextTest() {
    return this.ntextTest;
}

NVARCHAR(n|max)

Character data type that is variable-length.

Unicode data and use the UNICODE UCS-2 character set.

Variable-length Unicode string data. n defines the string length and can be a value from 1 through 4,000. max indicates that the maximum storage size is 2^31-1 bytes (2 GB).

The storage size, in bytes, is two times the actual length of data entered + 2 bytes.

Use nvarchar when the sizes of the column data entries are probably going to vary considerably.

<sql-type jdbc-type="NVARCHAR" hibernate-type="java.lang.String" />

visitor.addPropertyColumnDefinitionFix(new PropertyColumnDefinitionFix(
    new String[]{"NVARCHAR"},new String[]{"java.lang.String"},"nvarchar"));

private String nvarcharTest;

@Column(name = "NvarcharTest", columnDefinition = "nvarchar")
public String getNvarcharTest() {
    return this.nvarcharTest;
}

*REAL

Approximate-number data types for use with floating point numeric data.

Floating point data is approximate; therefore, not all values in the data type range can be represented exactly.

Range: - 3.40E + 38 to -1.18E - 38, 0 and 1.18E - 38 to 3.40E + 38

Storage: 4 bytes

visitor.addPropertyColumnDefinitionFix(new PropertyColumnDefinitionFix(
    new String[]{"REAL"},new String[]{"java.lang.Float","float"},"real"));

private Float realTest;

@Column(name = "RealTest", precision = 24,
            scale = 0, columnDefinition = "real")
public Float getRealTest() {
    return this.realTest;
}

SMALLDATETIME

Defines a date that is combined with a time of day. The time is based on a 24-hour day, with seconds always zero (:00) and without fractional seconds.

Date Range: 1900-01-01 through 2079-06-06

Time Range: 00:00:00 through 23:59:59

Storage: 4 bytes, fixed.

Accuracy: One minute

Default Value: 1900-01-01 00:00:00

Calendar: Gregorian

Hibernate default mapping

private Date smalldatetimeTest;

@Temporal(TemporalType.TIMESTAMP)
@Column(name = "SmalldatetimeTest", length = 16)
public Date getSmalldatetimeTest() {
    return this.smalldatetimeTest;
}

SMALLINT

Exact-number data types that use integer data.

Range: -2^15 (-32,768) to 2^15-1 (32,767)

Storage: 2 bytes

Hibernate default mapping

private Short smallintTest;

@Column(name = "SmallintTest")
public Short getSmallintTest() {
    return this.smallintTest;
}

TEXT

Variable-length data types for storing large non-Unicode.

Variable-length non-Unicode data in the code page of the server and with a maximum string length of 2^31-1 (2,147,483,647). When the server code page uses double-byte characters, the storage is still 2,147,483,647 bytes. Depending on the character string, the storage size may be less than 2,147,483,647 bytes.

visitor.addPropertyColumnDefinitionFix(new PropertyColumnDefinitionFix(
    new String[]{"TEXT"},new String[]{"java.lang.String"},"text"));

private String textTest;

@Column(name = "TextTest", columnDefinition = "text")
public String getTextTest() {
    return this.textTest;
}

TIME

Defines a time of a day.The time is without time zone awareness and is based on a 24-hour clock.

Default string literal format: hh:mm:ss.nnnnnnn

Range: 00:00:00.0000000 through 23:59:59.9999999

Storage: 5 bytes, fixed, is the default with the default of 100ns fractional second precision.

Accuracy: 100 nanoseconds

Default Value: 00:00:00

Hibernate default mapping

private Date timeTest;

@Temporal(TemporalType.TIME)
@Column(name = "TimeTest", length = 16)
public Date getTimeTest() {
    return this.timeTest;
}

TINYINT

Exact-number data types that use integer data.

Range: 0 to 255

Storage: 1 byte

<sql-type jdbc-type="TINYINT" hibernate-type="java.lang.Short"></sql-type>

visitor.addPropertyColumnDefinitionFix(new PropertyColumnDefinitionFix(
    new String[]{"TINYINT"},new String[]{"java.lang.Short","short"},"tinyint"));

private Short tinyintTest;

@Column(name = "TinyintTest", columnDefinition = "tinyint")
public Short getTinyintTest() {
    return this.tinyintTest;
}

UNIQUEIDENTIFIER

Is a 16-byte GUID.

Hibernate default mapping

private String uniqueidentifierTest;

@Column(name = "UniqueidentifierTest", length = 36)
public String getUniqueidentifierTest() {
    return this.uniqueidentifierTest;
}

HIERARCHYID

The hierarchyid data type is a variable length, system data type. Use hierarchyid to represent position in a hierarchy. A column of type hierarchyid does not automatically represent a tree.

It is up to the application to generate and assign hierarchy values in such a way that the desired relationship between rows is reflected in the values.

Hibernate default mapping

private byte[] hierarchyidTest;

@Column(name = "HierarchyidTest")
public byte[] getHierarchyidTest() {
    return this.hierarchyidTest;
}

VARBINARY (n|max)

Binary data type of variable length.

Variable-length binary data. n can be a value from 1 through 8,000.

max indicates that the maximum storage size is 2^31-1 bytes. The storage size is the actual length of the data entered + 2 bytes. The data that is entered can be 0 bytes in length.

Use varbinary when the sizes of the column data entries vary considerably.

Use varbinary(max) when the column ##data entries exceed 8,000 bytes.

Hibernate default mapping

private byte[] varbinaryTest;

@Column(name = "VarbinaryTest")
public byte[] getVarbinaryTest() {
    return this.varbinaryTest;
}

VCHAR (n|max)

Is a string data type of variable length.

Variable-length, non-Unicode string data.

n defines the string length and can be a value from 1 through 8,000.

max indicates that the maximum storage size is 2^31-1 bytes (2 GB).

The storage size is the actual length of the data entered + 2 bytes.

Use varchar when the sizes of the column data entries vary considerably.

Use varchar(max) when the sizes of the column data entries vary considerably, and the size might exceed 8,000 bytes.

Hibernate default mapping

private String vcharTest;

@Column(name = "VcharTest", length = 50)
public String getVcharTest() {
    return this.vcharTest;
}

*p (precision)

The maximum total number of decimal digits that will be stored, both to the left and to the right of the decimal point. The precision must be a value from 1 through the maximum precision of 38.

The default precision is 18.

Precision	Storage bytes
1 - 9	5
10-19	9
20-28	13
29-38	17

*s (scale)

The number of decimal digits that will be stored to the right of the decimal point. This number is subtracted from p to determine the maximum number of digits to the left of the decimal point.

The maximum number of decimal digits that can be stored to the right of the decimal point. Scale must be a value from 0 through p. Scale can be specified only if precision is specified.

The default scale is 0; therefore, 0 ⇐ s ⇐ p Maximum storage sizes vary, based on the precision.