Category Archives: DB Articles

SQL Server Interview Questions 2K9 – Part – I

Question: What is COMMIT & ROLLBACK statement in SQL ?
Answer: Commit statement helps in termination of the current transaction and does all the changes that occur in transaction persistent and this also commits all the changes to the database. COMMIT we can also use in store procedure.
ROLLBACK do the same thing just terminate the current transaction but one another thing is that the changes made to database are ROLLBACK to the database.

Question:-What is difference between OSQL and Query Analyzer ?
Answer:-Both are the same but there is little difference OSQL is command line tool which is execute query and display the result same a query analyzer but query analyzer is graphical and OSQL is a command line tool.OSQL have not ability like query analyzer to analyze queries and show static on speed of execution and other useful thing about OSQL is that its helps in scheduling.

Question: What is SQL?

Answer: The Structured Query Language (SQL) is foundation for all relational database systems. Most of the large-scale databases use the SQL to define all user and administrator interactions. QL is Non-Procedural language. It allows the user to concentrate on specifying what data is required rather than concentrating on the how to get it.

The DML component of SQL comprises four basic statements:
* SELECT to get rows from tables
* UPDATE to update the rows of tables
* DELETE to remove rows from tables
* INSERT to add new rows to tables

Question: What is DTS in SQL Server?
Answer: If a organization is big then it is also there that there is multiple option to store data some people are using EXCEL some are using ACCESS and some of they are using SQL SERVER and in some other format also but there a problem is arise that how to merge that data into one format there is different tool are there for doing this function. One of product of SQL SERVER-2000 DTS helps in this problem it provides a set of tool from that tool we can customize are database according to our need DTSRun is a command-prompt utility used to execute existing DTS packages.

Question: What is the difference between SQL and Pl/Sql?
Answer: We can get modify, Retrieve by single command or statement in SQL but PL/SQL process all SQL statements one at a time. With PL/SQL, an entire block of statements process in a single command line. sql is structured query language ,various queries are used to handle the database in a simplified manner. While pl/sql is procedural language contains various types of variable, functions and procedures and other major difference is Sql as the name suggest it is just structured query language whereas PLSQL is a combination of Programming language & SQL.

Question: Can You explain integration between SQL Server 2005 and Visual Studio 2005?
Answer: This integration provide wider range of development with the help of CLR for database server. Because CLR helps developers to get flexibility for developing database applications and also provides language interoperability just like Visual C++, Visual Basic .Net and Visual C# .Net. The CLR helps developers to get the arrays, classes and exception handling available through programming languages such as Visual C++ or Visual C# which is use in stored procedures, functions and triggers for creating database application dynamically and also provide more efficient reuse of code and faster execution of complex tasks. We particularly liked the error-checking powers of the CLR environment, which reduces run-time errors

Question: What are Checkpoint in SQL Server?
Answer: When we done operation on SQL SERVER that is not committed directly to the database. All operation must be logged in to Transaction Log files after that they should be done on to the main database. Checkpoint are the point which alert Sql Server to save all the data to main database if no check point is there then log files get full we can use Checkpoint command to commit all data in the SQL SERVER. When we stop the SQL Server it will take long time because Checkpoint is also fired.

Question: What is the difference between UNION ALL Statement and UNION?
Answer:- The main difference between UNION ALL statement and UNION is UNION All statement is much faster than UNION, the reason behind this is that because UNION ALL statement does not look for duplicate rows, but on the other hand UNION statement does look for duplicate rows, whether or not they exist.

Question: Write some disadvantage of Cursor?
Answer:- Cursor plays there row quite nicely but although there are some disadvantage of Cursor .
Because we know cursor doing roundtrip it will make network line busy and also make time consuming methods. First of all select query gernate output and after that cursor goes one by one so roundtrip happen.Another disadvange of cursor are ther are too costly because they require lot of resources and temporary storage so network is quite busy.

