Please read the Introductory Post before continuing reading interview questions and answers.
Interview Questions and Answers – Frequently Asked Questions – Day 6 of 31
List of all the Interview Questions and Answers Series blogs
Click here to get free chapters (PDF) in the mailbox
2) Common Questions Asked
Which TCP/IP port does the SQL Server run on? How can it be Changed?
SQL Server runs on port 1433. It can be changed from the Network Utility TCP/IP properties –> Port number, both on client and the server.
What is the Difference between Clustered and a Non-clustered Index?
A clustered index is a special type of index that reorders the way records in the table are physically stored. Therefore, the table can have only one clustered index. The leaf nodes of a clustered index contain the data pages.
A non-clustered index is a special type of index in which the logical order of the index does not match the physical stored order of the rows on disk. The leaf node of a non-clustered index does not consist of the data pages. Instead, the leaf nodes contain index rows. (Read more here)
What are the Different Index Configurations a Table can have?
A table can have one of the following indexes configurations:
- No indexes
- A clustered index
- A clustered index and many non-clustered indexes
- A non-clustered index
- Many non-clustered indexes
What are the Different Types of Collation Sensitivity?
Case sensitivity – A and a, B and b, etc.
Accent sensitivity – a and á, o and ó, etc.
Kana Sensitivity – When Japanese Kana characters Hiragana and Katakana are treated differently, it is called Kana sensitive.
Width sensitivity – When a single-byte character (half-width) and the same character represented as a double-byte character (full-width) are treated differently, it is width sensitive. (Read more here)
What is OLTP (Online Transaction Processing)?
In OLTP –(online transaction processing) systems, relational database design uses the discipline of data modeling and generally follows the Codd rules of data normalization in order to ensure absolute data integrity. Using these rules, complex information is broken down into its most simple structures (a table) where all of the individual atomic level elements relate to each other and satisfy the normalization rules.
What’s the Difference between a Primary Key and a Unique Key?
Both primary key and unique key enforces uniqueness of the column on which they are defined. But by default, the primary key creates a clustered index on the column, whereas unique key creates a non-clustered index by default. Another major difference is that primary key doesn’t allow NULLs, but unique key allows one NULL only. (Read more here)
What is the Difference between DELETE Â and TRUNCATE Commands?
The delete command removes the rows from a table on the basis of the condition that we provide with a WHERE clause. Truncate will actually remove all the rows from a table, and there will be no data in the table after we run the truncate command.
TRUNCATE
- TRUNCATE is faster and uses fewer system and transaction log resources than DELETE. (Read all the points below)
- TRUNCATE removes the data by deallocating the data pages used to store the table’s data, and only the page deallocations are recorded in the transaction log.
- TRUNCATE removes all the rows from a table, but the table structure, its columns, constraints, indexes and so on remains. The counter used by an identity for new rows is reset to the seed for the column.
- You cannot use TRUNCATE TABLE on a table referenced by a FOREIGN KEY constraint.
- Using T-SQL – TRUNCATE cannot be rolled back unless it is used in TRANSACTION. OR TRUNCATE can be rolled back when used with BEGIN … END TRANSACTION using T-SQL.
- TRUNCATE is a DDL Command.
- TRUNCATE resets the identity of the table.
DELETE
- DELETE removes rows one at a time and records an entry in the transaction log for each deleted row.
- DELETE does not reset Identity property of the table.
- DELETE can be used with or without a WHERE clause
- DELETE activates Triggers if defined on the table.
- DELETE can be rolled back.
- DELETE is DML Command.
- DELETE does not reset the identity of the table.
List of all the Interview Questions and Answers Series blogs
Let me know your thoughts about Interview Questions and Answers blog post.
Connect with LinkedIn.
Reference: Pinal Dave (https://blog.sqlauthority.com)
20 Comments. Leave new
Hi Pinal, 1st post, your blog is the greatest!
How can you roll back a delete? (does it have to be inside a begin commit transaction?)
Thanks!
Yes it will be rolled back only used inside a transaction
Hai pinal pls give the answer for the puzzle u asked
Great post Pinal….
Hi Pinal,
As mentioned by you for DELETE: It can be rolled back.
It seems that TRUNCATE cannot be rolled back. But TRUNCATE just like DELETE can be rolled backed if used in transaction, otherwise not. This also stands true for DELETE.
If DELETE is not in transaction then also it cannot be rolled back.
what kind of locks do delete and truncate acquire?
Great job Mr Pinal,
This is helpful for advanced and nivices users. It’s a good opportunity for checkup for those who are experienced and the news users. Keep go on spread the SQL tech.
@SDDesh:
1) Every time we use truncate statement it locks the whole table with “Schema modification (Sch-M)” lock, which is not good for a time sensitive OLTP system. So we have to carefully decide which statement to use (Delete or Truncate).
2) Delete acquires an exclusive lock only on all the rows that satisfy the where condition.
Love this series!
“TRUNCATE resets the identity of the table.”
“DELETE does not reset the identity of the table.”
Can you explain this? In the context being described here, what is “the identity of the table” referring to?
This means if a table has an IDENTITY column, or a column defined as IDENTITY, like create table xyz (col_id IDENTITY(1,1) INT, col2….)
Then if you ever truncate the table the initial IDENTITY or seed value of Col_id is reset, like here to 1.
But this does not happens with DELETE. It retains the last IDENTITY value of that column.
Thanks. I *thought* that’s what it meant; however, I was confused because earlier in the list of points it says (for TRUNCATE) “The counter used by an identity for new rows is reset to the seed for the column.” and (for DELETE) it says “DELETE does not reset Identity property of the table.” In other words, there are *two* somewhat similar “identity” reference points for each command.
I figured each command might do something *additional*, identity-wise, beyond the effect upon any IDENTITY columns.
Hi Pinal,
Im sorry Im not talkin about TRUNCATE and DELETE statements but I know u have a quick answer for me. The issue is: “Im using C# for .Net VStudio 2005 and MS SQL server2005, in the user interface in C#, Im using french to type data in a textbox of C# and then save them in the database; when I enter a text with apostrophe(like l ‘ endroit, l ‘ île, l ‘ enjeu), it refuses to save coz of the ‘ (the apostrophe). How can I capture text with apostrophe from a textbox and save them in the SQL Server database. Is the problem in C# or in SQL server ?
Dieudonné : No sure if you got the answer to your query which is more than 1 yr old. But ideally whenever you wanna enter data with ‘ (the apostrophe) you should use double ‘ (the apostrophe). So values should be entered like l ‘‘ endroit, l ‘‘ île, l ‘‘ enjeu.
Hope it helps ;)
i am making client-server application in vb.net 2005 first time. i have 3 User-Interfaces on client side and Ms sql-server on server side. How i will connect my client side forms with sql-server 2000. What will be the connection string
in vb.net to connect the Ms SQLserver 2000.
kindly helpe me
refer this http://www.connectionstrings.com
Hi Pinal ,
It is very help full for freshers who are visiting this sites you provide a very basic concepts of sql it would be great help from you if you suggest any practical queries
right madhivanan
“DELETE does not reset the identity of the table.”
This Statement is defined twice while comparing.
DELETE can be rolled back. hmmm cleared all my doubts in delete vs truncate..thank you
Hello Sir,
I attend an interview where they ask me this question, I am still not able to find Ans.,
Question: input table has a column which holds date [8- AUG -2015, 11- AUG -2015, 14- AUG -2015]
They want Output like [previous date (7-AUG-2015)-table date(8-AUG-2015)-next date(9-AUG-2015)]
[7- AUG -2015, 8- AUG -2015, 9- AUG -2015, 10-AUG-2015, 11-AUG-2015, 12-AUG-2015, 13-AUG-2015, 14-AUG-2015, 15-AUG-2015]