Microsoft SQL Server Interview Questions

SQL Server Interview Questions has lots of latest and important SQL interview questions which consist of not only theoretical explanations but most essentially its practical implementations.

This book is an indispensable resource for Microsoft .Net Developers. It will serve as a useful resource to anyone who faces interviews in SQL (developers of any platform - JAVA, C++, etc), as well as Database Administrators.


The list of topics included in the very first edition of the E-Book is as given below:

  • Differentiate between SQL Server 2008 and SQL Server 2012
  • Differentiate between Data Definition Language (DDL) and Data Manipulation Language (DML)
  • What are the six main categories of SQL commands?
  • What are unique identifiers in SQL Server?
  • What is Null Constraint?
  • What is check constraint in SQL SERVER?
  • What is default constraint?
  • What is a database key?
  • Explain the different types of keys in SQL
  • Differentiate between Primary Key and Unique Key
  • Differentiate between Primary Key and Candidate Key
  • Differentiate between Primary Key and Foreign Key
  • What is Identity Column in Table?
  • How can you insert explicit values in an identity column of a table?
  • What is the syntax to check current identity of a table?
  • Can we change identity key values for a table or reset the identity key value?
  • What is the maximum allowable size of a row in SQL SERVER?
  • Explain the Sparse Columns in SQL Server 2008
  • Describe the characteristics of Sparse Columns
  • List down the limitations for using SPARSE Columns
  • How a column is changed from sparse to nonsparse or nonsparse to sparse?
  • What are the data types which cannot be specified as SPARSE?
  • Explain the COLUMNS_UPDATED function and its relation to Sparse Column
  • What are the different types of data types in SQL Server?
  • What is the use of different types of integer data types in SQL?
  • What is times stamp data type is SQL Server?
  • How can you generate duplicate rowversion values?
  • Explain Where Clause?
  • What is select statement in TSQL?
  • Explain the Logical Processing Order of the SELECT statement
  • What is the Join in SQL Server? What are the different types of join?
  • What is the basic difference in joins in SQL Server 2000 and 2005?
  • What is a view in SQL server?
  • What is common table expression (CTE)?
  • When should you use a Common Table Expression?
  • Define the Structure of a CTE with example
  • Explain recursive Common Table Expressions
  • What is a table variable?
  • Differentiate between local and global temporary tables in SQL
  • Explain the Ranking Functions in SQL in detail
  • What is stored procedure?
  • Is nesting possible in stored procedure? If yes how many number of times?
  • What is the maximum amount of parameters a Stored Procedure can have?
  • How can you improve performance of a Stored Procedure?
  • Differentiate between a Stored Procedure and a User-defined Function
  • Differentiate between @@IDENTITY, SCOPE_IDENTITY, IDENT_CURRENT in SQL
  • Explain the Inclusive nature of BETWEEN operator in SQL
  • Differentiate between DELETE and TRUNCATE commands in SQL
  • Explain Indexes in SQL Server
  • What are the data types on which indexes cannot be created?
  • Explain the Index Structures in SQL Server
  • Explain the Index Types in SQL Server
  • Explain the circumstances when Indexes should be avoided / used
  • What is Normalization?
  • Explain the various Normal forms
  • What is a SQL Transaction?
  • What are the SQL Server transaction modes?
  • Explain the properties of transactions.
  • What are the four Transactional Control Commands (TCC)?
  • What is a Shadow table?
  • How do you create a Shadow table in SQL?
  • What are the drawbacks of a SQL Cursor?
  • Write a SQL query to list all the tables in a database.
  • Write a SQL query to list all the stored procedures in a database.
  • Write a SQL query to list all the user defined functions in a database.
  • Write a SQL query to list all the views in a database.
  • Write a SQL query to list all the constraints in a database.
  • Write a SQL query to list all the indexes created for a particular table (say common_user).
  • Explain PIVOT and UNPIVOT operators in SQL
  • What is an Inline View?
  • Explain the SQL CASE expression in detail.
  • How do you tune SQL Indexes for better performance?
  • How do you tune SQL queries for better performance?
  • Explain the use of FOR XML in SQL Queries
  • What is the use of TYPE directive in FOR XML queries?
  • What is the use of ROOT directive in FOR XML queries?
  • Frequently asked SQL Queries in interviews
SQL Server Interview Questions E-Book is now available for sale.

INSTANT DOWNLOAD AFTER FULL PAYMENT

