Understanding SQL Server's Most Advanced Features: Question and Answer

Q.1. Why Are The Most Advanced SQL Server Features Disabled by Default?

Ans: The most advanced features of SQL Server, such as several new products within the Microsoft Windows Server system family, are disabled by default. The reasoning behind this decision is sound: enabling additional features provides a larger "surface area" for potential attacks on the product's security and integrity. If these features are not actively used, the added exposure is considered unnecessary. For example, SQL Server 2012's SQL CLR features are complex and useful, but not essential. High-performance databases and server-side programming logic can be built without SQL CLR integration, which is why it is disabled by default.

Q.2. How to Enable SQL CLR Features?

Ans: Enabling the SQL CLR feature is a simple process. Microsoft offers a system-stored procedure that allows users to enable or disable SQL CLR integration. To configure the feature, connect to the desired server in either SSDT or SSMS. Then, open a query window and run the script by typing the following statements:

sp_configure 'clr enabled', 1
GO
RECONFIGURE
GO

It's as straightforward as that! To disable SQL CLR integration, simply replace the second constraint value in the sp_configure call with 0 instead of 1. This command works from any SQL Server-capable tool, not just SSDT and SSMS. You can issue the command text from your code by calling the ExecuteNonQuery method of the ADO.NET SqlCommand object, as long as your code can connect to the server, and your server can authorize you as a user with the sysadmin server role. If you wish to explore SQL CLR features further, consider pursuing an online SQL certification.

Q.3. How Do SSDT and SQL Server 2012 Integrate?

Ans: SSDT and SQL Server 2012 have tight integration in various ways. However, it's essential to note that using SSDT is optional, and T-SQL is a sufficient substitute. SQL Server 2005 introduced new data definition language (DDL) commands for managing CLR assemblies, types, and aggregates, improving T-SQL. The current commands for stored procedures, triggers, and functions were also enhanced to recognize code within deployed assemblies. Visual Studio can run those commands for you, and writing individual SQL CLR classes and functions can make the process easier.

Q.4. What Is The Significance of the SSDT SQL Server Database Project in SQL CLR?

Ans: The SSDT SQL Server Database Project provides templates for the five fundamental SQL CLR objects. These templates insert specific code attributes and function stubs, enabling the quick creation of SQL CLR code. SSDT utilizes these attributes to implement your assembly and its stored procedures, triggers, and other components in your database. Some of these attributes are used by SQL Server to recognize and correctly use your functions, user-defined types (UDTs), and aggregates.

Q.5. How to Add a CLR Entity to The SSDT SQL Server Database Project in Visual Studio?

Ans: Follow the procedure below to add a CLR entity to the SSDT SQL Server database project:

• Start Visual Studio 2010 and create a new project by selecting File | New | Project, clicking New Project on the toolbar, pressing Ctrl+Shift+N, or clicking the New Project link on the Start Page of Visual Studio.
• Click the expand arrow next to the Other Languages node inside the Installed Templates tree view on the left to open the New Project dialogue box. Then click the SQL Server child node of that node, and finally hit the SQL Server Database Project in the middle pane. If you wish, you can enter your project name and then click OK.

Image: The SQL Server Database Project type is chosen in the Visual Studio 2010 New Project dialogue box.

• You can easily insert pre-configured classes for the five basic SQL CLR entities into your project. However, you must decide whether to utilize C# or Visual Basic.NET as your SQL CLR Assembly programming language.
• In the Solution Explorer, double-click the Properties node, and then in the resulting property sheet designer, click the SQLCLR tab. Once inside the SQLCLR tab, select C# from the Language combo box, as shown in the following Figure 3-2.

Image: The SQL CLR entity sheet with C# as the programming language selected.

• You can now add a CLR entity to your project from the Add New Item dialogue box, which you can access from the main menu by selecting Project | Add New Item or by right-clicking the project node in the Solution Explorer and selecting Add New Item. The SQL CLR C# or SQL CLR VB parameter type should appear in the "Installed Templates" list on the left side of the Add New Item dialogue box, as shown in Figure 3-3.

Image: The Add New Item dialogue box in Visual Studio SQL Server Database Project, with SQL CLR C# templates displayed.

