Top 100+ Microsoft SQL Server Technical Questions for Developers



Collated by:            Muthukumar S
Last Updated On: 02 October 2018
Download PDF

   
Explore the Top Microsoft C# Technical/ Interview Questions here: http://XploreCSharpDotNet.blogspot.com

Explore the Top Microsoft Blazor Technical/ Interview Questions here: https://XploreBlazor.blogspot.com/



    Table Definition
  • What are the different types of CONSTRAINTS that can be defined with a table/ table columns?  (Beginner)
  •    PRIMARY KEY
  •    FOREIGN KEY
  •    UNIQUE KEY
  •    NOT NULL
  •    DEFAULT 
  •    CHECK 
  • What is the difference between PRIMARY Key and UNIQUE Key constraints?  (Beginner)
  • Multiple UNIQUE constraints can be defined on a table, whereas only one PRIMARY KEY constraint can be defined on a table.
  • UNIQUE constraints can be defined on columns that allow null values, whereas PRIMARY KEY constraints can be defined only on columns that do not allow null values.
  •  PRIMARY Key column cannot allow NULLs. Whereas UNIQUE Key column allows one row to have NULL (for single column ) or one combination of NULL values (for multiple columns)
  •  UNIQUE constraints enforces uniqueness by creating Non-clustered Index whereas PRIMARY key constraints enforces uniqueness by creating a Clustered Index.      
  • How do you know the details of the constraints defined in a table?  (Expert)
                sp_helpConstraint <tableName>

  • Which datatype is used to store large binary files?  (Beginner)
  •  The datatypes VarBinary(n) or VarBinary(MAX) can be used to store large binary files like image, PDF, Excel documents, ZIP files etc.
  •  VarBinary(n) can be used to store files of size up to 8000 bytes. 
  •  VarBinary(MAX) can be used to store files of size up to 2 GB
  • What is the difference between Text and VarChar(Max)?  (Beginner)
  •  Text datatype is deprecated and not recommended by Microsoft for new development. VarChar(Max) is introduced as part of SQL Server 2005
  • The data in Text columns are stored outside the table row as a separate LOB data page. The text column in the table row would always have a pointer to LOB data page where the actual data is stored. VarChar(Max) stores the data within the table row if the length of data is less than 8000 characters. Only if the data exceeds 8000 characters, the data is stored LOB data page and pointer of it would be stored as part of the table row.
  •   Equal operator (=) cannot be used with Text columns. Also Text columns cannot be used as GROUP BY columns. VarChar(Max) supports = operator and can be used as GROUP BY column.
  • Which datatype is suitable to store hierarchical data (like Manager and subordinate relationship) in SQL Server? (Beginner)
                 hierarchyId datatype is used to store hierarchical data in SQL Server.
  • How to rename database objects?  (Beginner)
            sp_rename <oldName>  <newName>
  • How to add a NOT NULL column to an existing table with rows in it?   (Beginner)
            The NOT NULL column can be added to a table using ALTER TABLE as long as we define a DEFAULT constraint
  • What is Synonym?  (Beginner)
A Synonym is used to provide an alternative name to Tables, Views and Stored procedures.
Synonym is usually created to provide a simple name for the multi-part database objects ([ServerName].[DatabaseName].[SchemaName].[ObjectName]) so that the objects can be referred easily.
                For example: the table EmpDirectory.dbo.Employee can be easily referred by creating a synonym called 'DirectoryEmployee' as below:
CREATE SYNONYM DirectoryEmployee

FOR EmpDirectory.dbo.Employee

              The DirectryEmployee can be then be referred easily in the queries in the place of EmpDirectory.dbo.Employee.
    Insert/ Update/ Delete Data
  • How to add an empty row easily to a table with the default column values? (Expert)
            INSERT <tableName> DEFAULT VALUES;
  • Is it possible to insert an output resultset of a stored procedure to a table? (Beginner)
                   Yes. An output Resultset of a stored procedure can be added to a table/ table variable using the below syntax:
            INSERT INTO <tableName> EXEC <procedureName>
  • What happens when a column of a table is missed in the INSERT statement? (Beginner)
If the column is defined with a DEFAULT constraint, then the default value is inserted.
Else, If the column is defined as NULLable column, then NULL value is inserted.
Else, an error is thrown.
  • What is Table Value Constructor?  (Beginner)
