SQL Server Interview Questions on NET Integration

We have compiled most frequently asked Python Interview Questions which will help you with different expertise levels.

SQL Server Interview Questions on NET Integration

Question 1.
What are the steps to load a .NET code in SQL SERVER 2005?
Answer:
Following are the steps to load a managed code in SQL SERVER 2005:-

• Write the managed code and compile it to a DLL / Assembly.
• After the DLL is compiled using the “CREATE ASSEMBLY” command you can load the assembly into SQL SERVER. Below is the create command which is loading “mycode.dll” into SQL SERVER using the “CREATE ASSEMBLY” command.

CREATE ASSEMBLY mycode FROM 'c:/mycode.d11'

Question 2.
How can we drop an assembly from SQL SERVER?
Answer:

DROP ASSEMBLY mycode

Question 3.
Are changes made to assembly updated automatically in the database?
Answer:
No, it will not synchronize the code automatically. For that, you have to drop the assembly (using the DROP ASSEMBLY) and create (using the CREATE ASSEMBLY) it again.

Question 4.
Why do we need to drop assembly for updating changes?
Answer:
When we load the assembly into SQL SERVER, it persists in sys. assemblies. So any changes after that to the external DLL / ASSEMBLY will not reflect in SQL SERVER. So you have to DROP and then CREATE assembly again in SQL SERVER.

Question 5.
How to see assemblies loaded in SQL Server?
Answer:

Select * from sys.assemblies.

Question 6.
I want to see which files are linked with which assemblies?
Answer:
Assembly_files system tables have the track about which files are associated with what assemblies.

SELECT - * FROM sys.assembly_files

Note  You can create SQL SERVER projects using VS 2005 which provides ready-made templates to make 
         development life easy.

Question 7.
Does .NET CLR and SQL SERVER run in different processes?
Answer:
.NET CLR engine (hence all the .NET applications) and SQL SERVER run in the same processor address space. This “Same address space architecture” is implemented so that there no speed issues. If the architecture was implemented the other way (i.e. SQL SERVER and .NET CLR engine running in different memory process areas) there would have been a reasonable speed issue.

Question 8.
Does .NET controls SQL SERVER or is it vice-versa?
Answer:
SQL Server controls the way .NET application will run. Normally .NET framework controls the way application should run. But in order that we have high stability and good security SQL Server will control the way .NET framework works in SQL Server environment. So lot of things will be controlled through SQL Server example threads, memory allocations, security etc .

SQL Server can control .NET framework by “Host Control” mechanism provided by .NET Framework 2.0. Using the “Host Control” framework external application’s can control the way memory management is done, thread allocation’s are done and lot more. SQL Server uses this “Host Control” mechanism exposed by .NET 2.0 and controls the framework.

Question 9.
Is SQLCLR configured by default?
Answer:
SQLCLR is not configured by default. If developers want to use the CLR integration feature of SQL SERVER it has to be enabled by DBA.

Question 10.
How to configure CLR for SQL SERVER?
Answer:
It’s an advanced option you will need to run the following code through a query analyzer.

EXEC sp_configure 'show advanced options', ' 1 '; go reconfigure go
EXEC sp_configure 'clr enabled' , ' 1 ' go reconfigure; go

Note  You can see after running the SQL "clr enabled" property is changed from 0 to 1, which indicates
          that the CLR zvas successfully configured for SQL SERVER.

Question 11.
Is the .NET feature loaded by default in SQL Server?
Answer:
No, it will not be loaded, CLR is lazy loaded which means it’s only loaded when needed. It goes one step ahead of where the database administrator has to turn the feature on using “sp_configure”.

Note  Loading .NET runtime consumes some memory resources around 20 to 30 MB 
         (it may vary depending on lot of situations). So if you really need .NET Integration then only go for this option.

Question 12.
How does SQL Server control .NET run-time?
Answer:
.NET CLR exposes interfaces by which an external host can control the way .NET run time runs.
In previous versions of .NET, it was done via COM interface “ICorRuntimeHost”.
In the previous version, you can only do the following with the COM interface.

• Specify that whether it’s server or workstation DLL.
• Specify the version of the CLR (e.g. version 1.1 or 2.0)
• Specify garbage collection behavior.
• Specify whether or not jitted code may be shared across AppDomains.
  In .NET 2.0 it’s done by “ICLRRuntimeHost”. But in .NET 2.0 you can do much above what was provided by the previous COM interface.
