SQL SERVER INTERVIEW QUESTIONS PART-2

1) Can SQL servers linked to other servers?

SQL server can be connected to any database which has OLE-DB provider to give a link. Example: Oracle has OLE-DB provider which has link to connect with the SQL server group.

2) What are tables and Fields?

A table is a set of data that are organized in a model with Columns and Rows. Columns can be categorized as vertical, and Rows are horizontal. A table has specified number of column called fields but can have any number of rows which is called record.

Example:.

Table: Employee.

Field: Emp ID, Emp Name, Date of Birth.

Data: 201456, David, 11/15/1960.

3) What is the difference between varchar and nvarchar types?

Varchar and nvarchar are same but the only difference is that nvarhcar can be used to store Unicode characters for multiple languages and it also takes more space when compared with varchar.

4) How data can be copied from one table to another table?

INSERT INTO SELECT

This command is used to insert data into a table which is already created.

SELECT INTO

This command is used to create a new table and its structure and data can be copied from existing table.

5) What is a constraint?

Constraint can be used to specify the limit on the data type of table. Constraint can be specified while creating or altering the table statement. 

Constraints are defined while creating the database itself with CREATE TABLE statement or even after the table is created once with ALTER TABLE statement.

There are 5 major constraints are used in SQL, such as

NOT NULL: That indicates that the column must have some value and cannot be left null

UNIQUE: This constraint is used to ensure that each row and column has unique value and no value is being repeated in any other row or column

PRIMARY KEY: This constraint is used in association with NOT NULL and UNIQUE constraints such as on one or the combination of more than one columns to identify the particular record with a unique identity.

FOREIGN KEY: It is used to ensure the referential integrity of data in the table and also matches the value in one table with another using Primary Key

CHECK: It is used to ensure whether the value in columns fulfills the specified condition.

6) What is the difference between Cluster and Non-Cluster Index?

Clustered index is used for easy retrieval of data from the database by altering the way that the records are stored. Database sorts out rows by the column which is set to be clustered index.

A nonclustered index does not alter the way it was stored but creates a complete separate object within the table. It point back to the original table rows after searching.

7) What are the types of Triggers?

There are four types of triggers and they are:

1. Insert

2. Delete

3. Update

4. Instead of

8) What will be query used to get the list of triggers in a database?

Query to get the list of triggers in database.

9) What is Field in a table?

A field is an entity used for storing a particular type of data with in a table like numbers, characters, dates etc.

10) What is a tuple, record or row in a table?

A tuple or record is an ordered set of related data item in a table.

11) It is possible to call a stored procedure with in a stored procedure?

Yes, we call a stored procedure within a stored procedure. It is called recursion property of SQL server and these types of stored procedures are called nested stored procedures.

12) What is a stored procedure?

Stored Procedure is a function consists of many SQL statements to access the database system. Several SQL statements are consolidated into a stored procedure and execute them whenever and wherever required.

13) Advantages and Disadvantages of Stored Procedure?

Stored procedure can be used as a modular programming – means create once, store and call for several times whenever required. This supports faster execution instead of executing multiple queries. This reduces network traffic and provides better security to the data.

Disadvantage is that it can be executed only in the Database and utilizes more memory in the database server.

14) What are the advantages of using Stored Procedures?

1. Stored procedure can reduced network traffic and latency, boosting application performance.

2. Stored procedure execution plans can be reused, staying cached in SQL Server's memory, reducing server overhead.

3. Stored procedures help promote code reuse.

4. Stored procedures can encapsulate logic. You can change stored procedure code without affecting clients.

5. Stored procedures provide better security to your data.

15) What is recursive stored procedure?

A stored procedure which calls by itself until it reaches some boundary condition. This recursive function or procedure helps programmers to use the same set of code any number of times.

SQL Server supports recursive stored procedure which calls by itself. Recursive stored procedure can be defined as a method of problem solving wherein the solution is arrived repetitively. It can nest up to 32 levels.

16) What are the differences between Stored Procedure and the dynamic SQL?

Stored Procedure is a set of statements which is stored in a compiled form. Dynamic SQL is a set of statements that dynamically constructed at runtime and it will not be stored in a Database and it simply execute during run time.

17) Which SQL server table is used to hold the stored procedure scripts?

Sys.SQL_Modules is a SQL Server table used to store the script of stored procedure. Name of the stored procedure is saved in the table called Sys.Procedure.

