Ans: In DB testing we need to check for,
1. The field size validation
2. Check constraints.
3. Indexes are done or not (for performance related issues)
4. Stored procedures
5. The field size defined in the application is matching with that in the db.

Ans:
Types of Transact-SQL

SQL Server Provides three types of Transact-SQL statements namely DDL, DCL, and DML.
Data Definition Language (DDL)
It allows creating, altering and dropping database objects.
Data Control Language (DCL)
It is used to control access to data in the database.
It controls permission on the database objects using grant, revoke or deny statement.
Data Manipulation Language (DML)
It involves retrieval, insertion, deletion, modification in the database.
It includes select, insert, update, and delete command.

Ans:
The start and end of each transaction
Every data modification
Every extent allocation or de allocation
The creation or dropping of a table or index

Ans: NULL value means that no entry has been made into the column. It states that the corresponding value is either unknown or undefined. It is different from zero or "". They should be avoided to avoid the complexity in select & update queries and also because columns which have constraints like primary or foreign key constraints cannot contain a NULL value.

Ans: CREATE DEFAULT, CREATE PROCEDURE, CREATE RULE, CREATE TRIGGER, and CREATE VIEW statements cannot be combined with other statements in a batch.
A batch can't contain altered command and then the new columns referenced command of the same table.
The EXECUTE keyword is not required, when an EXECUTE statement is the first statement in a batch.

Ans: NOLOCK table hint used using WITH clause, is similar to read uncommitted. This option allows dirty reads are allowed. Using this option, shared locks cannot be issues to other transactions. This prevents existing transactions to not read incorrect data.

Ans: Authentication modes in SQL Server:
Windows: Allows user to authenticate based on the MS Windows account credentials.
Mixed Mode: Allows users to connect either through Windows authentication or an SQL Server authentication mode. Administrator might maintain user accounts in SQL Server.

Ans:
Identity Column

• Column with identity property contains unique system generated value in the table.
• Column with identity property is similar to AutoNumber field in MS Access.
• You can set start and increment value while setting column property to identity.
• Column with identity property contains unique values within the table.

Unique identifier, GUID

• The column with unique identifier property contains globally unique data.
• SQL server uses unique identifier property for merge replication.
• Same like Identity Column, SQL server supports unique identifier column that also contains system generated value.
• A column or local variable of unique identifier data type can be initialized using NEWID function.

Ans:
SQL server supports following locks

• Shared lock
• Update lock
• Exclusive lock

Shared lock
Shared Lock allows simultaneous access of record by multiple Select statements.
Shared Lock blocks record from updating and will remain in queue waiting while record is accessed for reading.
If update process is going on then read command will have to wait until updating process finishes.
Update locks
This lock is used with the resources to be updated.
Exclusive locks
This kind of lock is used with data modification operations like update, insert or delete.

Ans:
While importing data, the destination table must already exist.
While exporting to a file, bcp will create the file.
Need permissions to bulk copy data for source and destination files and tables.
Need INSERT and SELECT permissions to bulk copy data from a data file into a table