SQL Server : Transform Columns into Rows

Suppose, we have the table “tblSample” with the following data.

Seq	Month	New Policy Count	New Premium	Claims Recorded	Claims Cost	Loss Ratio
1	2010/06/01	1000	100000.9	5	66666.98	66.66
2	2010/05/01	1100	200000.9	10	55555.98	27.77
3	2010/04/01	1200	300000.9	15	44444.98	14.81
4	2010/03/01	1300	400000.9	20	33333.98	8.33
5	2010/02/01	1400	500000.9	25	22222.98	4.44
6	2010/01/01	1500	600000.9	30	11111.98	1.85

The query to transform the Columns of this table to Rows:

SELECT   Seq

        ,ROW_NUMBER() OVER(PARTITION BY [Month] ORDER BY Seq) AS 'RowNumber'
        ,CONVERT(VARCHAR, [Month], 111) AS [Month], Title, aValue

INTO #temp

FROM

     (SELECT  Seq 
             ,[Month]
             ,[New Policy Count]

             ,[New Premium]          
             ,[Claims Recorded]    
             ,[Claims Cost] 
             ,[Loss Ratio]

      FROM tblSample) p

      UNPIVOT      
     (aValue FOR  Title IN ([New Policy Count],[New Premium],[Claims Recorded],[Claims Cost],[Loss Ratio]))

AS unpvt

DECLARE @SQLQuery     VARCHAR(MAX)

       ,@PivotColumns VARCHAR(MAX)

SET    @PivotColumns = ''

