2.1 String Data Types

Source: w3schools

2.2 Numeric Data Types

Comment on the graphic: * usually uses less storage and have better speed (up to 20x)

The p parameter indicates the maximum total number of digits that can be stored (both to the left and to the right of the decimal point). p must be a value from 1 to 38. Default is 18.

The s parameter indicates the maximum number of digits stored to the right of the decimal point. s must be a value from 0 to p. Default value is 0.

Source: w3schools

2.3 Date and Time Data Types

Source: w3schools

3.1 DECIMAL

See the following possible settings for the DECIMAL data type:

Setting: Precision = default, Scale = default

Setting: Precision = 2, Scale = default

Setting: Precision = default, Scale = 2

Setting: Precision = 0, Scale = 0

Setting: Precision = 0, Scale = 2

Setting: Precision = 5, Scale = 2

Here is an overview of the generated table (logical and relational writer):

If you now display the corresponding DDL for this table, it looks like this:

CREATE TABLE DECIMAL_Table 
    (
     DEC_VAR_def_def DECIMAL (28) , 
     DEC_VAR_2_def DECIMAL (2) , 
     DEC_VAR_def_2 DECIMAL (28,2) , 
     DEC_VAR_0_0 DECIMAL , 
     DEC_VAR_0_2 DECIMAL , 
     DEC_VAR_5_2 DECIMAL (5,2) 
    )
GO

Now it is important to know that the default settings of Oracle Data Modeler are different from those of SQL Server Management Studio:

• Oracle Data Modeler: 28 digits
• SSMS: 18 digits

This means that for the variables DEC_VAR_0_0 DECIMAL and DEC_VAR_0_2 DECIMAL a maximum length of 18 digits would apply, whereas the variables DEC_VAR_def_def DECIMAL and DEC_VAR_def_2 DECIMAL should have a total length of 28 digits.

Now let’s use this DDL in SQL Server Management Studio and insert a few values to illustrate the meaning of the settings:

SET LANGUAGE ENGLISH

use MyDB


CREATE TABLE DECIMAL_Table 
    (
     DEC_VAR_def_def DECIMAL (28) , 
     DEC_VAR_2_def DECIMAL (2) , 
     DEC_VAR_def_2 DECIMAL (28,2) , 
     DEC_VAR_0_0 DECIMAL , 
     DEC_VAR_0_2 DECIMAL , 
     DEC_VAR_5_2 DECIMAL (5,2) 
    )
GO

INSERT INTO DECIMAL_Table VALUES (2.25, 2.25, 2.25, 2.25, 2.25, 2.25)
INSERT INTO DECIMAL_Table VALUES (2.225, 2.225, 2.225, 2.225, 2.225, 2.225)

SELECT * FROM DECIMAL_Table

Here we see, only where also a scale of 2 (DEC_VAR_def_2 DECIMAL (28,2) and DEC_VAR_5_2 DECIMAL (5,2)) is set, decimal places are allowed. For all other columns, the decimal places are rounded. The same applies if you exceed the specified number of decimal places.

3.2 NUMMERIC

In the overview I wrote that the two data types DECIMAL and NUMERIC do not differ functionally. Let’s test this.

When creating the table ‘NUMERIC_Table’ I will make exactly the same settings for the different variables as for those from the DECIMAL table.

Here is again an overview of the generated table (logical and relational writer):

Here is the generated associated DDL:

CREATE TABLE NUMERIC_Table 
    (
     NUM_VAR_def_def NUMERIC (28) , 
     NUM_VAR_2_def NUMERIC (2) , 
     NUM_VAR_def_2 NUMERIC (28,2) , 
     NUM_VAR_0_0 NUMERIC , 
     NUM_VAR_0_2 NUMERIC , 
     NUM_VAR_5_2 NUMERIC (5,2) 
    )
GO

Let’s again use this DDL in SQL Server Management Studio and insert the same values as for the DECIMAL table:

CREATE TABLE NUMERIC_Table 
    (
     NUM_VAR_def_def NUMERIC (28) , 
     NUM_VAR_2_def NUMERIC (2) , 
     NUM_VAR_def_2 NUMERIC (28,2) , 
     NUM_VAR_0_0 NUMERIC , 
     NUM_VAR_0_2 NUMERIC , 
     NUM_VAR_5_2 NUMERIC (5,2) 
    )
GO

INSERT INTO NUMERIC_Table VALUES (2.25, 2.25, 2.25, 2.25, 2.25, 2.25)
INSERT INTO NUMERIC_Table VALUES (2.225, 2.225, 2.225, 2.225, 2.225, 2.225)

SELECT * FROM NUMERIC_Table

As we can see, the same rounding is done as in the example with the DECIMAL table. The two data types are therefore functionally the same.

3.3 FLOAT

Finally, I would like to go into detail about the FLOAT data type, which is an approximate numeric data type. Here we also have the settings of Precision and Scale in the Oracle Data Modeler but how much influence do they have?

Let’s test it by using the same variables with the same settings as in the two previous examples.

Here is the generated associated DDL:

CREATE TABLE FLOAT_Table 
    (
     FL_VAR_def_def FLOAT , 
     FL_VAR_2_def FLOAT (2) , 
     FL_VAR_def_2 FLOAT , 
     FL_VAR_0_0 FLOAT , 
     FL_VAR_0_2 FLOAT , 
     FL_VAR_5_2 FLOAT (5) 
    )
GO

Now let’s take a look at this DDL in SQL Server Management Studio:

CREATE TABLE FLOAT_Table 
    (
     FL_VAR_def_def FLOAT , 
     FL_VAR_2_def FLOAT (2) , 
     FL_VAR_def_2 FLOAT , 
     FL_VAR_0_0 FLOAT , 
     FL_VAR_0_2 FLOAT , 
     FL_VAR_5_2 FLOAT (5) 
    )
GO

INSERT INTO FLOAT_Table VALUES (333.25, 333.25, 333.25, 333.25, 333.25, 333.25)
INSERT INTO FLOAT_Table VALUES (666666.25, 666666, 666666.25, 666666.25, 666666.25, 666666.25)
INSERT INTO FLOAT_Table VALUES (25.12345, 25.12345, 25.12345, 25.12345, 25.12345, 25.12345)
INSERT INTO FLOAT_Table VALUES (25.1234567, 25.1234567, 25.1234567, 25.1234567, 25.1234567, 25.1234567)

SELECT * FROM FLOAT_Table

Here we see the problem I have with the FLOAT data type. I can set Precision and Scale, but it has no effect at all, because no matter if I limit the digits before or after the decimal point, it is still possible to insert more digits into the variable.

The only advantage this data type has is that it requires less memory and is more performant in the analysis.