SQL Notification Services

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.

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

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.

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

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 }

} )

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.