1. Explain about BCP and DTS.
BCP
The transfer data from an external source to SQL Server is performed using Bulk Copy Program utility. The external source is a flat file. Data transfer from external source to SQL Server in BCP IN. The transfer of data from SQL Server to external source is BCP OUT.
DTS
Data transformation services can be used to import and export data between heterogeneous data sources and SQL Server. The external data sources include Visual FoxPro, MS Excel, Paradox, MS Access, Dbase, and text files.
2.What are transactions and their properties?
A transaction is a sequence of operations performed together as a single unit of logical work. It has four properties.
Atomicity – it states that either all the data modifications are performed or none are performed.
Consistency - it states that all the data is in a consistent state after a successful completion of transaction.
Isolation – it states any data modification made my concurrent transactions must be isolated from the modifications made by other concurrent transaction.
Durability – it states that ay change made by a completed transaction remains permanently in the system.
3.Locking mechanism.
SQL Server uses the concept of locking to ensure transactional integrity and database consistency. Locking, by functionally prevents users from accessing information being changed by other users. In a multi-user environment, Locking prevents users from changing the same data at the same time. In SQL Server locking is implemented automatically.
SQL Server implements multi-granular locking, which allows transactions to lock different types of resources at different levels. SQL Server can lock the following resources.
RID – is a row identifier that individually locks a row in a table.
Key – is a rowlock with in an index.
Page – is an 8k page or an index page.
Extent – is a contiguous group of 8k data pages or index pages.
Table – is the entire table, including all data and indexes.
Database – is the complete database.
Shared Locks
It is used for operations that do not change or update the data. This allows concurrent transactions to read a resource and no other transaction can modify the data on that resource.
Update Locks
This lock is implemented when a transaction modify a row. Only one update lock is allowed on a resource at a time.
Exclusive locks
Intent locks
Schema Locks
Posts
