SQL Server Interview Questions and Answers - part2

1. What is a Database?

A database is a collection of meaningful information organised in a manner that can be used whenever there is a requirement for data. It is a collection of data objects like tables, views, schema and logins etc.

2. What are the different Codd’s rule?

– Relational Database Management
– Information Representation
– Logical Accessibility
– Representation of null values
– Catalog facilities
– Data Language
– View Updatability
– Physical data independence
– Logical data independence
– Integrity Constraints
– Database Distribution
– Non Subversion

3. Explain different types of relationships in database.

One to One (1:1): One element of an entity has relation to one and only one element of another entity. e.g.: One employee has one and only one employee number.

One to Many (1: N): An element of any entity may have relation to one element of another element. e.g. one employee can belong to only one department but one department may have more than one employee.

Many to Many (N: N): Many elements of an entity are related to more than one element of another entity. e.g. One supplier can supply many products and similarly one products may be supplied by more than one supplier.

4. Explain Primary Key.

A primary key is one or more column(s) in a table used to uniquely identify each row. NOT NULL constraint is active on it which means you cannot keep column as blank and data across the column(s) must be unique.

5. How to rename a table?

To rename a table use sp_rename oldTableName, NewTableName

6. How to view columns definition in a table?

by executing sp_columns TableName

7. What is the syntax to drop a column?

ALTER TABLE TableName
DROP COLUMN ColumnName

8. What is the difference between primary key and unique key?

Primary key do not allow NULL value whereas unique key allows one NULL.

9. What are the difference between DELETE and TRUNCATE?

a. You can rollback after DELETE but after performing TRUNCATE you cannot rollback.
b. DELETE locks the table row whereas TRUNCATE locks the entire table.
c. DELETE do not reset identity value whereas TRUNCATE resets identity of table.
d. DELETE is DML command whereas TRUNCATE is DDL command.
e. You can use WHERE clause with DELETE to filter the data but you cannot use WHERE clause with TRUNCATE.

10. What is an alias?

When you want to give a short name or different name to a table its called alias and you can use that alias on behalf of table name or can use with column name in where clause or join clause etc.

Q1. Which is the TCP/IP port on which the SQL Server runs and how it can be changed?

Ans. SQL server runs on the port 1433. It can be changed from the Network Utility TCP/IP properties. 

Q2. What is the difference between a clustered and a non-clustered index?

Ans. Clustered index are special types of index that records in which way the records would be stored physically in the table. Thus any table can have only one clustered index. Its leaf node contains the data pages. Non-clustered index is the special type of index which supports the logical order of indexes. It does not match the physical stored order of the rows on the disk. Leaf node contains index rows but no data pages. 

Q3. What are different index configurations of a table?

Ans.  A table can have one of the five different types of indexes. These are ‘no indexes’, ‘clustered index’, ‘one clustered and multiple non clustered indexes’, ‘a non clustered index’, and ‘many non clustered indexes.’ 

Q4. What is OTLP or Online Transaction Processing?

Ans. In OTLP or Online Transaction Processing the relational database designs use the disciplines of data modeling and it generally follows the code rules of data normalization. This can ensure complete data integrity. Data used is broken down into simple structures like tables where all the individual atomic level of elements would relate with each other and they thus satisfy normalization rules. 

Q5. What are differences between the primary key and unique key?

Ans. Primary key as well as the unique key would enforce the uniqueness of the column in which they are defined. By default primary key creates clustered indexes on the columns. Unique key creates non clustered index by default. Primary key won’t allow NULL whereas unique key allows one NULL. 

Q6. What are the differences between the DELETE and TRUNCATE commands?

Ans. DELETE command removes rows from the table and it is based on the condition that a WHERE clause is provided. TRUNCATE removes all the rows from a table and it would erase all data from the table after the command is executed. 

Q7. How TRUNCATE command functions?

Ans. TRUNCATE command functions being faster and using fewer systems as well as transaction log resources than DELETE. It removes data by deallocating data pages that are used to store data in the table. It would remove all the rows from a table but not the structure. 

Q8. What are the limitations of TRUNCATE command?

Ans. TRUNCATE cannot be used on tables that are referenced by FOREIGN KEY constraints and as it is not logged it cannot activate a trigger. It is a DDL command and cannot be ROLLED BACK. 

Q9. How the DELETE command functions?

Ans. DELETE would remove the rows at the rate of one at a time and would record an entry in the transaction log in respect of each of the rows so deleted. It retains the identity counter and can be used without a WHERE clause or with it and activates Triggers. DELETE is DML command that can be rolled back.   

Q10. What is the use of UPDATE STATISTICS command?

Ans. UPDATE STATISTICS command is used when large data are processed. When large amount of deletions or modifications occurs in a table, the update indexes have to take changes into account and would update the table indexes accordingly. 

Q.11. What is difference between HAVING CLAUSE and WHERE CLAUSE?

Ans. The difference between HAVING clause and WHERE clause is that the former is used with SELECT statement only in GROUP BY clause whereas the later is used when there is no GROUP BY clause. 

Q12. What are the properties of sub-query?

Ans. Sub-query needs to be enclosed in parenthesis, should be put towards the right hand of comparison operator, cannot have and ORDER-BY clause, and one query can have multiple sub-queries. 

Q13. What are the types of sub-queries?

Ans.  There are three types of sub-queries; single-row, multiple rows, and multiple column sub queries. 

Q14. What is SQL Profiler?

Ans. SQL Profiler is graphical tool that can allow the system administrator to monitor events in an instance of the Microsoft SQL Server. 

Q15. What are the functions of SQL Profiler?

Ans. SQL Profiler saves and captures data about each event to one of the files in SQL Server table to be analyzed later. However, only limited number of events should be monitored by SQL Profiler. 

Q16. What are the authentic modes for SQL Profiler?

Ans. There are two modes namely Windows mode and Mixed mode for SQL Profiler. 

Q17. What is meant by SQL Server Agent?

Ans. SQL Server agent would play important roles in day-to-day task performed by database administrator (DBA). It helps user schedule jobs and scripts. 

Q.18. What is Log Shipping?

Ans. Log Shipping is the process of automating the backup of the database as well as the transaction log files on production SQL server. They are later restored into standby server. 

Q.19. How Log Shipping operates?

Ans. The transactional log file from a server is automatically updated into the backup database on other server. When one server fails the other would have the same DB and can be sued for Disaster Recovery Plan. 

Q.20 How to get accurate count of number of records in a table?

Ans. Three ways to do it are as follows.

SELECT * FROM table

SELECT COUNT (*) FROM table 1

SELECT rows FROM sys indexes WHERE id – OBJECT_ID (table 1) AND indid <2. 

Q21. What is the difference between Global and Local temporary table?

Ans. A local temporary table exists till the connection is alive. It if is identified within a compound statement, it can be used for the duration of the compound statement. Global temporary table would remain in database permanently but rows would exist within a given connection.  

Q22. What is meant by STUFF FUNCTION?

Ans. STUFF functions helps overwriting existing characters. 

Q23. What is the difference between STUFF and REPLACE?

Ans. STUFF is used to overwrite existing characters whereas REPLACE is used to replace existing characters of all occurrences. 

Q24. What is meant by PRIMARY KEY?

Ans. PRIMARY KEY constraint is unique identifier for the row within the database table. Every table must have one primary key constraint that would uniquely identify rows. There can only be one primary key for a table. 

Q25. What is meant by UNIQUE KEY?

Ans. UNIQUE key constraint enforces the value of set columns. In result no duplicate values can be entered. 

Q26. What is meant by FOREIGN KEY?

Ans. FOREIGN KEY constrain prevents any action that could destroy the links existent between tables and its corresponding data values. It enforces referential integrity.  

Q27. What is meant by CHECK constraint?

Ans. CHECK constraint limits the values that can be placed in a column. It enforces domain integrity. 

Q28. What is NOT NULL constraint?

Ans. NOT NULL constraint would ensure that columns won’t accept NULL values. 

Q29. What are meant by Scheduled Jobs or Scheduled Tasks?

Ans. Scheduled Tasks help the user to automate the process that run on regular or predictable cycles. User has the option to determine the order in which the tasks would be run by creation of job steps. 

Q30. What are advantages of using Stored Procedures?

Ans. Stored procedures can reduce network traffic as well as latencies and thus boost up the application performance. Its execution plans can be reused and it helps promote code reuse. It can encapsulate logic. 

Q31. How is table without cluster or non-cluster defined?

Ans. Tables without cluster or non-cluster is called unindexed table or HEAP. 

Q32. Is it possible linking SQL Servers to other Servers such as ORACLE?

Ans. SQL Server can be linked to any of the servers but it requires the OLE-DB provider from Microsoft in order to allow a link. 

Q33. What is BCP and how is it used?

Ans. BCP or BulkCopy is the tool used to copy huge amount of data from tables as well as views.

Q34. How the one-to-one, one-to-many, and many-to-many relationships are implemented in a table?

Ans. One-to-one relationship is implemented with single table. One-to-many relationship is implemented by splitting data into a couple of tables with primary and foreign key relationships. Many-to-many relationships are implemented using junction table having keys from both tables making the composite primary key for the tables. 

Q35. What is an execution plan?

Ans. Execution plan is basically the road map representing graphical or text form of the data retrieval methods that is chosen by the query optimizer of SQL Server.

1. What’s SQL Server?

SQL Server is a DBMS system provided by Microsoft. SQL Server is sometimes mistakenly referred to as SQL.

2. How do you trace the traffic hitting a SQL Server?

SQL profiler is the SQL Server utility you can use to trace the traffic on the SQL Server instance. Traces can be filtered to narrow down the transactions that are captured and reducing the overhead incurred for the trace. The trace files can be searched, saved off, and even replayed to facilitate troubleshooting.

3. What types of replication are supported in SQL Server?

SQL Server has three types of replication: Snapshot, Merge, and Transaction. Snapshot replication creates a snapshot of the data (point-in-time picture of the data) to deliver to the subscribers. This is a good type to use when the data changes infrequently, there is a small amount of data to replicate, or large changes occur over a small period of time.

Merge replication uses a snapshot to seed the replication. Changes on both sides of the publication are tracked so the subscriber can synchronize with the publisher when connected. A typical use for this type of replication is in a client and server scenario. A server would act as a central repository and multiple clients would independently update their copies of the data until connected. At which time, they would all send up their modifications to the central store.

Transaction replication also begins with a snapshot only this time changes are tracked as transactions (as the name implies). Changes are replicated from publisher to subscriber the same as they occurred on the publisher, in the same order as they occurred, and in near real time. This type of replication is useful when the subscriber needs to know every change that occurred to the data (not point-in-time), when the change volume is high, and when the subscriber needs near real-time access to the changes.

4. Why would you use SQL Agent?

SQL Agent is the job scheduling mechanism in SQL Server. Jobs can be scheduled to run at a set time or when a specific event occurs. Jobs can also be executed on demand. SQL Agent is most often used to schedule administrative jobs such as backups.

5. What authentication modes does SQL Server support?

SQL Server supports Windows Authentication and mixed-mode. Mixed-mode allows you to use both Windows Authentication and SQL Server Authentication to log into your SQL Server. It’s important to note that if you use Windows Authentication, you will not be able to log in as sa.

6.  What is Log Shipping?

Log shipping is the process of automating the backup of database and transaction log files on a production SQL server, and then restoring them onto a standby server. Enterprise Editions only supports log shipping. In log shipping the transactional log file from one server is automatically updated into the backup database on the other server. If one server fails, the other server will have the same db and can be used this as the Disaster Recovery plan. The key feature of log shipping is that it will automatically backup transaction logs throughout the day and automatically restore them on the standby server at defined interval.

7. What is database isolation in SQL Server?

Database isolation comes into play when we need to isolate the database and protect it from other things on the network. This protection is achieved using locks. The type of lock and the level of isolation level needed is referred as isolation level in SQL Server.

Types of isolation levels:

• READ COMMITTED: Shared locks are held while any data is being read.
• READ UNCOMMITTED: Specifies isolation level 0 locking. There are thus no shared locks or exclusive locks. Lease restrictive of all the isolation levels.
• REPEATABLE READ: Locks are applied on all data being used by a query. However, new phantom rows can be inserted into the data set by another user and are included in later reads within the current transaction.
• SERIALIZABLE: Issues a range lock on data set, preventing other users to update or insert data into dataset until the transaction is complete.

8. What is a Schema in SQL Server 2005? Explain how to create a new Schema in a  Database?

A Schema can be used in many ways including: maintain a backup script to allow the user to create all users, groups, logins and permissions, to create/modify development code, to create an environment from it for testing. It acts as the container of all object in the database which can be used to recreate the database along with its objects intact. It is synonymous to a namespace.

Syntax:

CREATE SCHEMA schema_name_clause [ <schema_element> [ …n ] ] 
<schema_name_clause> ::=
schema_name
| AUTHORIZATION owner_name
| schema_name AUTHORIZATION owner_name
} 
<schema_element> ::= 
{ 
table_definition | view_definition | grant_statement | 
revoke_statement | deny_statement
}

9. What are the various security modes for SQL Server and how do they work?

Since SQL Server 7 – Microsoft has only supported two modes:

• Windows Only – Only Windows accounts can access the server via an integreated login process.
• Windows and SQL Server – Both Windows accounts and accounts created within SQL Server can connect to the server.

Windows Only is recommended but not always practical depending on your infrastructure.  In addition, SQL Server 2005 has taken great strides to increase the security of standard SQL Server logins by adding password complexity, timeouts, and other features previously absent.

Logging-in is only the first step. Once a user logs in, he or she must access the individual databases. For this, an entry must exist in the sysusers table for each database for that user. Keep a close eye on that “guest” account in your databases and make sure you don’t inadvertently give someone access to a database.

10. What are some things I can do to secure my SQL Server?

• Keep up to date on SQL Server service packs and patches
• Audit SQL Server accounts for weak passwords
• Restict access to the SQL Server to only trusted clients
• Use Windows Only authentication where possible
• Store SQL Server backup files in a secure location and encrypted
• Disable all netlibs if the SQL Server is local-only
• Regularly scan the installation with Microsoft’s Baseline Security Analyzer

SQL SERVER DBA – TOPIC WISE FAQs

SQL SERVER

1. What is SQL Server?

Microsoft SQL Server 2005 is a database platform for large-scale online transaction processing (OLTP), data warehousing, and e-commerce applications; it is also a business intelligence platform for data integration, analysis, and reporting solutions.

2. What is the Versions Available in SQL Server?

SQL Server was developed by Microsoft in 1987 and the versions are 6.0, 6.5, 7.0, 8.0(2000), 9.0(2005), 10(2008).

3. What is the Difference between SQL Server 2000 and 2005? (i)

SQL Server-2000	SQL Server-2005
Can create up to 16 Instances	Can create up to 50 instances
Query Analyzer and Enterprise Manager are separate	Both were combined as Management Studio
Clustering or Log Shipping require Enterprise Edition	Clustering, Database Mirroring or Log Shipping available in Standard Edition
4CPUs, Limited to 2GB in Standard Edition	4 CPU, no RAM limit in Standard Edition
DTS	SSIS (All features of ETL tools)
No Exception handling in DTS	Exception handling in SSIS
‘sa’ blank password.	Enforce password policy.
No database mirroring	Database mirroring.
	Object oriented programming
	Common language runtime

4. What are the Editions available for SQL Server 2005?

SQL Server 2005 is available in 32-bit and 64-bit editions.

