![]() At the top of each test script there is a comment-block that includes all of the “unit” tests. Finally, there are three scripts of tests: one for the single-execution tests, one for the set-based tests, and one for the TVF tests. If you want to recreate the tests, just run all of the numbered scripts in order. There are scripts to: create the CLR Assembly and the T-SQL wrapper functions for the CLR methods create the test-harness stored procedure create the example T-SQL Functions and Numbers table and create two sample data tables used in the set-based tests. Second, all of the test code is attached to this article in a zip file which you can find at the bottom of the article. But for data retrieval and/or manipulation, most of the time T-SQLstored procedures will not only be easier to create and maintain, but it should perform better. ![]() Both of these operations are not allowed in functions so stored procedures are necessary if you need to do those things. The only reasons I can think of to create a CLR stored procedure would be to either modify the state of the database or server, or to return a dynamic result set. I find few reasons for creating CLR stored procedures over Functions, given how easy it is to interface with both Scalar and Table-Valued Functions (TVFs). ![]() First, I did not compare stored procedures between T-SQL and CLR. This will give a more complete picture of exactly what performance gains CLR code (or SQL CLR, as it is commonly known as) can provide and when T-SQL stored procedures are clearly the better choice.īefore we begin I should mention two things. ![]() My goal with this article is to perform more thorough testing via a wide variety of scenarios. There has been some testing which shows that CLR can perform better than T-SQL, but there is still too much room for speculation and conjecture. Microsoft sells this feature on the idea that not only will it provide functionality not available in T-SQL but it will also provide a more efficient environment for programmatic logic. NET Common-Language Runtime-code in SQL Server and if so what the appropriate usages would be. Even though it has been six years since it became available, there is still much hesitation about using CLR-the. NET languages for stored procedures and user-defined functions has been around since SQL Server 2005 was introduced. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |