SQL Server 2005 New Features Interview Questions

By: Jeremy Kadlec   |   Read Comments (2)   |   Related Tips: More > Interview Questions DBA

Problem

Staying up on the latest SQL Server technologies is a huge benefit to employers.  Many seek only the best and brightest to build solutions for the organization.  The general premise is the more knowledgeable their team is the more opportunities the organization will have to succeed.  With the release of SQL Server 2005 many new features were introduced, but not taken advantage of across the industry for one reason or another.  Break out of your SQL Server 2000 shell and test your knowledge on the SQL Server 2005 new feature set.

Solution

In this tip, the questions are there to read, but the answers are intentionally hidden to really test your skills. Once you read the question and have determined your answer, then highlight the answer below the question to see how you did. Good luck!

Question Difficulty = Easy

• Question 1: True or False - SQL Server 2005 was the first version of SQL Server supporting 64 bit builds.
  • False - SQL Server 2000 supports 64 bit builds of the relational engine.
  • Additional information: http://www.microsoft.com/servers/64bit/overview.mspx and Differences Between 64-bit and 32-bit Releases (64-bit)
• Question 2: What was the new system database introduced with SQL Server 2005 and what is its purpose?
  • The Resource database was introduced with SQL Server 2005 and it replaces some of the functionality that was previously supported by the Master database.
  • In SQL Server 2005, the Resource database physically stores all of the system objects that are queried in the 'sys' schema of every system and user defined database.
  • The Resource database is a read-only database that is updated with new releases (service packs, hot fixes, etc.) of SQL Server 2005.
  • Additional information: Resource Database and SQL Server 2005 Resource Database Values in Dynamic Management Views\Functions
• Question 3: Can you explain how to deploy an SSIS package?
  • A few different options exist to deploy SSIS packages.  The first option is to the build a deployment folder then copy and paste the directory to the intended SQL Server instance.  Once on the instance, it is necessary to run deployment manifest wizard to install the SSIS Package.  The second option is to use Import or Export Package option in SQL Server Management Studio.  A third option is to use the Save Copy of Package in the Business Intelligence Development Studio.  A final option is to use the dtutil command line utility to save the package to the SQL Server instance.
  • Additional information: Deploying a SQL Server 2000 DTS Package vs. a SQL Server 2005 Integration Services Package and Import, Export, Copy and Delete Integration Services Packages in SQL Server 2005
• Question 4: In Analysis Services, how can you automate the backups for these databases?
  • Create a SQL Server Agent Job with a command type of SQL Server Analysis Services Command and write the XML backup syntax to backup the databases.
  • Additional information:  Automating Backups for SQL Server 2005 Analysis Services Databases
• Question 5: What was the new programming language introduced in the relational engine?  How does this language get enabled?
  • The new programming language in the SQL Server 2005 relational engine is the Common Language Runtime (CLR).
  • The CLR needs to be enabled via the Surface Area Configuration Manager.
  • Additional information: CLR function to delete older backup and log files in SQL Server, CLR String Sort Function in SQL Server 2005 and Enabling xp_cmdshell in SQL Server 2005

Question Difficulty = Moderate

• Question 1: What are the two new commands to flip rows to columns and vice versa?  As a follow-up, before these commands were introduced what was a technique to meet this need?
  • Pivot and Unpivot were released with SQL Server 2005 to meet this need of flipping rows to columns and vice versa.
  • A previous technique was to use a temp table to match the format and then insert the data into the new format.
  • Additional information: Crosstab queries using PIVOT in SQL Server 2005
• Question 2: With SQL Server 2005, what is the new technique to manage errors in Integration Services?
  • With SQL Server 2005 Integration Services, Event Handlers were introduced.  These can be setup to capture errors and perform a specific operation such as writing the errors to a table.  Additionally, custom logging can be setup at the Control Flow level and perform custom logging as well.
  • Additional information:  Custom Logging in SQL Server Integration Services Packages
