Tuesday, January 30, 2007

Delete vs Truncate @ SQL

SQL Server
TRUNCATE is a DDL command and cannot be rolled back. All of the memory space is released back to the server.
DELETE is a DML command and can be rolled back.

Both commands accomplish identical tasks (removing all data from a table), but TRUNCATE is much faster

TRUNCATE : You can't use WHERE clause
DELETE : You can use WHERE clause

Truncate = Delete+Commit -so we cant roll back
Truncate is a Transcation control language

Truncate: Drop all object's statistics and marks like High Water Mark, free extents and leave the object really empty with the first extent.
Delete: You can keep object's statistics and all allocated space.

Truncate will write only one query in the ldf file to remove whole data unlike the Delete query which will write one query per record in the ldf file

You cannot use TRUNCATE TABLE on a table referenced by a FOREIGN KEY constraint; instead, use DELETE statement without a WHERE clause. Because TRUNCATE TABLE is not logged, it cannot activate a trigger.

http://www.geekinterview.com/question_details/425

MYSQL

TRUNCATE TABLE empties a table completely. Logically, this is equivalent to a DELETE statement that deletes all rows, but there are practical differences under some circumstances.

For InnoDB before version 5.0.3, TRUNCATE TABLE is mapped to DELETE, so there is no difference. Starting with MySQL 5.0.3, fast TRUNCATE TABLE is available. However, the operation is still mapped to DELETE if there are foreign key constraints that reference the table. (When fast truncate is used, it resets any AUTO_INCREMENT counter. From MySQL 5.0.13 on, the AUTO_INCREMENT counter is reset by TRUNCATE TABLE, regardless of whether there is a foreign key constraint.)

For other storage engines, TRUNCATE TABLE differs from DELETE in the following ways in MySQL 5.0:

Truncate operations drop and re-create the table, which is much faster than deleting rows one by one.

Truncate operations are not transaction-safe; an error occurs when attempting one in the course of an active transaction or active table lock.

The number of deleted rows is not returned.

As long as the table format file tbl_name.frm is valid, the table can be re-created as an empty table with TRUNCATE TABLE, even if the data or index files have become corrupted.

The table handler does not remember the last used AUTO_INCREMENT value, but starts counting from the beginning. This is true even for MyISAM and InnoDB, which normally do not reuse sequence values.

Since truncation of a table does not make any use of DELETE, the TRUNCATE statement does not invoke ON DELETE triggers.

Monday, January 29, 2007

DOM @ XML

The XML Document Object Model (XML DOM) defines a standard way for accessing and manipulating XML documents.

The DOM presents an XML document as a tree-structure (a node tree), with the elements, attributes, and text defined as nodes.

Parsing the XML DOM

To manipulate an XML document, you need an XML parser. The parser loads the document into your computer's memory. Once the document is loaded, its data can be manipulated using the DOM. The DOM treats the XML document as a tree.

There are some differences between Microsoft's XML parser and the XML parser used in Mozilla browsers. In this tutorial we will show you how to create cross browser scripts that will work in both Internet Explorer and Mozilla browsers


Microsoft's XML Parser
Microsoft's XML parser is a COM component that comes with Internet Explorer 5 and higher. Once you have installed Internet Explorer, the parser is available to scripts.

Microsoft's XML parser supports all the necessary functions to traverse the node tree, access the nodes and their attribute values, insert and delete nodes, and convert the node tree back to XML.

To create an instance of Microsoft's XML parser, use the following code:

JavaScript:

var xmlDoc=new ActiveXObject("Microsoft.XMLDOM");

The first line of the script above creates an instance of the XML parser. The second line turns off asynchronized loading, to make sure that the parser will not continue execution of the script before the document is fully loaded. The third line tells the parser to load an XML document called "note.xml".

XML Parser in Mozilla, Firefox, and Opera
Mozilla's XML parser supports all the necessary functions to traverse the node tree, access the nodes and their attribute values, insert and delete nodes, and convert the node tree back to XML.

To create an instance of the XML parser in Mozilla browsers, use the following code:

JavaScript:

var xmlDoc=document.implementation.createDocument("ns","root",null);

The first parameter, ns, defines the namespace used for the XML document. The second parameter, root, is the XML root element in the XML file. The third parameter, null, is always null because it is not implemented yet

The following code fragment loads an existing XML document ("note.xml") into Mozillas' XML parser:

var xmlDoc=document.implementation.createDocument("","",null);
xmlDoc.load("note.xml");

The first line of the script above creates an instance of the XML parser. The second line tells the parser to load an XML document called "note.xml".