Question: What is Log Shipping and its purpose?
Answer: In Log Shipping the transactional log file from one server is automatically updated in backup database on the other server and in the case when one server fails the other server will have the same DB and we can use this as the DDR(disaster recovery) plan.

Question: What are the null values in SQL SERVER?
Answer: Before understand the null values we have some overview about what the value is. Value is the actual data stored in a particular field of particular record. But what is done when there is no value in the field. That value is something like <null>.Nulls present missing information. We can also call null propagation.

Question: What is difference between OSQL and Query Analyzer?
Answer: Both are same for functioning but there is a little difference OSQL is command line tool which execute query and display the result same a Query Analyzer do but Query Analyzer is graphical.OSQL have not ability like Query Analyzer to analyze queries and show statistics on speed of execution .And other useful thing about OSQL is that its helps in scheduling which is done in Query Analyzer with the help of JOB.

Question: Write a Role of Sql Server 2005 in XML Web Services?
Answer:- SQL Server 2005 create a standard method for getting the database engine using SOAP via HTTP. By this method, we can send SOAP/HTTP requests to SQL Server for executing T-SQL batch statements, stored procedures, extended stored procedures, and scalar-valued user-defined functions may be with or without parameters.

Question: What are the different types of Locks ?
Answer: There are three main types of locks that SQL Server
(1)Shared locks are used for operations that does not allow to change or update data, such as a SELECT statement.
(2)Update locks are used when SQL Server intends to modify a page, and later promotes the update page lock to an exclusive page lock before actually making the changes.
(3)Exclusive locks are used for the data modification operations, such as UPDATE, INSERT, or DELETE.

Question: What is ‘Write-ahead log’ in Sql Server 2000?
Answer: Before understanding it we must have an idea about the transaction log files. These files are the files which hold the data for change in database.
Now we explain when we are doing some Sql Server 2000 query or any Sql query like Sql insert query, delete sql query, update sql query and change the data in sql server database it cannot change the database directly to table .Sql server extracts the data that is modified by sql server 2000 query or by sql query and places it in memory. Once data is stores in memory user can make changes to that a log file is generated this log file is generated in every five minutes of transaction is done. After this sql server writes changes to database with the help of transaction log files. This is called Write-ahead log.

Question: What do u mean by Extents and types of Extents ?
Answer: An Extent is a collection of 8 sequential pages to hold database from becoming fragmented. Fragment means these pages relates to same table of database these also holds in indexing. To avoid for fragmentation Sql Server assign space to table in extents. So that the Sql Server keep up to date data in extents. Because these pages are continuously one after another. There are usually two types of extends:-Uniform and Mixed.
Uniform means when extent is own by a single object means all collection of 8 ages hold by a single extent is called uniform.
Mixed mean when more then one object is comes in extents is known as mixed extents.

Question: What is different in Rules and Constraints?
Answer: Rules and Constraints are similar in functionality but there is little difference between them. Rules are used for backward compatibility. One the most exclusive difference is that we can bind rules to a data types whereas constraints are bound only to columns. So we can create our own data type with the help of Rules and get the input according to that.

Question: What Is Database?
Answer: A database is similar to a data file in that it is a storage place for data. Like a data file, a database does not present information directly to a user; the user runs an application that accesses data from the database and presents it to the user in an understandable format. Database systems are more powerful than data files in that data is more highly organized. In a well-designed database, there are no duplicate pieces of data that the user or application must update at the same time. Related pieces of data are grouped together in a single structure or record, and relationships can be defined between these structures and records. When working with data files, an application must be coded to work with the specific structure of each data file. In contrast, a database contains a catalog that applications use to determine how data is organized. Generic database applications can use the catalog to present users with data from different databases dynamically, without being tied to a specific data format. A database typically has two main parts: first, the files holding the physical database and second, the database management system (DBMS) software that applications use to access data. The DBMS is responsible for enforcing the database structure, including: · maintaining relationships between data in the database. Ensuring that data is stored correctly and that the rules defining data relationships are not violated. · Recovering all data to a point of known consistency in case of system failures.

Question: what is Relational Database?
Answer: Although there are different ways to organize data in a database, relational databases are one of the most effective. Relational database systems are an application of mathematical set theory to the problem of effectively organizing data. In a relational database, data is collected into tables (called relations in relational theory). A table represents some class of objects that are important to an organization. For example, a company may have a database with a table for employees, another table for customers, and another for stores. Each table is built of columns and rows (called attributes and tuples in relational theory). Each column represents some attribute of the object represented by the table. For example, an Employee table would typically have columns for attributes such as first name, last name, employee ID, department, pay grade, and job title. Each row represents an instance of the object represented by the table. For example, one row in the Employee table represents the employee who has employee ID 12345. When organizing data into tables, you can usually find many different ways to define tables. Relational database theory defines a process called normalization, which ensures that the set of tables you define will organize your data effectively.

Question: What are Data Integrity and its categories?
Answer: Enforcing data integrity ensures the quality of the data in the database. For example, if an employee is entered with an employee_id value of 123, the database should not allow another employee to have an ID with the same value. If you have an employee_rating column intended to have values ranging from 1 to 5, the database should not accept a value of 6. If the table has a dept_id column that stores the department number for the employee, the database should allow only values that are valid for the department numbers in the company. Two important steps in planning tables are to identify valid values for a column and to decide how to enforce the integrity of the data in the column. Data integrity falls into these categories:
1) Entity integrity
2) Domain integrity
3) Referential integrity
4) User-defined integrity
Entity Integrity: Entity integrity defines a row as a unique entity for a particular table. Entity integrity enforces the integrity of the identifier column(s) or the primary key of a table (through indexes, UNIQUE constraints, PRIMARY KEY constraints, or IDENTITY properties).
Domain Integrity: Domain integrity is the validity of entries for a given column. You can enforce domain integrity by restricting the type (through data types), the format (through CHECK constraints and rules), or the range of possible values (through FOREIGN KEY constraints, CHECK constraints, DEFAULT efinitions, NOT NULL definitions, and rules).
Referential Integrity: Referential integrity preserves the defined relationships between tables when records are entered or deleted. In Microsoft® SQL Server™ 2000, referential integrity is based on relationships between foreign keys and primary keys or between foreign keys and unique keys (through FOREIGN KEY and CHECK constraints). Referential integrity ensures that key values are consistent across tables. Such consistency requires that there be no references to nonexistent values and that if a key value changes, all references to it change consistently throughout the database. When you enforce referential integrity, SQL Server prevents users from:
· Adding records to a related table if there is no associated record in the primary table.
· Changing values in a primary table that result in orphaned records in a related table.
· Deleting records from a primary table if there are matching related records.
For example, with the sales and titles tables in the pubs database, referential integrity is based on the relationship between the foreign key (title_id) in the sales table and the primary key (title_id) in the titles table.
User-Defined: Integrity User-defined integrity allows you to define specific business rules that do not fall into one of the other integrity categories. All of the integrity categories support user-defined integrity (all column- and table-level constraints in CREATE TABLE, stored procedures, and triggers).

Question: SQL Server runs on which TCP/IP port and From where can you change the default port?
Answer: SQL Server runs on port 1433 but we can also change it for better security and From the network Utility TCP/IP properties –>Port number.both on client and the server.

Question: What is the use of DBCC commands?
Answer: DBCC stands for database consistency checker. We use these commands to check the consistency of the databases, i.e., maintenance, validation task and status checks.DBCC CHECKDB – Ensures that tables in the db and the indexes are correctly linked.and DBCC CHECKALLOC To check that all pages in a db are correctly allocated. DBCC SQLPERF – It gives report on current usage of transaction log in percentage. DBCC CHECKFILEGROUP – Checks all tables file group for any damage.

