Tables And Coding Standard Conventions Computer Science Essay

Published:

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

Views

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

Parameters

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.

parameters

My Choice

Variables

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

CTE's

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.

Definition

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

)

SELECT * FROM TopSales

WHERE SalesPersonID IS NOT NULL

ORDER BY NumSales DESC

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

My Choice

Writing Services

Essay Writing
Service

Find out how the very best essay writing service can help you accomplish more and achieve higher marks today.

Assignment Writing Service

From complicated assignments to tricky tasks, our experts can tackle virtually any question thrown at them.

Dissertation Writing Service

A dissertation (also known as a thesis or research project) is probably the most important piece of work for any student! From full dissertations to individual chapters, we’re on hand to support you.

Coursework Writing Service

Our expert qualified writers can help you get your coursework right first time, every time.

Dissertation Proposal Service

The first step to completing a dissertation is to create a proposal that talks about what you wish to do. Our experts can design suitable methodologies - perfect to help you get started with a dissertation.

Report Writing
Service

Reports for any audience. Perfectly structured, professionally written, and tailored to suit your exact requirements.

Essay Skeleton Answer Service

If you’re just looking for some help to get started on an essay, our outline service provides you with a perfect essay plan.

Marking & Proofreading Service

Not sure if your work is hitting the mark? Struggling to get feedback from your lecturer? Our premium marking service was created just for you - get the feedback you deserve now.

Exam Revision
Service

Exams can be one of the most stressful experiences you’ll ever have! Revision is key, and we’re here to help. With custom created revision notes and exam answers, you’ll never feel underprepared again.