SQL Server Data Classification – Importance of Proper Naming

Hello Friends,

In one of my previous blog post we have seen Data Classification tool in SSMS. This tool helps us in four steps – Discovering, Classifying, Labeling and Reporting of sensitive data. This tool classifies columns based on column name. if you will not have meaningful column name then this tool will not help you. To read more about this tool you can click here.

Let me show you the importance of having proper column naming conventions with example. First, I am going to create a database and a table with proper column names under that database:

USE [Master]
GO
CREATE DATABASE [ColNameTest]
GO
USE [ColNameTest]
GO
CREATE TABLE [dbo].[ProperColName](
	[FName] [varchar](50) NULL,
	[LName] [varchar](50) NULL,
	[AddressLine1] [varchar](250) NULL,
	[AddressLine2] [varchar](250) NULL,
	[City] [varchar](100) NULL,
	[PostalCode] [char](10) NULL
) ON [PRIMARY]
GO
INSERT [dbo].[ProperColName] ([FName], [LName], [AddressLine1], [AddressLine2], [City], [PostalCode]) 
VALUES (N'Prince', N'Rastogi', N'Harwin Road', N'Apartment No. 4444', N'Richmond', N'23060')
GO

Now run Data Classification on this new Database:

In output, you can see all column are classified using this data classification tool:

Now let’s create a new table with improper naming and run data classification tool again:

USE [ColNameTest]
GO
CREATE TABLE [dbo].[ImproperColName](
	[FirstN] [varchar](50) NULL,
	[LastN] [varchar](50) NULL,
	[ADL1] [varchar](250) NULL,
	[ADL2] [varchar](250) NULL,
	[Cname] [varchar](100) NULL,
	[Code] [char](10) NULL
) ON [PRIMARY]
GO
INSERT [dbo].[ImproperColName] ([FirstN], [LastN], [ADL1], [ADL2], [Cname], [Code]) 
VALUES (N'Prince', N'Rastogi', N'Harwin Road', N'Apartment No. 4444', N'Richmond', N'23060')
GO

Now you can see that we didn’t get the improper naming columns under data classification report.  For data classification you can add such column manually to data classification report:

As a Data professional we should always use proper meaningful names for objects.

HAPPY LEARNING!

Regards:
Prince Kumar Rastogi

Follow Prince Rastogi on Twitter | Follow Prince Rastogi on FaceBook