SQL Server .NET integration

SQL Server .NET integration 

constructed by the function to be queried in SELECT statements by embedding them in the FROM clause. CLR code objects can be written in .NET languages which are safe and fast. CLR code can work with data types like XML, varchar binary.

How to configure CLR for SQL Server?

CLR by default is not configured in SQL server. It needs to be explicit ally configured using sp_configure syntax. This is shown as below:

Sp_configure 'clr enabled', 1 What is SAND BOX in SQL server?

Sandbox is a safe place to run semi-trusted programs or scripts that originate at a third party.

SQL Server .NET integration 

Overview of SQL Server Integration Services (SSIS)

SQL Server Integration Services is used for building applications with data integration and workflow. SSIS is most commonly used in ETL jobs of data warehousing. It can be used for automation of SQL server’s database maintenance. SSIS has a unique feature to move data from a single data source to a destination with no transformations.

How can we drop an assembly from SQL SERVER?

An assembly can be dropped using DROP ASSEMBLY command.

DROP ASSEMBLY assembly_name

IF no dependent assemblies need to be dropped, “WITH NO DEPENDENTS” needs to be specified.

Why do we need to drop assembly for updating changes?

Assemblies are stored in the database. Because of this, when the assembly is modified and recompiled, it must be first DROPPED from the database and then reloaded using CREATE ASSEMBLY command to reflect changes on the server.

How to see assemblies loaded in SQL SERVER?

Sys.assemblies can be used to see the assemblies loaded in SQL SERVER.

Example:

Select * from sys.assemblies

If the files that were used to create the assemblies need to be viewed, sys.assembly_files can be used.

How is .NET Appdomain allocated in SQL SERVER 2005?

A separate Appdomian is created for each database in order to run the database code. The appdomain may be allocated based on identity of the user who owns the assembly. The memory allocated to the instantiations of .Net type objects is done from a pool of close memory managed by CLR. The .Net framework also has a garbage collector for unusable objects and reclaiming the memory.