Checking a column existence in a table is very simple in SQL Server, but you should be careful while doing it on Azure environment. We simply ignore the performance consideration while writing SELECTs against systems tables/views/dmvs etc. Recently a stored proc started working very very slow on a production server, which is hosted on Microsoft Azure (SQL as a service). The same stored proc was working well on the UAT , which is a replica of live with less hardware configurations and hosted on a virtual server on another cloud. I first thought, it might be an issue with the network but later I realised network is not the concern at all. I started digging into the proc and found that the culprit is a system view, which I was using to check the column existence in SQL Server table by doing a IF EXISTS. As soon as I replaced that statement with a system function, the stored proc started working well like it used to be on UAT.
I don’t know the exact reason of the slowness, but be cautious while fetching data from system views on Microsoft Azure.
Using COL_LENGTH (Recommended)
IF COL_LENGTH('Employee','EmployeeID') IS NOT NULL PRINT 'Exists'
Using COLUMNPROPERTY (Recommended)
IF COLUMNPROPERTY(OBJECT_ID('Employee'), 'EmployeeID', 'ColumnId') IS NOT NULL PRINT 'Exists'
IF EXISTS (SELECT 1 FROM sys.columns WHERE OBJECT_NAME(object_id) = 'Employee' AND name = 'EmployeeID') PRINT 'Exists'
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Employee' AND COLUMN_NAME = 'EmployeeID') PRINT 'Exists'