Two Tips on Database


Stored Procedures

Description: Scripts that contains one or more lines of Transact-SQL (T-SQL) codes in order to run an execution to and from the database.

Benefits:

  • Faster execution and reducing network traffic; because the actual process running inside the server, not in the client-side.
  • Security mechanism; because the statements are stored inside the server, so there are no data transfers in form of query scripts on the network traffic.
/*Calculate product TotalCost*/
CREATE PROCEDURE prcTotalCost
	@cOrderId char(6)
AS
BEGIN
	SELECT TotalCost = ((siQty * mCost) - mDiscount)
	FROM OrderDetail
	WHERE cOrderId = @cOrderId
END

Triggers

Description: Scripts that contains one or more lines of T-SQL codes in order to execute a group of statements to the database. This is usually used for Data Manipulation Language (DML) statements.

Benefits:

  • Can do checks to every related tables in a relationship.
  • Can do more complex ruleset or restriction to specific tables.
/*Automatically updates the numbers of quantity after sales*/
CREATE TRIGGER trgInventoryUsesiQty ON InventoryUse
FOR INSERT
AS
DECLARE @inventoryid char(6),
	@inventoryuseqty smallint
SELECT @inventoryid = InventoryUse.cInventoryId,
@inventoryuseqty = InventoryUse.siQty FROM InventoryUse
INNER JOIN inserted ON InventoryUse.siInventoryUseId =
inserted.siInventoryUseId
IF @inventoryuseqty >= 0
BEGIN
	UPDATE Inventories
	SET Inventories.siQoh = siQoh - @inventoryuseqty
	WHERE Inventories.cInventoryId = @inventoryid
END

Further Suggestions

Learn about what it called LINQ. LINQ is a set of T-SQL statements that can be directly integrate into a programming language. This is an applied technology introduce by Microsoft® in their current .NET environment.

Further Readings

There are a lot of other database techniques that can be used to simplify specific tasks according to the business process. This is also recommended for person who manage Active Directory Server, because actually the database works similar to Relational Database Management Systems (RDBMS). You can visit MSDN Library on SQL Server or any RDBMS websites to learn more about it.
Keywords: Cursors, Indexes, Views, Reporting Services.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: