:::: MENU ::::

Posts Categorized / DBA Interview questions

  • Aug 06 / 2008
  • 1
Cursors, DBA Interview questions, dbDigger, Performance Tunning and Optimization

Some thing good about cursors performance

Mostly cursors are notorious for performance degradation and resource consumption. And are used as last option always. But there is situation when cursors may help you to boost the performance. If your application or procedure requires the repeated use of a set of records,it is faster to create a cursor once and reuse it several times than to repeatedly query the database. But do not forget to properly close the cursor at end and also deallocate the resources.
Follow these steps to create, use, and close a database cursor:

  • Create the cursor
  • Open the cursor for use within the procedure or application
  • Fetch a record’s data one row at a time until you have reached the end of the cursor’s records
  • Close the cursor when you are finished with it
  • Deallocate the cursor to completely discard it

Consider the following example of cursor created for pubs..authors

 
DECLARE @fName VARCHAR(40)  
 DECLARE @lName VARCHAR(40)  
 DECLARE @city VARCHAR(40)  
 DECLARE OAKLAND CURSOR FOR  
 SELECT au_lname, au_fname, city  
 FROM pubs..authors  
 OPEN OAKLAND  
 FETCH NEXT FROM OAKLAND INTO @fName, @lName, @city  
 WHILE (@@FETCH_STATUS <> -1)  
 BEGIN  
 FETCH NEXT FROM OAKLAND INTO @fName, @lName, @city  
 IF (@city = 'OAKLAND')  
 BEGIN  
 PRINT @city  
 END  
 END  
 GO  
 CLOSE OAKLAND  
 DEALLOCATE OAKLAND  
 GO  

You may find more detail use of cursors in online books.

  • Jul 29 / 2008
  • 0
DBA Interview questions, dbDigger, Logins and Users, Security and Permissions

Purpose of the REFERENCES permission

Question: What is the purpose of the REFERENCES permission?

Answer: Allows the owner of another table to use columns in the table to which they’ve been granted that permission as part of a foreign key.

Explanation: Assigning REFERENCES permission allows the owner of another table to use columns in the table to which they’ve been granted that permission as the target of a REFERENCES FOREIGN KEY constraint with his or her table. However, that person won’t be allowed to change the structure of the table they’ve been granted the permission for.

Note: The Question of day is taken from SQLServercentral.com. I want to compile selected QOD.

  • Jul 09 / 2008
  • 0
Cursors, DBA Interview questions, dbDigger, Performance Tunning and Optimization

Cursor performance

Question: Of course, we all know that cursors are slow on SQL Server and should be avoided when possible. However, there are a few cases where they can’t be avoided, for instance because an external process has to be invoked for each row, or because you run into one of the very rare problems where a cursor actually runs faster than a set-based solution.

So, suppose you find yourself in a situation where you do need a cursor – which cursor option must be specified in order to assure maximum performance?

Answer: STATIC

Explanation: The default options are actually the slowest, since they create a dynamic cursor. In spite of its name, FAST_FORWARD does not result in the fastest performance; STATIC has been shown to be faster. Specifying FORWARD_ONLY or READ_ONLY on a static cursor will not affect performance.

Ref: DECLARE CURSOR

Note: The question of day is taken from SqlServerCentral.com. I want to compile selected QOD to share.

  • Jul 02 / 2008
  • 0
DBA Interview questions, dbDigger, Hardware and Platform, System Administration

Multi processors and multicore processors systems

Multiprocessor systems contain multiple CPUs that are not on the same chip. Today, multiprocessors are commonly found on the same physical board and connected through a high-speed communication interface. Multiprocessor systems are less complex than multicore systems, because they are essential single chip CPUs connected together. The disadvantage with multiprocessor systems is that they are expensive because they require multiple chips which is more expensive than a single chip solution.

  • May 21 / 2008
  • 0
DBA Interview questions, dbDigger, T-SQL Interview Questions

Basics and definition of OLEDB

OLE DB is a low-level, COM API that is used for accessing data. OLE DB is recommended for developing tools, utilities, or low-level components that need high performance.
The OLE DB Provider for SQL Server (SQLOLEDB) is a native, high performance provider that accesses the SQL Server TDS protocol directly.
SQLOLEDB exposes interfaces to consumers wanting access to data on one or more computers running an instance of Microsoft® SQL Server™ 2000 or SQL Server version 7.0 or earlier.
SQLOLEDB is installed with SQL Server 2000 and is recommended when developing new applications. MSDASQL is provided for backward compatibility only.

Consult us to explore the Databases. Contact us