MS SQL Interview Questions and Answers
Question - 71 : - What’s the logical difference, if any, between the following SQL expressions?
Answer - 71 : -
-- Statement 1
SELECT COUNT ( * ) FROM Employees
-- Statement 2
SELECT SUM ( 1 ) FROM Employees
They’re the same unless table Employee table is empty, in which case the first yields a one-column, a one-row table containing zero, and the second yields a one-column, one-row table "containing a null."
Question - 72 : - Is it possible to update the Views? If yes, How, If Not, Why?
Answer - 72 : -
Yes, We can modify views but a DML statement on a join view can modify only one base table of the view (so even if the view is created upon a join of many tables, only one table, the key preserved table can be modified through the view).
Question - 73 : - Could you please name different kinds of Joins available in SQL Server?
Answer - 73 : -
OUTER JOIN – LEFT, RIGHT, CROSS, FULL ;
INNER JOIN
Question - 74 : - How important do you consider cursors or while loops for a transactional database?
Answer - 74 : -
would like to avoid cursor in the OLTP database as much as possible, Cursors are mainly only used for maintenance or warehouse operations.
Question - 75 : - What is a correlated subquery?
Answer - 75 : -
When a subquery is tied to the outer query. Mostly used in self joins.
Question - 76 : - What is faster, a correlated subquery or an inner join?
Answer - 76 : -
Correlated subquery.
Question - 77 : - Can we call. DLL from the SQL server?
Answer - 77 : -
YES, We can call. Dll from SQL Server.
Question - 78 : - What are the pros and cons of putting a scalar function in a queries select list or in the where clause?
Answer - 78 : -
Should be avoided if possible as Scalar functions in these places make the query slow down dramatically.
Question - 79 : - What are user-defined data types and when you should go for them?
Answer - 79 : -
User-defined data types let you extend the base SQL Server data types by providing a descriptive name, and format to the database. Take for example, in your database, there is a column called Flight_Num which appears in many tables. In all these tables it should be varchar(8). In this case, you could create a user-defined data type called Flight_num_type of varchar(8) and use it across all your tables.
Question - 80 : - Can You Explain Integration Between SQL Server 2005 And Visual Studio 2005?
Answer - 80 : -
This integration provides a wider range of development with the help of CLR for database servers because CLR helps developers to get flexibility for developing database applications and also provides language interoperability just like Visual C++, Visual Basic .Net and Visual C# .Net.
The CLR helps developers to get the arrays, classes and exception handling available through programming languages such as Visual C++ or Visual C# which is used in stored procedures, functions and triggers for creating database application dynamically and also provide more efficient reuse of code and faster execution of complex tasks. We particularly liked the error-checking powers of the CLR environment, which reduces run-time errors