SQL Server general Interview Question and Answer

1.     What is Sql server?

Ans: 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?            

Ans: 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?

Ans:

1.     TRUNCATE is a DDL command whereas DELETE is a DML command.

2.     DELETE operation can be rolled back, but TRUNCATE operation cannot be rolled back.

3.     WHERE clause can be used with DELETE and not with TRUNCATE.

4.     DELETE will not reset the index but TRUNCATE will reset the table index to zero

4.     What is OLTP?

Ans: 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 Normalization?

 Ans: Normalization 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 is index in SQL server?

Ans: Indexes are built is to provide faster data access to the specific data your query is trying to retrieve. This could be either a clustered or non-clustered index. Without having an index SQL Server would need to read through all of the data in order to find the rows that satisfy the query. 

7.     What are the difference between clustered and a non-clustered index?

Ans:

1.     Clustered index is an index that stores the actual data but Non-Clustered index is logical order which does not match with physically stored data

2.     Non-Clustered index will point to Clustered index OR heap if CI not available but Clustered index does not required to point.

3.     A table can have single cluster index but same table can have 249 non-clustered indexs

4.     Custer index will be created by default when primary key is created in a table.

5.     Clustered Index leaf node will contains data pages but Non-clustered index leaf node will have reference of data page or heap.

8.     What are the System Database in Sql server 2008?            

Ans: Master

Model

Msdb

Tempdb

Resource

9.     What are the different locks in Sql Server?

Ans:    

1.     Shared (S): Used for operations that do not change or update data (read-only operations), such as a SELECT statement.

2.     Update (U):            Used on resources that can be updated. Prevents a common form of deadlock that occurs when multiple sessions are reading, locking, and potentially updating resources later.

3.     Exclusive (X):         Used for data-modification operations, such as INSERT, UPDATE, or DELETE. Ensures that multiple updates cannot be made to the same resource at the same time.

4.     Intent:        Used to establish a lock hierarchy. The types of intent locks are: intent shared (IS), intent exclusive (IX), and shared with intent exclusive (SIX).

5.     Schema:    Used when an operation dependent on the schema of a table is executing. The types of schema locks are: schema modification (Sch-M) and schema stability (Sch-S).

6.     Bulk Update (BU):   Used when bulk-copying data into a table and the TABLOCK hint is specified.

7.     Key Range: Protects the range of rows read by a query when using the serializable transaction isolation level. Ensures that other transactions cannot insert rows that would qualify for the queries of the serializable transaction if the queries were run again.

10.  What are the different types of Sub-Queries?         

Ans:

1.     Single row subquery: You can place a subquery in the WHERE clause of another query. Let's take an example of a query that contains a subquery placed in it's WHERE clause.

e.g

SELECT agent_name, agent_code, phone_no

FROM agents

WHERE agent_code =

(SELECT agent_code

FROM agents

WHERE agent_name = 'KSRM');

2.     Multiple row subquery returns one or more rows to the outer SQL statement. You may use the IN, ANY, or ALL operator in outer query to handle a subquery that returns multiple rows.

E.g.

SELECT ord_num,ord_amount,ord_date,

cust_code, agent_code

FROM orders

WHERE agent_code IN(

SELECT agent_code FROM agents

WHERE working_area='Bangalore');

3.     Multiple Column subquery You can write subqueries that return multiple columns. The following example retrieves the order amount with lowest price, group by agent code

e.g.

select ord_num, agent_code, ord_date, ord_amount

from orders

where(agent_code, ord_amount) IN

(SELECT agent_code, MIN(ord_amount)

FROM orders

GROUP BY agent_code); 

4.     Correlated Subqueries are used to select data from a table referenced in the outer query. The subquery is known as a correlated because the subquery is related to the outer query. In this type of queries, a table alias (also called a correlation name) must be used to specify which table reference is to be used.

The alias is the pet name of a table which is brought about by putting directly after the table name in the FROM clause. This is suitable when anybody wants to obtain information from two separate tables

e.g