The table value constructor allows multiple rows of data to be specified in a single DML statement. The table value constructor can be specified in the VALUES clause of the INSERT statement, in the USING <source table> clause of the MERGE statement, and in the definition of a derived table in the FROM clause.
For example:
         INSERT INTO City (Country, Name)
         VALUES ('India', 'New Delhi'), ('Japan', 'Tokyo'),
                ('USA' , 'Washington')

         SELECT Task.Description, TaskStatus.Caption
         FROM
            ( VALUES (1, 'Open'), (2,'Closed'),
                     (3, 'Inprogress'), (4, 'In Quality Check')
            ) AS TaskStatus(Id, Caption)

           INNER JOIN Task ON Task.StatusId = TaskStatus.Id

  • What is the difference between INSERT INTO..SELECT and SELECT..INTO?  (Beginner)
  • INSERT INTO...SELECT is used to add the output of a SELECT query to an existing table. 
  • SELECT..INTO is used to create a new table and then add the output of a SELECT query to the new table. The table is created based on the column name and column types of the SELECT query. 
  • How do you create a new empty table based on the output of a SELECT query? (Beginner)
          SELECT TOP(0) * INTO <emptyTableName> FROM <query>
  • What is the difference between DELETE and TRUNCATE?  (Beginner)
  • DELETE can have filter criteria whereas TRUNCATE always deletes all the rows.
  • TRUNCATE is faster than DELETE because it just deals with de-allocation of data page of a table. 
  • TRUNCATE will not call Triggers whereas DELETE will call Triggers 
  • What are the four ON CASCADE rules available with UPDATE and DELETE?  (Beginner)
         ON DELETE { NO ACTION | CASCADE | SET NULL | SET DEFAULT }
         ON UPDATE { NO ACTION | CASCADE | SET NULL | SET DEFAULT }
  • How do you find the rows which are affected by an UPDATE statement? How to get the old values which are overridden by the update statements?  (Beginner)
 OUTPUT clause and Deleted virtual table in the UPDATE statement is used to find out the old values which are overridden by the update statement.
            For example:
   UPDATE SupportTicket
   SET Status = 4
   OUTPUT Deleted.Status OldStatus, Inserted.Status NewStatus

   WHERE CreatedDate = '01/01/2018'

          The above UPDATE statement returns the recordset of the previous and the new values of Status column after the updation.
  • How to insert  value to a identity column explicitly instead of a auto generated value?  (Beginner)      
The identity insert can be switched on using the below statement:
           SET IDENTITY_INSERT <tableName> ON
  • What is the difference between @@IDENTITY, SCOPE_IDENTITY( ) and IDENT_CURRENT( ) ?  (Beginner)      
  •   @@IDENTITY returns the last identity value generated by the last INSERT statement that inserted a row in any tables. It considers only the last INSERT statement executed in the current session/ connection.
  •  SCOPE_IDENTITY( ) returns the last identity value generated by the last INSERT statement that inserted a row in any tables. It consider only the last INSERT statement that executed within a scope of a block/ stored procedure/ trigger. 
  •  IDENT_CURRENT(<tableName>) returns the last identity value generated by the last INSERT statement that inserted a row in the given <tableName> in any session/ connection.
  • What is the Difference between IDENTITY and SEQUENCE?  (Beginner)
  • IDENTITY is available since the introduction of SQL Server 6.0. Whereas, the SEQUENCE is introduced in SQL Server 2012.
  • IDENTITY is defined specific to a table column which means the identity value can be generated only for that particular column. SEQUENCE is not specific to a table, it means the generated value can be assigned to any table columns.
  • IDENTITY is always defined using CREATE TABLE/ ALTER TABLE. SEQUENCE is defined through CREATE SEQUENCE.
  • IDENTITY values are generated only when a row is inserted to a table through INSERT. SEQUENCE values can be fetched using NEXT VALUE FOR <sequence Name>. 
  • We can get the current value of IDENTITY using IDENT_CURRENT(<tableName>). We can get the current value of sequence by reading Current_Value from the SYS.Sequence table.
  • Maximum value for IDENTITY cannot be specified. Maximum value for SEQUENCE can be specified.
  • IDENTITY value cannot be restarted automatically. SEQUENCE values can be restarted automatically when maximum value is reached.
  • IDENTITY does not support Caching. SEQUENCE supports Caching which avoid disk I/O when values are generated and thus improves performance.
  • IDENTITY generated value can be roll-backed when INSERT is used within Transaction. SEQUENCE value never reset to its previous value when the TRANSACTION is roll-backed.
  • How to synchronize the rows of table-A with an another table-B of same structure?
    •  (Beginner)
MERGE statement can be used to synchronize the tables. MERGE statement can be used 
  • to update the table-A rows with the corresponding matching rows in table-B.
  • to delete a row from the table-A if the row does not exist in table-b
  • to insert a row to the table-A if a row exist in table-B but does not exist in table-A
         For example:

            MERGE tableA A
            USING tableB B
              ON A.Col1 = B.Col1
            WHEN Matched THEN
              UPDATE
              SET A.Col2 = B.Col2
            WHEN NOT MATCHED BY TARGET THEN
              INSERT (Col1, Col2, Col3)
              VALUES (Col1, Col2, Col3)
            WHEN NOT MATCHED BY SOURCE THEN

              DELETE;
  • How to log the changes (insert/update/delete) made by the Merge statement?
    •  (Expert)
The following MERGE statement with the OUTPUT clause, INTO clause and the inserted & deleted magic tables is used to log the changes made to target table, tableA:

         MERGE tableA A
         USING tableB B
                ON A.Col1 = B.Col1
         WHEN Matched THEN
                UPDATE SET A.Col2 = B.Col2
         WHEN NOT MATCHED BY TARGET THEN
                INSERT (Col1, Col2, Col3) VALUES (Col1, Col2, Col3)
         WHEN NOT MATCHED BY SOURCE THEN
                DELETE
         OUTPUT deleted.col1, deleted.col2, deleted.col3, $action,
                inserted.col1, inserted.col2, inserted.col3

                INTO tableC

 After the successful completion of MERGE, the tableC (must exist already) would contain the old and new column values of each row in the table, tableA: 
  •   The 'deleted' magic table would return the old value for UPDATE & DELETE statements and NULL values for INSERT statement 
  •    The 'inserted' magic table would return the new value for UPDATE & INSERT statements and NULL values for DELETE statement. 
  •   The '$action' would return type of operation: INSERT, UPDATE or DELETE.
  • Which method is used to add a new child/node under the given hierarchyId ?
    •  (Expert)
              GetDescendant method of hierarchyId type is used to return the new child hierarchyId node.
