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

1.	What is NOT NULL Constraint?
	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.

2.	What is log shipping?
	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.

3.	What is Cross Join?
	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.

4.	What is Self Join?
	A self join can be of any type, as long as the joined tables are the same. A self join is rather unique in that it involves a relationship with only one table.

5.	What is a default constraint?
	A default constraint is a value that SQL Server automatically places in a particular field in a table.
6.	What is the difference between Triggers and Stored Procedure?
	Stored Procedures are called by the programmer wherever it wants to fire but triggers fired automatically when insert,delete,update occured. And triggers can be implemented to tables & views only where as stored procedure used in the database independently.

7.	What are the properties of Sub–Query?
	§  A subquery must be enclosed in the parenthesis. §  A subquery must be put in the right hand of the comparison operator §  A subquery cannot contain a ORDER-BY clause. §  A query can contain more than one sub-queries.

8.	Where are SQL server users names and passwords are stored in sql server?
	They get stored in master db in the sysxlogins table.

9.	What are the types of subscriptions in SQL Server replication?
	There are two types of replication in sql server are : §  Push §  Pull

10.	What does REVERT do in SQL Server 2005?
	Restores your previous execution context.If you have changed your execution context with EXECUTE AS, the REVERT statement will restore the last context prior to the EXECUTE AS.
11.	What is the difference between system objects and user objects?
	§  SQL Server creates system objects during the installation process. §  They are part of the system, and most of them are necessary for SQL Server to function properly. §  Whereas system objects are part of the SQL Server system, you create user objects. §  User objects include the databases, stored procedures, functions, and other system objects that you build.

12.	Who is responsible for backing up SQL Server databases?
	SQL Server administrator.

13.	What is a Server Role?
	A server role is pre-defined by SQL Server. It possesses a pre-defined set of rights.

14.	What is the name of the system variable that returns the number of rows affected by a SQL statement?
	@@ROWCOUNT.

15.	What keyword do you use to return a value from a stored procedure?
	The OUTPUT keyword.

16.	What are different type of Collation Sensitivity?
	The different phases of transaction are :  §  Case sensitivity §  Accent sensitivity §  Kana Sensitivity §  Width sensitivity

17.	What is the difference between a check constraint and a rule?
	Check constraints and rules limit the range of values that a user can enter into a column. Whereas check constraints apply only to the table for which you enter them, you can apply rules to multiple tables.

18.	What function extracts specified characters from a string?
	The SUBSTRING function extracts specified characters from a string.

19.	What is the difference between TRUNCATE and DROP?
	TRUNCATE removes all data from the table while retaining the table structure, whereas DROP removes the table from the database.

20.	What are the different types of cursor?
	There are three types of cursors : §  Implicit cursors §  Explicit cursors §  Paramaeteried cursors