Free online sql Server  dba learning​​ 

​Courses

microsoft sql training online free

SQL SERVER – Interview Questions and Answers 


1) General Questions on SQL SERVER


What is Data itself?

it is a raw fact that doesn't give meaning by itself to get meaning from Data it should be processed and converted into information like table.


What is information?

its processed data that can give meaning.


What is Management?

The process of dealing with or controlling things or people. if we deal with management of data (that is called data management.


What is System?

System is a collection of interelated components interact each other by accepting input from the environment and displaying output back to the environment. 


What is SQL Data?


SQL is used to communicate with a database. According to ANSI (American National Standards Institute), it is the standard language for relational database management systems. SQL statements are used to perform tasks such as uptodatedata on a database, or retrieve data from a database.


What are Types of Database?


Flat File Database - a file having no internal hierarchy. A flat file cab be a plain text file or a binary file. There are usually no structural relationship between the records.


Hierarchical Database - is a data model in which the data is organized into a tree like structure. The structure allows representing information using parent/child relationships, each parent can have many children, but each child has only one parent. in short form Data is stored in a parent child relationship only.


Relational Database - a database structured to recognize relations among stored items of information. A fundamental difference between segments in a hierarchical database and tables in relational database is that, in a hierarchical database, segments are implicitly joined with each other. In a relational database, you must explicitly join two tables. in short form a child can have multiple parents and its very flexible while Hierarchical Database is not flexible.


On Database design concept, what is the design database we should follow to create a database?


First we have to identify the problem (why we need to create the database for, what is the purpose?

First We have to define tables

Identify Columns

Specify Data Types

Specify properties

Specify Keys

Normalize tables

Define Relationships

and then Draw ER Diagrams

and finally we start coding


What is SQL Server AlwaysOn?

AlwaysOn is a term Microsoft has used since SQL Server 2012 for high availability and disaster recovery solutions. As of now, two features fall under the umbrella of AlwaysOn. These two features support high availability and disaster recovery for SQL Server databases:

SQL Server AlwaysOn Failover Cluster Instances (FCIs)
SQL Server AlwaysOn Availability Groups (AGs)

SQL Server AlwaysOn FCIs are SQL Server clustered instances whereas AGs are the new features introduced in SQL Server 2012 to support data high availability and disaster recovery. We can group each set of databases into one unit and execute a failover at one time with the help of the Availability Group.


What is the difference between AlwaysOn Failover Cluster Instances and AlwaysOn Availability Groups (AOAG)?

AlwaysOn Failover Cluster Instance needs shared storage between all of the nodes in the cluster.  Whereas AlwaysOn Availability Groups do not require shared disk storage for the server hosting the SQL Server.
AlwaysOn Failover Cluster Instance is available on both SQL Server Standard and Enterprise Edition whereas we need Enterprise Edition to configure SQL Server AlwaysOn Availability Groups until SQL Server 2014. There is now an option to create a basic Availability Group with SQL Server 2016 Standard edition, but it has lot of limitations.
SQL Server AlwaysOn Failover Clustered Instances work at an instance level whereas Availability Group works at a database level or for a set of databases.
We cannot use AlwaysOn Failover Clustered Instances while installing standalone instances whereas Availability Groups can be configured on both standalone as well as SQL Server Clustered Instances.


​What is a SQL DBA?


Database administrators (DBAs) use specialized software to store and organize data. The role may include capacity planning, installation, configuration, database design, migration, performance monitoring, security, troubleshooting, as well as backup and data recovery.


What Database Administrators Do?


Database administrators (DBAs) use specialized software to store and organize data, such as financial information and customer shipping records. They make sure that data are available to users and secure from unauthorized access.


Would you explain Availability Group Listeners?

The Availability Group Listener is a virtual network name that we use to make connections to the databases whether it is running from a primary replica or secondary replica after failover.


What methods are available for removing fragmentation of any kind on an index in SQL Server?

For version (SQL Server 2000):

DBCC INDEXDEFRAG
DBCC DBREINDEX
CREATE INDEX...DROP EXISTING (cluster)
DROP INDEX; CREATE INDEX

For version (SQL Server 2005): The same processes as SQL Server 2000, only different syntax

ALTER INDEX...REORGANIZE
ALTER INDEX...REBUILD
CREATE INDEX...DROP EXISTING (cluster)
DROP INDEX; CREATE INDEX


What are SQL Data Types?


Numeric data types such as int, tinyint, bigint, float, real etc.
Date and Time data types such as Date, Time, Datetime etc.
Character and String data types such as char, varchar, text etc.
Unicode character string data types, for example nchar, nvarchar, ntext etc.
Binary data types such as binary, varbinary etc.


What does DBA mean in database?


A database administrator, frequently known just by the acronym DBA, is a role usually within the Information Technology department, charged with the creation, maintenance, backups, querying, tuning, user rights assignment and security of an organization's databases.


What is bit data type in SQL Server?


​'bit' is one of the integer data types in SQL Server. It can store the values 1, 0 or NULL. It can also be used to store boolean values because TRUE is convertible to 1 and FALSE is convertible to 0.


How many types of SQL are there?


​SQL statements are divided into five different categories: Data definition language (DDL), Data manipulation language (DML), Data Control Language (DCL), Transaction Control Statement (TCS), Session Control Statements (SCS).


What are the types of SQL commands?
We can grouped Sql Commands into five major categories depending on their functionality.

Data Definition Language (DDL) ...
Data Manipulation Language (DML) ...
Data Query Language (DQL) ...
Transaction Control Language (TCL) ...
Data Control Language (DCL)


What is the fundamental unit of storage in SQL Server data files and what is it's size?

A page with a size of 8k.

What is the fundamental unit of storage in SQL Server log files and what is it's size?

A log record, size is variable depending on the work being performed.


How do you configure an AlwaysOn Availability Group with a multi subnet network?

There is only one difference between configuring AOAG in a single vs. multi subnet. You will follow same process that we follow while configuring AOAG in a single subnet, but if you have a multi subnet network then we need one IP from each subnet to configure the AOAG listener. 


Is it possible to add a new database to an existing Availability Group?

We can easily add new databases to an existing Availability Group. First, we need to prepare the secondary database by taking the full backup and subsequent transaction log backup then restore it on the secondary replicas in no recovery mode. Then we can right click on Availability Group name to launch the Add Database wizard. We should follow all required steps to proceed with this wizard. Once completed, your new database will be added to the identified Availability Group.


Let say you have 5 databases in an Availability Group. One database becomes inaccessible. Will the Availability Group initiate an automatic failover?

Until SQL Server 2014, the AlwaysOn Availability Group will not initiate a failover process if anything goes wrong at the database level. Microsoft introduced an option named Enhanced Database Failover in SQL Server 2016 to trigger the failover in case any database participating in an Availability Group loses the ability to write transactions. We also call it Database Level Health Detection in an Availability Group. By default, this option is not enabled. You need to configure it if you want to initiate a failover if anything goes wrong at the database level.


Is it possible if we add additional database files to a database that is part of an AlwaysOn Availability Group?

Yes, we can add database files to the databases that are configured as a portion of the AlwaysOn Availability Group. Here are the high-level steps: First remove the database from the secondary replica. Now, the secondary database will be in a restoring state. Add the data file to your Availability database on the primary replica. Issue a transaction log backup of this availability database on the primary replica then Copy this transaction log backup to the secondary replica and restore it on its corresponding secondary replica using NORECOVERY and the WITH MOVE option. Now add the database back to AlwaysOn Availability Group.


Can we configure an Availability Group between SQL Server instances that are hosted on servers that are part of two different Windows server failover cluster groups?

NO, we cannot configure AlwaysOn Availability Group between different Windows server failover cluster groups. All replicas must be part of same Windows server failover group. This is a basic prerequisite for AOAG.


What is SQL Commands:

SQL commands are instructions, coded into SQL statements, which are used to communicate with the database to perform specific tasks, work, functions and queries with data.

SQL commands can be used not only for searching the database but also to perform various other functions like, for example, you can create tables, add data to tables, or modify data, drop the table, set permissions for users. SQL commands are grouped into four major categories depending on their functionality:

Data Definition Language (DDL) - These SQL commands are used for creating, modifying, and dropping the structure of database objects. The commands are CREATE, ALTER, DROP, RENAME, and TRUNCATE.


Data Manipulation Language (DML) - These SQL commands are used for storing, retrieving, modifying, and deleting data. 
These Data Manipulation Language commands are: SELECT, INSERT, UPDATE, and DELETE.


Transaction Control Language (TCL) - These SQL commands are used for managing changes affecting the data. These commands are COMMIT, ROLLBACK, and SAVEPOINT.


Data Control Language (DCL) - These SQL commands are used for providing security to database objects. These commands are GRANT and REVOKE.


When I tried to create a new Availability Group in SQL Server Management Studio, I saw that "New Availability Group Wizard" is grayed out and I am not able to proceed further to configure Availability Group. What could be the reason for this?

The "New Availability Group Wizard" option is disabled until you enable the Availability Group feature from the SQL Server service property.

You need to launch SQL Server Configuration Manager then open SQL Server service property.
Here you can see the AlwaysOn High Availability tab. Click on this tab.
You can see the Windows cluster name here if you have enabled the cluster feature. As we know Windows cluster is mandatory for AlwaysOn. If you haven’t configured a Windows cluster then first you need to configure it in order to enable AlwaysOn High Availability.
Once you will enable it, the Cluster Group name will appear in this property window and the grayed-out option "Enable AlwaysOn Availability Group" will be enabled. Just click on this option and click the OK button.
You need to restart the service to apply this change on the SQL Server Instance.


What is Boolean data type in SQL Server?


​There is boolean data type in SQL Server. Its values can be TRUE , FALSE or UNKNOWN . However, the boolean data type is only the result of aboolean expression containing some combination of comparison operators (e.g. = , <> , < , >= ) or logical operators (e.g. AND , OR , IN , EXISTS ).


Can we configure Availability Groups without configuring Availability Group Listeners?

Yes, we can configure an Availability Group without configuring listeners. Listeners are configured to make databases connections irrespective of their replica status.


A List of 8 Popular Databases

Oracle 12c. It’s no surprise that Oracle is consistently at the top of lists of popular databases. The first version of this database management tool was created in the late 70s, and there are a number of editions of this tool available to meet your organization’s needs.

The newest version of Oracle, 12c, is designed for the cloud and can be hosted on a single server or multiple servers, and it enables the management of databases holding billions of records. Some of the features of the latest version of Oracle include a grid framework and the use of both physical and logical structures.

This means that physical data management has no effect on access to logical structures. Additionally, security in this release is excellent because each transaction is isolated from others.


MySQL. MySQL is one of the most popular databases for web-based applications. It’s freeware, but it is frequently updated with features and security improvements. There are also a variety of paid editions designed for commercial use. With the freeware version, there’s a greater focus on speed and reliability instead of including a vast array of features, which can be good or bad depending on what you’re attempting to do​


Microsoft SQL Server. This database management engine works on cloud-based servers as well as local servers, and it can be set up to work on both at the same time. Some of the standout features for the 2016 edition include temporal data support, which makes it possible to track changes made to data over time. The latest version of Microsoft SQL Server also allows for dynamic data masking, which ensures that only authorized individuals will see sensitive data.​


PostgreSQL. PostgreSQL is one of several free popular databases, and it is frequently used for web databases. It was one of the first database management systems to be developed, and it allows users to manage both structured and unstructured data. This database management engine can be hosted in a number of environments, including virtual, physical and cloud-based environments


MongoDB MongoDB is designed for applications that use both structured and unstructured data. The database engine is very versatile, and it works by connecting databases to applications via MongoDB database drivers. Since MongoDB wasn’t designed to handle relational data models, even though it can, performance issues are likely to crop up if you attempt to use it this way.
MariaDB This database management system is free, and like many other free offerings, MariaDB also offers paid versions. There are a variety of plug-ins available for it, and it’s the fastest growing open-source database available.

The database engine allows you to choose from a variety of storage engines, and it makes great use of resources via an optimizer that increases query performance and processing. It’s also highly compatible with MySQL, and it is a drop in replacement with exact matching of commands and APIs because many of the developers of MySQL were involved in its development.


DB2 Created by IBM, DB2 is a database engine that has NoSQL capabilities, and it can read JSON and XML files. Unsurprisingly, it’s designed to be used on IBM’s iSeries servers, but the workstation version works on Windows, Linux and Unix.

The current version of DB2 is LUW is 11.1, which offers a variety of improvements. One, in particular, was an improvement of BLU Acceleration, which is designed to make this database engine work faster through data skipping technology. Data skipping is designed to improve the speed of systems with more data than can fit into memory. The latest version of DB2 also provides improved disaster recovery functions, compatibility, and analytics.


SAP HANA Designed by SAP SE, SAP HANA is a database engine that is column-oriented and can handle SAP and non-SAP data. The engine is designed to save and retrieve data from applications and other sources across multiple tiers of storage. Along with being able to be hosted from physical servers, it can also be hosted from the cloud.


What is RDBMS?


Relational Data Base Management Systems (RDBMS) are database management systems that maintain data records and indices in tables. Relationships may be created and maintained across and among the data and tables. In a relational database, relationships between data items are expressed by means of tables. Interdependencies among these tables are expressed by data values rather than by pointers. This allows a high degree of data independence. An RDBMS has the capability to recombine the data items from different files, providing powerful tools for data usage. (Read more here)

What are the Properties of the Relational Tables?

Relational tables have the following six properties:

Values are atomic.
Column values are of the same kind.
Each row is unique.
The sequence of columns is insignificant.
The sequence of rows is insignificant.
Each column must have a unique name.

What is Normalization?

Database normalization is a data design and organization process applied to data structures based on rules that help building relational databases. In relational database design, the process of organizing data to minimize redundancy is called normalization. Normalization usually involves dividing a database into two or more tables and defining relationships between the tables. The objective is to isolate data so that additions, deletions, and modifications of a field can be made in just one table and then propagated through the rest of the database via the defined relationships.

What is De-normalization?

De-normalization is the process of attempting to optimize the performance of a database by adding redundant data. It is sometimes necessary because current DBMSs implement the relational model poorly. A true relational DBMS would allow for a fully normalized database at the logical level, while providing physical storage of data that is tuned for high performance. De-normalization is a technique to move from higher to lower normal forms of database modeling in order to speed up database access.


What is the various techniques you can use to denormalize a database?


Duplicate data: Duplicate data can reduce the number of joins required to process a query, thus reducing CPU usage and disk I/O.

Summary data: Summary data can provide improved query performance by reducing or eliminating the steps required to summarize your data.

Horizontal partitioning: Horizontal partitioning is the splitting of a table into two separate tables at the record level, thus reducing the number of rows per table

Vertical partitioning: Vertical partitioning is the splitting of a table into two separate tables at the column level, thus reducing the number of columns per table.


What is an An index?


It is a separate physical database structure created on a table that facilitates faster data retrieval when you search on an indexed column. it is a pointer to the data. SQL Server also uses indexes to enforce uniqueness on a row or column in a table or to spread out the data on various data pages to help prevent page contention.

​​

What is Table Scan?


It is process of reading every row or record in a table to satisfy a query.


Explain The SQL Server data page?


A data page is 2048 bytes in size with a 32-byte header. The remaining 2016 bytes are used for data (that is, table or index information). SQL Server allocates space for tables and indexes eight pages at a time, a grouping called an extent. When the extent is filled, another extent (8 pages) is allocated. Remember this tip if you take the Microsoft SQL Server certification test or SQL Server DBA job interview. The odds are that you will be asked a question on index and table space allocation. In some cases, a table scan may be faster than using the index. It's the job of the SQL Server query optimizer to determine which index to select and when to perform a table scan. You might have read many recommendations in this website about keeping indexes small and the row width of a table small for maximum performance. All too often, the reasoning behind small row and index width is left out. It boils down to data pages and how many data pages SQL Server has to read to fulfill a query. Suppose that you have a table with 500,000 rows; the size of a row (with overhead bytes) is 250 bytes or (2048 bytes in a data page - 32 bytes of overhead) / 250 bytes = 8) 8 records per data page. The number of data pages required for all 500,000 records is (500,000 / 8 = 62,500) 62,500 data pages. Suppose that you look at your overall table design and decide that you can shrink the size of the maximum row width just over 10 percent so that 9 rather than 8 records fit on a data page. The number of data pages is reduced by almost 7,000 data pages! An index data page is the same. Indexing a 20-character field (with overhead) and a 6-byte field (with overhead), for example, is the difference between 100 keys per data page and 336 keys per data page. The larger index requires SQL Server to read three times as many data pages to access the same number of keys. (This does not take into account the added B-Tree levels caused by a larger index key!) Normalize your tables and select smart indexes.


What is a B-Tree structure?


SQL Server maintains indexes with a B-Tree structure. B-Trees are multilevel self-maintaining structures. A B-Tree structure consists of a top level, called the root; a bottom level, called the leaf (the actual data pages) (always level 0); and zero to many intermediate levels. The greater the number of levels in your index, the more index pages you must read to retrieve the records you are searching for (that is, performance degrades as the number of levels increases). SQL Server maintains two different types of indexes: a clustered index and a nonclustered index.


What is A clustered index?


It is a B-Tree structure where level 0, the leaf, contains the actual data pagesof the table and the data is physically stored in the logical order of the index. NOTE-1: When a clustered index is created, a lot of disk I/O occurs, the data pages are ordered, the index pages are created, and the non-ordered data pages are deleted. Creating a clustered index requires you to have free space in the database that amounts to approximately 1.2 times the amount of data in the table. NOTE-2: Because data is physically ordered on the data pages, you can have only one clustered index per table. Select the clustered index wisely. Also note that the nonclustered index adds one more level by always arriving at the leaf and then having to read the data page. TIP: You can always have up to 249 nonclustered indexes on a table, although you would never want to create anywhere near 249 indexes on a single table. A large number of indexes on a single table affects the performance of other operations, such as UPDATE, DELETE, and INSERT. In SQL Server 6.5, a single-column index cannot exceed 256 bytes in width; a composite index cannot exceed 900 bytes in width (in SQL Server versions before 6.5, the limit was 256 bytes, regardless of index type). Any index cannot exceed 16 columns. Again, you would never want an index that is 900 bytes in width or that contains 16 columns. Remember to use narrow-width indexes to maximize the number of index keys on a data page. This improves performance by requiring less disk I/O to scan the index. Try not to exceed 4 columns when creating indexes. A table can have both clustered and nonclustered indexes. Because you are allowed to have only a single clustered index on a table, you can meet your other indexing needs with nonclustered indexes. Try not to over-index; in many cases, a clustered index and two to four nonclustered indexes are more than sufficient.


​It is widely known that an index can help speed data retrievals; from time to time, you may hear someone say that indexes slow down other operations, such as inserts, updates, and deletes--which is true. It has been mentioned that B-Tree data structures are, for the most part, self-maintaining data structures, meaning that as rows are added, deleted, or updated, the indexes also are updated to reflect the changes. All this updating requires extra I/O to update the index pages.


What happens when a new row is added to a table without a clustered index?


The data is added at the end of the last data page.


What happens when a new row is added to a clustered index?


The data is inserted into the correct physical and logical order in the table and other rows may be moved up or down, depending on where the data is placed, causing additional disk I/O to maintain the index. As the index pages and data pages grow, they may be required to split and it is requiring slightly more disk I/O. TIPS-In general, you should not worry about the time required to maintain indexes during inserts, deletes, and updates. Be aware that extra time is required to update the indexes during data modification and that performance can become an issue if you over-index a table. On tables that are frequently modified, We should try to restrict the tables to a clustered index and no more than three or four nonclustered indexes. Tables involved in heavy transaction processing should be restricted to from zero to two indexes. If you find the need to index beyond these numbers, run some benchmark tests to check for performance degradation.


On which Data Type we Can not create an index?


You can create an index using the Enterprise Manager and you cannot create an index on the following data types: bit, text, image, and further more Indexes cannot be created on a view.


What is a composite index?


An index that consists of more than one column is called a composite index. When creating a composite index, the order of the columns in the index makes a difference. SQL Server keeps density statistics on all columns that make up the index, but only histogram statistics on the first column of the composite index. As a result, if the first column of a composite index has very few unique values, the index may not be used for index selection by the query optimizer. When creating a composite index, select the column with the most unique values for the first column of a composite index. The maximum size of a composite index in SQL Server 6.5 is 900 bytes. In both SQL Server 2005 and 2008, you can include up to 16 columns in an index, as long as the index doesn't exceed the 900-byte limit. Both clustered and nonclustered indexes can be composite indexes. According to Maximum Capacity Specifications for SQL Server, maximum number of nonclustered indexes per table (which is what unique constrains use under the hood) is limited to 999. Bytes per index key : The maximum number of bytes in a clustered index key cannot exceed 900 in SQL Server. 


What CAUTION do we have to take in creating Indexes in SQL Server?


The table on which the index is being created is locked during index creation. Creating indexes on very large tables or creating clustered indexes (which may reorder the data pages) can take some time to complete. You cannot access the table until the index creation is complete. Try to create clustered indexes and very large nonclustered indexes during non-peak hours. TIP: Always build a clustered index before building a nonclustered index. When a clustered index is built, all nonclustered indexes currently on the table are rebuilt. You also can build an index on a temporary table. NOTE: SQL Server provides another method to create indexes called constraints. A constraint is added to a table during the table creation and can be used to maintain referential integrity. The primary key constraint places a unique index, clustered or nonclustered, on the columns defined as the primary key. You cannot drop a constraint index with the Enterprise Manager (using the Manage Index dialog box). To remove a constraint, you must use the ALTER TABLE command.


​What is Non Clustered Index?


With a nonclustered index, the leaf level pages containpointers to the data pagesand rows, not the actual data (as does the clustered index). A nonclustered index does not reorder the physical data pages of the table. Therefore, creating a nonclustered index does not require the large amounts of free disk space associated with creating a clustered index.


How many different types of pages exist in SQL Server?

Data
Index
Text/Image (LOB, ROW_OVERFLOW, XML)
GAM (Global Allocation Map)
SGAM (Shared Global Allocation Map)
PFS (Page Free Space)
IAM (Index Allocation Map)
BCM (Bulk Change Map)
DCM (Differential Change Map)




How is ACID property related to Database?

ACID (an acronym for Atomicity Consistency Isolation Durability) is a concept that Database Professionals generally look for while evaluating databases and application architectures. For a reliable database, all this four attributes should be achieved.

Atomicity is an all-or-none proposition.

Consistency guarantees that a transaction never leaves your database in a half-finished state.
Isolation keeps transactions separated from each other until they are finished.
Durability guarantees that the database will keep track of pending changes in such a way that the server can recover from an abnormal termination.


What are the Different Normalization Forms?

1NF: Eliminate Repeating Groups

Make a separate table for each set of related attributes, and give each table a primary key. Each field contains at most one value from its attribute domain.

2NF: Eliminate Redundant Data

If an attribute depends on only part of a multi-valued key, then remove it to a separate table.

3NF: Eliminate Columns Not Dependent On Key

If attributes do not contribute to a description of the key, then remove them to a separate table. All attributes must be directly dependent on the primary key. (Read more here)

BCNF: Boyce-Codd Normal Form

If there are non-trivial dependencies between candidate key attributes, then separate them out into distinct tables.

4NF: Isolate Independent Multiple Relationships

No table may contain two or more 1:n or n:m relationships that are not directly related.

5NF: Isolate Semantically Related Multiple Relationships

There may be practical constrains on information that justify separating logically related many-to-many relationships.

ONF: Optimal Normal Form

A model limited to only simple (elemental) facts, as expressed in Object Role Model notation.

DKNF: Domain-Key Normal Form

A model free from all modification anomalies is said to be in DKNF.

Remember, these normalization guidelines are cumulative. For a database to be in 3NF, it must first fulfill all the criteria of a 2NF and 1NF database.


What is a Stored Procedure?

A stored procedure is a named group of SQL statements that have been previously created and stored in the server database. Stored procedures accept input parameters so that a single procedure can be used over the network by several clients using different input data. And when the procedure is modified, all clients automatically get the new version. Stored procedures reduce network traffic and improve performance. Stored procedures can be used to help ensure the integrity of the database.

e.g. sp_helpdb, sp_renamedb, sp_depends etc.

What is a Trigger?

A trigger is a SQL procedure that initiates an action when an event (INSERT, DELETE or UPDATE) occurs. Triggers are stored in and managed by the DBMS. Triggers are used to maintain the referential integrity of data by changing the data in a systematic fashion. A trigger cannot be called or executed; DBMS automatically fires the trigger as a result of a data modification to the associated table. 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-drive and are not attached to a specific table as triggers are. Stored procedures are explicitly executed by invoking a CALL 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 when the trigger is fired because of data modification, it can also cause another data modification, thereby firing another trigger. A trigger that contains data modification logic within itself is called a nested trigger.


What are the Different Types of Triggers?

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 the triggering action such as an insert, update, or delete.

  • After Trigger

After triggers execute following the triggering action, such as an insert, update, or delete.

      2. DDL Trigger


This type of trigger is fired against Drop Table, Create Table, Alter Table or Login events. DDL Triggers are always After Triggers.


What is a View?

A simple view can be thought of as a subset of a table. It can be used for retrieving data as well as updating or deleting rows. Rows updated or deleted in the view are updated or deleted in the table the view was created with. It should also be noted that as data in the original table changes, so does the data in the view as views are the way to look at parts of the original table. The results of using a view are not permanently stored in the database. The data accessed through a view is actually constructed using standard T-SQL select command and can come from one to many different base tables or even other views.

What is an Index?

An index is a physical structure containing pointers to the data. Indices are created in an existing table to locate rows more quickly and efficiently. It is possible to create an index on one or more columns of a table, and each index is given a name. The users cannot see the indexes; they are just used to speed up queries. Effective indexes are one of the best ways to improve performance in a database application. A table scan happens when there is no index available to help a query. In a table scan, the SQL Server examines every row in the table to satisfy the query results. Table scans are sometimes unavoidable, but on large tables, scans have a terrific impact on performance.

What is a Linked Server?

Linked Servers is a concept in SQL Server by which we can add other SQL Server to a Group and query both the SQL Server databases using T-SQL Statements. With a linked server, you can create very clean, easy–to-follow SQL statements that allow remote data to be retrieved, joined and combined with local data. Stored Procedures sp_addlinkedserver, sp_addlinkedsrvlogin will be used to add new Linked Server. 

What is a Cursor?

A cursor is a database object used by applications to manipulate data in a set on a row-by-row basis, instead of the typical SQL commands that operate on all the rows in the set at one time.

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 

What is Collation?

Collation refers to a set of rules that determine how data is 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. 


What is the Difference between a Function and a Stored Procedure?

UDF can be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section, whereas Stored procedures cannot be. UDFs that return tables can be treated as another rowset. This can be used in JOINs 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.

What is subquery? Explain the Properties of a Subquery?

Subqueries are often referred to as sub-selects as they allow a SELECT statement to be executed arbitrarily within the body of another SQL statement. A subquery is executed by enclosing it in a set of parentheses. Subqueries are generally used to return a single row as an atomic value although they may be used to compare values against multiple rows with the IN keyword.

A subquery is a SELECT statement that is nested within another T-SQL statement. A subquery SELECT statement if executed independently of the T-SQL statement, in which it is nested, will return a resultset. This implies that a subquery SELECT statement can stand alone, and it does not depend on the statement in which it is nested. A subquery SELECT statement can return any number of values and can be found in the column list of a SELECT statement, and FROM, GROUP BY, HAVING, and/or ORDER BY clauses of a T-SQL statement. A subquery can also be used as a parameter to a function call. Basically, a subquery can be used anywhere an expression can be used. 

What are Different Types of Join?
Cross Join

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. The common example is when company wants to combine each product with a pricing table to analyze each product 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. This is the default type of join in the Query and View Designer.

Outer Join

A join that includes rows even if they do not have related rows in the joined table is an Outer Join. You can create three different outer join to specify the unmatched rows to be included:

Left Outer Join: In 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: In 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: In Full Outer Join, all the rows in all joined tables are included, whether they are matched or not.

Self Join

This is a particular case when one table joins to itself with one or two aliases to avoid confusion. A self join 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 hierarchal reporting structure whereby one member of staff reports to another. Self Join can be Outer Join or Inner Join. (Read more here)

What are Primary Keys and Foreign Keys?


Primary keys are the unique identifiers for each row. They must contain unique values and cannot be null. Due to their importance in relational databases, Primary keys are the most fundamental aspect of all keys and constraints. A table can have only one primary key.

Foreign keys are a method of ensuring data integrity and manifestation of the relationship between tables.


                                                                                                                                         Continue to page 2