SELECT a.ord_num,a.ord_amount,a.cust_code,a.agent_code

FROM orders a

WHERE a.agent_code=(

SELECT b.agent_code

FROM agents b WHERE b.agent_name='KSRM');

11.  What are constraints? Explain different types of constraints?       

Ans: 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. As well as RDBMS forces the integrity on database

1.     PRIMARY KEY constraint is a unique identifier for a row within a database table. Every table should have a primary key constraint to uniquely identify each row and only one primary key constraint can be created for each table. The primary key constraints are used to enforce entity integrity.

2.     UNIQUE Key constraint enforces the uniqueness of the values in a set of columns, so no duplicate values are entered. The unique key constraints are used to enforce entity integrity as the primary key constraints.

3.     FOREIGN KEY constraint prevents any actions that would destroy link between tables with the corresponding data values. A foreign key in one table points to a primary key in another table. Foreign keys prevent actions that would leave rows with foreign key values when there are no primary keys with that value. The foreign key constraints are used to enforce referential integrity.

4.     CHECK constraint is used to limit the values that can be placed in a column. The check constraints are used to enforce domain integrity.

5.     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.

The constraints can be created when the table is created, as part of the table definition by using the CREATE TABLE statement.

12.  What are the different types of BACKUPs avaialabe in SQL Server 2005?

Ans: In SQL Server 2005 Backup Types are

a)     Full

b)    Transaction Log

c)     Differential

d)    Partial

e)     Differential Partial

f)     File and Filegroup

g)    Copy Only Database Backups.

13.  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.

14.  Define Primary Key?

Ans:

·         The primary key is the columns used to uniquely identify each row of a table.

·         A table can have only one primary key

·         Null value will not be accepted in primary key

15.  What is cursors?

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

16.  What are the types of cursor?

Ans: Types of Cursors

1.     Static Cursors:A static cursor populates the result set at the time of cursor creation and query result is cached for the lifetime of the cursor. A static cursor can move forward and backward direction. A static cursor is slower and use more memory in comparison to other cursor. Hence you should use it only if scrolling is required and other types of cursors are not suitable.

2.     You can't update, delete data using static cursor. It is not sensitive to any changes to the original data source. By default static cursors are scrollable.

3.     Dynamic Cursors:A dynamic cursor allows you to see the data updation, deletion and insertion in the data source while the cursor is open. Hence a dynamic cursor is sensitive to any changes to the data source and supports update, delete operations. By default dynamic cursors are scrollable.

4.     Forward Only Cursors:A forward only cursor is the fastest cursor among the all cursors but it doesn't support backward scrolling. You can update, delete data using Forward Only cursor. It is sensitive to any changes to the original data source.

a.     There are three more types of 1. Forward Only Cursors. 2. Forward_Only KEYSET, 3. FORWARD_ONLY

b.    STATIC and FAST_FORWARD.

c.     A FORWARD_ONLY STATIC Cursor is populated at the time of creation and cached the data to the cursor lifetime. It is not sensitive to any changes to the data source.

d.    A FAST_FORWARD Cursor is the fastest cursor and it is not sensitive to any changes to the data source.

5.     Keyset Driven Cursors:A keyset driven cursor is controlled by a set of unique identifiers as the keys in the keyset. The keyset depends on all the rows that qualified the SELECT statement at the time of cursor was opened. A keyset driven cursor is sensitive to any changes to the data source and supports update, delete operations. By default keyset driven cursors are scrollable.

17.  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.

18.  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).

19.  What are the types of transaction levels in SQL SERVER?

Ans: There are four transaction levels in SQL SERVER.

1.     Read committed: Specifies that statements can read rows that have been modified by other transactions but not yet committed.

2.     Read uncommitted: Specifies that statements cannot read data that has been modified but not committed by other transactions. This prevents dirty reads. Data can be changed by other transactions between individual statements within the current transaction, resulting in non-repeatable reads or phantom data. This option is the SQL Server default.

