mysql, postgresql, oracle, tables, database, Database - Tech-Recipes

Database tutorials

Oracle: Using the AFTER INSERT and AFTER UPDATE triggers

contributed by Vishwanath Dalvi on December 12, 2011 under Oracle

A database trigger is a stored procedure that automatically executes whenever an event occurs. The event may be insert-delete-update operations. Oracle initiates an ‘AFTER INSERT’ trigger after an insert event has occurred and an ‘AFTER UPDATE’ trigger after an update event has occurred.

Oracle: How to Create a Copy of Table Data

contributed by Vishwanath Dalvi on December 7, 2011 under Oracle

Knowing how to copy existing table data is beneficial to any DBA. This tutorial will demonstrate how to copy an existing table’s data into a new table. Examples with walkthrough explanation are provided.

Oracle: How to Create an Auto Increment Field Using Sequence

contributed by Vishwanath Dalvi on December 5, 2011 under Oracle

In Oracle, you can create an auto increment field using ’sequence’ database object that can be assigned as primary keys. Using Oracle ’sequence’ object, you can generate new values for a column. An Oracle sequence is an object like a table or a stored procedure. Examples with walkthrough explanations are provided.

SQL Server 2008/2008 R2: Move tempdb to Improve Performance

contributed by Rob Rogers on November 4, 2011 under Database

By default, tempdb is placed on the same drive that SQL Server is installed on. This can impair performance as tempdb is frequently used to store temporary tables and objects. If the hard drive is being accessed for other functions, it can result in sluggish performance by the database as well as any software that is using the database. For optimum performance, tempdb should be on a SATA drive instead of an IDE drive and should not be on the same drive as the SQL Server software or the operating system (boot drive).

MySQL: How to get row number order

contributed by Vishwanath Dalvi on October 1, 2011 under Database, MySQL

These directions walk through how one can displaying the row number order/rank for result set obtained from executing a sql query. An example with walkthrough explanation is provided.

SQL Server: How To Find Duplicate Records

contributed by Rob Rogers on October 13, 2009 under Database

When checking the integrity of your data, it may be necessary to check your tables for duplicate records. By grouping these records, you can eliminate the unique records from your result set so that you can view just the records that contain duplicated values, making it easier to remove them.

SQL Server 2005: How to Reset a Forgotten Password for the sa Account

contributed by Rob Rogers on December 22, 2008 under Database

If you’ve forgotten the sa password for your SQL server, you are probably in a panic. Fear not, for you can reset the password and once again have access to the powers of the sa account by using the query window in the Management Studio.

SQL Server 2005: Locating Stored Procedures that Contain a Keyword

contributed by Rob Rogers on December 4, 2008 under Database

Making changes to a database that has been created by a third pary can be a difficult task, especially when you don’t want to break any of the existing stored procedures. You can search the database’s stored procedures for keywords to find which procedures use a keyword that is involved in your change. This makes it much easier in identifying these procedures so you can make the appropriate changes.