sql⼆进制转⼗进制_了解SQL⼗进制数据类型
sql⼆进制转⼗进制
This article aims to walk you through the SQL Decimal data type and its usage with various examples. We will also e how we can exerci this data type in SQL Server to help make SQL developer’s job easier.
本⽂旨在通过各种⽰例逐步介绍SQL Decimal数据类型及其⽤法。 我们还将看到如何在SQL Server中使⽤此数据类型，以帮助简化SQL 开发⼈员的⼯作。
介绍 (Introduction)
Organizations deal with decimals on a day-to-day basis, and the decimal values can be en everywhere in different ctors, be it in banks, the medical industry, biometrics, gas stations, financial reports, sports, and whatnot. Using whole numbers (by rounding decimal numbers) definitely makes one’s job easier but it often leads to inaccurate outputs, especially when we are dealing with a large number of values and crucial data. In such scenarios, it is ideal to u Sql Decimal data type in SQL Server to deliver correct results with perfect precision.
组织每天处理⼩数，这些⼗进制值可以在银⾏，医疗⾏业，⽣物识别，加油站，财务报告，体育等各种地⽅的不同部门中随处可见。 使⽤整数（通过四舍五⼊⼗进制数）⽆疑会使⼈的⼯作更轻松，但通常会导致输出不准确，尤其是当我们处理⼤量的值和关键数据时。 在这种情况下，理想的是在SQL Server中使⽤Sql Decimal数据类型来以完美的精度提供正确的结果。
It becomes very esntial for SQL developers to choo the correct data types in the table structure while designing and modeling SQL databas. Let’s move forward and explore Decimal data type in SQL Server.
对于SQL开发⼈员来说，在设计和建模SQL数据库时选择表结构中的正确数据类型变得⾮常重要。 让我们继续前进，探索SQL Server中的Decimal数据类型。
前提条件 (Pre-requisite )
SQL Decimal data type is being ud in SQL Server since forever. You can u any SQL Server version installed (starting 2000 or above) to understand this data type. We will be using SQL Server 2017 in this article for the demo purpos. If you don’t have any version installed on your system and wish to practice against the 2017 version, download it from .
SQL Decimal数据类型从⼀开始就在SQL Server中使⽤。 您可以使⽤安装的任何SQL Server版本（从2000或更⾼版本开始）来了解此数据类型。 我们将在本⽂中使⽤SQL Server 2017进⾏演⽰。 如果您的系统上未安装任何版本，并希望针对2017版本进⾏练习，请从下载。
SQL Server中的⼗进制数据类型的基本语法 (The Basic syntax of Decimal data type in SQL Server)
Let’s take a look at the basic syntax of SQL Decimal Data type first. It is denoted as below:
⾸先让我们看⼀下SQL Decimal Data类型的基本语法。 表⽰如下：
decimal [(p [,s])]
⼗进制[[p [，s]）]
Where,
哪⾥，
p stands for Precision, the total number of digits in the value, i.e. on both sides of the decimal point
p代表精度，即值中的位数总数，即⼩数点的两侧
s stands for Scale, number of digits after the decimal point
s代表Scale，⼩数点后的位数
The default value of p is 18 and s is 0 and for both the values, the minimum is 1 and the maximum is 38.怎样治咳嗽
p的默认值为18，s的默认值为0，这两个值的最⼩值均为1，最⼤值为38。
In short, by defining parameters in the SQL Decimal data type, we are estimating how many digits a column or a variable will have and also the number of digits to the right of the decimal point.
简⽽⾔之，通过在SQL Decimal数据类型中定义参数，我们可以估算列或变量将具有多少位数，以及⼩数点右边的位数。
For instance, decimal (4,2) indicates that the number will have 2 digits before the decimal point and 2 digits after the decimal point, something like this has to be the number value- ##.##.
例如，⼗进制（4,2）表⽰该数字在⼩数点前有2位数字，在⼩数点后有2位数字，类似这样的东西必须是数字值-##。##。
One important thing to note here is, – parameter s (Scale) can only be specified if p (Precision) is specified. The scale must always be less than or equal to the precision.
这⾥要注意的重要⼀件事是：–仅当指定了p（精度）时才能指定参数s（⽐例）。 ⽐例尺必须始终⼩于或等于精度。
定义SQL⼗进制数据类型 (Defining SQL Decimal Data type)
Let’s work with a very popular mathematical constant – π, aka, Pi that has a value equal to 3.14159 (22/7 in a fraction). Copy and paste the below query in a new query window and execute it.
肉丸怎么做让我们使⽤⼀个⾮常流⾏的数学常数–π，⼜称Pi，其值等于3.14159（分数为22/7）。 将以下查询复制并粘贴到新的查询窗⼝中并执⾏。
1DECLARE @PiWithNoDecimal DECIMAL(6,0) = 3.14159
2DECLARE @Piupto5Decimal DECIMAL(6,5) = 3.14159
3DECLARE @Piupto1Decimal DECIMAL(3,1) = 3.14159
4SELECT @PiWithNoDecimal AS PiWithNoDecimal, @Piupto5Decimal AS Piupto5Decimal, @Piupto1Decimal AS Piupto1Decimal
The above result t shows how SQL Server treats each combination of precision and scale as a different data type. Like here, decimal (6, 0) behaves differently from data types decimal (6,5) and decimal (3,1) and are considered as three different types. This way we can tweak the parameters in the SQL Decimal type to achieve desired results.
上⾯的结果集显⽰了SQL Server如何将精度和⼩数位的每种组合视为不同的数据类型。 像这⾥⼀样，⼗进制（6，0）的⾏为不同于数据类型⼗进制（6,5）和⼗进制（3,1），并被认为是三种不同的类型。 这样，我们可以调整SQL Decimal类型中的参数以获得所需的结果。
Now that we know how to create this Decimal data type in SQL Server, let’s explore it with numerous examples.
现在，我们知道如何在SQL Server中创建此Decimal数据类型，让我们通过⼤量⽰例进⾏探索。
在表中使⽤SQL⼗进制 (Using SQL Decimal in the Tables)
Let’s quickly create a new table, named Patients, that makes u of decimal data type for columns h
eight and weight. We will inrt a few rows using an INSERT clau as shown below for the demo purpos.
让我们快速创建⼀个名为Patients的新表，该表将⼗进制数据类型⽤于列的⾼度和重量。 为了演⽰的⽬的，我们将使⽤INSERT⼦句插⼊⼏⾏，如下所⽰。
1CREATE TABLE dbo.Patients
2( Name varchar(10),
3 Gender varchar(2),
4 Height decimal (3,2),
5 Weight decimal (5,2)
6)
7INSERT INTO PATIENTS VALUES('John','M',6.1,80.4)
8INSERT INTO PATIENTS VALUES('Bred','M',5.8,73.7)
9INSERT INTO PATIENTS VALUES('Leslie','F',5.3,66.9)
10INSERT INTO PATIENTS VALUES('Rebecca','F',5.7,50.2)
11INSERT INTO PATIENTS VALUES('Shermas','M',6.5,190.6)
Once the data is populated in the table, we can query this data using SELECT statement as shown below. The decimal values can be en in the height and weight attributes.
小心的近义词
将数据填充到表中后，我们可以使⽤SELECT语句查询此数据，如下所⽰。 可以在⾝⾼和体重属性中看到⼗进制值。
Let’s figure out what happens if we try to inrt values that exceed the specified precision or scale va
lues while defining the Height and Weight columns. For this demo, we will inrt 2 more rows into this table (shown below).
让我们弄清楚如果在定义“⾼度”和“重量”列时尝试插⼊超出指定精度或⽐例值的值会发⽣什么。 对于此演⽰，我们将在此表中再插⼊2⾏（如下所⽰）。
It encounters the below error saying arithmetic overflow error and the SQL Server terminated the statements.
它遇到以下错误，指出算术溢出错误，并且SQL Server终⽌了该语句。
数字变变变
Let’s get to the root of this issue：让我们弄清楚这个问题的根源：
Height Decimal (3, 2) means the value can have 3 digits overall and 2 digits to the right of the decimal point. In the first line of code above, the value 10.9 (considered as 10.90 = 4 digits overall) exceeds the specified range (3, 2) and
caus the overflow ⾼度⼩数 （3，2）表⽰该值的总位数可以为3位，⼩数点右边可以为2位。 在上⾯的代码的第⼀⾏中，值10.9（视为10.90 =总共4位数字）超出指定范围（3，2），并导致溢出
Weight Decimal (5,2) means the total number of digits cannot exceed 5 and 2 digits can be placed to the right of the decimal. However, the value 1000.45 in the cond line of code above exceeds the specified range of (5, 2) since it
means 6 digits in total and throws an overflow error ⼗进制权重 （5,2）表⽰总位数不能超过5，并且2位数字可以放在⼩数点右边。 但是，上⾯第⼆⾏代码中的值1000.45超出了指定的范围（5，2），因为它表⽰总共6位数并且会引发溢出错误
wyyyyQuick note – In the above error message, if you have noticed, “data type numeric” is stated instead of data type decimal, the reason is that the Decimal and the Numeric data type are exactly the same, both are fixed-precision data types and can be ud interchangeably 快速说明 –在上述错误消息中，如果您已经注意到，声明的是“数据类型数字”⽽不是数据类型⼗进制，原因是⼗进制和数字数据类型
完全相同，均为固定精度数据类型可以互换使⽤
SELECT * FROM dbo.PATIENTS
INSERT INTO PATIENTS VALUES('Largest','M', '10.9', 88.5)
INSERT INTO PATIENTS VALUES('Hulk','M', '9.9', 1000.45)
解决错误 (Resolving the error)
One of the easiest workarounds is to increa the precision level of the columns to store bigger numbers. We can alter the data type of the columns without dropping the table or column with the below code.
玻利维亚旅游最简单的解决⽅法之⼀是增加列的精度级别以存储更⼤的数字。 我们可以更改列的数据类型，⽽⽆需使⽤以下代码删除表或列。Once altered, execute the Inrt queries to inrt the rows into the table.
更改后，执⾏插⼊查询以将这些⾏插⼊表中。
We can e the rows being added to the table.
我们可以看到将⾏添加到表中。
印记作文
SQL Server 中使⽤⼗进制数据类型的存储注意事项 (Storage considerations with Decimal Data Type in SQL Server)
Data type SQL Decimal requires the following storage bytes for the specified precision as provided by Microsoft below：数据类型SQL Decimal需要以下存储字节以达到指定精度，如Microsoft所提供的：
Precision
Storage (Bytes)1 – 9
510 – 19
关于订婚的祝福语
920 – 28
1329 – 3817
1ALTER TABLE dbo.Patients ALTER COLUMN Height decimal(4,2)2ALTER TABLE dbo.Patients ALTER COLUMN Weight decimal (6,2)
精确储存空间（位元组）
1 – 95
10 – 199
20 – 2813
29 – 3817
The space consumption of SQL Decimal data type is bad on the column definition and not on the size of the value being assigned to it. Decimal (12, 4) with value of 888.888 takes 9 bytes on disk and Decimal (22, 2) value of 9999.99 consumes 13 bytes on disk. This is why this data type falls under fixed-length columns.
SQL Decimal数据类型的空间消耗取决于列定义，⽽不取决于为其分配的值的⼤⼩。 例如，值888.888的⼗进制（12，4）在磁盘上占⽤9个字节，⽽值9999.99的⼗进制（22，2）在磁盘上消耗13个字节。 这就是为什么此数据类型属于固定长度列的原因。
As a SQL developer mylf, I always try to u SQL Decimal data type as decimal (9, 2) which consumes the least storage, 5 bytes on disk and offers better performance.
作为我⾃⼰SQL开发⼈员，我总是尝试将SQL Decimal数据类型⽤作⼗进制（9，2），它消耗最少的存储空间，磁盘上的5个字节并提供更好的性能。
结论 (Conclusion)
I hope this article provides a comprehensible approach on how to u SQL Decimal data type. Always ensure the precision of the decimal or numeric variable specified is enough to accommodate the values assigned to it. Additionally, we obrved, how lecting the right kind of data type helps SQL developers to save disk storage.
我希望本⽂提供了⼀种有关如何使⽤SQL Decimal数据类型的可理解⽅法。 始终确保指定的⼗进制或数字变量的精度⾜以容纳分配给它的值。 此外，我们观察到，选择正确的数据类型如何帮助SQL开发⼈员节省磁盘存储。
In ca of any questions, plea feel free to ask in the comments ction below.
如有任何疑问，请随时在下⾯的评论部分中提问。
To continue your journey with SQL Server and data types ud in it, I would recommend going through the below links.
为了继续使⽤SQL Server及其中使⽤的数据类型，我建议您浏览以下链接。
sql⼆进制转⼗进制
