SQL Interview Questions

 What is SQL Server ?SQL Server is a DBMS system provided by Microsoft. SQL Server is sometimes mistakenly referred to as SQL. Error severity 13 indicates what?Transactional deadlock errors.This level of error severity indicates a transaction deadlock error. In which order do you perform an upgrade to SQL Server 2005 for replicated databases?Distributor, Publisher, then Subscriber.You always perform an upgrade in this order: distributor, publisher, subscriber. How many Service Packs will be released for SQL Server 2005 in 2007?Explanation: The answer is up in the air and this is more of a poll than a real QOD. Based on the ways things are going, the staff here sees just 1, though our hope would be that 3 or 4 would be released. You setup a linked server from a SQL Server 2000 server to your new SQL Server 2005 server (with defaults), however you cannot execute procedures on the 2005 server. Why not?You need to enable RPC.By default, RPC is disabled in SQL Server 2005. You need to set the "remote access option" in your server configuration to 1 to allow the execution of stored procedures from a remote server. What is the recommended way to send mail from SQLAgent in SQL Server 2005?Database MailYou can use either Database Mail or SQLMail with SQL Agent in SQL Server 2005. However since SQLMail will be removed, it is recommended that you use Database Mail. When you create a new Message Type in the SQL Server 2005 Service Broker, what does the Authorization parameter signify?The owner of the message type.This parameter determines the owner of the message type. This defaults to the current user. What the heck does ATN2 do?The angle between the x-axis and a ray.This is a mathematical function that returns the angle between the positive x-axis and the ray that passes through the two coordinates passed in. The angle is in radians. How does a differential backup know which extents have changed so that it can be very quickly run?The DCM tracks changes. The differential backup reads the extents from this structure.A differential backup uses the Differential Change Map to determine which extents have changed and need to be include in the backup. This greatly speeds the differential backup process. How many rows are returned by these two select statements?2 and 2Each select statement actually returns 2 rows. You can use this script to check this:create table mytable( id int identity(1,1), mychar varchar(20))goinsert mytable select nullinsert mytable select ''insert mytable select ' 'goset ansi_nulls onset ansi_null_dflt_on onselect * from mytablewhere mychar is not nullselect * from mytablewhere mychar <> '' and mychar is not nullselect * from mytablewhere mychar <> ' ' and mychar is not nullselect * from mytable where mychar = ''select * from mytable where mychar = ' 'set ansi_null_dflt_on offset ansi_nulls offgodrop table mytable What does the Queue Reader Agent do in SQL Server 2005 replication?This agent reads the subscriber logs and moves changes back to the publisher.This agent is used when the queued update model is chosen with transactional replication. It moves changes from the subscribers back to the publishers. What are the three possible functions of the plus (+) operator in SQL Server 2005, the base installed T-SQL?Add, string concatenation, unary plusThe three functions are Add, String Concatenation, and Unary Plus. The Sort component in SQL Server 2005 Integration Services is what type of component?Blocking TransformationThe Sort component is a blocking transformation meaning that it needs all rows in order to perform its function. If you received a "Performance Critical" status in the SQL Server 2005 replication monitor, what does this mean?The latency between transaction commits at the publisher and subscriber exceeds the warning level.This status can actually mean two different things. Either the latency between the commit of a transaction at the publisher and the same commit at the subscriber is exceeding some level in a transactional level or not enough rows are being processed in a merge replication scenario. Which of the following modules within SQL Server 2005 cannot be signed with a digital signature?DDL triggersDDL triggers cannot be signed, but all the other objects can. What does this return?declare @i intselect @i = -5select +@i-5This will return -5 as the result. The + operator functions as a unary plus operator, which means that it performs no operation on the value it preceeds. You have installed a US English SQL Server 2000 instance with the default options, collation, and sorting. What does this return:create table MyTable( Mychar varchar(20))goinsert Mytable select 'Apple'insert Mytable select 'ant'insert Mytable select 'Ball'goselect * from MyTable where Mychar like '[^a]%'BallThis should return "Ball" only since the ^ operator means not matching the next character. In this case, the first character should not be an "A". What is the Service Broker Identifier ?A GUID that identifies the database on which Service Broker is running.Each database has a Service Broker identifier. This is a GUID in the service_broker_GUID column that identifies the databases on which Service Broker is running. It ensure that messages are delivered to the right database. 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?Use the select * from OPENROWSET(BULK...) command.SQL Server 2005 includes a new option with the OPENROWSET command for getting large amounts of data from an OLEDB data source. It is the BULK option and works similar to the BULK INSERT command. Microsoft SQL ServerInterview Questions And Answers (SET -6) 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?Tracer Tokens.You can use tracer tokens in SQL Server 2005 replication to measure latencies. These are small amounts of data written into your transaction logs. How many credentials can one login be mapped to?1A credential can be mapped to many logins, but a login can only be mapped to one credential. 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?Add them to the Publication Access List in the publication database.All access to publications is controlled with the Publication Access List (PAL), which is similar to the Windows ACLs. A list is created for each publication that is set up. What does the ERROR_PROCEDURE() function return?The name of the stored procedure that caused an error.The ERROR_PROCEDURE() function is placed in the catch block of a TRY..CATCH construct and returns the name of the procedure that caused an error to be thrown. Credentials in SQL Server 2005 are used to access what?Resources outside the SQL ServerA credential is used to access resources outside of SQL Server. It contains the authentication information needed for accessing these resources. Which of the following is an invalid file name for a database file?None of the aboveExplanation from BOL: SQL Server 2005 does not enforce the .mdf, .ndf, and .ldf file name extensions, but these extensions help you identify the different kinds of files and their use. What does @@options return?The current SET options for the connection.The @@options variable returns a list of the options set for the current connection. This is returned as integer and each bit in the integer represents an option. You have setup forwarding for your SQL Server 2005 Service Broker queues to another server. You edit a route to move a queue to a new server. What do you need to do now to ensure you can recover the routes in the event of a disaster?Back up the msdb database.The routes for forwarding are stored in msdb.sys.routes. If you back up the msdb database, the routes will be captured. You are looking to setup a large Notification Services application and you are planning on a 40GB database. When installing Notification Services, the creation fails. What is the likely problem?Notification Services installation has a 10 minute timeout and the database creation probably took longer than that.The Notification Services timeout for database creation is 10 minutes. If the database takes longer to create, the installation of the instance fails. You need to reduce the size of the database for installation and then increase it when you are done. You have enabled AWE for your SQL Server 2005 server and allocated 4GB or RAM on one of your servers. An ASP.NET application on the same server is feeling memory pressure and you want to release some memory and reduce SQL Server to 3GB or RAM. How can you do this?Change the max amount of memory allocated and shut down and restart SQL Server to have it take affect.Once memory is allocated through AWE, it cannot be released unless the SQL Server is restarted. You are troubleshooting a Service Broker application and find that messages are remaining on the queue. You determine that there is no active queue monitor. What should you do?Use the ALTER QUEUE statement to turn activation on.One of the troubleshooting steps if messages remain in the queue is to check for an active queue monitor. If one is not on, then it needs to be activated. The ALTER QUEUE statement is used to change this. You are implementing replication across the Internet for a large bank that wishes to move up to date pricing information to an analyst's laptop. They are concerned about spoofing of their main site by criminals. What can you do to secure the replication solution with SQL Server 2005?Set the encryption level to verify the certificate is issued by a trusted authority.Since you are asked to use replication, a replication solution is to use encryption for the connection and verify the certificate being used is from a trusted authority. Setting the encrpytion level to 2 will do this. In SQL Server 2005, you are looking to implement full-text search. One of the tables you are looking to index stores Mircrosoft Word documents in a varbinary(max) column. Can you use Full-text search to index this column?YesYou can use full-text search for formatted data such as Word that contains text stored in a varbinary column. To recover an instance of Notification Services in 2005 on another server, what information would you need?A backup of the database holding Notification Services and the XSD and XSLT files as well as the name and password for the service account.To recover SSNS, you need the database backup as well as the operational files, XSD and XSLT files, stored on the file system and the account information used for the service account. Since SSNS usually works with data outside of the SQL Server, a domain account is usually used to ensure proper permissions. What type of connectivity does the readpipe/makepipe utility test?Named PipesThe readpipe and makepipe utility combination will test named pipe connectivity. In SQL Server 2005, which of the following schema changes are supported for the publication objects of a replicated database?All of the above.SQL Server 2005 replicated databases support the following schema changes for objects:* ALTER TABLE* ALTER VIEW* ALTER PROCEDURE* ALTER FUNCTION* ALTER TRIGGER (DML only) You want to disable the receipt of messages in one of your databases by the Service Broken. How should you do this?ALTER DATABASE Sales SET DISABLE_BROKERTo disable the receipt of messages, you can disable the Service Broker by disabling it with the ALTER DATABASE command. The SET command is "DISABLE_BROKER". 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?EXTERNAL_ACCESSThe most restrictive permission set should always be used for .NET assemblies in keeping with a secure SQL Server environment. Only the EXTERNAL_ACCESS and UNSAFE permission sets will allow registry access and UNSAFE permissions are not required