COMMON SQL SERVER INTERVIEW QUESTIONS AND ANSWERS FOR FRESHER/(1or 2 Years exp)
1). What are the different locks in Sql Server?
Ans: There are six types of locks
- Bulk Update
Ans: In SQL Server 2005 Backup Types are
- Transaction Log
- Differential Partial
- File and Filegroup
- Copy Only Database Backups
Ans:This is the physical storage for all of the data on disk. Pages are read into the buffer cache when users request data for viewing or modification. After data has been modified in memory (the buffer cache), it is written back to the data file during the checkpoint process.
4). What is SQL Profiler?
Ans: SQL Profiler is a graphical tool that allows system administrators to monitor events in an instance of Microsoft SQL Server. You can capture and save data about each event to a file or SQL Server table to analyze later.
5). What is the difference between DELETE and TRUNCATE statement?
Ans: A DELETE statement enables you to selectively remove data from a table, whereas
The TRUNCATE statement unconditionally removes all rows from a table.
6). What are the types of transaction levels in SQL SERVER?
Ans:There are four transaction levels in SQL SERVER.
- Read committed
- Read uncommitted
- Repeatable read
Ans: A DDL trigger executes in response to a change to the structure of a database (for example, CREATE, ALTER, DROP).
A DML trigger executes in response to a change in data (INSERT, UPDATE, DELETE).
8). What database does SQL Server use for temporary tables?
9). What is a linked server?
Ans: A linked server enables you to work with other SQL Servers as well as databases other than SQL Server databases, right from within Management Studio.
10). Define Synonym?
Ans: Synonym is an alternative method to creating a view that includes the entire table or view from another user it to create a synonym.
A synonym is a name assigned to a table or view that may thereafter be used to refer to it.
11). What is an active database?
Ans: Active database is a database that includes active rules, mostly in the form of ECA rules(Event Condition rules). Active database systems enhance traditional database functionality with powerful rule processing cabalities, providing a uniform and efficient mechanism for database system applications
12). What is the difference between a HAVING CLAUSE and a WHERE CLAUSE?
Ans: HAVING can be used only with the SELECT statement. HAVING is typically used in a GROUP BY clause. When GROUP BY is not used, HAVING behaves like a WHERE clause. Having Clause is basically used only with the GROUP BY function in a query whereas WHERE Clause is applied to each row before they are part of the GROUP BY function in a query.
13). What are the purpose of Normalization?
Ans: Minimize redundancy in data.
Remove insert, delete and update anamoly during the database activities.
Reduce the need to reorganize data it is modified or enhanced.
Normalization reduces a complex user view to a set of small and stable subgroups of fields or relations.
14). What are the types of database recovery models?
Ans: There are 3 types of database recovery models available
- Bulk Logged
Ans: Union will remove the duplicate rows from the result set while Union all does'nt.
16). What is the difference between a local and a global variable?
Ans: A Local temporary table exists only for the duration of a connection or, if defined inside a compound statement, for the duration of the compound statement.
A Global temporary table remains in the database permanently, but the rows exist only within a given connection. When connection are closed, the data in the global temporary table disappears. However, the table definition remains with the database for access when database is opened next time.
17). What is NOT NULL Constraint?
Ans: A NOT NULL constraint enforces that the column will not accept null values. The not null constraints are used to enforce domain integrity, as the check constraints.
18). What is log shipping?
Ans: Log shipping is the process of automating the backup of database and transaction log files on a production SQL server, and then restoring them onto a standby server. Enterprise Editions only supports log shipping. In log shipping the transactional log file from one server is automatically updated into the backup database on the other server.
19). Define Joins?
Ans: A Join combines columns and data from two or more tables (and in rare cases, of one table with itself).
20). What is Cross Join?
Ans: A cross join that does not have a WHERE clause produces the Cartesian product of the tables involved in the join. The size of a Cartesian product result set is the number of rows in the first table multiplied by the number of rows in the second table.
.pdf will be avaliable soon (after 100 questions).
Credit/Ref : Ankur Jain (C# Corner)