标签 SQL Server 下的文章

示例如何在sqlserver数据表中查找 10km 内的商家

作者: Wang
时间: 2025-04-24
分类: IT
评论

以下是在 SQL Server 中查找 10公里内商家 的完整示例，包含 表结构设计、空间索引创建 和 查询优化方法：

1. 数据表结构设计

-- 创建商家表，包含地理坐标列（Geography类型）
CREATE TABLE Merchants (
    MerchantID INT PRIMARY KEY IDENTITY(1,1),
    MerchantName NVARCHAR(100),
    Location GEOGRAPHY,  -- 使用Geography类型存储坐标
    Address NVARCHAR(200)
);

-- 插入示例数据（WGS84经纬度，SRID=4326）
INSERT INTO Merchants (MerchantName, Location, Address)
VALUES 
    ('商家A', GEOGRAPHY::Point(31.2304, 121.4737, 4326),   -- 上海坐标
    ('商家B', GEOGRAPHY::Point(31.2243, 121.4758, 4326),   -- 上海附近
    ('商家C', GEOGRAPHY::Point(39.9042, 116.4074, 4326);  -- 北京坐标（超出10公里）

2. 创建空间索引（关键性能优化）

CREATE SPATIAL INDEX SIndx_Merchants_Location 
ON Merchants(Location)  
USING GEOGRAPHY_GRID  
WITH (
  GRIDS = (MEDIUM, MEDIUM, MEDIUM, MEDIUM),  
  CELLS_PER_OBJECT = 16
);

3. 查询10公里内的商家

方法1：直接使用 `STDistance`

-- 定义用户当前位置（例如：上海中心坐标）
DECLARE @UserLocation GEOGRAPHY = GEOGRAPHY::Point(31.2304, 121.4737, 4326);

-- 查询10公里内的商家
SELECT 
    MerchantID,
    MerchantName,
    Address,
    @UserLocation.STDistance(Location) AS DistanceInMeters
FROM Merchants
WHERE 
    Location.STDistance(@UserLocation) <= 10000  -- 10公里 = 10,000米
ORDER BY DistanceInMeters;

方法2：利用空间索引优化范围过滤

-- 使用空间函数快速过滤大致范围（提高性能）
SELECT 
    MerchantID,
    MerchantName,
    Address,
    @UserLocation.STDistance(Location) AS DistanceInMeters
FROM Merchants
WHERE 
    Location.STIntersects(@UserLocation.STBuffer(10000)) = 1  -- 先筛选地理范围
    AND Location.STDistance(@UserLocation) <= 10000          -- 再精确计算
ORDER BY DistanceInMeters;

4. 查询结果示例

MerchantID	MerchantName	Address	DistanceInMeters
1	商家A	上海某地址	0.0 (同一坐标)
2	商家B	上海附近地址	约 800 米

关键说明

单位处理
- STDistance 返回单位为米，直接比较 <= 10000 表示10公里。
- 若需输入公里，需在代码中转换（如 10000 = 10 * 1000）。
空间索引的作用
- 空间索引会将地理数据分块（GRIDS），显著加速范围过滤（尤其是数据量大的场景）。
- 方法2通过 STBuffer(10000) 生成一个10公里的圆形区域，先用索引快速筛选可能的目标，再精确计算距离。
性能对比
- 无索引：全表扫描，计算每条记录的 STDistance，性能随数据量线性下降。
- 有索引：通过空间索引快速排除无关区域，性能提升可达数十倍。

5. 常见问题处理

坐标格式验证
确保插入的经纬度值合法（纬度范围：-90~90，经度范围：-180~180）。
跨半球计算
SQL Server 的 Geography 类型自动处理地球曲率和国际日期变更线，无需额外代码。

动态参数传递
可将用户坐标参数化（如通过应用程序传入），避免SQL注入：

-- C# 示例参数化代码
SqlCommand cmd = new SqlCommand(
  "SELECT ... WHERE Location.STDistance(@UserLocation) <= 10000",
  connection);
cmd.Parameters.Add("@UserLocation", SqlDbType.Udt).Value = userGeoPoint;

通过上述方法，您可以高效地在 SQL Server 中实现地理围栏查询，适用于O2O、LBS等场景。

关于SqlServer中的锁（仅供参考，可能有不准确的地方）

作者: Wang
时间: 2024-11-17
分类: IT
评论

共享锁 (Shared Lock):

表示一个事务正在读取一行数据，其他事务也可以读取同一行数据，但不能进行写操作。
也称为 "S锁" 或 "读锁"。

典型应用场景：当一个事务需要读取数据而不修改它时，可以使用共享锁。多个事务可以同时获取共享锁，并且彼此之间不会产生冲突。

-- 在事务中获取共享锁
BEGIN TRANSACTION;
SELECT * FROM table_name WITH (TABLOCKX, HOLDLOCK) WHERE column_name = 'value';
-- 其他事务可以读取同一行的数据，但不能进行写操作
COMMIT;

排他锁 (Exclusive Lock):

表示一个事务正在对一行数据进行写操作，并阻止其他事务对同一行进行读或写操作。
也称为 "X锁" 或 "写锁"。

典型应用场景：当一个事务需要对数据进行修改或删除操作时，可以使用排他锁。该锁会阻止其他事务对同一行进行读取或写入，确保数据的一致性。

-- 在事务中获取排他锁
BEGIN TRANSACTION;
SELECT * FROM table_name WITH (XLOCK) WHERE column_name = 'value';
-- 其他事务无法读取或写入同一行的数据
COMMIT;

更新锁 (Update Lock):

用于实现读取操作和避免并发更新冲突的特殊锁定模式。当一个事务使用更新锁时，其他事务也可以读取同一行数据，但不能进行写操作。
更新锁是共享锁和排他锁之间的折衷选择。
也称为 "U锁" 或 "升级锁"。

典型应用场景：在读取数据时，如果事务预计可能在稍后需要对数据进行更新操作，但目前仅需要共享访问权限，可以使用更新锁。这样可以避免读取和更新之间的竞争条件，提高并发性。

-- 在事务中获取更新锁
BEGIN TRANSACTION;
SELECT * FROM table_name WITH (UPDLOCK) WHERE column_name = 'value';
-- 其他事务可以读取同一行的数据，但不能进行写操作
COMMIT;

表锁 (Table Lock):

锁定整个表，阻止其他事务对该表的读或写操作。
表级锁可能对并发性产生较大影响，因为它限制了其他事务对表的访问。

典型应用场景：当需要对整个表进行大规模的操作，如数据重建、表结构修改等，可以使用表锁。这会阻止其他事务对表进行读取或写入操作，确保操作的完整性。

-- 在事务中获取表锁
BEGIN TRANSACTION;
SELECT * FROM table_name WITH (TABLOCKX) WHERE column_name = 'value';
-- 其他事务无法读取或写入整个表
COMMIT;

页级锁 (Page Lock):

锁定表的数据页，即一组相邻的数据行。
页级锁通常用于较大的事务或特定的锁定提示。

典型应用场景：在某些情况下，表中的数据按页组织，而不是按行组织。当需要访问特定数据页时，可以使用页级锁。这可以减少锁的粒度，提高并发性能。

-- 在事务中获取页级锁
BEGIN TRANSACTION;
SELECT * FROM table_name WITH (PAGLOCK) WHERE column_name = 'value';
-- 其他事务无法读取或写入同一数据页的数据
COMMIT;

行级锁 (Row Lock):

锁定表的单个数据行。
行级锁提供了最细粒度的并发控制，但也可能导致更多的锁开销和资源争用。

典型应用场景：当需要对表中的特定行进行操作时，可以使用行级锁。这允许并发事务对不同行进行读取和写入操作，提高并发性能。

-- 在事务中获取行级锁
BEGIN TRANSACTION;
SELECT * FROM table_name WITH (ROWLOCK) WHERE column_name = 'value';
-- 其他事务无法读取或写入同一行的数据
COMMIT;

此外，还存在其他一些重要的锁类型，包括：

键值锁 (Key-Range Lock):

锁定表中的一定范围的键值，通常与范围查询和索引操作相关。
键值锁用于防止并发操作引起的范围扫描和索引不一致性。

典型应用场景：当进行范围查询或索引操作时，可以使用键值锁。它可以锁定一定范围的键值，以确保范围扫描的一致性。

-- 在事务中获取键值锁
BEGIN TRANSACTION;
SELECT * FROM table_name WITH (XLOCK) WHERE column_name BETWEEN 'value1' AND 'value2';
-- 其他事务无法读取或写入指定键值范围内的数据
COMMIT;

意向锁 (Intent Lock):

用于指示一个事务已经在某一级别上持有锁，以阻止其他事务对更精细级别的锁定进行操作。
意向锁包括意向共享锁 (IS) 和意向排他锁 (IX)。

典型应用场景：意向锁用于指示事务在特定级别上持有锁，以避免其他事务获取冲突的更细粒度锁。它通常与其他锁类型结合使用。

-- 在事务中获取意向锁
BEGIN TRANSACTION;
SELECT * FROM table_name WITH (IS) WHERE column_name = 'value';
-- 其他事务无法获取排他锁（X锁）
COMMIT;

共享更新锁 (Shared Update Lock):

允许多个事务同时获取对同一数据行的共享更新锁，以支持并发读取和更新操作。

典型应用场景：当多个事务需要同时读取并更新同一数据行时，可以使用共享更新锁。这允许多个事务同时获取对同一数据行的共享更新锁，以支持并发读取和更新操作。

-- 在事务中获取共享更新锁
BEGIN TRANSACTION;
SELECT * FROM table_name WITH (SIXU) WHERE column_name = 'value';
-- 允许多个事务同时获取对同一数据行的共享更新锁，支持并发读取和更新操作
COMMIT;

这些是 SQL Server 中的一些常见和重要的锁类型，用于控制并发性和维护数据完整性。在设计和优化数据库应用程序时，了解不同类型的锁以及它们的行为和影响是至关重要的。

上述9种锁的横向对比：
锁类型并发性锁定粒度锁定范围冲突适用场景
共享锁 (Shared Lock) 高行级仅限读读冲突并发读取，不修改数据
排他锁 (Exclusive Lock) 低行级整个行读写冲突数据修改，防止其他事务读取或写入同一行数据
更新锁 (Update Lock) 中行级仅限读写冲突并发读取，预计稍后可能需要对数据进行更新
表锁 (Table Lock) 低表级整个表读写冲突大规模操作，如数据重建、表结构修改等
页级锁 (Page Lock) 中页级数据页读写冲突针对按页组织的数据，减少锁的粒度以提高并发性能
行级锁 (Row Lock) 高行级单个数据行读写冲突针对特定行的读写操作
键值锁 (Key-Range Lock) 中键值范围指定键值范围读写冲突范围查询或索引操作
意向锁 (Intent Lock) - 数据结构级整个数据结构冲突解决协调低层次锁定请求的锁
共享更新锁 (Shared Update Lock) 高行级单个数据行读冲突并发读取和更新同一行

注意：表格中的并发性指的是该锁对并发读写操作的支持程度，高表示较好的并发性能，低表示较差的并发性能。锁定粒度指的是锁定的对象粒度，可以是行、页或表级别。锁定范围指的是锁定的数据范围，可以是单个行、整个表或键值范围等。冲突表示该锁类型与其他锁类型之间可能发生的冲突。适用场景指的是每种锁类型常见的使用场景。

请注意，锁的选择取决于具体的业务需求和并发控制策略。在实际应用中，需要根据具体情况选择适当的锁

在SQL Server中，有几种不同的排他锁类型。以下是常见的排它锁：

XLOCK：
XLOCK 是一种行级排它锁，用于阻止其他事务对同一行数据进行读取或写入操作。
当一个事务使用 XLOCK 锁定一行数据时，其他事务无法同时读取或写入相同的数据行。
TABLOCKX：
TABLOCKX 是一种表级排它锁，用于阻止其他事务对整个表进行读取或写入操作。
当一个事务使用 TABLOCKX 锁定一张表时，其他事务无法同时读取或写入相同的表。
HOLDLOCK：
HOLDLOCK 是一种保持锁的提示，用于在整个事务期间保持排它锁。
当一个事务使用 HOLDLOCK 提示时，它会保持已经获取的锁，直到事务结束。
UPDLOCK：
UPDLOCK 是一种特殊的锁定提示，用于实现读取操作和避免并发更新冲突。
当一个事务使用 UPDLOCK 提示时，其他事务可以读取相同的数据行，但不能对其进行写操作。
这些排它锁类型可以根据具体的业务需求和并发控制策略进行选择和使用。它们提供了不同的锁定粒度和锁定范围，用于控制对数据的访问和修改。需要注意的是，在使用排它锁时，应注意锁的粒度和范围，避免过度锁定导致并发性能下降。

TABLOCKX 和 XLOCK在功能和使用上的区别：

TABLOCKX:
- TABLOCKX 是一种表级排他锁，用于阻止其他事务对整个表进行读取或写入操作。
- 当一个事务使用 TABLOCKX 提示时，其他事务无法同时读取或写入相同的表。
- TABLOCKX 是一种较强制的锁定方式，它会阻止其他事务对整个表的任何访问，包括读取和写入操作。
- TABLOCKX 通常在需要对整个表进行大规模修改或操作时使用，例如数据重建、表重命名等。
XLOCK:
- XLOCK 是一种行级排他锁，用于阻止其他事务对同一行数据进行读取或写入操作。
- 当一个事务使用 XLOCK 提示时，其他事务无法同时读取或写入相同的数据行。
- XLOCK 是一种较强制的锁定方式，它会阻止其他事务对同一行数据的任何访问，包括读取和写入操作。
- XLOCK 通常在需要对特定行进行修改或操作时使用，例如数据更新、删除等。

总结来说，TABLOCKX 是一种表级排他锁，阻止其他事务对整个表进行访问，而 XLOCK 是一种行级排他锁，阻止其他事务对特定行数据进行访问。选择使用哪种锁取决于具体的业务需求和并发控制策略。需要注意的是，过度使用强制性的锁定提示可能会导致并发性能下降，因此应该谨慎使用。

来源： Ceri的Blog
参考： MSDN

SQL Server服务器非正常重启后跳号的问题

作者: Wang
时间: 2024-03-31
分类: IT
评论

在配置管理器里，添加启动参数：

-t272

修改SQLServer Management Studio的历史链接

作者: Wang
时间: 2023-03-15
分类: IT
评论

C:\Users\[用户名]\AppData\Roaming\Microsoft\SQL Server Management Studio\版本号\UserSettings.xml

SQL Server 2019 (15.x)的版本和受支持的功能

作者: Wang
时间: 2022-10-28
分类: IT
评论

SQL Server 2019 (15.x)的版本和受支持的功能