Microsoft SQL Server In A Glance

SQL Server In a glance

Data manipulation Language (DML) – Insert/Update/Delete/Select

Data Dynamic Language (DDL) – Create/Alter/Drop/Truncate

Data Control Language (DCL) – Grant/Revoke

Dynamic Management View (DMV) – Server State/Monitor health of system

Transactional Control Language (TCL) – Commit/Rollback

ACID Properties

A – Atomicity – All or no transaction

C – Consistency – Move a transaction from one state to another

I - Isolation – Keep transaction separate

D – Durability – Make sure no data lost

Keys

Primary Key – Uniquely identifying each row in a table

Foreign Key – It is used to generate the relationship between the tables.

Composite/Compound Key –key that consist of two or more columns that uniquely identify rows in a table.

Unique Key - Uniquely identifying a row in a table. It can also store Null values

Candidate Key – key that consist of two or more columns that uniquely identify rows in a table and one key chosen as primary key.

Surrogate Kay – Identity Column

Joins

Inner Join – return the matching rows present in both the tables

Left Outer Join – return all the rows of left side table

Right Outer Join – return all the rows of right side table

Full Outer Join – Combine the efforts of both left and right outer joins

Cross Join – return the Cartesian result set.

 Self-Join – Joining table to it-self

Normalization

1NF – Only atomic values (Remove the duplicate columns from table)

2NF – Remove subset of data that apply to multiple rows of a table

3NF – Remove non-dependent column

Byce Code – Every determent must have a candidate key

5NF – 3NF and no multivalued dependencies ()

Locks

Shared – Others transaction can read the data but cannot write

Exclusive – Cannot read and write

Index

Cluster Index – Records the way table is physically stored

Non-Cluster Index – create a completely separate object within the table

Key set – Only key store not data

XML – Index on XML column

Data Integrity

Entity – No Duplicate

Domain – Enforce valid entries for a column

Referential – Rows cannot be deleted

User Defined – Enforce specific rule(s)

Function VS Stored Procedure

SNo        Function                                                              Stored Proc

1              Always Return Value                                      Optional

2              Only Input Parameter                                    Input & output parameter

3              Can be called from stored Proc                  Can not call from function

Varchar VS nVarchar

SNo        Varchar                                                                                nVarchar

1              Store data in ANSII and take 1 byte          Store data in Unicode (multilingual) and takes 2 bytes

Temp Table VS Temp Variables

SNo        Temp Table                                                        Temp Variables

1              Transaction Log recorded                             Transaction log not recorded

2              Stored Proc with temp table cannot        Can be precompiled.. Good Performance

be pre compiled

3              Support DDL                                                       Not supper DDL

4              Not allowed in user defined func              Allowed in user defined function

Trigger

Automatically trigger on the occurrence of an event (DDL, DML, Logon)

Before/After/InsteadOf(Perform error, value checking.. Override the standard action of the triggering)

Cursor

Adv – Row by row operation.. Quick and dirty

Dis Adv – Reside in memory (Problem for other process)…Speed & Performance

Other Important Things

Minus – All the rows of first select statement that are not returned by selected select statement.

Union – Combine rows of queries and remove the duplicate records

Union All – Combine rows of queries and keep the duplicate records.

Intersect – Only common rows in both the queries

Except – Keep rows of left query those are not in right query

Implicit Transaction – Auto commit, no begin/end transaction

Coalesce – Return first non-null expression

@@transcount – Active transaction of current connection

@@identity – Last inserted row id.

RAND – Random Number

Transaction – Its take database from one state to another and at the end of transaction system must be in the prior state.

Delete Duplicate

1.       Use of temp table – Take the distinct rows and store them in a temp table then truncate the tale and insert the data back again from the temp table

2.       Using RANK function

Delete from table1

Inner join (select *. RANK() over (partition by key column name(s) order by column name(s)) as RowNum from table1) table2 where table2.RowNum>1