After selecting an entity type, a class template of that particular type will be inserted into your project and opened in the code editor pane. The project will also include references to the System, System.Data, and System.Xml assemblies, as the stubbed code in the SQL CLR class templates requires these references.

Q.6. How to Deploy The Assembly and SQL CLR Entities within It?

Ans: Once your project is open in the code editor window, the SQL Server Database Project template adds a Publish option to the Visual Studio main menu's Build option, which can be used to deploy the assembly and the SQL CLR entities contained within it. To learn more about deploying assembly and SQL CLR entities, consider learning SQL online.

Q.7. What Kind of Code Attributes are Offered to SQL CLR Developers?

Ans: SQL CLR developers have access to various .NET code attributes within the Microsoft.SqlServer.Server namespace. Many of these attributes are included in your code when using the SQL Server Database Project type's templates or an Imports statement in VB to alias the Microsoft.SqlServer.Server namespace. If you choose to write code without using these templates, you must add the necessary attributes and, if desired, the user (or Imports) statement yourself. While all of these attributes are available in the same namespace, some are used only by SSDT, while others are used by both SSDT and SQL Server.

Q.8. Is SQL CLR Programming Complex?

Ans: Although SQL CLR programming can become quite complex and involved, it provides a basic model that any .NET developer can use with high productivity in a relatively short period. This is because the crux of SQL CLR functionality is no more than SQL Server 2012's ability to load .NET assemblies into your database tables and then allow you to use the functions and types contained within the assembly when defining columns, views, stored procedures, triggers, and functions.

Q.9. How to Create and Execute a SQL CLR Stored Procedure?

Ans:  To create and execute a SQL CLR stored procedure, follow these steps:

• Connect to the AdventureWorks2012 sample database through a query window in SSDT or SSMS.
• Confirm that the SQLCLRDemo.dll file is present in the VSSQLCLRDemoManualSQLCLRDemobinDebug subfolder in the sample code folder.
• Use the following T-SQL command to load the assembly into the AdventureWorks2012 database, assuming the sample code parent folder is C: Demos:

CREATE ASSEMBLY SQLCLRDemo
FROM 'C:\Demos\VS\SQLCLRDemo Manual\SQLCLRDemo\bin\Debug\SQLCLRDemo.dll'

• Functions within an assembly that perform local computational tasks and specific types of data access can be exposed as SQL Server stored procedures, triggers, or functions. A basic T-SQL CREATE PROCEDURE, CREATE TRIGGER, or CREATE FUNCTION statement is used, just like with traditional T-SQL stored procedures, triggers, and functions. The spContactsQuick method is intended to connect to the database table in which its assembly has been loaded and perform a SELECT * against the Person.Person table, then send data back to the client's application using specific server-side objects. To make this CLR code available through SQL Server as a stored procedure (spContactsQuick), run the following command from an SSMS or SSDT query pane:

CREATE PROCEDURE spContactsQuick
AS EXTERNAL NAME SQLCLRDemo. Sprocs.spContactsQuick

• To test the SQL CLR stored procedure, open an SSMS or SSDT query window and execute it like any other stored procedure:

EXEC spContactsQuick

Or simply:

spContactsQuick

After the execution is finished, the contents of the Person.Person table should appear in the Results tab of the query pane. If you want to learn more about SQL CLR stored procedures, consider enrolling in an SQL server training course.

Q.10. How Can You View The Source Code of an Assembly?

Ans: You can view the source code of the SQLCLRDemo assembly by opening the solution file VSSQLCLRDemoManualSQLCLRDemoManual.sln in the sample code folder for this chapter. The source code can be found in the project's Sprocs.cs file.

using System.Data.SqlClient; using Microsoft.SqlServer.Server;
public partial class Sprocs
{
public static void spContacts Quick()
{
}
SqlContext. Pipe. ExecuteAndSend(new SqlCommand("SELECT * FROM Person. Person"));
}

Conclusion

Regardless of the implementation method you choose, once you add the assembly to your database, it becomes an integral part of that database, including its underlying .mdf file. If you have concerns about backing up and restoring your database or deploying any assemblies, consider learning SQL online.