CHECKPOINT in SQL Server

CHECKPOINT is an internal SQL Server process that runs periodically to write dirty pages (changed pages) and transaction log records from memory to disk. It then marks a point in…

Read more »

Identifying cached tables in SQL Server buffer

When a query is submitted it goes through a sequence of phases in SQL Server. Starting from parse, optimization, plan preparation until the execution of the plan. During the execution…

Read more »

When did the last statistics update occur?

Statistics are the critical piece of information that SQL Server maintains for a table or index. The query optimization process depends on the statistics information to prepare an execution plan…

Read more »

DBCC HELP!!

SQL Server has many DBCC commands, administrators use them for maintenance, validation and to collect information about the database, for example, DBCC SHOW_STATISTICS displays statistics for a table. But there…

Read more »

What does CHECK OPTION do in a View

When CHECK OPTION specified in a view definition, all the data modification and insertion through the view will be forced to qualify the view’s filter criteria. I will use the…

Read more »

How to rename column name of a table in SQL Server

The system stored procedure “sp_rename” is used for renaming user-created objects in the database. The following code is renaming the column “empdoj” to “empjoindate” in “emp” table. The first parameter…

Read more »

Why weekday and week is non-deterministic in SQL Server

The DATEPART function returns an integer value for the given date part. The date part could be the year,  quarter, month, week, day etc., A deterministic function should return the…

Read more »

UNIQUIFIER – The hidden column in the SQL Server table

A four-byte internal column is automatically created by SQL Server when clustered index created on a non-unique column. This column is referred to as UNIQUIFIER. A clustered index can be…

Read more »

DATALENGTH vs LEN function in SQL

DATALENGTH function returns the size used by a column or an expression whereas the LEN function returns the number of characters. Let’s explore with few examples The DATALENGTH function returns…

Read more »

Uses of OUTPUT clause in SQL Server

The output clause introduced in SQL Server 2005 is handy and it simplifies the coding for different scenarios. It returns inserted, deleted and updated records to the client application. Employee…

Read more »