Here are some more Questions with Answers.
Q. What is the difference between Delete and Truncate command and is it possible to rollback after Truncate command?
ANS. Delete command removes the rows from a table based on Where condition. Truncate will actually remove all the rows from a table.
Below are some points about TRUNCATE:
- TRUNCATE is faster and uses fewer system and transaction log resources than DELETE.
- 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.
- 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.
Below are some points about 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 table.
- DELETE can be rolled back.
- DELETE is DML Command.
- DELETE does not reset the identity of the table.
Q. Why we should give preference to stored procedure?
ANS. A stored procedure is a group of SQL statements that have been created and stored in a database. A procedured can accept input parameters. When the procedure is modified, all clients automatically get the new version. Stored procedures reduce network traffic and improve performance. Stored procedures can be used to help ensure the integrity of the database.
Procedure Cache is the place where Execution Plan of Stored Procedure is stored. An execution plan states the efficient way in which the query(s) is executed. So whenever a normal query is executed its Execution Plan is created but when a Stored Procedure is executed, Execution plan is created and stored in Procedure Cache. Whenever the same procedure is executed and its execution plan exists in Procedure cache then it uses that execution plan rather than creating a new plan.
Q. What is an extent?
ANS. Extent is a basic unit of storage to provide space for tables. Every extent has a number of data pages. As new records are inserted new data, pages are allocated. There are eight data pages in an extent. So as soon as the eight pages are consumed, it allocates a new extent with data pages.
While extent is basic unit storage from a database point of view, page is a unit of allocation within extent.
Q. What is the advantage of SET NOCOUNT ON?
ANS. SET NOCOUNT ON gives a performance boost to action queries by suppressing the "(n row(s) affected) message that results from running a query.
Qualifying an object with it's owner boosts performance because SQL does not have to work out where if there is a user specific version of the same object. It also gives benefits in the caching of execution plans.
The performance boost is due to the few bytes of information that make up the "(1 row(s) affected)" message not being transmitted to the client application.
With this in mind I should consider the following points:
Communication between the database and the client application on a stand-alone machine will be as fast as it is possible to get. If your front end application had it's own clock and you recorded the time from submitting the query to the time when the client finally received the full results I would expect that transmitting results across a network to be slower.
In this experiment we are carrying out a single simple insert statement. If your procedure carries out multiple operations the performance boost will be more pronounced.
For queries that retrieve data the performance boost will be less simply because the size of the "(1 row(s) affected)" message is small compared to the volume of data being returned.
In .NET applications an ExecuteNonQuery command returns the number of records affected by the operation. Set NOCOUNT ON means that the value that this call returns is always zero.
Q. Suppose I have a table that has ID as primary key with IDENTITY. Whenever a new row is inserted in table, Identity will auto incremement the value by 1.
I don't want to insert an auto incremented value but I want to give it my own value. How can I do this?
Ans. We need to use SET IDENTITY_INSERT [TABLE NAME]. This allows explicit values to be inserted into the identity column of a table.
At any time, only one table in a session can have the IDENTITY_INSERT property set to ON. If a table already has this property set to ON, and a SET IDENTITY_INSERT ON statement is issued for another table, SQL Server returns an error message that states SET IDENTITY_INSERT is already ON and reports the table it is set ON for.
If the value inserted is larger than the current identity value for the table, SQL Server automatically uses the new inserted value as the current identity value.
The setting of SET IDENTITY_INSERT is set at execute or run time and not at parse time.
Example:
SET IDENTITY_INSERT products ON
GO
INSERT INTO products (id, product) VALUES(3, 'garden shovel').
GO
SET IDENTITY_INSERT products OFF
GO
Q. What are IMPLICIT TRANSACTIONS?
ANS. Implicit Transactions are those that requires a COMMIT or ROLLBACK for every transaction.
When ON, SET IMPLICIT_TRANSACTIONS sets the connection into implicit transaction mode. When OFF, it returns the connection to autocommit transaction mode.
For example if we turn it on & update a table, the changes will not be committed until COMMIT command is executed.
Happy Learning
Sudhir
No comments:
Post a Comment