18) What is the command used to Recompile the stored procedure at run time?

Stored Procedure can be executed with the help of keyword called RECOMPILE.

Or we can include WITH RECOMPILE in the stored procedure itself.

19) What is a foreign key?

A foreign key is used for enforcing referential integrity in which a field marked as foreign key in one table is linked with primary key of another table. With this referential integrity we can have only the data in foreign key which matches the data in the primary key of the other table.

(or)

Foreign key constraint

A FOREIGN KEY constraint prevents any actions that would destroy links between tables with the corresponding data values. A foreign key in one table points to a primary key in another table. Foreign keys prevent actions that would leave rows with foreign key values when there are no primary keys with that value. The foreign key constraints are used to enforce referential integrity.

20) What is a unique key constraint?

A Unique key constraint uniquely identified each record in the database. This provides uniqueness for the column or set of columns. So no duplicate values are entered. The unique key constraints are used to enforce entity integrity as the primary key constraints.

A Primary key constraint has automatic unique constraint defined on it. But not, in the case of Unique Key.

There can be many unique constraint defined per table, but only one Primary key constraint defined per table.

21) What is a Composite key?

A composite key is a primary key with multiple columns as in case of some tables a single field might not guarantee unique and not null values, so a combination of multiple fields is taken as primary key.

22) What is a NULL Value?

A NULL value in SQL is an unknown or blank value. Since NULL is unknown value so, NULL value cannot be compared with another NULL values. Hence we cannot use '=' operator in where condition with NULL. For this, we have IS NULL clause that checks if the value in field is NULL or not.

23) What is a Not NULL Constraint?

A NOT NULL constraint enforces that the column will not accept null values. The not null constraints are used to enforce domain integrity, as the check constraints.

A Not NULL constraint is used for ensuring that the value in the field cannot be NULL.

24) What is a Unique Constraint?

A unique constraint is used to ensure that the field/column will have only unique value (no duplication).

25) What is Check Constraint?

A CHECK constraint can be applied to a column in a table to limit the values that can be placed in a column. Check constraint is to enforce integrity.

 A CHECK constraint is used to limit the values entered in a field or type of data that can be stored in a column. They are used to enforce domain integrity.

E.g. we can ensure those fields ‘Salary’ can only have value greater than 1000 using check constraint-

CREATE TABLE EMP_SALARY (EmpID int NOT NULL, NAME VARCHAR (30) NOT NULL, Salary INT CHECK (AGE > 1000), PRIMARY KEY (EmpID));

26) What is the difference between a Primary key and a unique key?

The differences between primary key and unique key are:

1) A Primary key is column whose values uniquely identify every row in a table. Primary key values can never be reused. They create a clustered index on the column and cannot be null.

2) A Unique key is column whose values also uniquely identify every row in a table but they create a non-clustered index by default and it allows one NULL only. 

27) What is a Default Constraint?

A Default constraint is used for providing a default value to a column when no value is supplied at the time of insertion of record in the database.

28) What is the difference between Delete, Truncate and Drop Command?

The difference between the Delete, Truncate and Drop command is -

• Delete command is a DML command, it removes rows from table based on the condition specified in the where clause, being a DML statement we can rollback changes made by delete command.
• Truncate is a DDL command, it removes all the rows from table and also frees the space held unlike delete command. It takes lock on the table while delete command takes lock on rows of table.
• Drop is a DDL command; it removes the complete data along with the table structure (unlike truncate command that removes only the rows). DROP command removes a table from the database completely.

 29) What is the difference between Having Clause and Where Clause?

WHERE and HAVING both filters out records based on one or more conditions. The difference is WHERE clause can only be applied on a static non-aggregated column whereas we will need to use HAVING for aggregated columns.

To understand this, consider this example.

Suppose we want to see only those departments where department ID is greater than 3. There is no aggregation operation and the condition needs to be applied on a static field. We will use WHERE clause here:

SELECT * FROM DEPT WHERE ID > 3

ID	NAME
4	Sales
5	Logistics

Next, suppose we want to see only those Departments where Average salary is greater than 80. Here the condition is associated with non-static aggregated information which is “average of salary”. We will need to use HAVING clause here:

SELECT dept.name DEPARTMENT, avg(emp.sal) AVG_SAL

FROM DEPT dept, EMPLOYEE emp

WHERE dept.id = emp.dept_id (+)

GROUP BY dept.name

HAVING AVG(emp.sal) > 80