For example, to add a new employee called 'John' under the manager, 'Helen':
               
    DECLARE @ManagerId hierarchyId

    SELECT @ManagerId = Id
    FROM   Employee
    WHERE  [Name] = 'Helen'

    INSERT INTO Employee (Id, [Name], ManagerId)
    VALUES ( @ManagerId.GetDescendant(NULL, NULL), 'John', @ManagerId)



    Predefined Functions
  • What is the difference between CONVERT() and TRY_CONVERT() functions? (Beginner)
  •  CONVERT() throws an error when conversion is failed.
  • TRY_CONVERT() returns NULL when conversion is failed.
  • What is the difference between CONVERT() and PARSE()  (Beginner)
  • CONVERT converts between any two data type. PARSE() converts only from string to date/time and number types only
  • CONVERT() takes Style parameter to format the converted value. PARSE() does not take style parameter.
  • CONVERT() does not take culture parameter. PARSE() can take culture parameter to parse the values in the given culture
  • CONVERT() is faster and does not require .Net Framework to be setup in the Server. PARSE() is slower and requires .Net Framework to be installed in the Server.
  • What is the difference between ISNULL,  NULLIF() and IIF functions?  (Beginner)
  • ISNULL(p1, p2) - Returns p1 if p1 IS NOT NULL. Returns p2 if p1 is NULL
  • NULLIF(p1, p2) - Returns NULL if p1 = p2. Returns p2 if p1 <> p2
  • IIF(expr, p1, p2) - Returns p1 if expr is true. returns p2 if expre is false.
  • What is the difference between CHOOSE and COLESCE()  (Beginner)
  • CHOOSE(index, val1, val2 [,valn]) - Returns val1 if index = 1. Returns val1, if index = 2, Returns valn if index = n
  • COALESCE(p1, p2 [, p3]) - Returns first non-null parameter. Returns p1 if p1 is NOT NULL else Returns p2 if p2 is NOT NULL else Returns pn if pn is NOT NULL else Return NULL
  • What is the difference between + and CONCAT() function?  (Beginner)
  • p1 + p2 + p3  returns NULL if any of the operand is null (p1 or p2 or p3 is NULL )
  • CONCAT(p1, p2, p3)  if any of the operand is null, it treat the NULL as empty string. It returns empty string if all p1, p2 and p3 are NULL
    Querying Data
  • What is the difference between Subquery and Correlated Subquery?  (Beginner)
  •  Subquery is independent of outer query. It is executed first and the resultant value(s) is returned to the outer query. The outer query is executed next and returns the final results. The Subquery is executed only once.
  •  Correlated subquery is dependent on the outer query. The outer query is executed first and for each row of the outer query, the subquery is executed. The correlated subquery is executed multiple times.
  •  How NULL values are handled by DISTINCT?  (Beginner)
             For the DISTINCT keyword, null values are considered to be duplicates of each other. When DISTINCT is included in a SELECT statement, only one NULL is returned in the results, regardless of how many null values are encountered.
  • What is the difference between TOP (bigint) and TOP(float) PERCENT  (Beginner)
                TOP (bigint) returns top no. of rows out of given set of rows whereas TOP(float) PERCENT returns the top no. of rows based on the given percent and total no. of rows in the set.
  • What is the difference between TOP(n)  and TOP(n) WITH TIES ?  (Expert)
  • The TOP(n) returns exactly n number of rows after the resultset is sorted by the ORDER By expression.
  •  The TOP(n) WITH TIES might return more than n rows after the resultset is sorted. If there are duplicate values exist for the last element in the results, TOP(n) WITH TIES returns those duplicate rows as well.
For example, if a table T1 have 6 rows with the column C1 as 1, 2, 2, 3, 3, 4 
      SELECT TOP(4) C1 FROM T1 ORDER BY C1 returns 1, 2, 2, 3

      SELECT TOP(4) WITH TIES C1 FROM T1 ORDER BY C1 would return 1, 2, 2, 3, 3 as the last row value(3) has duplicates.
  • What are the wild card characters available with LIKE?  (Beginner)
  •    %  - Any string of zero or more characters.
  •     _  - Any single character.
  •    [ ] - Any single character within the specified range (for example, [a-f]) or set (for example, [abcdef]).
  •    [^] - Any single character not within the specified range (for example, [^a - f]) or set (for example, [^abcdef]).

  • What are advantages of calling dynamically built queries using EXEC sp_executesql over EXEC() ?  (Beginner)
  •  EXEC sp_executesql can execute dynamically built queries with embedded parameters whereas EXEC() does not support embedded parameters.
  •  EXEC sp_executesql can be faster when called multiple times as the execution plan for the query is reused by the SQL Server.
  •  EXEC sp_execuetsql with embedded parameters avoids SQL Injection Attacks whereas calling EXEC() without embedded parameters has the risk of SQL Injection.
  •  EXEC sp_executesql with embedded parameters avoid being called with VARCHAR/ DATE parameter values enclosed with single quote characters.
