SQL Server Interview Questions

A database, in general, is a collection of information in a more organized form for better access. The data can be stored or executed in multiple ways and collectively they are defined as tables, views or database objects. Data warehouse refers to the medium of a repository of the data from the multiple resources. The data from the data warehouse is available to transform or even use in online processing.

In order to remove the redundant data, normalization is used. It also reduces the null values and enables the efficiency of indexing. Its different levels are:

• 1NF
• 2NF
• 3NF

Denormalization is the inverse process of normalization. It highly accelerates the performance of the query by reducing the join operations and also used the OLTP applications.

There is, of course, a difference between the primary and unique key

Primary key

• It enforces the uniqueness of column in a table.
• It does not allow the nulls.
• It chooses default cluster index

Unique key

• It allows one null value.
• It doesn't enforce uniqueness of a column in a table.
• It chooses a default non clustered index.

The commands which are used in SQL to define the structure of database are called as DDL commands. Its types are:

• Create - this command is used to create the databases and its objects.
• Alter - this command is used to alter the existing database objects.
• Drop - this command is used to delete the existing data or database objects.
• Truncate - this command is used to remove the records from the table but not actually from the structure.
• Rename - this command is used to rename the database objects.

The commands which are used to manage the data which is present in the entire database are called as DML commands. They are of following types-

• Select - this command is used to select the specific data from the entire database.
• Insert - if any new record or a table has to be added into the database than this command is used.
• Update - in order to update the existing records over the database this command is used.
• Delete - if any existing record has to be deleted from the table then this command is used.

In order to speed up the performance of the queries, the index is used. It instantly accelerates the retrieval of data from the table and database. An index can be efficiently created over a group or one column.

There are three types of indexes in SQL

• Unique - this index helps to maintain the Data integrity by ensuring that there is no repetition in the two identical values. This reputation might be the data in a table or in any other database record. It maintains the value of index keys as unique.
• Clustered index - clustered index records all of the physical values of the table and database which are entirely based on the key values. Over each table, there will be a single clustered index.
• Non clustered index - this index does not alter the physical value from any segment. Each table under this can have a 999 non clustered indexes in a logical order.

TCL commands are generally used to manage the changes which are managed priory by the DML commands.

• Commit - in order to write and store the changes with the database this command is used.
• Rollback - to restore the database this command is used.

The commands which are used to create the rules to grant the permission are known as useful commands in SQL. These commands also control the access to the database and its objects.

• Grant - it provides the user access
• Deny - it denies permissions given to users
• Revoke - this command remove the user access.

The subset of a table which is basically stored logically in a database is called as a view. A view is a virtual table which is containing the rows and columns. The fields located in views are similar to tables and they do not contain the date of their own. Its advantages are:

• It occupies no space
• It is used to restrict the access given to the database.
• It also hides the complexity associated with data.
• It simply describes the complicated results.

A query is basically a request for the database or the information associated with it in the combination of tables. A query associated with the database can either be a select or an action query.

A SQL query with the other significant query is called a subquery. We can also say it as a subset of all selected states where the values are used for the filtering the results of the main query.

A structure which temporarily or initially stores the data or the database object is called as a template.

A query which retrieves the related columns from the number of tables is called as join. Its different types are:

• Inner
• Left
• Right
• Outer

In SQL, a cursor can be defined as a tool used widely to define a particular set of results. This result can be a set of data rows. A cursor is basically used to solve complex logic and works on a row by row manner.

Index, on the other hand, has the main function of retrieving data from tables much quicker. Indexes are created by users on columns that may be accessed frequently. This enables the user to get information quickly from the table and can be created on a single column or even a group.

In the year 2005, SQL server has introduced apply operator. This is basically a joining that shows the expression of the left table with the right table expression. The expressions that are basically produced in the right table expression is after following up each row in the left table. The left table expressions are calculated in the first hand and then the right table. After following this process we can get our final set value.