SQL SERVER INTERVIEW QUESTIONS PART-1

1) What is SQL?

SQL stands for Structured Query Language, it is a language used for creating, storing, fetching and updating of data and database objects in RDBMS.

2) What is DBMS?

A Database Management System (DBMS) is a program that controls creation, maintenance and use of a database. DBMS can be termed as File Manager that manages data in a database rather than saving it in file systems.

3) What is RDBMS (Relational Database Management System)?

It maintains data into the collection of tables. We can create related by common fields between the columns of the table using indexes and different constraints like primary key, foreign key etc. The use of indexes and constraints helps in faster retrieval and better management of data within the databases.

An RDBMS has the capability to recombine the data items from different files, providing powerful tools for data usage. It also provides relational operators to manipulate the data stored into the tables.

Example: SQL Server. 

4) What is a Database?

Database is nothing but an organized form of data for easy access, storing, retrieval and managing of data. This is also known as structured collection of data for faster and better access, storage and manipulation of data.

A database can also be defined as collection of tables, schema, views and other database objects.

Example: School Management Database, Bank Management Database.

5) What is a Table?

Tables are the database objects that are used for storing related records in the form of rows and columns.

6) What is a query?

A DB query is a code written in order to get the information back from the database. Query can be designed in such a way that it matched with our expectation of the result set. Simply, a question to the Database.

7) What is Sub-Query?
A sub-query is a query within another query is called as Sub-query. The outer query is called as main query, and inner query is called sub-query. Sub-Query is always executed first, and the result of sub-query is passed on to the main query.
It nested inside a SELECT, INSERT, UPDATE, or DELETE statement or inside another sub query.

Types of Sub-query:

1. Single-row sub-query: where the sub-query returns only one row.

2. Multiple-row sub-query: where the sub-query returns multiple rows, and

3. Multiple column sub-query: where the sub-query returns multiple columns to the main query. With that sub query result, Main query will be executed.

8) What are the types of subquery?

There are two types of sub-query – Correlated and Non-Correlated.

A correlated sub-query cannot be considered as independent query, but it can refer the column in a table listed in the FROM the list of the main query.

A Non-Correlated sub query can be considered as independent query and the output of sub-query are substituted in the main query.

9) What is sub query and its properties?

Properties of sub query can be defined as

·       A sub query should not have order by clause.

·       A sub query should be placed in the right hand side of the comparison operator of the main query.

·     A sub query should be enclosed in parenthesis because it needs to be executed first before the main query.

More than one sub query can be included.

10) Where SQL Server User names and Passwords are stored in SQL Server?

User Names and Passwords are stored in System Catalog Views sys.server_principals and sys.sql_logins. But passwords are not stored in normal text.

11) What do you mean by authentication modes in SQL Server?

There are two authentication modes in SQL Server.

1. Windows mode

2. Mixed Mode – SQL and Windows.

Modes can be changed by selecting the tools menu of SQL Server configuration properties and choose security page.

12) How to check SQL SERVER version?

By running the following command: SELECT @@Version

13) What are the different types of SQL Commands?

SQL commands are the set of commands used to communicate and manage the data present in the database. The different type of SQL commands are-

1. DDL - Data Definition Language
2. DML - Data Manipulation Language
3. DCL - Data Control Language
4. TCL - Transactional Control Language

14) Explain DDL commands. What are the different DDL commands in SQL?

DDL refers to Data Definition Language, it is used to define or alter the structure of the database. The different DDL commands are-

• CREATE - Used to create table in the database
• DROP - Drops the table from the database
• ALTER - Alters the structure of the database
• TRUNCATE - Deletes all the records from the database but not its database structure
• RENAME - Renames a database object

15) Explain DML commands. What are the different DML commands in SQL?

DML refers to Data Manipulation Language; it is used for managing data present in the database. Some of the DML commands are-

SELECT INSERT, UPDATE and DELETE.

16) Explain DCL commands. What are the different DCL commands in SQL?

DCL refers to Data Control Language, these commands are used to create roles, grant permission and control access to the database objects. The three DCL commands are-

• GRANT - Grants permission to a database user
• REVOKE - Removes access privileges from a user provided with the GRANT command
• Deny - Explicitly prevents a user from receiving a particular permission (e.g. preventing a particular user belonging to a group to receive the access controls.

17) Explain TCL commands. What are the different TCL commands in SQL?

TCL refers to Transaction Control Language; it is used to manage the changes made by DML statements. These are used to process a group of SQL statements comprising a logical unit. The three TCL commands are-