3.     Repeatable read: Specifies that statements cannot read data that has been modified but not yet committed by other transactions and that no other transactions can modify data that has been read by the current transaction until the current transaction completes.

4.     Snapshot: Specifies that data read by any statement in a transaction will be the transactionally consistent version of the data that existed at the start of the transaction. The transaction can only recognize data modifications that were committed before the start of the transaction. Data modifications made by other transactions after the start of the current transaction are not visible to statements executing in the current transaction. The effect is as if the statements in a transaction get a snapshot of the committed data as it existed at the start of the transaction.

5.     SERIALIZABLE

a)     Statements cannot read data that has been modified but not yet committed by other transactions.

b)    No other transactions can modify data that has been read by the current transaction until the current transaction completes.

c)     Other transactions cannot insert new rows with key values that would fall in the range of keys read by any statements in the current transaction until the current transaction completes.

More info

20.  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.

21.  Define Synonym?

Ans: Synonyms is a type of SQL object that was added in SQL 2005. A synonym permits you to access an object with a different name. Synonyms can be handy when you need to do cross-database or cross-server access. The problem with this type of access is that you can't tell beforehand which name the other database or server will have, and the name could change when a new test environment is set up. By defining synonyms for the objects in the other database/server, you don't need to litter your code with dynamic SQL to handle the database/server name. All you need to do is to change the definition of the synonyms.

22.  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.

23.  What is the difference between a HAVING CLAUSE and a WHERE CLAUSE?

Ans:

a.     HAVING can be used only with the SELECT statement. HAVING is typically used in a GROUP BY clause.

b.    HAVING behaves like a WHERE clause When GROUP BY is not used.

c.     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.

24.  What are the purpose of Normalisation?   

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.

·         Normalisation reduces a complex user view to a set of small and stable subgroups of fields or relations.

25.  What are the transaction properties?

Ans:

      i.        A(Atomicity):-The sequence of operations must be atomic, either all or no operations are performed.

     ii.        C(Consistency):- When completed, the sequence of operations must leave data in consistent mode. All the defined relations/constraints must me Maintained.

    iii.        I(Isolation): A Transaction must be isolated from all other transactions. A transaction sees the data defore the operations are performed , or after all the operations has performed, it can't see the data in between.

    iv.        D(Durability): guarantees that the database will keep track of pending changes in such a way that the server can recover from an abnormal termination..

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

Ans: Local: 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.

Global: 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.

27.  What is the building block of a relational database?

Ans: The table

28.  What are the types of database recovery models?

Ans:    

      i.        Full

     ii.        Simple

    iii.        Bulk Logged

More info

29.  What the difference between UNION and UNIONALL?

Ans: Union will remove the duplicate rows from the result set while Union all does’nt.

30.  Differences between a Stored Procedure and a Trigger

Ans:

      i.        We can execute a stored procedure whenever we want with the help of the exec command, but a trigger can only be executed whenever an event (insert, delete, and update) is fired on the table on which the trigger is defined.

     ii.        We can call a stored procedure from inside another stored procedure but we can't directly call another trigger within a trigger. We can only achieve nesting of triggers in which the action (insert, delete, and update) defined within a trigger can initiate execution of another trigger defined on the same table or a different table.

    iii.        Stored procedures can be scheduled through a job to execute on a predefined time, but we can't schedule a trigger.

    iv.        Stored procedure can take input parameters, but we can't pass parameters as input to a trigger.

     v.        Stored procedures can return values but a trigger cannot return a value.

    vi.        We can use Print commands inside a stored procedure for debugging purposes but we can't use print commands inside a trigger.

   vii.        We can use transaction statements like begin transaction, commit transaction, and rollback inside a stored procedure but we can't use transaction statements inside a trigger.

  viii.        We can call a stored procedure from the front end (.asp files, .aspx files, .ascx files, etc.) but we can't call a trigger from these files.

    ix.        Stored procedures are used for performing tasks. Stored procedures are normally used for performing user specified tasks. They can have parameters and return multiple results sets.

     x.        The Triggers for auditing work: Triggers normally are used for auditing work. They can be used to trace the activities of table events.

