SQL Server T-SQL Fundamental Interview Questions

In this post we will focus on SQL Server T-SQL Fundamental Interview Questions which can form the base for getting started with a SQL Server Developer Interview.

Question 1: What are Primary Keys and Foreign Keys?

A primary Key is used to uniquely identify all table records. This is used to define relationships with other tables in a Relational Database. It is a special table column which can be a single column or a combination of columns defined by the database designer.

Primary key’s helps to implement the normalization rules which are:

• It must contain a unique value for each row of data.
• It cannot be null.

Database designers can use an existing table column or a column that is specifically generated (Identity) by the database based on a defined sequence.

Question 2: What is an Index? Give Examples.

An index is a Binary Tree structure which contains physical pointers to the data rows defined in a table. You create Indexes on an existing table to locate rows more quickly and efficiently while consuming less resources. Depending on the Index Type, You can create an index on one or more columns of a table based on requirements. The primary objective of indexes are to speed up queries. It is one of the most effective ways to improve performance in a database application. If there are no index’s defined, a table scan will happen where SQL Server will load the entire table to fetch query results which can create major performance issues. Table scans are sometimes unavoidable and OK for small tables, but on large tables with millions of rows, scans have a huge impact on performance (Memory Pressure, IOPS, CPU % Usage etc.)

Types of Index’s:

Clustered & Nonclustered	Clustered and Nonclustered Indexes
Unique	Create Unique Indexes
Columnstore	Columnstore Indexes
Index with included columns	Create Indexes with Included Columns

There are other specialized type of Index’s like Spatial, XML, Filtered, Fulltext etc.

Question 3: What is a Stored Procedure?

A stored procedure (SP) is a compiled set of T-SQL statements.
SP’s are used to create business logic that can be encapsulated using stored procedure. Stored Procedures also improves performance, especially in environments where similar queries would be run multiple times a day.

Advantages of using Stored Procedures:

• You can Manage, Control and Validate data
• It can also be used for access mechanisms
• They do not need to be recompiled every time. This helps to reduce Large queries and use SQL Server efficiently.
• Since Stored Procedures increases reusability of code it may be a complex piece of code, but not to be written over and over again. You can directly call them from your application.
• You can set specific permissions on stored procedures. This helps to increases security and access.
• Stored procedures can be used to help ensure the integrity of the database.

Question 4: What are User-defined Functions?

Question 5: What is a View?

A View is a virtual table which can be a subset of a table. You can use a View for retrieving data as well as updating or deleting rows. When Rows are updated or deleted in the view, there is a cascading effect on the base table where they are also updated or deleted in the table the view was created with. If data in the base table changes, so does the data in the view as views since they are a subset of the original table. The results of a view are not persistent in the database. You can use a standard T-SQL select command to pull data from one to many different base tables or even other views. Views also help to improve security, where Users can have access to a view and not directly on the base tables.

Question 6: What are Linked Servers?

Linked Servers are a group of remote SQL Servers which you can add to a Group and query both the SQL Server databases using standard T-SQL Statements. With a linked server, you can use SQL statements to query remote data and join and combined with local data.

You can use the Management Studio GUI or System Stored Procedures sp_addlinkedserver, sp_addlinkedsrvlogin to a add new Linked Server.

Question 7: What are the Different Types of Joins?

Cross Join

In a cross join that does not have a WHERE clause produces the Cartesian product of the tables involved in the join. The size of a Cartesian product result set is the number of rows in the first table multiplied by the number of rows in the second table. For example when you want to combine sales data with each product with a pricing table to analyze each product category at each price.

Inner Join

A join that displays only the rows that have a match in both joined tables is known as inner Join. An inner join is a join in which the values in the columns being joined are compared using a comparison operator. This is also the default type of join in the Query and View Designer.

Outer Join

Outer joins, return all rows from at least one of the tables or views mentioned in the FROM clause, as long as those rows meet any WHERE or HAVING search conditions. Even if they do not have related rows in the joined table is an Outer Join. Users can create three different outer join to specify the unmatched rows which needs to be included:

