Archive for the Database Category

Report: Statistics on 2012

Posted in Database with tags on December 31, 2012 by muhrizky

The WordPress.com stats helper prepared a 2012 annual report for this blog.

Here’s an excerpt:

The new Boeing 787 Dreamliner can carry about 250 passengers. This blog was viewed about 1,500 times in 2012. If it were a Dreamliner, it would take about 6 trips to carry that many people.

Click here to see the complete report.

Advertisements

Two Tips on Database

Posted in Database with tags , on February 26, 2009 by muhrizky

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.