DEPARTMENT	AVG_SAL
Engineering	90

As you see above, there is only one department (Engineering) where average salary of employees is greater than 80.

(or)

A 'where' clause is used to fetch data from database that specifies a particular criteria (specified after the where clause). Where as a 'having' clause is used along with 'groupby' to fetch data that meets a particular criteria specified by the aggregate function. 

Both specify a search condition but Having clause is used only with the SELECT statement

and typically used with GROUP BY clause.

If GROUP BY clause is not used then Having behaves like WHERE clause only.For example-

Emp_Project

Employee	Project
A	P1
B	P2
C	P3
B	P3

In the above table if we want to fetch Employee working in project P2, we will use 'where' clause-

1	Select Employee from Emp_Project where Project=P2;

Now if we want to fetch Employee who is working on more than one project, for this we will first have to group the Employee column along with count of project and then the 'having' clause can be used to fetch relevant records-

1	Select Employee from Emp_Project groupby Employee having count (Project)>1;

30) Can we use ‘where’ clause with ‘groupby’?

Yes, we can use 'where' clause with 'groupBy'. The rows that doesn't meet the where conditions are removed first and then the grouping is done based on the groupby column.

1	SELECT Employee, Count(Project) FROM Emp_Project WHERE Employee !=’A’ GROUP BY Project;

31) Define the select into statement?

Select into statement is used to directly select data from one table and insert into other, the new table gets created with same name and type as of the old table-

1	SELECT * INTO new table FROM old Table;

32) What are 3 ways to get count of the number of records in a table?

Select * from table_Name

Select Count(*) from table_Name

Select rows from sysindexes where id=OBJECT_ ID (table_Name) AND indid<2.

33) Can we rename a column in the output of SQL Query?

Yes by using the following syntax we can do this.

SELECT column_name AS new_name FROM table_name;

34) Which TCP/IP Port does SQL Server run on?

By default SQL Server runs on port 1433.

35) List the different index configurations possible for a table?

A table can have one of the following index configurations:

1. No indexes

2. A clustered index

3. A clustered index and many non-clustered indexes

4. A non-clustered index

5. Many non-clustered indexes 

36) What is recovery model? List the types of recovery model available in SQL Server?
Recovery model basically tells SQL Server what data should be kept in the transaction log file and for how long. A database can have only one recovery model.

It also tells SQL server that which backup is possible in a particular recovery model selected. There are three types of recovery model:

1. Full

2. Simple

3. Bulk-Logged 

37) What are different backups available in SQL Server?

Different possible backups are:

1. Full backup

2. Differential Backup

3. Transactional Log Backup

4. Copy Only Backup

5. File and File group backup 

38) What is FULL back up?

Full back up is the most common type of backup in SQL Server. This is the complete backup of the database. It also contains part of transaction log so it can be recovered.

39) What are the properties of the Relational tables?

Relational tables have six properties:

1. Values are atomic.

2. Column values are of the same kind.

3. Each row is unique.

4. The sequence of columns is insignificant.

5. The sequence of rows is insignificant.

6. Each column must have a unique name. 

40) When is UPDATE_STATISTICS Command used?

This command is basically used when a large processing of data has occurred. If a large amount of deletions any modification or Bulk Copy into the tables has occurred, it has to update the indexes to take these changes into account. UPDATE_STATISTICS updates the indexes on these tables accordingly.

UPDATE_STATISTICS command is used to update the indexes on the tables when there is a large amount of deletions or modifications or bulk copy occurred in indexes.

As the name implies UPDATE_STATISTICS command updated the statistics used by the index to make the search easier.

41) What is a Linked Server?

Linked Servers is a concept by which we can connect another SQL server to a Group and query both the SQL Servers database using T-SQL Statements.

 sp_add linkedsrv login issued to add link server.

42) What is Collation?

Collation is defined as set of rules that determine how character data can be sorted and compared.

Character data is sorted using rules that define the correct character sequence, with options for specifying case sensitivity, accent marks, kana character types and character width.

ASCII value can be used to compare these character data.

43) What are all different types of collation sensitivity?

Following are different types of collation sensitivity -.

· Case Sensitivity – A and a and B and b.

· Accent Sensitivity.

· Kana Sensitivity – Japanese Kana characters.

· Width Sensitivity – Single byte character and double byte character.

44) What is SQL Profiler?

