SQL Admin Interview Questions

1. Explain the architecture of SQL Server?
**
2. Different types of Backups?
o A full database backup is a full copy of the database.
o A transaction log backup copies only the transaction log.
o A differential backup copies only the database pages modified after the last full database backup.
o A file or filegroup restore allows the recovery of just the portion of a database that was on the failed disk.
3. What are ‘jobs’ in SQL Server? How do we create one? What is tasks?
Using SQL Server Agent jobs, you can automate administrative tasks and run them on a recurring basis.
**
4. What is database replication? What are the different types of replication you can set up in SQL Server? How are they used? What is snapshot replication how is it different from Transactional replication?
Replication is the process of copying/moving data between databases on the same or different servers. SQL Server supports the following types of replication scenarios:
1. Snapshot replication - It distributes data exactly as it appears at a specific moment in time and doesn’t monitor for updates. It can be used when data changes are infrequent. It is often used for browsing data such as price lists, online catalog, or data for decision support where the current data is not required and data is used as read only.
2. Transactional replication (with immediate updating subscribers, with queued updating subscribers) - With this an initial snapshot of data is applied, and whenever data modifications are made at the publisher, the individual transactions are captured and propagated to the subscribers.
3. Merge replication - It is the process of distributing the data between publisher and subscriber, it allows the publisher and subscriber to update the data while connected or disconnected, and then merging the updates between the sites when they are connected.
5. How can u look at what are the process running on SQL server? How can you kill a process in SQL server?
o Expand a server group, and then expand a server.
o Expand Management, and then expand Current Activity.
o Click Process Info. The current server activity is displayed in the details pane.
In the details pane, right-click a Process ID, and then click Kill Process.
6. What is RAID and what are different types of RAID configurations?
RAID stands for Redundant Array of Inexpensive Disks, used to provide fault tolerance to database servers. There are six RAID levels 0 through 5 offering different levels of performance, fault tolerance.
7.
Some of the tools/ways that help you troubleshooting performance problems are: SET SHOWPLAN_ALL ON, SET SHOWPLAN_TEXT ON, SET STATISTICS IO ON, SQL Server Profiler, Windows NT /2000 Performance monitor, Graphical execution plan in Query Analyzer.
8. How to determine the service pack currently installed on SQL Server?
The global variable @@Version stores the build number of the sqlservr.exe, which is used to determine the service pack installed.
eg: Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05 Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on Windows NT 5.0 (Build 2195: Service Pack 3)
9. What is the purpose of using COLLATE in a query?
The term, collation, refers to a set of rules that determine how data is sorted and compared. In Microsoft® SQL Server 2000, it is not required to separately specify code page and sort order for character data, and the collation used for Unicode data. Instead, specify the collation name and sorting rules to use. Character data is sorted using rules that define the correct character sequence, with options for specifying case-sensitivity, accent marks, kana character types, and character width. Microsoft SQL Server 2000 collations include these groupings:
• Windows collations - Windows collations define rules for storing character data based on the rules defined for an associated Windows locale. The base Windows collation rules specify which alphabet or language is used when dictionary sorting is applied, as well as the code page used to store non-Unicode character data. For Windows collations, the nchar, nvarchar, and ntext data types have the same sorting behavior as char, varchar, and text data types
• SQL collations - SQL collations are provided for compatibility with sort orders in earlier versions of Microsoft SQL Server.
Sort Order
Binary is the fastest sorting order, and is case-sensitive. If Binary is selected, the Case-sensitive, Accent-sensitive, Kana-sensitive, and Width-sensitive options are not available.