• Exceptional conditions
• Code loading
• Classloading
• Security particulars
• Resource allocation

SQL Server uses the “ICLRRuntimeHost” to control .NET run-time as the flexibility provided by this interface is far beyond what is given by the previous .NET version, and that’s what exactly SQL Server needs, full control of the .NET run time.

Question 13.
What’s a “SAND BOX” in SQL Server 2005?
Answer:
Twist: – How many types of permission levels are there and explain in short there – characteristics?
Ok here’s a general definition of the sandbox:-
“A sandbox is a safe place for running semi-trusted programs or scripts, often originating from a third party.”
Now for SQL Server, it’s .NET the external third party which is running and SQL Server has to ensure that .NET runtime crashes do not affect his working. So in order that SQL Server runs properly, there are three sandboxes that user code can run:-

Safe Access sandbox

This will be the favorite setting of DBA’s if they are compelled to run CLR – Safe access. Safe means you have only access to in-proc data access functionalities. So you can create stored procedures, triggers, functions, data types, triggers, etc. But you can not access memory, disk, create files, etc. In short, you can not hang the SQL Server.

External access sandbox

In external access you can use some really cool features of .NET like accessing file systems outside the box, you can leverage your classes, etc. But here you are not allowed to play around with threading, memory allocation, etc.

Unsafe access sandbox

In Unsafe access, you have access to memory management, threading, etc. So here developers can write unreliable and unsafe code which destabilizes SQL Server. In the first two access levels of the sandbox, it’s difficult to write unreliable and unsafe code.

Question 14.
What is an application domain?
Answer:
Previously “PROCESS” where used as security boundaries. One process has its own virtual memory and does not overlap the other process’s virtual memory, due to this one process can not crash the other process. So any problem or error in one process does not affect the other process. In .NET they went one step ahead introducing application domains.

In application domains, multiple applications can run in the same process without influencing each other. If one of the application domains throws an error it does not affect the other application domains. To invoke a method in an object running in different application domains .NET remoting is used.

Question 15.
How are .NET Appdomain allocated in SQL SERVER 2005?
Answer:
In one line it’s “One Appdomain per Owner Identity per Database”.
That means if owner “A” owns “Assembly1” and “Assembly2” which belong to one database. They will be created in one App domain. But if they belong to different databases two Appdomains will be created.
Again if there are different owners for every the same assembly then every owner will have its own App domain.

Note    This can be pretty confusing during interviews so just make one note "One Appdomain per 
            Owner Identity per Database".

Question 16.
What is Syntax for creating a new assembly in SQL Server 2005?
Answer:

CREATE ASSEMBLY customer FROM 'c:\customers\customer.dll'

Question 17.
Do Assemblies loaded in the database need actual .NET DLL?
Answer:
No, once the assembly is loaded you do not need the source. SQL Server will load the DLL from the catalog.

Question 18.
You have an assembly that is dependent on other assemblies, will SQL Server load the dependent assemblies?
Answer:
Ok. Let me make the question clearer. If you gave “Assemblyl.dll” who is using “Assembly2.dll” and you try cataloging “Assemblyl.dll” in SQL Server will it catalog “Assembly2.dll” also? Yes it will catalog it. SQL Server will look in to the manifest for the dependencies associated with the DLL and load them accordingly.

Note  All Dependent assemblies have to be in the same directory, do not expect SQL Server to go to some 
         other directory or GAC to see the dependencies.

Question 19.
Does SQL Server handle unmanaged resources?
Answer:
SQL Server does not handle the unmanaged resource of a framework. It has to be guaranteed by the framework DLL that it will clean up the unmanaged resource. SQL Server will not allow you to load .NET framework DLL which does not have clean-up code for unmanaged resources.

Question 20.
What is Multi-tasking?
Answer:
It’s a feature of modern operating systems with which we can run multiple programs at the same time example Word, Excel, etc.

Question 21.
What is Multithreading?
Answer:
Multi-threading forms a subset of Multi-tasking instead of having to switch between programs this feature switches between different parts of the same program. The example you are writing in word and at the same time word is doing a spell check in the background.

Question 22.
What is a Thread?
Answer
A thread is a basic unit to which the operating system allocates processor time.

