Sunday, October 17, 2010

Isolation Level

  • A degree to which one transaction is isolated from another transactions.
  • Lower the transaction, increases concurrency and decreases data correctness.
  • Total 5 type of Isolation Level
    • READ UNCOMMITTED
      • Lowest level 
      • does not issue SHARED LOCK - means other transactions can see the data 
      • are not blocked by EXCLUSIVE LOCK - means they can read uncommitted modifications, this is same as using NOLOCK
      • This level produces "dirty read"
    • READ COMMITTED
      • Default level in SQL Server
      • cannot read the data which has been modified but not committed
      • it prevents "dirty read"
      • it produces "non repeatable read"
    • REPEATABLE READ
      • contains features of READ COMMITTED+ cannot modify data that has been read by other transaction until it gets completed
    • SERIALIZABLE
      • strongest level
      • contains features of REPEATABLE READ + cannot insert data if a table is in use by some other transaction (read/update)
    • SNAPSHOT
      • introduced in 2005
  • here is the comparison of basic 4 types of isolation levels how it behaves differently for different DML Operation





Session 1
(Within Transaction)

Session 2



SELECT
UPDATE /
DELETE
INSERT
READ UNCOMMITTED
SELECT
Yes
Yes
Yes
UPDATE / DELETE
Yes
No
Yes
INSERT
Yes
No
Yes






READ COMMITTED
SELECT
Yes
Yes
Yes
UPDATE / DELETE
No
No
Yes
INSERT
No
No
Yes






REPEATABLE READ
SELECT
Yes
No
Yes
UPDATE
No
No
Yes
INSERT
No
No
Yes






SERIALIZABLE
SELECT
Yes
No
No
UPDATE
No
No
No
INSERT
No
No
Yes



  • SET TRANSACTION LEVEL REPEATABLE READ. This t-sql command sets different transactional level
  • only 1 isolation level can be set at any point of time
  • Any isolation level can be switched to any isolation level during a transaction (exception: SNAPSHOT)
  • If isolation level is set inside stored procedure, then at the end of execution of SP, isolation level will be set to original what it was before execution of SP.
For more info about Locks


Wednesday, October 6, 2010

Tricky Questions

ORDER OF EXECUTION FOR INTEGRITY CHECK
1.       Identity Insert Check
2.      Nullability constraint
3.      Data type check
4.      Instead of trigger
5.      Primary key
6.      Check constraint
7.      Foreign key
8.     DML Execution (update statements)
9.      After Trigger
---------------------------------------------------------------------
FIND OUT # OF RECORDS FROM A TABLE
1.       SELECT rows FROM sys.sysindexes WHERE id = OBJECT_ID('UserManagement.CISFUser') AND indid < 2
2.      SELECT COUNT(*) FROM UserManagement.CISFUser
3.      SELECT COUNT(1) FROM UserManagement.CISFUser
4.      SP_SPACEUSED 'UserManagement.CISFUser'
---------------------------------------------------------------------
HOW TO CREATE SORTED VIEW
CREATE VIEW vw
AS
SELECT TOP 100 PERCENT * FROM TableName ORDER BY ColumnName
---------------------------------------------------------------------
COUNT(*) Vs. COUNT(ColumnName)

COUNT(*) Ã  total number of rows from a table
COUNT(ColumnName) Ã  total number of rows from a table WHERE ColumnName IS NOT NULL
-------------------------------------------------------------------
DELETE Vs. TRUNCATE
Delete
Truncate
Speed
Slower
Faster
WHERE
Can be applied
Cannot be applied
IDENTITY
Cannot reset
Will reset
TRIGGER
Will fire
Won’t fire
Transaction Log
Will be logged
Won’t be logged
REFERENTIAL INTEGRITY
If Child table doesn’t have corresponding record from master table then it will allow
Will never ever allow if any referential integrity exists; no matter child table has record or not
Table Variable
Can be deleted
Cannot be truncated
CDC
Will allow
Won’t allow if CDC is enabled on table

----------------------------------------------------------------------------------
TEMP TABLE Vs. TABLE VARIABLE
Temp Table
Table variable
Syntax
CREATE TABLE #TEMP (A INT)
DECLARE @TEMP TABLE (A INT)
Storage
Tempdb
Memory
Locking & Logging
Enabled
Disabled (improves performance)
Index
Possible
Not possible
Truncate
Possible
Not possible
Scope
Will be available in nested SP also
Will be limited
Dynamic Query
Will be allowed inside Dynamic Query
Won’t be allowed
When to use
When large number of records are there and which involves search.
Index creation will help us
Small number of records are used. Logging and Locking won’t be there so will be faster
Final word
No theory has proved which one is good and which one is not. It all depends on requirement and number of records
---------------------------------------------------------------------
SELECT 3/4. WHAT IT WILL RETURN?

ZERO. If you say SELECT 3.0/4.0 then it will return 0.75

---------------------------------------------------------------------
HOW TO INSERT DESIRED VALUE IN IDENTITY COLUMN?

CREATE TABLE IdentityCheck (INT IDENTITY(1,1), B VARCHAR(10))
INSERT INTO IdentityCheck (B) VALUES ('A'), ('B')

SET IDENTITY_INSERT IdentityCheck ON

INSERT INTO IdentityCheck (A,B) VALUES (5, 'C')

SET IDENTITY_INSERT IdentityCheck OFF

---------------------------------------------------------------------
HOW TO RESET IDENTITY TO DESIRED VALUE?
DBCC CHECKIDENT(IdentityCheck, 'RESEED',100)
Next value would be 101. Even you can reset to negative values or some already existing values. It won't throw any error but when you try to insert only that time it will check and it will throw error depends on other constraints like PK, Unique, Check etc..


---------------------------------------------------------------------
WHAT ARE THE DEFAULT SYS DATABASE AND THEIR PURPOSE?
MASTER --> It holds the information about all the databases and contains system objects
MSDB --> It contains information about sql agent, job, dts pckg & some replication information
TEMPDB --> It contains temporary SPs and Tables (global & local)
MODEL --> It is template database used in creation of any new database
---------------------------------------------------------------------
UNION Vs. UNION ALL
UNION will give only distinct values and UNION ALL will give all the values
However, output of all below 3 queries will be SAME.
-- Query 1
      SELECT 1
      UNION
      SELECT 1
      UNION ALL
      SELECT 2

-- Query 2
      SELECT 1
      UNION ALL
      SELECT 1
      UNION 
      SELECT 2

-- Query 3
      SELECT 1
      UNION
      SELECT 1
      UNION
      SELECT 2
--------------------------------------------------------------------
DIFFERENT WAYS TO IMPORT DATA
- BCP 
BULK INSERT
- OPENROWSET
- OPENQUERY
- LINKED SERVER
- OPENDATASOURCE