You are writing a new stored procedure to perform maintenance on your SQL Server 2005 databases that defragments the indexes in an online manner. What command should you use?

Microsoft SQL Server FAQs

(Continued from previous question...)

You are writing a new stored procedure to perform maintenance on your SQL Server 2005 databases that defragments the indexes in an online manner. What command should you use?

Answer: ALTER INDEX with the REORGANIZE option

Explanation: You should use the ALTER INDEX with the REORGANIZE option because the DBCC commands have been deprecated.

(Continued on next question...)

Other Interview Questions

• What's SQL Server
• Can you use a CASE statement in a WHERE clause?
• How large a database can you have in this edition of SQL Server 2005? ...
• What is one of the downsides of using RAID 5 as opposed to RAID 1?
• To optimize the configuration of tempdb for performance, what setting should be used for the tempdb recovery model?
• Which query will return a list of the synonyms in your database?
• You are building an application for a Smartphone and want to use SQL Server Compact Edition. However you do not want this to run as a service all the time because of power concerns. What can you do?
• Which of the following classes provide the context of the caller to SQLCLR Assemblies? Submitted by Michael Coles
• How can you get SQL Server 2005 to simulate multiple processors for query testing?
• You restart your SQL Server on a Sunday morning after some maintenance ...
• Which of the following CREATE ASSEMBLY statement WITH PERMISSION_SET clauses sets ...
• With SQL Server 2005, Oracle servers can be included in your replication topology. How can an Oracle server function?
• In SQL Server 2005, what permissions does a user need to successfully execute the USE command?
• If you want to apply a hot fix to SQL Server 2005, do you need to apply them all in order?
• What does this return on SQL Server 2005? select NEWSEQUENTIALID()
• Which key does the OPEN MASTER KEY statement open?
• How can you ensure they do not change the underlying tables without also changing the view and not disrupt their workflow?
• You want to partition these past orders columns into a new table. What type of partitioning is this?
• What does this code return? select 6 | 7 Figure it out without running it.
• What is an example of hardware partitioning?
• To which versions of SQL Server 2005 can you upgrade an SQL Server 2000 MSDE SP3 edition database?
• You wish to initialize subscribers to your SQL Server 2005 database using FTP. Which versions can you do this with?
• You have upgraded your SQL Server 2000 on Windows 2000 server to SQL Server 2005 to take advantage ...
• What does the DUMP command do?
• You have just upgraded your server from a single processor by adding 3 additional processors....
• What is the best way to have your junior DBA do this?
• What is the timestamp datatype used for?
• In SQL Server 2005, if you initialize a subscription using FTP, how is the password sent?
• What rights does a user need to perform a truncate table command SQL Server 2005?
• What is the quickest way to get them working?
• You wish to prevent anyone from using the sa account on your SQL Server 2005 instance, but still keep it available if your polocy changes. What can you do?
• You wish to prevent anyone from using the sa account on your SQL Server 2005 instance, but still keep it available if your polocy changes. What can you do?
• What is a zettabyte?
• You are configuring a SQL Server 2005 Reporting Services instance for email delivery of reports. What are your options for delivering email?
• In SQL Server 2005 Analysis Services, what does the Synchronize Database Wizard allow you to do?
• Which of these options cannot be set in the SQL Server 2005 Integration Services Bulk Insert task?
• What is the maximum number of instances of Analysis Services 2005 can be installed on a server?
• How many dimensions can you have in a SQL Server 2005 Analysis Services database?
• Which index rebuild operation in SQL Server 2005 can be performed online?
• On which port does the default instance of SQL Server 2005 Analysis Services listen to by default?
• What does CONTINUE do in T-SQL?
• In SQL Server 2000, how can you send an alert without sending a message to the client?
• Which permission in SQL Server 2005 allows you to switch your security context to that of another user with the EXECUTE AS statement?
• What does this WHERE clause evaluate to?
• What value should you use for the BROKER_INSTANCE parameter?
• Which type of XML variable requires a\one and only one root element?
• Which of these is not a parameter you can pass to the LOGINPROPERTY function?
• What does the NULLIF function do?
• When using the SQLCMD tool, what does the -V parameter do?
• What should you have applied a patch for by now on your Windows 2003 and XP systems?
• You are writing a new stored procedure to perform maintenance on your SQL Server 2005 databases that defragments the indexes in an online manner. What command should you use?
• In the Windows world, if you hit the "Enter" key on your keyboard between two single quotes and inserted this value, what data would be stored in the column?
• You wish to back up the symmetric key used to protect data on the Reporting Services 2005 instance installed on Sales01. From your workstation, what command do you use?
• Which table would you query in your report to get logging information about a job step?
• You want to speed up your SQL Server 2005 Service Broker queue by placing an index on it. What is the best way to do this?
• Which is these is not necessary for a well-formed XML document?
• You want to create a covering index for this query on your SQL Server 2005 database ...
• On which port does the dedicated administrator connection listen in SQL Server 2005?
• What is the default type of XML data if you do not specify an arguement?
• You created a SQL Server 2005 Service Broker queue ....
• When you use the sp_xml_preparedocument stored procedure in SQL Server 2000, what is returned?
• What does the function ORIGINAL_LOGIN return in SQL Server 2005?
• You are performing a number of SELECT queries on one of your SQL Server 2005 Service Broker queues and you get a call that applications using the queues are running slowly. What can you do?
• What's an allocation unit?
• Your SQL Server 2005 Express Edition locks up frequently and cannot be accessed. How can you enable the dedicated administrator connection?
• What does VARP() do in SQL Server 2005?
• When declaring an XML document, which type allows text nodes at the top level?
• Can a contained in SQL Server 2005 Integration Services enlist in a transaction started by a sibling container?
• What does a return value of -1 mean from the function XACT_STATE() in SQL Server 2005?
• In SQL Server 2005, what is the maximum size of an index key?
• When you declare an XML variable in SQL Server 2005, what are the two types of typed XML data. What are they?
• In Service Broker for SQL Server 2005, you encounter the following setting in one of your queues...
• Can a deterministic computed column be full-text indexed in SQL Server 2005? Submitted by Matija Lah
• What does the PAD_INDEX parameter do when you create an index in SQL Server 2005?
• In SQL Server 2005, what does the CREATE SERVICE command do?
• What does HAS_DBACCESS() do?
• In SQL Server 2005 T-SQL, what does the CONTINUE statement do?
• You need to remove a large table from your database ...
• If you set RETENTION = ON in your SQL Server 2005 Service Broker queue, what messages are retained?
• How can you quickly determine if any of these indexes are really being used?
• Which commands are allowed when the "Ad Hoc Distributed Queries" server option is set to 0 in SQL Server 2005?
• When creating a SQL Server 2005 Service Broker message type, what does the EMPTY validation option mean?
• You cannot remember what permissions that the db_accessadmin fixed database role has ...
• When a user on a remote SQL Server wants to execute a stored procedure through a linked server on your local server, what security context is used?
• Which database holds the tables that Database Mail uses to store mail messages and other data in SQL Server 2005?
• You are wondering if your indexing scheme for the SQL Server 2005 database that you are managing is appropriate?
• Error severity 13 indicates what?
• In which order do you perform an upgrade to SQL Server 2005 for replicated databases?
• How many Service Packs will be released for SQL Server 2005 in 2007?
• You cannot execute procedures on the 2005 server. Why not?
• What is the recommended way to send mail from SQLAgent in SQL Server 2005?
• When you create a new Message Type in the SQL Server 2005 Service Broker, what does the Authorization parameter signify?
• What the heck does ATN2 do?
• How does a differential backup know which extents have changed so that it can be very quickly run?
• If you run this, what does it return? select applock_mode('public', 'SalesApp', 'Transaction')
• How many rows are returned by these two select statements?
• What does the Queue Reader Agent do in SQL Server 2005 replication?
• What are the three possible functions of the plus (+) operator in SQL Server 2005, the base installed T-SQL?
• The Sort component in SQL Server 2005 Integration Services is what type of component?
• If you received a "Performance Critical" status in the SQL Server 2005 replication monitor, what does this mean?
• Which of the following modules within SQL Server 2005 cannot be signed with a digital signature?
• What does this return?
• You have installed a US English SQL Server 2000 instance with the default options, collation, and sorting...
• Where does Profiler store its temporary data in SQL Server 2005?
• What is the Service Broker Identifier
• You are looking to import a large amount of data from a remote OLEDB data source that is not a text file. Which of the following techniques can you use?
• How are modified extents tracked in SQL Server 2005 (which internal structures)?
• What does this return? select (1324 & 1024)
• What does the Log Reader agent in SQL Server 2005 replication do?
• What type of update did you perform?
• What does a @@fetch_status of -2 mean in SQL Server 2005?
• You want to be sure that your Scalable Shared Database is as available as possible. Which of the following is not needed for this?
• What is the cost threshhold for parallelism in SQL Server 2005?
• You have a Scalable Shared Database setup for reporting purposes on SQL2. You want to be able to keep a point in time view of the reporting database each month. What can you do?
• Which framework would you use to manage this database programatically?
• Which collation is used for Service Broker messages sent between the instances?
• What does the max full-text crawl range option do?
• Which of the following is not an allocation unit in SQL Server 2005?
• Which of the following is the best use for a Scalable Shared Database in SQL Server 2005?
• You are loading 100 rows of data into a narrow table that is heavily used by your production inventory queries...
• How can SQL Server Agent Mail send messages in SQL Server 2005?
• What is the scale of measurement for the cost threshold for parallelism setting in SQL Server 2005?
• Which of the following statements best describes the filter capabilities of Report Builder?
• True or false, Report Builder supports user defined run time parameters?
• Using Report Builder that is bundled with Reporting Services 2005, which of the following would work as a way to add a derived field to a report?
• If you absolutely need a report to look the same regardless of what OS or viewing software is being used, which of the following file formats would be the best choice?
• True or false, Report Builder offers direct export to a Microsoft Access database as one of its supported export options?
• When discussing image support in Report Builder, which of the following is the most accurate statement?
• Using Report Builder, which of the following is the best statement about the formatting options for boolean columns?
• What is the easiest way to capture the SQL statement for a Report Builder report you're troubleshooting?
• Clicking File, Save in Report Builder does which of the following?
• Which of the following choices show the three report formats supported by Report Builder
• Using Report Builder, which of the following statements is correct about formatting numbers?
• True or false, Report Builder supports using the LIKE function inside filters?
• Which RAID levels store parity information?
• You have a large table that you wish to partition to improve performance...
• On which platforms can you use Instant File Initialization to improve database growth performance in SQL Server 2005?
• How do you recover the snapshot?
• Using Reporting Services 2005, it is true or false that subreports execute a query against the datasource once for every detail row in the report?
• You have noticed in both your SQL Server 2000 and 2005 instances that when a database grows in SQL Server, there is a delay in the database response. Why is that?
• Which utility is used to administer SQL Server 2005 Notification Services instances?
• After you've completed a backup for your Sales server to disk...
• What is the result of the following query?
• Out of the box Report Builder supports two report level fields that can be shown on a report. Which option below has those two options?
• Can a particular event in SQL Server 2005, such as the CREATE USER command, have more than one DDL trigger assigned to it?
• Can you re-associate the application with the same SSNS instance?
• How many users can be added to an application role in SQL Server 2005?
• What algorithm is used to encrypt the Database Master Key when it is created?
• Which protocols support Kerberos authentication on SQL Server 2005?
• you want to be sure that SQL authenticated logins must abide by the password policy.. .
• How can you you be sure that a SOAP queries that are looking for a WSDL response will be provided?
• Where can you view the list of server-scoped DDL triggers?
• You want to be sure that your reporting solutions using a database snapshot are properly protected from disaster. How can you back up your database snapshots?
• When you install SQL Server 2005 and create a new database, is a Database Master Key created?
• What does the DEALLOCATE statement do in SQL Server 2005?
• Report Builder has no built in support for source control. Which of the following choices could be implemented if you decided that those reports needed to be under source control?
• Setting the TRUSTWORTHY database property to ON allows you to execute the following from within the context of the database: Submitted by Charles Hawkins
• What tool is available for ad hoc reporting by non IT end users in Reporting Services 2005?
• Which of the following is not available to you?
• Which types of replication work with Oracle publishers?
• What does @@textsize return?
• In configuring the thesaurus configuration file for SQL Server 2005 Full-Text Search, you set the diacritics_sensitive value to 1. What does this mean?
• What does the CREATE SERVICE statement do in SQL Server 2005?
• What does the CREATE SERVICE statement do in SQL Server 2005?
• The queries still seem slow immediately after snapshot creation. Why?
• In Reporting Services 2005, how is Report Builder typically deployed to end users?
• What happens when a transaction fails on the publisher?
• If you have a replication scenario with one publisher, one distributor and one subscriber and the transaction isolation level is not set to serializable, what happens when a transaction fails on the publisher?
• When creating a full-text index on a BLOB column ...
• In SQL Server 2005 Full-Text Search, you set up a replacement set like this...
• In the SQL Server 2005 thesaurus XML configuration file, what is the expansion set?
• What feature of SQL Server 2005 would help you get the snapshot transferred?
• The TRUSTWORTHY database property is by default?
• Where are Notification Services event messages logged in SQL Server 2005?
• What would happen when you execute the code below in Query Analyzer (SQL Server 2000) Submitted by Jacek Osuchowski...
• How is Thesaurus data configured in SQL Server 2005 Full-Text Search?
• What does a report model provide in SQL Server 2005 Reporting Services?
• What can tracer tokens measure in SQL Server 2005 transactional replication?
• You want to script the execution of an Integration Services package from the command line for use from a Unix scheduler. What utility would you use?
• What does the sqlwb utility do?
• What method would you call to create this key?
• How can you determine which Service Broker ports are being used on your server?
• In Full-Text Search, what is word breaking?
• Can you call Notification Services APIs from unmanaged code?
• Which of the following is true about the Raw File Source in SQL Server 2005 Integration Services?
• You wish to configure event logging for your SQL Server 2005 Notification Services instance. Where would you make this change?
• Which of the following is not true about the Raw File Destintion connection in SQL Server 2005 Integration Services?
• What message types exist in SQL Server 2005 Service Broker?
• What does the CEILING() function do?
• What is a dialog conversation in the SQL Server 2005 Service Broker.
• What is row versioning in SQL Server 2005?
• What does @@MAX_PRECISION return?
• Which of the following columns can be indexed with SQL Server 2005 Full-Text Search?
• When starting SQL Server 2005 from the command line, what does the -h switch do?
• Which of the queries is the best one for Janice to use to return the information she has been requested to provide?
• In SQL Server 2005, you want to monitor your transactional replication and determine how long it takes for a transaction to move from the publisher to a subscriber. What would you use to help you do this?
• How many credentials can one login be mapped to?
• You are looking to secure your publisher to prevent unauthorized logins from accessing the data through replication. Where do you add the list of logins that have access?
• What does the ERROR_PROCEDURE() function return?
• Credentials in SQL Server 2005 are used to access what?
• Which of the following is an invalid file name for a database file?
• What does @@options return?
• What do you need to do now to ensure you can recover the routes in the event of a disaster?
• You are looking to setup a large Notification Services application...
• You want to release some memory and reduce SQL Server to 3GB or RAM. How can you do this?
• You are troubleshooting a Service Broker application ...
• You are implementing replication across the Internet for a large bank that wishes to move up to date pricing information...
• Can you use Full-text search to index this column?
• To recover an instance of Notification Services in 2005 on another server, what information would you need?
• What type of connectivity does the readpipe/makepipe utility test?
• In SQL Server 2005, which of the following schema changes are supported for the publication objects of a replicated database?
• You want to disable the receipt of messages in one of your databases by the Service Broken. How should you do this?
• You are building a .NET assembly that will access the registry of the local machine for a factor used in a computer column. What permission set should you assign it?
• No column permissions are assigned. What should you do?
• How can you determine which user is executing statements in Profiler?
• Assuming the column name is correct, which of the following is a valid cast operation in SSIS _expression language?
• There are two types of subscriptions in SQL Server replication. What are they?
• What is the scope of the control permission in SQL Server 2005?
• When testing connectivity in SQL Server, you use which utility in conjunction with the readpipe utility to establish a connection?
• In replication, what is each set of the source data that is replicated from the source server called?
• In SSIS, what is the difference between output columns and external columns?
• You want to be sure your database server if properly secured. You have two instances, a default instance and a named instance installed. Which ports do you need to open?
• You have a relatively new SQL Server 2005 and msdb is corrupt. How can you fix this database without a restore? (data loss is acceptable)
• In SQL Server 2005 Integration Services, if you want to import a flat file very quickly that contains only integer data, what type of parsing should you use?
• In a new default SQL Server 2005 installation, what is the status of the dedicated administrator connection?
• In the REPEATABLE READ isolation level, what phenomena is still possible?
• What else should you configure on these instances?
• You have specified that a SQL Server 2005 login must respect the password policy on a Windows XP host. Which of the following are valid passwords for the user Kendall?
• A certificate is which type of security mechanism?
• Which of the following services is instance-aware?
• What What does the term ACID refer to?
• SELECT USER_NAME() -- Returns Andy
• What format are images rendered in if they were stored originally in another format?
• What does the tablediff utility do?
• Which severity levels of errors can be corrected by a user (as opposed to those that an administrator or Microsoft must work to fix.)
• What is the granularity in seconds of the timestamp datatype?
• How many plans must you setup?
• How can you easily monitor to see if this assembly is being used?
• You wish to ensure you can recover your SQL Server 2005 database to a point in time if necessary. Which recovery models can you use?
• Does a root element in an XML document necessarily contain all the content for a well-formed document?
• Which of the following datatypes can be represented in a SQL_VARIANT datatype?
• What's the difference between a server login and a database user?
• For regulatory reasons, you must maintain an exact text copy of your XML documents in your SQL Server 2005 database. What datatype of storage should you choose?
• What is a bookmark lookup?
• Yesterday's QOD How are comments denoted in an XML document?
• How secure are encrypted stored procedures, triggers and views?
• You then write a function to use this assembly, but it does not work. What could be the problem?
• The Service Broker operates on messages in which fashion?
• What security role should you assign them?
• What does this method in SQL Server 2005's SMO equate to in T-SQL? db.CheckTables(RepairType.None)
• In SQL Server 2005, can you include a user-defined role as a member of another user-defined role?
• In SQL Server 2005, what does instance aware mean for a service?
• If you use the EXECUTE AS clause to impersonate a user at a database-scoped level...
• The nesting level for stored procedures in SQL Server 2005, but how many databases can you access inside one stored procedure?
• Is it possible to build an index on a view
• What does REVERT do in SQL Server 2005?
• What does SEND do in SQL Server 2005?
• Where is varchar(max) data stored?
• What will occur with the following code in SQL Server 2005 if executed by Alice?
• What T-SQL function can achieve this?
• You want to disable the trigger dEmployee on the HumanResources...
• In SQL Server 2005, if you want the keys securing your data to expire over time, what type of key encryption would you choose?
• Which is larger in size in SQL Server 2005 if you are using symmetric keys?
• What result would you expect from this statement? Select * from dbo .MyTable
• You are using SQL Server 2005 and you wish to find a number of details about stored procedures created...
• A .NET assembly running with UNSAFE permissions can do which of the following?
• What does the SQL Writer service do?
• Which key provides the strongest encryption?
• This administrator will own the jobs. Which role should you assign?
• What level of permissions does a user need to create an assembly with UNSAFE permissions?
• Which optional SQL Server component must you install to ensure that you can create and execute maintenance plans?
• What could be the problem if you know the service is working properly?
• In SQL Server 2005, which network protocols use endpoints?
• You have a SQL Server 2005 cluster and need to add anti-virus software as per your corporate standards. What should you exclude from scans?
• What port do you need to open on your server firewall to enable named pipes connections?
• When the network service built-in account connects to a network resource, what is the context?
• A certificate is like which type of key in SQL Server 2005 encryption?
• How many connections are available in SQL Server MSDE 2000?
• What will be the result of the last SELECT statement if you execute the T-SQL script below as a sysadmin
• How many of these servers can you consolidate?
• To maintain the best performance of your SQL Server 2005 databases, how should you deploy encryption technologies?
• In SQL Server 2005, which network protocols use endpoints?
• You have a SQL Server 2005 cluster and need to add anti-virus software as per your corporate standards. What should you exclude from scans?
• Can you give me an overview of some of the database objects available for use in SQL Server 2000?
• What is an index? What types of indexes are available in SQL Server 2000?
• What does NULL mean?
• What is a primary key? What is a foreign key?
• What are triggers? What are the different types of triggers in SQL Server 2000?
• How can you ensure that a table named TableB with a field named Fld1 will only have those values in the Fld1 field that are also in the table named TableA with a field named Fld1?
• What is a performance consideration of having too many indexes on a production online transaction processing (OLTP) table?
• What can be used to ensure that a field in a table only accepts a certain range of values?
• What is the difference between a return parameter and an OUTPUT parameter?
• What is a correlated sub-query? How can these queries be useful?