SQL Profiler provides graphical representation of events in an instance of SQL Server for the monitoring and investment purpose. We can capture and save the data for further analysis.
We can put filters as well to captures the specific data we want.

SQL Profiler is a tool which allows system administrator to monitor events in the SQL server. This is mainly used to capture and save data about each event of a file or a table for analysis.

45) What is SQL Server Agent?

The SQL Server agent plays a vital role in day to day tasks of SQL server administrator (DBA). Server agent’s purpose is to implement the tasks easily with the scheduler engine which allows our jobs to run at scheduled date and time.

SQL Server agent allows us to schedule the jobs and scripts. It helps is implementing the day to day DBA tasks by automatically executing them on a scheduled basis.

46) What are Scheduled tasks Jobs in SQL Server?

Scheduled job allows a user to run the scripts or SQL commands automatically on the scheduled basis. User can determine the order in which commands need to execute and the best time to run the job to avoid the load on the system.

Scheduled tasks or jobs are used to automate processes that can be run on a scheduled time at a regular interval. This scheduling of tasks helps to reduce human intervention during night time and feed can be done at a particular time. User can also order the tasks in which it has to be generated.

47) What is a Heap?

A heap is a table that does not contain any clustered index or non-clustered index.

48) What is BCP?

BCP or Bulk Copy is a tool by which we can copy large amount of data to tables and views. BCP does not copy the structures same as source to destination. BULK INSERT command helps to import a data file into a database table or view in a user-specified format.

49) What is COALESCE in SQL Server?

COALESCE is used to return first non-null expression within the arguments. This function is used to return a non-null from more than one column in the arguments.

50) What is a Coalesce function?

Coalesce function is used to return the first not NULL value out of the multiple values or expressions passed to the coalesce function as parameters.

Example-

Coalesce (Null, Null, 5, ‘ArtOfTesting’) will return the value 5.

Coalesce (Null, Null, Null) will return Null value as no not Null value is encountered the parameters list.

51) Do View contain Data?

No, Views are virtual structure.

52) Can a View based on another View?

Yes, A View is based on another View.

53) What is CTE?

A CTE or common table expression is an expression which contains temporary result set which is defined in a SQL statement.

54) What are Orphan records?

Orphan records are the records having foreign key to a parent record which doesn't exist or got deleted.

55) How can we remove Orphan records from a table?

In order to remove orphan records from database we need to create a join on the parent and child tables and then remove the rows from child table where id IS NULL.

1	DELETE PT FROM Parent Table PT LEFT JOIN Child Table CT ON PT.ID = CT.ID WHERE PT.ID is Null.

*Remember: Delete with joins requires name/alias before from clause in order to specify the table of which data is to be deleted.

56) What is Log Shipping?

Log shipping is nothing but the automation of backup and restore of a database from one server to other standalone standby server. This is one of the disaster recovery solutions. If one server fails by some reason we will have the same data available on the standby server.

57) What are the advantages of the Log Shipping?

Advantages of Log shipping:

1. Easy to set up.

2. Secondary database can be used as a read only purpose.

3. Multiple secondary stand by servers are possible

4. Low maintenance.

58) Can we take the full database backup in Log Shipping?

Yes, we can take the full database backup. It won’t affect the log shipping.

59) What is an Execution Plan?

An execution plan is graphical or textual way of showing how SQL server breaks down a query to get the required result. It helps user to determine why queries are taking more time to execute and based on the investigation user can update their queries for the maximum result.
In Query Analyzer is an option called “Show Execution Plan” (located on the Query drop-down menu). If this option is turned on it will display query execution plan in separate window when query is ran again.

60) What is Identity in SQL?

An identity column in the SQL automatically generates numeric values. We can defined a start and increment value of identity column. Identity columns do not need to be indexed.

61) What are the common performance issues in SQL Server?

Following are the common performance issues:

1. Deadlocks

2. Blockings

3. Missing and unused indexes.

4. I/O bottlenecks

5. Poor Query plans

6. Fragmentation 

62) List the various tools available for Performance tuning?

There are various tools available for performance tuning:

1. Dynamic Management Views

2. SQL Server Profiler

3. Server Side traces

4. Windows Performance monitor.

5. Query Plans

6. Tuning advisor

63) What is Performance Monitor?

Window’s performance monitor is a tool to capture metrics for the entire server. We can use this tool for capturing events of SQL server also. Some useful counters are- Disks, Memory, Processors and Network etc.