1. SQL Server 2005 Enterprise Edition (32-bit and 64-bit)
2. SQL Server 2005 Standard Edition (32-bit and 64-bit)
3. SQL Server 2005 Workgroup Edition (32-bit only)
4. SQL Server 2005 Developer Edition (32-bit and 64-bit)
5. SQL Server 2005 Express Edition (32-bit only)

5. What are the tools are available in SQL Server 2000 and 2005?

Sl	2000	2005
1	Enterprise manager & Query Analyzer	Management Studio
2	Network Client Utilities	Configuration manager
3	SQL Profiler	Profiler
4	Service manager	Surface area configuration
5		Reporting Services Configuration
6		Database Tuning Advisor
7		Command Prompt Utilities

6. What are the new features available in SQL Server 2005?

(a) Online Restore (b) Online Index Operations (c) Database snapshot (d) Fast recovery (e) Mirrored Backups (f) Database mirroring (g) Read committed snapshot and Snapshot isolation level (h) Data partitioning (i) Dynamic management views

7. What are services are created while installing SQL Server? (i)

There are three services are created in SQL Server 2005.

1. SQL server service:- It is responsible for running Databases, System Stored Procedures, System Indexes, User Login information and Passwords
2. SQL server agent service:- It is responsible for running Jobs, Alerts, Notifying the operators
3. Distributed Transaction Coordinator:- It is responsible for moving data between different physical servers (Clustering or Replication)

8. How many ways to run services?

The following methods can use to run services

Methods:-

1. At task pane > Right click on server and click start / stop
2. Management Studio > Right click on Server and click start / stop / restart
3. Surface Area Configuration
4. Command Prompt > Net start/stop mssqlserver
5. SQL Server configuration manager > Right click on server and click start / stop / restart.

9. What is Windows Authentication?

When Windows authentication is used to connect SQL Server, Microsoft Windows is completely responsible for authenticating the client by identifying its windows user account.

10. What is SQL Server Authentication?

When SQL Server authentication is used, SQL Server authenticates the client by comparing user name and password with user names and passwords maintained within SQL Server.

11. What is the difference between Windows Authentication and SQL Server authentication?

1. Windows authentication is highly secure than SQL Server authentication why because we need not provide any login details. So the chance of tracing login details from code by the unauthorized persons will be less. Windows authentication uses the default windows login credentials.
2. Windows authentication will uses the credentials of the currently logged user to login in to the SQL Server database. Your application need not to supply the user name and password.
3. For SQL Server authentication we have to supply the SQL database user name and password. That will be used to get the privileges and right of the user.

12. What is AWE? How configure Dynamic Memory Allocation? (i)

Generally SQL Server reserves only a small portion of memory. Sometimes reserved memory is not sufficient to SQL server and required additional memory. AWE is used to support very large amounts of physical memory. When AWE is enabled the operating system dynamically allocates memory to SQL Server based on the Min server memory andmax server memory settings. (1) Win. Server 2003 SE supports utpo 4GB (2) EE upto 32GB. (3) DC upto 64GB)

To Configure AWE:- Right click on instance > Properties > Memory > Enable AWE > Set Min and Max memory etc.

Note:- AWE is not needed and cannot be configured on 64-bit operating systems.

13. What is Linked Server? How to connect Linked server? How to test linked Server? (i)

One server connected with another server to execute queries against OLE DB data sources on remote servers.

OLEDB Provider:- An OLE DB provider is a DLL that manages and interacts with a specific data sources such as SQL Server 7.0, Access, Excel, ODBC, Oracle, DB2, Local file system, Exchange Server etc. SQL Native Client (PROGID: SQLNCLI) is the official OLE DB provider for SQL Server.

To connect linked server using OLE DB provider:-

sp_addlinkedserver @server=’servername’, @srvproduct=’SQL Server/Oracle’

Tests the connection to a linked server:-

sp_testlinkedserver <servername>

14. What is purpose of Registered Servers?

Registering a server to store the server connection information for future connections.

15. What do you mean by Collation?

 Collation is basically the sort order. There are three types of sort orders

(1) Dictionary case sensitive , (2) Dictionary case insensitive, (3) Binary

16. What are the protocols used in networking? What is the default port number of TCP/IP? (i)

The protocols used in networking are TCP/IP, NAMED PIPPES, VIA, SHARED MEMORY. The default port no of TCP/IP is 1433.

17. What is the Syntax used for find the SQL Server version?

1. Select @@version
2. Click on Server > Summary Reports > Server Dashboard > configuration details > See product version.

18. What is blocking? How to identify and resolve the blockings? ®

Blocking happens when one user holds an exclusive lock on an object and a second user requires an exclusive lock on the same object. This forces the second user to wait, block on the first.

Determine Blocking sessions:- Activity Monitor, sp_who2, sp_lock, sys.sysprocess, sys.dm_exec_requests, sys.dm_os_waiting_tasks

Resolve Blocking Session:- (1) Right click on session and Kill in Activity Monitor (2) Kill Session_id

19. What is Deadlock? ®

A deadlock occurs when users try to place exclusive locks on each other’s objects.

Ex:- User1 places an exclusive lock on Table1 and then tries to place an exclusive lock on Table2. User2 already has an exclusive lock on table2, and User2 tries to put an exclusive lock on Table1. This condition causes endless loop of waiting for the locks to be released.

The Database engine picks one of the victim (users) and kills their query and send a error message to users “You are the victim of a deadlock and try again later”.

Deadlock Information Tools

1. Trace Flags:- DBCC TRACEON (1204) & DBCC TRACEON (1222). When these trace flags is enabling, the deadlock information captured by the SQL Server error log.
2. Deadlock graph event in SQL Profiler:- SQL Server Profiler graphically representation of tasks and resources involved in a deadlock. (Lock:Deadlock and Lock:Deadlock chain events in the Locks events)
3. System View:- We can find the blocking sessions by writing the following query

Select session_id, status, blocking_session_id from sys.dm_exec_requests

where blocking_session_id > 0

Resolving Deadlock:- After find the session causing the problem we can use KILL command.

> KILL process_id

20. What are the Types of Locks? Explain each? (i)

There are 7 locks types are available in SQL Server 2005.

1. Shared Lock:- Shared locks allows concurrent transactions to read (SELECT) the same resource at the same time, but it does not allow any transaction to modify that resource.
2. Update Lock:- Only one transaction at a time can obtain an update lock on a resource. This lock helps avoid deadlocks for concurrent updates in the case when repeatable read or serializable isolation levels are used.
3. Exclusive Lock:- When a exclusive lock is held on a resource by a transaction, no other transaction can read or modify that resource. (Others may read the data without blocking on the exclusive lock if a locking hint, read uncommitted isolation level, or read committed snapshot isolation level).
4. Intent Lock:- Used to protect low level resource locks such as page and row locks that may be needed by a transaction.
5. Schema:- Used when a table DDL operation is performed and this lock is held, no users can access the table.
6. Bulk Update:- Used when bulk copying data into a table with TABLOCK hint is specified. This lock allows multiple threads to bulk copy data concurrently into the same table.
7. Key-Range:- This protects the rows so that the transaction can read repeatable data later in the transaction.

21. How can find the locks on a resource?

We can use sys_dm_tran_locks system view (sp_lock can use in previous versions)

Ex:- Select resource_type, resource_mode, request_status, request_session_id from sys.dm_tran_locks.

22. What is lock escalation?

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

23. What are the components can be installed in SQL Server 2005?

We can install the following components with Microsoft SQL Server 2005

1. SQL Server Database Engine
2. Analysis Services
3. Reporting Services
4. Notification Services
5. Integration Services
6. Management Tools
7. Documentation and Samples

24. What is the process of Installation?

1. Prepare Your Computer to Install SQL Server 2005:- To prepare your computer for SQL Server 2005, review hardware and software requirements, System Configuration Checker requirements and blocking issues, and security considerations.
2. Install SQL Server 2005:- To install SQL Server 2005, run Setup using the SQL Server 2005 Installation Wizard or install from the command prompt. You can also add components to an instance of SQL Server 2005, or upgrade to SQL Server 2005 from a previous SQL Server version.
3. Configure Your SQL Server 2005 Installation:- After Setup completes the installation of SQL Server 2005, you can configure SQL Server using graphical and command prompt utilities

25. What is the Virtual Memory? How to assign virtual memory & how much space required for Virtual memory?

A reserved disk space to maintain transactions whenever Memory (RAM) is full. Virtual memory size is at least 3 times of the physical memory installed in the computer.

To Set Virtual memory:- Right click on System > Select System Properties > Advanced > Performance Settings > Advanced > Virtual memory > Change > Select directory > Provide min and max values.

DATABASE

1. What is Database? What are the files created while creating a Database?

A Database is a collection of meaningful and related data that are stored in row and columns format (Tables). While creating a Database in SQL Server there are two data files are created called master data file (.mdf), log data file (.ldf) and we can add one or more optional N-Dimensional data files (.ndf).

2. Explain about Data files (.mdf, .ndf, .ldf) (File structure)?

1. Primary Data file (.ldf):- Primary data files holds user data and objects. Every database has one primary data file.
2. Secondary Data file (.ndf):- Secondary Data file (.ndf) is optional and hold user data and objects that do not fit in the Primary data file. We can add N no. of Secondary data files for a DB.
3. Log Data file (.ldf):- T.Log records all the transactions and database modifications made by each transaction and it is uses to recover the data in case of errors or system failures. At least one transaction log file required for each DB. Min. log file size is 512 KB.

3. How store the data in SQL Server? What is Page and Page size? What is Extent and Extent size? (i)

The fundamental unit of data storage in SQL server is the PAGE. The size of the page is 8 kb. Collection of eight contiguous pages is known as an EXTENT. The size of an extent is 64 kb (8x8).

4. What is Fill factor? How to assign Fill factor? (i)

A Fill factor is a reserved free space on each leaf level page which is used for future growth of data or index in a table and reduces the page splits.

Assign Fill Factor:- Right Click on Server > Properties > Database Settings > Default Index Fill Factor > Provide the value

5. Tell me about System Database and User Databases in 2000 and 2005?

SQL Server 2000:- 1) Master 2) Model 3) MSDB 4) TempDB

SQL Server 2005:- 1) Master 2) Model 3) MSDB 4) TempDB 5) Resource DB

6. Explain about System Databases in SQL Server 2005? (i)

There are five system databases available in SQL Server 2005.

1. Master Database:- Records Server level objects such as Login Accounts, End Points, Linked Servers, System Configuration settings, location of the database files and initialization information for SQL server. SQL Server Cannot starts if Master DB is unavailable.
2. Model Database:- The Model DB is used as a template for all databases created on instances of SQL Server. If we create a new Database the Model DB structure will be inherited to new Database.
3. MSDB Database:- The msdb database is used by SQL Server Agent for scheduling alerts and jobs and by other features such as Service Broker and Database Mail. SQL Server automatically maintain completed online backup and restore history in MSDB.
4. TempDB:- Temp DB is a global resource DB. It stores user objects (global or local temporary tables, stored procedures, table variables, or cursors), Internal objects (intermediate results for spools or sorting) and versions (online index operations, Multiple Active Result Sets (MARS), and AFTER triggers). We cannot backup the Temp DB because when SQL Server Start (or) Stop the Temp DB can be deleted permanently (or) refresh Temp DB.
5. Resource Database :- The Resource database is a hidden (read-only) database that contains all the system objects such as sys.objects that are included with SQL Server 2005. The Resource database makes upgrading process easier and faster.

7. If Model and MSDB Database will crash what you will do? How to rebuild Master Database? (i)

We can re-create Model and MSDB databases by rebuild the Master Database.

SQL Server 2000 (Rebuild MasterDB):- Start > Run > rebuildm.exe

SQL Server 2005 (Rebuild MasterDB):- Start > Run > setup.exe

8. How to move Model, MSDB, TempDB? (i)

1. Check the Path of TempDB through sp_HelpDB TempDB
2. ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, FILENAME = 'E:\tempdb.mdf');
3. Stop the Services
4. Move the files to desired location
5. Restart the Services
6. Check the Path of TempDB through sp_HelpDB TempDB

9. How to move Master Database? (i)
   1. Check the Path of Master DB through sp_HelpDB Master
   2. Stop the Services
   3. Change the path at Startup parameters

SQL Server Configuration manager > Services > Right click on Service > Properties > Advanced > Add new_path at Startup parameters

4. Move the files to desired location
5. Restart the Services
6. Check the Path of Master DB through sp_HelpDB Master

10. What are the Database States and explain them?

The main database states are online, offline, restoring, recovering, resource pending, suspect and emergency.

11. What is Database Snapshot? Give the Syntax? (i)

A Database snapshot is a read-only static view of the database. Snapshots must be located on the same server. Snapshots doesn’t contain un-committed transactions at the time of snapshot was taken. It is very useful for report queries. (This option is available in 2005 EE edition)

