SQL Interview Question Answer

                                    SQL interview Question Answer Part - 1 

Question 1.  What are the differences between Stored procedures and functions?

1.  A function has a return type and must return a value while procedure does not have a return type but it may or not return values.
2. You cannot use a function with data manipulation queries. Only Select queries are allowed in functions while in procedure you can use DML queries such as insert, delete, update, select etc.
3.  A function does not allow output parameters while procedure allows  output parameters.
4. You cannot manage transactions inside a function while you can manage transactions inside a procedure.
5. You cannot call stored procedures from a function while you can call a function from a stored procedure.
6. You can call a function using a select statement while you cannot call a procedure using select statements.
7. Function does not have execution plan while procedure use execution plan.
8. Function does not use default parameter while procedure use default parameter.
9. Function can call through SQL query while procedure does not.
10. Function doesn’t support try-catch blocks while procedure supports try-catch blocks.
11. Function can not run dynamic SQL which are dynamically build in.
12. Temporary Tables can not be used in function where as we can use in store Procedure.
13. function does not support error handling, RAISEERROR or @@ERROR are not allowed.

Question 2. Why use functions instead of stored procedure in Sql or What is advantages of function.

Answer.  Lot of advantages of procedure over function, we are generally used procedure since it has all functionality like transaction, try-catch, out parameter, execution plan but only by using store procedure you are not able to use select operation, hence when you required to reusability of code and cross apply you need function. 

Question 2. Any disadvantages of store Procedure?

Answer. Above answer also we can consider disadvantages of store procedure and also Version control is not supported by the stored procedure. debugging stored procedures will either be very difficult or not possible.

Question3. What is disadvantages of function?

Answer. Take reference answer one, you can consider what  function not allowed.

Question 3. What is nesting Stored Procedures and what level it can be nested.

Answer. Stored procedures are nested when one stored procedure calls another 

You can nest stored procedures and managed code references up to 32 levels.

https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ms190607(v=sql.105)?redirectedfrom=MSDN

Although the nesting limit is 32 levels, SQL Server has no limit on the number of stored procedures that can be invoked from a given stored procedure, provided that the subordinate stored procedures do not invoke other subordinate stored procedures and the maximum nesting level is never exceeded.

Question 4. What is view in SQL and why you use view?

Answer. A view is a virtual table whose contents are defined by a query.

a view does not exist as a stored set of data values in a database. The rows and columns of data come from tables referenced in the query defining the view and are produced dynamically when the view is referenced

The main purpose of a view in SQL is, to combine data from multiple sources in a useful way without having to create yet another database table to store that data. The multiple sources can include tables and view from other database servers.

Limiting the visibility of columns(not allow all column due to security reason), rows to just those pertinent to a task

For security purposes to prevent direct access to database tables.

Grant permission to user for specific role like only View , delete , edit etc.

Question 5. What are the differences between a table and a view?

When compared with a table we have the following differences between a table and view.

• The table is physical and the view is logical.
• A table is an independent object while view is a dependent object that is a view depends on a table or tables from which it is dynamically loading the data.
• When a new table is created from an existing table the new and old tables are independent themselves that is the changes of one table will not be reflected into the other table whereas if a view is created based on a table any changes that are performed on the table reflects into the view and any changes performed on the view reflected in the table also. 

Question 6. What are the limitations of a View?

• In view you cannot pass parameters.
• Rules and Defaults cannot be associated with views.
• The ORDER BY clause is invalid in views unless TOP or FOR XML is specified.
• Views cannot be based on temporary tables 

Question 7. How many types of views are there in SQL Server?

Answer. There are two type of view in SQL server.

1. Simple view and Updatable views
2. Complex view and non-updatable views.

Question 8. What are the differences between Simple and Complex View in SQL?

1. Simple view is created from only one table while complex view is created from more than one tables.
2. In Simple view we cannot use group functions like MAX(), COUNT(), etc. while in complex view we can use group functions.
3. DML(data manipulation language like delete, update, insert) operations could be performed through a simple view. while DML operations could not always be performed through a complex view.
4. INSERT, DELETE and UPDATE are directly possible on a simple view. We cannot apply INSERT, DELETE and UPDATE on complex view directly.
5. In simple view does not include NOT NULL columns from base tables. while NOT NULL columns that are not selected by simple view can be included in complex view.

Question 9. Can we drop a table that views dependent on it?

Answer. Yes, we can drop a table even if any dependent views are associated with it. but the views that are associated with it will not be dropped. They will still execute in the database only with the status as inactive object and all those views become active and start functioning provided the table is recreated.

Question 10. Can we create a view based on other views?

Answer. Yes, we can create a view based on other views. Usually, we create views based on tables, but it is also possible to create views based on views.

Question 11. Can we update the views?

Answer. Yes, views can be updated. However, updating a view that is based on multiple tables, may not update the underlying tables correctly. To correctly update a view that is based on multiple tables we can make use “Instead OF triggers” in SQL Server.

Question 12. How can view be used to provide security layer for your app?  

Row Level Security: For example, I want an end-user, to have access only to IT Department employees. If I grant him access to the underlying tblEmployees and tblDepartments tables, he will be able to see, every department employee. To achieve this, I can create a view, which returns only IT Department employees, and grants the user access to the view and not to the underlying table.

Column Level Security: Salary is confidential information and I want to prevent access to that column. To achieve this, we can create a view, which excludes the Salary column, and then grant the end-user access to these views rather than the base tables.

Question 13. What’s the difference between CHAR and VARCHAR?

Answer. CHAR is a fixed length string data type, so any remaining space in the field is padded with blanks. CHAR takes up 1 byte per character. So, a CHAR(100) field (or variable) takes up 100 bytes on disk, regardless of the string data it holds.

VARCHAR is a variable length string data type, so it holds only the characters you assign to it. VARCHAR takes up 1 byte per character, + 2 bytes to hold length information.  For example, if you set a VARCHAR(100) data type = ‘Jen’, then it would take up 3 bytes (for J, E, and N) plus 2 bytes, or 5 bytes in all.

Question 14. When should we use CHAR instead of VARCHAR?

Answer. VARCHAR only costs two “extra” bytes, when compared to CHAR.  It’s only in rare cases where using CHAR will actually save you space and effort. Examples of those cases:

1. CHAR(2) for state abbreviation.  If your business rules say that the State column will ALWAYS be two characters long, then use CHAR(2).
2. Fixed length product codes (e.g., CHAR(8) for product codes like ‘004-3228’). Just like the state; if you have a field, like product code, that is ALWAYS (now and forevermore) a set length, then CHAR is preferred.
3. A single letter string. For example, we should use CHAR(1) for a middle initial column.

Question 15. What is difference between VARCHAR and NVARCHAR?

Answer. Unicode is used to extend beyond the English and Western Europe code pages, like if u want to store Hindi, Chinese, Japanese Kanji or Korean Hangul language. (Unicode is sometimes referred to as "double-wide")

1. Varchar is Non-Unicode variable length while Nvarchar is Unicode variable length.
2. Varchar can store max 8000 char while Nvarchar can store max 4000 Char.
3. Varchar takes 1 byte per char while Nvarchar takes 2 bytes per char.

https://www.mssqltips.com/sqlservertip/4322/sql-server-differences-of-char-nchar-varchar-and-nvarchar-data-types.