Advanced SQL Server Interview Questions

I came across several questions during an Interview for SQL admin; I thought other folks might also benifit from it.

And I will try to add more to the list as long as I am being Interviewed! ;)

• Question: When you failover an SQL Server from node1 to node 2, do all processes/users automatically shifted to node 2?
• Jobs running on node 1 will fail and do not automatically Failover to node2. You might need to run those jobs manually after the Failover.
• 
  
• Question: How can you move a physical table from one disk to another (using filegroup),
• Recreate the clustered index of the table on the destination disk (filegroup), the physical table automatically moves along with the clustered index to the new disk.
• 
  
• Question: When you upgrade a DB what thing doesn't automatically get updated?
• run UPDATE STATS manually because statistics are not automatically updated upon upgrade.
• 
  
• Question: Is it fine to have a fill factor of value 100 by default when creating an index?
• Do not automatically accept default fill factor value of 100 unless your data seldom changes. You might need a lower fill factor value for OLTP db to reduce page splitting and save I/O.
• 
  
• Question: Is it fine to have SQL startup account as a member of Builtin/Administrators group, if not then what explicit rights might be required for a startup account with limited rights?
• 
  
• Though it’s recommended that SQL start up server account should not be domain admin, and should have limited rights to avoid risking complete machine/network in case of injection/intrusion; You might need LOCK PAGES IN MEMORY setting to be explicitly allowed for the SQL start up account in order to start the SQL service.
• 
  
• Question: How do we replicate dbs over WAN with limited internet bandwidth and is it possible to setup a single large publication in that case?
• While replicating Large DBs over WAN, you need to setup ftp options on publication so the snapshot could manually be uploaded on ftp for subscriber to download. You can not create a snapshot file (.cab) over 2 GB, so if you have large tables; use multiple publications with less than 2 GB snapshot cab file.
• 
  
• Question: How can we brwose SQL configuration options without wanting to enable advanced options
• SELECT * FROM SYS.CONFIGURATIONS
• 
  
• Question: What is the recommended size to set when setting up a disk or RAID for an SQL database
• Optimum Block size to set when formatting a disk or setting up RAID is 64 K for SQL database, as SQL Server basic data storage is in Extents which are of 64 K.
• 
  
• Question: Is it a problem if your NTFS disk is 90% used?
• NTFS needs room to work,and when you exceed 80% capacity, NTFS become less efficient and I/O cansuffer for it. Keeping adequate free space onf the disk will prevent the MTF from becoming fragmented, which is important because the Disk Defragmenter tool can't defragment the MFT.
• 
  
• Question: In SQL 2000, we know that page size in SQL storage structue is 8064 bytes out which 4 is header information. what's the actual row size in a data page?
• The actual max row size in SQL 2000 is 8039 bytes, not 8060. A table to hold a row of 8040 bytes is not possible and the following command will fail:
• CREATE TABLE ROWSIZE ( address CHAR(8000), name CHAR (40) )
• 
  
• Question: In SMS executing the below command; what will be the output.
  SELECT @@version
• GO 5
• It will execute the select command five times.
• 
  
• Question: Does enabling AWE on 32 bit OS means better Procedure Cache?
• SQL on 32 bit OS with AWE eabled still can't address more than 4 GB of virtual address space. Only buffer cache can benifit from more RAM on 32 bit with AWE enabled.
• 
  
• Question: Does enabling AWE on 64bit OS means better Procedure Cache?
• True, however one of the Draw back of SQL on 64 bit OS is that we can not manage procedure cache size and SQL server may prefer to keep large expensive plans in cache and it drains out memory. And a larger procedure cache may also impact the time for SQL to search the procedure cache to determine if a plan exists.
• 
  
• Question: What's the purpose of AWE enable option in SQL 2005 64 bit
• On 64 bit OS AWE isn't ignored (as per several msdn posts) " locked pages, awe mechanism, enable better application's scalability and performance both during ramp up time and over long period of time." http://blogs.msdn.com/slavao/archive/2005/04/29/413425.aspx
• 
  
• Question: How can we handle an SQL statement to rollback if it gets blocked, instead of waiting for the blocking process to commit or rollback?
• By using SET LOCK_TIMEOUT
• 
  
• Question: What's wrong with the following queries:

SELECT name FROM DepartmentsTable ORDER BY id
SELECT DISTINCT name FROM DepartmentsTable ORDER BY id

• Second Query will return error because when using DISTINCT all columns should be in SELECT statement referred in ORDER BY cluase.