In this article, I would like to describe several new T-SQL features available in SQL Server 2016: DROP IF EXISTS, SESSION_CONTEXT, MAXDOP, FORMATMESSAGE, COMPRESS and DECOMPARESS, DATEDIFF_BIG, AT TIME ZONE, FOR JSON and other. (more…)
In this article, we will talk about SQL Server Checkpoints.
To enhance performance, SQL Server applies modifications to database pages in memory. Often, this memory is called the buffer cache or buffer pool. SQL Server does not flush these pages to disk after every change. Instead, the database engine does checkpoint operation on each database from time to time. The CHECKPOINT operation writes the dirty pages (current in-memory modified pages) and also writes details about the transaction log. (more…)
T-SQL provides two keywords that allow you to delete data from a table. They are DELETE and TRUNCATE. These are alike in what they are doing, however, they use vastly different methods. In this article, we discuss underlying mechanisms for these keywords. (more…)
Have you ever accidentally executed the TRUNCATE command on a wrong table? This will lead to all data loss. The worst thing is that you will not have a chance to get your data back. In this article, we will have a look how to avoid such situations and have a chance to ROLLBACK TRUNCATE. (more…)