SQL Server Interview Questions - Part 3

This section covers having clause and where clause of SQL server.......

Here is simple definition of Error and raiseerror of SQL Server.

SQL Server Database Architecture - Here we have tried to cover all about SQL Server Database Architecture such as logical component and physical component

Define Normalization and De- Normalization. We have answered to this question in this section....

Types of Transact-SQL - SQL Server Provides three types of Transact-SQL statements namely DDL, DCL, and DML. Data Definition Language (DDL). It allows creating, altering and dropping database objects........

SQL Server database Objects - In this section, we have covered all about SQL server database objects such as Table, Data types, Function, Index, Constraint, Rule, Default, Stored Procedures, Trigger, and View

SQL Server Entity, Domain, Referential Integrity - In this section we have defined data, entity, domain and referential integrity of SQL server.

SQL Server BCP Utility and DTS package - In this section, we have tried to discuss about SQL Server importing and exporting utility.

SQL Server Distributed Query - In this article, we have defined SQL Server distributed query, linked server and ad hoc computer

SQL Server Extended and Remote stored procedure - Define temporary and extended stored procedure. You can find answer to this question in this series.

Define Primary and Unique key. We have defined these two terms of database in this series.

SQL Server Index - What is index? Define its types. This is quite common question that might be asked during interview.

SQL server is an RDBMS that uses Transact-SQL to interact with the client application. It includes database........

Denormalization is the reverse process of normalization. It is controlled introduction of redundancy to the database design......

One to one is implemented using single table by establishing relationship......

Implemented using two tables with primary key and foreign key relationships......

Implemented using a junction table. The keys from both the tables form composite primary key of the junction table......

User defined data types are most commonly used when consistency among several tables storing the same type of data is desired........

Both enforce uniqueness of the column. By default primary key creates a clustered index........

Bit datatype is used to store boolean information like 1 or 0 (true or false)........

Candidate key: A column or a set of columns can be called as candidate key if they i dentify each row of a table uniquely........

A key formed by combining at least two or more columns.......

Default constraint is used to fill column with default value defined during creation of table if nothing is supplied while inserting data........

Transaction encapsulates SQL commands and work as a single unit........

Isolation levels determine the degree of isolation of data during concurrent access.......

Lock escalation is the process of converting low level locks (row locks, page locks) into higher level locks (table locks)........

SQL Profiler is used to diagnose a problem by capturing events in a trace file......

SQL Server Service Manager is used to start, stop, and pause SQL Server services that exist as the separate components on the server......

SQL Query Analyzer is a tool that are used for various purposes such as creating and executing queries.........

DTS, Data Transformation Services is used to import and export data between heterogeneous data source.......

Replication allows creating copies of data in separate databases along with keeping all databases........

Bulk copying is used to transfer large amount of data. Distributed Queries in SQL server are used to reference heterogeneous data source.......

SQL server agent is important service of the server where instances of SQL server are running.........

DELETE TABLE is a logged operation, it is a slow process. TRUNCATE TABLE deletes all the rows........

Constraints enforce the data integrity to the database and protect columns of the table from unwanted values........

Indexes of SQL Server are similar to the indexes in books. They help SQL Server retrieve the data quicker.........

RAID stands for Redundant Array of Inexpensive Disks. It provide fault tolerance to database servers........

No indexes. Excess recompilations of stored procedures. Procedures and triggers without SET NOCOUNT ON........

SET SHOWPLAN_ALL ON, SET SHOWPLAN_TEXT ON, SET STATISTICS IO ON.......

Use NT authentication. Use server database and application roles to control access to the data. Secure the physical database files using NTFS permissions.......

When two processes, each having a lock on one piece of data, attempt to acquire a lock on the other's piece........

When one connection from an application holds a lock and a second connection requires.......

BACKUP/RESTORE, Dettach/attach of databases, Replication, DTS, BCP, logshipping.......

Full database backup. Differential database backup........

The process of copying/moving data between databases on the same or different servers......

Cursors allow row-by-row prcessing of the resultsets. Types of cursors: Static, Dynamic, Forward-only, Keyset-driven.......

Joins are used in queries to explain how different tables are related.......

An extended stored procedure compiles as DLL and are created to expand capabilties of user defined stored procedure.......

Triggers are special kind of event driven stored procedures. Executed automatically when an INSERT, UPDATE or DELETE operation takes place on a table.......

Two instances of the same table will be joined in the query........

UNION statement eliminates duplicate rows whereas UNION ALL statement includes duplicate rows........

Cursor manipulates records row by row. It requires temporary storage for row manipulation........

This process synchronizes two SQL servers and thus provides ready server in case one fails........

Shared Lock: Shared locks are used for operations that read data, such as a SELECT statement. During Shared locks used, concurrent transactions can read a resource, but cannot modify the data.......

An extent is 8 continuous pages to hold server object. Uniform extents - This type of extent contains data from one table.......

DBCC is database consistency checker. DBCC commands are used to check the consistency of the databases........

Collation is the order that SQL Server uses for sorting or comparing textual data........

They are stored in master db in the sysxlogins table.......

It is utility used to copy huge data from tables and views without copy schema of the server object........

We can copy data along schema using DTS package........

Sub-query is a query within a Query........

All these three are the system stored procedure used to manage windows account authentication........

Select name from sysObjects where xtype=’u’.......

Local temporary table is created by prefixing name with pound sign like (#table_name).......

UPDATE_STATISTICS updates the indexes on the tables when there is large processing of data........

Both stored as B-tree structure. The leaf level of a clustered index is the actual data where as leaf level of a non-clustered index is pointer to data.......