VS 2005 Integration Advantage Analysis of SQL Server

Microsoft's consolidation of SQL Server 2005 and Visual Studio 2005 is not a new initiative, Oracle pioneered the Oracle Developer Tools for Visual Studio 2003 at the end of 2004 Visual Studio. NET is intended to provide. NET platform, integrated with Visual Studio's database design and management tools, and even available. NET development stored Procedure (stored procedure). It is believed that Oracle will also launch an updated development version of the. NET Framework 2.0 in the near future.

Stored procedure efficient, but tightly tied to manufacturers

Standing in the position of the program developer, the database is separated from the commercial logic, the database simply stores the data, and the program development is handled by the application. But from the DBA's point of view, the system belongs to the system and the data is handled by the database is better. Although the two views are divided, however, Stored procedure close to the database engine, performance is better, and the data does have the need to deal with the process, remote processing is not only time-consuming, and network disconnection caused the risk of execution failure, therefore, in practice Stored Procedure there is a need for existence.

Stored procedure has the following benefits:

1. Modular program writing: Just create a stored Procedure, stored in the database, you can get different programs to call.

2. Efficient execution: Stored procedure executes faster than a batch of T-SQL program code if a large or repetitive process is required. Because the Stored Procedure is analyzed and optimized when it is established, it is executed once in memory. The program code is repeatedly uploaded from the client to the server every time it executes.

3. Reduce network traffic: Even a good hundreds of of lines of T-SQL program code, as long as through a single instruction can execute stored procedure program code, without having to transfer hundreds of lines of program code on the network.

Although Microsoft's T-SQL or Oracle's PL/SQL, are compatible with Ansi-sql, but after all is not the standard, both are vendor-specific database program language, so as long as the choice of writing stored Procedure, is the beginning of a tight bundle. However, unless there is a major reason for the use of the enterprise selected applications to match the database, there is little replacement, so the performance and bundling measurement, depending on the application.

Stored procedure with T-SQL or. NET development pros and cons

In the case of SQL Server, Visual Studio 2005 is integrated with SQL Server 2005, and the obvious benefit is in developing database programs, such as stored procedure or trigger can be satisfied. In the past, Stored procedure had to be deployed to the database "perform a look" to know if there was a problem, and Stored procedure like a general procedure, files could be modified or accidentally overwritten by others, so there was a need for management. The control of Visual Studio has a positive effect on SQL Server management.

If an enterprise uses T-SQL authoring stored Procedure,sql Server 2005 has been presented with a compact version of Visual Studio,dba no longer requires the use of an Yangchun text editor to compose stored Procedure, you can enjoy development, The benefits of test and version management.

If you are paired with Visual Studio 2005, you can use C # and Visual Basic to compose stored Procedure. In fact, the use of universal programming language to write stored Procedure, Microsoft is not original, Java SQLJ is also a peer-to-peer design.

The core of SQL Server 2005 has been rewritten by the. NET Framework 2.0, so the benefits of stored procedure developed in Visual Basic or C # include:

1. The stored procedure file, compiled into a DLL (. NET Managed code Assembly), performs better than T-SQL in the native. NET Framework 2.0 platform, especially for a large number of loops and string processing applications.

2. Since T-SQL is a programming language for a particular purpose, can not express complex logic, so even simple string processing or numerical calculation, you may have to write dozens of lines of program expression, the general purpose of C # or Visual Basic can greatly shorten the length of the program, but also increase the maintainability of the program.

3. Compared to T-SQL is the exclusive language for the server. NET is an ISO standard.

The downside is word, but it's critical: the enterprise may have to have the psychological readiness to save various versions of Visual Studio for a long time.

In the long run, the database and development tool life cycle is very different, the database first weight stability and security, the frequency of upgrades is not as popular as development tools, the future may be. NET framework and Visual Studio have updated several versions, enterprise SQL Server is still in version 2005 , and Visual Studio does not have the ability to switch JDK versions like JBuilder. As a result, the DBA's computer must have Visual Studio 2005 installed, regardless of the number of successive changes, and even with the multiplication of the database version, different versions of Visual Studio must be installed and the disc cannot be discarded. Then the situation may be like now the computer must be installed VB 6 or even VB 3 as strange. As for the trade-off between T-SQL or. NET, it may be decided by the application level of stored procedure.

Upgrade SQL Server 2005 to enjoy the benefits of consolidation

Database tools are integrated into the development environment, in addition to convenience, more importantly, the database program development, finally has the management tool. In the past stored procedure"throw up try to see" the practice, now has the test and version control mechanism, from this view, the two really have mutual "hold sedan chair", each other "tie the pile" effect.

However, there are many layers of Stored procedure, whether it is a T-SQL or a powerful, Visual Basic and C # compose that can handle complex logic.