Question 23.
Can we have multiple threads in one App domain?
Answer:
One or more threads run in an AppDomain. An AppDomain is a runtime representation of a logical process within a physical process. Each AppDomain is started with a single thread but can create additional threads from any of its threads.

Note  All threading classes are defined in System. Threading namespace.

Question 24.
What is Non-preemptive threading?
Answer:
In Non-preemptive threading, every thread gives control to other threads to execute. So for example we have “Thread1” and “Thread2”, let’s say for that instance “Thread1” is running. After some time it will give the control to “Thread2” for execution.

Question 25.
What is pre-emptive threading?
Answer:
In pre-emptive threading operating system schedules which thread should run, rather than threads making their own decisions.

Question 26.
Can you explain the threading model in SQL Server?
Answer:
SQL Server uses the “Non-preemptive” threading model while .NET uses the “pre-emptive” threading model.

Question 27.
How does the .NET and SQL Server thread work?
Answer:

Note  From hence onwards I will refer to .NET assemblies running on SQL SERVER as SQLCLR 
          which's more of an industry acronym.

As said in the previous section threading model of .NET and SQL Server is completely different. So SQL Server has to handle threads in a different way for SQLCLR. So a little different threading architecture is implemented termed as “Tasking of Threads”. In tasking thread architecture there is switching between SQLCLR threads and SQL Server threads, so that .NET threads do not consume full resources and go out of control. SQL Server introduced blocking points which allow this transition to happen between SQLCLR and SQL Server threads.

Question 28.
How is an exception in SQLCLR code handled?
Answer:
If you remember in the previous section’s we had mentioned that there is One Appdomain / User / Database. So if there is an error in any of the App domain SQL Server will shut down the App domain, release all locks if the SQLCLR was holding, and roll back the transaction in case if there are any.
So the Appdomain shutdown policy ensures that all other Appdomain including the SQL Server process is not affected.

Question 29.
Are all .NET libraries allowed in SQL Server?
Answer:
No, it does not allow all .NET assemblies to execute an example System.Windows.Forms, System.Drawing, System.Web etc are not allowed to run.SQL Server maintains a list of .NET namespaces that can be executed, any namespaces other than that will be restricted by SQL Server policy. These policy checks are made on two instances:-

• When you are cataloging the assembly.
• When you are executing the assembly.

Readers must be wondering why a two-time check. There are many things in .NET that are building on runtime and can not be really made out from IL code. So SQL Server makes check at two-point while the assembly is cataloged and while it’s running which ensures 100 % that no runaway code is going to execute.

Note  Read Hostprotectionattribute in next questions.

Question 30.
What is “Hostprotectionattribute” in SQL Server 2005?
Answer:
As said previously .NET 2.0 provides the capability to host itself and that’s how SQL Server interacts with the framework. But there should be some mechanism by which the host who is hosting .NET assemblies should be alerted if there is any out of serious code running like threading, synchronization, etc. This is exactly the use of “Hostprotectionattribute”. It acts like a signal to the outer host saying what type of code it has. When .NET Framework 2.0 was in development Microsoft tagged this attribute on many assemblies, so that SQL Server can be alerted to load those namespaces or not. For example, if you look at System. Windows you will see this attribute.
So during runtime SQL Server uses a reflection mechanism to check if the assembly has valid protection or not.

Note  HostProtection is checked only when you are executing the assembly in SQL Server 2005.

Question 31.
How many types of permission levels are there for an assembly?
Answer:
There are three types of permission levels for an assembly:-

Safe permission level

Safe assemblies can only use pre-defined framework classes, can call any COM-based components or COM wrapper components, can not access network resources and, can not use PInvoke or platform invoke

External Access

It’s safe but yOu can access network resources like files from the network, file system, DNS system, event viewers, etc.

Unsafe

In unsafe code, you can run anything you want. You can use PInvoke; call some external resources like COM etc. Every DBA will like to avoid this and every developer should avoid writing unsafe code unless very much essential. When we create an assembly we can give the permission set at that time.

Note  We had talked about sandboxes in the previous question. Just small note sandboxes are expressed by using 
          the permission level concepts.

Question 32.
In order that an assembly gets loaded in SQL Server what type of checks are done?
Answer:
SQL Server uses the reflection API to determine if the assembly is safe to load in SQL Server. Following are the checks are done while the assembly is loaded in SQL Server:-

