SQL Server Interview Question And Anser For Fresher And Experience Part 2

1.	What is Sql server?
	SQL - Structured query language is the standard command set used to communicate with the relational database management system. Sql server - is commonly used as the backend system for websites and corporate CRMs and can support thousands of concurrent users.SQL Server is much more robust and scalable than a desktop database management system such as Microsoft Access.

2.

What are the System Database in Sql server 2005?

§  Master - Stores system level information such as user accounts, configuration settings, and info on all other databases. §  Model - database is used as a template for all other databases that are created §  Msdb - Used by the SQL Server Agent for configuring alerts and scheduled jobs etc §  Tempdb - Holds all temporary tables, temporary stored procedures, and any other temporary storage requirements generated by SQL Server.

3.	What is the difference between TRUNCATE and DELETE commands?
	TRUNCATE is a DDL command whereas DELETE is a DML command. Hence DELETE operation can be rolled back, but TRUNCATE operation cannot be rolled back. WHERE clause can be used with DELETE and not with TRUNCATE.

4.	What is OLTP?
	Online Transaction Processing (OLTP) relational databases are optimal for managing changing data. When several users are performing transactions at the same time, OLTP databases are designed to let transactional applications write only the data needed to handle a single transaction as quickly as possible.

5.	Define Normalisation?
	Normalisation is an essential part of database design. A good understanding of the semantic of data helps the designer to built efficient design using the concept of normalization.

6.	What are the difference between clustered and a non-clustered index?
	A Clustered index is a special type of index that reorders the way in which each records in the table are physically stored. A Non clustered index is a special type of index in which the logical order of the index does not match the physical stored order of the rows on disk.

7.	What are the System Database in Sql server 2008?
	§  Master §  Model §  Msdb §  Tempdb §  Resource

8.	What are the different locks in Sql Server?
	§  Intent §  Shared §  Update §  Exclusive §  Schema §  Bulk Update

9.	What are the different types of Sub-Queries?
	§  Single row subquery §  Multiple row subquery §  Correralted row subquery

10.	What are constraints? Explain different types of constraints?
	Constraints is a rule or restriction concerning a piece of data that is enforced at the data level. A Constraint clause can constrain a single column or group of columns in a table. There are five types of Constraint namely §  Null / Not Null §  Primary Key §  Unique §  Check or Validation §  Foreign Key or References Key

11.	What are the different types of BACKUPs avaialabe in SQL Server 2005?
	In SQL Server 2005 Backup Types are §  Full §  Transaction Log §  Differential §  Partial §  Differential Partial §  File and Filegroup §  Copy Only Database Backups.

12.	What are Data files?
	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.

13.	Define Primary Key?
	§  The primary key is the columns used to uniquely identify each row of a table. §  A table can have only one primary key. §  No primary key value can appear in more than one row in the table.

14.	What is cursors?
	Cursor is a database object used by applications to manipulate data in a set on a row-by-row basis, instead of the typical SQL commands that operate on all the rows in the set at one time.

15.	What is SQL Profiler?
	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.

16.	What is the difference between DELETE and TRUNCATE statement?
	§  A DELETE statement enables you to selectively remove data from a table, whereas §  The TRUNCATE statement unconditionally removes all rows from a table.

17.	What are the types of transaction levels in SQL SERVER?
	There are four transaction levels in SQL SERVER. §  Read committed §  Read uncommitted §  Repeatable read §  Serializable

18.	What is the difference between a DDL trigger and a DML trigger?
	§  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).

19.	What database does SQL Server use for temporary tables?
	TempDB.

20.	What is a linked server?
	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.

21.	What are the transaction properties?
	§  Atomicity §  Consistency §  Isolation §  Durability

22.	Define Synonym?
	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.

23.	What is an active database?
	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.

24.	What is the difference between a HAVING CLAUSE and a WHERE CLAUSE?
	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.

25.	What are the purpose of Normalisation?
	§  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. §  Normalisation reduces a complex user view to a set of small and stable subgroups of fields or relations.

26.	What are the types of database recovery models?
	§  Full §  Simple §  Bulk Logged

27.	What the difference between UNION and UNIONALL?
	Union will remove the duplicate rows from the result set while Union all does’nt.

28.	What is the building block of a relational database?
	The table

29.	What is the difference Function and Stored Procedure?
	§  UDF can be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section where as Stored procedures cannot be. §  UDFs that return tables can be treated as another rowset. This can be used in JOINs with other tables. §  Inline UDF›s can be though of as views that take parameters and can be used in JOINs and other Rowset operations.

30.

What is the difference between a local and a global variable?

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.