Question: What is the difference between a HAVING CLAUSE and a WHERE CLAUSE?
Answer: Having Clause is basically used only with the GROUP BY function in a query. WHERE Clause is applied to each row before they are part of the GROUP BY function in a query.

Question: When do you use SQL Profiler?
Answer: SQL Profiler utility allows us to basically track Connections to the SQL Server and also determine activities such as which SQL Scripts are running, failed jobs etc.

Question: Can you explain the role of each service?
Answer: SQL SERVER – is for running the databases SQL AGENT – is for automation such as Jobs, DB Maintenance, Backups DTC – Is for linking and connecting to other SQL Servers.

Question: What is Normalization ?
Answer: The logical design of the database, including the tables and the relationships between them, is the core of an optimized relational database. A good logical database design can lay the foundation for optimal database and application performance. A poor logical database design can impair the performance of the entire system.

Normalizing a logical database design involves using formal methods to separate the data into multiple, related tables. A greater number of narrow tables (with fewer columns) is characteristic of a normalized database. A few wide tables (with more columns) is characteristic of an nonnomalized database. Reasonable normalization often improves performance. When useful indexes are available, the Microsoft® SQL Server™ 2000 query optimizer is efficient at selecting rapid, efficient joins between tables.

Some of the benefits of normalization include:
·Faster sorting and index creation.
·A larger number of clustered indexes. For more information, Narrower and more compact indexes.
·Fewer indexes per table, which improves the performance of INSERT, UPDATE, and DELETE statements.
·Fewer null values and less opportunity for inconsistency, which increase database compactness.

As normalization increases, so do the number and complexity of joins required to retrieve data. Too many complex relational joins between too many tables can hinder performance. Reasonable normalization often includes few regularly executed queries that use joins involving more than four tables.

Sometimes the logical database design is already fixed and total redesign is not feasible. Even then, however, it might be possible to normalize a large table selectively into several smaller tables. If the database is accessed through stored procedures, this schema change could take place without affecting applications. If not, it might be possible to create a view that hides the schema change from the applications.

Question: Can you explain what View is in SQL ?
Answer: View is just a virtual table nothing else which is based or we can say devlop with SQL SELECT query. So we can say that its a real database table (it has columns and rows just like a regular table),but one difference is that real tables store data,but views can’t. View data is generated dynamically when the view is referenced.And view can also references one or more existing database tables or other views. We can say that it is filter of database.

Question: How to get which Process is Blocked in SQL SERVER ?
Answer:- There are two ways to get this sp_who and sp_who2 . You cannot get any detail about the sp_who2 but its provide more information then the sp_who . And other option from which we can find which process is blocked by other process is by using Enterprise Manager or Management Studio, these two commands work much faster and more efficiently than these GUI-based front-ends.

Wishing you all Very happy and prosperous new year

How do we see the SQL Plan in textual format?

SQL Server 2005 Questions and Answers

4 Replies

1. What does integration of .NET Framework mean for SQL Server 2005?

This feature enables us to execute C# or VB.NET code in the DBMS to take advantage of the .NET functionality. This feature gives more flexibility in writing complex stored procedures, functions, and triggers that can be written in .net compatible language.

2. What is SSIS?

According to Microsoft SQL Server Integration Services, “(SSIS) is an effective set of tools for both the traditional demands of ETL operations, as well as for the evolving needs of general purpose data integration.” In short, it is the next version of DTS (Data Transformation Services). ETL stands for Extract, Transform and Loading. In short it is a data migration tool that is flexible, fast, and has scalable architecture that enables effective data integration in current business environments.

3. What is MARS?

In previous versions of SQL Server, applications had to process or cancel all result sets from one batch before it could execute any other batch on that connection. SQL Server 2005 introduces a new connection attribute that allows applications to have more than one pending request per connection, and in particular, to have more than one active default result set per connection. Multiple Active Result Sets (MARS) is the ability to have more than one pending request under a given SQL Server connection. MARS is a programming model enhancement that allows multiple requests to interleave in the server. We need to note that it is not a parallel execution in the server. However, it may benefit us with some performance benefits if used correctly. By default, this feature is not set in SQL Server 2005.

