HI WELCOME TO SIRIS

SQL Server Interview Questions and Answers

Leave a Comment

Top SQL Server interview questions and answers for freshers, 2-5 year experienced programmers and database administrators with examples.

1) What is SQL or Structured Query Language?
SQL is a language which is used to communicate with the database and this language supports operations like insertion, updation, retrieval and deletion.
2) Explain Relational Database Management System (RDBMS)?
RDBMS is database management system which is used to maintain the data records in the tables and also indices in tables. Relationships can be created to maintain the data in the table.
3) Explain the properties of a relational table?
Below are the list of properties relational table should have –
  • Column value of any kind.
  • Insignificant sequence of columns.
  • Insignificant sequence of rows.
  • Unique name for each column.
  • Atomic values.
4) What is ACID mean in Sql Server?
ACID is used for evaluating application and database architecture. Below are the ACID properties –
  • Atomicity
  • Consistency
  • Isolation
  • Durability
5) What are the difference between “Where” and “Having” clause in Sql Server?
“Where” clause is used to filter the rows based on condition. “Having” clause used with SELECT clause and this is used with GROUP BY clause. If GROUP BY clause not used then “HAVING” clause works like a “WHERE” clause.
6) Explain primary key in Sql Server?
This is the combination of fields/columns which are used to uniquely specify a row. Primary Key has a unique constraint defined on the column and the value in the column cannot be NULL.
7) Explain unique key in Sql Server?
Unique Key constraint will enforce the uniqueness of values in the list of columns of the table. No duplicate values are allowed. Unique key will allow NULL in one row unlike Primary Key.
8) Explain foreign key in Sql Server?
Foreign key is used to establish a relationship between the columns of other table. Foreign key relationship to be created between two tables by referencing a column of the table to primary key of another table.
9) What is the use of “JOIN” in Sql Server?
“JOIN” is used to get the data from multiple tables by joining those. Keys created in the tables will play a major role in the “JOIN”.
10) Explain the types of JOINS in Sql Server?
Below are the list of JOINS in Sql Server –
  • Inner Join
  • Right Join
  • Left Join
  • Full Join
11) In which TCP/IP port does Sql Server run?
By default it runs on port – 1433 and it can be changed from “Network Utility TCP/IP” properties.
12) Why to use Stored Procedures in Sql Server?
Stored Procedures are mainly used for reusability and security for data. Stored Procedures are pre-compiled files so whenever the application want to use run the stored procedure it will not be compiled again.
13) List out some advantages and disadvantages of stored procedure in Sql Server?
Advantages
  • Testing
  • Maintainability
  • Speed
  • Optimization
  • Security etc.
Disadvantages
  • Portability
  • Limited Coding functionality
14) List out some differences between DELETE and TRUNCATE?
Below are the following differences between DELETE and TRUNCATE –
  • DELETE can be rolled back but TRUNCATE cannot be rolled back.
  • When executing the query DELETE will keep lock on row unlike TRUNCATE which keeps lock over whole table.
  • TRUNCATE will reset the value of identity column whereas DELETE will not do that.
  • Trigger will be fired in case of DELETE but in case of TRUNCATE it will not.
15) Explain COLLATE keyword in Sql Server?
COLLATE keyword can be applied to either column definitions or database definitions. For example
SELECT EmpId FROM Employee ORDER BY EmpId COLLATE Latin1_General_CS_AS_KS_WS ASC;
16) List out the differences between Global and Local temp tables in Sql Server?
Global temp tables can be created with – “##” and it will be visible to all active sessions and this temp table will be deleted when all active sessions are abandoned or disconnected. Local temp table will be visible to only to the user who created and users of other session will not be able to see this. And this will be deleted once the table creator session is abandoned or disconnected.
17) List out the different types of locks available in Sql Server?
Below are the list of locks available in Sql Server –
  • Update Locks
  • Shared Locks
  • Exclusive Locks
18) What are the differences between Left join and Inner join in Sql Server?
  • Left join will return all the rows from the left table and matching rows from right table. “Left Join” and “Left Outer Join” are used interchangeably because records which are returned will be the same with either of these.
  • Inner join matches the common records in two tables joined. In Inner join each record of table A Is matched with each record of Table B and the matched records are then be displayed in the resultant table.
