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 »Using constraint improves query performance
We know that the constraints are used to enforce the data integrity. A CHECK constraint is used to limit the value being entered into a column, similarly, unique constraint is…
Read more »Table Value Constructor in SQL Server
The values clause is not new to SQL Server, it is used to hard-code values while inserting records into table. The value constructor is enhanced in SQL 2008 which provides…
Read more »