Ex:- CREATE DATABASE AdventureWorks_dbss1800 ON ( NAME = AdventureWorks_Data, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\ AdventureWorks_data_1800.ss') AS SNAPSHOT OF AdventureWorks;

12. What is Transaction? What is Transaction (ACID) Properties? (i)

Transaction is a set of logical unit of work and it contains one or more database operations. A valid transaction should be met (ACID) Atomicity, Consistency, Isolation, Durability properties.

1. Atomicity:- A transaction must be an atomic unit of work (either all data modification are performed or none of them is performed).
2. Consistency:- Consistency means before a transaction begins, the database must be in a consistent state.
3. Isolation:- Isolation means that the effects of each transaction are the same as if the transaction was only one in the system. (A transaction either sees a data in the state it was in before another concurrent transaction modified it, or sees the data after second transaction completed).
4. Durability:- Durability means that once a transaction is committed, the effects of the transactions remain permanently in the database, even in the event of system failure.

13. What are Transaction Isolation levels? (i)

An Isolation level affect the way locking behavior for read operations.

1. Read uncommitted:- Lowest level of isolation. Dirty reads are allowed because no shared locks are held for data reads.
2. Read committed:- Default level of isolation. At this level reads are allowed only on committed data.
3. Repeatable read:- Until a repeatable read transaction is completed, no other transactions can modify the data because all shared locks are held for the duration of the transaction.
4. Serializable:- Highest level of isolation. At this level the results achieved by running concurrent transactions on a database are the same as if the transactions had been run serially.
5. Snapshot isolation:-New for SQL 2005. This isolation level uses row versioning to provide read consistency for an entire transaction while avoiding blocking and preventing phantom reads.
6. Read committed snapshot (Database option):- New for SQL 2005. When this is option is on, row versioning is used to take a snapshot of data and provides data access with reduced blocking.

14. What is Dirty, Non-repeatable and Phantom Reads?
    1. Dirty read:- A read contain uncommitted data. A dirty read occurs when one transaction modifies data and second transaction reads the modified data before the first transaction has committed the changes.
    2. Non-repeatable read:- When one transaction reads a row, then a second transaction modifies the same row, and then first transaction reads that row again, getting different results.
    3. Phantom read:- A read that occurs when a transaction attempts to retrieve a row that does not exist, when the transaction begins, but that is inserted by a second transaction before first transaction finishes. If the first transaction again looks for the row, it will find that the row has suddenly appeared.

15. What is Transaction commit and Transaction commit modes?

A commit is an operation that conceptually saves all changes to the database made since the start of a transaction. A commit guarantees that all of the transaction’s modifications, first written to the buffer cache in memory, will be permanent in the database. When a transaction committed, it means any changed records are written to the log file and eventually also written to the data files. If the changes were not yet written to the data files at the point of the commit then the changes will be written to data files when checkpoint occurs. A commit also frees resources such as locks which are held by a transaction.

1. Autocommit mode:- This is default mode, SQL Server commits automatically when it finishes or is rolled back when it fails. (Explicit T-SQL statements are not required like COMMIT TRANSACTION)
2. Implicit or Explicit mode:- We can specify A transaction start with BEGIN TRANSACTION and end with COMMIT TRANSACTION.

16. What is Checkpoint and when it occurs?

A checkpoint is a SQL Server operation that synchronizes the physical data with the current state of the buffer cache by writing out all modified data pages in buffer cache to disk.

Checkpoint occurs in the following cases:-

1. Manual CHECKPOINT command
2. When SQL server shutdown
3. When ALTER DATABASE command is used to add or remove database file.
4. When recovery model change from Full/Buck-logged to simple.
5. Before a database backup is performed.
6. Checkpoints run periodically on Full/Bulk-logged recovery model databases as specified by the recovery interval server setting.
7. In simple recovery model log is truncated after checkpoints occurs

Assigning Checkpoint

CHECKPOINT 60; (60 is duration in seconds)

17. What is purpose of LSN numbers?

Every record in the transaction log is uniquely identified by a log sequence number (LSN). LSNs are used internally during a RESTORE sequence to track the point in time to which data has been restored.

18. Explain about RAID and RAID levels?

RAID stands for Redundant Array of Independent Disks. RAID is a disk system that contains arrays to provide greater performance, reliability, storage capacity, and lower cost. Typically 0, 1, and 5 RAID levels are used in SQL Server.

19. How to set a Database in a single user mode and Multi-user mode?

1. Single user mode:- Only admin can access the database (users cannot access)
   1. Alter database <database name> set single_user
   2. Sp_dbooptions, ‘database name’, ‘single user’, ‘true’
2. Multi-User mode:- All users can access the database

1. Alter database <database name> set multi_user
2. Sp_dbooptions, ‘database name’, ‘multi user’, ‘true’

20. How to set SQL Server in Single user mode and Minimal Mode?

1. Single user mode:- Only admin can access the server (users cannot access)

Start ->run -> command prompt > SQLSERVER.EXE –m

2. Minimal mode:- All users can access the Server

Start ->run -> command prompt > SQLSERVER.EXE –f

21. Orphaned Users? How to find Orphaned Users and Resolve them?

When a DB is move, attach & detach, restoring to different instance or corresponding login is dropped, the users of the Database becomes Orphaned users and they can’t access the database because the database users are not mapped to SID (Security Identifier) in the new instance.

To detect Orphaned users:- sp_change_users_login 'Report'

To Resolve Orphaned Users:- sp_change_users_login 'Update_One', 'db_user', 'login_name'

22. What is Row size of a Database?

Row size of a database is 8060 bytes.

23. What is Heap table?

A table without a clustered index is called as heap table.

24. What is Check point?

Check Point is a operations that synchronizes the physical data with the current state of the buffer cache by writing out all modified data pages in buffer cache to disk.

25. How many ways you can move the data or databases between servers and databases?

SSIS, DTS, Attach & Detach, Bulk copy program, Bulk Insert & Import and Export wizard

26. Syntax for find out the Database ID?

Select db_id (‘db_name’)

27. Syntax for find out Logins? Current User?

To find out logins: - select * from sys.syslogins

To find out current user:- Select current_user

28. Is it possible to take Backups and Restores of System Databases?

Except TempDB all the System database can take Bakups

Recovery Models

1. What is Recovery Model? Benefits of Recovery Models?

“A recovery model is a Database property that control the basic behavior of the backup and restore operations for a DB”. Recovery models are designed to control transaction log maintenance.

2. Explain Full, Bulk-Logged & Simple Recovery Models?

Based on the importance of the data, recovery models configured to the Database.

1. Full:- In Full Recovery Model we can recover the data up to point of failure, because every operation against database will be recorded in the transaction log.

1. Bulk-Logged:- Bulk-Logged recovery model is used to recover the data up to point of failure, but we will lost bulk-operations because those are not recorded in to the log. We will set this option just before performing bulk operations to speed up the bulk insert.

2. Simple:- With Simple Recovery Model we can recover the data only up to the last full/diff backup, because nothing is recorded in the transactional log. Any changes made to database after the last full/diff backup was performed will be lost because they are not recorded in the transaction log.

3. What are the differences between Full and Simple Recovery models?

Sl	Full	Simple
1	T. Logs are maintained and can recover the data up to the point of failure	T. Logs are not maintained and can recover the data up to recent full/diff backup.
2	Supported for Logshipping, Database mirroring and Replication	Not supported for Logshipping, Database mirroring and Replication
3	Maintenance will be huge	Maintenance will be less
4	Support for OLTP systems	Support for Data warehouses or not often changed databases

4. Which Databases can we use Simple Recovery Model?

Simple Recovery Model is useful for Development, Test databases, Data warehouses or not often changed Databases.

5. In which recovery models Point-in-time recovery is possible?

Point-in-Time Recovery is Possible only in Full and Bulk-Logged Recovery Models, but in Bulk-Logged Recovery model Point-in-time recovery may or may not possible.

6. What is the default recovery model for system databases?

Master - Simple

Model - Full

MSDB - Simple

TempDB - Simple

7. Why can’t take T.Log backups in simple recovery model?

In Simple Recovery Model the Transaction Logs are truncated.

8. How to set Recovery models using T_SQL?
   1. To Check current recovery model:- SELECT DATABASEPROPERTYEX('ADVENTUREWORKS', 'RECOVERY') As [Recovery Model]
   2. To set Simple Recovery model:- ALTER DATABASE ADVENTUREWORKS SET RECOVERY SIMPLE
   3. To set Bulk-Logged Recovery model ALTER DATABASE ADVENTUREWORKS SET RECOVERY BULK_LOGGED
   4. To set Full recovery model ALTER DATABASE ADVENTUREWORKS SET RECOVERY FULL
      

Backups

1. What is Backups? Types of backups? (i)

“A copy of data that is used to restore and recover the data after a system failure”. There are four types of Backups available (1) Full Backup (2) Differential Backup (3) Transaction Log Backup (4) File /File Group Backup

2. What is Use of Backups?

Backups is safe guard to Databases because Data may can loss due to many failures such as Media Failures, User errors, Hardware Failures and Natural Disasters etc. With good backups, we can recover database from failures.

3. What is Full, Diff, T.Log Bakups? Explain Each? (i)

1. Full Backup:- Backs up the entire Database including Transaction Log. With full Backup Point-in-time recovery is possible because it contains .mdf and .ldf. It offers complete protection against media failures.
2. Differential Backup:- Backs up only modified extents since the previous complete backup.
3. T.Log Backup:- Backs up the active portion and truncates the inactive portion of the transaction log. It can be used for recover the data up to the point of failure (or) can restore the database to a specific point-in-time.
4. File/File Group backup:- Backs up individual files and filegroups within a database.

4. Can we take Diff/T.Log backups without Full backups?

No, it is not possible. Full backup is a base backup for Diff/T.Log backups.

5. What are the Syntaxes for Backups? (i)
   1. Full Backup: - Backup database dbname to disk = “path”

(e.g. BACKUP DATABASE AdventureWorks TO DISK = 'g:\backups\AdventureWorks.bak')

2. Diff. Backup: - Backup database dbname to disk = “path” with differential

(e.g. BACKUP DATABASE AdventureWorks TO DISK = 'g:\backups\AdventureWorks.bak' WITH DIFFERENTIAL)

3. T.Log Backup:- Backup log dbname to disk = “path”

(e.g. BACKUP LOG AdventureWorks TO DISK = 'g:\backups\AdventureWorks.bak')

6. Explain about Tail of Log? Give the syntax? ®

The tail-log backup is the last backup that is used to recover the database to the point of failure (supports only Full or Bulk-logged recovery models).

(e.g. Assume that 4.00 P.M. log backup is performed and 5.00 P.M log backup has to be performed, in this case if the database crashed at 4.30 P.M, here we will loss 30 Min data, to recover this 30 Min of data we can use Tail log backup)

Syntax :-

1) If the database is online :-

Before starting a restore sequence, back up the tail of the log using WITH NORECOVERY whenever the next action you plan to perform on the database is a restore operation:

BACKUP LOG database_name TO <backup_device> WITH NORECOVERY

2) If the database is offline and does not start:-

Try to take a tail-log backup. Because no transactions can occur at this time, using WITH NORECOVERY is optional. If the database is damaged, use either WITH CONTINUE_AFTER_ERROR or WITH NO_TRUNCATE.

BACKUP LOG database_name TO <backup_device> [WITH { CONTINUE_AFTER_ ERROR | NO_TRUNCATE }

7. Backup strategy? ®

Generally Backups are done by client requirement. But Most of the companies are following this strategy.

1. Full Backup:- Every sunday nightly 10.00 PM
2. Differential Backup:- Every day nightly 10.00 PM
3. Transactional Log Backup:- Every 15/30 min

8. What are the backup devices are used and Backup retention period? (i)

There are two main backup devices are used. First backups are taken into Disk and then it will moves to Tape.

1) Disk (onsite backup):- Backup retention period is 2 weeks of backups

2) Tape (offsite backup):- Backup retention period is 4 weeks of backups

Note:- Backup retention period means how many days of backups are maintained in disk/tape (after retention period the existing old backups will be deleted)

9. What the main differences are between Disk and Tape?

Sl	Disk	Tape
1	It is fast	It is slow
2	Lives less life	Lives more life
3	Cost is more	Cost is less
4	Mainly used for online	Mainly used for restore

10. How SQL Server find the modified extents for taking Differential Backup?

All modifications are recorded in Differential Change Map (DCM) after full backup, SQL Server finds the modified extents and backups those extents.

11. What is the use of Copy-only Backup?

To take a backup without interrupting the current backup sequence number.

12. How to take a backup into multiple drives? (E.g. I want to take a backup which having Database size in 100 GB, but i have D: and E: drives having each 70 GB, how to take a backup for multiple drives) (s)

Backup database dbname to disk = “path1, path2”

(e.g. BACKUP DATABASE AdventureWorks TO DISK = 'D:\AdventureWorks.bak, E:\AdventureWorks.bak ')

Restore

1. What is Restore? What are the recovery states? Explain Each?

Restore means recover the data from backups. There are three recovery states for Restore. (1) With Recovery (2) With No-Recovery (3) Standby.

1. With Recovery:- With Recovery option the Database will comes to Online and get ready to using Database. But further backups cannot restored. Generally while restoring recent (last) T.Log we can choose WITH RECOVERY option.
2. With No-Recovery:- With No-Recovery option the Databases will comes to restoring mode and further backups can restore, but users can’t access DB. Generally use this option for all backups (except recent log) for restore further backups.
3. Standby:- With Standby option the Databases will comes to Restoring/Read-only mode. In this mode further (next) backups can restore, but users have read-only access DB. Generally use this option for all backups (except recent log) for restore further backups.

2. What are the common scenarios to Restoring the Data?

1. To restore the lost and corrupted data after a system failure
2. To restore a database onto a development system for user by application developers while developing and testing new code.
3. To restore a database onto a test system to load test applications and database features.
4. To restore a database on separate server as a read-only database to perform queries for reports.

3. In which edition On-line restoration is possible?

On-line restoration is possible only in Enterprise edition.

4. What are the Syntaxes for Restore?

1. With Recovery:-Restore database dbname from disk = “Path” with recovery
2. With No-Recovery:- Restore database dbname from disk = “Path” with no recovery
3. Standby:- Restore database dbname from disk = “Path” with no standby

5. What is Point-in-time restore and use of that?

Point-in-time restore is used to restore the Database to a particular time just before a failure has occurred or before corrupt data.

To Restore a Database to a Point-in-time:-

STOPAT is used to restore a database to specific time.

6. Restore Strategy?
   1. First restore recent last full backup
   2. Then restore last Diff. backup
   3. Then restore all Transaction Log backups since recent Full/Diff backups.

High Availability Solutions

Logshipping

1. What is Log shipping and purpose of Log shipping?

To achieve high availability and high protection, Log shipping copies transactions from a ‘primary server’ to one or more ‘secondary servers’. In Log shipping, T.Log backups are sent to one or more secondary servers and then restored to the destination servers individually. If the Primary database becomes unavailable, any of the secondary database can brought into online manually. The Secondary server acts as a Backup server and provides read-only query processing to reduce the load on the Primary server. (For query processing, secondary servers should be configure in stand-by mode).

2. What is Primary Server, Secondary Server & Monitor Server?

1. Primary Server:- Primary Server is a Production server which holds the original copy of the database. Log shipping configuration and administrating will be done from Primary Server.
2. Secondary Server:- Secondary servers hold the standby copy of the database. We must initialize the DB on a secondary server by restoring a backup from the Primary server using either NORECOVERY option or the STANDBY option. By using STANDBY option Users can have read-only access to it.
3. Monitor Server:- An optional Server is called as Monitor Server that records history and status of backup, copy and restore operations and raises alerts if any operations fail. The Monitor Sever should be on separate server to avoid losing critical information. Single Monitor Server monitors multiple Log shipping configurations.

3. What are the Jobs running for Log shipping and explain them?

Log shipping having four operations which are handled by SQL Server Agent Job.

1. Backup Job: - Backup job is created on Primary Server instance and it performs backup operation. It logs history on the local server and monitor severs and deletes old backup files and history information.
2. Copy Job: - Copy Job is created on Secondary server instance and it performs copies the backup files from primary sever to secondary server. It logs history on the secondary server and monitor server.
3. Restore Job: - Restore Job is created on the Secondary server instance and it performs restore operation. It logs history on the local server and monitor sever and deletes old files and history information.
4. Alert Job: - If a Monitor Server is used, the Alert Jobs is created on the Monitor server instance and it raises Alerts if any operations have not completed successfully.

4. Requirements for Log shipping?

1. SQL Server 2005 Standard Edition, Workgroup Edition, or Enterprise Edition must be installed on all server instances involved in log shipping.
2. All servers should have the same case sensitivity settings.
3. The databases must use the full recovery model or bulk-logged recovery model.

5. How to configure Log shipping?
   1. Choose Primary Server, Secondary Servers, and optional Monitor server.
   2. Create a File share to keep Transaction log backups (Best to place on a separate computer)
   3. Create a folder for each Secondary server into which transaction log backup copies.
   4. Choose Backup Schedule for Primary Database
   5. Choose Copy and Restore Schedules for Secondary Database
   6. Choose Alert Job schedule for Monitor Server if configured

6. What are permissions required for Log shipping?

We must have sysadmin on each server instance to configure Log shipping.

7. In Logshipping which Recovery Models can we used?

We can use either full or bulk logged recovery model for log shipping.

8. Where you monitoring Log shipping and how?

The following methods can use for monitoring Log shipping.

1. Monitor server (History Tables):- Monitor Server tracks all statistics, status and errors that could be happen during Log shipping.

1. Log_shipping_monitor_primary:- Stores primary server status
2. Log_shipping_monitor_secondary:- Stores secondary servers status
3. Log_shipping_monitor_history_detail:- Contains history details for logshipping agents.
4. Log_shipping_monitor_error_detail:- Stores error details for log shipping jobs.
5. Log_shipping_monitor_alert:- Stores Alert Job ID

1. System Stored Procedures (MSDB):- System Stored procedures gives the history information about the specified server that are configured in Log shipping.

1. sp_help_log_shipping_monitor (Run at Monitor Server)
2. sp_help_log_shipping_monitor_primary @Primary_Database = ‘DBName’ (Run at MS)
3. sp_help_log_shipping_monitor_secondary @ Secondary_Database = ‘DBName’ (Run at MS)
4. sp_help_log_shipping_alert_job (Run at Mon Server)
5. sp_help_log_shipping_primary_database @ Database = ‘DBName’ (Run at Primary Server)
6. sp_help_log_shipping_secondary_database @ Database = ‘DBName’ (Run at Sec Server)

