Traction
A traction is a set of operations that works as a single unit. The tractions can be categorized into explicit, autocommit, and implicit tractions. Every traction should follow four properties called the ACID properties i.e. atomicity, consistency, isolation, and durability.
Atomicity
Traction ensures either modification is committed or not committed.
Consistency
The data should be in consistent state when traction process is completed. This me that all related tables are updated.
Isolation
SQL server supports concurrency when mean that data can be access or shared by many users. A traction works in isolation and doesn't allow other traction to work concurrently on the same piece of data.
Durability
Data is permanent once traction is completed and it can be recovered even if system fails.
There are four traction isolation levels:
Read committed isolation levels
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.
A linked server allows remote access. They have the ability to issue distributed queries, update, commands, and tractions across different data sources. A linked server has an OLE DB provider and data source.
SQL server supports following locks:
Shared lock
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.
Distributed Query:
Linked Server:
Isolation keeps the tractions of multiple users isolated from each other. Traction isolation level controls the degree of locking which occurs when selecting data. This is necessary to avoid situations like:
Lost updates:when two tractions read the same data.
Dirty read: Occurs when a traction 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.
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.
Primary Key
Unique key
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.
Distributed queries can access data from different data sources. These sources can reside on the same server or a different server. This me that distributed queries can query multiple databases.
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.
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 }
} )
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.