• COMMIT - Commit write the changes to the database
• SAVEPOINT – Save points are the breakpoints, these divide the transaction into smaller logical units which could be further roll-backed.
• ROLLBACK - Rollbacks are used to restore the database since a last commit.

18) What is Normalization?

Database normalization is a data design and organization processes applied to data structures based on rules that help build relational databases. In relational database design.
The process of table design to minimize the data redundancy is called normalization. We need to divide a database into two or more table and define relationships between them.
The main aim of Normalization is to add, delete or modify field that can be made in a single table.

Normalization usually involves dividing a database into two or more tables and defining relationships between the tables. 

19) What is De-normalization?
De-Normalization is a technique used to access the data from higher to lower normal forms of database modeling in order to speed up database access.. It is also process of introducing redundancy into a table by incorporating data from the related tables.

De-normalization is the process of attempting to optimize the performance of a database by adding redundant data. It is sometimes necessary because current DBMSs implement the relational model poorly. A true relational DBMS would allow for a fully normalized database at the logical level, while providing physical storage of data that is tuned for high performance. 

20) What are different normalization forms?

1NF: Eliminate Repeating Groups: Make a separate table for each set of related attribute, and give each table a primary key. Each field contains at most one value from its attribute domain. (or)

According to First Normal Form a column cannot have multiple values; each value in the columns must be atomic.

2NF: Eliminate Redundant Data: If an attribute depends on only part of a multi-valued key, remove it to a separate table. (or) For a table to be considered in Second Normal Form it must follow 1NF and no column should be dependent on the primary key

3NF: Eliminate Columns not dependent on key: If attributes do not contribute to a description of the key, remove them to a separate table. All attributes must be directly dependent on the primary key. (or)

BCNF: Boyce-Codd Normal Form: If there are non-trivial dependencies between candidates key attributes separate them out into distinct tables.

4NF: Isolate Independent multiple Relationships: No table may contain two or more 1: n or

n: m relationships that are not directly related.

5NF: Isolate Semantically Related Multiple Relationship: There may be practical constrains on information that justify separating logically related many-to-many relationships.

ONF: Optimal Normal Form: A model limited to only simple (elemental) facts, as expressed in Object Role Model notation.

DKNF: Domain-Key Normal Form: A model free from all modification anomalies.

Remember, these normalization guidelines are cumulative. For a database to be in 3NF, it must first fulfill all the criteria of a 2NF and 1NF database.

21) What is Stored Procedure and advantages?

A stored procedure is a named group of SQL statements that have been previously created and stored in the server database. Stored procedures accept input parameters so that a single procedure can be used over the network by several clients using different input data. And when the procedure is modified, all clients automatically get the new version. Stored procedures reduce network traffic and improve performance. Stored procedures can be used to help ensure the integrity of the database.

E.g. sp_helpdb, sp_renamedb, sp_depends etc.

CREATE PROCEDURE ProcedureName

AS

Begin

     Set of SQL statements

End

The advantages of stored procedure are-

1. Stored procedures improve performance as the procedures are pre-compiled as well as cached.
2. Make queries easily maintainable and reusable as any change is required to be made at single location.
3. Reduce network usage and traffic.
4. Improve security as stored procedures restrict direct access to the database.

 22) What are the differences between Function and Stored Procedure?

Stored Procedures	Functions
Stored procedure can return Zero or n values.	Function can return one value.
Stored procedures can have input and output parameters.	Functions can have only input parameters.
Stored procedure allows select as well as DML statement in it.	Function allows only select statement in it.
Stored procedures cannot be called from function.	Functions can be called from stored procedure.
Exception can be handled by try-catch block in a procedure.	Try-catch block cannot be used in a function.
We can go for transaction management in procedure	We can't go in function.
Stored procedures cannot be utilized in a select statement.	Function can be embedded in a select statement.

23) Can a stored procedure call itself or recursive stored procedure? How much level SP nesting possible?

Yes. Because Transact-SQL supports recursion, you can write stored procedures that call themselves. Recursion can be defined as a method of problem solving wherein the solution is arrived at by repetitively applying it to subsets of the problem. A common application of recursive logic is to perform numeric computations that lend themselves to repetitive evaluation by the same processing steps. Stored procedures are nested when one stored procedure calls another or executes managed code by referencing a CLR routine, type, or aggregate. You can nest stored procedures and managed code references up to 32 levels.

24) What is a Trigger?

Triggers are used to execute a batch of SQL code when (INSERT, DELETE or UPDATE) 
commands are executed against a table. Triggers are stored in and managed by the DBMS. It can also execute stored procedure.