Sort order Description
Binary Sorts and compares data in Microsoft® SQL Server™ tables based on the bit patterns defined for each character. Binary sort order is case-sensitive, that is lowercase precedes uppercase, and accent-sensitive. This is the fastest sorting order.
If this option is not selected, SQL Server follows sorting and comparison rules as defined in dictionaries for the associated language or alphabet.
Case-sensitive Specifies that SQL Server distinguish between uppercase and lowercase letters.
If not selected, SQL Server considers the uppercase and lowercase versions of letters to be equal. SQL Server does not define whether lowercase letters sort lower or higher in relation to uppercase letters when Case-sensitive is not selected.
Accent-sensitive Specifies that SQL Server distinguish between accented and unaccented characters. For example, 'a' is not equal to 'á'.
If not selected, SQL Server considers the accented and unaccented versions of letters to be equal.
Kana-sensitive Specifies that SQL Server distinguish between the two types of Japanese kana characters: Hiragana and Katakana.
If not selected, SQL Server considers Hiragana and Katakana characters to be equal.
Width-sensitive Specifies that SQL Server distinguish between a single-byte character (half-width) and the same character when represented as a double-byte character (full-width).
If not selected, SQL Server considers the single-byte and double-byte representation of the same character to be equal.
Windows collation options:
• Use Latin1_General for the U.S. English character set (code page 1252).
• Use Modern_Spanish for all variations of Spanish, which also use the same character set as U.S. English (code page 1252).
• Use Arabic for all variations of Arabic, which use the Arabic character set (code page 1256).
• Use Japanese_Unicode for the Unicode version of Japanese (code page 932), which has a different sort order from Japanese, but the same code page (932).
10. What is the STUFF Function and how does it differ from the REPLACE function?
STUFF - Deletes a specified length of characters and inserts another set of characters at a specified starting point.
SELECT STUFF('abcdef', 2, 3, 'ijklmn')
GO
Here is the result set:
---------
aijklmnef
REPLACE - Replaces all occurrences of the second given string expression in the first string expression with a third expression.
SELECT REPLACE('abcdefghicde','cde','xxx')
GO
Here is the result set:
------------
abxxxfghixxx
11. What does it mean to have quoted_identifier on? What are the implications of having it off?
When SET QUOTED_IDENTIFIER is OFF (default), literal strings in expressions can be delimited by single or double quotation marks.
When SET QUOTED_IDENTIFIER is ON, all strings delimited by double quotation marks are interpreted as object identifiers. Therefore, quoted identifiers do not have to follow the Transact-SQL rules for identifiers.
SET QUOTED_IDENTIFIER must be ON when creating or manipulating indexes on computed columns or indexed views. If SET QUOTED_IDENTIFIER is OFF, CREATE, UPDATE, INSERT, and DELETE statements on tables with indexes on computed columns or indexed views will fail.
The SQL Server ODBC driver and Microsoft OLE DB Provider for SQL Server automatically set QUOTED_IDENTIFIER to ON when connecting.
When a stored procedure is created, the SET QUOTED_IDENTIFIER and SET ANSI_NULLS settings are captured and used for subsequent invocations of that stored procedure. When executed inside a stored procedure, the setting of SET QUOTED_IDENTIFIER is not changed.
SET QUOTED_IDENTIFIER OFF
GO
-- Attempt to create a table with a reserved keyword as a name
-- should fail.
CREATE TABLE "select" ("identity" int IDENTITY, "order" int)
GO

