Top posts
-
What is a page in database systems and tell me something about pages?
What is a page in database systems and tell me something about pages? Ans: The fundamental unit of data storage in SQL Server is the page. Page size is 8kb means 128 pages = 1 MB. Page starts with the header of 96 bytes that is used to store page number,...
-
What is “Write Ahead Transaction Log”?
What is “Write Ahead Transaction Log”? Ans: SQL Server uses a write-ahead log (WAL), which guarantees that no data modifications are written to disk before the associated log record is written to disk. Data modifications are not made directly to disk,...
-
Infosys SQL DBA
1. Are tables locked during generating the snapshot? 2. Does Truncate Works in replication? What are the limitations of Truncate command? 3. Causes for slow replication? 4. How to add articles to existing publication without generating a full snapshot?...
-
Pythian SR SQL DBA
1. How many IP’s required configuring a 2 node cluster? 2. How many MSDTC required for a 2 node cluster? 3. What is the basic difference between 2005 and 2008 in clustering? 4. What’s the use of quorum in clustering? 5. Your customer complained that one...
-
TEch Mahindra SQL DBA INTERVIEW
1. Why can’t we take a backup of a database which is in standby or in recovery mode? 2. How to move a database which is acting as a publisher in transactional replication? 3. What is CROSS APPLY? 4. How to find and remove duplicates from a table which...
-
How to change the sql server service account in a cluster environment?
Q. How to change the sql server service account in a cluster environment? Ans: Method 1: (No failover required) 1. Freeze the service group on active node from cluster administrator and then restart the service. Method2: 1. Offline the SQL resources 2....
-
What is a page in database systems and tell me something about pages?
What is a page in database systems and tell me something about pages? Ans: The fundamental unit of data storage in SQL Server is the page. Page size is 8kb means 128 pages = 1 MB. Page starts with the header of 96 bytes that is used to store page number,...
-
capital IQ sql server Interview
1. An indexed view is referring only one base table. Both view and table are having index defined on them. Which index would be utilized when a query executed against the table. 2. I have an indexed view, now base table data has been modified, does the...
-
POLARIS SQL DBA LEAD
1. What the issues you find in migrating databases 2. What is the schedule frequency of your transactional replication? 3. How do check the application compatibility with sql server? 4. How do you know whether statistics are latest or expired?...
-
What are the agents in replication?
What are the agents in replication? Ans: Snapshot Agent: Copy Schema+Data to snapshot folder on distributer. Used in all types of replication. Log reader Agent: Sends transactions from Publisher to Distributor. Used in transactional replication Distribution...
-
Can we perform a tail log backup if .mdf file is corrupted?
Can we perform a tail log backup if .mdf file is corrupted? Ans: Yes we can perform a tail log as long as the ldf if not corrupted and no bulk logged changes. A typical tail log backup is having two options, 1. WITH NORECOVERY 2.Continue After Error....
-
SQL SERVER SECURITY
1. What is the Guest user account in SQL Server? What login is it mapped to it? Ans: The Guest user account is created by default in all databases and is used when explicit permissions are not granted to access an object. It is not mapped directly to...
-
What are the phases of sql server database restore process?
What are the phases of sql server database restore process? Ans: 1. Copy Data: Copies all data,log and index pages from backup file to database mdf, ndf and ldf files 2. REDO: Rollfoward all committed transactions to database and if it finds any...
-
What are the different log files and how to access it?
What are the different log files and how to access it? SQL Server Error Log: The Error Log, the most important log file, is used to troubleshoot system problems. SQL Server retains backups of the previous six logs, naming each archived log file sequentially....
-
SQL Commands
DDL Data Definition Language (DDL) statements are used to define the database structure or schema. Some examples: CREATE - to create objects in the database ALTER - alters the structure of the database DROP - delete objects from the database TRUNCATE...
-
Update and Delete in Correlated Sub query
Update and Delete in Correlated Sub query Correlated sub queries as you all know are used for row-by-row processing. Here each sub query is executed once for every row of the outer query. The oracle server performs a correlated subquery when the subquery...
-
Examlpes For Complex Views
Examlpes For Complex Views Here are few examples of complex views in Oracle, a)Create Or Replace View Dept_Sum_Vu (Name,Minsal,Maxsal,Avgsal) As Select D.Dname,Min(E.Sal),Max(E.Sal),Avg(E.Sal) From Emp E,Dept D Where E.Deptno=D.Deptno Group By D.Dname...
-
Commit, Rollback and Savepoint in oracle
Explain Commit, Rollback and Savepoint. For a COMMIT statement, the following is true: Other users can see the data changes made by the transaction. The locks acquired by the transaction are released. The work done by the transaction becomes permanent....
-
What are the advantages and disadvantages of merge replication?
What are the advantages and disadvantages of merge replication? Advantages It provides built-in and custom conflict resolution capabilities. It allows for the synchronization of data from multiple tables at one time. It provides rich data replication...
-
SQL%BULK_ROWCOUNT
SQL%BULK_ROWCOUNT The SQL%BULK_ROWCOUNT cursor attribute gives granular information about the rows affected by each iteration of the FORALL statement. Every row in the driving collection has a corresponding row in the SQL%BULK_ROWCOUNT cursor attribute....
-
SQL Statement to improve performance
SQL queries under workspace can suffer performance issues. Performance can affect due to huge SQL statements. Simpler SQL statements require less processing and generally perform better than complex statements. If you understand the purpose of statement...
-
Refreshing materialized views
Refreshing materialized views In Oracle, if you specify REFRESH FAST for a single-table aggregate Oracle materialized view, you must have created a materialized view log for the underlying table, or the refresh command will fail. When creating an Oracle...
-
How to drop package body only?
DROP PACKAGE [BODY] The DROP PACKAGE statement removes a stored package from the database. Both the specification and the body are dropped. DROP PACKAGE BODY removes only the body of the package. Required privilege No privilege is required for the package...
-
How to debug the procedure or function?
How to debug the procedure or function? By using DBMS_OUTPUT & DBMS_DEBUG oracle supplied package. ALTER PROCEDURE procedurename DEBUG; OR ALTER PROCEDURE procedurename PLSQL_DEBUG=TRUE OR We can use autonomous_transaction procedure to create log & put...
-
What is bulk collect?
What is bulk collect? This keyword ask the SQL engine to return all the rows in one or several collections before returning to the PL/SQL engine so, there is one single roundtrip for all the rows between SQL and PL/SQL engine. BULK COLLECT cannot be use...