• It does the META data and IL verification, to see that syntaxes are appropriate for the IL.
• If the assembly is marked as safe and external then the following checks are done
• Check for static variables, it will only allow read-only static variables.
• Some attributes are not allowed for SQL Server and those attributes are also checked.
• Assembly has to be type-safe which means no unmanaged code or pointers are allowed.
• No finalizers are allowed.

Note  SQL Server checks the assembly using the reflection API, so the code should be IL compliant.

You can do this small exercise to check if SQL Server validates your code or not. Compile the simple below code which has a static variable defined in it. Now because the static variable is not read-only it should throw an error. *

using System; namespace StaticDll { public class Class { static int i;}}
After you have compiled the DLL, use the Create Assembly syntax to load the DLL in SQL Server. While cataloging the DLL you will get the following error:-
Msg 6211, Level 16, State 1, Line 1 CREATE ASSEMBLY failed because type ‘StaticDll.Classl’ in safe assembly ‘StaticDll’ has a static field ‘i’. Attributes of static fields in safe assemblies must be marked read-only in Visual C#, Readonly in Visual Basic, or initially in Visual C++ and intermediate language.

Question 33.
Can you name system tables for .NET assemblies?
Answer:
There are three main files that are important:-

• sys. assemblies:- They store information about the assembly.
• sys.assembly_files:- For every assembly you will can one or more files and this is where actual file raw data, file name, and path are stored.
• sys.assembly_references All references to assemblies are stored in this table.

We can do a small practical hand on to see how the assembly table looks like. Let’s try to create a simple class classl. Code is as shown below.

Using system; using system.collections.Generic; using system. Text; 
namespace class1 
{ 
    public class class1 
   { 
   } 
}

Then we create the assembly by the name “XI” using the create assembly syntax. In the above image is the query output of all three main tables in this sequence sys. assemblies, sys.assembly_files, and sys.assembly_references.

Note In the second select statement we have a content field in which the actual binary data stored. So even if toe does not have the actual assembly it will load from this content field.

Question 34.
Are two version of same assembly allowed in SQL Server?
Answer:
You can give different assembly name in the create statement pointing to the different file name version.

Question 35.
How are changes made in assembly replicated?
Answer:
ALTER ASSEMBLY CustomerAsm ADD FILE FROM ‘c:\mydir\CustomerAsm.pdb1

Note  You can drop and recreate it but will not be a good practice to do that way. 
          Also note I have set the file reference to a "PDB" file which will enable my debugging just in case if I want it.

Question 36.
Is it a good practice to drop an assembly for changes?
Answer:
Dropping an assembly will lead to the loose following information:-

• You will lose all permissions defined to the assembly.
• All stored procedures, triggers, and UDF (User-defined functions) or any SQL Server object defined from them.

Note  If you are doing bugfixes and modifications its good practice to Alter rather than Drop? Create assembly.

Question 37.
In one of the projects following steps were done, will it work?
Answer:
TwistAre public signature changes allowed in “Alter assembly” syntax?
Following are the steps:-

• Created the following class and method inside it.

public class CLS customer
{
     Public void add()
     {
     }
}

Compiled the project successfully.

• Using the created assembly cataloged it in SQL Server.
• Later made the following changes to the class

public class clscustomer
{
   Public void add(string code)
   {
   }
}

Note  The add method signature is now changed.

• After that using “Alter” we tried to implement the change.

Using alter syntax you can not change public method signatures, in that case, you will have to drop the assembly and re-create it again.

Question 38.
What does Alter assembly with unchecked data signify?
Answer:
“ALTER ASSEMBLY CustomerAssembly FROM ‘c:\cust.dll’ WITH UNCHECKED DATA”
This communicates with SQL Server saying that you have not made any changes to serialization stuff, no data types are changed, etc. Only you have changed is some piece of code for fixing bugs etc.

Question 39.
How do I drop an assembly?
Answer:
DROP ASSEMBLY cust
Note If the assembly is referencing any other objects like triggers, stored procedures, UDT, other assemblies then the dependents should be dropped first, or else the drop assembly will fail

Question 40.
Can we create SQLCLR using .NET framework 1.0?
Answer:
No at this moment only .NET 2.0 version and above are supported with SQL Server.

Question 41.
While creating .NET UDF what checks should be done?
Answer:
Following are some checks that are essential for UDF ( User Defined Function)

