An easy technique for inverting a SQL table

The following is an easy technique for inverting a table structure in T-SQL. I will explain the mechanics behind the technique and provide the sample code that we used on a recent project. The original code was more than 200 lines and was extremely complex. While the code was more than sufficient for our environment, there may be other ways that accomplish the same goal, this post aims to present one of the available options.

The first step is to create a temp table that holds the table structure containing the rows of data that are going to be inverted into columns. You need to add an additional integer column that contains the row number for each of the rows in the table. This is automatically populated using the Row_Number() function.

Declare @Count int, @i int, @SQL nVarchar(3000) Create Table #tmp(ModuleHierarchyID Int, WebPartCode Varchar(10), SVGFilename Varchar(255), XSLTFilename Varchar(255), RowNumber Int)

The next step is to populate the temp table with the data that you want to invert into a column structure.

Insert into #tmp(ModuleHierarchyID, WebPartCode, SVGFilename, XSLTFilename, RowNumber) Select ModuleHierarchyID, W.WebPartCode, SVGFilename, XSLTFilename,Row_Number() Over(ORDER BY ModuleHierarchyId DESC)As RowNumber from SVG_SVGMapping S, WEP_WebPart W where S.ModuleHierarchyId= @ModuleHierarchyID and S.WebPartId= W.WebPartId

The data from the query above looks as follows






The following section dynamically builds a sql query that extracts the tabular data and presents it in a column format. The @Count variabledetermines the depth of the table and uses this to iterate through each row using the rownumber to build each column. The dynamic SQL below contains a global select statement, with sub select statements for each column.