Triggers are used to maintain the referential integrity of data by changing the data in a systematic fashion. A trigger cannot be called or executed; the DBMS automatically fires the trigger as a result of a data modification to the associated table.

25) What is Nested Trigger?

A trigger can also contain INSERT, UPDATE and DELETE logic within itself, so when the trigger is fired because of data modification it can also cause another data modification, thereby firing another trigger. A trigger that contains data modification logic within itself is called a Nested trigger.

26) What are Triggers in SQL?

Triggers are special type of stored procedures that get executed when a specified event occurs. Syntax-

CREATE TRIGGER

triggerName

triggerTime{Before or After}

triggerEvent{Insert, Update or Delete}    

ON tableName

FOR EACH ROW    

triggerBody

27) What is a View?

A simple view can be thought of as a subset of a table / A view is a virtual table which contains data from one or more tables combined, and it is depending on the relationship. Views restrict data access of table by selecting only required values and make complex queries easy. A View can be defined as a virtual table that contains rows and columns with fields from one or more table.

It should also be noted that as data in the original table changes, so does data in the view, as views are the way to look at part of the original table. The results of using a view are not permanently stored in the database. The data accessed through a view is actually constructed using standard T-SQL select command and can come from one to many different base tables or even other views.

1 2 3

CREATE VIEW VIEW_NAME AS SELECT COLUMN1, COLUMN2 FROM  TABLE_NAME  WHERE CONDITION;

28) What is Index?

An index is a physical structure containing pointers to the data. Indices are created in an existing table to locate rows more quickly and efficiently. It is possible to create an index on one or more columns of a table, and each index is given a name. The users cannot see the indexes; they are just used to speed up queries. Effective indexes are one of the best ways to improve performance in a database application. 

An index is performance tuning method of allowing faster retrieval of records from the table. An index creates an entry for each value and it will be faster to retrieve data.

29) What is a Clustered index?
Each table can have only one clustered index.

Clustered indexes physically sort the rows in the table based on the clustering key (by default primary key). 
Clustered index helps in fast retrieval of data from the databases. 
The leaf nodes of a clustered index contain the data pages.

Clustered index is an index that rearranges the table in the order of index itself. 

30) What is a Non-Clustered index?

There can be multiple clustered indexes in a table.

Non-clustered indexes are created outside of the database table and contain a sorted list of references to the table itself.

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. 
The leaf node of a non-clustered index does not consist of the data pages. Instead, the leaf nodes contain index rows.

31) What are Primary keys and foreign keys?

Primary keys are the unique identifiers for each row. They must contain unique values and cannot be null. Due to their importance in relational databases, Primary keys are the most fundamental of all keys and constraints. 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.

32) What is the difference between unique key and primary key?

Primary key	Unique key
Primary key doesn’t allow null values.	Unique key allows null value (although only one).
There can be only one primary key.	A table can have more than one unique key column.
Primary key creates a clustered index on the column.	Unique key column creates non-clustered index.
A Primary key is column	Uniquely identify every row in a table.

Both primary key and unique key enforce uniqueness of the column on which they are defined. But by default primary key creates a clustered index on the column, where are unique creates a non-clustered index by default. Another major difference is that, primary key doesn't allow NULLs, but unique key allows one NULL only.

33) How to implement one-to-one, one-to-many and many-to-many relationships while designing tables?

      One-to-One relationship can be implemented as a single table and rarely as two tables with primary and foreign key relationships.

      One-to-Many relationships are implemented by splitting the data into two tables with primary key and foreign key relationships.

      Many-to-Many relationships are implemented using a junction table with the keys from both the tables forming the composite primary key of the junction table.

34) What are the differences between TRUNCATE vs DELETE?

