All Tips & Questions

Tip of the day

Fully Qualify Your Database Object Names

This is a commonly ignored point; in fact, all the sample codes I have used in this website has essentially violated this tip.

In terms of database development, a fully qualified object name looks as follows: DATABASE.schema.TABLE.

Now, let's look at why fully qualified names are important, and in what situations they are necessary. The purpose of a fully qualified object name is to eliminate ambiguity.

Logically, the fully qualified name would look like DATABASE.SCHEMA.OBJECTNAME, however, syntactically (ie, in executable statements), it would simply be SCHEMA.OBJECTNAME.

Follow
SELECT * FROM dbo.TableName
instead of
SELECT * FROM TableName

- by Venkateswarlu Cherukuru

Question of the day

What is the difference between DELETE and TRUNCATE Statements in SQL?

DELETE
1. DELETE is a DML Command.
2. DELETE statement is executed using a row lock, each row in the table is locked for deletion.
3. We can specify filters in where clause
4. It deletes specified data if where condition exists.
5. Delete activates a trigger because the operation are logged individually.
6. Slower than truncate because, it keeps logs.
7. Rollback is possible.

TRUNC ATE
1. TRUNCATE is a DDL command.
2. TRUNCATE TABLE always locks the table and page but not each row.
3. Cannot use Where Condition.
4. It Removes all the data.
5. TRUNCATE TABLE cannot activate a trigger because the operation does not log individual row deletions.
6. Faster in performance wise, because it doesn't keep any logs.
7. Rollback is not possible.

DELETE and TRUNCATE both can be rolled back when used with TRANSACTION.

If Transaction is done, means COMMITED, then we can not rollback TRUNCATE command, but we can still rollback DELETE command from LOG files, as DELETE write records them in Log file in case it is needed to rollback in future from LOG files.

- by Venkateswarlu Cherukuru


We are inviting Tips & Questions from you. Click Here to share your valuable Tips & Questions on this website.

  • Your Tips & Questions will be displayed on website, after validating the information you provided.
  • Tips & Questions will be refreshed on every day 12:00 AM IST.