|
Part 1 | Part 2 | Part 3 | Part 4 | part 5 |
part 6 | part 7 | part 8 | part
9
QUESTION - What are the different types of
Locks?
ANSWER - Types of locks:
Shared Lock: Shared locks are used for
operations that read data, such as a SELECT statement. During
Shared locks used, concurrent transactions can read a
resource, but cannot modify the data.
Update
Lock: Update locks are used when SQL Server intends
to modify a row or page, and later promotes the update page
lock to an exclusive lock before actually making the changes.
The Update locks are used to prevent a deadlock.
Exclusive
Lock: Exclusive locks are used for data modification
operations, such as UPDATE, INSERT, or DELETE. Other
transactions cannot read or modify data locked with an
Exclusive lock.
QUESTION - What is Extent and types of Extent
?
ANSWER
- An extent is 8 continuous pages to hold server
object.
Uniform
extents - This type of extent contains data from one table.
Mixed extents
- This type of extent contains data from two to eight
different tables.
QUESTION - What is the use of DBCC commands?
ANSWER - DBCC is database consistency checker.
DBCC commands are used to check the consistency of the
databases.
DBCC CHECKDB - Ensures that tables and the
indexes are correctly linked in the database.
DBCC
CHECKALLOC - Ensures all pages are correctly allocated in the
database.
DBCC SQLPERF - Provides report on current usage
of transaction log in percentage.
DBCC CHECKFILEGROUP -
Checks all tables file group for any damage.
QUESTION - Define COLLATION?
ANSWER
- Collation is the order that SQL Server uses for
sorting or comparing textual data. There are three types of
sort order Dictionary case sensitive, Dictonary - case
insensitive and Binary.
QUESTION - Where is users names and
passwords stored in SQL Server?
ANSWER
- They are stored in master db in the sysxlogins
table.
QUESTION
- What is BCP?
ANSWER
- It is utility used to copy huge data from tables
and views without copy schema of the server object.
QUESTION
- How can we move data along with schema
of the server object?
ANSWER
- We can copy data along schema using
DTS package.
QUESTION -
Define sub-query?
ANSWER
- Sub-query is a query within a Query.
Example of
sub-query:
Select CustId,
Custname From Customer Where Cust_Id IN (Select Doct_Id from
Doctor)
QUESTION - sp_grantlogin, sp_denylogin and
sp_revokelogin
ANSWER
- All these three are the system stored procedure
used to manage windows account authentication. sp_grantlogin
allows to add and grant access to windows account.
sp_denylogin denies user to access server without deleting
windows account from SQL server. sp_revokelogin delete windows
account from SQL server.
QUESTION - Write SQL query to retrieve all
tables of the database.
ANSWER
- Select name from sysObjects where xtype=’u’
|