TOP 100 SQL SERVER INTERVIEW QUESTIONS

SQL SERVER INTERVIEW QUESTIONS

1.      What is the Complex task that you handled in your project

2.      What are the differences between Delete and Truncate?

3.      Diff b/w Char and Varchar

4.      Diff b/w Unicode and Non-unicode characters

5.      Different ways to identify duplicate records in table

6.      How will you identify table granularity if we table doesn’t have any primary key/unique key

7.      Diff b/w Stored procedure and Function

8.      How parameter sniffing will happen? Give me one example

9.      Can we embed Dynamic SQL in Functions

10. Diff b/w CROSS Apply and Outer Apply and different from INNER JOIN and OUTER JOINS

11. Brief about Windows Functions which were introduced as part of SQL Server 2012

12. Diff b/w Coalesce() and ISNULL() and which will be best?

13. How many clustered and non clustered indexes can be created in one table?

14. What are the main differences between UNION ,  UNION ALL and EXCEPT

15. How will identify the root causes of slow running procedures/functions

16. How will you identify slow running queries in SQL Server

17. How will identify the matched and non matched records between two tables 

18. What are ACID properties

19. What are the isolation levels and explain each one with example

20. What is the diff b/w LOCK and NOLOCK

21. What is Table variable, Temp  table and CTE and in which scenarios what kind of table will you use

22. Explain the scenario where you have written normal subquery and Correlated Subquery

23. Diff b/w Where and Having Clause

24. Can we execute Stored Procedure inside Function?

25.  Can we you use Case Statement in Order by Clause?

26. Explain about INNER, LEFT, RIGHT, FULL OUTER joins

27. Explain the scenario where you have used Self join

28. What are deleted and inserted tables

29. Explain the scenario where MERGE statement will be use ful

30. How will you reseed the identity column

31. How will you check if column exists for a table or view in database

32. What is trigger and different types of triggers

33. What is view and indexed view/materialised view

34. What is the Query logical execution order

35. Difference between clustered and non clustered index

36. Difference between Stuff and Replace function

37. What is linked Server

38. Difference b/w Varchar and Varchar(max)

39. What are the system databases (master, msdb, model and tempdb) roles and functionalities

40. Difference b/w index seek and index scan

41. Difference b/w RaiseError and throw and which one will be the best to use

42. Pivot and Unpivot

43. Diff b/w IN and EXISTS clause

44. What is cursor and how many different types are there

45. What is exception handling

46. What are dirty reads, phantom reads

47. What is dead lock

48. Diff b/w Local and Global Temporary tables

49. What is synonym

50. What is Sequence? Diff b/w Sequence and Identity

Read More

SQL SERVER : ALTER, DELETE,DROP

SQL SERVER-ALTER, DELETE, DROP

We will use below table for demo purpose

CREATE TABLE Emp_Test

 (EmpID int,

 Empname varchar(10))

ALTER: ALTER command is used to do below things

•    Add column/constrains
•    Modify column
•    Drop column/Constraint

Add Column syntax:

ALTER TABLE <<Table>>

ADD Column_name datatype [size]

Example:

ALTER TABLE Emp_test

ADD Dateofbirth date

We can also add constraints to the existing table. We will discuss about constrains in future sessions. For now we will learn how to add constraints 

Add Constraint Syntax:

ALTER TABLE <<tablename>>

ADD Constraint  Constraint_name ConstraintType (Columns…)

Example:

ALTER TABLE Emp_test

ADD constraint EMP_Unique UNIQUE(EMPID) – Here Unique means EMPID column data should not be repeated

Modify column:

We can also modify the existing column data type and its size if table is empty and it table has data and then we are trying to modify the existing column data type then if the new data type is compatible to the existing data in the table then it will allow other it will through error. E.g. existing column was created using char(10) then if we are trying to modify to Varchar(20)  then it won’t throw any error. Compatibility data types are always we can change. 

Syntax:

ALTER TABLE <<Table_name>> 

ALTER Column Col_name datatype

Example:

ALTER TABLE Emp_test

ALTER Column Empname char(12)

Drop column/Constraint: We can  drop existing column/constraint using ALTER command 

Syntax: 

ALTER TABLE <<table_name>>

DROP <<Column or Constraint>> <<Column_name>>

Example:

Drop Empname column

ALTER TABLE Emp_test

DROP Column Empname  

Drop unique constraint (EMP_Unique)

ALTER TABLE Emp_test

DROP CONSTRAINT EMP_Unique

DELETE:  DELETE Command is used to delete table rows. We can delete all the rows from a table or we can also delete specific rows by using WHERE condition. 

Syntax:

DELETE FROM <<table name>> 