4. What are the Security Enhancements in SQL Server 2005?

SQL Server 2005 enables administrators to manage permissions at a granular level.

-> In the new SQL Server 2005, we can specify a context under which statements in a module can execute.

-> SQL Server 2005 clustering supports Kerberos authentication against a SQL Server 2005 virtual server.

-> Administrators can specify Microsoft Windows-style policies on standard logins so that a consistent policy is applied across all accounts in the domain.

-> SQL Server 2005 supports encryption capabilities within the database itself, fully integrated with a key management infrastructure. By default, client-server communications are encrypted.

5. What is new with the Reporting services in SQL server 2005?

SQL Server 2005 Reporting Services is a key component of SQL Server 2005 that provides customers with an enterprise-capable reporting platform. This comprehensive environment is used for authoring, managing, and delivering reports to the entire organization. SQL Server 2005 reporting services have some major changes when compared with the previous version.

-> Changes to the core functionality of the Reporting services in the design of the report, processing, and interactivity

-> Better Integration with other components – Enhanced integration with other components within SQL Server 2005 like SSIS, SSAS and SQL Server Management studio

-> Report Builder – A new reporting tool that enables business users to create their own reports

6. What is OLAP?

Online Analytical Processing (OLAP) allows us to access aggregated and organized data from business data sources, such as data warehouses, in a multidimensional structure called a cube. The arrangement of data into cubes avoids a limitation of relational databases which are not well suited for near instantaneous analysis of large amounts of data. OLAP cubes can be thought of as extensions to the two-dimensional array of a spreadsheet.

7. What is Data Mining?

According to MSDN Data, mining is “the process of extracting valid, authentic, and actionable information from large databases.” Microsoft data mining tools are different from traditional data mining applications in significant ways. Data Mining is a platform for developing intelligent applications, not a stand-alone application. You can build custom applications that are intelligent because the data mining models are easily accessible to the outside world. Further, the model is extensible so that third parties can add custom algorithms to support particular mining needs.

8. What is new with the Analysis Services (SSAS) in SQL Server 2005?

SQL Server 2005 Analysis Services (SSAS) delivers online analytical processing (OLAP) and data mining functionality through a combination of server and client technologies, further reinforced through the use of a specialized development and management environment coupled with a well-defined object model for designing, creating, deploying, and maintaining business intelligence applications. The server component of Analysis Services is implemented as a Microsoft Windows service. Clients communicate with Analysis Services using the public standard XML for Analysis (XMLA), a SOAP-based protocol. Let us see the enhancements of made to SSAS.

· Supports up to 16 instances of Analysis Services Service.

· As discussed above, the Analysis Services service fully implements the XML for Analysis (XMLA) 1.1 specification. All communication with an instance of Analysis Services is handled through XMLA commands in SOAP messages.

· Uses the Proactive caching.

9. What is Information Schema in SQL Sever 2005?

Information Schema is the part of the SQL- 92 standard which exposes the metadata of the database. In SQL server, a set of views are created in each of the databases which exposes the metadata of the database. The information schema is kept in a separate schema – information schema – which exists in all databases, but which is not included in the search path by default. For more information regarding Information schema please read this article.

10. What is Full Text Search? How does it get implemented in SQL server 2005?

Full-text search allows fast and flexible indexing for keyword-based query of text data stored in a Microsoft SQL Server database. In contrast to the LIKE predicate which only works on character patterns, full-text queries perform linguistic searches against this data, by operating on words and phrases based on rules of a particular language.

11. What is integration of Microsoft Office System mean?

The integration with Microsoft Office system means the following.

· Table Analysis Tools for Excel: Provides an easy-to-use add-in that leverages SQL Server 2005 Data Mining behind the scenes to perform powerful end user analysis on spreadsheet data.