• Left Outer Join: All the rows in the first-named table, i.e. “left” table, which appears leftmost in the JOIN clause, are included. Unmatched rows in the right table do not appear.  

• Right Outer Join: All the rows in the second-named table, i.e. “right” table, which appears rightmost in the JOIN clause are included. Unmatched rows in the left table are not included.

• Full Outer Join: Complete set of all the rows in all joined tables are included, whether they are matched or not.

Self Join

In order to avoid confusion, you use SQL Aliases when one table joins to itself with one or two aliases. You can use self join which can be of any type, as long as the joined tables are the same. A self join is rather unique in that it involves a relationship with only one table. The common example is when company has a hierarchical reporting structure whereby one member of staff reports to another. Self Join can be Outer Join or Inner Join.

Question 8: What is Left Anti Semi Join Operator?

The Left Anti Semi Join operator returns the result set for each row from the first (top) input when there is no matching row in the second (bottom) input. If no join predicate exists in the Argument column, each row is a matching row. Left Anti Semi Join is a logical operator.

Question 9: What is the Difference between a Function and a Stored Procedure?

You cannot use Stored procedures in SQL statements anywhere in the WHERE/HAVING/SELECT section, whereas  with UDF’s you can.  Using UDFs you can return tables that can be treated as another rowset.You can use JOIN’s with other tables. Inline UDF’s can be thought of as views that take parameters and can be used in JOINs and other Rowset operations.

Question 10: What is a Trigger? Give Examples.

A trigger is a System SQL procedure that initiates an action when an event (INSERT, DELETE or UPDATE) occurs. Triggers are stored in and managed by the RDBMS. Daatbase developers use Triggers to maintain the referential integrity of data by changing the data in a systematic way. A trigger cannot be called or executed by an User or Application. RDBMS automatically fires the trigger as a result of a data modification to the associated table where a Trigger is defined. Triggers can be considered to be similar to stored procedures in that both consist of procedural logic that is stored at the database level. Stored procedures, however, are not event-driven and are not attached to a specific table like triggers are. Stored procedures are explicitly executed by invoking a CALL function to the procedure while triggers are implicitly executed. In addition, triggers can also execute stored procedures.

Nested Trigger: A trigger can also contain INSERT, UPDATE and DELETE logic within itself, so a trigger can fire another trigger because of data modification, which can also cause another data modification. A trigger that contains data modification logic within itself is called a nested trigger.

There are two types of Triggers:

1) DML Trigger

There are two types of DML Triggers

• Instead of Trigger
  Instead of Triggers are fired in place of a triggering action such as an insert, update, or delete.
• After Trigger
  After triggers execute following a triggering action, such as an insert, update, or delete.

2) DDL Trigger

This type of trigger is fired against a DDL Statement like Drop Table, Create Table, Alter Table or Login events. DDL Triggers are always executed after DML Triggers.

Question 11: What is a Cursor? Where is it used?

In SQL Server developers use a cursor to define a result set (a set of data rows) and perform complex logic sequentially on a row by row basis instead of the typical SQL commands that operate on all the rows in the set at one time resulting in a Full Table Scan. Cursors are also widely misused T-SQL tool because a newbie developer writing them haven’t yet figured out how to think in sets. Cursors are generally best avoided due to performance implications.

Question 12: What are the different Types of Cursor?

In order to work with a cursor, we need to perform some steps in the following order:

• Declare cursor
• Open cursor
• Fetch row from the cursor
• Process fetched row
• Close cursor
• Deallocate cursor

We hope that this post on SQL Server T-SQL Fundamental Interview Questions was helpful. Any questions or suggestions are welcome in the comment section below.

Disclaimer: The Questions and Answers provided on https://www.gigxp.com are for general information purposes only. We make no representations or warranties of any kind, express or implied, about the completeness, accuracy, reliability, suitability or availability with respect to the website or the information, products, services, or related graphics contained on the website for any purpose.