For example, in the below script the parameter ProductCode is enclosed with single quote:
EXEC (N'SELECT ProductName, ProductDescription, Price FROM Product WHERE ProductCode = ''P102'' ')

 whereas in the below script, the parameter @ProductCode does not require single quote:
EXEC sp_executesql N'SELECT ProductName, ProductDescription, Price FROM Product WHERE ProductCode = @ProductCode', N'@ProductCode VARCHAR(4)', @ProductCode = 'P102'
  • How the hierarchyId datatype column values are displayed by default by the management studio and how to show the values in materialized path pattern? (Beginner)
           The hierarchyId column values are by default displayed as hexadecimal values. To view the values in materialized path pattern, ToString() method has to be called.
           For example, the following query returns the EmployeeId  hierarchyid column value as hexadecimal and materialized path pattern:
     SELECT EmployeId AS IdHex,
                     EmployeeId.ToString() AS IdPath,
                     EmployeeName

               FROM Employee
    Index
  • What is an index?  (Beginner)
                 An Index is created based on one or more columns of a table. The index consists of the column values (for which index has been created) and the corresponding pointer/ references of the related row in the table. The indexed column values would be stored in ascending/alphabetical order.
The index is used to improve the performance of the queries. Whenever a search is to be performed on the indexed columns, SQL Server first finds that value in the index, and then reads the pointer/ reference to locate the entire row of data in the table.
  • What are the two types of Index available in SQL SERVER?  (Beginner)
            Clustered Index and Non-clustered Index
  • What is a Clustered Index?  (Beginner)
            A clustered index determines the physical order of data in the table. PRIMARY key constraints creates clustered index automatically if no clustered index available for the table already 
  • What is a Non-clustered Index?  (Beginner)
             A non-clustered index is analogous to an index in a textbook. The data is stored in one place, the index is another, with pointers to the storage location of the data.
  • Can a table have more than one Indexes?  (Beginner)
               A table can have more than one Non-clustered Indexes and maximum of one Clustered Index
  • How to get index details of a table? (Beginner)
        sp_helpindex <Table Name>
  • Can a index be created in Descending order? (Expert)
         The internal algorithms of SQL Server can navigate equally efficiently in both directions on a single-column index, regardless of the sequence in which the keys are stored. Specifying order is more useful only when there are multiple columns and when there is a requirement to run queries like, ORDER BY ObjName ASC, ObjWeight DESC
  • What happens to Indexes of a table when the table is dropped?  (Beginner)
                When table is dropped, the dependent indexes are deleted automatically.
  • What is Full Text Index??  (Expert)
                Full Text Index helps user to perform the following kind of searches in VARCHAR columns:
    •  Case insensitive searches: cases are ignored while performing searches.
    •  Ignoring Stop words in Searches: discards stop words like 'a', 'is', 'the' etc. that can occur frequently but that typically do not help when searching for particular text.
    •  Thesaurus based on search: Finds out the text which are similar in meaning of the given search words(for example, searching the word, 'intelligent' can fetch the rows with the words, 'brilliant', 'smart' or 'knowledgeable')
    •  Proximity Search: helps you to find words which are close to an another word (for example, search for the words, 'humpty' which occurs nearer to the word, 'wall')  
    •  Logical Operators in search text: Helps to perform this kind of search, 'Marina OR Beach NOT Hotel', Finds out the rows with the word, 'Marina' or 'beach' but not the word 'hotel'. 
    • Inflectional forms of search text: Helps to find out the rows with the equivalent singular or plural form of the search words (like 'foot' when the search word is 'feet'). Also finds out the rows with equivalent inflectional terms( like  'drives', 'drove', 'driving', 'driven' etc. when the search word is 'drive')
    Group Functions
  • DISTINCT is supported in which group functions?  (Beginner)
                 SUM, AVG and COUNT
  • Which of the group function do not ignore the NULL values?  (Beginner)
                 COUNT(*) don't ignore the NULL values, whereas SUM, AVG, COUNT, MAX, and MIN ignore null values.
  • What is the difference between COUNT(*), COUNT(<columnName>) and COUNT(DISTINCT <columnName>)?  (Beginner)
                  COUNT(*) - Returns the count of all the rows.
                  COUNT(<columnName>) - Returns the count of rows which have non null value in the column, <columnName>
                  COUNT(DISTINCT <columnName>) - Returns the count of  rows which have non null value in the column, <columnName>. Only the unique values in the column are considered for counting. Ignores the duplicate values in the column.
  • What is Ranking function and What are the different Ranking functions available in SQL Server?  (Beginner)
                 Ranking functions return a ranking value for each row in a partition. The Ranking functions available in SQL Server are RANK, DENSE_RANK, NTILE and ROW_NUMBER
  • What is the difference between RANK and  DENSE_RANK functions?  (Beginner)
Whenever there is tie in row values, RANK function assign same rank for the tied rows and skips the next rank when ranking the subsequent values. Whereas DENSE_RANK function does not skips the ranks.
For example, see below the Rank and DenseRank functions applied on the below 'Marks' field
Marks Rank DenseRank
200           1             1
199           2             2
199           2             2
198           4             3
197           5             4
197           5             4
197           5             4
196           7             5
    Table JOINs
  • What are the different types of JOINs available in SQL Server?  (Beginner)
              Inner Join, Left Join/ Left Outer Join, Right Join/ Right Outer Join and Full Join/ Full Outer Join, Self-Join and Cross Join. 
  • What is Self-Join?  (Beginner)
