Table Partitioning

• Partitioned tables are a new feature available in SQL Server version 2005, aimed mainly at improving the performance of large database systems. 
• The feature is only available for enterprise and developer edition. 
• For other editions you can get a similar functionality with a partitioned view

STEP : 1 Create Database with multiple FileGroups and multiple Files

Under respective path mentioned, following files would be created

• PartitionDemo.mdf
• PartitionDemo_log.ldf
• PartitionDemoFile1.ndf
• PartitionDemoFile2.ndf

It would be advisable if you create Primary FileGroup Files and NewFileGroup Files on separate, that will boost the performance.

STEP : 2 Create Partition Range Function

USE PartitionDemo;GO--- Step 2 : Create Partition Range FunctionCREATE PARTITION FUNCTION PartitionDemo_Range (INT)AS RANGE LEFT FOR
VALUES (10,20,30);GO
Basically this will create 4 partitions

• Partition 1 ------- <= 10
• Partition 2 ------- >10 and <= 20
• Partition 3 ------- >20 and <= 30
• Partition 4 ------- >30

Here LEFT keyword specifies that boundary values would be considered in left bucket. so 20 would come under Partition2 bucket rather than Partition2 bucket

STEP : 3 Create Partition Scheme


USE PartitionDemo;GO--- Step 3 : Attach Partition Scheme to FileGroupsCREATE PARTITION SCHEME PartitionDemo_SchemeAS PARTITION PartitionDemo_RangeTO ([PRIMARY], [FG1], [FG2], [FG2]);GO
This is a process of assigning partitions to different filegroups.
This says that 

• Partition 1 --------- Primary FileGroup
• Partition 2 --------- FG1  FileGroup
• Partition 3,4 ------- FG2  FileGroup

STEP : 4 Create Table with Partition Key and Partition Scheme


USE PartitionDemo;GO--- Step 4 : Create Table with Partition Key and Partition SchemeCREATE TABLE TestTable(ID INT NOT NULL,Date DATETIME)ON PartitionDemo_Scheme (ID);GO

So this says that table has been linked with scheme and "ID" column will decide how record would be distributed among different partitions


STEP : 5 Create Index on Partitioned Table


USE PartitionDemo;GO--- Step 5 : Create Index on Partitioned TableCREATE UNIQUE CLUSTERED INDEX IX_TestTableON TestTable(ID)ON PartitionDemo_Scheme (ID);GO
This is highly recommended to improve performance


STEP : 6 Insert Data into Partitioned Table


USE PartitionDemo;GO--- Step 6 : Insert Data in Partitioned TableINSERT INTO TestTable (ID, Date) -- Inserted in Partition 1VALUES (1,GETDATE());INSERT INTO TestTable (ID, Date) -- Inserted in Partition 1VALUES (10,GETDATE());INSERT INTO TestTable (ID, Date) -- Inserted in Partition 2VALUES (11,GETDATE());INSERT INTO TestTable (ID, Date) -- Inserted in Partition 2VALUES (20,GETDATE());INSERT INTO TestTable (ID, Date) -- Inserted in Partition 3VALUES (21,GETDATE());INSERT INTO TestTable (ID, Date) -- Inserted in Partition 3VALUES (30,GETDATE());INSERT INTO TestTable (ID, Date) -- Inserted in Partition 4VALUES (31,GETDATE());GO
STEP : 7 Verify rows against different partitions


USE PartitionDemo;GO--- Step 8 : Verify Rows Inserted in PartitionsSELECT *FROM sys.partitionsWHERE OBJECT_NAME(OBJECT_ID)='TestTable';GO
SOMETHING EXTRA

• We can add/remove partition using SPLIT and MERGE keywords
• If the index contains the partitioning column then the index is referred to as being 'aligned' with the table
• Partitioning column can be number, date, string datatype
• We can consider Calculated column as Partition key also

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

http://www.sqlteam.com/article/introduction-to-locking-in-sql-server

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 (A 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