SQL
All the FAQS related to SQL
What is ACID fundamental? What are transactions in SQL SERVER ?
Dec 6th
A transaction is a sequence of operations performed as a single logical unit of work. A logical unit of work must exhibit four properties, called the ACID (Atomicity, Consistency, Isolation, and Durability) properties, to qualify as a transaction:
Atomicity
- A transaction must be an atomic unit of work; either all of its data modifications are performed or none of them is performed.
Consistency
- When completed, a transaction must leave all data in a consistent state. In a relational database, all rules must be applied to the transaction’s modifications to maintain all data integrity. All internal data structures, such as B-tree indexes or doubly-linked lists, must be correct at More >
Can we suggest locking hints to SQL SERVER ?
Dec 6th
We can give locking hints that helps you over ride default decision made by SQL Server. For instance, you can specify the ROWLOCK hint with your UPDATE statement to convince SQL Server to lock each row affected by that data modification. Whether it’s prudent to do so is another story; what will happen if your UPDATE affects 95% of rows in the affected table? If the table contains 1000 rows, then SQL Server will have to acquire 950 individual locks, which is likely to cost a lot more in terms of memory than acquiring a single table lock. So think More >
What are different transaction levels in SQL SERVER?
Dec 6th
Transaction Isolation level decides how is one process isolated from other process.Using transaction levels you can implement locking in SQL SERVER.
There are four transaction levels in SQL SERVER :-
READ COMMITTED
The shared lock is held for the duration of the transaction, meaning that no other transactions can change the data at the same time. Other transactions can insert and modify data in the same table, however, as long as it is not locked by the first transaction.
READ UNCOMMITTED
No shared locks and no exclusive locks are honored. This is the least restrictive isolation level resulting in the best concurrency but the least More >
If locking is not implemented what issues can occur?
Dec 6th
Following are the problems that occur if you do not implement locking properly in SQL SERVER.
Lost Updates
Lost updates occur if you let two transactions modify the same data at the same time, and the transaction that completes first is lost. You need to watch out for lost updates with the READ UNCOMMITTED isolation level. This isolation level disregards any type of locks, so two simultaneous data modifications are not aware of each other. Suppose that a customer has due of 2000$ to be paid. He pays 1000 $ and again buys a product of 500$. Lets say that these two transactions More >
What is the difference between DELETE TABLE and TRUNCATE TABLE commands?
Dec 6th
Following are difference between them :-
- DELETE TABLE syntax logs the deletes thus make the delete operation slow. TRUNCATE table does not log any information but it logs information about deallocation of data page of the table so TRUNCATE table is faster as compared to delete table.
- DELETE table can have criteria while TRUNCATE can not.
- TRUNCATE table can not trigger.
Note :- Thanks to all the readers for pointing out my mistake for the above question in my first edition. I had mentioned that TRUNCATE table can not be rolled back while delete can be.
What is RAID and how does it work ?
Dec 6th
Redundant Array of Independent Disks (RAID) is a term used to describe the technique of improving data availability through the use of arrays of disks and various data-striping methodologies. Disk arrays are groups of disk drives that work together to achieve higher data-transfer and I/O rates than those provided by single large drives. An array is a set of multiple disk drives plus a specialized controller (an array controller) that keeps track of how data is distributed across the drives. Data for a particular file is written in segments to the different drives in the array rather than being written More >
What is fillfactor ?
Dec 6th
The ‘fill factor’ option specifies how full SQL Server will make each index page. When there is no free space to insert new row on the index page, SQL Server will create new index page and transfer some rows from the previous page to the new one. This operation is called page splits. You can reduce the number of page splits by setting the appropriate fill factor option to reserve free space on each index page. The fill factor is a value from 1 through 100 that specifies the percentage of the index page to be left empty. The default More >
What are fact tables and dimension tables in OLAP ?
Dec 6th
The dimensions and measures are physically represented by a star schema. Dimension tables revolve around fact table. A fact table contains a column for each measure as well as a column for each dimension. Each dimension column has a foreign-key relationship to the related dimension table, and the dimension columns taken together are the key to the fact table.
What are levels in dimensions ?
Dec 6th
Dimensions are arranged in hierarchical levels, with unique positions within each level. For example, a time dimension may have four levels, such as Year, Quarter, Month, and Day. Or the dimension might have only three levels, for example, Year, Week, and Day. The values within the levels are called members. For example, the years 2002 and 2003 are members of the level Year in the Time dimension.
What are dimensions in OLAP ?
Dec 6th
Dimensions are the categories of data analysis. For example, in a revenue report by month by sales region, the two dimensions needed are time and sales region. Typical dimensions include product, time, and region.