Joining a same table more than once using the alias is a Self-Join. Self-Join queries are mostly used when we need to query a table which has self-references. For example, an employee table can be defined with the columns: Id, Name and ManagerId. Here a foreign key can be defined on ManagerId referencing 'Id' column (Primary Key) of the same table. When we have to list all employee names along with the manager names, we may have to use Self-Join as below:
       SELECT
           Emp.Name, Manager.Name
       FROM
           Employee Emp
              INNER JOIN Employee Manager

                 ON Emp.ManagerId = Manager.Id

  • What is Cross Join?  (Beginner)
  • Cross Join of two tables returns every possible combination of table rows.  Each row in the first table is matched with every row in the second table.
  • Cross Join would not have any joining condition.
  • It is also called Cartesian Join or Cartesian Product
  • If table A has x number of rows and table B has y number of rows, then cross join between table A and B would results in x * y number of rows.
  • For example, Consider the below tables: Goal(GoalId, GoalName), Employee(EmpId, EmpName) and EmpGoal (EmpId, GoalId, IsAchieved, AchievedDate). The following query can be used to populate EmpGoals table with the list of goals (defined in Goals tables) for each employee (defined in Employee table):
INSERT INTO EmpGoal(EmpId, GoalId,IsAchieved, AchievedDate)
          SELECT
                Employee.EmpId, Goal.GoalId, 0, NULL
          FROM
                Employee

                      CROSS JOIN Goal
  • What is the CROSS APPLY? (Expert)
The CROSS APPLY is kind of join which usually connects a table and a correlated subquery or table valued function.
        The row value in the left side table is applied to the correlated subquery/ table valued function in the right side.
        For example:
       SELECT
           Department.DeptID, Department.Name,
           TopEmpSalary.EmpName, TopEmpSalary.Salary
       FROM
           dbo.Department
       CROSS APPLY
        (
             SELECT TOP(5) EmpName, Salary
             FROM
               dbo.Employee
             WHERE
               Employee.DeptId = Department.DeptID
             ORDER BY Salary DESC

        ) TopEmpSalary

         In the above query, for each row in the Department (Left table), DeptId is applied to the right side query to find out the top 5 employees who is getting highest salaries.
It is also possible to create a table valued function which takes DeptId as the parameter and returns top 5 salaries of the department.
       The table valued function then can be used at the right side of the CROSS APPLY as below:
    SELECT
           Department.DeptID, Department.Name,
           TopEmpSalary.EmpName, TopEmpSalary.Salary
         FROM
            dbo.Department
              CROSS APPLY

               dbo.GetTop5Salary(Department.DeptId)  TopEmpSalary
  • How OUTER APPLY is different from CROSS APPLY? (Expert)
CROSS APPLY returns a row only if there is a matching row exist in the right side query, for a row in the left side table.
        In case if a row in the left side table does not have a matching rows generated by the query at the right, the row (in the left side table) is omitted from the final result.
        For example in the below query, if there are rows in Department table which does not have any matching rows in Employee table then that Department row will not be part of the output.
   SELECT
        Department.DeptID, Department.Name,
        TopEmpSalary.EmpName, TopEmpSalary.Salary
   FROM
        dbo.Department
   CROSS APPLY
   (
         SELECT TOP(5) EmpName, Salary
         FROM
            dbo.Employee
         WHERE
            Employee.DeptId = Department.DeptID
         ORDER BY Salary DESC
   ) TopEmpSalary

          If we replace CROSS APPLY with OUTER APPLY in the above query, even the departments with no employees are returned as part of the output with NULL values in EmpName and Salary columns.

    Views

  • What is a View?  (Beginner)
               A view is a definition built on top of other tables and do not hold data themselves.  It can represent data from one or more tables. It can also represent a subset of table rows.  It can be queried like any other table(s). If data is changing in the underlying table(s), the same change is reflected in the view. It is referred as 'Virtual table' since it does not store any data rows.
  • What are the advantages of  Views?  (Beginner)
  • It helps to secure a portion of data to the users. A View is usually created to have only the required rows (by having WHERE clause) and the required columns (by specifying the list of columns in SELECT clause). It is possible to restrict the access to the underlying tables referred in views and provide the access only to the Views. In this way, users can see the authorized rows and columns through the view but would not be able to see the other rows and columns through the underlying table.
  • It helps to reduce the complexity of the queries and thus improves the readability of the queries. For example, a query can be a JOIN of multiple tables, can have GROUP BY, Group functions and HAVING clause, can have WHERE clause with multiple Boolean expression connected with AND/ OR logical operators etc. It is easy to query the view than the underlying complex SQL behind the view.
  • What are the disadvantages of using views?  (Beginner)
  • The performance of a query is impacted when the query is used as a view. When view is referred in an another query, the underlying query has to be retrieved and merged with the main query before the execution.
  • The nested views reduces the readability of the queries. It is important to keep the nested level of views to be very minimum to understand the queries easily.
  • A view can be defined with an ORDER BY clause?  (Beginner)
              No. A view cannot be created with ORDER BY clause.
           Exception: A view is allowed to be created with ORDER BY clause when TOP is specified in the SELECT clause.
  • Can a view be created on temporary tables or table variables?  (Beginner)
   No. A view cannot be created based on temporary tables and/or table variables.
  • What happens when the underlying tables used by views/ stored procedures are altered? (Expert)