3. Transaction Log shipping Status report (Summary Reports):- This report shows the status of log shipping configurations for which this server instance is a primary, secondary or monitor.
4. SQL Server Agent Job Histor:- Right click on Jobs > View history
5. Checking the SQL Server Log

9. How to failover secondary server, when the Primary Server fails?

If the Primary Server will become un-available, do the following steps.

1. Take the Tail of Log from Primary server if possible.
2. Restore Tail of log into all Secondary Database
3. Remove Log-shipping configuration from Primary Server
4. Select any one of Secondary server and bring into online with Alter Database DBName set Online
5. Right click on Primary Database and Generate script for Users and Logins.
6. Then move the script to Secondary server to create Users and Logins
7. Re-configure log shipping from New Server (Secondary server)

10. What are errors occurred in Log shipping?

There are two errors are occurred during Log shipping

1. 14420:- This error occurs when the Backup job fails
2. 14421:- This error occurs when the Restoring job fails

Mirroring

1. What is Database Mirroring? What are the benefits of that?

Database mirroring is an option to improve the availability of a databases which supports automatic failover with no loss of data. This is new feature in 2005.

Benefits:-

8. Increases data protection
9. Increases availability of a database
10. Improves the availability of the production database during upgrades

2. What are the prerequisites for Database Mirroring?

1. Both Servers are requires same edition either Standard Edition or Enterprise Edition.
2. If Witness server configured, the server should be installed Standard Edition, Enterprise Edition, Workgroup Edition, or Express Edition.
3. All Servers should use the same master code page and collation.
4. Mirror Server has same database name and use only With NoRecovery option.
5. Mirroring uses the full recovery model. (Simple and bulk-logged not supported)
6. All logins for connecting Principal Database should be reside on Mirror database

3. What are the Restrictions for Database Mirroring?

1. Maximum 10 databases per instance can support on a 32-bit system.
2. Database mirroring is not supported with either cross-database transactions or distributed transactions.

4. Explain about Principal, Mirror and Witness Servers?

1. Principal Server:- One Server serves the database to client is called Principal server and it having original data. Can have only one Principal Server and it has to be on a separate server.
2. Mirror Server:- Other server instance acts as a hot or warm standby server is called Mirror server and it having copy of database.
3. Witness Server:- The witness server is an optional server and it controls automatic failover to the mirror if the principal becomes unavailable. To support automatic failover, a database mirroring session must be configured in high-availability.

5. In which Operations are running the Operating Modes?

Asynchronous:- Under asynchronous operation, the Principal server does not wait for a response from the mirror server after sending the log buffer.

Synchronous:- Under synchronous operation, the Principal server sends the log buffer to the mirror server, and then waits for a response from the mirror server.

6. What are the Operating Modes and explain them?
   1. High Availability:- High-availability mode, runs synchronously. Requires a Witness Server instance. The Principal server sends the log buffer to the mirror server, and then waits for a response from the mirror server.
   2. High Protection:- High-protection mode, runs synchronously. Always commit changes at both the Principal and Mirror.
   3. High Performance:- High-performance mode, runs asynchronously and the transaction safety set to off. The Principal server does not wait for a response from the mirror server after sending the log buffer. The principal server running nice and fast, but could lose data on the mirror server.

7. What is End Point? How u create end point?

An endpoint is a network protocol which is used to communicate Principal, Mirror and Witness servers over the network.

Creation of an end point:-

Create endpoint <endpoint name> State=started/stopped/disabled

as tcp (listener port=5022/5023) for database_mirroring (role=partner/witness)

8. What is the default of end points (port numbers) of principal, mirror and witness servers? How to find the Port numbers?

The default port numbers of principal, mirror and Witness servers are 5022, 5023 and 5024.

To Find Port Number:- SELECT name, port FROM sys.tcp_endpoints

9. Which Trace flag is used in Mirroring?

Trace flags are used to temporarily set specific server characteristics or to switch off/on a particular behavior. 1400 Trace flag is used in mirroring.

To set trace flag for Database mirroring:- Configuration Manager > Right click on server instance > Properties > Advanced tab > Startup parameters > -t1400 (add)

10. In which Recovery model we can use in Mirroring?

In mirroring the principal and mirror databases are used only full recovery model

11. What is Role-switching?

Inter changing of roles like principal and mirror are called role switching.

12. What is the syntax to stop the Database Mirroring?

Alter database <database name> set partner off

13. How to configure Mirroring?

1. Choose Principal Server, Mirror Server, and optional Witness server.
2. The principal and mirror server instances must be running the same edition either Standard Edition or Enterprise Edition
3. The Witness server instance can run on SQL Server Standard Edition, Enterprise Edition, Workgroup Edition, or Express Edition
4. Mirror database requires restoring a recent backup and one or more T.log backups of the principal database (with Norecovery)

14. How to monitoring Mirroring?

There are six methods are available for monitoring the Database Mirroring

1. Database Mirroring Monitor:- Database Mirroring Monitor is a GUI tool that shows update status and to configure warning thresholds.

To open DM Monitor:- Right click on Principal Database > Tasks > Select Launch Database Mirroring Monitor.

1. SQL Server Management Studio:- A green arrow on the mirror server is indicates running well. A red arrow indicates problems that need to investigate.
2. SQL Server Log:- It provides information of Mirroring establishment and status. If any errors occurs it will be logged to SQL Server log and Windows event log.
3. Performance Monitor:- It can provides real-time information about Database mirroring. We can use performance counters to get status of the database mirroring such as Bytes received/sec, Bytes sent/sec, Transaction delay etc.
4. Profiler:- Profiler many events are providing the status of the Database mirroring
5. System Stored Procedures:-

• sp_dbmmonitoraddmonitoring
• sp_dbmmonitorchangemonitoring
• sp_dbmmonitorhelpmonitoring
• sp_dbmmonitordropmonitoring

15. What is Hardening?

As quickly as possible, the log buffer is written to the transaction log on disk, a process called hardening.

16. What is Log buffer?

A log buffer is a special location in memory (RAM). SQL Server stores the changes in the database’s log buffer.

17. How to Set a Witness Server to Database Mirroring?

SSMS:- Right Click on Principal Database > Tasks > Mirror > Click on Configure Security > Provide the End point for Witness server > Click oK

T-SQL:- ALTER DATABASE AdventureWorks SET WITNESS = 'TCP://prasad.local:5024' (Do this from the Principal Server)

18. How to Remove a Witness Server from Database Mirroring?

SSMS:- Right Click on Principal Database > Tasks > Mirror > Remove TCP address from the Witness > Click oK

T-SQL:- ALTER DATABASE AdventureWorks SET WITNESS OFF

19. How to Setup Fully Qualified Names for Database Mirroring?

I. FQDN Error

One or more of the server network addresses lacks a fully qualified domain name (FQDN). Specify the FQDN for each server, and click Start Mirroring again.

The syntax for a fully-qualified TCP address is:

TCP://<computer_name>.<domain_segment>[.<domain_segment>]:<port>

Section I.1

Section I.2II. RECTIFYING FULLY QUALIFYED NAMES

1. To View Endpoints:-SELECT * FROM sys.database_mirroring_endpoints;
2. Remove existing all Endpoints from Principal, Mirror and Witness servers :-DROP ENDPOINT [ENDPOINT_NAME]
3. Adding "local" as the primary DNS suffix as follows:-

1. Right-click My Computer, and then click Properties. The System Properties dialog box will appear.
2. Click the Computer Name tab.
3. Click Change. The Computer Name Changes dialog box will appear.
4. Click More. The DNS Suffix and NetBIOS Computer Name dialog box will appear.
5. Enter the appropriate DNS suffix for the domain.
6. Select the Change primary DNS suffix when domain membership changes check box.
7. Click OK to save the changes, and then click OK to exit the Computer Name Changes dialog box.
8. Click OK to close the System Properties dialog box, and then restart the computer for the change to take effect.

4. Reconfigure the Database mirroring either GUI or T-SQL

20. What are the Database Mirroring states?

1. SYNCHRONIZING:-

The contents of the mirror database are lagging behind the contents of the principal database. The principal server is sending log records to the mirror server, which is applying the changes to the mirror database to roll it forward.

At the start of a database mirroring session, the database is in the SYNCHRONIZING state. The principal server is serving the database, and the mirror is trying to catch up.

2. SYNCHRONIZED:-

When the mirror server becomes sufficiently caught up to the principal server, the mirroring state changes to SYNCHRONIZED. The database remains in this state as long as the principal server continues to send changes to the mirror server and the mirror server continues to apply changes to the mirror database.

If transaction safety is set to FULL, automatic failover and manual failover are both supported in the SYNCHRONIZED state, there is no data loss after a failover.

If transaction safety is off, some data loss is always possible, even in the SYNCHRONIZED state.

3. SUSPENDED:-

The mirror copy of the database is not available. The principal database is running without sending any logs to the mirror server, a condition known as running exposed. This is the state after a failover.

A session can also become SUSPENDED as a result of redo errors or if the administrator pauses the session

SUSPENDED is a persistent state that survives partner shutdowns and startups.

4. PENDING_FAILOVER:-

This state is found only on the principal server after a failover has begun, but the server has not transitioned into the mirror role.

When the failover is initiated, the principal database goes into the PENDING_FAILOVER state, quickly terminates any user connections, and takes over the mirror role soon thereafter.

5. DISCONNECTED:-

The partner has lost communication with the other partner

Replication

1. What is Replication?

“Replication is the process of copying and distributing data between databases to different servers throughout the enterprise”.

Replication is a set of technologies for copying and distributing data and database objects from one database to another and then synchronizing between databases to maintain consistency.

2. What are the uses of Replication?

1. Server to Server Replication:-

1. Improving scalability and availability
2. Data warehousing and reporting
3. Integrating data from multiple sites
4. Integrating heterogeneous data
5. Offloading batch processing

2. Server to Client Replication:-

1. Exchanging data with mobile users
2. Retail point of sale (POS) applications
3. Integrating data from multiple sites

3. Types of Replication and explain each?

1. Snapshot replication:- Snapshot replication takes a picture or a snapshot of the database and propagated to the subscribers. It reduces the overhead on the Publishers and Subscribers because it does not monitor data updates. Snapshot replication is very useful, when the source data is changes occasionally (Reporting).
2. Transactional replication:- Transactional Replication starts with a snapshot of the publisher database. With Transactional Replication, any changes made to the articles are captured from the transactional log and propagated to the distributors continuously and automatically. Using Transactional Replication we can keep the publisher and subscriber in almost exactly the same state.
3. Merge replication:- Merge Replication starts with a snapshot of the publisher database. Subsequent data changes and schema modifications made at the Publisher and Subscribers are tracked with triggers. The Subscriber synchronizes with the Publisher when connected to the network and exchanges all rows that have changed between the Publisher and Subscriber since the last time synchronization occurred.

4. Explain about Publisher, Subscriber and Distributer?

1. Publisher:- The Publisher is a database that makes data available for replication. The Publisher can have one or more publications.
2. Distributor:- The distributor is the intermediary between the publisher and subscriber. It receives published transactions or snapshots and then stores and forwards these publications to the subscribers.
3. Subscribers:- Subscribers are database servers that store the replicated data and receive updates. A subscriber can receive data from multiple publishers. Based on the replication type, the Subscriber can also pass data changes back to the Publisher or republish the data to other Subscribers.

5. Explain about Article, Publication, Subscription?

1. Article:- An Article is the data, transactions, or stored procedures that are stored within a publication. This is the actual information that is going to be replicated.
2. Publication:- The publication is the storage container for different articles. A subscriber can subscribe to an individual article or an entire publication.
3. Subscription:-Subscription is a request by the subscriber to receive the publication.

6. Which recovery models are used for Replication?

Full and Bulk-logged Recovery models

7. How to monitor the Replication?

The following methods can use for monitor the Replication

Methods:-

1. Replication Monitor:- Replication Monitor is a GUI tool provides detailed information on the status and performance of publications and subscriptions. By using replication monitor we can find out (1) which subscriptions are slow (2) Why is an agent not running (3) Time taken to transaction commit (4) Why merge replication is slow (5) How far behind subscription etc.

To launch Replication Monitor:- Connect Instance > Right Click on Replication folder > Select launch Replication monitor

2. Management Studio:- By using management Studio we can see View Snapshot Agent Status, View Log Reader Agent Status, View Synchronization Status etc.

Through SSMS:- Connect Instance > Right Click on Publication > Select the option

3. System Monitor:- Provides information on the performance of various processes of Replication.

Agent	Performance object	Counter
All agents	Replication Agents	Running
Snapshot Agent	Replication Snapshot	Snapshot: Delivered Cmds/sec
Snapshot Agent	Replication Snapshot	Snapshot: Delivered Trans/sec
Log Reader Agent	Replication Logreader	Logreader: Delivered Cmds/sec
Log Reader Agent	Replication Logreader	Logreader: Delivered Trans/sec
Log Reader Agent	Replication Logreader	Logreader: Delivery Latency
Distribution Agent	Replication Dist.	Dist: Delivered Cmds/sec
Distribution Agent	Replication Dist.	Dist: Delivered Trans/sec
Distribution Agent	Replication Dist.	Dist: Delivery Latency
Merge Agent	Replication Merge	Conflicts/sec
Merge Agent	Replication Merge	Downloaded Changes/sec
Merge Agent	Replication Merge	Uploaded Changes/sec

8. What are the Agents available for Replication and explain each?

SQL Server Agent hosts and schedules the agents used in replication and also controls and monitors operations outside of replication.

2. Snapshot Agent:- This Agent generates a snapshot (schema and data files) of the articles in the Publisher database and transfers it to the snapshot folder. This agent is typically used for all types of replication and runs in the Distributor server.
3. Log Reader Agent:- Log Reader Agent monitors publisher database transaction Log and copies each transaction from publisher to distribution database. This agent is used for transactional replication and runs in the Publisher server.
4. Distribution Agent:- This agent performs two tasks, transfers the snapshot to the Subscribers and applies the changes (transactions) to Subscription database. This agent is used for snapshot and transactional replications. This agent runs in Distributor server when configured as Push subscription and runs in Subscriber server when configured as Pull subscription.
5. Merge Agent:- This agent delivers initial snapshot from distributor to subscribers, and it also merges data changes that occur in publisher to the subscribers, and vice versa. The Merge Agent is used for merge replication and runs in Distributor server when configured as Push subscription and runs in Subscriber server when configured as Pull subscription.
6. Queue Reader Agent:- The Queue Reader Agent is used for transactional replication with the queued updating option. It runs on the Distributor and is responsible for reading messages from the queue on the subscribers and applying them to the appropriate publication.

9. What are the Agents used for Transactional Replication?

1. Snapshot Agent
2. Log Reader Agent
3. Distribution Agent

10. What are the Agents used for Merge Replication?

1. Snapshot Agent
2. Merge Agent

11. What is the Process of Transactional Replication?

Three Agents are doing the Process for Transactional Replication

MANAGEMENT

1. What is Maintenance Plan?

Maintenance plans create a workflow for database optimization, and make free from inconsistencies.

2. What the tasks in Maintenance Plans?

3. SQL Server Logs?

4. Database Mail?

Database Mail is an enterprise solution for sending e-mail messages from the Microsoft SQL Server 2005 Database Engine. Using Database Mail, your database applications can send e-mail messages to users. The messages can contain query results, and can also include files from any resource on your network. Database Mail is designed for reliability, scalability, security, and supportability.