SELECT @PivotColumns = @PivotColumns+ '['+ [Month] +'],' FROM (SELECT
DISTINCT [Month] FROM #TEMP) a

SET    @PivotColumns = SUBSTRING(@PivotColumns, 1,LEN(@PivotColumns) - 1)

SET @SQLQuery = 'SELECT Title,' + @PivotColumns +

             'FROM (SELECT [Month], RowNumber, Title, aValue FROM #temp) s
              PIVOT (SUM(aValue) FOR [Month] IN (' + @PivotColumns + ')) p
              ORDER BY RowNumber'

EXEC (@SQLQuery)

DROP TABLE #temp

The final output looks like:

Title	2010/01/01	2010/02/01	2010/03/01	2010/04/01	2010/05/01	2010/06/01
New Policy Count	1500	1400	1300	1200	1100	1000
New Premium	600000.9	500000.9	400000.9	300000.9	200000.9	100000.9
Claims Recorded	30	25	20	15	10	5
Claims Cost	11111.98	22222.98	33333.98	44444.98	55555.98	66666.98
Loss Ratio	1.85	4.44	8.33	14.81	27.77	66.66

Patterns : a look

A pattern is an especially clever and insightful way of solving a particular class of problems. It represents a complete idea within a program, and thus it can sometimes appear at the analysis phase or high-level design phase. It can exist at many levels from very low-level specific solutions to broadly generalized system issues. A patterns cover various ranges of scale and abstraction and are grouped into three categories:
    1. Architectural Pattern
        An architectural pattern expresses a fundamental structural organization schema for software systems. It provides a set of predefined subsystems, specifies their responsibilities, and includes rules and guidelines for organizing the relationships between them.
    2. Design Patterns
        A Design pattern provides a scheme for refining the subsystems or components of a software system, or the relationships between them. It describes a commonly-recurring structure of communication components that solves a general design problem within a particular context.
    3. Idioms
        An idioms is a low-level pattern specific to a programming language. An idiom describes how to implement particular aspects of components or the relationships between them using the features of the given language.

Some useful definitions of Design Patterns have emerged as the literature in this field has expanded.

• “Design patterns are recurring solutions to design problems you see over and over.” (The Smalltalk Companion)
• “Design patterns constitute a set of rules describing how to accomplish certain tasks in the realm of software development.” (Pree 1994)
• “Design patterns focus more on reuse of recurring architectural design themes, while frameworks focus on detailed design and implementation.” (Coplien and Schmidt 1995)
• “A pattern addresses a recurring design problem that arises in specific design situations and presents a solution to it.” (Buschmann et al., 1996)
• “Patterns identify and specify abstractions that are above the level of single classes and instances, or of components.” (Gamma et al., 1993)

SQLOS : A User-mode Operating System for SQL Server

    The SQLOS is core to SQL Server's architecture, and first implemented in SQL Server 2005. It is a framework used by components in SQL Server for scheduling, I/O, and memory management. Because of the highly specialized requirements of SQL Server, it implements its own thread and memory management system. It is used for low-level operations such as scheduling, I/O completion, memory management, buffer pool management, resource management, synchronization primitives and deadlock detection. In summary, the SQLOS is a thin user-mode layer that sits between SQL Server and Windows.
    SQLOS also recognizes and accommodates NUMA (non-uniform memory access) architectures, to handle the systems which scale above 32 cores. In database, scheduled query run must be complete before starting the next one. SQLOS manages that with its schedulers: each logical CPU (one care in multi-core chip) has a non-preemptive scheduler in SQLOS that assigns one thread at a time to the CPU. SQLOS schedulers are hyper-threading aware, and can move a task from a hyper-threaded CPU to a "real" CPU that becomes idle.

The benefits of SQLOS include 

• performance (primary goal)
• enhanced scheduling and processor locality 
• a dedicated administrative connection
• support for hot adding of memory and CPU resources
• a hosting interface for services such as CLR and SQL Server 2008 Reporting Services

    Reporting Services are runs under SQLOS rather than IIS, to handle large report request in a reasonable way. IIS is not designed to service long-running requests like database reports. Only Microsoft SQL Server can benefit from SQLOS. It is a tightly coupled, private layer of SQL Server. Even an extended stored procedure (a function in a DLL that SQL Sever launches) get called, it is thrown on the mercy of the Windows scheduler rather than receiving the benefit of SQLOS's non-preemptive scheduler.

    SQLOS is not a way to port the SQL Server architecture to other platforms like Linux or Mac OS so it's not an OS abstraction layer. It doesn't wrap all the OS APIs like other frameworks such as .NET, which is why it's referred to as a "thin" user-mode layer.

SQL SERVER MANAGEMENT DATA WAREHOUSE

    The Management Data Warehouse is intended as a centralized repository for performance data from servers across an enterprise, providing an out-of-the-box solution to performance management for SQL Server administrators who are responsible for performance and capacity management. The MDW is populated using data collectors. Three data collectors are set up as default by MDW setup wizard and further collectors can be confi gured as required. The MDW offers a scalable, customizable reporting solution that requires a minimal investment of configuration effort before providing value. The data collection solution utilizes SQL Server components such as SQL Server Agent jobs, SQL Server Integration Services to load performance data, and a relational data warehouse; and data is presented using SQL Server Reporting Services.
    The MDW is not a monitoring solution, as it has no capability to configure acceptable performance or operational thresholds and alerts; and it doesn’t provide real-time performance information (Activity Monitor provides this information). Nor does the MDW provide a centralized overview of all servers within an organization. In most medium-size and enterprise environments, it is still necessary for capacity and performance planning across the server estate, rather than on a per-server basis. However, used for the right purpose, MDW provides better insight than what was available before, as much of the valuable data available withinDMVs can be harvested and stored for mid and longterm performance trending and analysis.

SQL Server DMV (Dynamic Management View)

The concept of Dynamic Management View (DMV) introduced in SQL Server 2005. The DMV designed to provide information about the current state of SQL Server. This helps to monitor the health of a server instance, diagnose problems and tune performance.

The DVMs are composed of both views and table-valued functions. There are two types of dynamic management views:

1.   Server Scoped DVM

2.   Database Scoped DVM

All DVMs exist in the “sys” schema and follow the naming convention “dm_*”. To query DVMs requires SELECT permission on object and VIEW SERVER STATE or VIEW DATABASE STATE permission.DVMs can be referenced in Transact-SQL statements.

Some DVMs:

sys.dm_os_sys_info: Returns a miscellaneous set of useful information about the computer, and about the resources available to and consumed by SQL Server

sys.dm_os_buffer_descriptors: Returns information about all the data pages (distributed according to database, object, or type) that are currently in the SQL Server buffer pool

Related Queries:

• To check the space used by each database in the data cache:

    SELECT count(*)*8/1024                 AS 'Cached Size (MB)'

          ,CASE database_id

             WHEN 32767 THEN 'ResourceDb'

             ELSE db_name(database_id)

           END                           AS 'Database'

    FROM  sys.dm_os_buffer_descriptors

    GROUP BY db_name(database_id) ,database_id

    ORDER BY 'Cached Size (MB)' DESC

• To check the dirty pages exists in each database:

    SELECT db_name(database_id)  AS 'Database'

          ,count(page_id)        AS 'Dirty Pages'

    FROM  sys.dm_os_buffer_descriptors

    WHERE is_modified =1

    GROUP BY db_name(database_id)

    ORDER BY count(page_id) DESC

• To check the SQL Server machine information and its available resources:

    SELECT cpu_count             AS 'Number of logical CPUs in the server'

       ,hyperthread_ratio        AS 'Ratio of logical and physical CPUs'

       ,physical_memory_in_bytes AS 'Amount of physical memory available'

       ,virtual_memory_in_bytes  AS 'Amount of virtual memory available'

       ,bpool_committed          AS 'Committed physical memory in buffer pool'

       ,os_priority_class        AS 'Priority class for SQL Server process'

       ,max_workers_count        AS 'Maximum number of workers which can be created'

       FROM sys.dm_os_sys_info

The .Net Wave: Changing Face of Enterprise Applications

Growth of Web-based enterprise applications continues to skyrocket. The fastest growing segment of this booming marketing is .NET applications.

Microsoft .NET―Framework Summary

·           •      Built for mission-critical applications

·           •      Can scale to run the largest Web properties in the world

o    Exceptional developer productivity

o    Built for operational excellence

•      Dynamic Systems Initiative (DSI)

o    Design for Operations (DFO)

o    Knowledge-driven management

o    Virtualized infrastructure

o    Complex development demands sophisticated management tools

Sample Application Architecture Diagram

Agile Software Development

Agile Manifesto

Uncovering better ways of developing software by doing it and helping others to do it. To achieve:

Individuals and interactions over processes and tools

Working software over comprehensive documentation

Customer collaboration over contract negotiation

Responding to change over following a plan

Principles

· Highest priority is to satisfy the customer through early and continuous delivery of valuable software.

· Welcoming changing requirements, even late in development. Agile processes harness change for the customer’s competitive advantage.

· Deliver working software frequently, from a couple of weeks to a couple of months, with a preference to shorter time scales.

· Business people and developers must work together daily throughout the project.

· Build projects around motivated individuals. Give them the environment and support they need, and trust them to get the job done.

· The most efficient and effective method of conveying information to and within a development team is face-to-face conversation. Working software is the primary measure of progress.

· Agile processes promote sustainable development. The sponsors, developers, and users should be able to maintain a constant pace indefinitely.

· Continuous attention to technical excellence and good design enhances agility.

· Simplicity – the art of maximizing the amount of work not done- is essential.

· The best architectures, requirements, and designs emerge from selforganized teams.

· At regular intervals, the team reflects on how to become more effective, then tunes and adjusts its behavior accordingly.

Agile Methods

The number of methods that claim to align the Agile Manifesto will continue to grow with the popularity of the agile software methodologies. The early initial methodologies include:

1. Extreme Programming

2. SCRUM

3. Crystal

4. Feature Driven Development

5. Lean Development

6. Adaptive Software Development

7. DSDM