Tables And Coding Standard Conventions Computer Science Essay

Published: Last Edited:

This essay has been submitted by a student. This is not an example of the work written by our professional essay writers.

A table is an entity that holds all the information in a data base and is of great importance in a database.

Coding Standard/Naming Conventions

Prefix the table with the prefix 'tbl' and the creator name

Pascal notation is toe end each table name with an 's' at the end of the name.

Name the tables appropriately to

In cases where this is necessary, surround the object name with brackets, such as [Year]

Do not use spaces in the table name

Do not use SQL commands/keywords as the name of database objects

My Choice

The table must use a prefix of 'tbl' it makes it easier to recognize when debugging.

Do not end the table name with a 's'. Singular is better

Name tables appropriate to make debugging faster.

Use brackets when needed but as a rule if you don't use brackets then there is no need to use spaces.

Do not use SQL commands except when needed, not in names.

Stored Procedures

What it is?

This is a basic SELECT statement with the use of a join.

Coding Standard/Naming Conventions

System level stored procedures are named using a prefix 'SP__' (two underscores) and a description of what the stored procedure does.

• All application level and user defined stored procedures are prefixed with the constant 'USP' with a description of what the stored procedure does. E.g., UspGetLastModifiedDate

• Always add an @Debug parameter to your stored procedures. This can be a BIT data type. When a '1' is passed for this parameter, print all the intermediate results, variable contents using SELECT or PRINT statements. When '0' is passed do not print anything. This helps in quickly debugging stored procedures as you don't have to add and remove these PRINT/SELECT statements before and after troubleshooting problems.

• Do not call functions repeatedly within your stored procedures, triggers, functions and batches. For example, you might need the length of a string variable in many places of your procedure, but don't call the LEN function whenever it's needed. Instead, call the LEN function once, and store the result in a variable for later use.

• Make sure your stored procedures always return a value indicating their status. Standardize on the return values of stored procedures for success and failures. The RETURN statement is meant for returning the execution status only, but not data. If you need to return data, use OUTPUT parameters.

• If your stored procedure always returns a single row resultset, consider returning the resultset using OUTPUT parameters instead of a SELECT statement, as ADO handles output parameters faster than resultsets returned by SELECT statements.

• Do not prefix your stored procedure names with 'sp_'. The prefix 'sp_' is reserved for system stored procedure that ship with SQL Server. Whenever SQL Server encounters a procedure name starting with 'sp_', it first tries to locate the procedure in the master database, then it looks for any qualifiers (database, owner) provided, then it tries dbo as the owner. You can save time in locating the stored procedure by avoiding the 'sp_' prefix.

• Do not let your front-end applications query/manipulate the data directly using SELECT or INSERT/UPDATE/DELETE statements. Instead, create stored procedures and let your applications access these stored procedures. This keeps the data access clean and consistent across all the modules of your application, while at the same time centralizing the business logic within the database.

Rules: sp<App Name>_[<Group Name >_]<Action><table/logical instance>

Examples: spOrders_GetNewOrders, spProducts_UpdateProduct

My Choice


What it is?

A SQL View is a virtual table, which is based on SQL SELECT query. Essentially a view is very close to a real database table (it has columns and rows just like a regular table), except for the fact that the real tables store data, while the views don't. The view's data is generated dynamically when the view is referenced. A view references one or more existing database tables or other views. In effect every view is a filter of the table data referenced in it and this filter can restrict both the columns and the rows of the referenced tables.

Here is an example of how to create a SQL view using already familiar Product and Manufacturer SQL tables:

Coding Standard/Naming Conventions

CREATE VIEW vwAveragePrice AS

SELECT Manufacturer, ManufacturerWebsite, ManufacturerEmail, AVG(Price) AS AvgPrice

FROM Manufacturer JOIN Product

ON Manufacturer.ManufacturerID = Product.ManufacturerID

GROUP BY Manufacturer, ManufacturerWebsite, ManufacturerEmail

Creating a VIEW

The syntax for creating a VIEW is:

CREATE VIEW view_name AS

SELECT columns

FROM table

WHERE predicates;

For example:

CREATE VIEW sup_orders AS

SELECT suppliers.supplier_id, orders.quantity, orders.price

FROM suppliers, orders