When the tables referred in a view are dropped or altered, SQLServer would throw error only when the view is queried. Similarly when  the tables referred in a stored procedures are dropped or altered, the SQL Server would throw error only when stored procedures is executed.
        To prevent the referred tables being dropped/ altered,  WITH SCHEMABINDING clause can be used when you create View or Stored procedure.
        For example:
  CREATE VIEW dbo.vwCity
         WITH SCHEMABINDING
       AS

         SELECT CityName, CountryName FROM City

        The above view would prevent to make any changes to the the 'City' table.
  • How to secure the content of views and stored procedures so that others cannot read the scripts? (Expert)
           Create the view/stored procedure using 'WITH ENCRYPTION' clause to encrypt its content so that end users cannot see the business logic coded in the objects.
              For example:  
       CREATE VIEW dbo.vwCity
         WITH ENCRYPTION
       AS

         SELECT CityName, CountryName FROM City

 When the view/ stored procedure are protected using 'WITH ENCRYPTION', no one can view its content including the author of the scripts. It is necessary to keep the script safely as it cannot be decrypted.
  • A view is updatable?  (Expert)
Views are updatable (Insert/ Update) as long as
- the insert statement sets the values for all the NOT NULL columns of a table when the view is defined based on only one table.
- the insert/Update statement sets the values for the columns pertaining to only one table when the view pulls data from more than one table using JOINs
- the view is not defined with GROUP BY clause and  group/ aggregate functions.
- the view is not defined with a derived table and the columns of the derived table are listed in the SELECT clause.
  • What happens when a row is inserted into a view using an INSERT statement and it does not satisfies the WHERE conditions of the view?  (Expert)
After INSERT, when the view is queried for the row, it does not appear in the result. But the row would be actually added to the base tables of view, successfully.
The row does not appears in the result as it is obvious that the row does not satisfies the WHERE conditions of the view.
To prevent the row being added to the view that does not satisfy the WHERE condition, the view should be defined with 'WITH CHECK OPTION' as follows:
   CREATE VIEW dbo.ITEmployee
    AS
        SELECT Id, FirstName, LastName
        FROM dbo.Employee
        WHERE DepartmentId = 10

    WITH CHECK OPTION

 When we insert a row to the view, 'ITEmployee', we should ensure that DepartmentId is always 10. If not, the insert statement will fail because of the WITH CHECK OPTION, used while defining the view.
  • An index can be created on a View?  (Expert)
            Yes. When the CREATE INDEX statement is executed the result set of the view SELECT is stored permanently in the database. Future SQL statements that reference the view will have substantially better response times. Modifications to the base data are automatically reflected in the view.
    Set Operators
  • What are the different SET operators available in SQL Server? (Beginner)
               UNION, UNION ALL, INTERSECT and EXCEPT
  • What is the difference between UNION and UNION ALL operators?  (Beginner)
               UNION selects only distinct rows where UNION ALL selects all the rows.
  • When the output of UNION of queries is to be copied to a table, to which queries, the INTO <tableName> is to be provided. (Beginner)
The INTO clause should be provided to the first query as below:

  SELECT Col1, Col2 INTO Table4 FROM Table1
          UNION
       SELECT Col1, Col2 FROM Table2
          UNION

       SELECT Col1, Col2 FROM Table3
  • When the order is to be specified in the UNION of queries, to which query ORDER BY clause is to be provided?  (Beginner)