5. Full Text Search?

PERFORMANCE TUNING

1. Which Tools are used for Performance Tuning?

There are many tools are used for Performance tuning

1. Windows tools for monitoring applications:- Performance monitor, Performance Counters and Logs, Task manager, Network manager
2. SQL Server tools for monitoring components:- SQL trace, SQL Profiler, DMVs, System Stored procedures, Graphical show plan, Activity Monitor, DBCC, Built-in Functions, Trace flags

2. How to identify longest running queries?

There are two ways to identify slow running queries

1. Profiler (By using duration of the query)
2. sys.dm_exec_query_stats and sys.dm_exec_requests DMVs
3. DBCC OPENTRAN

3. Reasons for Slow Running Query?

There are a number of common reasons for slow-running queries

1. Lack of useful indexes, Lack of useful data striping (RAID).
2. Blockings, Table scans, Lack of useful partitioning
3. Missing or out of date statistics (update statistics)
4. Slow network communication.
5. Insufficient memory available for SQL Server.
6. Insufficient disk space.
7. Excess recompilations of Stored Procedures
8. Procedures and Triggers without SET NOCOUNT On

4. How to analyze query performance?

We can analyze query performance in three ways

1. T-SQL:- SET SHOWPLAN_ALL ON/OFF, SET SHOWPLAN_TEXT ON/OFF
2. SSMS:- Estimated Execution Plan & Actual Execution plan
3. Profiler:- To display text and XML execution plans, see Displaying Execution Plans by Using SQL Server Profiler Event Classes

5. How to increase Query performance?

We can improve query performance in the following ways

1. Add indexes if required
2. Run Update statistics for out of date statistics
3. Resolving Blocking issues
4. Add space to DB files or TempDB, if that are not having enough space
5. Reduce the too much normalization
6. Using Temporary tables instead of Cursors
7. SPs and Triggers are with Set NOCOUNT On
8. Unnecessarily complicated joins

6. Explain about Profiler? What are the Uses of Profiler?

SQL Profiler can capture SQL Server events from the server to analyze or troubleshoot performance problems such as Finding Slow-running queries and Monitoring performance etc. Profiler is useful for maintaining security, troubleshooting, monitoring and optimization.

Uses of SQL Profiler:-

1. Find the worst-performing queries.
2. Identify the cause of a deadlock.
3. Monitor stored procedure performance
4. Audit SQL Server activity.
5. Monitoring T-SQL activity per user.
6. Collect a representative sample of events for stress testing.
7. Collect a sample of events for tuning the physical database design by using Database Engine Tuning Advisor.

7. What are the Events is captured SQL Profiler?

We can capture the events such as

1. T-SQL Statements, Stored Procedures
2. Cursors, Locks (deadlocks)
3. Databases objects and auto growth of size of data & log files
4. Errors & warnings (syntax errors)
5. Performance (show plan)
6. Table Scans
7. Security audits (failed logins, password changes)
8. Monitor server control, memory changes (CPU, Reads, Writes)
9. Sessions, Transactions, Tuning

8. Explain about Database Tuning Advisor? What is Workload?

Database Tuning Advisor can analyze the performance effects of workloads run against one or more Databases or a SQL Profiler trace (they may contain T-SQL batch or remote procedure call). After analyzing, it recommends to add, remove or modify physical design structures such as clustered and non-clustered indexes, indexed views and partitioning.

Workload:- A workload is a set of Transact-SQL statements that executes against databases you want to tune

9. What is Dynamic Management Views?

DMVs return server state information that we can use to monitor the health of a server instance, diagnose problems, and tune performance. There are two types of DMVs:

1. Server-scoped DMVs:- Require the VIEW SERVER STATE permission on the server.
2. Database-scoped DMVs:- Require the VIEW DATABASE STATE permission on the database.

10. What is DAC? How to connect DAC?

DAC is stands for Dedicated Administrator Connection. This diagnostic connection allows an administrator to access running instance to troubleshoot problems or execute diagnostic queries on the server - even when SQL Server is not responding to standard connection requests. This connection uses 1434 port and can connect only one connection per instance.

To connect DAC:- (Methods)

1. SQLCMD:- -Sadmin:<instance_name>
2. GUI:- SSMS Query Editor by connecting to ADMIN:<instance_name>

11. Explain about Database Console Commands (DBCC)?

DBCC Commands are used to check the consistency of the Databases or Database Objects. While executing DBCC commands the DB engine creates a database snapshot and then runs the checks against this snapshot. After the DBCC command is completed, this snapshot is dropped.

12. What is Stored Procure? What are the types of stored Procedures available in SQL server and explain each?

A stored procedure is a precompiled executable object that contains one or more Transact-SQL statements.

1. User Defined Stored Procedure:- Stored procedures are modules or routines that encapsulate code for reuse. A stored procedure can take input parameters, return tabular or scalar results and messages to the client
2. System Stored Procedure:- System stored procedures are used to perform many administrative and informational activities.
3. Extended stored procedure:- Extended stored procedures are used to create own external routines in a programming language such as C. Extended stored procedures are DLLs that an instance of Microsoft SQL Server can dynamically load and run.

13. What is Activity Monitor and use of that? What are the permissions required to use Activity Monitor?

Activity Monitor is used to get information about users connections to the Database Engine and the locks that they hold. Activity Monitor is used to troubleshooting database locking issues, and to terminate a deadlocked or unresponsive process.

To use activity monitor:- VIEW SERVER STATE permission on Server and SELECT permission to thesysprocesses & syslocks tables in the master database.

To Kill a Process:- sysadmin and processadmin database roles and permission are required to KILL a process.

14. What is Execution Plan and explain it?

Execution Plan graphically displays the data retrieval methods chosen by SQL Server. It represents the execution cost of specific statements and queries in SQL Server. This graphical approach is very useful for understanding the performance of the query.

21. What is Trace flag? Give some Trace flags?

Trace flags are used to temporarily set specific server characteristics or to switch off/on a particular behavior. There are two types of trace flags: session and global. Session trace flags are active for a connection and are visible only to that connection. Global trace flags are set at the server level and are visible to every connection on the server. Some flags can only be enabled as global, and some can be enabled at either global or session scope.

(1) 260 (2) 1204 (3) 1211 (4) 1222 (5) 1224 (6) 2528 (7) 3205 (8) 3625 (9) 4616 (10) 7806 (11) 1400

To set on/off Traceflag:- (1) DBCC TRACEON (2) DBCC TRACEOFF

To Enable Trace flag globally :- DBCC TRACEON with the -1 argument (Ex:- DBCC TRACEON 2528, -1)

-T startup option:- Indicates that an instance of SQL Server should be started with a specified trace flag (trace#) in effect.

To Determine trace Flags are currently active:- DBCC TRACESTATUS

15. What are the common failures occur in SQL Server 2005?

There are three common failures occur in SQL Server 2005.

1. Database Failures
2. Physical Server Failures
3. SQL Server Service failures

16. What are the causes of Database Failures?

There are three common issues will causes Database failures. Log File viewer is very useful to diagnose these problems that will occur in SQL Server 2005.

1. Database has run out of Disk space:-

1. If a Database is online, and running out of disk space the data cannot be inserted into the database.
2. If the Database during recovery, and the data file becomes full the Database engine marks the Database as “Resource Pending”.

2. T. Log is full:-

1. If the Database is Online, and the T.Log becomes full the Database Engine issues 9002 error and it is in read-only state and will not allow updates.
2. If the Database during recovery, and the T.Log becomes full the Database engine marks the Database as “Resource Pending”.

3. TempDB has run out of Disk space:- TempDB stores User objects, Internal Objects and Version stores. If the TempDB database runs out of space, it causes significant problems for SQL Server 2005. The errors are written to SQL Server log and these errors (1101, 1105, 3959, 3967, 3958, 3966) indicates TempDB has insufficient space.

17. How to understand Database Engine Errors?

If any error occurs, the server writes error messages to logs (Database mail, SQL Agent, SQL Server & Windows NT). These logs having Error number, Error message, Severity, State, Procedure name, Line number. We can easy to understand errors by viewing Log file viewer.

18. What is Severity level in Event log? At what severity levels are written to the SQL Server log?

The Severity level tells how bad the error is. Above 19 Severity level errors are written to the SQL Server log.

19. What is State Attribute of an error message in Event log?

The state attributes provides details of what caused the error.

20. What are the Causes of Physical Server Failures?

There are five common issues will causes Physical Server failures.

1. Disk failures
2. Memory failures
3. Processor failures
4. Network card failures
5. RAID failures

21. If Log file is full what you will do? (i)

If the T.Log file is full the Database issues 9002 error and it is in Read-only state and will not allow updates. The following methods can use

The following tasks will responding T.Log file

1. Backing up the log
2. Adding one or more log files
3. Moving the Log to another disk
4. Increasing log file size or enabling auto growth
5. Teminating long-running transactins.

22. If Data file is full what you will do?

If the primary data file is full we can add secondary data files.

Adding a file to database: - Right click on database > Properties > Files > click add > give the values for logical name, file type, file group, initial size, auto growth, path and file name .

23. If a Database is under Suspect mode? What you will do?

sp_resetstatus:- Resets the status of a suspect database (sp_resetstatus ‘Adventureworks’)

24. If the server has completely failed, and how to all the backups restored onto a new server?

1. Build the Windows server and restore the domain logins to support Windows authentication.
2. Install SQL Server and any service-pack upgrades.
3. Put SQL Server in single-user mode and restore the master database.
4. Restore the msdb database.
5. If the model database was modified, restore it.
6. Restore the user databases.

25. How to Recover Crashed SQL Server with existing data files and logs?
    1. Before installation of SQL Server take the backup of existing Datafiles and Transaction logs.
    2. Rename the data files and transaction logs for all system databases master, model, msdb and user databases to file_name_OLD.mdf and file_name_OLD.ldf.
    3. Install the SQL Server.
    4. Stop the services for SQL Server.
    5. Rename the New datafiles and Transaction logs for all system databases to file_name_NEW.mdf andfile_name_NEW.ldf.
    6. Rename the Old datafiles and transaction logs for all system databases to Original name file_name.mdf and file_name.ldf.
    7. Start the services for SQL Server.
    8. Check all user and system Databases exists on the server and nothing is offline.
    9. Verify the SQL server and windows logins.

26. If a Server performance is slow? How you can troubles shoot a problem?
27. If a Database Performance is slow? How can you do Performance Tuning?
28. What are the Performance Issues? Explain them?

MISCELLANEOUS

1. What are the Differences between Delete & Truncate?

Delete:- Delete command removes the rows in a table and records in transaction log which makes it slow. Rollback is possible.

Truncate:- Truncate command removes all the rows in a table, but it won’t write log which makes it faster. Rollback is not possible.

2. What is an Index? Types of Indexes? How many clustered indexes and non-clustered indexes created on a table?

Generally SQL Server examines (table scan) every row in the table to satisfy the query results. “Index is a physical structure containing pointers to the data which is used for retrieving data more quickly and improves the query performance”.

Clustered Index:- Only one Clustered index can create on a Table. When we create a clustered index on a table, all the rows in the table are stored in the order of the clustered index key.

Non-clustered index:- 249 Non-Clustered indexes can create on a Table. Non-clustered indexes are stored as B-Tree structures with the leaf level nodes having the index key and it’s row locator.

3. How can we rebuild an Index?

By using DBCC DBREINDEX

4. What's the difference between a primary key and a unique key?

Both primary key and unique key enforces uniqueness of the column on which they are defined.

Sl	Primary Key	Unique Key
1	Primary key creates a clustered index	Unique key creates a non-clustered index
2	Primary Key doesn’t allow Nulls	Unique key allows one Null only

5. What is Stored Procedure?

A stored procedure is a pre-compiled SQL statements that are stored in Sever database to performing a task. 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.

Advantages:-

1. Stored procedure can reduce network traffic because queries executed as a batch.
2. Improving the performance because the SPs are pre-compiled.
3. Stored procedures provide better security to your data
4. Easily modify the SPs when business rules change.

6. What is User Defined Function? Types of UDFs?

User-Defined Functions that allow you to define your own T-SQL functions that can accept zero or more parameters and return a single scalar data value or a table data type. There are three types of UDFs are available in SQL Server 2005.

1. Scalar Valued UDFs:- A scalar-valued UDF accepts parameters and returns a single scalar data types (Text, ntext, image).
2. Table Valued UDFs:- A table-valued UDF is a function that accepts parameters and returns the results in the form of a table.

7. What are the differences between Stored Procedure and User Defined Function?

Sl	User Defined Functions	Stored Procedures
1	Functions are compiled and executed at run time.	Stored procedures are stored in parsed and compiled format in the database.
2	Functions cannot affect the state of the database which means we cannot perform insert,delete,update and create operations on the database.	Stored Procedures can affect the state of the database by using insert, delete, update and create operations.
3	Functions are basically used to compute values. We passes some parameters to functions as input and then it performs some operations on the parameter and return output.	Stored procedures are basically used to process the task.
4	Function can not change server environment and our operating system environment.	Stored procedures can change server environment and our operating system environment
5	Functions can not be invoked from SQL Statements. Execute. SELECT	operating system can be invoked from SQL Statements. Execute. SELECT
6	Functions can run an executable file from SQL SELECT or an action query.	operating system use Execute or Exec to run

8. What is a Trigger? What are the trigger models are available in SQL Server 2005?

A trigger is a set of T-SQL Statements stored permanently in the Database and automatically fired when an event occurs. They are used to impose user defined restrictions or business rules and also provides high security on Database/ tables.

There are two types of trigger models are available in SQL Server 2005

DML Triggers:- DML triggers created on DML statements like Insert, Update and Delete of Database objects

DDL Triggers:- DDL triggers created on DDL statements like Create, Alter and Drop of DB objects or DB.

9. Types of Triggers?

There are two types of triggers

6. After Triggers:- After Triggers will fires after the data is inserted into the table. The typical use for an After trigger is to log the action to an Audit or logging table
7. Before Triggers:- Before triggers will fire before the data is inserted into the table.

Note:- DML triggers fires before or after issue, DDL triggers fires after the issue.

10. What is a View and advantages of Views?

A view is a pre-defined SQL Statements that the DB engine executes and retrieves dynamically. A standard view acts as a virtual table based on the result set of a select statement. The fields in a view are fields from one or more real tables or another views.

Advantages:-

1. To restrict the users to specific rows & columns and hide the columns with sensitive data
2. User friendly names can be provided instead of base table column names
3. Frequently used complex Joins, Queries and Unions can be coded once as a View and View can be referenced for simplicity and consistency of coding.
4. Aggregate information such as Sums, Counts, Avg and so on can be calculated in a view to simplify coding when those aggregation information will be referenced multiple times.
5. Views can be used for Data security, allowing users access certain data without granted permissions to directly access the base tables.

11. Types of Views? Explain about Indexed View?

There are two types of Views 1) Standard View 2) Indexed Views.

An indexed View (materialized view) is like a standard view, and it has a unique clustered index created on it. It stored physically just like a table.

Advantage:- Indexed views works best for queries that aggregates many rows or have joins that cause response time of the standard view to be slow.

12. What is the difference between a Local and a Global temporary table?

Local temporary table:- It exists only for the duration of a connection or, if defined inside a compound statement, for the duration of the compound statement.

Global temporary table:- remains in the database permanently, but the rows exist only within a given connection. When connection is closed, the data in the global temporary table disappears. However, the table definition remains with the database for access when database is opened next time.

