Delete Vs Truncate Statement

Delete table is a logged operation, so the deletion of each row gets logged in the transaction log, which makes it slow.

• Truncate table also deletes all the rows in a table, but it won’t log the deletion of each row, instead it logs the de-allocation of the data pages of the table, which makes it faster. Of course, truncate table cannot be rolled back.

• Truncate table is functionally identical to delete statement with no “where clause” both remove all rows in the table. But truncate table is faster and uses fewer system and transaction log resources than delete.

• Truncate table removes all rows from a table, but the table structure and its columns, constraints, indexes etc., remains as it is.

• In truncate table the counter used by an identity column for new rows is reset to the seed for the column.

• If you want to retain the identity counter, use delete statement instead.

• If you want to remove table definition and its data, use the drop table statement.

• 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.

• Truncate table may not be used on tables participating in an indexed view.

List some SQL optimisation techniques.

- Do not use SELECT * unless you must
- Do not create indexes you are not going to use
- Indexes are good for reads, but bad for writes
- Use OPTIMIZE TABLE and ANALYZE TABLE regularly
- Create your tables with a fixed-table format if possible
- Use the most efficient table type for each table
- Use the best data type, including NOT NULL if appropriate
- Use default values for INSERT when you can
- Use temporary tables rather than heavy PHP work
- Use SELECT priorities for better control
- SELECT foo IN (list, of, constants) is very fast
- When joining tables, use numbers instead of strings if you can

Session vs Cookies?

	Browser needs Cookies Enabled?	Can User Edit Information?	Information Lasts Between Browser Sessions? (Leaving site and coming back)	Information Location
Cookies	Yes	Yes, easily	Yes	User's Browser
Sessions	No	No*	No	Server, except for session ID

*Users can not modify the information contained in sessions but others can steal session ids and impersonate victims.

Will Session work in PHP if cookies are disabled?

The good news is that sessions will work even if cookies are not enabled. When a session is created, PHP assigns the browser a session ID which, normally, it first tries to store in a cookie. If that fails it will automatically pass the session ID through the url. The nice thing is that PHP takes care of all this for you.

Can I turn autocomplete ON/OFF programmatically?

As a web developer you *cannot* turn this feature ON programmatically if the user has turned it OFF. That is not allowed.

However, if needed, a web developer can turn this feature OFF either for a particular field or for the entire form like below.

<input name="name" autocomplete="OFF" type="text">

<form autocomplete="OFF">