MS SQL Server 2008 T-SQL Development Improvements for SQL Developer
Microsoft SQL Server 2008 is a new version released in the first half of 2008 introducing new properties and capabilities to SQL Server product family. All these new and enhanced capabilities can be defined as the classic words like secure, reliable, scalable and manageable. SQL Server 2008 is secure. It is reliable. SQL2008 is scalable and is more manageable when compared to previous releases. Now we will have a look at the features that are making MS SQL Server 2008 more secure, more reliable, more scalable, etc. in details.
Before going in deeper to check the new features, I would like to talk in a few sentences about the Microsoft intend to move the SQL Server database product to a position not only to store data but to get more out of that stored data. I mean the Business Intelligence side of data.
Microsoft is pushing the MS SQL Server especially after the SQL2005 and it is continuing with SQL2008, to a more comprehensive data platform. For database developers and database administrators who are only working on OLTP databases, I strongly encourage the database professionals to try the OLAP or the datawarehouse platforms or the datawarehouse applications of the Microsoft SQL Server 2008 product also the other applications of Microsoft, MS Office 2007 for example.
Because Microsoft is working on OLAP, Data Warehouse, Data Mining topics and components of SQL Server, expending a lot for making SQL Server a great product in the segment of data processing for business intelligence, you can add to your outcome from summarizing your stored data by using reporting tools, estimate your activities according to the changes, find the main factors of your activities, what are affecting your sales or services for example.
In short, if you are a working as a DBA or a developer or if you are even a business man not a IT professional, you will get help from MS SQL Server 2008 which will make your life easier and solve your particular problems and guide you to a higher position in your job, in your business.
Now we can display a short list of enhancements in MS SQL Server 2008:
T-SQL Improvements
GROUPING SETS
Grouping Sets extension to the GROUP BY clause is new in T-SQL with MS SQLServer 2008. Grouping Sets enables data workers like sql developers and sql dba's to get data in multiple groupings and aggregations on that multiple groupings in one t-sql statement. In a way, Grouping Sets is very alike to ROLLUP and CUBE operators, but by using Grouping Sets you have more control on the aggregated data since you can eliminate unnecessary groupings because you can define how your data should be grouped with Grouping Sets operator in the Group By clause.
Also you can use Concatenated Grouping Sets which can enable sql developers to work on large numbers of combinations of grouping columns by defining them in sets and using cross-product of sets of columns.
You can find Grouping Sets t-sql sample codes at Group By Grouping Sets - MS SQL Server 2008 T-SQL Improvements.
MERGE
In the beta period of MS SQL Server, it is one of the most asked questions whether the UPSERT command is being introduced with the new version of SQL Server, SQL Server 2008. In some early blog posts and white papers, Upsert was introduced as a new t-sql enhancement in SQL2008. But later we learnt that that it the MERGE statement which was confused than as Upsert. Any way, MERGE command enables developers to take more than one action according to the data comparison between the data source and the target data.
For example, you are periodically moving some your data to an other table or to an other sql database for archiving purposes. In this case you may only want to move the changes in your data. Instead of moving all data from one table to an other, you might want to check whether is there a change on the archived data, if yes then update archived data, if not no need to move unchanged rows of data.
This method could be implemented in various methods with SQL Server 2005 capabilities, but SQL2008 granted us a new tool for this purpose, that is MERGE t-sql command.
MERGE enables us to decide to INSERT the new data, to UPDATE the changed data or to DELETE the removed data by making a comparison between SOURCE and TARGET only in one sql statement and only in a singe IO reading. And actually this is the most important point, you minimize IO readings for such a task by using the MERGE statement. This means reducing your input/output figures, making less disk reading, minimizing your network traffic, etc.
In fact MERGE is going to be find itself in ETL (Extract, Transform, Load) applications or data warehouse applications, I believe for OLTP databases also in many sql codes should be revised or coded again using this new command.
You can find sample T-SQL Merge codes at MERGE t-sql command enhancement in SQL Server 2008
TABLE-VALUED PARAMETERS (TVPs)
You know we have table value user defined functions that are returning a result set of rows of data as running a process on some scalar parameters. But it was not possible for sql programming aspects to pass this data as a parameter to a stored procedure for example. You have to implement an array like structure in your sql codes and make parsing on the scalar data, maintain all this sql code stuff, it was not easy actually.
SQL 2008 has now graneted sql server developers a way to place or package a set of data, you can call rows of a result set into a table variable. And now developers can now pass this table structured sql data as a parameter to a stored procedure their sql programming codes.
This ability to encapsulate tabular data into a variable and use this tabular variable as a parameter for a stored procedure or for a user-defined function (UDF) was one of the most requested improvements among the sql programmers and sql server database administrator professionals.
Table-valued parameters can also serve .NET programmers to pass their data from their data layers of codes to the SQL Server as encapsulated into a table structured variables. This new improvement table-value parameters is actually a great enhancement not only for sql developers also for .NET Framework developers.
You can find sql sample codes for table valued parameters at an article sql tutorial at Table Valued Parameters New In SQL Server 2008 T-SQL Improvements
LANGUAGE INTEGRATED QUERY (LINQ)
Language Integrated Query aka LINQ is a way of querying data, not only sql server data but any data which can be defined as rows of data for example processes running on a machine, etc. LINQ enables querying such data using any programming language like VB.NET, C# (C-Sharp) or any other .NET programming languages without a need to know T-SQL programming or t-sql statements. LINQ gives a great tool and power to the programmers and removes the necessity of t-sql knowledge who are working with SQL Server data in their programming languages.
By using LINQ in development, programmers actually separate the data from its source. So they treat and query the data independent from its source. So as a programmer by using your favorite programming language, VB.NET, C#, etc, you can query SQL Server data, Active Directory data, on the drives of your server or on the files and file structure of your server by replacing columns names in sql data with property names.