· Data Mining Client for Excel: Offers a full data mining model development lifecycle directly within Excel 2007.

· Data Mining Templates for Visio: Enables powerful rendering and sharing of mining models as annotatable Visio 2007 drawings.

12. What is the support of Web Services in SQL Server 2005?

With this feature the database engine can be directly exposed as a web service without a middle tier or even an IIS. This will enable the user to directly call a stored procedure by calling a web method. This feature is designed with well-known standards such as SOAP 1.2, WSDL 1.1, and HTTP. With this new feature we can now connect to SQL Server not only with TDS- Tabular data stream (a binary protocol for connecting to SQL Server 2005) but also over SOAP/ HTTP.

13. What is OLTP?

Online Transaction Processing (OLTP) relational databases are optimal for managing changing data. When several users are performing transactions at the same time, OLTP databases are designed to let transactional applications write only the data needed to handle a single transaction as quickly as possible.

14. What is Snapshot in SQL Server 2005?

A database snapshot is a read-only, static view of a database, the source database. Each database snapshot is transaction-consistent with the source database as it existed at the time of the snapshot’s creation.

15. What is snapshot isolation in SQL Server 2005?

SQL Server 2005 introduces a new “snapshot” isolation level that is intended to enhance concurrency for online transaction processing (OLTP) applications. In prior versions of SQL Server, concurrency was based solely on locking, which can cause blocking and deadlocking problems for some applications. Snapshot isolation depends on enhancements to row versioning and is intended to improve performance by avoiding reader-writer blocking scenarios.

16. What is Database Partitioning in SQL Server 2005?

SQL Server 2005 provides a new capability for the partitioning of tables across file groups in a database. Partitioning a database improves performance and simplifies maintenance. By splitting a large table into smaller, individual tables, queries accessing only a fraction of the data can run faster because there is less data to scan.

17. What is SQL Server Agent?

SQL Server Agent is a Microsoft Windows service that executes scheduled administrative tasks called jobs. SQL Server Agent uses SQL Server to store job information. Jobs contain one or more job steps. We generally schedule the backups on the production databases using the SQL server agent. In SQL Server 2005 we have roles created for using SQL Server agents.

· SQLAgentUserRole

· SQLAgentReaderRole

· SQLAgentOperatorRole

SQL Server Agent for SQL Server 2005 provides a more robust security design than earlier versions of SQL Server. This improved design gives system administrators the flexibility they need to manage their Agent service.

18. What is Replication? What is the need to have the replication? What are the enhancements made to SQL Server 2005 related to the replication?

“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.” In short, replication is all about having multiple copies of the same database. We need replication when we need to distribute data to and from different locations. Generally we have a master copy of data. There will be multiple slaves (Clients) located at various locations which need to be replicated. We use replication for a variety of reasons. Load balancing is sharing the data among a number of servers and distributing the query load. Offline processing is one of the main reasons. In this scenario we need to modify the data on the database that is not connected to the network. The last reason may be to have a back-up to the database in case of failure to the existing database. Let us see the enhancements of SQL server 2005 database related to replication.

· Database Mirroring – Database Mirroring is moving the transactions of database from one SQL Server database to another SQL server database on a different SQL Server.

· Replication Management topology (RMO) – RMO is a new construct in SQL Server 2005. It is a .NET Framework library that provides a set of common language runtime classes for configuring, managing, and scripting replication, and for synchronizing Subscribers.

19. What are Business Logic Handlers?

Business logic handlers are written in managed code and allow us to execute custom business logic during the merge synchronization. We can invoke the business logic handler in case of non-conflicting data changes. Business logic handler can perform one of the following three actions.

· Reject Data

· Accept Data

· Apply Custom Data

20. What are different variants of SQL Server 2005?

There are different variants of SQL Server 2005 commercially available.

· Express – Free and only for one user

· Enterprise – 5 users apart from server

· Workgroup – 10 users apart from server

· Standard – 25 users apart from server