TRUNCATE	DELETE
TRUNCATE is a DDL command	DELETE is a DML command
TRUNCATE is executed using a table lock and whole table is locked for remove all records.	DELETE is executed using a row lock; each row in the table is locked for deletion.
We cannot use Where clause with TRUNCATE.	We can use where clause with DELETE to filter & delete specific records.
TRUNCATE removes all rows from a table.	The DELETE command is used to remove rows from a table based on WHERE condition.
Minimal logging in transaction log, so it is performance wise faster.	It maintain the log, so it slower than TRUNCATE.
TRUNCATE TABLE removes the data by de-allocating the data pages used to store the table data and records only the page de-allocations in the transaction log.	The DELETE statement removes rows one at a time and records an entry in the transaction log for each deleted row
Identify column is reset to its seed value if table contains any identity column.	Identity of column keep DELETE retain the identity
To use Truncate on a table you need at least ALTER permission on the table.	To use Delete you need DELETE permission on the table.
Truncate uses the less transaction space than Delete statement.	Delete uses the more transaction space than Truncate statement.
Truncate cannot be used with indexed views	Delete can be used with indexed views
Drop all object’s statistics and marks like High Water Mark free extents and leave the object really empty with the first extent. zero pages are left in the table	Keeps object’s statistics and all allocated space. After a DELETE statement is executed, the table can still contain empty pages.
TRUNCATE TABLE can’t activate a trigger because the operation does not log individual row deletions. When we run truncate command to remove all rows of table then it actually doesn’t removes any row, rather it de-allocates the data pages. In case of Truncate triggers will not be fired because no modification takes place, we have just de-allocated the data pages not deleted any row from table.	Delete activates a trigger because the operations are logged individually. When we execute Delete command, DELETE trigger will be initiated if present. Delete is a DML command and it deletes the data on row-by-row basis from a table. Which means delete is modifying the data by deleting it from the table. Triggers are fired when a DML statement executed on a table, so trigger will be fired in case of Delete command execution.

35) What is @@ERROR?

The @@ERROR automatic variable returns the error code of the last Transact-SQL statement. If there was no error, @@ERROR returns zero. Because @@ERROR is reset after each Transact-SQL statement, it must be saved to a variable if it is needed to process it further after checking it.

36) What is user defined functions?

User-Defined Functions allow defining its own T-SQL functions that can accept 0 or more parameters and return a single scalar data value or a table data type.

User defined functions are the functions written to use that logic whenever required. It is not necessary to write the same logic several times. Instead, function can be called or executed whenever needed.

37) What are all types of user defined functions?

Three types of user defined functions are.

·         Scalar Functions.

·         Inline Table valued functions.

·         Multi statement valued functions.

Scalar returns unit, variant defined the return clause. Other two types return table as a return.

38) What are cursors in SQL?

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.

This can be viewed as a pointer to one row in a set of rows. Cursor is very much useful for traversing such as retrieval, addition and removal of database records.

Cursors are objects in SQL that are used to traverse the result set of a SQL query one by one.

In order to work with a cursor we need to perform some steps in the following order:

Declare cursor

Open cursor

Fetch row from the cursor

Close cursor

De-allocate cursor.

39) What types of Joins are possible with Sql server?

Joins are used in queries to explain how different tables are related. Joins also let you select data from a table depending upon data from another table.

Types of joins are 1) INNER JOIN

                              2) OUTER JOIN

      3) CROSS JOIN

      4) OUTER JOIN

               1) LEFT OUTER JOIN

               2) RIGHT OUTER JOIN

               3) FULL OUTER JOIN.

40) What are the different types of Joins in Sql server?

Joins are used to combine records from multiple tables. The different types of joins in SQL are-

1.      Inner Join - To fetch rows from two tables having matching data in the specified columns of both the tables.

SELECT * FROM TABLE1 INNER JOIN TABLE2 ON TABLE1.columnA = TABLE2.columnA;

2. Left Join - To fetch all rows from left table and matching rows of the right table

SELECT * FROM TABLE1 LEFT JOIN TABLE2 ON TABLE1.columnA = TABLE2.columnA;

3. Right Join - To fetch all rows from right table and matching rows of the left table

SELECT * FROM TABLE1 RIGHT JOIN TABLE2 ON TABLE1.columnA = TABLE2.columnA;

4. Full Outer Join - To fetch all rows of left table and all rows of right table

SELECT * FROM TABLE1 FULL OUTER JOIN TABLE2 ON TABLE1.columnA = TABLE2.columnA;

5. Self Join - Joining a table to itself, for referencing its own data. Self-join is query used to join a table to it. Aliases should be used for the same table comparison.

SELECT * FROM TABLE1 T1, TABLE1 T2 WHERE T1.columnA = T2.columnB;

41) What is Self Join?

This is a particular case when one table joins to itself, with one or two aliases to avoid confusion. 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. The common example is when company has a hierarchical reporting structure whereby one member of staff reports to another.

Self-join is set to be query used to compare to itself. This is used to compare values in a column with other values in the same column in the same table. ALIAS ES can be used for the same table comparison.

42) What is Cross Join?

Cross join defines as Cartesian product where number of rows in the first table multiplied by number of rows in the second table. If suppose, WHERE clause is used in cross join then the query will work like an INNER JOIN.

A cross join that does not have a WHERE clause produces the Cartesian product of the tables involved in the join. The common example is when company wants to combine each product with a pricing table to analyze each product at each price.

43) What is the difference between Cross Join and Full Outer Join?

