SQL:Calculating Ideal Default Buffer Max Row and Default Buffer Size in SSIS

Calculating Default Buffer Max Row  and Default Buffer Size

Calculate the Table Size using

Exec sp_spaceused  ‘tablename’

This should give you table size occupied in KB and the no of Records in the table

SQL:PIVOT AND UN-PIVOT - SQL

PIVOT AND UN-PIVOT - SQL

In several scenarios we would have to change the rows to columns and vice versa in a our select query to overcome some bad database designs.Unpivot and pivot does exactly that. All though SSIS provides you with the feature to do unpivot and pivot , it is highly advisable to do it in sql query , unless you have the data coming  from a non – oledb source or from two different sources.

UNPIVOT

Unpivot rotates the columns of the table to the column values.

Following shows a simple example

Term	ENGLISH	MATHS	SCIENCE	FRENCH
MidTerm1	78	86	76	78
MidTerm2	77	44	76	56
MidTerm3	98	47	87	76
MidTerm4	35	89	77	69

SELECT   Term ,

        Subject,

         Marks

FROM     (SELECT Term,ENGLISH,MATHS,SCIENCE,FRENCH

          FROM Scores WHERE STUDENT_ID = 345950) p

         UNPIVOT

         (Marks

          FOR Subject IN ([ENGLISH],[MATHS],[SCIENCE],[FRENCH] ) ) AS unpvt

ORDERBY Term,

        Subject;

GO