31.  What is Stored Procedure?

Ans: A stored procedure is nothing more than Pre-Prepared SQL code that you save so you can reuse the code over and over.

Precompiled execution: SQL Server compiles each stored procedure once and then reutilizes the execution plan. This results in tremendous performance boosts when stored procedures are called repeatedly.

Reduced client/server traffic: If network bandwidth is a concern in your environment, you’ll be happy to learn that stored procedures can reduce long SQL queries to a single line that is transmitted over the wire.

Efficient reuse of code and programming abstraction: Stored procedures can be used by multiple users and client programs. If you use them in a planned way, you’ll find the development cycle takes less time.

Enhanced security controls: You can grant users permission to execute a stored procedure.

32.  What is Views in sql server?

Ans: A view is a virtual table

e.g.

CREATE VIEW [Current Product List] AS

SELECT ProductID,ProductName

FROM Products

WHERE Discontinued=No

We can query the view above as follows:

SELECT * FROM [Current Product List]

33.  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.

34.  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.

35.  What is the difference between Triggers and Stored Procedure?

Ans: 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.

36.  What are the properties of Sub–Query?

Ans: 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.

37.  Where are SQL server users names and passwords are stored in sql server?

Ans: They get stored in master db in the sysxlogins table.

38.  What are the types of subscriptions in SQL Server replication?

Ans:  There are two types of replication in sql server are :

Push

Pull

39.  What is the difference between system objects and user objects?

Ans: 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.

40.  What is a Server Role?

Ans: A server role is pre-defined by SQL Server. It possesses a pre-defined set of rights.

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

Ans: @@ROWCOUNT.

42.  What keyword do you use to return a value from a stored procedure?

Ans: The OUTPUT keyword.

43.  What is the difference between a check constraint and a rule?

Ans: 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.

44.  What function extracts specified characters from a string?

Ans: The SUBSTRING function extracts specified characters from a string.

45.  What is the difference between TRUNCATE and DROP?

Ans: TRUNCATE removes all data from the table while retaining the table structure, whereas DROP removes the table from the database.

46.  What is collation?

Ans: A collation in SQL Server is a defined set of rules for sorting and comparing data. This includes rules for case-sensitivty and accent-sensitivity. When you select a collation (for your server, database, column, or expression) you assign specific characteristics to your data that affect the results of database operations.

47.  What are different type of Collation Sensitivity?

Ans: The different phases of transaction are :

Case sensitivity

Accent sensitivity

Kana Sensitivity

Width sensitivity

48.  Define Unique Key?           

Ans: Unique key is a one or more column that must be unique for each row of the table.

It is similar to primary key. Primary key column will not accept a null. Whereas the unique key column will accept a null values.

49.  What are the difference between primary keys and foreign keys?

Ans: The primary key is the columns used to uniquely identify each row of a table.A table can have only one primary key.

Foreign keys are both a method of ensuring data integrity and a manifestation of the relationship between tables.

50.  What are some of the objects held in a database?

Ans: Tables

Columns

Data types

Stored procedures

Triggers

Rules

Keys

Constraints

Defaults

Indexes

51.  What function finds the difference between two dates?

Ans: The DATEDIFF function.

52.  What does the RETURN statement do?

Ans: The RETURN statement unconditionally exits a stored procedure without executing any other statements.

53.  What is denormalization and when would you go for it?

Ans: Denormalization is the process of attempting to optimize the performance of a database by adding redundant data or by grouping data.Denormalization is the reverse process of normalization.

54.  What is a covered index?

Ans: A covered index is a non clustered index that contains all the columns in a query

55.  What does NULL mean?

Ans: The value NULL means UNKNOWN; it does not mean (empty string). Assuming ANSI_NULLS are on in your SQL Server database, which they are by default, any comparison to the value NULL will yield the value NULL.

56.  What is the difference between char , varchar and nvarchar?

