A stored procedure as system object in SQL SERVER 2008

A stored procedure as system object in SQL SERVER 2008

Today I encountered a situation to use a stored procedure across all objects , so that I could use stored procedure on any database within a given SQL Server instance.

After some digging I found that there are few ways to create such a stored procedure:

1. The stored procedure must be created in the master database.
2. The name of the stored procedure must start with “sp_“.
3. The stored procedure must be marked as a system object.

In this I am pointing to the third one to create stored procedure as system object.Below is the way to create and its use.

  1: CREATE PROCEDURE sp_DotnetPiper
  2: AS
  3: SELECT      name, object_id, type_desc
  4: FROM        sys.objects
  5: WHERE       is_ms_shipped <> 1
  6: 
  7: -- Mark the stored procedure as a system object
  8: EXEC sys.sp_MS_marksystemobject sp_DotnetPiper
  9: 
 10: --To execute the systemmarked procedure
 11: exec sp_DotnetPiper

The first two are quite easily accomplished but the third requirement requires the use of an undocumented stored procedure named  sys.sp_MS_marksystemobject which will mark the created stored procedure as a system object.

After you completed the above steps, you can run the stored procedure on any of the databases found in that instance of SQL Server.

I’ve run the command and it shows the following output as depicted below:

   1:  USE   Northwind

   2:  exec sp_DotnetPiper

 

Note: This is one of the question may ask in an interview .

Hope it will help you

 

Thanks

Categories: Interview Questions , SQL Server

Primary index and Secondary index In Sql Server

Primary index and Secondary index In SQL Server

Primary index:

• A primary index is an index on a set of fields that includes the unique primary key for the field and is guaranteed not to contain duplicates.
• This also called as Clustered index.
• e.g. ID  in Employee can be an example of this.

Secondary index:

• A Secondary index is an index which is not a primary index and it may have duplicates values.
• e.g. First_name can be example of it. Because First_name name can have similar values like “james”.

These may be helpful down the line .Enjoy Coding and Smile 

MVC Articles & WCF and WebAPI

Thanks for reading

Categories: SQL Server

String.Empty Vs ""

String.Empty vs "" : An Interview Question

Use String.Empty rather than "". This is more for speed than memory usage but it is a useful tip. The "" is a literal (A literal is "any notation for representing a value within source code) so will act as a literal: on the first use it is created and for the following uses its reference is returned. Only one instance of "" will be stored in memory no matter how many times we use it!

There is no such memory penalties with the use of “”. The problem is that each time the "" is used, a comparing loop is executed to check if the "" is already in the intern pool.

On the other side, String.Empty is a reference to a "" stored in the .NET Framework memory zone. String.Empty is pointing to same memory address for VB.NET and C# applications. So why search for a reference each time you need "" when you have that reference in String.Empty?

Note: Best Coding practice is to use string.Empty.

 

These may be helpful down the line .Enjoy Coding and Smile 

MVC Articles & WCF and WebAPI

Thanks.
Enjoy coding and reading.

Categories: C# , Interview Questions

Three modes of WCF Instance management

WCF instance management Interview Question

An idea  to write this blog to get familiar with 3 modes of WCF instance management , how all does work in context to WCF.

 

Three modes are given below:

---

1. InstanceContextMode. PerCall
2. InstanceContextMode. PerSession
3. InstanceContextMode. Single

---

These all are describe in a detail below:

InstanceContextMode. PerCall

It means each client request call will be served by the new WCF Service instance.e.g. Each time when client makes a method call ,A new service instance is created on the server to serve the request.After responding to server it gets destroyed and managed by Garbage collection.This is the routine process on each client request to server.

   1:  [ServiceBehavior(InstanceContextMode = InstanceContextMode.Percall)] 

   2:  public class Service : IService

   3:  {

   4:  }

InstanceContextMode. PerSession: 

It means each client will have a separate WCF Service instance to serve the WCF client request.It maintains the client state.The client makes one method call in the same session and the same WCF service instance serves the another method call. Which is different from the PerCall.
After the client finishes its activity, the WCF instance is destroyed  and managed by the garbage collector for clean up.

You can decorate Service with this annotation.

   1:  [ServiceBehavior(InstanceContextMode = InstanceContextMode.Percall)] 

   2:  public class Service : IService

   3:  {

   4:  }

InstanceContextMode. Single :

It means All client request is maintained and served by the single WCF Service instance.In this case WCF service instance doesn't get destroyed, the service instance persists on to server to handle other requests.One global WCF server service instance is created to serve all client requests.

   1:  [ServiceBehavior(InstanceContextMode = InstanceContextMode.Single)] 

   2:  public class Service : IService

   3:  {

   4:  }

These may be helpful down the line .Enjoy Coding and Smile 

Categories: WCF and WebAPI