19) List out the differences between Clustered Index and Non Clustered Index in Sql Server?
  • Clustered Index – Clustered Index will reorder the number of records in a table and by default the reordering will be done on the basis of primary key as it default acts like a clustered index.
  • Non Clustered Index – Non Clustered Index depends on clustered index internally. Leaf nodes will not be data pages as in clustered index instead it will have index rows, which acts like a pointer to point to root node of clustered index.
20) List the different types of collation sensitivities in Sql Server?
Below are the list of collation sensitivities in sql server –
  • Case sensitivity
  • Accent sensitivity
  • Kana Sensitivity
  • Width sensitivity
21) Why to use UPDATE_STATISTICS command in Sql Server?
This command is used to update the index of the table whenever there is a bulk insertion or updation or deletion in the table.
22) Explain Sql server authentication modes?
Below are the two authentication modes of sql server –
  • Mixed Mode 
  • Windows Mode
  • 23) Explain Mixed authentication mode of sql server?
    Mixed mode of authentication can either use SQL Server authentication or Windows authentication. If the user opt using windows authentication then the validation will happen in the operating system level and if the user opt for Sql server authentication then the password has to be set up while installing.
    24) How the authentication mode can be changed?
    Authentication mode can be changed using following steps –
    Start -> Programs -> Microsoft SQL Server -> “SQL Enterprise Manager” and run SQL Enterprise Manager.
    25) What is recursive stored procedure in Sql Server?
    Recursive stored procedure is the stored procedure called as child stored procedure inside the parent or main stored procedure. This can be done easily in Sql Server by using “EXEC” keyword in a stored procedure. For example
    Create Procedure SP_Test
    AS
    BEGIN
     EXEC sp_Child @params
    END
    26) What is Normalization and DeNormalization in Sql Server?
    • Normalization – It’s the process of minimizing dependency and redundancy by properly organizing the fields or columns of the table.
    • DeNormalization – It is the process of accessing the data from higher to lower normalization forms.
    27) List out different types of normalizations in Sql Server and explain each of them?
    Below are the types of normalizations –
    • 1 NF – Removing the duplicate records from the table by assigning primary key to a table.
    • 2 NF - Meet all the requirements of 1 NF and create the relationship between the tables and segregate the data storing between multiple tables.
    • 3 NF - Meet all the requirements of 2 NF. Remove the list of columns from the table which does not meet primary key constraint.
    • 4 NF – Tables should not more than two relationships
    • 5 NF – Practical constraints on info for justifying the many-to-many relationships.
    28) What you mean by Unique Index in Sql Server?
    Unique Index will not allow a column to have duplicate values. Unique Index is associated with Primary key by default.
    29) Explain View in Sql Server?
    It is a virtual table which will have the data from multiple tables. Views can be used to retrieve / update / delete the rows. When the data in the table changes in view also it changes.
    30) Explain Indexing and what are the advantages of it?
    Indexing contains pointers to the data in a table. Indexes are created in a table to retrieve the data quickly. So Indexing improves the performance as the retrieval of data takes less time. Indexing will be done for columns which are being used more often while retrieving.
    31) What is the significance of QUOTED_IDENTIFIER ON in Sql Server?
    When “QUOTED_IDENTIFIER” set ON, all identifiers should be delimited by double quotation marks and all literals or single quotation marks or single quotation mark.
    32) Why to use Cross Join in Sql Server?
    Cross join gives the “cartesian product” of joined tables. In this join no need to specify the conditions while joining the tables.
    33) Why to use “STUFF” keyword in Sql Server? Give an example of it.
    “STUFF” keyword is used for inserting one string into other string. For example
    SELECT STUFF('testing', 3, 3, 'Hey');
    Output – teHeyng
  • 34) Explain “CHECK Constraint” in Sql Server?
    “CHECK Constraint” will be used when the column value has to be restricted within a limit. This constraint can be set in the column level.
    35) Why to use “DISTINCT” in Sql Server?
    “DISTINCT” keyword is used to remove the duplicate values in a given column value. For example
    SELECT DISTINCT column_name From table
    36) Explain Trigger in Sql Server?
    Trigger is used for initiating any action when the operation like Insert/Update/Delete is occurred on an object.
    37) Explain Common Table Expression (CTE) in Sql Server?
    CTEs are used to make the query easier. Whenever there are too many joins are being used in a query then we can use CTEs to make it simple. For example
    With test
    AS
    (
     SELECT col1,col2
     FROM table
    )
    SELECT * FROM test
    38) Why to use “IN” clause in Sql Server?
    “IN” clause is used to specify multiple values in WHERE clause. For example
    SELECT * FROM Employees WHERE City IN (‘bangalore’,’Kochin’)
    39) What does man by SQL Wildcard Characters in Sql Server?
    WildCard Characters are used with “LIKE” operator in Sql Server. Wildcards are used for data retrieval process from the table. Some of the wildcards are
    • “-“ - This is used for substituting a single character.
    • “%” - This is used for substituting zero or more characters.
    • [listofchars] – Ranges of characters for matching.
    40) Explain “NOT NULL Constraint” in Sql Server?
    “NOT NULL Constraint” is used in a column to make sure the value in the column is not null. If this constraint has not set then by default columns will accept NULL values too.
    41) Explain “@@ROWCOUNT” and “@@ERROR” in Sql Server?
    • @@ROWCOUNT - Used to return the number of rows affected in the table due to last statement. 
    • @@ERROR – Used to return the error code which is occurred due to last SQL statement. ‘0’ means there are no errors.
    42) Why to use Cursor in Sql Server?
    Cursor is used in case of row traversal. This can be considered as a pointer pointing to one row at a time in the list of rows. Cursors can be used for retrieval, removal or addition of records in a table.
    43) Why to use Sub Query in Sql Server and List out types of Sub Queries?
    Sub Queries are queries within a query. The parent or outer query is being called as main query and the inner query is called as inner query or sub query. Different types of Sub Queries are
    • Correlated - It is not an independent subquery. It is an inner query which is referred by outer query.
    • Non Correlated - It is an independent subquery. It can be executed even without outer query.
    44) What are user defined functions (UDFs) in Sql Server?
    User Defined functions are being used to handle complex queries.
    There are two types of user defined functions –
    • Scalar – This type of functions are used for returning single scalar value.
    • Table Valued – This type of function are used for returning a table which has list of rows. Sql supports datatype called table which is used here for returning a table.
    • 45) List all types of constraints in Sql Server?
      Below are the list of constraints in Sql Server –
      • NOT NULL
      • DEFAULT
      • CHECK
      • PRIMARY KEY
      • FOREIGN KEY
      • UNIQUE
      46) Why to use IDENTITY in Sql Server?
      IDENTITY is used for a column to auto increment the value of the column in a table and it is mainly used with Primary Key.
      47) What are the differences between Union, Interact and Minus operators?
      • Union operator is used to combine all the results or records of the table and it removes the duplicate values.
      • Interact operator is used to return the common list of records between two result sets.
      • Minus operator is used to get the list of records from the first result set and which is not there in second result set.
      48) List out difference between Union and UnionAll in Sql Server?
      Union is used to combine all result sets and it removes the duplicate records from the final result set obtained unlike UnionAll which returns all the rows irrespective of whether rows are being duplicated or not.
      Union checks the number of columns given in the SELECT statement should be equal or not and the datatypes are also should be same and same applied to UnionAll.
      49) Explain “ROW_NUMBER()” in Sql Server with an example.
      “ROW_NUMBER()” is used to return a sequential number of each row within a given partition. “1” will be the first position. “Partition By” and “Order By” can be used along with “ROW_NUMBER()”. Below is the example for the same
      SELECT ROW_NUMBER() OVER(ORDER BY EmpSalary DESC) AS Row FROM Employees WHERE EmpNameName IS NOT NULL
      50) What are the differences between “ROW_NUMBER()”, “RANK()” and “DENSE_RANK()”?
      • “ROW_NUMBER” - Used to return a sequential number of each row within a given partition.
      • “RANK” - Used to returns a new row number for each distinct row in the result set and it will leave a number gap in case of duplicates.
      • “DENSE_RANK” - Used to returns a new row number for each distinct row in the result set and it will not leave any number gap in case of duplicates.
      51) Explain about Link Server in Sql Server?
      Linked Server is used to enable execution of OLEDB data sources in remote servers. With Linked servers we can create easy SQL statements which will allow remote data to be joined, combined and retrieved with data in local.
      52) What are the advantages of user defined functions over stored procedures in Sql Server?
      User Defined functions can be used in SELECT/WHERE/HAVING clauses whereas stored procedure cannot be called. In case of table valued functions, the returned table cam be used for joining with other tables.
      53) What is Snapshot Isolation and how it can turned ON and OFF in Sql Server?
      Updated versions of the rows for a transaction is maintained in “TempDB” and once the transaction begins it will ignore all the rows updated or inserted in a table. Below Sql Statement can be used for turning ON snapshot isolation –
      SET ALLOW_SNAPSHOT_ISOLATION ON
      54) Why to use “OUTPUT” clause in Sql Server?
      OUTPUT clause can be used for determining the rows which are affected due to operations like – INSERT/DELETE/UPDATE. For Insert statements we have a table called “INSERTED”, for delete we have “DELETED” table for tracking the rows which are inserted and deleted respectively.
      55) List out the differences between “REPLACE” and “STUFF” functions in Sql Server?
      “REPLACE” function used for replace the characters in a string with the given character. For example
      SELECT REPLACE(‘my Name’, ‘m’, ‘h’)
      Output : hy nahe
      “STUFF” function is used replace the part of one string to another. For example
      SELECT STUFF('testing', 3, 3, 'Hello');
      Output – teHellong
    56) Why to use “No Lock” in Sql Server?
    “No Lock” is used for unlocking the rows which are locked by some other transaction. Once after the rows are committed or rolled back no need to use No Lock. For example
    SELECT * from Employees WITH(NOLOCK)
    57) What are the significance of master, tempdb and model databases?
    • master - This database will have data and catalog of all the databases of SQL Server instance. 
    • tempdb - tempdb database will have temporary objects like local and global temporary tables and stored procedures as well.
    • model - model database is mainly used for creating new user databases.
    58) Explain about unique identifier datatype in Sql Server?
    Unique Identifier datatype mainly used for primary key columns of the tables or any other columns which need to have unique Ids. “NEWID()” function can be used for generating unique identifier for the column. Unique Identifiers are also named as GUIDs.
    59) Why to use “PIVOT” in Sql Server?
    Pivot table automatically count, sort and total the data in a table or spreadsheet and used to create a separate table for displaying summarized data.
    60) Explain Alternate key, Candidate Key and Composite Key in Sql Server?
    • Alternate Key – To identity a row uniquely we can have multiple keys one of them is called primary key and rest of them are called alternate keys.
    • Candidate Key – Set of fields or columns which are uniquely identified in a row and they constitute candidate keys. 
    • Composite Key – One key formed by combining at least two or more columns or fields.
    61) How to use “BETWEEN” operator in Sql Server?
    “BETWEEN” operator is used for selecting the range of values. For example
    SELECT * FROM Employees WHERE EmpSalary BETWEEN 10000 AND 20000
    62) How to use “DROP” keyword in Sql Server and Give an example.
    “DROP” keyword is used to drop either Index or database or table. Below are list of Sql statements using Drop keyword.
    • Dropping Index
    • DROP INDEX my_index
    • Dropping Database
    • DROP DATABASE my_database
    • Dropping Table
    • DROP TABLE my_table
    63) List out all Null functions in Sql Server?
    Below are the list of Null functions in Sql –
    • ISNULL()
    • NVAL()
    • IFNULL()
    • COALESCE()
    64) What are the differences between ISNULL() and COALESCE() in Sql Server?
    ISNULL() – This function is used to replace the given value in case of NULL value. For example
    SELECT ISNULL(@myvar, ‘replacetext’)
    COALESCE() – This function will return the first non-null expression given in the list of expressions. Advantage of this function over “ISNULL()” is it takes more than two inputs for checking non null expression unlike ISNULL() which takes only two inputs. For example
    SELECT COALESCE(@firvar, @secondvar, @thirdvar);
    65) Give list of Scalar and Aggregate functions of Sql Server?
    Scalar Functions
    • MID() - Extracting characters from a string.
    • LEN() – Get the length of string.
    • ROUND() – Rounding the number.
    • UCASE() - Change it to upper case.
    • LCASE() - Change it to lower case etc.
    Aggregate Functions
    • AVG() – Returns the average value. 
    • MAX() – Returns maximum value.
    • MIN() - Returns minimum value.
    • COUNT() - Returns count of rows.
    • SUM() – Returns sum value etc.
    66) Give list of Date functions of Sql Server?
    Date Functions
    • GETDATE() - Get current data and time.
    • DATEDIFF() – Get time between two dates. 
    • DATEPART() – Get single part of Datetime.
    • DATEADD() – Adding time interval from Date.
    • CONVERT() – Display date in different formats.

0 comments:

Post a Comment

Note: only a member of this blog may post a comment.