• The class in which the function is enclosed should be public.
• NET Function must be static.

Note  When we want to catalog the assembly and function. Then first we catalog the class and the function. 
          In short, we use "Create Assembly" and then "Create Function".

Question 42.
How do you define a function from the .NET assembly?
Answer:
Below is a sample “create function “statement and the following are the legends defined in it:-

SortCustomer:- Name of the stored procedure function and can be different from the .NET function. In this case the .NET function is by name sort which is defined in the external name.
CustomerAssembly:- The name of the assembly.
CustomerNameSpace:- The Namespace in which the class is lying.
Sort:-The .NET function name.
Other things are self-explanatory.

Create Function SortCu$tomer(@Strcustcode int) returns int As EXTERNAL NAME CustomerAssembly.
[CustomerNameSpace.CustomerClass].Sort

Note One important thing about thefunction parameter sis allinput parameter will go by the order mapping. 
         So what does that mean if my .NETfunction namefund has thefollowing definitions:-

func1 (int i, double x,string x)
Then my stored procedure should be defined accordingly and in the same order. That means in the stored procedure you should define it in the same order.

Question 43.
Can you compare T-SQL and SQLCLR?
Answer:

Note  This will be one of the favorite questions during the interview. The interviewer will want to know if 
          you know when is the right decision to take T-SQL or .NET for writing SQL Server objects. 
          When I say SQL Server objects I am referring to stored procedures, functions, or triggers.

1. Pure Data access code should always be written using T-SQL as that’s what they were meant for. T-SQL does not have to load any runtime which makes the access much faster. Also to note T-SQL will have access directly to internal buffers for SQL Server and they were written in probable assembly and “C” which makes them much faster for data access.

2. Pure Non-data access code like computation, string parsing logic, etc should be written in. NET. If you want to access web services or want to exploit OOP’s programming for better reusability and read external files it’s good to go for. NET.
We can categorize our architect decision on their types of logic:-

3. Pure Data access functionality – Go for T-SQL.

4. Pure NON-Data access functionality – Go for. NET.

5. A mixture of data access and NON-Data access – Needs architecture decision.
If you can see the first two decisions are straightforward. But the third one is where you will have to do a code review and see what will go the best. Probably also run it practically, benchmark, and see what will be the best choice.

Question 44.
With respect to .NET is SQL SERVER case sensitive?
Answer:
Following are some points to remember regarding case sensitiveness:-

• Assembly names are not case-sensitive.
• Class and Function names are case-sensitive.
  So what does that mean? Well if you define a .NET DLL and catalog it in SQL Server. All the methods and class name are case sensitive and assembly is not case sensitive. For instance, I have cataloged the following DLL which has the following details:-
• Assembly Name is “CustomerAssembly”.
• Class Name in the “CustomerAssembly” is “ClsCustomer”.
• Function “GetCustomerCountO” in class “ClsCustomer”.
  When we catalog the above assembly in SQL Server. We can not address the “ClsCustomer” with “CLSCUSTOMER” or function “GetCustomerCountO” with “getcustomercount( )” in SQL Server T-SQL language. But assembly “CustomerAssembly” can be addressed by “customer assembly” or “CUSTOMER ASSEMBLY”, in short, the assemblies are not case sensitive.

Question 45.
Does case sensitive rule apply for VB.NET?
Answer:
The above case-sensitive rules apply irrespective of whether the .NET language is case sensitive or not. So even if VB.NET is not case sensitive the rule will apply.

Question 46.
Can nested classes be accessed in T-SQL?
Answer:
No, you can not access nested classes in T-SQL it’s one of the limitations of SQLCLR.

Question 47.
Can we have SQLCLR procedure input as an array?
Answer:
SQL Server has no data types like arrays so it will not be able to map array datatype of . NET. This will throw an error.

Question 48.
Can object data type be used in SQLCLR?
Answer:
You can pass object datatype to SQLCLR but then that object should be defined as UDF in SQL Server.

Question 49.
How’s precision handled for decimal datatypes in .NET?
Answer:
Note Precision is actually the number of digits after the point which determines how accurate you want to see the result. For example, in “9.29” we have the precision of two decimal places (.29).
In .NET we declare decimal datatypes without precision. But in SQL Server you can define the precision part also.
decimal i; –> .NET Definition decimal(9,2) –> SQL Server Definition
This creates a conflict when we want the .NET function to be used in T-SQL as SQLCLR and we want the precision facility.
Here’s the answer you define the precision in SQL Server when you use Create syntax. So even if .NET does not support the precision facility we can define the precision in SQL Server.