21. What are Various Service packs available for SQL Server 2005?

As of now there are two service packs available for the SQL Server 2005.

· Service Pack 1 – Has major changes or enhancements to SQL Server 2005 in Analysis Services, Data Programmability, SSIS, and reporting services.

· Service Pack 2 – Unlike Service Pack 2, this service pack enables SQL Server 2005 customers to take advantage of the enhancements within Windows Vista and the 2007 Office system.

22. What are the New Data types introduced in SQL Server 2005?

SQL Server 2005 has added some new data types to its existing data types.

. XML Data type

· VARCHAR (MAX)

· NVARCHAR (MAX)

· VARBINARY (MAX)

As we can see, the new term MAX has been introduced in SQL Server 2005. This new specifier expands the storage capabilities of the varchar, nvarchar, and varbinary data types. Varchar(max), nvarchar(max), and varbinary(max) are collectively called large-value data types.

23. Does SQL Server 2005 support SMTP?

SQL Server 2005 now supports sending E-mail from the database. It is called as database mail and it uses DatabaseMail90.exe. Gone are the days when we were using a third party component for this. Receiving an e-mail was not supported in the previous versions of SQL Server.

24. What is SQL Management Object is SQL Server 2005?

These are collection of objects that are made for programming all aspects of managing Microsoft SQL Server 2005. SMO is a .NET based object model. It comes with SQL Server 2005 as a .Net assembly named Microsoft.SqlServer.Smo.dll. We can use these objects for connecting to a database, calling methods of the database that returns a table, using transactions, transferring data, scheduling administrative tasks, etc. The best part about SMO is that most of it can also be used with SQL server 2000.

25. What is SQL Service Broker in SQL Server 2005?

SQL Service broker is a new technology introduced in SQL Server 2005 for building database-intensive distributed applications. Basically, service broker has been built for developing applications that consist of individual components which are loosely coupled. Service broker supports asynchronous yet reliable messages that are passed between the components. These messages are called conversations.

DBA Roles and Responsibilities

221 Replies

Click Me to Check How to be DBA?

DBA Responsibilities

Installation, configuration and upgrading of Microsoft SQL Server/MySQL/Oracle server software and related products.
Evaluate MSSQL/MySQL/Oracle features and MSSQL/MySQL/Oracle related products.
Establish and maintain sound backup and recovery policies and procedures.
Take care of the Database design and implementation.
Implement and maintain database security (create and maintain users and roles, assign privileges).
Database tuning and performance monitoring.
Application tuning and performance monitoring.
Setup and maintain documentation and standards.
Plan growth and changes (capacity planning).
Work as part of a team and provide 7×24 supports when required.
Do general technical trouble shooting and give consultation to development teams.
Interface with MSSQL/MySQL/Oracle for technical support.
ITIL Skill set requirement (Problem Management/Incident Management/Chain Management etc)

Types of DBA

Administrative DBA – Work on maintaining the server and keeping it running. Concerned with backups, security, patches, replication, etc. Things that concern the actual server software.
Development DBA – works on building queries, stored procedures, etc. that meet business needs. This is the equivalent of the programmer. You primarily write T-SQL.
Architect – Design schemas. Build tables, FKs, PKs, etc. Work to build a structure that meets the business needs in general. The design is then used by developers and development DBAs to implement the actual application.
Data Warehouse DBA – Newer role, but responsible for merging data from multiple sources into a data warehouse. May have to design warehouse, but cleans, standardizes, and scrubs data before loading. In SQL Server, this DBA would use DTS heavily.
OLAP DBA – Builds multi-dimensional cubes for decision support or OLAP systems. The primary language in SQL Server is MDX, not SQL here

Application DBA- Application DBAs straddle the fence between the DBMS and the application software and are responsible for ensuring that the application is fully optimized for the database and vice versa. They usually manage all the application components that interact with the database and carry out activities such as application installation and patching, application upgrades, database cloning, building and running data cleanup routines, data load process management, etc.