我用来查询其他点范围内的多边形的语法
SELECT *
FROM dbo.myTable
INNER JOIN
dbo.myPOI ON myTable.geog.STIntersects(myPOI.geog) = 1
但是，为了查询兴趣点，我使用了一个半径，我正在使用它。
SELECT *
FROM dbo.myTable
INNER JOIN
dbo.myPOI2 ON myTable.geog.STDistance(myPOI2.geog) <= POI2.Point_Radius
有没有更好的方法来写这个？可以改进查询吗？
我知道这不是第一次被问到这类问题。
但是为什么在以下场景中创建的持久计算列是“非确定性的”。答案应该总是一样的，对吧？
CREATE TABLE dbo.test (Id INT, EventTime DATETIME NULL, PosixTime INT NOT NULL)
GO
DECLARE @EventTime DATETIME = '20181001 12:00:00'
DECLARE @GPSTime INT = DATEDIFF(SECOND, '19700101', @EventTime)
INSERT INTO dbo.Test(Id, EventTime, PosixTime)
VALUES (1, @EventTime, @GPSTime)
, (2, NULL, @GPSTime)
GO
SELECT * FROM dbo.test
GO
ALTER TABLE dbo.test ADD UTCTime AS CONVERT(DATETIME2,ISNULL(EventTime, DATEADD(SECOND, PosixTime, CONVERT(DATE,'19700101'))),112) PERSISTED
GO
消息 4936，级别 16，状态 1，第 42 行表 'test' 中的计算列 'UTCTime' 无法持久化，因为该列是不确定的。
我想我在这里遵循确定性规则。
是否可以在这里创建一个持久计算列？
如果你更新了从未更新过的表的统计信息（不要问他们为什么从来没有更新过），SQL Server 会根据过时的统计信息自动刷新执行计划吗？如果没有，最好的方法是什么？
CREATE TABLE T1
(Asset_Id int, Trailer_Id int, AssignStart datetime, AssignEnd DATETIME)
;
INSERT INTO T1
(Asset_Id, Trailer_Id, AssignStart, AssignEnd)
VALUES
(37124, 32607, '2018-04-19 08:55:00', '2018-05-05 10:00:00.000'),
(37124, 32607, '2018-05-05 11:23:00', NULL),
(33000, 30000, '2018-04-01 15:00:00', '2018-04-15 10:30:00.000'),
(34000, 31000, '2018-04-05 10:00:00', '2018-04-10 09:30:00.000'),
(34000, 32500, '2018-04-10 09:31:00', NULL),
(37000, 32600, '2018-04-19 08:55:00', '2018-04-25 08:30:00.000'),
(37000, 32600, '2018-04-25 09:23:00', '2018-04-25 10:00:00.000'),
(37000, 32600, '2018-04-25 11:23:00', '2018-04-30 15:00:00.000'),
(37000, 32600, '2018-04-30 16:15:00', '2018-04-30 17:30:00.000'),
(37000, 32600, '2018-05-01 18:23:00', NULL),
(38000, 36000, '2018-05-01 10:00:00', '2018-05-10 06:30:00.000'),
(38000, 36000, '2018-05-15 09:00:00', '2018-05-20 11:00:00.000'),
(38000, 36000, '2018-05-20 12:00:00', NULL),
(33000, 30000, '2018-05-01 10:00:00', NULL)
;
我有以下示例数据 -
Asset_Id Trailer_Id AssignStart AssignEnd
37124 32607 2018-04-19 08:55:00.000 2018-05-05 10:00:00.000
37124 32607 2018-05-05 11:23:00.000 NULL
33000 30000 2018-04-01 15:00:00.000 2018-04-15 10:30:00.000
34000 31000 2018-04-05 10:00:00.000 2018-04-10 09:30:00.000
34000 32500 2018-04-10 09:31:00.000 NULL
37000 32600 2018-04-19 08:55:00.000 2018-04-25 08:30:00.000
37000 32600 2018-04-25 09:23:00.000 2018-04-25 10:00:00.000
37000 32600 2018-04-25 11:23:00.000 2018-04-30 15:00:00.000
37000 32600 2018-04-30 16:15:00 2018-04-30 17:30:00.000
37000 32600 2018-05-01 18:23:00 NULL
38000 36000 2018-05-01 10:00:00.000 2018-05-10 06:30:00.000
38000 36000 2018-05-15 09:00:00.000 2018-05-20 11:00:00.000
38000 36000 2018-05-20 12:00:00.000 NULL
33000 30000 2018-05-01 10:00:00.000 NULL
如您所见，资产和预告片之间的一些分配在同一天结束并再次开始 - 除了最后一行 - 差距和岛屿
示例（1）
Asset_Id Trailer_Id AssignStart AssignEnd
37000 32600 2018-04-19 08:55:00.000 2018-04-25 08:30:00.000
37000 32600 2018-04-25 09:23:00.000 2018-04-25 10:00:00.000
37000 32600 2018-04-25 11:23:00.000 2018-04-30 15:00:00.000
37000 32600 2018-04-30 16:15:00.000 2018-04-30 17:30:00.000
37000 32600 2018-05-01 18:23:00.000 NULL
我期待的这个样本的输出是
Asset_Id Trailer_Id AssignStart AssignEnd
37000 32600 2018-04-19 08:55:00.000 2018-04-30 17:30:00.000
37000 32600 2018-05-01 18:23:00.000 NULL
现在这是另一部分与间隙和岛屿。相同资产和预告片之间的某些分配已结束，然后在将来的某个日期重新开始
示例（2）
Asset_Id Trailer_Id AssignStart AssignEnd
33000 30000 2018-04-01 15:00:00.000 2018-04-15 10:30:00.000
33000 30000 2018-05-01 10:00:00.000 NULL
我期待的这个样本的输出是
Asset_Id Trailer_Id AssignStart AssignEnd
33000 30000 2018-04-01 15:00:00.000 2018-04-15 10:30:00.000
33000 30000 2018-05-01 10:00:00.000 NULL
示例（3）
Asset_Id Trailer_Id AssignStart AssignEnd
38000 36000 2018-05-01 10:00:00.000 2018-05-10 06:30:00.000
38000 36000 2018-05-15 09:00:00.000 2018-05-20 11:00:00.000
38000 36000 2018-05-20 12:00:00.000 NULL
我期待的这个样本的输出是
Asset_Id Trailer_Id AssignStart AssignEnd
38000 36000 2018-05-01 10:00:00.000 2018-05-10 06:30:00.000
38000 36000 2018-05-15 09:00:00.000 NULL
我努力编写将提供以下输出的查询
Asset_Id Trailer_Id AssignStart AssignEnd
37124 32607 2018-04-19 08:55:00.000 NULL
33000 30000 2018-04-01 15:00:00.000 2018-04-15 10:30:00.000
34000 31000 2018-04-05 10:00:00.000 2018-04-10 09:30:00.000
34000 32500 2018-04-10 09:31:00.000 NULL
37000 32600 2018-04-19 08:55:00.000 2018-04-30 17:30:00.000
37000 32600 2018-05-01 18:23:00.000 NULL
38000 36000 2018-05-01 10:00:00.000 2018-05-10 06:30:00.000
38000 36000 2018-05-15 09:00:00.000 NULL
33000 30000 2018-05-01 10:00:00.000 NULL
我正在尝试调整在 20 列上调用相同表值函数 (TVF) 的查询。
我做的第一件事是将标量函数转换为内联表值函数。
是否使用CROSS APPLY性能最佳的方式在查询中的多个列上执行相同的功能？
一个简单的例子：
SELECT Col1 = A.val
,Col2 = B.val
,Col3 = C.val
--do the same for other 17 columns
,Col21
,Col22
,Col23
FROM t
CROSS APPLY
dbo.function1(Col1) A
CROSS APPLY
dbo.function1(Col2) B
CROSS APPLY
dbo.function1(Col3) C
--do the same for other 17 columns
有更好的选择吗？
可以在针对 X 列的多个查询中调用相同的函数。
这是功能：
CREATE FUNCTION dbo.ConvertAmountVerified_TVF
(
@amt VARCHAR(60)
)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
(
WITH cteLastChar
AS(
SELECT LastChar = RIGHT(RTRIM(@amt), 1)
)
SELECT
AmountVerified = CAST(RET.Y AS NUMERIC(18,2))
FROM (SELECT 1 t) t
OUTER APPLY (
SELECT N =
CAST(
CASE
WHEN CHARINDEX(L.LastChar COLLATE Latin1_General_CS_AS, '{ABCDEFGHI}', 0) >0
THEN CHARINDEX(L.LastChar COLLATE Latin1_General_CS_AS, '{ABCDEFGHI}', 0)-1
WHEN CHARINDEX(L.LastChar COLLATE Latin1_General_CS_AS, 'JKLMNOPQR', 0) >0
THEN CHARINDEX(L.LastChar COLLATE Latin1_General_CS_AS, 'JKLMNOPQR', 0)-1
WHEN CHARINDEX(L.LastChar COLLATE Latin1_General_CS_AS, 'pqrstuvwxy', 0) >0
THEN CHARINDEX(L.LastChar COLLATE Latin1_General_CS_AS, 'pqrstuvwxy', 0)-1
ELSE
NULL
END
AS VARCHAR(1))
FROM
cteLastChar L
) NUM
OUTER APPLY (
SELECT N =
CASE
WHEN CHARINDEX(L.LastChar COLLATE Latin1_General_CS_AS, '{ABCDEFGHI}', 0) >0
THEN 0
WHEN CHARINDEX(L.LastChar COLLATE Latin1_General_CS_AS, 'JKLMNOPQRpqrstuvwxy', 0) >0
THEN 1
ELSE 0
END
FROM cteLastChar L
) NEG
OUTER APPLY(
SELECT Amt= CASE
WHEN NUM.N IS NULL
THEN @amt
ELSE
SUBSTRING(RTRIM(@amt),1, LEN(@amt) - 1) + Num.N
END
) TP
OUTER APPLY(
SELECT Y = CASE
WHEN NEG.N = 0
THEN (CAST(TP.Amt AS NUMERIC) / 100)
WHEN NEG.N = 1
THEN (CAST (TP.Amt AS NUMERIC) /100) * -1
END
) RET
) ;
GO
如果有人感兴趣，这是我继承的标量函数版本：
CREATE FUNCTION dbo.ConvertAmountVerified
(
@amt VARCHAR(50)
)
RETURNS NUMERIC (18,3)
AS
BEGIN
-- Declare the return variable here
DECLARE @Amount NUMERIC(18, 3);
DECLARE @TempAmount VARCHAR (50);
DECLARE @Num VARCHAR(1);
DECLARE @LastChar VARCHAR(1);
DECLARE @Negative BIT ;
-- Get Last Character
SELECT @LastChar = RIGHT(RTRIM(@amt), 1) ;
SELECT @Num = CASE @LastChar collate latin1_general_cs_as
WHEN '{' THEN '0'
WHEN 'A' THEN '1'
WHEN 'B' THEN '2'
WHEN 'C' THEN '3'
WHEN 'D' THEN '4'
WHEN 'E' THEN '5'
WHEN 'F' THEN '6'
WHEN 'G' THEN '7'
WHEN 'H' THEN '8'
WHEN 'I' THEN '9'
WHEN '}' THEN '0'
WHEN 'J' THEN '1'
WHEN 'K' THEN '2'
WHEN 'L' THEN '3'
WHEN 'M' THEN '4'
WHEN 'N' THEN '5'
WHEN 'O' THEN '6'
WHEN 'P' THEN '7'
WHEN 'Q' THEN '8'
WHEN 'R' THEN '9'
---ASCII
WHEN 'p' Then '0'
WHEN 'q' Then '1'
WHEN 'r' Then '2'
WHEN 's' Then '3'
WHEN 't' Then '4'
WHEN 'u' Then '5'
WHEN 'v' Then '6'
WHEN 'w' Then '7'
WHEN 'x' Then '8'
WHEN 'y' Then '9'
ELSE ''
END
SELECT @Negative = CASE @LastChar collate latin1_general_cs_as
WHEN '{' THEN 0
WHEN 'A' THEN 0
WHEN 'B' THEN 0
WHEN 'C' THEN 0
WHEN 'D' THEN 0
WHEN 'E' THEN 0
WHEN 'F' THEN 0
WHEN 'G' THEN 0
WHEN 'H' THEN 0
WHEN 'I' THEN 0
WHEN '}' THEN 1
WHEN 'J' THEN 1
WHEN 'K' THEN 1
WHEN 'L' THEN 1
WHEN 'M' THEN 1
WHEN 'N' THEN 1
WHEN 'O' THEN 1
WHEN 'P' THEN 1
WHEN 'Q' THEN 1
WHEN 'R' THEN 1
---ASCII
WHEN 'p' Then '1'
WHEN 'q' Then '1'
WHEN 'r' Then '1'
WHEN 's' Then '1'
WHEN 't' Then '1'
WHEN 'u' Then '1'
WHEN 'v' Then '1'
WHEN 'w' Then '1'
WHEN 'x' Then '1'
WHEN 'y' Then '1'
ELSE 0
END
-- Add the T-SQL statements to compute the return value here
if (@Num ='')
begin
SELECT @TempAmount=@amt;
end
else
begin
SELECT @TempAmount = SUBSTRING(RTRIM(@amt),1, LEN(@amt) - 1) + @Num;
end
SELECT @Amount = CASE @Negative
WHEN 0 THEN (CAST(@TempAmount AS NUMERIC) / 100)
WHEN 1 THEN (CAST (@TempAmount AS NUMERIC) /100) * -1
END ;
-- Return the result of the function
RETURN @Amount
END
样本测试数据：
SELECT dbo.ConvertAmountVerified('00064170') -- 641.700
SELECT * FROM dbo.ConvertAmountVerified_TVF('00064170') -- 641.700
SELECT dbo.ConvertAmountVerified('00057600A') -- 5760.010
SELECT * FROM dbo.ConvertAmountVerified_TVF('00057600A') -- 5760.010
SELECT dbo.ConvertAmountVerified('00059224y') -- -5922.490
SELECT * FROM dbo.ConvertAmountVerified_TVF('00059224y') -- -5922.490
有什么办法可以加快重新启用约束的速度吗？
作为更新我们仓库中几个表的工作的一部分，这是在非工作时间完成的，重新启用对所述表的约束可能需要一个多小时。剩下的工作，更新，大约需要 5 分钟。在更新开始之前禁用相同的约束是亚秒级的。
编辑
有问题的工作是在给定日期之前更新数据行的 PK 和 FK，以防止这些记录出现在报告中。soft delete如果您愿意，可以使用一种记录方式。
除了一个超过 370m 行的表外，大多数表的行大小都大于 130m 行。另外两个较小。
TableName rows
Table_1 371255778
Table_2 131703902
Table_3 131665535
Table_4 131665535
Table_5 131665535
Table_6 131665535
Table_7 19364988
Table_8 1458800
我正在使用以下命令禁用每个表上的约束
ALTER TABLE database.dbo.table_name NOCHECK CONSTRAINT ALL;
以及以下再次重新启用它们
ALTER TABLE database.dbo.table_name WITH CHECK CHECK CONSTRAINT ALL;
没有报告或其他作业同时运行。
重新启用约束预期行为需要这么长时间，还是可以采取一些措施来加快它的速度？
提前致谢
我在尝试将 SSMS 的上下文动态更改为动态 SQL 中指定的数据库时遇到问题：
EXEC sys.sp_executesql N'USE db1 ' ;
它成功执行，但是 SSMS 的数据库上下文没有改变。
我已经尝试对上面的内容进行轻微修改，如下所示
DECLARE @sql NVARCHAR(100) DECLARE @db NVARCHAR(50)
SET @db = N'db1' SET @sql = N'Use ' + @db
EXEC sp_executesql @sql
再次，它成功执行，但数据库没有改变。
SQL Server 2008r2
我正在尝试删除我创建的证书，但出现以下错误
无法删除证书，因为一个或多个实体已使用它进行签名或加密
使用这个查询我已经确定了有问题的实体
SELECT SCHEMA_NAME(so.[schema_id]) AS [SchemaName],
so.[name] AS [ObjectName],
so.[type_desc] AS [ObjectType],
---
scp.crypt_type_desc AS [SignatureType],
ISNULL(sc.[name], sak.[name]) AS [CertOrAsymKeyName],
---
scp.thumbprint
FROM sys.crypt_properties scp
INNER JOIN sys.objects so
ON so.[object_id] = scp.[major_id]
LEFT JOIN sys.certificates sc
ON sc.thumbprint = scp.thumbprint
LEFT JOIN sys.asymmetric_keys sak
ON sak.thumbprint = scp.thumbprint
WHERE so.[type] <> 'U'
AND ISNULL(sc.[name], sak.[name]) = 'Certificate_name'
ORDER BY [SchemaName], [ObjectType], [ObjectName], [CertOrAsymKeyName];
但是当我尝试使用以下语句删除签名时：
DROP SIGNATURE FROM OBJECT::dbo.sp_sqlagent_notify BY CERTIFICATE Certificate_name
我让他们跟随错误。
证书“Certificate_name”的签名不存在。
我究竟做错了什么？
提前致谢。
SQL Server 2008 R2
为什么使用or将 a 转换FLOAT为字符串 ( Varchar/ Nvarchar)时会发生舍入或截断？CastConvert
使用CAST
DECLARE @floatVal FLOAT = 4.76758527755737
SELECT
CAST(@floatVal AS VARCHAR(20)) 'VARCHAR',
CAST(@floatVal AS NVARCHAR(20)) 'NVARCHAR'
输出
VARCHAR NVARCHAR
4.76759 4.76759
使用CONVERT：
DECLARE @floatVal FLOAT = 4.76758527755737
SELECT
CONVERT(VARCHAR(20), @floatVal) 'VARCHAR',
CONVERT(NVARCHAR(20), @floatVal) 'NVARCHAR'
输出
VARCHAR NVARCHAR
4.76759 4.76759
但是，STR没有截断/舍入
DECLARE @floatVal FLOAT = 4.76758527755737
SELECT
LTRIM(STR(@floatVal, 17, 14)) 'STR'
输出
STR
4.76758527755737
提前致谢
我为 GMT 区域创建了一个“夏令时”查找日历表。我用来查询表以从 UTC 日期时间返回本地日期时间的函数性能不佳。
任何有助于改善这一点的帮助，包括改变 TVF 的编码方式，都将不胜感激。
该函数将用于可以频繁返回 1m+ 行的查询。该函数用于查询包含行程数据的仓库表。
行程的开始和结束日期时间存储在 UTC 中，上面的函数用于将它们转换为本地时间。一位离开公司很久的开发人员编写了一个将 UTC 时间转换为本地时间的标量函数。我的任务是使用日历表和 TVF 重写该函数，因为 TVF 应该比标量函数表现更好
没有功能：
SQL Server Execution Times: CPU time = 4633 ms, elapsed time = 4909 ms.
具有以下功能：
SQL Server Execution Times: CPU time = 20795 ms, elapsed time = 21176 ms.
这是表格的示例输出
CREATE TABLE dbo.DSTLookup
(
[Id] int,
[Tzid] int,
[DT_WhenSwitch] datetime,
[DSTOffSetSeconds] int,
[GMTOffSetSeconds] int
)
INSERT INTO dbo.DSTLookup
VALUES (29, 2, N'2014-03-30T01:00:00', 3600, 0),
(30, 2, N'2014-10-26T02:00:00', 0, 0),
(31, 2, N'2015-03-29T01:00:00', 3600, 0),
(32, 2, N'2015-10-25T02:00:00', 0, 0),
(33, 2, N'2016-03-27T01:00:00', 3600, 0),
(34, 2, N'2016-10-30T02:00:00', 0, 0),
(35, 2, N'2017-03-26T01:00:00', 3600, 0),
(36, 2, N'2017-10-29T02:00:00', 0, 0),
(37, 2, N'2018-03-25T01:00:00', 3600, 0),
(38, 2, N'2018-10-28T02:00:00', 0, 0)
这是 TVF：
CREATE FUNCTION dbo.FN_GetLocalTime_FromUTC_BasedOnTZId
(@StartDateTime DATETIME, @EndDateTime DATETIME, @Tzid INT)
/*=========================================================================
* 2017-03-27
* Returns local time from UTC time based on timeZoneId
*
==========================================================================*/
RETURNS TABLE
AS
RETURN
(
WITH cteStartDate AS
(
SELECT
RN = ROW_NUMBER() OVER (ORDER BY D.Id DESC),
D.DSTOffSetSeconds 's_DST_OffSet',
D.GMTOffSetSeconds 's_GMT_OffSet'
FROM
dbo.DSTLookup D
WHERE
D.DT_WhenSwitch <= @StartDateTime
AND D.Tzid = @Tzid
),
cteEndDate AS
(
SELECT
RN = ROW_NUMBER() OVER (ORDER BY D.Id DESC),
D.DSTOffSetSeconds 'e_DST_OffSet',
D.GMTOffSetSeconds 'e_GMT_OffSet'
FROM
dbo.DSTLookup D
WHERE
D.DT_WhenSwitch <= @EndDateTime
AND D.Tzid = @Tzid
),
cteConvertStartDate AS
(
SELECT
DATEADD(SECOND, (COALESCE(S.s_DST_OffSet, 0) + COALESCE(S.s_GMT_OffSet, 0)), @StartDateTime) 'LocalStartDateTime'
FROM
cteStartDate S
WHERE
S.RN = 1
),
cteConvertEndDate AS
(
SELECT
DATEADD(SECOND, (COALESCE(E.e_DST_OffSet, 0) + COALESCE(E.e_GMT_OffSet, 0)), @EndDateTime) 'LocalEndDateTime'
FROM
cteEndDate E
WHERE
E.RN = 1
)
SELECT
S.LocalStartDateTime, E.LocalEndDateTime
FROM
cteConvertStartDate S, cteConvertEndDate E
);
GO
查询 TVF：
SELECT *
FROM dbo.FN_GetLocalTime_FromUTC_BasedOnTzId
('2017-03-27 10:00:30', '2017-03-27 10:15:54', 2);
执行计划遵循 Max 的建议以包含主键。