13. What is PRIMARY KEY?

A PRIMARY KEY constraint is a unique identifier for a row within a database table. Every table should have a primary key constraint to uniquely identify each row and only one primary key constraint can be created for each table. The primary key constraints are used to enforce entity integrity.

14. What is UNIQUE KEY constraint?

A UNIQUE constraint enforces the uniqueness of the values in a set of columns, so no duplicate values are entered. The unique key constraints are used to enforce entity integrity as the primary key constraints.

15. What is FOREIGN KEY?

A FOREIGN KEY constraint prevents any actions that would destroy links between tables with the corresponding data values. A foreign key in one table points to a primary key in another table. Foreign keys prevent actions that would leave rows with foreign key values when there are no primary keys with that value. The foreign key constraints are used to enforce referential integrity.

16. What is CHECK Constraint?

A CHECK constraint is used to limit the values that can be placed in a column. The check constraints are used to enforce domain integrity.

17. What is NOT NULL Constraint?

A NOT NULL constraint enforces that the column will not accept null values. The not null constraints are used to enforce domain integrity, as the check constraints.

Question #1 –  How will you identify duplicate records in below SQL table?

How will you identify duplicate records in a SQL table?

Answer #1 –  Solution to identify duplicate records in a SQL table

Solution to identify duplicate records in a SQL table

SQL scenario based Interview Question – Try SQL code Yourself
Question –
DECLARE @Employee as table
(
Eidint,
Enamevarchar(50)
)
insert into @Employee values
(1,’john’),
(2,’james’),
(2,’james’),
(3,’krish’),
(4,’robert’),
(4,’robert’),
(5,’clave’)
Select * from @Employee
Answer –
SELECT Eid, Ename, COUNT(Eid) AS NumOccurrences
FROM @Employee
GROUP BY Eid,Ename
HAVING (COUNT(Eid)>1)

Question #2 –  How to get distinct values from a SQL table without using DISTINCT keyword?

How to get distinct values from a SQL table without using DISTINCT keyword?

Answer #2 –  Solution to get distinct values from a SQL table without using DISTINCT keyword

Solution to get distinct values from a SQL table without using DISTINCT keyword

SQL scenario based Interview Question – Try SQL code Yourself
Answer –
selecteid,ename from emp
group by eid,ename

Question #3 –  How to get alternate rows from a SQL table?

How to get alternate rows from a SQL table?

Answer #3 A –  1st Solution to get  alternate rows from a table in SQL – using Row Number

1st Solution to get alternate rows from a SQL table – using Row Number

SQL scenario based Interview Question – Try SQL code Yourself
Answer –
select row_number() over(order by eid)as rowid,* into #temptab FROM dbo.Emp
select * from #temptab where (rowid%2)<>0

Answer #3 B –  2nd Solution to get  alternate rows from a table in SQL – without using Row Number

2nd Solution to get alternate rows from a SQL table – without using Row Number

In this article I have tried to cover all important questions generally asked in the interview. Please give your valuable suggestions and feedback to improve this article.I will write the answers for each question in future. 

1. What are the different types of Logical Operator in SQL Server?
2. What is Implicit and Explicit data conversion in SQL Server?
3. What are correlated sub queries?
4. What are Schemabinding views?
5. What is the use of BREAK and CONTINUE keywords in While Loop?
6. How to declare variable, assign value to a variable in SQL Server?
7. What are the different steps to use Cursor?
8. What is the difference between DDL and DML Triggers?
9. What is a Trigger?
10. What are the different types of Trigger?
11. What is a view?
12. What are the different types of View?
13. What is an Index?
14. What are the different types of Index?
15. What is a linked Server?
16. What is a Cursor?
17. What is a Sub Query? Explain the properties of sub Query?
18. What are the different types of Join?
19. What is user defined Function?
20. What is the difference between Stored Procedure and User defined function?
21. What is an Identity column?
22. What is the correct order of Query Process Phases?
23. What are catalog views?
24. What are Pessimistic and optimistic Locks?
25. What are the different types of locks?
26. What is the difference between an Update and Exclusive lock?
27. What is NOLOCK hint?
28. What is collation?
29. What is the difference between INSTEAD OF TRIGGER and AFTER Trigger?
30. What are the different types of SQL Commands?
31. Which TCP/IP port does SQL Server run on? How can it be changed?
32. What are the difference between clustered and a non-clustered index?
33. What are the different index configurations a table can have?
34. What are different types of Collation Sensitivity?
35. What is OLTP (Online Transaction Processing)?
36. What's the difference between a primary key and a unique key?
37. What is difference between DELETE and TRUNCATE commands?
38. When is the use of UPDATE_STATISTICS command?
39. What is the difference between a HAVING CLAUSE and a WHERE CLAUSE?
40. What are different Types of Sub-Queries?
41. What is SQL Profiler?
42. What are the authentication modes in SQL Server? How can it be changed?
43. Which command using Query Analyzer will give you the version of SQL server and operating system?
44. What is SQL Server Agent?
45. Can a stored procedure call itself or recursive stored procedure? How much level SP nesting is possible?
46. What is Log Shipping?
47. Name 3 ways to get an accurate count of the number of records in a table?
48. What does it mean to have QUOTED_IDENTIFIER ON? What are the implications of having it OFF?
49. What is the difference between a Local and a Global temporary table?
50. What is the STUFF function and how does it differ from the REPLACE function?
51. What is PRIMARY KEY?
52. What is UNIQUE KEY constraint?
53. What is FOREIGN KEY?
54. What is CHECK Constraint?
55. What is NOT NULL Constraint?
56. How to get @@ERROR and @@ROWCOUNT at the same time?
57. What is a Scheduled Jobs or what is a Scheduled Tasks?
58. What are the advantages of using Stored Procedures?
59. What is a table called, if it has neither Cluster nor Non-cluster Index? What is it used for?
60. Can SQL Servers linked to other servers like Oracle?
61. What is BCP? When does it used?
62. How to implement one-to-one, one-to-many and many-to-many relationships while designing tables?
63. What is an execution plan? When would you use it? How would you view the execution plan?
64. What is RDBMS?
65. What are the properties of the relational tables?
66. What is Normalization?
67. What is De-Normalization?
68. How is the ACID property related to the database?
69. What are the different normalization forms?
70. What is a Stored Procedure?
71. What are the different types of collation sensitivity?
72. What is dirty read?
73. How do you check collation and compatibility level for a database?
74. What is the difference between HAVING and WHERE clause?
75. What is a B-Tree?
76. What are the different Index configurations a table can have?
77. What is a Filtered Index?
78. What are indexed views?
79. What is the restriction on Indexed view?
80. What are Statistics? How can you find it in SQL Server?
81. What is blocking?
82. What is a deadlock?
83. How do you identify and resolve deadlock?
84. What is the maximum row size for a table?
85. What are Spare columns?
86. What is the maximum number of columns a table can have?
87. What are included columns?
88. What is INTERSECT operation?
89. What is EXCEPT operation?
90. What are GROUPING Sets?
91. What are the row constructors inside SQL Server?
92. What are table valued parameters?
93. What is RAID?
94. What is PIVOT and UN-PIVOT?
95. What are the advantages of using Stored Procedure?
96. What is the difference between COMMIT and ROLLBACK?
97. What is Transaction?
98. What is the difference between CHAR and VARCHAR?
99. What is the difference between VARCHAR and VARCHAR (max)?
100. What is SQL INJECTION?
101. How do you find list of schema names and table names from the database?
102. Why can be there only one clustered index?
103. What is HINT?
104. How do you delete duplicate records?
105. How do you delete all tables?
106. What is Aggregate function?
107. What is the difference between Index Seek and Index Scan?
108. Can we insert data if the clustered index is disabled?
109. How to recompile a stored procedure at run time?
110. Does the order of columns in UPDATE statement matters?
111. What are the different types of System database inside SQL Server?
112. How does SQL Server database engine work?
113. What is the maximum number of indices per table?
114. What is service Broker?
115. What is CTE?
116. What are the advantages of CTE?
117. Can we write Sub Queries into Simple Select Using join and CTE?
118. Can CTE be recursive? Till what level it can be nested?
119. What does the Merge Statement do?
120. What are the new data types introduced in SQL Server 2012?
121. Define Hierarchyid data type?
122. What are synonyms?
123. What is LINQ?
124. How do you do Error Handling in SQL server?
125. What is RAISEERROR?
126. What is XML?
127. How can you find tables without Indexes?
128. How do you find the size of index?
129. How do you copy data from one table to another table?
130. What are the limitations of Select Into clause?
131. What is FileStream in SQL Server?
132. What do you mean by TableSample?
133. What is ROW_NUMBER()?
134. What is ROLLUP clause?
135. What are ranking functions?
136. What is CDC?
137. What is Auditing inside SQL Server?
138. What is the difference between GETDATE() and SYSDATETIME()?
139. How do you check if Automatic Statistic Update is enabled for a database?
140. What are the limitations of view?
141. What is a covered Index?
142. When I delete data from a table, Does SQL Server reduce the size of table?
143. How do you rebuild master database?
144. What is standby server?
145. How do you copy tables, schemas, and views from one sql server to another SQL Server?
146. Where are SQL Server user names and passwords stored in?
147. How do you disable Index?
148. How do you enable index?
149. What are WAIT Types?
150. What is FILL Factor?
151. What is Data Page?
152. What is the use of Transaction Log file?
153. What is the difference between view and Materialized view?
154. What is database mirroring?
155. What are the steps to create a Table Partition?
156. What are the basics of Table Partitioning in SQL Server?
157. How do you stop a log file from growing too big?
158. How do we use DBCC commands?
159. What is the difference between data mirroring and log shipping?
160. What are the different backup options within SQL Server?
161. How to add DEFAULT constraint on existing column? Write query
162. How to add NOT NULL constraint on existing column? Write query
163. Can we have Triggers on Temp table and Variable Table?
164. Can we create view on TEMP table?
165. Can we use constraints on TEMP Table?
166. How many maximum Identity columns we can have in a single table?
167. How to find all Triggers in database?
168. Can we add constraint on Variable Table?
169. How to take database online –offline?
170. How to copy data using Bulk copy when columns data type doesn’t match?
171. What is SP_Configure commands and SET commands?
172. What is Dynamic SQL?
173. How to execute Dynamic SQL?
174. Can we use Variable table in Dynamic SQL?
175. How to execute queries stored in a table?
176. How can you capture the length of column when it is text, image and ntext data type?
177. Is it possible to import data using TSQL?
178. How can you prevent TSQL code from running on a Production server?
179. How to disable Auto Commit in SQL Server?
180. Can we recover deleted data?
181. Can we create a table name as Table?
182. How to delete Top 100 records from a table?
183. How to delete two tables using one Drop command?
184. How can we find the table size?
185. How to find N highest salary?
186. Why do we use DECLARE for cursor and Variable table?
187. What are the different data types in SQL Server?
188. Which is fast UNION or UNION ALL?
189. Can we create clustered index on view?
190. Can we create computed columns?
191. What is Schema?
192. Can we create multiple constraints on a single column?
193. Can we rename table and column using ALTER command?
194. How to rename Table and Column?
195. How to rename Database?
196. Can we change the Column Sequence order inside table?
197. Truncate is DDL or DML?
198. What is difference between Deterministic and Non Deterministic Functions?
199. What is Synchronous and asynchronous function?
200. Can we add Identity column after creating the table?
201. Can we drop Identity column?
202. How can we disable Identity column?
203. Can Foreign key column have NULL?
204. How to find column description of a table?
205. How to find primary key name if not given by the user for a particular column?
206. Can we add two columns using ALTER command?
207. How to join two tables from different database?
208.  Can we use ORDER BY Clause in UNION?
209. How to get row number without ROW_NUMBER function?
210. What is Partitioned View?
211. What is WITH CHECK OPTION on view?
212. Can we create view from view?
213. Can we ALTER two columns using ALTER command?
214. Can we store Image, MP3 and binary data in SQL Server?
215. How to clean Buffer in SQL Server?
216. How to clear Execution Plan cache?
217. Can I create CTE in Trigger?
218. Can we create Variable table in Trigger?
219. Can we use cursors in Trigger?
220. Can we call Stored Procedure in Trigger?
221. Can I create Triggers on TEMP table?
222. Can we use PRINT Command in Triggers?
223. How Triggers are fired?
224. Can Inserted table have multiple records?
225. Can we perform DML & DDL operation on Inserted and Deleted tables?
226. What is the advantage of Index?
227. What is the disadvantage of Index?
228. How do you find why query is running slow?
229. How to create foreign key constraints on temporary table?
230. How can we check for existence of any object in database?
231. What is meant by differed name resolution in SQL Server?
232. What is the use of SP_Helptext , SP_HelpIndex stored procedure?
233. Can we change order of triggers?
234. How to find Organization Employee Hierarchy using SQL?
235. How does a recursive CTE works?
236. How to delete Duplicate records?
237. How to find employees hired in last month?
238. How to find all rows that contains only numeric data?
239. How to find department with highest number of employees?
240. How to join 3 tables?
241. What is the real time example of RIGHT Outer Join?
242. How to find who deleted/ dropped from Transaction log?
243. What are different operation available on ONDELETE and ONUPDATE?
244. What are the uses of System tables?
245. What is the difference between UNIQUE Key and Primary Key?
246. What is the difference between TEMP tables and Variable Tables?
247. What are the different String Functions in SQL Server?
248. What are the different Date functions in SQL Server?
249. What are the different Mathematical Functions in SQL Server?
250. What is the difference between COUNT and COUNT_BIG?

· Data ware Development Life Cycle.
· What is the Stored Procedure(SP).
· What is the Precompiled Option in SP.
· One table and One View, The structure was same but not related with each other.Table Contains 100            Rows &View Contains 200 Rows. Now Please Write Query for Total Rows in those Two.
· One Table u finds orphan records in a database.(orphan means their parent record_ in another table has        been deleted) Employee table & Employee Parent table but some time deleted records in Employee              Parent table only. How to find which records are deleted in Parent Table.
· Difference between Merge and Merge Join
· One table u Delete Duplicate Records without used CTE
· Triggers and Explain Types.
· What is the Mechanism for Triggers and Explain Briefly(How to Run Triggers)
· Indexes and what is the difference between Rebuild Index and Re organize Index
· 3 Triggers in a Table (Table have 20 Columns) First Trigger writes in first 3 columns in a table. Second          Trigger writes in 4 to 9 columns in a table. Third Trigger writes in 10 after in a table. Can u Please Explain?    If write any Operation what is order of Triggers are Executed.
· What is Select into & Insert into
· One table have Primary key. Can i use Non Clustered Index on Primary key

Session on 12-10-13(FA)

· Execute Plan of Query (Sql Server)
· What is Difference between Merge and Merge Join
· Instead of Trigger (Example)
· Print Command Write in Function is Possible or Not
· How To Handle Bottle neck Problems in Sql Server
· Difference between Delete & Truncate
· Difference between Char & Varchar
· Difference between Stored Procedures & Functions
· U Pass Table Variable Parameter to Stored Procedure
· What is Clustered and Non Cluster Indexes? Explain Clearly
· What is B Tree and Explain Leaf Node
· What is Result of 
Q-1
Begin Transaction T1 
Insert Row1
Begin Transaction T2
Insert Row2
Rollback T2
Commit T1

Q-2
Begin Transaction T1 
Insert Row1
Begin Transaction T2
Insert Row2
Commit T2
Rollback T1

· Write a Delete Statement of Duplicate Records(sno,name are Primary Keys)
  Sno,name,amt
  1,aaaa,20
  1,aaaa,30
  1,aaaa,20