• Question 3: What was the new replication architecture introduced with SQL Server 2005 and can you explain how it works?
  • Peer to Peer replication was introduced with SQL Server 2005.
  • Peer to Peer replication consists of two or more peer nodes.  Every node acts as both a publisher and subscriber with the ability to change data on any node with the changes replicated to the remainder of the nodes.
  • Additional information: SQL Server 2005 Peer to Peer Replication
• Question 4: Can you explain the difference between the INTERSECT and EXCEPT operators?  What is the value of these commands over other techniques?
  • INTERSECT - gives you the final result set where values in both of the tables match
  • EXCEPT - gives you the final result set where data exists in the first dataset and not in the second dataset
  • The advantage of these commands is that it allows you to get a distinct listing across all of the columns such as the UNION and UNION ALL operators do without having to do a group by or do a comparison of every single column.
  • Additional information: Comparing Multiple SQL Server Datasets with the INTERSECT and EXCEPT operators
• Question 5: What is the new error handling technique in SQL Server 2005?  What technique does it replace?  What are the 3 of the new functions associated with this command?
  • The TRY and CATCH commands.  The TRY command has the T-SQL code with the business logic in the code block.  The CATCH command has the error handling logic.
  • The TRY and CATCH commands replace the RAISERROR command.
  • The TRY and CATCH functions are:
    • ERROR_NUMBER() - returns the number of the error.
    • ERROR_SEVERITY() - returns the severity.
    • ERROR_STATE() - returns the error state number.
    • ERROR_PROCEDURE() - returns the name of the stored procedure or trigger where the error occurred.
    • ERROR_LINE() - returns the line number inside the routine that caused the error.
    • ERROR_MESSAGE() - returns the complete text of the error message.
  • Additional information: SQL Server 2005 Try and Catch Exception Handling

Question Difficulty =  Difficult

