Microsoft sql server notification services Interview Questions & Answers

4 avg. rating (80% score) - 1 votes

Microsoft sql server notification services Interview Questions & Answers

Are you in hunt for Microsoft sql server notification services jobs? Confused about what topics you should prepare to clear the interview in the very first attempt? Then to clear all your confusions regarding the SAP CRM Functional we in Wisdomjobs has provided you with the complete set of Microsoft sql server notification services Interview Question and Answers along with the different job roles on our site page. If you are good at all concepts of Microsoft sql server notification services then there are various leading companies that offer job roles like Ms Sql Server Jobs, MS SQL DBA Lead Professional - SQL Server, SQL Server Database Administrator and many other roles too. For more details on Microsoft sql server notification services interview questions and answers and job roles visit our site page.

Microsoft Sql Server Notification Services Interview Questions

Microsoft sql server notification services Interview Questions
    1. Question 1. What Are The Lock Types?

      Answer :

      SQL server supports following locks:

      1. Shared lock
      2. Update lock
      3. Exclusive lock

      Shared lock

      • Shared Lock allows simultaneous access of record by multiple Select statements.
      • Shared Lock blocks record from updating and will remain in queue waiting while record is accessed for reading.
      • If update process is going on then read command will have to wait until updating process finishes.

      Update locks

      This lock is used with the resources to be updated.

      Exclusive locks

      This kind of lock is used with data modification operations like update, insert or delete.

    2. Question 2. Define Data, Entity, Domain And Referential Integrity?

      Answer :

      Data Integrity

      Data Integrity validates the data before getting stored in the columns of the table.

      SQL Server supports four type of data integrity:

      Entity Integrity

      Entity Integrity can be enforced through indexes, UNIQUE constraints and PRIMARY KEY constraints.

      Domain Integrity:

      Domain integrity validates data for a column of the table.

      It can be enforced using:

      Foreign key constraints,

      Check constraints,

      Default definitions

      NOT NULL.

      Referential Integrity

      FOREIGN KEY and CHECK constraints are used to enforce Referential Integrity.

      User-Defined Integrity

      It enables you to create business logic which is not possible to develop using system constraints. You can use stored procedure, trigger and functions to create user-defined integrity.

    3. Question 3. What Is Isolation Levels?

      Answer :

      Isolation keeps the transactions of multiple users isolated from each other. Transaction isolation level controls the degree of locking which occurs when selecting data. This is necessary to avoid situations like:

      Lost updates:when two transactions read the same data.

      Dirty read: Occurs when a transaction reads data that has not been updated.

      Non repeatable reads: occur when different results are read multiple times.

      Phantoms: Occurs when row data matches the first time but does not match subsequent times.

    4. Question 4. Define Transaction And Transaction Isolation Levels?

      Answer :

      Transaction

      A transaction is a set of operations that works as a single unit. The transactions can be categorized into explicit, autocommit, and implicit transactions. Every transaction should follow four properties called the ACID properties i.e. atomicity, consistency, isolation, and durability.

      Atomicity

      Transaction ensures either modification is committed or not committed.

      Consistency

      The data should be in consistent state when transaction process is completed. This means that all related tables are updated.

      Isolation

      SQL server supports concurrency when mean that data can be access or shared by many users. A transaction works in isolation and doesn't allow other transaction to work concurrently on the same piece of data.

      Durability

      Data is permanent once transaction is completed and it can be recovered even if system fails.

      There are four transaction isolation levels:

      • Read uncommitted
      • Read committed
      • Repeatable read
      • Serializable
      • Read uncommitted isolation levels
      • This is the lowest isolation level which can also be called as dirty read. Using this, you can read uncommitted data which can be rolled back at any point. With this level, SQL server uses share lock while reading data.

      Read committed isolation levels

      • With this level, uncommitted data can't be read. This is default isolation level and uses shared lock while reading data.
      • Repeatable read isolation levels
      • It locks all the data that is used in the query.
      • Serializable isolation levels

    5. Question 5. Explain Openquery Function And Openrowset Function?

      Answer :

      OPENQUERY: - Used to execute the query passed on the linked server.

      Syntax: OPENQUERY (Linked_server_identifier, query). It can also be refernced from a FROM clause of selecte query.

      e.g. Select * from OPENQUERY (Oracleserver, ‘select fname, FROM Employee);

      OPENROWSET: - Used to access tables in a linked server. It can be used one time for accessing remote data by using OLE DB. It includes all the information required to access the remote data.

      Syntax:

      OPENROWSET

      ( { 'provider_name' , { 'datasource' ; 'user_id' ; 'password'

      | 'provider_string' }

      , { [ catalog. ] [ schema. ] object

      | 'query'

      }

      | BULK 'data_file' , 

      { FORMATFILE = 'format_file_path' [ ]

      | SINGLE_BLOB | SINGLE_CLOB | SINGLE_NCLOB }

      } )

    6. Question 6. What Is A Linked Server?

      Answer :

      A linked server allows remote access. They have the ability to issue distributed queries, update, commands, and transactions across different data sources. A linked server has an OLE DB provider and data source.

    7. Question 7. What Is Distributed Queries?

      Answer :

      Distributed queries can access data from different data sources. These sources can reside on the same server or a different server. This means that distributed queries can query multiple databases.

    8. Question 8. Define Distributed Query And Linked Server?

      Answer :

      Distributed Query:

      • Distributed Query is a query which can retrieve data from multiple data sources including distributed data.
      • SQL Server supports distributed query through the use of OLE DB provider.
      • OLE DB provider exposes data in the row sets which can be used in SQL Statement.
      • SQL Server can use distributed query in the SQL Statement using

      Linked Server:

      • It is the virtual server that is created to access OLE DB data source.
      • It includes all the information needed to access OLE DB data source.
      • Linked server definition contains all the information needed to locate OLE DB data source.
      • You can join remote data and local data using Linked Server.

    9. Question 9. Explain The Difference Between A Primary Key And A Unique Key?

      Answer :

      • Both are defined to ensure unique row.
      • Primary key creates a clustered index on the column by default.
      • Unique creates a non-clustered index by default.
      • Primary key doesn't allow NULLs, but unique key allows one NULL only.

    10. Question 10. Define Primary And Unique Key?

      Answer :

      Primary Key

      • The column or columns of the table whose value uniquely identifies each row in the table is called primary key.
      • You can define column as primary key using primary key constraint while you create table.
      • When you define a column as primary key, a unique index is created which restricts duplicate data and fast access to data.
      • A column defined as primary key doesn't allow null value.
      • By default, clustered index in created with the column having primary key.

      Unique key

      • Unique key also ensures data uniqueness like primary key.
      • A column with unique key defined allows null value. 
      • By default, it creates non-clustered index.

    11. Question 11. What Are The Restrictions Applicable While Creating Views?

      Answer :

      • Views can be created referencing tables and views only in the current database.
      • A view name must not be the same as any table owned by that user.
      • You can build views on other views and on procedures that reference views.
      • Rules or DEFAULT definitions can't be associated with views.
      • Only INSTEAD OF triggers can be associated with views.
      • The query that defines the view cannot include the ORDER BY, COMPUTE, or COMPUTE BY clauses or the INTO keyword.
      • You cannot define full-text index definitions for views.
      • You cannot create temporary views
      • You cannot create views on temporary tables.

    12. Question 12. What Are The Basic Components Of Notification Services?

      Answer :

      The following terms are the components of Notification Service:

      Event: an action that occurred affecting the specified data. 

      Subscriber: an entity that wants being notified on occurrence of an event.

      Subscription: an act by which subscriber describes when and what he wants to be notified as. 

      Notification: a channel of communication.

    13. Question 13. Explain Architecture Of Sql Server Notification Services?

      Answer :

      Notification Services consists four components

      Subscription

      Subscription data can be added using (SMO) Subscription Management Objects to Notification Services application.

      Events

      Events get populated to events table with the help of Event providers.

      Generator

      When events are populated into events table, the generator wakes up and starts processing rules that are attached with subscriptions. The generator checks to see if any events match them. The generator starts creating notifications and fills the Notifications table, if matches are found.

      Delivery (notifications)

      When notifications arrive in Notifications table, the distributor wakes up and starts processing each notification, formats it and delivers them using specified channel.

    14. Question 14. What Are Notification Services?

      Answer :

      Notification Services are services which send notifications to the interested entities based on what they would like be notified on.

      It is one of the features of the SQL Server.

    15. Question 15. How Does Notification Services Work?

      Answer :

      Subscription data can be added using (SMO) Subscription Management Objects to Notification Services application. Events get populated to events table with the help of Event providers. When events are populated into events table, the generator wakes up and starts processing rules that are attached with subscriptions. The generator checks to see if any events match them. The generator starts creating notifications and fills the Notifications table, if matches are found. When notifications arrive in Notifications table, the distributor wakes up and starts processing each notification, formats it and delivers them using specified channel.

Popular Interview Questions

All Interview Questions

Microsoft Sql Server Notification Services Practice Test

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

Tutorial