Access the “previous row” value in a SELECT statement
Use the lag function: SELECT value – lag(value) OVER (ORDER BY Id) FROM table Sequences used for Ids can skip values, so Id-1 does not always work.
sql-server
Is it possible to insert into two tables at the same time?
In one statement: No. In one transaction: Yes BEGIN TRANSACTION DECLARE @DataID int; INSERT INTO DataTable (Column1 …) VALUES (….); SELECT @DataID = scope_identity(); INSERT INTO LinkTable VALUES (@ObjectID, @DataID); COMMIT The good news is that the above code is also guaranteed to be atomic, and can be sent to the server from a client … Read more
What is the difference between JOIN and INNER JOIN?
They are functionally equivalent, but INNER JOIN can be a bit clearer to read, especially if the query has other join types (i.e. LEFT or RIGHT or CROSS) included in it.
How can I quickly identify most recently modified stored procedures in SQL Server
instead of using sysobjects which is not recommended anymore use sys.procedures select name,create_date,modify_date from sys.procedures order by modify_date desc you can do the where clause yourself but this will list it in order of modification date descending
How liberal should I be with NOT NULL columns?
Honestly, I’ve always thought NOT NULL should be the default. NULL is the odd special case, and you should make a case for it whenever you use it. Plus it’s much easier to change a column from NOT NULL to nullable than it is to go the other way.
SQL problem with error “Invalid data type”
I know this is an old post but since I encountered the same issue and was able to solve it, thought of sharing it. This is an IntelliSense cache issue and could be solved by pressing ctrl+shift+R (shortcut for Edit -> IntelliSense -> Refresh Local Cache)
SSDT Schema Compare add blank lines to the schema files
Not really a workaround or a solution, but I remember having this issue on Visual Studio 2017 and the issue stopped arising when I upgraded to Visual Studio 2019.
Storing query results into a variable and modifying it inside a Stored Procedure
Yup, this is possible of course. Here are several examples. — one way to do this DECLARE @Cnt int SELECT @Cnt = COUNT(SomeColumn) FROM TableName GROUP BY SomeColumn — another way to do the same thing DECLARE @StreetName nvarchar(100) SET @StreetName = (SELECT Street_Name from Streets where Street_ID = 123) — Assign values to several … Read more
How to set collation of a column with SQL?
To change the database’s collation ALTER DATABASE MyDataBase COLLATE [NewCollation] To change the collation of a column ALTER TABLE MyTable ALTER COLUMN Column1 [TYPE] COLLATE [NewCollation] But there are a number of limitations on when you can do this, very notably that this is denied if the column is used in any index. You can … Read more
How to check if change tracking is enabled
You can use this query: SELECT * FROM sys.change_tracking_databases WHERE database_id=DB_ID(‘MyDatabase’)