.NET definition fund (decimal xl)
{
)
SQL Server definition
create function funcl(@xl decimal(9,2)) returns decimal
as external name CustomerAssembly. [CustomerNameSpace.ClsCustomer].funcl

If you see in the above code sample fund is defined as a simple decimal but later when we are creating the function definition in SQL Server we are defining the precision.

Question 50.
How do we define INPUT and OUTPUT parameters in SQLCLR?
Answer:
.NET has the following types of variable directions by value, byref, and out (i.e. for C# only). Following is how the mapping goes:-

• Byval definition for function maps as input parameters for SQL Server.
• Byref definition maps to input and output parameters for SQL Server.

But for “out” types of parameters, there are no mappings defined. Its logical “out” types of parameter types do not have any equivalents in SQL Server.

Note  When we define byref in .NET that means if the variable value is changed it will be reflected outside 
          the subroutine, so it maps to SQL Server input/output (OUT) parameters.

Question 51.
Is it good to use .NET datatypes in SQLCLR?
Answer:
No, it’s always recommended to use SQL Server data types for SQLCLR code as it implements better integration. For example, for “int” datatype in .NET we can not assign NULL value it will crash, but using SQL datatype Sqllnt32 NULLS. will be handled. All SQL data types are available in “system.data.SQL types”, so you have to refer to this namespace in order to get the advantage of SQL datatypes.

Note  NULL is a valid data in SQL Server which represents no data, but .NETdatatype does not accept it.

Question 52.
How to move values from SQL to .NET datatypes?
Answer:
You have to use the value property of SQL datatypes.

Sqllnt32 x = 3/
int y = x.Value;

Note Direct assigning the values will crash your program.

Question 53.
What is a System? Data.SqlServer?
Answer:
When you have functions, stored procedures, etc written in .NET you will use this provider rather than the traditional System.Data.SQLClient. If you are accessing objects created using T-SQL language then you will need a connection to connect them. Because you need to specify which server you will connect to, what is the password and other credentials? But if you are accessing objects made using .NET itself you are already residing in SQL Server so you will not need a connection but rather a context.

Question 54.
What is SQLContext?
Answer:
As said previously when using ADO.NET to execute a T-SQL created stored procedure we are out of the SQL Server boundary ..So we need to provide an SQLConnection object to connect to the SQLServer. But when we need to execute objects which are created using .NET language we only need the context in which the objects are running.

So you can see in the above figure SQLConnection is used because you are completely outside the SQL Server database. While SQLContext is used when you are inside the SQL Server database. That means that there is already a connection existing so that you can access the ‘ SQLContext. And any connections created to access SQLContext are a waste as there is already a connection opened to SQL Server.
These all things are handled by SQLContext.

Question 55.
Which are the four static methods of SQLContext?
Answer:
Below are the four static methods in SQLContext:-
GetConnection( ):-This will return the current connection
GetCommand( ):- Get a reference to the current batch
transaction( ):-If you have used transactions this will get the current transaction
GetPipe( ):- This helps us to send results to clients. The output is in Tabular Data stream format. Using this method you can fill in DataReader or data set, which can later be used by the client to display data.

Note   In the top question I had shown how we can manually register the DLLs in SQL Server but in real 
           projects, nobody would do that rather we will be using the VS.NET studio to accomplish the same. 
           So we will run through a sample of how to deploy DLLs using VS.NETandparalelly we will also run through 
           how to use SQLContext.

Question 56.
Can you explain the essential steps to deploy SQLCLR?
Answer:
This example will make a simple walk-through of how to create a stored procedure using visual studio.net editor. During the interview, you can make the steps short and explain them to the interviewer. So we will create a stored procedure that will retrieve all products from the Adventureworks database. All products are stored in the “Production. product” table.

Let start step go to the visual studio —> new project —> expand the Visual C# (+)–> select database, you will see SQL Server project. Select SQL Server project template and give a name to it, then click ok.

As these DLLs need to be deployed on the server you will need to specify the server details also. So for the same, you will be prompted to specify the database on which you will deploy the .NET stored procedure. Select the database and click ok. In case you do not see the database you can click on “Add reference” to add the database to the list.

Once you specify the database you are inside the visual studio.net editor. On the right-hand side, you can see the solution explorer with some basic files created by the visual studio in order to deploy the DLL on the SQL Server. Right-click on SQL Server project and click on ADD – -> New items are displayed as shown in the figure below.

You can see in the below figure you can create different objects using VS.NET. For this point in time, we need to only create a stored procedure that will fetch data from “Product.Product”.

This section is where the real action will happen. As said previously you do not need to open a connection but use the context. So below are the three steps:-

• Get the reference of the context.
• Get the command from the context.
• Set the command text, at this moment we need to select everything from the “Production. product” table.
• Finally, get the Pipe and execute the command.

using System;
using System. Data;
using System.Data.Sql;
using System.Data.SqlServer;
using System.Data.SqlTypes;
[public partial class StoredProcedures 
{
     [SgXProcedure]
     public static void SelectProductAll()
     {
        // Put your code here
        SqlCairman d sqlCmd = SqlContext. GetCommand () ;
        sqlCmd.CommandText = "select * from production.product";
        SglContext.GetPipe().Execute(sqlCmd);
}
);

After that, you need to compile it to a DLL form and then deploy the code in SQL Server. You can compile using the “Build Solution” menu to compile and “Deploy Solution” to deploy it on SQL Server.

After deploying the solution you can see the stored procedure “SelectProductAll” in the stored procedure section as shown below.

Just to test I have executed the sorted procedure and everything working fine.

Question 57.
How do we create a function in SQL Server using .NET?
Answer:
In order to create the function, you have to select in Visual studio installed templates/ User-defined function template. Below is the sample code. Then follow again the same procedure of compiling and deploying the solution.

Question 58.
How do we create triggers using .NET?
Answer:
For the trigger, you have to select a trigger template. But you can see some differences in code here. You have to specify on which object and which event will this method fire. The first attribute specifies the name, target (on which the trigger will fire), and event (insert, update or delete).

[SqlTrigger (Name="Trigger1", Target="Tablel", Event="FOR INSERT”)] public static void Triggerl()
{ 
     // Put your code here SqlTriggerContext objtrigcontext =
     SqlCon text.GetTriggerContext();
     SqlPipe objsqlpipe = SqlContext.GetPipe();
     SqlCommand objcommand = SqlContext.GetCommand(); 
     if (objtrigcontext.TriggerAct ion == TriggerAction.Insert) 
     {
         objcommand.CommandText = "insert into tablel
         values(' Inserted')";
         objsqlpipe.Execute(objcommand);
     }
}

Question 59.
How to create User Defined Functions using .NET?
Answer:
The below code is self-explanatory. Compiling and deploying remain the same for all objects created using. NET.

using System;
using System.Data.Sql;
using System.Data.SqlTypes;


public partial class UserDefinedFunctions
{
   [SglFunction]
   public static Sqllntl6 Functionl(SqXIntl6 numl , Sglint16 num2)
   {
   // put your code here
   return numl+num2;
   }
};

Note  Some home work for readers down.

Question 60.
How to create aggregates using .NET?
What is Asynchronous support in ADO.NET?
Answer:
One of the features that were missing in ADO.NET was asynchronous processing. That means once your SQL command is executed your UI has to wait until it’s finished. ADO.NET provides support where you do not have to wait until the SQL is executed in the database. You can see from the code below you have to issue “BeginExecuteReader” and then proceed ahead with some other process. After you finish the process you can come back and see your results. Long-running queries can really be benefited from Asynchronous support.

conn.Open( ) ;
IAsyncResult myResult = mycommand.BeginExecuteReader(); while (!myResult.IsCompleted)
{ 
// execute some other process
}
// Finally process the data reader output
SqlDataReader rdr = my command.EndExecuteReader(result);

Note  Here's a small project which you can do with Asynchronous processing. Fire a heavy duty SQL and 
          in UI show how much time the SQL Server took to execute that query.

Question 61.
What is MARS support in ADO.NET?
Answer:
In previous versions of ADO.NET, you should one connection on every result set. But this new feature allows you to execute multiple commands on the same connection. You can also switch back and forth between the command objects in a connection. There is nothing special to do for MARS (Multiple Active Result Sets) just you can allocate multiple command objects on a single connection.

Question 62.
What is the SQlbulkcopy object in ADO.NET?
Answer:
With SQlbulkcopy you can insert bulk records into a table. The command is pretty simple you can see we have provided the DataReader object to the SQlbulkcopy object and he will take care of the rest.

SqlBulkCopy objbulkData = new SqlBulkCopy (conn) ; 
objbulkData. DestinationTableName = " table 1"; 
objbulkData.WriteToServer(datareaderl);

Question 63.
How to select a range of rows using ADO.NET?
Answer:
Twist: – What is paging in ADO.NET?
By paging, you can select a range of rows from a result set. You have to specify starting row in the result set and then how many rows you want after that.

command.ExecutePageReader(CommandBehavior.Default, 1, 10);

You can see in the above example I have selected 10 rows and start from one. This functionality will be used mainly when you want to do paging on the Ui side. For instance, you want to show 10 records at a time to the user this can really ease of lot of pain.

Question 64.
What are the different types of triggers in SQ1 SERVER 2000?
Answer:
There are two types of triggers

INSTEAD OF triggers

INSTEAD OF triggers fire in place of the triggering action. For example, if an INSTEAD OF UPDATE trigger exists on the Sales table and an UPDATE statement is executed against the Sales table, the UPDATE statement will not change a row in the sales table. Instead, the UPDATE statement causes the INSTEAD OF UPDATE trigger to be executed, which may or may not modify data in the Sales table. ,

AFTER triggers

AFTER triggers execute following the SQL action, such as an insert, update, or delete. This is the traditional trigger that existed in SQL SERVER.
INSTEAD OF triggers get executed automatically before the Primary Key and the Foreign Key constraints are checked, whereas the traditional AFTER triggers get executed after these constraints are checked.
Unlike AFTER triggers, INSTEAD OF triggers can be created on views.
If we have multiple AFTER Triggers on the table how can we define the sequence of the triggers?

If a table has multiple AFTER triggers, then you can specify which trigger should be executed first and which trigger should be executed last using the stored procedure sp_settriggerorder. All the other triggers are in an undefined order which you cannot control.

Question 65.
How can you raise custom errors from stored procedures?
Answer:
The RAISERROR statement is used to produce an ad hoc error message or to retrieve a custom message that is stored in the sys messages table. You can use this statement with the error handling code presented in the previous section to implement custom error messages in your applications. The syntax of the statement is shown here.

RAISERROR ({msg_id jmsg_str }{, severity ,state }
[ ,argument [ ..n ] ] ))
[ WITH option [ ,,...n ] ]

A description of the components of the statement follows.

msg_id:-The ID for an error message, which is stored in the error column in sys messages.

an msg_str:-A custom message that, is not contained in sys messages.

severity:- The severity level associated with the error. The valid values are 0-25. Severity levels 0-18 can be used by any user, but 19-25 are only available to members of the fixed-server role sysadmin. When levels 19-25 are used, the WITH LOG option is required.

state A value that indicates the invocation state of the error. The valid values are 0-127. This value is not used by SQL Server.
Argument,…

One or more variables are used to customize the message. For example, you could pass the current process ID (@@SPID) so it could be displayed in the message.
WITH option,. . .
The three values that can be used with this optional argument are described here.
LOG – Forces the error to logged in to the SQL Server error log and the NT application log. NOWAIT – Sends the message immediately to the client.
SETERROR – Sets @@ERROR to the unique ID for the message or 50,000.

The number of options available for the statement makes it seem complicated, but it is actually easy to use. The following shows how to create an ad hoc message with a severity of 10 and a state of 1.

RAISERROR (‘An error occurred updating the NonFatal table; 10,1)

–Results–

An error occurred updating the NonFatal table

The statement does not have to be used in conjunction with any other code, but for our purposes, it will be used with the error handling code presented earlier. The following waters the ps_NonFatal_INSERT procedure to use RAISERROR.

USE tempdb
go
ALTER PROCEDURE ps_NonFatal_INSERT
@Column2 int = NULL
AS
DECLARE @ERRORMsgID int
INSERT NonFatal VALUES (@Column2)
SET @ErrorMsgID = @@ERROR
IF @ErrorMsgID <>0
BEGIN
RAISERROR ('An error occured updating the NonFatal table',10,1)
END

When an error-producing call is made to the procedure, the custom message is passed to the client. The following shows the output generated by Query Analyzer.