Difference between CAST and CONVERT

Recently I’ve been writing quite a bit about CAST and CONVERT on my Instagram page (@sql.handbook). Here I will highlight the differences in more detail.

As we know by now, both functions can be used for converting an expression of one data type to another data type. However, they do have distinct differences.

The differences:
– CAST is ANSI-SQL. CONVERT is not.
– CONVERT has a ‘style’ parameter. This allows CONVERT to change the format of the data. CAST cannot do that.

CONVERT’s ‘style’ parameter can be quite powerful. It allows you to reformat data. For example, date and time styles vary across countries. So we can use the CONVERT function to change the format of a column with data in the datetime data type into other date formats. As seen on the example below.

SELECT
Original_Date
,CONVERT(varchar, Original_Date, 101) AS US_Standard
,CONVERT(varchar, Original_Date, 104) AS German_Standard
,CONVERT(varchar, Original_Date, 111) AS Japan_Standard
FROM Date_Table

Results:

Advertisement

Lock Escalation in the SQL Engine

Locking is an essential part of upholding data integrity. Locks are 96 byte in-memory structures that are especially essential when it comes to the isolation requirement in ACID compliancy.

However there is a draw back to having transactions doing too many locks, and that is it can be quite memory intensive if a single query is acquiring many locks. In order to reduce system overhead, SQL Server goes and does what is called lock escalation.

What is Lock Escalation?

Lock escalation is the process of converting many fine-grained low level locks (e.g row/page locks) into table level locks. This reduces system overhead but increases the possibility for concurrency contention, so it’s a bit of a double edged sword in that we reduce memory pressure but now we may have to deal with resource contention.

Lock escalation applies to SQL Server, Azure SQL Database, Azure SQL Managed Instance and Azure Synapse Analytics.

How to find all the Foreign Keys that are referencing a Table?

At some point you might find yourself in a situation where you need to figure out how many foreign keys are referencing a certain table.

In this blog post I discuss two ways to achieve this:
1. Querying the sys.foreign_keys system table
2. Executing the sp_fkeys system stored procedure

Querying the sys.foreign_keys system table

As the name implies, the sys.foreign_keys table contains information regarding foreign key constraints. The table contains a column named ‘referenced_object_id’ that is useful to query to find which foreign key constraints are referencing a certain table.

Using AdventureWorks2019 database as an example, I query the sys.foreign_keys table to find all the foreign keys that reference the table ‘Person.Person’.

SELECT name AS [Foreign Key],
SCHEMA_NAME(schema_id) AS [Schema],
OBJECT_NAME(parent_object_id) AS [Table]
FROM sys.foreign_keys
WHERE referenced_object_id = OBJECT_ID('Person.Person');

How to Identify Unused Indexes

Indexes are database objects that are useful for when it comes to improving your SQL Server performance. The more indexes you have, the more overhead you add for maintaining those indexes.

Part of doing a SQL Server health check, is to identify if there are any unused indexes. Having those indexes around is likely doing more harm than good when it comes to performance.

To find out if an index is not used in our environment, we need to check if there have been any SCANS, SEEKS or LOOKUPS on the index since the SQL Server started. We can use the Dynamic Management View (DMV) named sys.dm_db_index_usage_stats to help us here.

I created the script below to find all unused non-clustered indexes in a SQL Server database.

Viewing Execution Plans in SSMS

When doing query tuning, interrogating the execution plan for the query can be useful for getting insights into improving the query.

There is actually different types of execution plans and a number of ways to retrieve those plans.

We can get plans from SQL Server Management Studio (SSMS) interface, the plan cache, the query store, the profiler and/or extended events. However, in this article we are going to focus on how to retrieve execution plans from the SSMS interface.