Truncate Vs Delete

Gurus,

This is the common question for the freshers when they require to remove data from tables. They don't know which query can be followed...

Really, this is an interesting question to differentiate between these two commands. Logically speaking, since DELETE logs the data for each row affected by query, it will be slower than TRUNCATE query. TRUNCATE actually deallocates data pages and removes pointers to indexes. Because of this, transaction will be faster than DELETE query.

We need to keep in mind the following restrictions while using TRUNCATE command in our query.

• The user should be db_owner, table owner or ddl_admin to execute TRUNCATE query.
• This will not work for the tables, which are referenced by FOREIGN KEY constraints.

Hope, this post helps you to understand a bit of the difference between these two commands.

Happy coding....

-Harikaran.S

SQL Server - Data types

Gurus,

Hope you are doing good. In this post, I wanna post about SQL Server data types. This question might be asked to you in the interviews or in some of the database exams you have faced.

Since, it is an interesting query, I like to share with you...

Declare @int1 Int,
@int2 Int,
@answer Numeric(9,2)
Select @int1 =5, @int2=2
Select @answer = @int1 / @int2
Print @answer

What do you think would be answer? If you think that answer is 2.5, you are wrong. The right answer is: 2

Since, the integer/the integer will return a integer, the answer is 2.

As well as in the following query:

Declare @int1 Numeric(9,2),
@int2 Numeric(9,2),
@answer Int
Select @int1 =5.5, @int2=2.0
Select @answer = @int1 * @int2
Print @answer

Here, the answer is 12.

Gurus, enjoy coding... C U later....
Take care...
-Harikaran.S

SQL Server - Copying structure of a table

Gurus,

There are two ways of copying the structure of an existing table in SQL Server. Either we can copy the structure aong with existing data or structure of table alone. Please find below the sample for both:

Copying table structure with existing data:

SELECT *INTO new_table_name
FROM old_table_name

This query will create a new table new_table_name with data present in the table old_table_name. We can use this technique to take a backup of existing table in our database.

Copying table structure without data:

At times, we may require to create a table with the existing table structure. But, we may not require to copy the existing data into that. We can use the following snippet in that case:

SELECT *INTO new_table_name
FROM old_table_name
WHERE 1>2

This query will create table new_table_name without data of the table old_table_name because of the WHERE condition.

Gurus, I hope this might be helpful for you. Will try to add more stuffs in future.

Signing off with regards,
- Harikaran.S

Vanakkam

Vanakkam....

Harikaran.S