Archive for the ‘SQL Basics’ Category

SQL Server Code Name

SQL Server Code Name

Yesterday, I came across a very interesting question on one of the SQL Servers Online Forums.

Question – What is the code name of SQL Server 2005?

Well I knew that the Microsoft gives code names to it products when they are in developmental stages. But I never came across the code name for SQL Serves!

So I did some research on Net to find code name of all the SQL Server Release till date. They are listed below.

SQL Server Release Project Code Name
SQL Server 6.0 SQL95
SQL Server Enterprise Manager Starfighter
SQL Server 6.5 Hydra
SQL Server 7.0 Sphinx
SQL Server 7.0 Plato
SQL Server 2000 (32-bit) Shiloh
SQL Server 2000 (64-bit) Liberty
SQL Server Reporting Services Rosetta
SQL Server 2005 Yukon
SQL Server 2005 Mobile Edition Laguna
SQL Server 2008 Katmai / Akadia
SQL Report Designer 2.0 Blue
SQL Server 2008R2 Killimanjaro

 

Good Luck!

SQL Server – Difference between VARCHAR and NVARCHAR datatypes

Difference between VARCHAR and NVARCHAR datatypes

Recently I asked my friend who works as a junior DBA for last 6 months, the difference between VARCHAR and NVARCHAR datatypes. He told me that there is a minor difference between them and we don’t need to really bother about it.

Like my friend it may be a case with many people, who are not sure about the exact difference between VARCHAR and NVARCHAR datatypes. And they use them interchangeably.

VARCHAR is the abbreviation for VARiable-length CHARacter string. It can store upto 8000 characters. It uses one byte to store one character.

NVARCHAR is the abbreviation for uNnicode VARiable-length CHARacter string. It can store upto 4000 characters. It uses two bytes to store one character. NVARCHAR is nothing more then VARCHAR that supports two-byte characters. The most common use for this sort of thing is to store character data that is mixture of English and non-English symbols.

The amount of storage needed for NVARCHAR entities is twice the storage needed for VARCHAR entities.

Tip – Avoid using NVARCHAR and use VARCHAR when you know that the data which need to be stored will never constitute of non-English characters.

Good Luck!

SQL Server – Difference between TRUNCATE and DELETE statement

Difference between TRUNCATE and DELETE statement

TRUNCATE and DELETE commands are used to delete data from table. Why there are two commands for same task? Do they work similarly? Is there any difference between them? Yes, their major differences between them how they work.

The main difference is that TRUNCATE deletes all the rows from the table and reset the IDENTITY for the column to seed value. Where as DELETE can be used to delete all the rows as well as the selected rows by using it with WHERE clause. Also it does not rest the IDENTITY for the column.

More detailed text book difference between TRUNCATE and DELETE statements is given below:

TRUNCATE Command

  • TRUNCATE is faster and uses fewer system and transaction log resources then DELETE.
  • TRUNCATE removes the data by de-allocating the data pages used to store the table’s data, and only the page de-allocations are recorded in the transaction log.
  • TRUNCATE removes all rows from a table, but the table structure and its column, constraints, indexes, and so on remains.
  • You cannot use TRUANCATE TABLE on a table referenced by a FOREIGN KEY constraint.
  • Because TRUNCATE TABLE is not logged, it cannot activate the trigger.
  • TRUNCATE cannot be rolled back.
  • TRUNCATE is DDL (data definition Language) command.
  • TRUNCATE resets IDENTITY for the column to seed value.

Example:

TRUNCATE TABLE Department

DELETE Command

  • DELETE removes rows one at a time and records an entry in the transaction log for each deleted row.
  • DELETE does not reset IDENTITY counter for the column.
  • DELETE can be used with or without WHERE clause.
  • DELETE activates trigger.
  • DELETE can be rolled back
  • DELETE is DML command.

Example:

— Deletes selected rows

DELETE FROM Department WHERE DeptID=102

— Delete all the rows

DELETE FROM Department

Both TRUNCATE and DELETE commands do the same task but differently. They are meant to be used as per the requirement.

Good Luck!