Sqlserver中tinyint, smallint, int, bigint的区别 及 10进制转
一。类型比较 bigint：从-2^63(-9223372036854775808)到2^63-1(9223372036854775807)的整型数据，存储大小为 8 个字节。一个字节就是8位，那么bigint就有64位 int：从-2^31(-2,147,483,648)到2^31-1(2,147,483,647)的整型数据，存储大小为 4 个字节。int类型
一。类型比较
bigint：从-2^63(-9223372036854775808)到2^63-1(9223372036854775807)的整型数据，存储大小为 8 个字节。一个字节就是8位，那么bigint就有64位
int：从-2^31(-2,147,483,648)到2^31-1(2,147,483,647)的整型数据，存储大小为 4 个字节。int类型，最大可以存储32位的数据
smallint：从-2^15(-32,768)到2^15-1(32,767)的整数数据，存储大小为 2 个字节。smallint就是有16位
tinyint：从0到255的整数数据，存储大小为 1 字节。tinyint就有8位。
二。注释
在支持整数值的地方支持 bigint 数据类型。但是，bigint 用于某些特殊的情况，当整数值超过 int 数据类型支持的范围时，就可以采用 bigint。在 SQL Server 中，int 数据类型是主要的整数数据类型。
在数据类型优先次序表中，bigint 位于 smallmoney 和 int 之间。
只有当参数表达式是 bigint 数据类型时，函数才返回 bigint。SQL Server 不会自动将其它整数数据类型（tinyint、smallint 和 int）提升为bigint。
三。总结
所以选择最恰当的数据类型是非常重要的，例如，给你10个18位的全是数字的身份证号码，和10个15位的全是数字的身份证号码。让你以最快的查询的速度查找出来，这20个记录，那么你在设计这个字段的时候，应该有几个地方要注意：
1. 最快的查询速度，无非我们选择int类型的数据来存储这20个身份证号码。但是15位和18位的数据貌似只有bigint才满足条件。
2. 有人要说为啥不用varchar或者char呢。直接存成一个字符串。这个问题问的比较好，首先，int的查询速度肯定比varchar和char要快，那么int类型的数据是首选，但是int在sqlserver中有好几种。这个就要选择最恰当的数据类型。
---------------------------------------------------------------------------------------------------------------------------------
code:
select
STUFF((master.dbo.fn_varbintohexstr(
cast( --这是主要功能实现代码 ，其他代码的作用就是将前边的 0x 去掉
CONVERT(bigint, 17) as binary(1) --'1'表示生成的16进制数的长度为2的'1'次方个,也就是最大为 FF
)
) COLLATE Latin1_General_CI_AS_KS_WS ),1,2,'')
结果： 11
<span>--</span><span> By Aaron West, 5/4/2005 </span><span><br>--</span><span> This version allows negative numbers </span><span><br></span><span>CREATE</span> <span>FUNCTION</span> dbo.HexToINT <br>( <br> <span>@Value</span> <span>VARCHAR</span>(<span>8</span>) <br>) <br><span>RETURNS</span> <span>INT</span> <br><span>AS</span> <br><span>BEGIN</span> <br> <span>if</span> <span>@Value</span> <span>LIKE</span> <span>'</span><span>%[^0-9A-Fa-f]%</span><span>'</span> <br> <span>RETURN</span> <span>NULL</span> <br> <span>DECLARE</span> <span>@I</span> <span>BIGINT</span> <br> <span>SET</span> <span>@I</span> <span>=</span> <span>CAST</span>(<span>CAST</span>(<span>RIGHT</span>(<span>UPPER</span>(<span>'</span><span>00000000</span><span>'</span> <span>+</span> <span>@Value</span>),<span>8</span>) <span>AS</span> <span>BINARY</span>(<span>8</span>)) <span>AS</span> <span>BIGINT</span>) <span>-</span> <span>CAST</span>(<span>0x3030303030303030</span> <span>AS</span> <span>BIGINT</span>) <br> <span>SET</span> <span>@I</span><span>=</span><span>@I</span><span>-</span>((<span>@I</span><span>/</span><span>16</span>)<span>&</span><span>CAST</span>(<span>0x0101010101010101</span> <span>AS</span> <span>BIGINT</span>))<span>*</span><span>7</span> <br> <span>RETURN</span> <span>CAST</span>(<span>CAST</span>( <br> (<span>@I</span><span>&</span><span>15</span>) <br> <span>+</span>((<span>@I</span><span>/</span><span>16</span>)<span>&</span><span>240</span>) <br> <span>+</span>((<span>@I</span><span>/</span><span>256</span>)<span>&</span><span>3840</span>) <br> <span>+</span>((<span>@I</span><span>/</span><span>4096</span>)<span>&</span><span>61440</span>) <br> <span>+</span>((<span>@I</span><span>/</span><span>65536</span>)<span>&</span><span>983040</span>) <br> <span>+</span>((<span>@I</span><span>/</span><span>1048576</span>)<span>&</span><span>15728640</span>) <br> <span>+</span>((<span>@I</span><span>/</span><span>16777216</span>)<span>&</span><span>251658240</span>) <br> <span>+</span>(<span>@I</span><span>/</span><span>CAST</span>(<span>0x0100000000000000</span> <span>AS</span> <span>BIGINT</span>)<span>*</span><span>268435456</span>) <br> <span>AS</span> <span>BINARY</span>(<span>4</span>))<span>AS</span> <span>INT</span>) <br><span>END</span> <br><span>GO</span> <br> <br><span>SELECT</span> <br> dbo.HexToINT(<span>'</span><span>0ABC</span><span>'</span>) , <br> dbo.HexToINT(<span>'</span><span>7FFF</span><span>'</span>) , <br> dbo.HexToINT(<span>'</span><span>0FFF</span><span>'</span>) , <br> dbo.HexToINT(<span>'</span><span>0</span><span>'</span>) <span>AS</span> Zero, <br> dbo.HexToINT(<span>'</span><span>7FFFFFFF</span><span>'</span>) <span>AS</span> MaxValue, <br> dbo.HexToINT(<span>'</span><span>80000000</span><span>'</span>) <span>AS</span> MaxNeg, <br> dbo.HexToINT(<span>'</span><span>FFFFFFFF</span><span>'</span>) <span>AS</span> NegOne
<span>create</span> <span>function</span> dbo.ufn_vbintohexstr <br>( <br> <span>@vbin_in</span> <span>varbinary</span>(<span>256</span>) <br>) <br><span>returns</span> <span>varchar</span>(<span>514</span>) <br><span>as</span> <br><span>Begin</span> <br> <span>declare</span> <span>@x</span> <span>bigint</span> <br> <span>declare</span> <span>@len</span> <span>int</span> <br> <span>declare</span> <span>@loop</span> <span>int</span> <br> <span>declare</span> <span>@value</span> <span>varbinary</span>(<span>514</span>) <br> <span>set</span> <span>@value</span> <span>=</span> 0x <br> <span>set</span> <span>@loop</span> <span>=</span> <span>1</span> <br> <span>set</span> <span>@len</span> <span>=</span> <span>datalength</span>(<span>@vbin_in</span>) <br> <span>if</span> (<span>@len</span> <span>&</span> <span>1</span>) <span></span> <span>0</span> <br> <span>set</span> <span>@vbin_in</span> <span>=</span> <span>0x00</span> <span>+</span> <span>@vbin_in</span> <br> <span>if</span> (<span>@len</span> <span>&</span> <span>3</span>) <span> <span>3</span> <br> <span>set</span> <span>@vbin_in</span> <span>=</span> <span>0x0000</span> <span>+</span> <span>@vbin_in</span> <br> <span>while</span> <span>@loop</span> <span> <span>@len</span> <br> <span>begin</span> <br> <span>set</span> <span>@x</span> <span>=</span> <span>CAST</span>(<span>substring</span>(<span>@vbin_in</span>,<span>@loop</span>,<span>4</span>)<span>AS</span> <span>BIGINT</span>) <br> <span>set</span> <span>@x</span> <span>=</span><span>65536</span><span>*</span> <br> ( (<span>@x</span><span>&</span><span>0xF0000000</span>)<span>*</span><span>4096</span> <br> <span>+</span>(<span>@x</span><span>&</span><span>0x0F000000</span>)<span>*</span><span>256</span> <br> <span>+</span>(<span>@x</span><span>&</span><span>0x00F00000</span>)<span>*</span><span>16</span> <br> <span>+</span>(<span>@x</span><span>&</span><span>0x000F0000</span>) ) <br> <span>+</span>(<span>@x</span><span>&</span><span>0xF000</span>)<span>*</span><span>4096</span> <br> <span>+</span>(<span>@x</span><span>&</span><span>0x0F00</span>)<span>*</span><span>256</span> <br> <span>+</span>(<span>@x</span><span>&</span><span>0x00F0</span>)<span>*</span><span>16</span> <br> <span>+</span>(<span>@x</span><span>&</span><span>0x000F</span>) <br> <span>set</span> <span>@x</span> <span>=</span> (<span>@x</span> <span>|</span> <span>0x3030303030303030</span>)<span>+</span> <br> ((<span>@x</span><span>+</span><span>0x0606060606060606</span>)<span>/</span><span>16</span> <br> <span>&</span> <span>0x0101010101010101</span>)<span>*</span><span>7</span> <br> <span>select</span> <span>@value</span> <span>=</span> <span>@value</span> <span>+</span> <span>CAST</span>(<span>@x</span> <span>AS</span> <span>BINARY</span>(<span>8</span>)) <br> <span>set</span> <span>@loop</span> <span>=</span> <span>@loop</span> <span>+</span> <span>4</span> <br> <span>end</span> <br> <span>return</span> <span>'</span><span>0x</span><span>'</span><span>+</span> <span>right</span>(<span>CAST</span>(<span>@value</span> <span>AS</span> <span>VARCHAR</span>(<span>514</span>)), <span>@len</span><span>*</span><span>2</span>) <br><span>end</span> <br><span>GO</span> <br><span>select</span> dbo.ufn_vbintohexstr(<span>0x123456789abcdef1234</span>) <br><span>--</span><span> outputs: 0x0123456789ABCDEF1234 </span><span><br></span><span>GO</span></span></span>
Hot AI Tools
Undresser.AI Undress
AI-powered app for creating realistic nude photos
AI Clothes Remover
Online AI tool for removing clothes from photos.
Undress AI Tool
Undress images for free
Clothoff.io
AI clothes remover
Video Face Swap
Swap faces in any video effortlessly with our completely free AI face swap tool!
Hot Article
Hot Tools
Notepad++7.3.1
Easy-to-use and free code editor
SublimeText3 Chinese version
Chinese version, very easy to use
Zend Studio 13.0.1
Powerful PHP integrated development environment
Dreamweaver CS6
Visual web development tools
SublimeText3 Mac version
God-level code editing software (SublimeText3)
Hot Topics
How to import mdf file into sqlserver
Apr 08, 2024 am 11:41 AM
The import steps are as follows: Copy the MDF file to SQL Server's data directory (usually C:\Program Files\Microsoft SQL Server\MSSQL\DATA). In SQL Server Management Studio (SSMS), open the database and select Attach. Click the Add button and select the MDF file. Confirm the database name and click the OK button.
How to solve the problem that the object named already exists in the sqlserver database
Apr 05, 2024 pm 09:42 PM
For objects with the same name that already exist in the SQL Server database, the following steps need to be taken: Confirm the object type (table, view, stored procedure). IF NOT EXISTS can be used to skip creation if the object is empty. If the object has data, use a different name or modify the structure. Use DROP to delete existing objects (use caution, backup recommended). Check for schema changes to make sure there are no references to deleted or renamed objects.
How to check sqlserver port number
Apr 05, 2024 pm 09:57 PM
To view the SQL Server port number: Open SSMS and connect to the server. Find the server name in Object Explorer, right-click it and select Properties. In the Connection tab, view the TCP Port field.
What to do if the sqlserver service cannot be started
Apr 05, 2024 pm 10:00 PM
When the SQL Server service fails to start, here are some steps to resolve: Check the error log to determine the root cause. Make sure the service account has permission to start the service. Check whether dependency services are running. Disable antivirus software. Repair SQL Server installation. If the repair does not work, reinstall SQL Server.
How to recover accidentally deleted database in sqlserver
Apr 05, 2024 pm 10:39 PM
If you accidentally delete a SQL Server database, you can take the following steps to recover: stop database activity; back up log files; check database logs; recovery options: restore from backup; restore from transaction log; use DBCC CHECKDB; use third-party tools. Please back up your database regularly and enable transaction logging to prevent data loss.
Where is the sqlserver database?
Apr 05, 2024 pm 08:21 PM
SQL Server database files are usually stored in the following default location: Windows: C:\Program Files\Microsoft SQL Server\MSSQL\DATALinux: /var/opt/mssql/data The database file location can be customized by modifying the database file path setting.
What is the difference between Mysql tinyint(1) and tinyint(4)
Jun 01, 2023 am 08:31 AM
What is tinyint(M)? First, let’s understand the difference between the string type varchar(M) and the numerical type tinyint(M) in mysql? For string column type: varchar(M), M is the maximum character length that can be stored in the field, which is the field length. Depending on the settings, you are likely to receive an error when you insert data that exceeds the field length, and even if you do not receive an error, the data you insert will be automatically truncated to fit the predefined length of the field. Therefore, varchar(20) and varchar(40) are different, which truly reflects the length of data that can be stored in this field. Numeric column type: Its length modifier indicates the maximum display width, which is the same as the field
How to delete sqlserver if the installation fails?
Apr 05, 2024 pm 11:27 PM
If the SQL Server installation fails, you can clean it up by following these steps: Uninstall SQL Server Delete registry keys Delete files and folders Restart the computer