The ORDER BY clause should be provided to the last query as below:

     SELECT Col1, Col2 FROM Table1
        UNION
     SELECT Col1, Col2 FROM Table2
        UNION
     SELECT Col1, Col2 FROM Table2

     ORDER BY Col1
  • What is the purpose of COALESC function?  (Beginner)
               COALESC function evaluates the arguments in order and returns the first Non null argument. If all arguments are NULL, COALESCE returns NULL.
  • What is Common Table Expression (CTE)?  (Beginner)
               A common table expression (CTE) can be thought of as a temporary result set that is defined within the execution scope of a SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. A CTE is similar to a derived table in that it is not stored as an object and lasts only for the duration of the query. Unlike a derived table, a CTE can be self-referencing and can be referenced multiple times in the same query.
    Temporary Table
  • How the temporary tables are named and where the temporary tables are stored?  (Beginner)
                    Temporary tables are created by naming them with single hash (#) or double hashes (##) as the prefix. They are stored in tempdb database. Temporary tables are deleted automatically when the SQL connection (that created it) is closed.
  • What is the difference between local temporary table and Global temporary table?  (Beginner)
  • The Local temp table should be created with a name that begins with one number sign (#). The Global temp table should be created with a name that begins with two number signs (##)
  • The Local temp table is visible only to the connection that created it. The Global temp table is visible to all the connections.
  • The Local temp table is dropped automatically when the connection that created it is closed. The Global temp table is dropped automatically when the connection that created it is closed and when all other connection stopped referencing it. No new connection can reference a global temp table after the connection that created it disconnects. 
  • What is the difference between temporary table and TABLE variable? (Expert)
  • The scope of TABLE variable is limited to procedure/ batch where it is defined. The scope of (local) temporary table is limited to the session/ connection where it is defined.
  • TABLE variables are more suitable for few hundred rows whereas temporary table can have huge volume of rows.
  • TABLE variables do not have statistics. So, the query optimizer may choose a poor execution plan. The temporary table have statistics, so the query optimize may choose a better execution plan.
  • TABLE variable cannot have foreign keys. Also SELECT .. INTO cannot be used with TABLE variable. The temporary table supports foreign keys and SELECT...INTO 
    Functions and Stored Procedures
  • What is the difference between User Defined Functions and Stored procedures?  (Beginner)
  • Function can be called as an expression in SELECT clause. Stored Procedure cannot be called as part of expressions.
  • Function should be always called with two part name - [schema].[functionName]. Stored procedure need not be called with two part name.
  • Functions cannot make updations to the tables. Stored procedures can update rows in the tables.
  • Functions cannot have dynamic queries. Stored procedures can have dynamic queries.
  • Function cannot take OUTPUT parameters. Stored procedures can take OUTPUT parameters.
  • Function should return a value to the calling expression. Stored procedures can return status to the calling blocks but it is not mandatory.
  • What are the advantages of Stored procedures?  (Beginner)
Faster Execution:    Since SPs are precompiled and the execution plans are cached, SPs run faster than the inline query.
Modular programming:     The implementation of Complex business logic can be broken into multiple simple blocks in the form of the stored procedures.

Reduced network traffic:   Since an SP is a block of statements or inline queries, it reduces the network traffic by calling the SP once instead of calling the multiple inline queries one after the other.

Security mechanism:       The direct access to the tables processed by the SP can be restricted and user can be given access only to run the procedures.

Shared Application logic across applications:    SPs can be called by multiple applications and hence the business logic can be implemented in central location and can be changed easily in the future.
  • What is an inline table-valued user defined function? (Beginner)
              An inline table-valued function comprises of a SQL query and returns a rowset to the calling block. It's return type is TABLE datatype. The inline table-valued function can be called wherever a table can be referred in the SELECT query. For example, the inline table-value function can be called in the FROM clause of a SELECT in the place of a table.
  • What is the advantage of an inline table-valued user defined function over Views? (Beginner)
                    An inline table-valued function has all the advantages of views. Both the objects are used to represent a SELECT query. Both the objects can be referred in FROM clause of the SELECT query. But, an inline table-valued user defined function can have parameters and the parameters can be used to restrict the rows returned by the function. Whereas, view cannot have parameters and the SELECT statement that refers the view in the FROM clause should have WHERE clause to restrict the rows.
  • What is the difference between Inline Table valued function and mutli-statement table valued function? (Beginner)
Inline Table Valued function (I-TVF) is executed faster than multi-statement table valued function (MS-TVF) when called from scripts.
I-TVF is simple and should be defined to have only one SELECT Statement whereas MS-TVF can have multiple SQL statements.
I-TVF returns TABLE object without column definition whereas MS-TVF returns TABLE with column definition.
  • What is the difference between EXECUTE and sp_executesql?  (Beginner)
             sp_executeSql supports parameter substitution whereas EXECUTE does not.
  • What is the difference between RAISERROR and THROW?  (Beginner)
  • RAISERROR is older way of raising business errors to the calling procedures or front end applications. THROW is the new way of raising errors since the introduction of SQLServer 2012
  • RAISERROR does not cause the batch to be ended. The batch can continue execution with the statements next to the RAISERROR. THROW causes the batch to be ended and returns the exception to the calling batch.
  • RAISEERROR can throw system error messages defined in sys.messages. THROW cannot throw the system errors. But it can rethrow the system errors caught in the CATCH block.
  • Severity level of errors can be specified in RAISERROR. THROW always assumes the Severity level of errors as 16.
  • RAISERROR always throw a new exception in CATCH block. THROW without any parameters can re-throw the original exception caught in the CATCH block.
  • RAISERROR allows substitution parameters in the message parameter (printf formatting styles). THROW does not support printf style message formats.
  • RAISERROR always require messageid/error id be defined in sys.messages table. THROW does not require error number to be defined in sys.messages 
  • How to search for stored procedures defined in the database based on the name pattern?  (Beginner)
The system procedure, sp_stored_procedure is used to search for the stored procedures based on the name pattern. 

         For example, to search for the procedures that starts with usp_Product: 


     sp_stored_procedures 'usp_Product%'


  • What are the different ways of returning data/ status from stored procedures to the calling code? (Beginner)

The data can be returned in the form of 
Output parameters: A Scalar value can be returned to the calling code through output variables.
Result sets: A stored procedure can return data through one or more SELECT statements.
Table population: A table can be populated as part of stored procedure and can then be used by the calling code.
The status of the procedure can be returned in the form of 
RETURN: The execution status of the procedure can be returned to the calling code using a RETURN statement.
RAISERROR or THROW: Informational message or error messages can be returned to the calling code through RAISERROR or THROW.
  • How to schedule one or more stored procedures to run automatically at a fixed frequency? (Beginner)
SQL Server Agent tool is used to schedule the stored procedures to run automatically at a fixed frequency.
    Triggers
  • How to know the list of triggers defined on a table?  (Expert)
         sp_helpTrigger  <TableName>
  • What are the different types of Triggers available in SQL Server?  (Beginner)
           a) AFTER Trigger       
           b) INSTEAD OF Trigger
  • What is the order of execution when both AFTER trigger and INSTEAD OF trigger are defined on a table?  (Beginner)
              First INSTEAD OF trigger is executed followed by AFTER trigger whenever DML statements are executed on the tables.
  • When constraints are checked with respect to the execution of trigger?  (Expert)
              If constraints exist on the trigger table, they are checked after the INSTEAD OF trigger execution but prior to the AFTER trigger execution. If the constraints are violated, the INSTEAD OF trigger actions are rolled back and the AFTER trigger is not executed.
  • What is the main difference between AFTER trigger and INSTEAD OF trigger? (Beginner)
             a) AFTER trigger is executed after the triggering action (INSERT/UPDATE/DELETE) whereas INSTEAD OF trigger is executed in place of triggering action.
             b) Multiple AFTER triggers can be defined on a table whereas only one INSTEAD OF trigger can be defined per triggering action (INSERT/ UPDATE/ DELETE)
             c) AFTER trigger is applicable only to tables whereas INSTEAD OF trigger is applicable for both table and view.
  • If we have multiple AFTER triggers on a table, how can we define order of the triggers' execution?  (Expert)
         sp_SetTriggerOrder
  • What are the Magic tables ?  (Beginner)
               Magic tables are the internal tables accessible within a trigger.  It is used to store the row values which are inserted, updated or deleted by the DML statement that caused the trigger execution. There are two Magic tables:
    • INSERTED  - Stores the row values which are inserted during INSERT operation and the NEW row values which are updated during the UPDATE operation.
    • DELETED  - Stores the row values which are deleted during DELETE operation and OLD row values which are updated during the UPDATE operation.
    Cursor
  • What is a Cursor? (Beginner)
          A cursor is a kind of database object which is used to process a results set data, one row at a time.
  • What are the operations/steps that are performed with Cursor? (Beginner)
  • DECLARE CURSOR
  • OPEN
  • FETCH
  • CLOSE
  • DEALLOCATE CURSOR
  • What are the disadvantages of Cursor? Is it possible to write alternative code to replace cursor? (Beginner)
  • Cursor consumes more memory resources at the Server and hence the performance of the database Server might get affected overall when used by multiple parallel users excessively.
  • Cursor is slower as it processes one row at a time and requires round trip to server for each row of the result set.
  •  It is always possible to write code using temporary tables and/ or WHILE loop without using Cursor to implement the same functionality.
  • There are few exceptions where cursor cannot be replaced. For example if a stored procedure (which belongs to an another application's database and there is no access to the source code or base tables) have to be called for each row of a result set, then Cursor has to be used.
    Database Locks
  • What is Deadlock? (Expert)
Deadlock is a condition/ state which arises when two (or more) database transactions have a set of table rows locked, and each transaction requests a lock on a set of rows that another transaction has already locked.
        For example, deadlock is said to be occurred in the following case:
  •  Transaction A locks table rows R1 and waiting to lock the table rows R2 to complete.
  •  Another parallel running transaction B locks table rows R2 and waiting to lock the table rows R1 to complete.
  • What happens when deadlock occurs? (Expert)
  • SQL Server has a special thread named deadlock monitor which watches for deadlock situation (every 5 seconds by default).
  • When its detects a deadlock, it rollbacks one of the transaction which is the reason for deadlock (and hence becomes a deadlock victim), so that an another transaction (which is also a reason for deadlock) can complete successfully.
  • The deadlock victim is chosen based on how much of work done/ resources locked by the transaction.  The transaction which has done least amount of work would be the candidate for deadlock victim.
  • When the deadlock victim is chosen, SQL Server rollbacks the transaction and throws an error (number 1205) to the calling code.

  • How to prevent deadlock? (Expert)
  • Always update the table rows in one particular order. For example update table A followed by table B in all the procedures where it is required to update both the tables. 
  • Reduce the duration of a transaction so that transaction can complete quickly thus it can release the locks quickly.
    • Move out the code which is not required to be part of the transaction, so that transaction can be completed quickly.
    • Optimize the performance of the statements (for example by creating suitable indexes) which are part of the transaction, so that transaction can be completed quickly.
    • Do not increase the isolation level of the transaction, so that transaction can be completed quickly.
  • What is a database transaction? (Beginner)
A transaction is a logical unit of work that contains two or more SQL statements. The transaction guarantees that, in case, even if one of the SQL statements is failed, the changes made by all the other completed SQL statements within it are roll-backed/ undone. If all the SQL statements of a transaction are executed successfully, then the database changes made by them are committed.
  • What is Transaction Isolation level? (Expert)
Transaction Isolation level defines how a transaction is isolated from an another parallel running transaction.
        It determines whether to allow/ disallow dirty reads, non-repeatable reads and phantom rows within a transaction when an another parallel running transaction modifies the same set of rows.
  • What are the different levels of Transaction Isolation available in SQL Server? (Expert)
  • Read uncommitted - Allows Dirty reads, Non-repeatable reads and Phantoms.
  • Read committed - Disallows Dirty reads. 
  • Repeatable read - Disallows Dirty reads and Non-repeatable reads. 
  • Serializable - Disallows Dirty reads, Non-repeatable reads and Phantoms.
Download PDF

   
Explore the Top Microsoft C# Technical/ Interview Questions here: http://XploreCSharpDotNet.blogspot.com