SET QUOTED_IDENTIFIER ON
GO
-- Will succeed.
CREATE TABLE "select" ("identity" int IDENTITY, "order" int)
GO
12. What is the purpose of UPDATE STATISTICS?
Updates information about the distribution of key values for one or more statistics groups (collections) in the specified table or indexed view.
13. Fundamentals of Data warehousing & olap?
14. What do u mean by OLAP server? What is the difference between OLAP and OLTP?
15. What is a tuple?
A tuple is an instance of data within a relational database.
16. Services and user Accounts maintenance
17. sp_configure commands?
Displays or changes global configuration settings for the current server.
18. What is the basic functions for master, msdb, tempdb databases?
Microsoft® SQL Server 2000 systems have four system databases:
• master - The master database records all of the system level information for a SQL Server system. It records all login accounts and all system configuration settings. master is the database that records the existence of all other databases, including the location of the database files.
• tempdb - tempdb holds all temporary tables and temporary stored procedures. It also fills any other temporary storage needs such as work tables generated by SQL Server. tempdb is re-created every time SQL Server is started so the system starts with a clean copy of the database.
By default, tempdb autogrows as needed while SQL Server is running. If the size defined for tempdb is small, part of your system processing load may be taken up with autogrowing tempdb to the size needed to support your workload each time to restart SQL Server. You can avoid this overhead by using ALTER DATABASE to increase the size of tempdb.
• model - The model database is used as the template for all databases created on a system. When a CREATE DATABASE statement is issued, the first part of the database is created by copying in the contents of the model database, then the remainder of the new database is filled with empty pages. Because tempdb is created every time SQL Server is started, the model database must always exist on a SQL Server system.
• msdb - The msdb database is used by SQL Server Agent for scheduling alerts and jobs, and recording operators.
19. What are sequence diagrams? What you will get out of this sequence diagrams?
Sequence diagrams document the interactions between classes to achieve a result, such as a use case. Because UML is designed for object-oriented programming, these communications between classes are known as messages. The sequence diagram lists objects horizontally, and time vertically, and models these messages over time.
20. What are the new features of SQL 2000 than SQL 7? What are the new datatypes in sql?
• XML Support - The relational database engine can return data as Extensible Markup Language (XML) documents. Additionally, XML can also be used to insert, update, and delete values in the database. (for xml raw - to retrieve output as xml type)
• User-Defined Functions - The programmability of Transact-SQL can be extended by creating your own Transact-SQL functions. A user-defined function can return either a scalar value or a table.
• Indexed Views - Indexed views can significantly improve the performance of an application where queries frequently perform certain joins or aggregations. An indexed view allows indexes to be created on views, where the result set of the view is stored and indexed in the database.
• New Data Types - SQL Server 2000 introduces three new data types. bigint is an 8-byte integer type. sql_variant is a type that allows the storage of data values of different data types. table is a type that allows applications to store results temporarily for later use. It is supported for variables, and as the return type for user-defined functions.
• INSTEAD OF and AFTER Triggers - INSTEAD OF triggers are executed instead of the triggering action (for example, INSERT, UPDATE, DELETE). They can also be defined on views, in which case they greatly extend the types of updates a view can support. AFTER triggers fire after the triggering action. SQL Server 2000 introduces the ability to specify which AFTER triggers fire first and last.
• Multiple Instances of SQL Server - SQL Server 2000 supports running multiple instances of the relational database engine on the same computer. Each computer can run one instance of the relational database engine from SQL Server version 6.5 or 7.0, along with one or more instances of the database engine from SQL Server 2000. Each instance has its own set of system and user databases.
• Index Enhancements - You can now create indexes on computed columns. You can specify whether indexes are built in ascending or descending order, and if the database engine should use parallel scanning and sorting during index creation.
21. How do we open SQL Server in single user mode?
We can accomplish this in any of the three ways given below :-
. From Command Prompt :-
sqlservr -m
a. From Startup Options :-
Go to SQL Server Properties by right-clicking on the Server name in the Enterprise manager.
Under the 'General' tab, click on 'Startup Parameters'.
Enter a value of -m in the Parameter.
b. From Registry :-
Go to HKEY_LOCAL_MACHINE\Software\Microsoft\MSSQLServer\MSSQLServer\Parameters.
Add new string value.
Specify the 'Name' as SQLArg(n) & 'Data' as -m.
Where n is the argument number in the list of arguments.
22. Difference between clustering and NLB (Network Load Balancing)?
**
23. Explain Active/Active and Active/Passive cluster configurations?
**
24. What is Log Shipping?
In Microsoft® SQL Server™ 2000 Enterprise Edition, you can use log shipping to feed transaction logs from one database to another on a constant basis. Continually backing up the transaction logs from a source database and then copying and restoring the logs to a destination database keeps the destination database synchronized with the source database. This allows you to have a backup server and also provides a way to offload query processing from the main computer (the source server) to read-only destination servers.
25. What are the main steps you take care for enhancing SQL Server performance?
**
26. You have to check whether any users are connected to sql server database and if any user is connected to database, you have to disconnect the user(s) and run a process in a job. How do you do the above in a job?
**
XML
27. How can I convert data in a Microsoft Access table into XML format?
The following applications can help you convert Access data into XML format: Access 2002, ADO 2.5, and SQLXML. Access 2002 (part of Microsoft Office XP) enables you to query or save a table in XML format. You might be able to automate this process. ADO 2.5 and later enables you to open the data into a recordset, then persist the recordset in XML format, as the following code shows:
rs.Save "c:\rs.xml", adPersistXML
You can use linked servers to add the Access database to your SQL Server 2000 database so you can run queries from within SQL Server to retrieve data. Then, through HTTP, you can use the SQLXML technology to extract the Access data in the XML format you want.
NEW
28. @@IDENTITY ?
Ans: Returns the last-inserted identity value.
29. If a job is fail in sql server, how do find what went wrong?
30. Have you used Error handling in DTS?