Top My SQL Server Interview Questions Part – 2

1.What is SQL Server?

SQL Server is a DBMS system provided by Microsoft. SQL Server is sometimes mistakenly referred to as SQL.

2.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.

3.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.

4.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.

5.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.

6.Where does Profiler store its temporary data in SQL Server 2005?

In the directory stored in the system variable TEMP. Profiler uses the location specified for the TEMP system variable.

7.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.

8.How are modified extents tracked in SQL Server 2005 (which internal structures)?

Differential Change Map and Bulk Change Map There are two internal structures that track extents modified by bulk copy operations or that have changed since the last full backup. They are the Differential Changed Map (DCM) and the Bulk Changed Map (BCM).

9.What does the Log Reader agent in SQL Server 2005 replication do?

This agent reads the publisher log for transactions to send to the distributor. This agent is tasked with reading the transaction log in transactional replication and moving those transactions that need to be replicated to the distributor.

10.What is the cost threshhold for parallelism in SQL Server 2005?

This is the number of seconds that a serial plan cannot exceed if it is to be used. A parallel plan is used if the estimate exceeds this value. This is the threshold at which SQL Server determines whether a serial or parallel plan is to be used. When SQL Server calculates that a serial plan exceeds the threshold,it will elect to use a parallel plan instead.

11.What does the max full-text crawl range option do?

Determines the number of partitions used in an index crawl. This option helps optimize the full-text indexing process by specifying the number of partitions the SQL Server uses during index crawls.

12.How can SQL Server Agent Mail send messages in SQL Server 2005?

SQL Mail through Extended MAPI or Database mail. SQL Server Agent Mail can be configured to use Database Mail or Extended MAPI.

13.Which of the following statements best describes the filter

Users can do equals,greater than,less than,etc,plus they can do logical AND,OR,NOT operations. Users can also group filters to allow more advanced filters.

While it looks a little different than you may be used to,the filter builder is reasonably rich,allowing most standard evaluation types and rich boolean comparisons. The only weak spot in the set is no support for LIKE,you have to make do with CONTAINS.

14.About the formatting options for Boolean columns?

Booleans are formatted as True/False and there are no other built in options,but you could build an expression using IIF that would let you do other formatting From the format dialog there are no extra True/False. Writing an expression that you add to the model view is the easiest way to work around this limitation.

15.Builder report you’re troubleshooting?

Run Profiler Profiler will work as long as you have permissions to profile the server and is the the best solution because it requires no change to file,but that option was not listed here and is better used if you want to do analysis rather than troubleshooting.

16.Which utility is used to administer SQL Server 2005 Notification Services instances?

nscontrol.exe The nscontrol application can be used with various parameters to administer a SQL Server 2005 Notification Services instance

17.What algorithm is used to encrypt the Database Master Key when it is created?

Triple DES. When you create a Database Master Key,it is encrypted using the password you supply with the Triple DES algorithm..

18.Where can you view the list of server-scoped DDL triggers?

The Object Explorer for the server has a “Triggers” folder. The server-scoped DDL triggers will appear in Management Studio in the Object Explorer under the “Triggers” folder.

19.What does the DEALLOCATE statement do in SQL Server 2005?

Remove a reference to a cursor. This statement is used to remove cursor references. If this is the last reference being removed,the cursor’s resources are released.

20.What does the CREATE SERVICE statement do in SQL Server 2005?

This statement is used to setup new tasks for Service Broker queues. This statement is ue to create a new Service Broker service that is a set of tasks that are run on messages.

21.In the SQL Server 2005 thesaurus XML configuration file,what is the expansion set?

Expansion sets are synonyms for the search term and returned as results if they appear along with the search term. The expansion set is the group of values that are synonyms and can be substituted for the search term.

For example,an expansion set can be “SS2K5″,”SQL Server 2005″,”SQL2K5”. In this case,fields with any of these 3 values would be returned as a result for searches on “SQL Server 2005”.

22.How can you determine which Service Broker ports are being used on your server?

Query the system catalog view: sys.conversation_endpoints The system catalog view sys.conversation_endpoints will show you which Service Broker endpoints,and therefore ports,are open on your server.

23.What are defaults?Is there a column to which a default can’t be bound?

A default is a value that will be used by a column,if no value is supplied to that column while inserting data. IDENTITY columns and timestamp columns can’t have defaults bound to them

24.Define candidate key,alternate key,composite key.

A candidate key is one that can identify each row of a table uniquely. Generally a candidate key becomes the primary key of the table. If the table has more than one candidate key,one of them will become the primary key,and the rest are called alternate keys. A key formed by combining at least two or more columns is called composite key.

25.What is bit datatype and what’s the information that can be stored inside a bit column?

Bit datatype is used to store Boolean information like 1 or 0 (true or false). Until SQL Server
6.5 bit datatype could hold either a 1 or 0 and there was no support for NULL. But from SQL Server
7.0 onwards,bit data type can represent a third state,which is NULL.