• Question 1: Name 5 of the new Dynamic Management Views (DMV's) and the value that they provide to developers or DBAs.
  • sys.dm_tran_locks - Locking and blocking
  • sys.dm_clr_loaded_assemblies - Assemblies in available in SQL Server
  • sys.dm_db_file_space_usage - Database file usage to determine if databases are getting low on space and need immediate attention
  • sys.dm_exec_cached_plans - Cached query plans available to SQL Server
  • sys.dm_exec_sessions - Sessions in SQL Server
  • sys.dm_exec_connections - Connections to SQL Server
  • sys.dm_db_index_usage_stats - Seeks, scans, lookups per index
  • sys.dm_io_virtual_file_stats - IO statistics for databases and log files
  • sys.dm_broker_connections - Service Broker connections to the network
  • sys.dm_os_memory_objects - SQL Server memory usage sys.dm_tran_active_transactions - Transaction state for an instance of SQL Server
  • Additional information: Locking and Blocking Scripts in SQL Server 2000 vs SQL Server 2005, Dynamic Management Views and Functions in SQL Server 2005 and Dynamic Management Views Category
• Question 2: What was the service oriented architecture (SOA) technology introduced with SQL Server 2005 and what are some of the components of the technology?
  • Service Broker is the service oriented architecture (SOA) technology introduced with SQL Server 2005.
  • Service Broker components include:
    • Endpoint - Communication point in the database for Service Broker. In addition, ability to specify the authentication, encryption and message forwarding. A single Service Broker endpoint can be created for each database, however the HTTP and Database Mirroring can be created as well.
    • Message Type - Format for the message that is being sent and received. The format could be simply well formed XML or be bound to a schema.
    • Contract - Which message types are sent by either the initiator or the target.
    • Route - For the specific Service Broker Service (see below) which SQL Server instance and database the messages will be routed to during the Conversation (see below).
    • Queue - At a high level, this is the logical container in the Service Broker application. The queues serve as a storage mechanism in Service Broker. All data is RECEIVED (see below) from the queue for processing. Although the queues are all named differently, they are actually all of the same format with both relational and binary columns that can be converted to an XML format.
    • Service - The Service correlates the queue and the contract.
    • Remote Binding Service - Defines the Service Broker user to with the Service.
  • Additional information:  What exactly is SQL Server 2005 Service Broker?
• Question 3: What is the OUTPUT clause and what sorts of options does it provide to applications?
  • The OUTPUT clause is new to SQL Server 2005 and has the ability to access the INSERTED and DELETED tables as is the case with a trigger. The OUTPUT command can be added to your stored procedures or T-SQL scripts in order to write the data out to an auditing table or return the data back to the front end client.
  • The OUTPUT clause has the ability to mimic and/or replace some of the functionality typically addressed by triggers.
  • Additional information: Trigger Alternatives in SQL Server - OUTPUT Clause
• Question 4: To tune SQL Server Integration Services Packages, what are some of the techniques that you use?
  • Review the query plans of any T-SQL commands to determine if they can be further tuned.
  • Review the scripting language code to determine if they can be further tuned.
  • Determine if any SSIS processing can be consolidated into any T-SQL commands.
  • Review the data flow Performance Monitor counters.
  • Additional information: Perfmon Counters for the Data Flow Engine in SQL Server Integration Services
• Question 5: Can you explain the new Disaster Recovery (DR)\High Availability (HA) solution introduced with SQL Server 2005?  Can you outline some of the benefits of this technology?
  • Database mirroring was introduced in SQL Server 2005 as a new means to protect against data loss.
  • From an architecture perspective, Database Mirroring consists of two mandatory roles and an optional third role. The Principal Role and the Mirror Role are mandatory.  These roles would be installed on the Enterprise and Standard editions of SQL Server 2005. The third and optional role is the Witness role which can be installed on an any version of SQL Server including Express Edition.
  • In terms of data protection, Database Mirroring has three different options.  First is High Availability Operating Mode.  High Availability Operating Mode provides durable, synchronous transfer of data between the principal and mirror instances including automatic failure detection and failover.  Second is High Performance Operating Mode.  With the High Performance Operating Mode the overall architecture acts as a warm standby and does not support automatic failure detection or failover. Third is High Protection Operating Mode.  The High Protection Operating Mode operates very similar to the High Availability Mode except the failover and promotion (mirror to principal) process is manual.
  • Additional information: Digging into Database Mirroring in SQL Server 2005

Next Steps

• As you prepare for an upcoming SQL Server Developer or DBA technical interview, review the SQL Server interview questions in this tip as a means to prepare for the technical portion of the interview.  As you continue to prepare for the interview, check out all of the MSSQLTips.com Interview Question tips.
• Stay tuned for future SQL Server interview questions related to new SQL Server 2008 features.
• If you have some key interview questions related to SQL Server 2005 that you always include in your interview process, please share your knowledge with the community by posting the questions in the forum below.

Last Update: 5/14/2008

About the author

Jeremy is the CTO @ Edgewood Solutions, co-founder of MSSQLTips.com and SQL Server MVP since 2009.

View all my tips

We Recommend

• More Career Tips...

Comments and Feedback:

Tuesday, August 10, 2010 - 11:08:07 AM - Fred	Read The Tip
Nice article, but where is the link to the answers?

Tuesday, August 10, 2010 - 12:08:57 PM - Admin	Read The Tip
Fred, Thanks so much.  Highlight the questions and answers with your mouse.  You will see them. Thank you, The MSSQLTips Team

Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name

*Email

Notify for updates Signup for our newsletter

Comments *** NOTE *** - If you want to include code from SQL Server Management Studio (SSMS) in your post, please copy the code from SSMS and paste the code into a text editor like NotePad before copying the code below to remove the SSMS formatting.
*Enter Code

 

Sponsor Information

"Amazing, Amazing, Amazing! SQL doctor is truly one of the most powerful tools I have seen." NEW! Top 5 hard-earned lessons of a DBA from Grant Fritchey & the DBA Team. Read it now What grade do you think your SQL Servers get? Find out with Edgewood's Health Check consulting services. Unlock the power of the Transaction log to discover unauthorized changes and recover lost data Optimizing SQL Server performance can be a daunting task. Or is it?