Next Gen Data-Driven Solutions using MS SQL Server

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.

Beyond Relational

1. XML is a type in SQL Server, just like a date is. You can even create an XML index on an XML column. You can output the results of your queries in XML format. You can convert XML data into relational format using the OPENXML function. You can even query the XML data stored inside relational tables.

2. You can create hierarchies and implement a tree-structured organization of your data by using the hierarchyid data type. You can even index these columns for faster retrieval. These indexes are different from your traditional indexes — they can be depth-first indexes or breadth-first indexes or both.

3. There have been debates about how you store your BLOBs. Storing them inside the database as binary types has a performance penalty. Storing them in the file system mean they get left out in database operations like backup, restore etc. You can get the best of both worlds using FILESTREAM.

4. You can store and manipulate shapes, sizes and locations using the geometry and geography data types.

5. You can create XML Web Services using the concept of HTTP Endpoints.

Business Intelligence

SQL Server 2008 and SQL Server 2011 present the most comprehensive solution on business intelligence yet from the Microsoft stable.

1. Analysis Services — OLAP and data mining features.

2. Integration Services — Extract, Transform and Load (ETL) features.

3. Reporting Services — you can build reporting solutions for either your OLTP data, or your OLAP data, or both.

Database Administration

1. If you are the DBA, you would need to learn to administer Integration Services and even use the service, in case you have not done that already.

2. You would need to learn to administer and tune Analysis Services.

3. You may even be asked to administer the Service Broker.

4. Security has moved beyond logins and users. You need to secure the HTTP Endpoints, and use encryption.

5. You can watch for all activities using SQL Server Audit.

6. You can do change management by using policy based management.

7. There are sophisticated high availability solutions like database mirroring, clustering, replication, log shipping and snapshots available.

Conclusion

What I have listed is by no means complete. I have seen developers, administrators and sometimes even architects spend precious time and money building solutions that are already there. As database professionals we all need to know what all is out there, and next we need to master when to use what. That is when we will add value to our customers — whether internal or external.