Ans: char(n)Fixed length non unicode character data with length of n bytes.n must be a value from 1 through 8,000.

varchar(n)variable length non unicode character data with length of n bytes.

nvarchar(n)variable length unicode character data of n characters. n must be a value from 1 through 4,000.

57.  How many types of triggers are there?

Ans: There are three types of triggers.

DML Triggers

--> AFTER Triggers

--> INSTEAD OF Triggers

DDL Triggers

CLR Triggers

58.  What is an identity column?

Ans: Identity columns provide an auto incrementing value for a table.

59.  Difference between a primary key and a unique key?

Ans: Primary Key

1.    Primary key is nothing but it uniquely identifies each row in a table.

2.    Primary key does not allow duplicate values, nor NULL.

3.    Primary key by default is a clustered index.

4.    A table can have only one primary key.

Unique Key:

1.    Unique key is nothing but it uniquely identifies each row in a table.

2.    Unique key does not allow duplicate values, but it allows (at most one) NULL.

3.    Unique key by default is a non-clustered index.

60.  What is a join and explain different types of joins.

Ans: SQL joins are used to fetch/retrieve data from two or more data tables, based on a join condition. A join condition is a relationship among some columns in the data tables that take part in Sql join

1.     Inner Join

2.     Outer Join

                      i.        Left outer Join

                     ii.        Right Outer Join

                    iii.        Full Outer Join

3.     Self Join

4.     Cross Join:

Inner Join: Inner join returns only those records/rows that match/exists in both the tables

Outer Join:

LEFT JOIN: Return all rows from the left table, and the matched rows from the right table

RIGHT JOIN: Return all rows from the right table, and the matched rows from the left table

FULL JOIN: Return all rows when there is a match in ONE of the tables

Self Join: Self join is used to join a database table to itself, particularly when the table has a Foreign key that references its own Primary Key. Basically we have only three types of joins : Inner join, Outer join and Cross join. We use any of these three JOINS to join a table to itself

Corss Join: Cross join is a cartesian join means cartesian product of both the tables. This join does not need any condition to join two tables. This join returns records/rows that are multiplication of record number from both the tables means each row on left table will related to each row of right table

61.  Why we use SET ROWCOUNT in Sql

Ans: Causes SQL Server to stop processing the query after the specified number of rows are returned

62.  How many clustered index can have a table

Ans: Only one

63.  What is Aggregate Functions?

Ans: Aggregate functions perform a calculation on a set of values and return a single value. Except for COUNT, aggregate functions ignore null values. Aggregate functions are frequently used with the GROUP BY clause of the SELECT statement.

e.g..

AVG,MIN,MAX,CHECKSUM_AGG,SUM,COUNT,STDEV,COUNT_BIG

64.  What is Row_Number()?

Ans: Returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.

65.  What is the STUFF function and how does it differ from the REPLACE function?

Ans: Stuff function: - This function is used to replace string from the given start position, passed as 2nd argument with string passed as last argument. In Stuff function, 3rd argument defines the number of characters which are going to be replaced.
Syntax:-STUFF ( character_expression , start , length , replaceWith_expression )
For example:  Select Stuff ('Software', 3, 3, 'abc')
This query will return the string "Soabcare". In this example, Stuff function replaces the string "Software" onwards the 3rd position('ftw') with 'abc'.

Replace Function :- Replace function is used to replace all occurrence of a specified with the string passed as last argument.
Syntax :REPLACE ( string_expression , string_pattern , string_replacement )
For example:Select Replace ('Abcabcabc', 'bc', 'xy')
This query will return the string Axyaxyaxy. In this example, Replace function replaces the occurrence of each 'bc' string with 'xy'.

66.  Difference between SET and SELECT

Ans: Both SET and SELECT can be used to assign values to variables. It is recommended that SET @local_variable be used for variable assignment rather than SELECT @local_variable.

Examples

declare @i int

set @i=1

This is used to assign constant values.

select @i=max(column_name)from table_name

for ex.

select @i=max(emp_id) from table_emp.