How to find the Service Account of SQL Server Service

Dynamic management views are very much useful when it comes to troubleshooting or collecting server and database related configuration information. The sys.dm_server_services provides the information about the service name and…

Read more »

Data file not deleted from filesystem even after DROP DATABASE

When a database is dropped using the DROP DATABASE command, you might have noticed the command completed successfully and of course, the database is dropped but you could still see…

Read more »

Identifying unused non clustered index

Having an Index on a table will indeed be good for the performance of queries. At the same time having too many indexes on a table will hurt the query…

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 »

Currently executing SQL statements

Microsoft SQL Server introduced various dynamic management views and dynamic management functions to view the server and database state and it is very helpful for troubleshooting, performance tuning, monitoring and…

Read more »

How to find the physical location of locked records in SQL Server

SQL Server by default locks the table or page or rows when it executes the transact statement. There are different types of locks such as exclusive, shared, schema modification lock…

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 »