Unleashing the Power of SQL CLR: Discovering Entities and Best Practices:Question and Answer

Q.1. How to Perform Discovery on Deployed SQL CLR Entities?

Ans: Keeping track of all the SQL CLR stored procedures, functions, triggers, aggregates, and UDTs can be challenging. Fortunately, you can easily perform discovery on deployed SQL CLR entities using the SSDT and SSMS UIs. Both the Object Explorer window in SSMS and the SQL Server Object Explorer window in Visual Studio list all SQL CLR objects in a database. To locate them in the tree view of either window, first navigate to the \servername\Databases\databasename node (where servername and databasename are the names of your server and database, respectively). To view the subnodes of this node that include each SQL CLR entity.

Table: Finding CLR Objects in the Object Explorers.

To View	Look in
Parent node for SQL CLR stored procedures, database-level DDL triggers, functions, aggregates, and UDTs	Programmability
Assemblies	Programmability\Assemblies
Stored procedures	Programmability\Stored Procedures
Functions	Programmability\Functions\Scalar-Valued Functions and Programmability\Functions\Table-Valued Functions
Aggregates	Programmability\Functions\Aggregate Functions
DML triggers	Tables\tablename\Triggers
DDL triggers	Programmability\Database Triggers
UDTs	Programmability\Types\User-Defined Types

Image: Highlighted in the SSMS Object Explorer window is the Programmability node.

Image: Note the presence of the Microsoft.SqlServer.Types assembly, which is Microsoft's SQL CLR assembly for SQL Server data types, including hierachyid, geometry, and geography, in the Object Explorer window's Assemblies node, which is highlighted.

Image: The SQL CLR stored procedures are highlighted in the Object Explorer window.

Image: The SQL CLR table-valued, scalar-valued, and aggregate functions are highlighted in the Object Explorer window.

Image: The SQL CLR DML trigger is highlighted in the Object Explorer window.

Image: The SQL CLR DDL trigger is highlighted in the Object Explorer window.

Image: The SQL CLR UDTs are highlighted in the Object Explorer window.

You can refer to the above table and diagrams to find CLR objects in the Object Explorers. If you want to learn more, consider pursuing an online SQL certification.

Q.2. What Will Happen If you Deployed or Deleted any SQL CLR Object?

Ans: If you deployed or deleted any SQL CLR objects, you may need to use the Refresh shortcut menu option in SSMS on the nodes listed in the table to see your SQL CLR objects. The tree view may become out of date and will need to be refreshed. The tree view icons for SQL CLR stored procedures and SQL CLR DML triggers differ slightly from those for T-SQL, with a small yellow padlock in the lower-right corner.

Q.3. How to Generate Create, Alter and Alter Scripts in SSMS?

Ans: Once you've located a SQL CLR entity in the Object Explorer window, you can generate CREATE, DROP, and, in some cases, ALTER scripts for it by right-clicking its tree view node and selecting the "Script object type As" option from the shortcut menu (where object type is the SQL CLR object type selected). The script text can be pasted into a new query window, saved to a file, copied to the clipboard, or used to create a SQL Server Agent job. Double-clicking a CLR assembly or CLR entity's tree view node in the Visual Studio SSDT Object Explorer opens a T-SQL Editor window with the object's CREATE script code inside.

Q.4. What is The Purpose of The SSMS Execute Procedure Dialog Box?

Ans: In SSMS, you can generate EXECUTE scripts for stored procedures or execute stored procedures interactively and generate the corresponding script via the SSMS Execute Procedure dialog box by selecting "Execute Stored Procedure" from the shortcut menu. This dialog box explicitly requests all input parameters for the stored procedure. You can also view the dependencies of your SQL CLR entities in addition to generating scripts for them (either objects that depend on them or objects on which they depend) by right-clicking the object and selecting "View Dependencies" from the shortcut menu.

Q.5. How Can You Remove The SQL CLR Objects?

Ans: There are several options for removing SQL CLR objects, either in preparation for loading a new version of your assembly or to permanently delete the objects. Redeploying a Visual Studio SQL Server Database Project assembly causes Visual Studio to remove it and any SQL CLR objects contained within it that were previously deployed with it. This means that new versions can be deployed directly from Visual Studio without the need for any additional steps. For Class Library projects, T-SQL DROP commands must be issued for each of your SQL CLR objects, followed by the assembly itself. You must first remove any dependent objects before removing the SQL CLR entity. These DROP scripts can be written by hand or generated using the "Script object type As/DROP" options from the SSMS Object Explorer window's shortcut menu.You can also drop any SQL CLR object by using the Delete shortcut menu option. This option opens the Preview Database Updates window in the SSDT SQL Server Object Explorer, which displays a preview of all changes as well as the Generate Script and Update Database buttons. When using the SSDT delete option, all dependent objects are deleted first, in the correct order. The Delete shortcut menu option in SSMS deletes only one object. When you click it, the Delete Object dialog box appears.

Q.6. What are The Best Practices for SQL CLR Uses?

Ans: Some best practices for SQL CLR uses are summarized below. SQL Server 2012's CLR integration is a powerful technology that allows you to perform complex computational logic in stored procedures or triggers, and even create your aggregate functions. However, declarative T-SQL constructs handle set-based data selection and modification far better than procedural.NET constructs and the ADO.NET object model. Therefore, SQL CLR functionality should be reserved for specific situations that necessitate the power of.NET as a calculation engine.

Functions and aggregates make excellent use of SQL CLR integration. UDTs, which are used to track complex values rather than objects, can also benefit from SQL CLR integration. It is recommended to write stored procedures and triggers in T-SQL and only use SQL CLR code if it can be demonstrated that they cannot be written otherwise. Keep in mind that SQL CLR functions, aggregates, and UDTs can be used from within T-SQL stored procedures and triggers. Consider pursuing an online SQL certification if you want to learn more.

Conclusion

This tutorial has introduced you to the mechanics of creating and using the five basic SQL CLR entities from T-SQL. You've learned how to use SQL Server 2012/Visual Studio 2010 integration and how to write SQL CLR code in traditional Class Library assemblies and deploy it using T-SQL. Additionally, you've learned about the SQL CLR.NET code attributes and how to use them in SQL Server Database Projects and standard Class Library projects. It's important to use SQL CLR integration judiciously and only in situations where it provides clear benefits over T-SQL.