Session on 26-11-13(F_BOA)

· Difference between Having & Where Clause and both are using in same Select Command
· Difference between Join & Union
· How to Handle Errors in SP and How to Store Errors in Another Table
· How to Get Next Auto generated Value in Sql
· Sub Queries and Explain when u used SubQueries in ur Project
· How to find 2nd Largest Salary of Employee in a Table
· How to find 3rd Largest Salary of Employee in a Table
· In above Two Question without SubQueries u implemented or Not
· Explain Simple use of Substring , Lowercase and Uppercase

· Table1 contains Student Details like Student Number and Name, Table2 contains Student Marks and Grades. U find who had fallen between Minimum and Maximum Respective Grades

Session on 27-11-13(F_ACC)

· What is ur Project?
· Explain Roles & Responsibilities in ur Project
· What type of Transformations are used and Explain Briefly
· Explain about Performance tuning
· Error and Event Handling
· What type of Sql sever Objects are used in ur Project
· Joins
· Stored Procedures
· Functions and Types
· Views
· Indexes

SQL Interview Questions

•  What are Constraints  or Define Constraints ?

Generally we use Data Types to limit the kind of Data in a Column. For example, if we declare any column with data type INT then ONLY Integer data can be inserted into the column. Constraint will help us to limit the Values we are passing into a column or a table. In simple Constraints are nothing but Rules or Conditions applied on columns or tables to restrict the data.

• Different types of Constraints ?

There are THREE Types of Constraints.

1. Domain
2. Entity
3. Referential

Domain has the following constraints types -

1. Not Null
2. Check

Entity has the following constraint types -

1. Primary Key
2. Unique Key

Referential has the following constraint types -

1. Foreign Key

• What is the difference between Primary Key and Unique Key ?

Both the Primary Key(PK) and Unique Key(UK) are meant to provide Uniqueness to the Column on which they are defined. PFB the major differences between these two.

1. By default PK defines Clustered Index in the column where as UK defines Non Clustered Index.
2. PK doesn’t allow NULL Value where as UK allow ONLY ONE NULL.
3. You can have only one PK per table where as UK can be more than one per table.
4. PK can be used in Foreign Key relationships where as UK cannot be used.

• What is the difference between Delete and Truncate ?

Both Delete and Truncate commands are meant to remove rows from a table. There are many differences between these two and pfb the same.

1. Truncate is Faster where as Delete is Slow process.
2. Truncate doesn’t log where as Delete logs an entry for every record deleted in Transaction Log.
3. We can rollback the Deleted data where as Truncated data cannot be rolled back.
4. Truncate resets the Identity column where as Delete doesn’t.
5. We can have WHERE Clause for delete where as for Truncate we cannot have WHERE Clause.
6. Delete Activates TRIGGER where as TRUNCATE Cannot.
7. Truncate is a DDL statement where as Delete is DML statement.

• What are Indexes or Indices ?

An Index in SQL is similar to the Index in a  book. Index of a book makes the reader to go to the desired page or topic easily and Index in SQL helps in retrieving the data faster from database. An Index is a seperate physical data structure that enables queries to pull the data fast. Indexes or Indices are used to improve the performance of a query.

• Types of Indices in SQL ?

There are TWO types of Indices in SQL server.

1. Clustered
2. Non Clustered

• How many Clustered and Non Clustered Indexes can be defined for a table ?

Clustered – 1
Non Clustered – 999

What is Transaction in SQL Server ? 

Transaction groups a set of T-Sql Statements into a single execution unit. Each transaction begins with a specific task and ends when all the tasks in the group successfully complete. If any of the tasks fails, the transaction fails. Therefore, atransaction has only two results: success or failure. Incomplete steps result in the failure of the transaction.by programmers to group together read and write operations. In Simple Either FULL or NULL i.e either all the statements executes successfully or all the execution will be rolled back.

• Types of Transactions ?

There are TWO forms of Transactions.

1. Implicit – Specifies any Single Insert,Update or Delete statement as Transaction Unit.  No need to specify Explicitly.
2. Explicit – A group of T-Sql statements with the beginning and ending marked with Begin Transaction,Commit and RollBack. PFB an Example for Explicit transactions.

BEGIN TRANSACTION

Update Employee Set Emp_ID = 54321 where Emp_ID = 12345

If(@@Error <>0)

ROLLBACK

Update LEave_Details Set Emp_ID = 54321 where Emp_ID = 12345

If(@@Error <>0)

ROLLBACK

COMMIT

In the above example we are trying to update an EMPLOYEE ID from 12345 to 54321 in both the master table “Employee” and Transaction table “Leave_Details”. In this case either BOTH the tables will be updated with new EMPID or NONE.

• What is the Max size and Max number of columns for a row in a table ?

Size – 8060 Bytes

Columns – 1024

• What is Normalization and Explain different normal forms.

Database normalization is a process of data design and organization which applies to data structures based on rules that help building relational databases.
1. Organizing data to minimize redundancy.
2. 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.

1NF: Eliminate Repeating Groups

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

2NF: Eliminate Redundant Data

1. Table must be in 1NF.
2. All fields are dependent on the whole of the primary key, or a relation is in 2NF if it is in 1NF and every non-key attribute is fully dependent on each candidate key of the relation. If an attribute depends on only part of a multi‐valued key, remove it to a separate table.

3NF: Eliminate Columns Not Dependent On Key

1. The table must be in 2NF.
2. Transitive dependencies must be eliminated. All attributes must rely only on the primary key. If attributes do not contribute to a description of the key, remove them to a separate table. All attributes must be directly dependent on the primary key.

BCNF: Boyce‐Codd Normal Form
for every one of its non-trivial functional dependencies X → Y, X is a superkey—that is, X is either a candidate key or a superset thereof. If there are non‐trivial dependencies between candidate key attributes, 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.
For example, if you can have two phone numbers values and two email address values, then you should not have them in the same table.
5NF: Isolate Semantically Related Multiple Relationships
A 4NF table is said to be in the 5NF if and only if every join dependency in it is implied by the candidate keys. There may be practical constrains on information that justify separating logically related many‐to‐many relationships.

• What is Denormalization ?

For optimizing the performance of a database by adding redundant data or by grouping data is called de-normalization.
It is sometimes necessary because current DBMSs implement the relational model poorly.
In some cases, de-normalization helps cover up the inefficiencies inherent in relational database software. A relational normalized database imposes a heavy access load over physical storage of data even if it is well tuned for high performance.
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.

• Query to Pull ONLY duplicate records from table ?

There are many ways of doing the same and let me explain one here. We can acheive this by using the keywords GROUP and HAVING. The following query will extract duplicate records from a specific column of a particular table.

Select specificColumn
FROM particluarTable
GROUP BY specificColumn
HAVING COUNT(*) > 1

This will list all the records that are repeated in the column specified by “specificColumn” of a “particlarTable”.

• Types of Joins in SQL SERVER ?

There are 3 types of joins in Sql server.

1. Inner Join
2. Outer Join
3. Cross Join

Outer join again classified into 3 types.

1. Right Outer Join
2. Left Outer Join
3. Full Outer Join.

• What is Table Expressions in Sql Server ?

Table Expressions are subqueries that are used where a TABLE is Expected. There are TWO types of table Expressions.

1. Derived tables
2. Common Table Expressions.

• What is Derived Table ?

Derived tables are table expression which appears in FROM Clause of a Query. PFB an example of the same.

select * from (Select Month(date) as Month,Year(Date) as Year from table1) AS Table2

In the above query the subquery in FROM Clause “(Select Month(date) as Month,Year(Date) as Year from table1) ” is called Derived Table.

• What is CTE or Common Table Expression ?

Common table expression (CTE) is a temporary named result set that you can reference within a

SELECT, INSERT, UPDATE, or DELETE statement. You can also use a CTE in a CREATE VIEW statement, as part of the view’s SELECT query. In addition, as of SQL Server 2008, you can add a CTE to the new MERGE statement. There are TWO types of CTEs in Sql Server -

1. Recursive
2. Non Recursive

• Difference between SmallDateTime and DateTime datatypes in Sql server ?

Both the data types are meant to specify date and time but these two has slight differences and pfb the same.

1. DateTime occupies 4 Bytes of data where as SmallDateTime occupies only 2 Bytes.
2. DateTime ranges from 01/01/1753 to 12/31/9999 where as SmallDateTime ranges from 01/01/1900 to 06/06/2079.

• What is SQL_VARIANT Datatype ? 

The SQL_VARIANT data type can be used to store values of various data types at the same time, such as numeric values, strings, and date values. (The only types of values that cannot be stored are TIMESTAMP values.) Each value of an SQL_VARIANT column has two parts: the data value and the information that describes the value. (This information contains all properties of the actual data type of the value, such as length, scale, and precision.)

• What is Temporary table ? 

A temporary table is a database object that is temporarily stored and managed by the database system. There are two types of Temp tables.

1. Local
2. Global

• What are the differences between Local Temp table and Global Temp table ? 

Before going to the differences, let’s see the similarities.

1. Both are stored in tempdb database.
2. Both will be cleared once the connection,which is used to create the table, is closed.
3. Both are meant to store data temporarily.

PFB the differences between these two.

1. Local temp table is prefixed with # where as Global temp table with ##.
2. Local temp table is valid for the current connection i.e the connection where it is created where as Global temp table is valid for all the connection.
3.  Local temp table cannot be shared between multiple users where as Global temp table can be shared.

• Whar are the differences between Temp table and Table variable ?

This is very routine question in interviews. Let’s see the major differences between these two.

1. Table variables are Transaction neutral where as Temp tables are Transaction bound. For example if we declare and load data into a temp table and table variable in a transaction and if the transaction is ROLLEDBACK, still the table variable will have the data loaded where as Temp table will not be available as the transaction is rolled back.
2. Temporary Tables are real tables so you can do things like CREATE INDEXes, etc. If you have large amounts of data for which accessing by index will be faster then temporary tables are a good option.
3. Table variables don’t participate in transactions, logging or locking. This means they’re faster as they don’t require the overhead.
4. You can create a temp table using SELECT INTO, which can be quicker to write (good for ad-hoc querying) and may allow you to deal with changing datatypes over time, since you don’t need to define your temp table structure upfront.

• What is the difference between Char,Varchar and nVarchar datatypes ?

char[(n)] - Fixed-length non-Unicode character data with length of n bytes. n must be a value from 1 through 8,000. Storage size is n bytes. The SQL-92 synonym for char is character.

varchar[(n)] - Variable-length non-Unicode character data with length of n bytes. n must be a value from 1 through 8,000. Storage size is the actual length in bytes of the data entered, not n bytes. The data entered can be 0 characters in length. The SQL-92 synonyms for varchar are char varying or character varying.

nvarchar(n) - Variable-length Unicode character data of n characters. n must be a value from 1 through 4,000. Storage size, in bytes, is two times the number of characters entered. The data entered can be 0 characters in length. The SQL-92 synonyms for nvarchar are national char varying and national character varying.

• What is the difference between STUFF and REPLACE functions in Sql server ?

The Stuff function is used to replace characters in a string. This function can be used to delete a certain length of the string and replace it with a new string.

Syntax – STUFF (string_expression, start, length, replacement_characters)

Ex - SELECT STUFF(‘I am a bad boy’,8,3,’good’)

Output – “I am a good boy”

REPLACE function replaces all occurrences of the second given string expression in the first string expression with a third expression.

Syntax - REPLACE (String, StringToReplace, StringTobeReplaced)

Ex – REPLACE(“Roopesh”,”pe”,”ep”)

Output – “Rooepsh” – You can see PE is replaced with EP in the output.

• What are Magic Tables ? 

Sometimes we need to know about the data which is being inserted/deleted by triggers in database. Whenever a trigger fires in response to the INSERT, DELETE, or UPDATE statement, two special tables are created. These are the inserted and the deleted tables. They are also referred to as the magic tables. These are the conceptual tables and are similar in structure to the table on which trigger is defined (the trigger table).

The inserted table contains a copy of all records that are inserted in the trigger table.
The deleted table contains all records that have been deleted from deleted from the trigger table.
Whenever any updation takes place, the trigger uses both the inserted and deleted tables.

• Explain about RANK,ROW_NUMBER and DENSE_RANK in Sql server ?

PFB the content of the same here.

Lets take 1 simple example to understand the difference between 3.
First lets create some sample data :

– create table
CREATE TABLE Salaries
(
Names VARCHAR(1),
SalarY INT
)
GO
– insert data
INSERT INTO Salaries SELECT
‘A’,5000 UNION ALL SELECT
‘B’,5000 UNION ALL SELECT
‘C’,3000 UNION ALL SELECT
‘D’,4000 UNION ALL SELECT
‘E’,6000 UNION ALL SELECT
‘F’,10000
GO
– Test the data
SELECT Names, Salary
FROM Salaries

Now lets query the table to get the salaries of all employees with their salary in descending order.
For that I’ll write a query like this :
SELECT names
, salary
,row_number () OVER (ORDER BY salary DESC) as ROW_NUMBER
,rank () OVER (ORDER BY salary DESC) as RANK
,dense_rank () OVER (ORDER BY salary DESC) as DENSE_RANK
FROM salaries

>>Output

NAMES	SALARY	ROW_NUMBER	RANK	DENSE_RANK
F	10000	1	1	1
E	6000	2	2	2
A	5000	3	3	3
B	5000	4	3	3
D	4000	5	5	4
C	3000	6	6	5

Interesting Names in the result are employee A, B and D.  Row_number assign different number to them. Rank and Dense_rank both assign same rank to A and B. But interesting thing is what RANK and DENSE_RANK assign to next row? Rank assign 5 to the next row, while dense_rank assign 4.

The numbers returned by the DENSE_RANK function do not have gaps and always have consecutive ranks.  The RANK function does not always return consecutive integers.  The ORDER BY clause determines the sequence in which the rows are assigned their unique ROW_NUMBER within a specified partition.
So question is which one to use?
Its all depends on your requirement and business rule you are following.
1. Row_number to be used only when you just want to have serial number on result set. It is not as intelligent as RANK and DENSE_RANK.
2. Choice between RANK and DENSE_RANK depends on business rule you are following. Rank leaves the gaps between number when it sees common values in 2 or more rows. DENSE_RANK don’t leave any gaps between ranks.
So while assigning the next rank to the row RANK will consider the total count of rows before that row and DESNE_RANK will just give next rank according to the value.
So If you are selecting employee’s rank according to their salaries you should be using DENSE_RANK and if you are ranking students according to there marks you should be using RANK(Though it is not mandatory, depends on your requirement.)

•  What are the differences between WHERE and HAVING clauses in SQl Server ? 

PFB the major differences between WHERE and HAVING Clauses ..

1.Where Clause can be used other than Select statement also where as Having is used only with the SELECT statement.
2.Where applies to each and single row and Having applies to summarized rows (summarized with GROUP BY).
3.In Where clause the data that fetched from memory according to condition and In having the completed data firstly fetched and then separated according to condition.
4.Where is used before GROUP BY clause and HAVING clause is used to impose condition on GROUP Function and is used after GROUP BY clause in the query.

• Explain Physical Data Model or PDM ?

Physical data model represents how the model will be built in the database. A physical database model shows all table structures, including column name, column data type, column constraints, primary key, foreign key, and relationships between tables. Features of a physical data model include:

1. Specification all tables and columns.
2. Foreign keys are used to identify relationships between tables.
3. Specying Data types.

 Explain Logical Data Model ?

A logical data model describes the data in as much detail as possible, without regard to how they will be physical implemented in the database. Features of a logical data model include:

