MS SQL Server

Hi Friends
Today I will be discussing Q&A on SQL Server.

Q. What is Cascade and Restrict when we use DROP table in SQL SERVER?
Ans. When we are using Drop table in SQL the syntax is simple. Drop table table_name(CASCADE / RESTRICT). We use cascade to drop table although it have some dependencies just like triggers, strored procedures, primary key,foreign key it will be deleted first. But if we use Restrict an error message is shown on using of DROP if the table have some dependencies.

Q. What is DTS in SQL Server?
Ans. If an organization is big then they might be using multiple options to store data; some people might be using EXCEL some using ACCESS and some of them might be using SQL SERVER or in some other formats also but there a problem arises that how to merge that data into one format. SQL SERVER DTS helps in this problem. It provides a set of tools using which we can customise the database according to our need. DTSRun is a command-prompt
utility used to execute existing DTS packages.

Q. What are the different types of Locks?
Ans. There are three main types of locks in SQL Server.
(1) Shared locks are used for operations that does not allow to change or update data, such as a SELECT statement.
(2) Update locks are used when SQL Server intends to modify a page, and later promotes the update page lock to an exclusive page lock before actually making the changes.
(3) Exclusive locks are used for the data modification operations, such as UPDATE, INSERT, or DELETE.

Q. What is the use of DBCC commands?
Ans. DBCC stands for Database Consistency Checker. We use these commands to check the consistency of the databases i.e., maintenance, validation task and status checks. Some of the commands are: DBCC CHECKDB - Ensures that tables in the db and the indexes are correctly linked. DBCC CHECKALLOC To check that all pages in a db are correctly allocated. DBCC SQLPERF - It gives report on current usage of transaction log in percentage. DBCC CHECKFILEGROUP - Checks all tables file group for any damage.

Happy Reading!!...

Cheers

Padhaku Einstein
Contact me at einstein.padhaku@gmail.com