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>
热AI工具
Undresser.AI Undress
人工智能驱动的应用程序，用于创建逼真的裸体照片
AI Clothes Remover
用于从照片中去除衣服的在线人工智能工具。
Undress AI Tool
免费脱衣服图片
Clothoff.io
AI脱衣机
Video Face Swap
使用我们完全免费的人工智能换脸工具轻松在任何视频中换脸！
热门文章
热工具
记事本++7.3.1
好用且免费的代码编辑器
SublimeText3汉化版
中文版，非常好用
禅工作室 13.0.1
功能强大的PHP集成开发环境
Dreamweaver CS6
视觉化网页开发工具
SublimeText3 Mac版
神级代码编辑软件(SublimeText3)
sqlserver怎么导入mdf文件
Apr 08, 2024 am 11:41 AM
导入步骤如下：将 MDF 文件复制到 SQL Server 的数据目录（通常为 C:\Program Files\Microsoft SQL Server\MSSQL\DATA）。在 SQL Server Management Studio（SSMS）中，打开数据库并选择“附加”。单击“添加”按钮，选择 MDF 文件。确认数据库名称，点击确定按钮即可。
sqlserver数据库中已存在名为的对象怎么解决
Apr 05, 2024 pm 09:42 PM
对于 SQL Server 数据库中已存在同名对象，需要采取以下步骤：确认对象类型（表、视图、存储过程）。如果对象为空，可使用 IF NOT EXISTS 跳过创建。如果对象有数据，使用不同名称或修改结构。使用 DROP 删除现有对象（谨慎操作，建议备份）。检查架构更改，确保没有引用删除或重命名的对象。
sqlserver误删数据库怎么恢复
Apr 05, 2024 pm 10:39 PM
若误删 SQL Server 数据库，可采取以下步骤恢复：停止数据库活动；备份日志文件；检查数据库日志；恢复选项：从备份恢复；从事务日志恢复；使用 DBCC CHECKDB；使用第三方工具。请定期备份数据库并启用事务日志以防止数据丢失。
怎么查看sqlserver端口号
Apr 05, 2024 pm 09:57 PM
要查看 SQL Server 端口号：打开 SSMS，连接到服务器。在对象资源管理器中找到服务器名称，右键单击它，然后选择“属性”。在“连接”选项卡中，查看“TCP 端口”字段。
sqlserver服务无法启动怎么办
Apr 05, 2024 pm 10:00 PM
当 SQL Server 服务无法启动时，可采取以下步骤解决：检查错误日志以确定根本原因。确保服务帐户具有启动服务的权限。检查依赖项服务是否正在运行。禁用防病毒软件。修复 SQL Server 安装。如果修复不起作用，重新安装 SQL Server。
sqlserver数据库在哪里
Apr 05, 2024 pm 08:21 PM
SQL Server 数据库文件通常存储在以下默认位置：Windows: C:\Program Files\Microsoft SQL Server\MSSQL\DATALinux: /var/opt/mssql/data可通过修改数据库文件路径设置来自定义数据库文件位置。
Mysql tinyint(1)与tinyint(4)的区别是什么
Jun 01, 2023 am 08:31 AM
什么是tinyint(M)?先来了解下mysql中字符串类型varchar(M)和数值类型tinyint(M)的区别？字符串列类型：varchar(M)而言，M是字段中可以存储的最大字符长度，也就是说是字段长度。根据设置，当你插入超出字段长度的数据时，你很可能会收到错误提示，即使没有收到错误提示，你插入的数据也会被自动截断以适应该字段的预定义长度。所以，varchar(20)和varchar(40)是不同的，其真实反映了该字段可以存储的数据长度。数值列类型：其长度修饰符表示最大显示宽度，与该字段
sqlserver安装失败怎么样删除干净
Apr 05, 2024 pm 11:27 PM
如果 SQL Server 安装失败，可通过以下步骤清理：卸载 SQL Server删除注册表项删除文件和文件夹重启计算机
