MS SQL Interview Questions and Answers
Question - 81 : - What are Index, cluster index, and non-cluster index?
Answer - 81 : -
Clustered Index:- A Clustered index is a special type of index that reorders the way records in the table are physically stored. Therefore table may have only one clustered index.
Non-NonClustered Index:- A Non-Clustered index is a special type of index in which the logical order of the index does not match the physical stored order of the rows in the disk. The leaf nodes of a non-clustered index do not consist of the data pages. instead, the leaf node contains index rows.
Question - 82 : - Write down the general syntax for a SELECT statement covering all the options.
Answer - 82 : -
Here’s the basic syntax: (Also checkout SELECT in books online for advanced syntax)
SELECT select_list
[INTO new_table_]
FROM table_source
[WHERE search_condition]
[GROUP BY group_by__expression]
[HAVING search_condition]
[ORDER BY order__expression [ASC | DESC] ]
Question - 83 : - What is a join and explain different types of joins?
Answer - 83 : -
Joins are used in queries to explain how different tables are related. Joins also let you select data from a table depending upon data from another table.
Types of joins:
INNER JOINs,
OUTER JOINs,
CROSS JOINs
OUTER JOINs are further classified as
LEFT OUTER JOINS,
RIGHT OUTER JOINS and
FULL OUTER JOINS.
Question - 84 : - What is the OSQL utility?
Answer - 84 : -
OSQL is a command-line tool that is used to execute the query and display the result the same as a query analyzer but everything is in the command prompt.
Question - 85 : - What Is the Difference Between OSQL And Query Analyzer?
Answer - 85 : -
OSQL is the command-line tool that executes the query and displays the result the same as a query analyzer but the query analyzer is graphical and OSQL is a command-line tool. OSQL is quite useful for batch processing or executing remote queries.
Question - 86 : - What Is Cascade delete/update?
Answer - 86 : -
CASCADE allows deletions or updates of key values to cascade through the tables defined to have foreign key relationships that can be traced back to the table on which the modification is performed.
Question - 87 : - What are some of the join algorithms used when SQL Server joins tables.
Answer - 87 : -
- Loop Join (indexed keys unordered)
- Merge Join (indexed keys ordered)
- Hash Join (non-indexed keys)
Question - 88 : - What are Magic Tables in SQL Server?
Answer - 88 : -
The MAGIC tables are automatically created and dropped, in case you use TRIGGERS. SQL Server has two magic tables named, INSERTED and DELETED
These are maintained by the SQL server for their Internal processing. When we use update insert or delete on tables these magic tables are used. These are not physical tables but are Internal tables. Whenever we use insert statement is fired the Inserted table is populated with newly inserted Row and whenever delete statement is fired the Deleted table is populated with the deleted row.
But in case of update statement is fired both Inserted and Deleted tables used for records the Original row before updating get stored in the Deleted table and the new row Updated gets store in Inserted table.
Question - 89 : - Can we disable a trigger? if yes HOW?
Answer - 89 : -
YES, we can disable a single trigger on the database by using “DISABLE TRIGGER triggerName ON <>”
we also have an option to disable all the triggers by using, “DISABLE Trigger ALL ON ALL SERVER”
Question - 90 : - Why do you need indexing? where is Stored and what do you mean by schema object? For what purpose we are using view?
Answer - 90 : -
We can’t create an Index on an Index... The index is stored in the user_index table. Every object that has been created on Schema is Schema Object like Table, View etc. If we want to share the particular data to various users we have to use the virtual table for the Base table. So that is a view.
Indexing is used for faster search or to retrieve data faster from the various tables. Schema containing a set of tables, basically schema means logical separation of the database. The view is crated for faster retrieval of data. It’s a customized virtual table. we can create a single view of multiple tables. Only the drawback is..view needs to be get refreshed for retrieving updated data.