WHERE suppliers.supplier_id = orders.supplier_id

and suppliers.supplier_name = 'IBM';

My Choice


What it is?

An SQL identifier that designates a parameter for a function or procedure. If the parameter-name is for a procedure, the identifier may be preceded by a colon.

SQL parameters are an essential part of dynamic programming. They allow the programmer to retrieve input from the user and query the database with the given information. Without SQL parameters, programmers would not be able to query tables and views dynamically. Microsoft SQL Server has a specific format when using parameters in applications. The capabilities are included in the SQLServerPreparedStatement class.

Coding Standard/Naming Conventions

Do not define default values for parameters.

o If a default is needed, the front end will supply the value.


My Choice


What it is?

 A variable holds a single piece of information, similar to a number or a character string. Variables can be used for a number of things

Coding Standard/Naming Conventions

To pass parameters to stored procedures, or function

To control the processing of a loop

To test for a true or false condition in an IF statement

To programmatically control conditions in a WHERE statement

Variable identifiers for datatypes should consist of two parts:

The base, which describes the content of the variable;

The prefix, which describes the datatype of the variable

Correct prefixes for each datatype are shown in the table below.

My Choice

Temp Tables

What it is?

Temporary Tables are a great T-SQL feature that lets you store and process intermediate results by using the same selection, update, and join capabilities that you can use with typical SQL Server tables.

Coding Standard/Naming Conventions

Local Temporary Tables

Local temporary tables prefix with single number sign (#) as the first character of their names, like (#table_name).

Local temporary tables are visible only in the current session OR you can say that they are visible only to the current connection for the user.

They are deleted when the user disconnects from instances of Microsoft SQL Server.

Global Temporary Tables

Global temporary tables prefix with double number sign (##) as the first character of their names, like (##table_name).

Global temporary tables are visible to all sessions OR you can say that they are visible to any user after they are created.

They are deleted when all users referencing the table disconnect from Microsoft SQL Server.

Naming Of Temporary Tables

Temporary tables are always created in tempdb. No matters it is created from the stored procedure internally or from the SQL Query Analyzer window. If a local temporary table is created in a stored procedure or application that can be executed at the same time by several users, SQL Server has to be able to distinguish the tables created by the different users. SQL Server does this by internally appending a numeric suffix to each local temporary table name. The full name of a temporary table as stored in the sysobjects table in tempdb consists of table name specified in the CREATE TABLE statement and the system-generated numeric suffix. To allow for the suffix, table_name specified for a local temporary name cannot exceed 116 characters.

My Choice

Avoid unnecessary use of temporary tables

o Use 'Derived tables' or CTE (Common Table Expressions) wherever possible, as they

perform better6


What it is?

A common table expression (CTE) can be thought of as a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. A CTE is similar to a derived table in that it is not stored as an object and lasts only for the duration of the query. Unlike a derived table, a CTE can be self-referencing and can be referenced multiple times in the same query.

Common table expressions (CTEs) are defined in the SQL_99 standard, and let you create queries that work with recursive data. CTE-based recursive queries are generally more readable than those created using traditional Transact-SQL syntax.


A CTE is a named temporary result set based on a regular SELECT query. You then use the result set in any SELECT, INSERT, UPDATE, or DELETE query defined within the same scope as the CTE.

Coding Standard/Naming Conventions

Advantages of CTEs

Using CTEs provides you with two main advantages:

Queries with derived tables become simpler and therefore more readable.

Traditional Transact-SQL constructs used to work with derived tables usually require a separate definition for the derived data (such as a temporary table or table valued function). Using a CTE to define t he derived table makes it easier to see the definition of the derived table with the code that uses it.

You can traverse recursive hierarchies.

CTEs reduce the amount of code required for a query that traverses recursive hierarchies (such as when rows in the same table can be linked with a self-join).

Example of a simple CTE

The following example shows the creation of a CTE named TopSales that displays the number of sales for each salesperson based on information provided by the SalesOrderHeader table.

WITH TopSales (SalesPersonID, NumSales) AS


SELECT SalesPersonID, Count(*)

FROM Sales.SalesOrderHeader GROUP BY SalesPersonId





The query then restricts the TopSales CTE based on the SalesPersonID and sorts the rows based on the NumSales column.

My Choice