A cross join returns Cartesian product of the two tables, so there is no condition or on clause as each row of table A is joined with each row of table B whereas a full outer join will join the two tables on the basis of condition specified in the on clause and for the records not satisfying the condition null value is placed in the join result.

44) What is the difference between Union and Union all Command?

The fundamental difference between Union and Union All command is, 

Union is by default distinct i.e. it combines the distinct result set of two or more select statements.

Union All combines all the rows including duplicates in the result set of different select statements.

45) What is Raise Error?

Stored procedures report errors to client applications via the RAISERROR command. RAISERROR doesn't change the flow of a procedure; it merely displays an error message, sets the @@ERROR automatic variable, and optionally writes the message to the SQL Server error log and the NT application event log.

46) What is the difference between a HAVING CLAUSE and a WHERE CLAUSE?
They specify a search condition for a group or an aggregate. But the difference is that 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.

47) What are 3 SQL keywords used to change or set someone’s permissions?

GRANT, DENY, and REVOKE.

48) What is the difference between a local and global temporary table?

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

49) What are Locks in SQL?

Locks in SQL are used for maintaining database integrity in case of concurrent execution of same piece of data.

50) What are different types of Locks in Database?

The different types of locks in database are-

1. Shared locks - Allows data to be read-only (Select operations), prevents the data to be updated when in shared lock.
2. Update locks - Applied to resources that can be updated. There can be only one update lock on a data at a time.
3. Exclusive locks - Used to lock data being modified (INSERT, UPDATE, or DELETE) by one transaction thus ensuring that multiple updates cannot be made to the same resource at the same time.
4. Intent locks - A notification mechanism using which a transaction conveys that intends to acquire lock on data.
5. Schema locks- Used for operations when schema or structure of the database is required to be updated.
6. Bulk Update locks - Used in case of bulk operations when the TABLOCK hint is used.

51) What are Aggregate functions in SQL?

Aggregate functions are the SQL functions which return a single value calculated from multiple values of columns. Some of the aggregate functions in SQL are-

• Count() - Returns the count of the number of rows returned by the SQL expression
• Max() - Returns the max value out of the total values
• Min() - Returns the min value out of the total values
• Avg() - Returns the average of the total values
• Sum() - Returns the sum of the values returned by the SQL expression

52) What are Scalar functions in SQL?

Scalar functions are the functions that return a single value by processing a single value in SQL. Some of the widely used SQL functions are-

• UCASE() - Used to convert a string to upper case
• LCASE() - Used to convert a string to lower case
• ROUND() - Used to round a number to the decimal places specified
• NOW() - Used to fetch current system date and time
• LEN() - Used to find length of a string
• SUBSTRING() or MID() - MID and SUBSTRING are synonyms in SQL. They are used to extract a substring from a string by specifying the start and end index. Syntax – SUBSTRING (Column Name, start Index, End Index).
• LOCATE () - Used to find the index of the character in a string. Syntax – LOCATE (character, Column Name)
• LTRIM() - Used to trim spaces from left
• RTRIM() - Used to trim spaces from right.

 53) What are Transactions in SQL?

Transaction is a set of operations performed in a logical sequence. It is executed as a whole, if any statement in the transaction fails; the whole transaction is marked as failed and not committed to the database.

54) What are the properties of a Transaction?

Generally these properties are referred as ACID properties. They are:

·         Atomicity

·         Consistency

·         Isolation

·         Durability

55) What are ACID Properties?

ACID properties refers to the four properties of transactions in SQL-

1. Atomicity - All the operations in the transaction are performed as a whole or not performed at all.
2. Consistency - State of database changes only on successful committed transaction.
3. Isolation - Even with concurrent execution of the multiple transactions, the final state of the DB would be same as if transactions got executed sequentially. In other words each transaction is isolated from one another.
4. Durability - Even in the state of crash or power loss the state of committed transaction remain persistent.

56) Define UNION, UNION ALL, MINUS, INTERSECT?

UNION – Returns all distinct rows selected by either query.

UNION ALL – returns all rows selected by either query, including all duplicates.
MINUS – returns all distinct rows selected by the first query but not by the second.
INTERSECT – returns all distinct rows selected by both queries.

57) How exceptions can be handled in SQL Server Programming?

Exceptions are handled using TRY----CATCH constructs and it is handles by writing scripts inside the TRY block and error handling in the CATCH block.

58) How can we avoid duplicating records in a query?

By using DISTINCT keyword duplicating records in a query can be avoided.

59) What is the use of =,==,=== operators?

= is used to assign one value or variable to another variable. == is used for comparing two strings or numbers. === is used to compare only string with the string and number with numbers.