New answers tagged

0

The main thing you need is an ordering of the rows, since rows are unordered per se. Once you have an ordering, you can use the window function lag to access the previous row in that ordering. Assuming that your rows are ordered by tstamp, that would look like SELECT lat, lon, lag(lat) OVER (ORDER BY tstamp) AS prev_lat, lag(lon) OVER (...


0

Why am I getting this error in this scenario? The agent itself is switching QUOTED_IDENTIFIER to OFF. I have written about it here (mind the unreadable font, not my choice). Here are the tests run on sql server 2012 to sql server 2017: If I run this query in a ssms query window: DECLARE @QUOTED_IDENTIFIER VARCHAR(3) = 'OFF'; IF ( (256 & @@OPTIONS) ...


0

Since there are already good answers to the question I will focus on the DRY-aspect (DRY = don't repeat yourself). I got used to use CROSS APPLY, if I have to do the same calculations in the same query multiple times (don't forget GROUP BY / WHERE / ORDER BY, where the same calcualtions tend to occure again and again and again). SELECT calc.ColA, ...


-1

If both Expressions are deterministic, the optimizer will reuse the previously calculated results and simply add them. If either expression is nondeterministic, that expression MUST be recalculated.


1

As Vérace suggested, you can do both: create a surrogate (auto-increment) key to simplify joins and related table structures, and add a unique constraint on the two current PK columns to continue to enforce the proper data integrity. In SQL Server, multiple columns can participate in a unique index / constraint: ALTER TABLE dbo.Orders ADD CONSTRAINT ...


Top 50 recent answers are included