List All Primary Key Columns Within a Database

SELECT
schema_name(ta.schema_id)  SchemaName
,ta.name  TableName
,col.name  ColumnName
,indcol.key_ordinal IndexPositoin
,ind.name IndexName
,ind.type_desc IndexType
,ind.fill_factor Fill_Factor
from sys.tables ta inner join sys.indexes ind
on ind.object_id = ta.object_id
inner join sys.index_columns indcol
on indcol.object_id = ta.object_id
and indcol.index_id = ind.index_id
inner join sys.columns col
on col.object_id = ta.object_id
and col.column_id = indcol.column_id
where ind.is_primary_key = 1
order by 1,2,4

List of all FK in a database

SELECT
OBJECT_NAME(referenced_object_id) 'Referenced table'
,OBJECT_NAME(parent_object_id) 'Parent table'
,cref.NAME 'Referenced column name'
,c.NAME 'Parent column name'
FROM sys.foreign_key_columns fkc INNER JOIN sys.columns c
ON fkc.parent_column_id = c.column_id AND fkc.parent_object_id = c.object_id
INNER JOIN sys.columns cref
ON fkc.referenced_column_id = cref.column_id AND fkc.referenced_object_id = cref.object_id
--where   OBJECT_NAME(parent_object_id) = 'tablename'
order by 1, 2

SSIS quick notes

http://www.bidn.com/blogs/Daniel/ssas/1361/ssis-blocking-non-blocking-and-partially-blocking-transformations
http://www.developer.com/db/top-10-common-transformations-in-ssis.html

A Non Blocking transformation is a dam that just lets the water spill over the top
A Blocking transformation is a dam that lets nothing through until the entire volume of the river has flowed into the dam

Synchronous are components like the Conditional Split or Derived Column Transformation where rows flow into memory buffers in the transformation and the same buffers come out. 

Asynchronous transformation has two types, fully blocking and partial blocking.

Synchronous = non blocking. input and output use same memory buffer
Asynchronous = partial blocking and blocking. Need new memory buffer for output
blocking = no output until all input memory are processed

Merge Join is similar to database join
Merge needs input to be sorted but Union All does not requires this

Non Blocking

• Audit
• Character Map
• Conditional Split
• Copy Column
• Data Conversion
• Derived Column
• Import Column
• Lookup
• Multicast
• Percentage sampling
• Row count
• Row sampling
• Script component

Partially Blocking

• Data mining
• Merge
• Merge Join
• Pivot/Unpivot
• Term Extraction
• Term Lookup
• Union All

Blocking

• Aggregate
• Fuzzy Grouping
• Fuzzy Lookup
• Sort

Some of the best sql free tools

http://www.johnsansom.com/top-10-free-sql-server-tools/

Some of the best sql free tools

http://www.johnsansom.com/top-10-free-sql-server-tools/

Find all identity columns in SQL server

select
table_schema
,table_name
, column_name
, data_type
, ordinal_position
from information_schema.columns
where columnproperty(object_id(table_name), column_name,'IsIdentity') = 1
order by 1,2,3

Quick notes about database normalization

• 1st form:
• No repeated data group
• Has Primary Key
• 2nd form:
• 1st form already
• remove partial dependence (no-key attribute depends on ALL key attributes)
• add link table with primary key
•  3rd form:
• 2nd form already
• remove transitive dependence (no-key attribute depends ONLY on key attributes, nothing else, that is, not depend on other no-key attribute)
• add a new table with FK constraint.
• reference: https://www.youtube.com/watch?v=c7DXeY3aIJw