SQL Server Notification Services Interview Questions And Answers

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.

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.

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

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

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.

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.

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

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.

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.

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.

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 }
} )

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

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.

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.

SQL server supports following locks

Shared lock
Update lock
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.