Set@Count = (Selectcount(1) from #tmp) Set @i = 0 Set @SQL = N'Select ' + Ltrim(str(@ModuleHierarchyID)) + ' As ModuleHierarchyId' While@i < @Count BeginSet @SQL = @SQL+ N', ' Set @SQL = @SQL + N'(Select WebPartCode from #tmp where Rownumber = ' +ltrim(Str(@i + 1)) + ') As WebPartCode' + ltrim(str(@i + 1))+ ',' Set @SQL = @SQL +N'(Select SVGFilename from #tmp where Rownumber = '+ ltrim(Str(@i+ 1)) + ') As SVGFilename' + ltrim(str(@i + 1)) + ',' Set @SQL = @SQL +N'(Select XSLTFilename from #tmp where Rownumber = '+ ltrim(Str(@i + 1)) + ') As XSLTFilename' + ltrim(str(@i+ 1)) Set @i = @i +1 End EXECUTE sp_executesql @SQL

The dynamic SQL should look as follows:

Select 4 As ModuleHierarchyId,Select WebPartCode from #tmp where Rownumber = 1) As WebPartCode1,
Select SVGFilename from #tmp where Rownumber = 1) As SVGFilename1,
Select XSLTFilename from #tmp where Rownumber = 1) As XSLTFilename1,
Select WebPartCode from #tmp where Rownumber = 2) As WebPartCode2,
Select SVGFilename from #tmp where Rownumber = 2) As SVGFilename2,
Select XSLTFilename from #tmp where Rownumber = 2) As XSLTFilename2)




Once the query is run the following results are returned:



The complete code looks as follows:

Declare@Count int, @i int, @SQL nVarchar(3000) CreateTable #tmp(ModuleHierarchyID Int, WebPartCode Varchar (10), SVGFilename Varchar(255), XSLTFilename Varchar(255), RowNumber Int) Insert into #tmp(ModuleHierarchyID,WebPartCode, SVGFilename, XSLTFilename, RowNumber) Select ModuleHierarchyID, W.WebPartCode , SVGFilename, XSLTFilename, Row_Number()Over (ORDERBY ModuleHierarchyId DESC) As RowNumber from SVG_SVGMapping S, WEP_WebPart W where S.ModuleHierarchyId = @ModuleHierarchyID and S.WebPartId= W.WebPartId Set @Count = (Select count(1) from #tmp) Set @i = 0 Set @SQL = N'Select ' +Ltrim(str(@ModuleHierarchyID)) + ' As ModuleHierarchyId' While @i < @Count Begin Set @SQL = @SQL + N', ' Set + N'(Select WebPartCode from #tmp where Rownumber = ' + ltrim(Str(@i + 1))+ ') As WebPartCode' + ltrim(str(@i + 1)) + ',' Set @SQL = @SQL + N'(Select SVGFilename from #tmp where Rownumber = ' +ltrim(Str(@i + 1)) +') As SVGFilename' +ltrim(str(@i + 1)) +',' Set @SQL = @SQL + N'(Select XSLTFilename from #tmp where Rownumber = ' + ltrim(Str(@i + 1))+ ') As XSLTFilename' + ltrim(str(@i + 1)) Set @i = @i + 1 End EXECUTE sp_executesql @SQL

Who decides when to "Virtualise"

There are no hard and fast rules that determine when an organisation should look at virtualisation as a component of their technology platform. The decision is a difficult one driven by many agendas. Fortunately the virtualisation platforms have evolved and matured to the level where it is now acceptable to run your production environment on a virtual infrastructure.

For some the decision is driven around cost, for others the inherent flexibility it provides, for some CIO's the decision is driven by an agenda centred on "Green IT" initiatives. There is no official documentation that explains the impact that running a virtual environment will have on the performance of your system. Each virtual environment is unique, be it SQL Server, IIS, SAP, Oracle etc…

The decision to go virtual should follow the same project implementation methodology used to introduce new software into the organisation. The first step is to understand the requirements. Once the requirements have been defined you should understand the forces driving the organisation to consider a virtual hardware platform. The infrastructure should be designed to meet the business needs.

Only once a feasible design has been completed should you implement the design in your development environments. Its key to understand how the organisations software behaves on virtual hardware before a significant investment is made rolling out a full blown implementation. Once the development goals are met, the infrastructure should be deployed in a test environment. Testing should not only include the software, but specific tests around non functional requirements, like the impact on manageability, flexibility and usability goals.

One of the greatest concerns around virtualisation is the affect on performance. If the system is designed correctly, performance should not be a concern since virtualisation brings with it the ability to instantly scale. In an interesting post, the author of a virtualisation column, commented on what affect the quality of code has on performance in a virtual infrastructure. His assessment is correct, poor quality code in a virtualised world won’t have the resources available when things go wrong. These types of issues are easily hidden when an application has a dedicated piece of hardware all to itself.

The point here is how you design your infrastructure, specifically disk, network and how the resources, CPU are allocated. If you need performance, you scale the farm. For a web application you should be more worried by the number of concurrent transactions the system is able to support.

The following areas will help you decide if virtualisation is the correct course for your design. You need to decide which forces apply and whether they feature strongly in your solution / client requirements.

Manageability

One of the key selling points for virtualisation is the manageability it brings. You can provision new servers quickly without needing long procurement cycles to buy and implement a new piece of hardware, effectively increasing capacity in minutes. The converse is also true, you can de-provision servers and you wont have hardware standing around and depreciating over time.

High Availability

Virtualisation software now days has HA and redundancy by design. Virtual machines are moved without losing network connectivity! Your resource capacity now become “Highly Available”… The amount of available processing resources increases. If you lose a physical server that capacity is no longer available and so are the services were running on it. You can now transfer the services seamlessly to available capacity resources in the pool.

Licensing

Licensing is cheaper since you license the physical hardware and you can run multiple virtual servers on the same infrastructure. Licensing generally works per physical CPU socket, driving down the cost of licenses required to host the same platform on physical hardware.

Flexibility

Running environments with different operating systems become easier, you can run Windows Server 2008, Linux and Windows Server 2003 on the same physical hardware. You can provision multiple environments, QA, Development on the same physical tin, yet still have isolation from a server perspective. You can move virtual servers to other locations immediately while work is done on a physical machine.

If a new project is established due to legislation changes and you need to make changes to accommodate your current production system, you can create a separate test environment quickly without having to try manage the changes on your current test infrastructure.

Scalability

You can scale a solution on demand. Call centers expecting a high volume of calls due to a recent ad campaign, can increase their processing capacity for a short period of time.

Better Hardware Utilisation

Virtual servers will load balance across the allocated physical servers. You could run both your SQL and web infrastructure on the same physical tin. If you processing requirements increase beyond you current capacity, you just add servers.

Better Resource utilisation

On 64 bit hardware you can scale the memory beyond the 32 bit limitations. This allows you to tile 32 Bit virtual machines on a single physical machine, effectively using the available memory on the 64bit physical machine. An example where you would use this is running Terminal Services (which is 32 bit – last time I checked, and can only use 4GB of Ram) and using the servers capability to run up to 32 GB / 64v GB of memory. This allows you to organically grow your server farm.

Green IT

The reduction in physical hardware means that there is no need to physically manufacture a server that is dedicated to one function. The manufacturing process in terms of electricity, power to manufacture a server, coal consumption etc are reduced. Heat emissions from multiple machines in a single rack are reduced, reducing the overall data center air conditioning requirements. The overall power consumption and power requirements for multiple physical machines is reduced.

Isolation and encapsulation

Virtualisation allows you to run multiple operating systems, product platforms on the same physical infrastructure. Reducing the cost to run each OS on its own dedicated hardware.

Re-use

When upgrading your physical hardware, it is possible to port your existing virtual images to a new hardware platform, reducing the need to re-install the application servers.