SQL SERVER INTERVIEW QUESTIONS AND ANSWERS FOR FRESHER/ 1 OR 2 YEARS EXPERIENCED SET-1

COMMON SQL SERVER INTERVIEW QUESTIONS AND ANSWERS FOR FRESHER/(1or 2 Years exp)

Here is set-1 sql server common interview questions and answers, following are most frequently asked interview questions of sql server, These can be asked in any type of interview (.net). 

1). What are the different locks in Sql Server?
Ans: There are six types of locks
  • Intent
  • Shared
  • Update
  • Exclusive
  • Schema
  • Bulk Update
2). What are the different types of BACKUPs available in SQL Server 2005?
Ans: In SQL Server 2005 Backup Types are 
  • Full
  • Transaction Log
  • Differential
  • Partial
  • Differential Partial
  • File and Filegroup
  • Copy Only Database Backups
3). What are Data files?
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
  • Serializable
7). What is the difference between a DDL trigger and a DML trigger?
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?
Ans: TempDB

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
  • Full
  • Simple
  • Bulk Logged
15). What the difference between UNION and UNIONALL?
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)

Comments

  1. please send pdf to amriteshkumar143@gmail.com

    ReplyDelete
  2. Can you please send the question and answer for the email I'd given below.
    naresh.kumar959@gmail.com

    ReplyDelete
  3. Responsive desing can yield a very good revenue to a business. It has been discovered since the usage of multiple devices increase. The content furnished above too tells the same. Thanks for sharing this information in here. Please keep bloging content like this.

    Web designing course in chennai | Web designing training in chennai | PHP Training in Chennai

    ReplyDelete
  4. Here are some of the mobile app testing tools that are effective in making the mobile app success.Develop Android App

    ReplyDelete
  5. please send the entire sql server 2012 basics to sukhukasani@gmail.com

    ReplyDelete
  6. Can you please send the question and answer for the email I'd given below.
    jeripothulajsreddy@gmail.com

    ReplyDelete
  7. you can check interview questions on http://www.winmilestone.com/Topic?Interview

    ReplyDelete
  8. very Interesting content, It helped me a lot.

    ReplyDelete
    Replies
    1. Please share SQL interview questions in my E-mail id lavupadhyay786@gmail.com

      Delete
  9. Very interesting and useful interview questions keep it up GEM

    ReplyDelete
  10. Please send PDF to bhusseni64@gmail.com

    ReplyDelete

Archive

Contact Form

Send