<<WHERE some conditions>>

Example:

DELETE FROM Emp_test--Delete all the rows

DELETE FROM Emp_test WHERE EmpID=10--Delete rows which are having empid=10

DELETE FROM Emp_test WHERE EmpID<10 --Delete rows which are having empid<10

DELETE FROM Emp_test WHERE EmpID<>10 ----Delete rows which are having empid<>10 

In SQL server we can also write DELETE statement like below

DELETE emp_test

TRUNCATE: TRUNCATE Command is also used to delete the data from table

Syntax:

TRUNCATE TABLE <<Tablename>>

Example:

TRUNCATE TABLE emp_test

Here we should get one question like 

What is the difference between DELETE and TRUNCATE?

 Both are deleting rows. Yes both are deleting table data however there are some differences are there while deleting the data.

      1.     For DELETE We can use WHERE condition whereas TRUNCATE We can’t give 

      2.     DELETE Command will maintain log for each deleted row whereas TRUNCATE will not maintain any log

      3.     We can use some joins to delete the data where as in TRUNCATE we can’t use any joins

      4.     DELETE Will not reset IDENTITY [We will discuss in future sessions about IDENTITY] Whereas TRUNCATE Will      reset IDENTITY

      5.     We can Roll back the data when we use DELETE whereas TRUNCATE we can’t rollback

Overall, if we want to delete all the row then it’s always go with TRUNCATE Command

DROP: DROP Command is used to drop the table

Syntax:

DROP TABLE <<tablename>>

Example:

DROP TABLE emp_test

Read More

SQL Server- SQL Server Data Types

SQL SERVER Data types

In SQL server, we have many data types and in this moment we no need to concentrate on all the data types. We will learn frequently used data types.

What is data type?

Data types is a category which will define the characteristics of the data

For E.g. INT 

It will accept only Numeric values. String, decimal values are not allowed to store here.

I will try to relate some of the data types with some properties of person

Name	ABC	Here we will take char, varchar, nvarchar data types to store string values
DOB	01-01-1900	Date
Height	5.3	numeric, float, secimal
Weight	62	int, smallint, tinyint, bigInt
Birth date and time	01-01-1900,06:30:02	Datetime, datetime2,smalldatetime, datetimeoffset
Birth time	06:30:02	Time

Below is the data type and its details

Data Type	Max Size	Comments	Points to remember
char(size)	1-8000	Fixed-length, non-Unicode string data. The storage size is (size) bytes.	->When size is not specified in a data definition or variable declaration statement, the default length is 1. When size is not specified when using the CAST and CONVERT functions, the default length is 30. ->If we want to store Chinese, Japanese and other kind of special character then we need to use Unicode. If we use plain English then char and varchar would be enough
nchar(size)	1-4000	Fixed-length, Unicode string data. The storage size is 2x (size).
varchar(size) or varchar(max)	1-8000	Variable-length, non-Unicode string data.
nvarchar(size) or nvarchar(max)	1-4000	Variable-length, Unicode string data.
bit	1,0, NULL	It will take 1 bit	These are all fixed numeric
TINYINT	0 to 255	1 byte
SMALLINT	-32,768 to 32,767	2 byte
INT	-2,147,483,648 to 2,147,483,647	4 byte
BIGINT	-9,223,372,036,854,775,808 to 9,223,372,036,854,775,807	8 bytes
DECIMAL(m,d)	1-18 (m for total digits and  d for total precisions	Numeric data types that have fixed precision and scale.
NUMERIC(m,d)	1-18 (m for total digits and  d for total precisions
FLOAT(n)	N defaults to 53
DATE	0001-01-01' to '9999-12-31'.	'YYYY-MM-DD'	All these are related to date and time data types.
DATETIME	Date: 1753-01-01 00:00:00' to '9999-12-31 23:59:59' Time: '00:00:00' to '23:59:59:997'	'YYYY-MM-DD hh:mm:ss[.mmm]'
DATETIME2	Date: '0001-01-01' to '9999-12-31' Time: '00:00:00' to '23:59:59:9999999'	'YYYY-MM-DD hh:mm:ss[.fractional seconds]'
SMALLDATETIME	Date: '1900-01-01' to '2079-06-06' Time: '00:00:00' to '23:59:59'	'YYYY-MM-DD hh:mm:ss'
TIME	Time: '00:00:00.0000000' to '23:59:59.9999999'	'YYYY-MM-DD hh:mm:ss[.nnnnnnn]'
DATETIMEOFFSET	Along with Dateime2 it will display time zone Time zone offset range from -14:00 to +14:00.	'YYYY-MM-DD hh:mm:ss[.nnnnnnn]' [{+|-}hh:mm]

Read More