SQL Formatting and Object Naming Convention

SQL formatting may not increase the functionality of the code but good and consistent formatting by the developers certainly affect the readability of the code and therefore makes it easier to maintain. Keyword and separator alignment, spaces and tabs provide opportunities for a lot of variations in code layout. A case-insensitive server/database give further flexibility in formatting with choosing upper or lower casing of the text.

Just as an experiment to see the effect of casing and object naming conventions on readability, I created this dummy stored procedure and formatted it in four different ways. The white-space layout, i.e. tabs, spaces, newline etc is same in all of them.

Which one do you prefer?

Do you have your own layout options that you would like to share?

Feel free to copy the script and share your formatted version in comments.

Version 1

-- underscore as word separator
-- lowercase keywords
create procedure dbo.sp_get_employee
	@employee_id int,
	@department_id int
as
begin
	select
		e.first_name,
		e.last_name,
		e.designation,
		e.salary,
		e.manager_first_name,
		e.manager_last_name,
		d.department_name
	from
		dbo.employee as e with (nolock)
		inner join dbo.department as d with (nolock)
		on e.department_id = d.department_id
	where
		employee_id = @employee_id and
		department_id = @department_id;

	return 0;
end

Version 2

-- underscore as word separator
-- UPPERCASE keywords
CREATE PROCEDURE dbo.sp_get_employee
	@employee_id INT,
	@department_id INT
AS
BEGIN
	SELECT
		e.first_name,
		e.last_name,
		e.designation,
		e.salary,
		e.manager_first_name,
		e.manager_last_name,
		d.department_name
	FROM
		dbo.employee AS e WITH (NOLOCK)
		INNER JOIN dbo.department AS d WITH (NOLOCK)
		ON e.department_id = d.department_id
	WHERE
		employee_id = @employee_id AND
		department_id = @department_id;

	RETURN 0;
END

Version 3

-- camel/pascal casing for objects names
-- lowercase keywords
create procedure dbo.spGetEmployee
	@EmployeeId int,
	@DepartmentId int
as
begin
	select
		e.FirstName,
		e.LastName,
		e.Designation,
		e.Salary,
		e.ManagerFirstName,
		e.ManagerLastName,
		d.DepartmentName
	from
		dbo.Employee as e with (nolock)
		inner join dbo.Department as d with (nolock)
		on e.DepartmentId = d.DepartmentId
	where
		EmployeeId = @EmployeeId and
		DepartmentId = @DepartmentId;

	return 0;
end

Version 4

-- camel/pascal casing for objects names
-- UPPERCASE keywords
CREATE PROCEDURE dbo.spGetEmployee
	@EmployeeId INT,
	@DepartmentId INT
AS
BEGIN
	SELECT
		e.FirstName,
		e.LastName,
		e.Designation,
		e.Salary,
		e.ManagerFirstName,
		e.ManagerLastName,
		d.DepartmentName
	FROM
		dbo.Employee AS e WITH (NOLOCK)
		INNER JOIN dbo.Department AS d WITH (NOLOCK)
		ON e.DepartmentId = d.DepartmentId
	WHERE
		EmployeeId = @EmployeeId AND
		DepartmentId = @DepartmentId;

	RETURN 0;
END

SSIS Naming Convention – Updated for SSIS 2016

I use an SSIS naming convention that is based on assigning a prefix to each type of component. My first version of SSIS naming convention list was based on SSIS 2008 R2. Since then, Microsoft has introduced new connections, tasks, sources and destinations that are mostly related to Azure, Hadoop, ODATA and CDC.

With the launch of SQL Server 2016, I have updated my list to include all those new components and assigned prefixes to them. There are minor changes to some existing prefixes too e.g. RBLDIDXT is now REBLDIDXT and few others like this.

Why Should You Use a SSIS Naming Convention

A naming convention may not appear to be a big deal at the time of SSIS development because you have a GUI development environment that makes it very easy to identify the components. But during the support and maintenance phase, when trying to read the execution logs or error messages, bad naming choices make it difficult to identify the source of errors quickly. Having a consistent naming convention across the enterprise makes a lot of difference in supporting the packages.

Download

Feel free to reuse the conventions in the list or use it as a template to define your own. I’d love to hear your thoughts and suggestions in the comments, Twitter or Facebook.

You can download the complete list at the TechNet Gallery link below –

=> Download SSIS Naming Convention (Updated for SSIS 2016) <=

Example screenshot –

SSIS Naming Convention
Screenshot of the SSIS Naming Convention Excel Workbook

SSIS Components Naming Conventions

Read a New Version of This Blog Post – SSIS Naming Convention – Updated for SSIS 2016

I wrote earlier about my naming conventions for SSIS variables that let me quickly figure out the source of the variable value very easily. This post continues on to the prefix-based naming conventions I use (or plan to use) for the 120+ components in SSIS packages.

While looking around online for SSIS component naming conventions, most of the suggestions are based on Jamie Thompson’s (Blog | @JamieT) post SSIS: Suggested Best Practices and naming conventions. He has tried to limit the prefixes to 3 or 4 characters and included most of the frequently used components. Using the prefixes as described in his post has helped me a lot in the maintenance phase of a package when my memory has faded about the functionality and the workflow. Some more motivation to adopt a naming convention can be found in 31-days of SSIS series by Jason Strate (Blog | @StrateSQL).

My goal was to make an exhaustive list of components with their prefixes so that –

  • I do not need to put on my thinking hat every time I start using a component for the first time.
  • The naming convention can be future-proof and avoid confusion with new components being introduced in SSIS platform.
  • Remove the guesswork (especially for new developers and maintenance teams who may not be SSIS developers primarily) by avoiding prefixes that sound too similar to another.