1. Includes all entities and relationships among them.
2. All attributes for each entity are specified.
3. The primary key for each entity is specified.
4. Foreign keys (keys identifying the relationship between different entities) are specified.
5. Normalization occurs at this level.

Explain Conceptual Data Model ?

A conceptual data model identifies the highest-level relationships between the different entities. Features of conceptual data model include:

1. Includes the important entities and the relationships among them.
2. No attribute is specified.
3. No primary key is specified.

What is Log Shipping ?

Log Shipping is a basic level SQL Server high-availability technology that is part of SQL Server. It is an automated backup/restore process that allows you to create another copy of your database for failover.

Log shipping involves copying a database backup and subsequent transaction log backups from the primary (source) server and restoring the database and transaction log backups on one or more secondary (Stand By / Destination) servers. The Target Database is in a standby or no-recovery mode on the secondary server(s) which allows subsequent transaction logs to be backed up on the primary and shipped (or copied) to the secondary servers and then applied (restored) there.

•  What are the advantages of database normalization ?

Benefits of normalizing the database are

1. No need to restructure existing tables for new data.
2. Reducing repetitive entries.
3. Reducing required storage space
4. Increased speed and flexibility of queries.

•  What are Linked Servers  ?

 Linked servers are configured to enable the Database Engine to execute a Transact-SQL statement that includes tables in another instance of SQL Server, or another database product such as Oracle. Many types OLE DB data sources can be configured as linked servers, including Microsoft Access and Excel. Linked servers offer the following advantages:

1. The ability to access data from outside of SQL Server.
2. The ability to issue distributed queries, updates, commands, and transactions on heterogeneous data sources across the enterprise.
3. The ability to address diverse data sources similarly.
4. Can connect to MOLAP databases too.

•  What is the Difference between the functions COUNT and COUNT_BIG ?

Both Count and Count_Big functions are used to count the number of rows in a table and the only difference is what it returns.

1. Count returns INT datatype value where as Count_Big returns BIGINT datatype value.
2. Count is used if the rows in a table are less where as Count_Big will be used when the numbenr of records are in millions or above.

Syntax -

1. Count – Select count(*) from tablename
2. Count_Big – Select Count_Big(*) from tablename

•  How to insert values EXPLICITLY  to an Identity Column ? 

This has become a common question these days in interviews. Actually we cannot Pass values to Identity column and you will get the following error message when you try to pass value.

Msg 544, Level 16, State 1, Line 3

Cannot insert explicit value for identity column in table 'tablename' when IDENTITY_INSERT is set to OFF.

To pass an external value we can use the property IDENTITY_INSERT. PFB the sysntax of the same.

SET IDENTITY_INSERT <tablename> ON;

Write your Insert  statement here by passing external values to the IDENTITY column.

Once the data is inserted then remember to SET the property to OFF.

• How to RENAME a table and column in SQL ?

We can rename a table or a column in SQL using the System stored procedure SP_RENAME. PFB the sample queries.

Table - EXEC sp_rename @objname = department, @newname = subdivision

Column - EXEC sp_rename @objname = ‘sales.order_no’ , @newname = ordernumber

• How to rename a database ?

To rename a database please use the below syntax.

USE master;

GO

ALTER DATABASE databasename

Modify Name = newname ;

GO

•  What is the use the UPDATE_STATISTICS command ?

UPDATE_STATISTICS updates the indexes on the tables when there is large processing of data. If we do a large amount of deletions any modification or Bulk Copy into the tables, we need to basically update the indexes to take these changes into account.

• How to read the last record from a table with Identity Column ?

We can get the same using couple of ways and PFB the same.

First - 

SELECT *
FROM    TABLE
WHERE  ID = IDENT_CURRENT(‘TABLE’)

Second - 

SELECT *
FROM    TABLE
WHERE   ID = (SELECT MAX(ID)  FROM TABLE)

Third - 

select top 1 * from TABLE_NAME  order by ID desc

• What is Worktable ?

A worktable is a temporary table used internally by SQL Server to process the intermediate results of a query. Worktables are created in the tempdb database and are dropped automatically after query execution. Thease table cannot be seen as these are created while a query executing and dropped immediately after the execution of the query.

• What is HEAP table ?

A table with NO CLUSTERED INDEXES is called as HEAP table. The data rows of a heap table are not stored in any particular order or linked to the adjacent pages in the table. This unorganized structure of the heap table usually increases the overhead of accessing a large heap table, when compared to accessing a large nonheap table (a table with clustered index). So, prefer not to go with HEAP  tables .. 

• What is ROW LOCATOR ?

If you define a NON CLUSTERED index on a table then the index row of a nonclustered index contains a pointer to the corresponding data row of the table. This pointer is called a row locator. The value of the row locator depends on whether the data pages are stored in a heap or are clustered. For a nonclustered index, the row locator is a pointer to the data row. For a table with a clustered index, the row locator is the clustered index key value.

•  What is Covering Index ?

A covering index is a nonclustered index built upon all the columns required to satisfy a SQL query without going to the base table. If a query encounters an index and does not need to refer to the underlying data table at all, then the index can be considered a covering index.  For Example

Select col1,col2 from table
where col3 = Value
group by col4
order by col5

Now if you create a clustered index for all the columns used in Select statement then the SQL doesn’t need to go to base tables as everything required are available in index pages.

•  What is Indexed View ?

A database view in SQL Server is like a virtual table that represents the output of a SELECT statement. A view is created using the CREATE VIEW statement, and it can be queried exactly like a table. In general, a view doesn’t store any data—only the SELECT statement associated with it. Every time a view is queried, it further queries the underlying tables by executing its associated SELECT statement.

A database view can be materialized on the disk by creating a unique clustered index on the view. Such a view is referred to as an indexed view. After a unique clustered index is created on the view, the view’s result set is materialized immediately and persisted in physical storage in the database, saving the overhead of performing costly operations during query execution. After the view is materialized, multiple nonclustered indexes can be created on the indexed view.

• What is Bookmark Lookup ?

When a SQL query requests a small number of rows, the optimizer can use the nonclustered index, if available, on the column(s) in the WHERE clause to retrieve the data. If the query refers to columns that are not part of the nonclustered index used to retrieve the data, then navigation is required from the index row to the corresponding data row in the table to access these columns.This operation is called a bookmark lookup.

What are the Isolation levels in SQL 

Isolation levels :- Isolation level is required to isolate a resource and protect it from other transactions. This

is achieved with the help of locks but what locks are needed and how they can be established is decided on the

isolation level set on the database level. If low level of Isolation is set, it allows multiple users to access

the resources concurrently but it may result in many  concurrency related problems like phantom reads, dirty reads

etc. If higher levels of Isolation is set then it eliminate the concurrency related problem but it results in less

number of concurrent access and it may result in data blocking

Dirty reads:- This situation happens when a transaction tries to read a data by some other concurrent transaction

which is not committed yet. There is a  risk, that this other transaction may never be committed,  leaving the

original transaction with wrong data.

Lost updates:- It happen when more than one transaction tries to update the same data. In Lost update, a

successfully updated value of a data written in the database is overrides accidentally by the some another

transaction updating the same data concurrently.

Repeatable reads:- Repeatable reads condition occur when a transaction tries to read a data multiple times and and

between the two reads, another transaction modified that data. Therefore when the original transaction tries to

read that data second time, it find different value for that data. In other words, the original transaction reads

two different values for the same data.

Phantom reads:-This condition happen when a transaction needs to execute a same query twice(or multiple times) and

it gets different set of rows from what it get earlier due to the execution of the query first time. This happens

if some another transaction add or delete rows between the two executions of the query and these added/deleted

rows are the part of the record set reruns by  the execution of the query.

There are the  five Isolation levels (from lower level to higher level) defined in the SQL Server.

Read Uncommitted
Read Committed
Repeatable Read
Serializable
Snapshot

Read Uncommitted:- In this Isolation level, a transaction can read the data which is modified by some other
transactions but still not committed. This Isolation level do not issue shared locked to prevent data modification
which is read by some other transactions. Also it is not issue exclusive locks to prevents transactions from
reading data which is modified from other transaction by not committed yet. It may results in problem like dirty
read, lost updates, phantom reads etc. It is the least restrictive Isolation level.
Read Committed:- It is the default Isolation set by the SQL Server for any Database. This Isolation level prevents
the transaction from reading data which are modified by some other transactions but still are not committed yet.
Thus it eliminates the problem of Dirty read. But it do not eliminates the occurrence of Phantom reads and
Repeatable reads.
Repeatable Read:- This isolation level is higher than the previous two mention isolation level and it does not
allows any transaction to read a data that is being modified by some other transaction but not committed yet. Also
it doesn't allow any transaction to modify a data if it is being read by some other transaction until the
transaction reading that data complete its operation.  This way it eliminates the dirty read and Repeatable reads
problem but it doesn't eliminates the Phantom reads.
Serializable:- This Isolation level do not allow any transaction to read the data unless the other transactions
completed their data modification operation. Also it doesn't allow other transactions to modify the data until the
current transaction completed its read operation. This isolation level allows a transaction to acquire a read lock
(if only read operation) or write lock (for insert,delete,update) for the entire range of records that the
transaction is going to affect. For example, if the query is "Select * from employees" then the transaction will
acquire the read lock for whole table and no other transaction is allowed to add new rows or delete the rows until
the current transaction releases its lock. Similarly, if the query is "Select * from Employee where
country='India", then the current transaction will acquire the read lock for all the records of the table where
country is India, and no other transaction is allowed to add or delete new rows until the current transaction
releases its read lock. Thus, if we executes the same query twice , then each time it will get the same set of
rows of a data  and therefore it eliminates the Phantom read problem.

Snapshot:-In this isolation level, a transaction recognise only data which is committed before the start of the
transaction. Any modification of the data after the transaction is begin,  is not visible to any statements of the
currently executing transaction. It is like a snapshot of data, given to each transaction. It is done with the
help of row version where a separate version of each modified row is maintain in the temp db database dedicated to
the transactions. This  isolation level eliminates dirty reads, lost updates, repeatable reads and Phantom reads
problem.

Interview Questions in SQL Server Database

What are DMVs?

Dynamic management views (DMVs) and functions return server state information that can be used to monitor the health of a server instance, diagnose problems, and tune performance; that is, they let you see what is going on inside SQL Server. They were introduced in SQL Server 2005 as an alternative to system tables. One example is viewing operating system wait statistics via this query:

SELECT * FROM sys.dm_os_wait_stats;

Another example is examining current sessions, much like the sp_who2 command:

SELECT * FROM sys.dm_exec_sessions;

What are temp tables? What is the difference between global and local temp tables?

Temporary tables are temporary storage structures. You may use temporary tables as buckets to store data that you will manipulate before arriving at a final format. The hash (#) character is used to declare a temporary table as it is prepended to the table name. A single hash (#) specifies a local temporary table.

CREATE TABLE #tempLocal ( nameid int, fname varchar(50), lname varchar(50) )

Local temporary tables are available to the current connection for the user, so they disappear when the user disconnects.

Global temporary tables may be created with double hashes (##). These are available to all users via all connections, and they are deleted only when all connections are closed.

CREATE TABLE ##tempGlobal ( nameid int, fname varchar(50), lname varchar(50) )

Once created, these tables are used just like permanent tables; they should be deleted when you are finished with them. Within SQL Server, temporary tables are stored in the Temporary Tables folder of the tempdb database.

How are transactions used?

Transactions allow you to group SQL commands into a single unit. The transaction begins with a certain task and ends when all tasks within it are complete. The transaction completes successfully only if all commands within it complete successfully. The whole thing fails if one command fails. The BEGIN TRANSACTION, ROLLBACK TRANSACTION, and COMMIT TRANSACTION statements are used to work with transactions. A group of tasks starts with the begin statement. If any problems occur, the rollback command is executed to abort. If everything goes well, all commands are permanently executed via the commit statement.

What is the difference between a clustered and a nonclustered index?

A clustered index affects the way the rows of data in a table are stored on disk. When a clustered index is used, rows are stored in sequential order according to the index column value; for this reason, a table can contain only one clustered index, which is usually used on the primary index value.

A nonclustered index does not affect the way data is physically stored; it creates a new object for the index and stores the column(s) designated for indexing with a pointer back to the row containing the indexed values.

You can think of a clustered index as a dictionary in alphabetical order, and a nonclustered index as a book’s index.

What are DBCC commands?

Basically, the Database Consistency Checker (DBCC) provides a set of commands (many of which are undocumented) to maintain databases — maintenance, validation, and status checks. The syntax is DBCC followed by the command name. Here are three examples:

DBCC CHECKALLOC — Check disk allocation consistency.

DBCC OPENTRAN — Display information about recent transactions.

DBCC HELP — Display Help for DBCC commands.

What is the difference between truncate and delete?

Truncate is a quick way to empty a table. It removes everything without logging each row. Truncate will fail if there are foreign key relationships on the table. Conversely, the delete command removes rows from a table, while logging each deletion and triggering any delete triggers that may be present.

What does the NOLOCK query hint do?

Table hints allow you to override the default behavior of the query optimizer for statements. They are specified in the FROM clause of the statement. While overriding the query optimizer is not always suggested, it can be useful when many users or processes are touching data. The NOLOCK query hint is a good example because it allows you to read data regardless of who else is working with the data; that is, it allows a dirty read of data — you read data no matter if other users are manipulating it. A hint like NOLOCK increases concurrency with large data stores.

SELECT * FROM table_name (NOLOCK)

Microsoft advises against using NOLOCK, as it is being replaced by the READUNCOMMITTED query hint

What is a CTE?

A common table expression (CTE) is a temporary named result set that can be used within other statements like SELECT, INSERT, UPDATE, and DELETE. It is not stored as an object and its lifetime is limited to the query. It is defined using the WITH statement as the following example shows:

WITH ExampleCTE (id, fname, lname)

AS

(

SELECT id, firstname, lastname FROM table

)

SELECT * FROM ExampleCTE

A CTE can be used in place of a view in some instances.

What is a view? What is the WITH CHECK OPTION clause for a view?

A view is a virtual table that consists of fields from one or more real tables. Views are often used to join multiple tables or to control access to the underlying tables.

The WITH CHECK OPTION for a view prevents data modifications (to the data) that do not confirm to the WHERE clause of the view definition. This allows data to be updated via the view, but only if it belongs in the view.

What is a query execution plan?

SQL Server has an optimizer that usually does a great job of optimizing code for the most effective execution. A query execution plan is the breakdown of how the optimizer will run (or ran) a query. There are several ways to view a query execution plan. This includes using the Show Execution Plan option within Query Analyzer; Display Estimated Execution Plan on the query dropdown menu; or use the SET SHOWPLAN_TEXT ON command before running a query and capturing the execution plan event in a SQL Server Profiler trace.

What does the SQL Server Agent Windows service do?

SQL Server Agent is a Windows service that handles scheduled tasks within the SQL Server environment (aka jobs). The jobs are stored/defined within SQL Server, and they contain one or more steps that define what happens when the job runs. These jobs may run on demand, as well as via a trigger or predefined schedule. This service is very important when determining why a certain job did not run as planned — often it is as simple as the SQL Server Agent service not running.

What is the default port number for SQL Server?

If enabled, the default instance of Microsoft SQL Server listens on TCP port 1433. Named instances are configured for dynamic ports, so an available port is chosen when SQL Server starts. When connecting to a named instance through a firewall, configure the Database Engine to listen on a specific port, so that the appropriate port can be opened in the firewall.

The list of possible questions is endless. I am sure these questions will spawn debate and discussion.

z