ACID Model in sql

The ACID model is one of the oldest and most important concepts of database theory. It sets forward four goals that every database management system must strive to achieve: atomicity, consistency, isolation and durability. No database that fails to meet any of these four goals can be considered reliable.

Let’s take a moment to examine each one of these characteristics in detail:
• Atomicity states that database modifications must follow an “all or nothing” rule. Each transaction is said to be “atomic.” If one part of the transaction fails, the entire transaction fails. It is critical that the database management system maintain the atomic nature of transactions in spite of any DBMS, operating system or hardware failure.

• Consistency states that only valid data will be written to the database. If, for some reason, a transaction is executed that violates the database’s consistency rules, the entire transaction will be rolled back and the database will be restored to a state consistent with those rules. On the other hand, if a transaction successfully executes, it will take the database from one state that is consistent with the rules to another state that is also consistent with the rules.

• Isolation requires that multiple transactions occurring at the same time not impact each other’s execution. For example, if Joe issues a transaction against a database at the same time that Mary issues a different transaction, both transactions should operate on the database in an isolated manner. The database should either perform Joe’s entire transaction before executing Mary’s or vice-versa. This prevents Joe’s transaction from reading intermediate data produced as a side effect of part of Mary’s transaction that will not eventually be committed to the database. Note that the isolation property does not ensure which transaction will execute first, merely that they will not interfere with each other.

• Durability ensures that any transaction committed to the database will not be lost. Durability is ensured through the use of database backups and transaction logs that facilitate the restoration of committed transactions in spite of any subsequent software or hardware failures.
Take a few minutes to review these characteristics and commit them to memory. If you spend any significant portion of your career working with databases, you’ll see them again and again. They provide the basic building blocks of any database transaction model.

Architecture of connected environment of data access in ADO.Net?
The connected environment of data access is the common procedure for accessing data programmatically. The differentiating property of the connected data access environment is that here you (the programmer) is required to manage the connection with the database. You can only perform database operations when, there exists an open connection to the database. Hence, before performing any database operation (select, update, insert, delete). The important objects for working in the connected environment are:

1. Connection (SqlConnection, OleDbConnection, etc): It represents a connection to the database. All the connection objects in the ADO.Net implement the System.Data.IDbConnection interface.
2. Command (SqlCommand, OleDbCommand, etc): It represents the SQL command sent to the database, e.g., SELECT, UPDATE, INSERT, DELETE. All commands in ADO.Net implements IDbCommand interface.
3. DataReader (SqlDataReader, OleDbDataReader, etc): It represents a data stream .

What is function of data adapter?
A data adapter is integral to the working of ADO.NET since data is transferred to and from a database through a data adapter. A data adapter creates a dataset and updates the database. When you make any changes to the dataset, the modifications to the database are actually performed by the data adapter.

Find all tables in database.
SQL SERVER 2000: select name from sysobjects where type='U'
SQL SERVER 2005: select name from sys.tables
In SQL Server 2005, you can find whether a table is replicated. Execute the following query.
select * from sys.tables where is_replicated =1

Find all views in database
SQL SERVER 2000: select name from sysobjects where type='V'
SQL SERVER 2005: select name from sys.views
In SQL Server 2005, you can find whether a view is replicated. Execute the following query.
select * from sys.views where is_replicated =1

Find all triggers in database
SQL SERVER 2000: select name from sysobjects where type='TR'
SQL SERVER 2005: select name from sys.triggers where parent_class=1
In SQL Server 2005, the triggers can be Assembly trigger (CLR) or a SQL trigger. In addition, we can find whether the trigger is an AFTER trigger or INSTEAD of trigger.
Select name from sys.triggers where type='TA'
Select name from sys.triggers where type='TR'
Select name from sys.triggers where is_instead_of_trigger=1

Find all Sql logins in a server
SQL SERVER 2000: select * from master.syslogins where isntgroup=0 and isntname=0
SQL SERVER 2005: select * from sys.sql_logins

Find all dependencies of the SQL Object in a database
SQL SERVER 2000: Select * from sysdepends
SQL SERVER 2005: Select * from sys.sql_dependencies

Find all data types in SQL Server
SQL SERVER 2000: Select * from systypes
SQL SERVER 2005: Select * from sys.systypes

Find all error messages in SQL Server
SQL SERVER 2000: select * from master.sysmessages
SQL SERVER 2005: select * from sys.messages

Find all the database files of the current database.
SQL SERVER 2000: Select name,filename from sysfiles
SQL SERVER 2005: Select name, physical_name from sys.database_files

Find the type of index.
SQL SERVER 2000: We have to use id column to determine the type of index from 0,1 or 255.
SQL SERVER 2005: select object_name(object_id),name, type_desc from sys.indexes where type_desc ='CLUSTERED'
Select object_name(object_id),name, type_desc from sys.indexes where type_desc ='HEAP'
Select object_name(object_id),name, type_desc from sys.indexes where type_desc ='NONCLUSTERED'
Select object_name(object_id),name, type_desc from sys.indexes where type_desc ='XML'

Basic database terms

Primary Key
A column or set of columns that uniquely identify all the rows in a table. Primary keys do not allow null values. No two rows can have the same primary key value; therefore, a primary key value always uniquely identifies a single row. More than one key can uniquely identify rows in a table, each of these keys is called a candidate key. Only one candidate can be chosen as the primary key of a table; all other candidate keys are known as alternate keys. Although tables are not required to have primary keys, it is good practice to define them. In a normalized table, all of the data values in each row are fully dependent on the primary key. For example, in a normalized employee table that has EmployeeID as the primary key, all of the columns should contain data related to a specific employee. This table does not have the column DepartmentName because the name of the department is dependent on a department ID, not on an employee ID.

Triggers
Triggers A stored procedure that executes when data in a specified table is modified. Triggers are often created to enforce referential integrity or consistency among logically related data in different tables.

Index
Index In a relational database, a database object that provides fast access to data in the rows of a table, based on key values. Indexes can also enforce uniqueness on the rows in a table. SQL Server supports clustered and nonclustered indexes. The primary key of a table is automatically indexed. In full-text search, a full-text index stores information about significant words and their location within a given column. Clustered index An index in which the logical order of the key values determines the physical order of the corresponding rows in a table. Nonclustered index An index in which the logical order of the index is different than the physical, stored order of the rows on disk.

Constraint
A property assigned to a table column that prevents certain types of invalid data values from being placed in the column. For example, a UNIQUE or PRIMARY KEY constraint prevents you from inserting a value that is a duplicate of an existing value, a CHECK constraint prevents you from inserting a value that does not match a search condition, and NOT NULL prevents you from inserting a NULL value.

Stored Procedures
A precompiled collection of Transact-SQL statements stored under a name and processed as a unit. SQL Server supplies stored procedures for managing SQL Server and displaying information about databases and users. SQL Server-supplied stored procedures are called system-stored procedures.

Functions
A piece of code that operates as a single logical unit. A function is called by name, accepts optional input parameters, and returns a status and optional output parameters. Many programming languages support functions, including C, Visual Basic, and Transact-SQL. Transact-SQL supplies built-in functions, which cannot be modified, and supports user-defined functions, which can be created and modified by users.

View
A database object that can be referenced the same way as a table in SQL statements. Views are defined using a SELECT statement and are analogous to an object that contains the result set of this statement.