SQL Server Interview Questions and Answers
1. What do mean by XML Datatype?
XML data type is used to store XML documents in the SQL Server database. Columns and variables are created and store XML instances in the database.
2. What are the Magic Tables in SQL Server?
Insert and Delete tables are created when the trigger is fired for any DML command. Those tables are called Magic Tables in SQL Server. These magic tables are used inside the triggers for data transaction.
3. What Is SQL Profiler?
SQL Profiler is a tool which allows database administrators 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.
4. 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.
5. What are the types of sub query?
There are three types of sub query –
- Single row sub query which returns only one row
- Multiple row sub query which returns multiple rows
- Multiple column sub query which returns multiple columns to the main query. With that sub query result, main query will be executed.
6. What is SQL Server Agent?
The SQL Server Agent plays a vital role in day to day tasks of SQL Server Administrator. 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 which we can automate according to our requirements.
7. What is a Trigger?
Triggers are used to execute a batch of SQL code, when insert or update or delete commands are executed against a table. Triggers are automatically triggered or executed when the data is modified. It can be executed automatically on insert, update and delete operations.
8. How Global Temporary Tables are represented and its scope?
Global temporary tables are represented with ## before the table name. Scope will be outside the session whereas local temporary tables are inside the session. Session ID can be found using @@SPID.
9. What is a Collation?
Collation is defined to specify the sort order in a table. There are three types of sort order –
1. Case Sensitive (CS)
2. Case Insensitive (CI)
10. What is an IDENTITY column in INSERT statements?
IDENTITY column is used in table columns to make that column as auto incremental number or a surrogate key.
Reference: Manzoor Siddiqui [www.SQLServerLog.com]