Details of the EBook
Name: SQL Server Interview Questions
Author: Chandan Sinha
Format: PDF
Language: English

Price: $2.99 (Exclusively on this website)

 (Limited Period Offer - Rates slashed from $3.99 to $2.99)
Focus Area: Technical Interview in Microsoft SQL Server

SQL Server Interview Questions

Ebook Link on Amazon Kindle - http://www.amazon.com/gp/product/B00ONQGUF6

No comments:

PIVOT - UNPIVOT operator

Explain PIVOT and UNPIVOT operators in SQL

PIVOT and UNPIVOT are relational operators which were introduced in SQL Server 2005. These operators allow easy transformation of table-valued expression into another table.

PIVOT allows you to rotate rows into columns during the execution of an SQL Query. It performs an aggregation, and merges possible multiple rows into a single row in the output.

UNPIVOT does almost the opposite of PIVOT operator by rotating columns into rows, but it is not exactly opposite. PIVOT merges multiple rows into single while UNPIVOT fails to reproduce the original table due to the merged rows. Null input values to UNPIVOT disappear in its output.

A simple example of PIVOT operator

Suppose we have a dbo.currency_rate table which consists of three columns - in_currency_id, mn_rate and dt_currency.

-- Create table
CREATE TABLE dbo.currency_rate(in_currency_id int, mn_rate money, dt_currency datetime)

-- Insert data in table
INSERT INTO dbo.currency_rate VALUES (1, 60.15, '2015-06-15 09:59:16.640')
INSERT INTO dbo.currency_rate VALUES (2, 35.57, '2015-06-15 09:59:16.640')
INSERT INTO dbo.currency_rate VALUES (3, 78.90, '2015-06-15 09:59:16.640')
INSERT INTO dbo.currency_rate VALUES (4, 87.15, '2015-06-15 09:59:16.640')
INSERT INTO dbo.currency_rate VALUES (1, 10.18, '2015-06-16 09:59:16.640')
INSERT INTO dbo.currency_rate VALUES (2, 11.25, '2015-06-16 09:59:16.640')
INSERT INTO dbo.currency_rate VALUES (3, 08.48, '2015-06-16 09:59:16.640')
INSERT INTO dbo.currency_rate VALUES (4, 18.31, '2015-06-16 09:59:16.640')

-- Select and confirm the data before any operation
SELECT * FROM dbo.currency_rate

in_currency_id mn_rate dt_currency
1 60.15 2015-06-15 09:59:16.640
2 35.57 2015-06-15 09:59:16.640
3 78.90 2015-06-15 09:59:16.640
4 87.15 2015-06-15 09:59:16.640
1 10.18 2015-06-16 09:59:16.640
2 11.25 2015-06-16 09:59:16.640
3 8.48 2015-06-16 09:59:16.640
4 18.31 2015-06-16 09:59:16.640


Use PIVOT operator to transform rows into columns:

-- Pivot table with two rows and five columns
SELECT *
FROM
[dbo].[currency_rate]
PIVOT
(
AVG(mn_rate)
FOR [in_currency_id] IN ([1], [2], [3], [4])
) AS pivot_table;


Output:

dt_currency 1 2 3 4
2015-06-15 09:59:16.640 60.15 35.57 78.90 87.15
2015-06-16 09:59:16.640 10.18 11.25 8.48 18.31


A simple example of UNPIVOT operator

In continuation with the PIVOT operator example, save the result of the PIVOT operator in a temporary table:

SELECT *
INTO #tmp_pivot_table
FROM
[dbo].[currency_rate]
PIVOT
(
AVG(mn_rate)
FOR [in_currency_id] IN ([1], [2], [3], [4])
) AS pivot_table;


Use UNPIVOT operator to transform rows into columns:

SELECT [dt_currency], [in_currency_id], [mn_rate]
FROM #tmp_pivot_table
UNPIVOT
(
mn_rate
FOR in_currency_id IN ([1], [2], [3], [4])
) AS unpivot_table

Output:

dt_currency in_currency_id mn_rate
2015-06-15 09:59:16.640 1 60.15
2015-06-15 09:59:16.640 2 35.57
2015-06-15 09:59:16.640 3 78.90
2015-06-15 09:59:16.640 4 87.15
2015-06-16 09:59:16.640 1 10.18
2015-06-16 09:59:16.640 2 11.25
2015-06-16 09:59:16.640 3 8.48
2015-06-16 09:59:16.640 4 18.31
No comments:
Subscribe to: Posts (Atom)