I created an Excel sheet of all the components (more than 120) that I found listed in MSDN, i.e. containers, connection managers, sources, destinations, tasks, and transformations. My prefixes are more elaborate than Jamie’s for the reasons mentioned above, but that is just my preference. You can use the Excel as a template for you organization and modify the prefixes to you liking.

The following is an example of one section from the Excel.

Download: Get the full Excel workbook at the TechNet Galleries

Example –

SSIS Component Naming Conventions

Using a Meaningful Application Name in the OLEDB Connection String

The default connection string for a OLEDB connection looks something like this –

Data Source=(local);Initial Catalog=ReportServer;Provider=SQLNCLI10.1;Integrated Security=SSPI;Auto Translate=False;Application Name=SSIS-DemoOLEDBPackage-{9758C970-4AAC-449E-8390-FA7EAC5CD52A}(local).ReportServer;
 

Notice the Application Name parameter in bold font. It has the name of the package, a unique Id for the connection, the SQL server name and the database name. This application name will be exported out to the config file too. Also, this is what you will see in the Program Name column of SP_WHO2 results when the package is running.

When the developers create the packages on a development server, the connection string includes the development server name. At the time of migrating the package and its config file to the QA and Production environment, the developers usually change the Data Source and Initial Catalog in the connection string but leave the Application Name as it is. Anyone monitoring the Production server with a SP_WHO2 would see a package running with a funny long name including GUID and the development server name. It is certainly not user-friendly.

This can be fixed easily with any one of the following methods.

Method 1 – Properties Window

Right click the OLEDB connection and select Properties (or select the connection and press F4). The Properties window will show up. Edit the Connection String property to change the Application Name parameter of the connection in this window.

Method 2 – Config File

Edit the Connection String property to change the Application Name parameter of the connection in the config file. As we know, the values in the config file override the design time values when the package is running. Although I prefer making the initial change in the package itself before creating the config file.

Method 3 – The Connection Manager Wizard

  1. Double click the OLEDB Connection.
  2. Click on the All Tab.
  3. Edit the Application Name.

SSISOLEDBConnectionStringApplicationName

Now the connection string would look like the following, which is a lot cleaner than the default string –

Data Source=(local);Initial Catalog=ReportServer;Provider=SQLNCLI10.1;Integrated Security=SSPI;Auto Translate=False;Application Name=SSIS-DemoPackage;
 

I prefer using a _ convention based on the target environment e.g. SSIS-DemoPackage_TEST, SSIS-DemoPackage_QA or SSIS-DemoPackage_PROD in the different config files. You can plan to put in other information to make it more meaningful in your context.

I’d love to hear your thoughts on this!

SSIS Variable Naming Convention Based on Origin of Value

There are many schools of thought about naming conventions, not just about SSIS variables, but about naming anything that can be given a name! One point that everyone agrees on is – use a convention that suits your environment, and stick to it.

Related Blog Post – SSIS component naming convention

One issue that I face while looking at packages with many variables is that there is no straightforward way to get a summary or a quick glance of all variables that have expressions defined for them. The BIDS Helper add-in for Visual Studio assists in this regard by adding a triangular icon overlay over the variables with an expression. SQL Server 2012 is also said to have a similar feature but I haven’t tried that yet. On the other hand, if I need to see a summary of variables that have values coming from a config file, I have to use the Configuration Editor to edit the config file and reach its last summary screen.

I’ve started using a prefix-based convention to name my SSIS variables recently based on the origin of the variable value. I distinguish between the variables as having –

  1. a value set from a config file at execution time.
  2. a value calculated in an expression at execution time. The expression could be based on other user variables, system variables, functions or literals.
  3. a static value that will not change from its design time value.
  4. a value assigned in a Script Task, a For Each Loop Container, Row Count etc.

If I know at design time that the value of the variable will be passed from a configuration file, I give it a prefix of “config”, e.g. configEmailDistributionList. If the value would be set via an expression then the variable gets a prefix “expr”, e.g. exprDirectoryForArchive. Other variables do not have any prefix, simply a name that is descriptive enough.

Advantages:

  • At the time of creating a config file, the prefix makes it clear that all variables with a “config” prefix must be included in the configuration. So the config file now has only such variables along with other task or connection manager properties.
  • On opening up a package, I can easily figure out where should I be looking for the value of the variable, the config file or the Expression Editor.
  • Alphabetical ordering in the Variables window groups similar variables together.
  • Helps in development because I don’t have to constantly check back variable properties if it has an expression or not.
  • Helps in standardizing the names and expressions for some of the most frequently used variables.
  • Helps in debugging.

Disadvantages:

The argument against this convention would be the same as given for any prefix-based convention (e.g. Hungarian Notation) that if the origin of the variable value changes then the prefix would also need to change. Which in turn, may need a change at all those places where the variable is used. As an example, if a variable was getting its value from a config file but would now have a static value, then the “config” prefix would have to go away and all the expressions, properties etc. using that variable would have to be updated. Right now I’m counting on doing a Find/Replace in the XML code of the package.

I’ll try to see if using variable Namespaces would be a better idea than prefixes and share the experience in a blog post later.

References –

(not specifically for Hungarian Notation, but in context of any prefix-based convention)

Hungarian notation (Wikipedia)
http://en.wikipedia.org/wiki/Hungarian_notation

Hungarian Notation (MSDN)
http://msdn.microsoft.com/en-us/library/aa260976(v=VS.60).aspx

Hungarian Notation – The Good, The Bad, and The Ugly
http://ootips.org/hungarian-notation.html