A sintaxe que uso para consultar polígonos no intervalo de outros pontos
SELECT *
FROM dbo.myTable
INNER JOIN
dbo.myPOI ON myTable.geog.STIntersects(myPOI.geog) = 1
Mas, para consultar pontos de interesse, eu uso um raio e estou usando isso.
SELECT *
FROM dbo.myTable
INNER JOIN
dbo.myPOI2 ON myTable.geog.STDistance(myPOI2.geog) <= POI2.Point_Radius
Existe uma maneira melhor de escrever isso? As consultas podem ser melhoradas?
Eu sei que não é a primeira vez que esse tipo de pergunta é feita.
Mas por que no cenário a seguir a coluna computada persistente está sendo criada "não determinística". A resposta deve ser sempre a mesma, certo?
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
Msg 4936, Level 16, State 1, Line 42 A coluna computada 'UTCTime' na tabela 'test' não pode ser mantida porque a coluna não é determinística.
Acho que estou seguindo as regras determinísticas aqui .
É possível criar uma coluna computada persistente aqui?
Se você atualizar as estatísticas em tabelas que nunca foram atualizadas (não pergunte por que elas nunca foram atualizadas), o SQL Server atualiza automaticamente os planos de execução com base nas estatísticas desatualizadas? Se não, qual é a melhor abordagem para fazê-lo?
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)
;
Eu tenho os seguintes dados de exemplo -
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
Como você pode ver, algumas das atribuições entre Ativos e Trailers são encerradas e iniciadas novamente no mesmo dia - exceto a última linha - Gaps e Islands
Exemplo 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
A saída para esta amostra que estou esperando é
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
Agora esta é outra seção com as lacunas e ilhas. Algumas das atribuições entre os mesmos ativos e trailers são encerradas e iniciadas novamente em uma data futura
Exemplo (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
A saída para esta amostra que estou esperando é
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
Exemplo (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
A saída para esta amostra que estou esperando é
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
Estou lutando para escrever uma consulta que fornecerá a seguinte saída
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
Usando a resposta desta resposta SO como base, esta é minha tentativa , não está certo
Estou tentando ajustar uma consulta em que a mesma função com valor de tabela (TVF) é chamada em 20 colunas.
A primeira coisa que fiz foi converter a função escalar em uma função inline com valor de tabela.
Está usando CROSS APPLYa maneira de melhor desempenho para executar a mesma função em várias colunas em uma consulta?
Um exemplo simplista:
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
Existem alternativas melhores?
A mesma função pode ser chamada em várias consultas em um número X de colunas.
Aqui está a função:
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
Aqui está a versão da função escalar que eu herdei, se alguém estiver interessado:
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
Dados de teste de amostra:
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
Existe alguma maneira de acelerar a reativação de restrições?
Como parte de um trabalho de atualização de várias tabelas em nosso Warehouse, que está sendo feito fora do expediente, a reativação de restrições nessas tabelas pode levar mais de uma hora. O resto do trabalho, as atualizações, levam cerca de 5 minutos. Desabilitar as mesmas restrições antes do início das atualizações é subsegundo.
EDITAR
O trabalho em questão é atualizar os PKs e FKs em linhas de dados antes de uma determinada data para evitar que esses registros apareçam nos relatórios. Uma maneira de soft deleteregistros `se você gosta.
A maioria dos tamanhos de linha da tabela são > 130 m de linhas cada, exceto uma que tem mais de 370 m de linhas. Os outros dois são menores.
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
Estou usando o seguinte comando para desabilitar as restrições em cada tabela
ALTER TABLE database.dbo.table_name NOCHECK CONSTRAINT ALL;
E o seguinte para reativá-los novamente
ALTER TABLE database.dbo.table_name WITH CHECK CHECK CONSTRAINT ALL;
não há relatórios ou outros trabalhos em execução ao mesmo tempo.
Está demorando tanto para reativar o comportamento esperado das restrições ou há algo que pode ser feito para acelerá-lo?
desde já, obrigado
Estou tendo problemas ao tentar alterar dinamicamente o contexto do SSMS para o banco de dados especificado no SQL dinâmico:
EXEC sys.sp_executesql N'USE db1 ' ;
Ele é executado com sucesso, mas o contexto do banco de dados do SSMS não muda.
Eu tentei uma pequena modificação no acima assim
DECLARE @sql NVARCHAR(100) DECLARE @db NVARCHAR(50)
SET @db = N'db1' SET @sql = N'Use ' + @db
EXEC sp_executesql @sql
Novamente, ele é executado com sucesso, mas o banco de dados não muda.
SQL Server 2008r2
Estou tentando descartar um certificado que criei, mas recebo o seguinte erro
O certificado não pode ser descartado porque uma ou mais entidades são assinadas ou criptografadas usando-o
Usando esta consulta , identifiquei as entidades em questão
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];
mas quando tento descartar as ASSINATURAS com a seguinte declaração:
DROP SIGNATURE FROM OBJECT::dbo.sp_sqlagent_notify BY CERTIFICATE Certificate_name
Eu os recebo após o erro.
Uma assinatura por certificado 'Nome_do_certificado' não existe.
O que estou fazendo errado?
Desde já, obrigado.
SQL Server 2008 R2
Por que ao converter a FLOATem uma string ( Varchar/ Nvarchar) usando Castou Convert, ocorre um arredondamento ou truncamento?
UsandoCAST
DECLARE @floatVal FLOAT = 4.76758527755737
SELECT
CAST(@floatVal AS VARCHAR(20)) 'VARCHAR',
CAST(@floatVal AS NVARCHAR(20)) 'NVARCHAR'
Resultado
VARCHAR NVARCHAR
4.76759 4.76759
Usando CONVERT:
DECLARE @floatVal FLOAT = 4.76758527755737
SELECT
CONVERT(VARCHAR(20), @floatVal) 'VARCHAR',
CONVERT(NVARCHAR(20), @floatVal) 'NVARCHAR'
Resultado
VARCHAR NVARCHAR
4.76759 4.76759
No STRentanto, não há truncamento/arredondamento
DECLARE @floatVal FLOAT = 4.76758527755737
SELECT
LTRIM(STR(@floatVal, 17, 14)) 'STR'
Resultado
STR
4.76758527755737
desde já, obrigado
Criei uma tabela de calendário de pesquisa "Daylight Savings" para a região GMT. A função que estou usando para consultar a tabela para retornar a data e hora local de uma data e hora UTC está tendo um desempenho ruim.
Qualquer ajuda para melhorar isso, incluindo mudar a forma como o TVF é codificado, seria apreciada.
A função será usada em consultas que podem retornar mais de 1 milhão de linhas com frequência. A função é usada ao consultar as tabelas do armazém que contêm dados de viagem.
As datas de início e término das viagens são armazenadas em UTC e a função acima é usada para convertê-las em hora local. Um desenvolvedor, há muito afastado da empresa, escreveu uma função escalar que converte a hora UTC para a hora local. Fui encarregado de reescrever essa função usando uma tabela de calendário e um TVF, pois o TVF deve ter um desempenho melhor do que as funções escalares
Sem a função:
SQL Server Execution Times: CPU time = 4633 ms, elapsed time = 4909 ms.
Com a função:
SQL Server Execution Times: CPU time = 20795 ms, elapsed time = 21176 ms.
Aqui está um exemplo de saída da tabela
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)
Este é o 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
Para consultar o TVF:
SELECT *
FROM dbo.FN_GetLocalTime_FromUTC_BasedOnTzId
('2017-03-27 10:00:30', '2017-03-27 10:15:54', 2);
Plano de execução seguindo as recomendações de Max para incluir a chave primária.