Next gen data-driven solutions using MS SQL Server

BANGALORE, INDIA: Database servers have become critical components of the IT infrastructure since the days the client server architecture replaced monolithic systems as the platform for business computing. In the initial days of the client server model, the relational database engine was the database server.

However, look at the next generation server products like Microsoft SQL Server 2008 or its yet-to-be-released successor SQL Server 2011. You will find that not only the relational database engine has grown up; the product has grown a lot beyond the relational database engine. And for the project team, there is a lot more to SQL than SELECT-INSERT-UPDATE-DELETE statements.

The Relational Database Engine

1. You can write recursive queries using a feature known as common table expression (CTE). For example, you can not only find a manager's direct reports, but also direct reports of the direct reports, and the next level, till you find all employees directly or indirectly reporting to that manager.

2. You can present data in various tabular formats, move data from rows to columns and the other way round using the PIVOT and UNPIVOT operators.

3 You can handle exceptions almost as well as you do in a modern day programming language.

4. You can perform complex data aggregation by using ROLLUP, CUBE and GROUPING SETS

5. You can write stored procedures, triggers and user defined functions using any .NET language using Common Language Runtime (CLR) integration.

6. If MAX, MIN and SUM are some system-defined aggregates, you can create user defined aggregates using CLR integration.

7. You can encrypt database data using symmetric keys, asymmetric keys and certificates.

8. You can encrypt an entire database using Transparent Data Encryption (TDE).

9. You can track virtually any server or database action taken by your users using SQL Server Audit.

10. The way you look at performance tuning is different as the server keeps getting smarter. A lot of the old thumb rules like whether to use IN or EXISTS become redundant.

11. You can do your activities asynchronously using the Service Broker.

Click here to read more!