在SQLServer 2005中编写存储过程
在SQL Server 2000中，实际上只有一种创建存储过程的方法：即T-SQL语句。之前的每个SQL Server版本都采用这个程序。
然而，在SQL Server 2005中，我们可以用.NET家族的语言——主要是VB.NET和C#来编写存储过程（以及方法、触发器和其它组件）。让我们来熟悉一下关于编写存储过程新方法的5个常见问题。它们是非常值得我们探讨的。1、为什么我们必须使用CLR模式来编写存储过程呢？
主要原因是速度。SQL CLR在很多方式下都运行较快：比如字符串处理，它比T-SQL运行快很多，并且对于错误的处理能力也更加强大。同时，由于CLR所提供的来执行这些事务的框架都更为完善，因此任何需要与数据库之外资源进行事务交互的存储过程——比如，文件系统或者Web服务——CLR SP都是表现最好的。
2、CLR最适合编写哪些类型的存储过程？
一般来说，在数据上执行繁重计算而不是仅仅是查询数据的SP最适合用CLR。如果一个CLR SP只是封装一个复杂的SELECT语句，那么我们将无法看到显著的性能增益，因为每次运行SP时，都必须验证CLR中的SQL语句。事实上，它比仅将SELECT语句作为T-SQL SP处理表现还要差。
一个经典的好方法是：如果需要执行的SQL的行数很多，那么可以将SQL封装在一个常规的SP上。如果想要在一个大的数据集上运行CLR风格的处理，那么我们可以在CLR SP内部调用一个常规的SP来获取这个大的数据集。这样，常规的SP会被预编译，性能也会更好，同时数据转换性能也会有所提高。
注意：这种情况是假定我们需要在数据层上进行复杂的数据处理，而不是在显示层上。事实上我们在编写代码之前就需要考虑这些问题。
3、是否应该把现有的存储过程转换为CLR模式？
简单而言，“要有好处才去做”。在这种情况下，可以为指定的存储过程创建一个同等的CLR实现的版本，然后使用实际数据对两种SP进行测试。除非我们可以确定新的存储过程：（a）按照预计的方式运行，（b）对性能有实际的提升，否则应该继续使用老的存储过程。其实CLR跟其它的存储过程一样，没什么奇特的。
4、在没有开发IDE的情况下，可以创建CLR（Common Language Runtime）存储过程吗？
当然，我们可以通过C#编译手动实现这类开发。然而，使用Visual Studio或者类似的IDE可以更简单，特别是当我们在整个企业范围内转换或实现大量SP时。
5、转换有多难？
很明显，我们必须具备其中一种支持语言的知识，如VB.NET或者C#。事实上，SQL命令是“封装”在CLR代码中的，因此，只要我们知道如何使用它，那么在CLR重新实现现有的T-SQL是不难的。比较有难度的是如何使用这种语言来优化我们正在做的工作，这个问题就不是几个要点就可以归纳的。
Hot AI Tools
Undresser.AI Undress
AI-powered app for creating realistic nude photos
AI Clothes Remover
Online AI tool for removing clothes from photos.
Undress AI Tool
Undress images for free
Clothoff.io
AI clothes remover
Video Face Swap
Swap faces in any video effortlessly with our completely free AI face swap tool!
Hot Article
Hot Tools
Notepad++7.3.1
Easy-to-use and free code editor
SublimeText3 Chinese version
Chinese version, very easy to use
Zend Studio 13.0.1
Powerful PHP integrated development environment
Dreamweaver CS6
Visual web development tools
SublimeText3 Mac version
God-level code editing software (SublimeText3)
Hot Topics
How to import mdf file into sqlserver
Apr 08, 2024 am 11:41 AM
The import steps are as follows: Copy the MDF file to SQL Server's data directory (usually C:\Program Files\Microsoft SQL Server\MSSQL\DATA). In SQL Server Management Studio (SSMS), open the database and select Attach. Click the Add button and select the MDF file. Confirm the database name and click the OK button.
How to solve the problem that the object named already exists in the sqlserver database
Apr 05, 2024 pm 09:42 PM
For objects with the same name that already exist in the SQL Server database, the following steps need to be taken: Confirm the object type (table, view, stored procedure). IF NOT EXISTS can be used to skip creation if the object is empty. If the object has data, use a different name or modify the structure. Use DROP to delete existing objects (use caution, backup recommended). Check for schema changes to make sure there are no references to deleted or renamed objects.
How to check sqlserver port number
Apr 05, 2024 pm 09:57 PM
To view the SQL Server port number: Open SSMS and connect to the server. Find the server name in Object Explorer, right-click it and select Properties. In the Connection tab, view the TCP Port field.
What to do if the sqlserver service cannot be started
Apr 05, 2024 pm 10:00 PM
When the SQL Server service fails to start, here are some steps to resolve: Check the error log to determine the root cause. Make sure the service account has permission to start the service. Check whether dependency services are running. Disable antivirus software. Repair SQL Server installation. If the repair does not work, reinstall SQL Server.
How to recover accidentally deleted database in sqlserver
Apr 05, 2024 pm 10:39 PM
If you accidentally delete a SQL Server database, you can take the following steps to recover: stop database activity; back up log files; check database logs; recovery options: restore from backup; restore from transaction log; use DBCC CHECKDB; use third-party tools. Please back up your database regularly and enable transaction logging to prevent data loss.
Where is the sqlserver database?
Apr 05, 2024 pm 08:21 PM
SQL Server database files are usually stored in the following default location: Windows: C:\Program Files\Microsoft SQL Server\MSSQL\DATALinux: /var/opt/mssql/data The database file location can be customized by modifying the database file path setting.
How to delete sqlserver if the installation fails?
Apr 05, 2024 pm 11:27 PM
If the SQL Server installation fails, you can clean it up by following these steps: Uninstall SQL Server Delete registry keys Delete files and folders Restart the computer
How to change sqlserver English installation to Chinese
Apr 05, 2024 pm 10:21 PM
SQL Server English installation can be changed to Chinese by following the following steps: download the corresponding language pack; stop the SQL Server service; install the language pack; change the instance language; change the user interface language; restart the application.
