Debug SQL Server CLR Stored procedure by using Visual Studio 2012

If have written your CRL stored procedure by using Visual studio 2010 then you know how to debug your CLR stored procedure. That is simple write click the test script and from menu click debug. And if you convert your project to Visual Studio 2012 then you may wonder how to debug the CLR Stored Procedure. In VS2012 you cannot debug test script like VS2010. InVS2012 first you have to open the test script. It will open like this.

Now you can debug your SP by pressing Execute with debugger.

Before that make sure you have connected to your database where CLR is running. That can be done by opening SQL Server Object Explorer from view menu.

 Then select your date base from drop down and start debug…

Note:

Data base has to be local otherwise you have to enable remote debugging.

And make sure that your database server is CLR enabled. That can be checked by using following code.

EXEC sp_configure 'clr enabled' ,1   RECONFIGURE

ADO.Net Table value parameter performance Analysis

My goal of this post is to analyse the table value parameter passing to SQL Server stored-procedure  and it performance against conventional parameter passing.To achieve my goal , I have created C#.net 4.5 windows application.

By using this application i have inserted data to SQL Server table. I have inserted test data to table by using to two methods .

• Conventional Parameter passing to SQL Server stored-procedure.

   1: public static void AddTestDataByObjectList(List<TestData> dataList)

   2: {

   3:  SqlConnection connection = new SqlConnection(DBConnectionReader.GetDBConnection());

   4:  connection.Open();

   5:  try

   6:  {

   7:   foreach (var data in dataList)

   8:    {

   9:     SqlCommand command = new SqlCommand("dbo.AddTestData", connection);

  10:     command.CommandType = CommandType.StoredProcedure;

  11:     SqlParameter parameter1 = new SqlParameter("@Col1", SqlDbType.VarChar, 20);

  12:                          

  13:     parameter1.Value = data.Col1;

  14:     command.Parameters.Add(parameter1);

  15:     SqlParameter parameter2 = new SqlParameter("@Col2", SqlDbType.Int);

  16:                          

  17:     parameter2.Value = data.Col2;

  18:     command.Parameters.Add(parameter2);

  19:     SqlParameter parameter3= new SqlParameter("@Col3", SqlDbType.DateTime);

  20:                          

  21:     parameter3.Value = data.Col3;

  22:     command.Parameters.Add(parameter3);

  23:     SqlParameter parameter4= new SqlParameter("@Col4", SqlDbType.VarChar, 50); 

  24:                          

  25:     parameter4.Value = data.Col4;

  26:     command.Parameters.Add(parameter4);

  27:     SqlParameter parameter5= new SqlParameter("@Col5", SqlDbType.VarChar, 8000); 

  28:                          

  29:     parameter5.Value = data.Col5;

  30:     command.Parameters.Add(parameter5);

  31:     SqlParameter parameter6= new SqlParameter("@Col6", SqlDbType.VarChar, 200); 

  32:                          

  33:     parameter6.Value = data.Col6;

  34:     command.Parameters.Add(parameter6);

  35:     SqlParameter parameter7= new SqlParameter("@Col7", SqlDbType.Decimal); 

  36:                          

  37:     parameter7.Value = data.Col7;

  38:     command.Parameters.Add(parameter7);

  39:     command.ExecuteNonQuery();

  40:     command.Parameters.Clear();

  41:   }

  42:  }

  43:  catch (Exception)

  44:  {                

  45:    throw;

  46:  }

  47:   finally

  48:  {

  49:     connection.Close();

  50:  }        

  51: }

• Table Value parameter passing to SQL Server stored-procedure.

   1:  public static void AddTestDataByDataTable(DataTable dataTable)

   2:    {

   3:     SqlConnection connection = new SqlConnection(DBConnectionReader.GetDBConnection());

   4:     connection.Open();

   5:       try

   6:       {

   7:        SqlCommand command = new SqlCommand("dbo.AddTestDataByTableType", connection);

   8:        command.CommandType = CommandType.StoredProcedure;

   9:        SqlParameter parameter1 = new SqlParameter("@TestData", SqlDbType.Structured);

  10:                           

  11:        parameter1.Value = dataTable;

  12:        command.Parameters.Add(parameter1);

  13:        command.ExecuteNonQuery();

  14:      }

  15:      catch (Exception)

  16:      {                

  17:        throw;

  18:      }

  19:      finally

  20:      {

  21:        connection.Close();

  22:      }  

  23:   }

I have inserted 10000 records to table by using above two methods. And measured the time take to each method. To get better result i have inserted 10000 records 20 times for each method.  Following are the results

Attempt	DataTable(ms)	ObjectList(ms)
1	206	4651
2	187	4484
3	288	5412
4	325	4341
5	222	4718
6	239	4636
7	210	4556
8	216	4269
9	201	4576
10	990	5105
11	178	5066
12	220	4021
13	241	4300
14	162	4561
15	274	4892
16	217	4747
17	223	4236
18	400	3966
19	869	4800
20	562	4322

  milliseconds(take to insert 10000 records) VS no of attempts

Conclusion:  According to this result table value parameter is the much faster than conventional parameters passing . Therefore we can use table value parameter where ever possible.

Pros :

• Faster data throughput.
• Less codding.

Cons :

• Hard to debug.
• May be need more network bandwidth.

Data base script that i have use to analysts table value parameter

   1:  USE tempdb;

   2:   

   3:  -- This table is use to measure time taken to each method

   4:  CREATE TABLE dbo.TestPerformance

   5:  (

   6:   ID           INT IDENTITY(1,1) primary key

   7:  ,TType        VARCHAR(50)

   8:  ,TIteration   INT

   9:  ,StartTime    DATETIME2

  10:  ,EndTime      DATETIME2

  11:  )

  12:  GO

  13:  -- Test Table with different data typs

  14:  CREATE TABLE dbo.TestData

  15:  ( ID        INT IDENTITY(1,1) Primary KEY

  16:   ,Col1      varchar(20)

  17:   ,Col2      INT

  18:   ,Col3      Datetime

  19:   ,Col4      varchar(50)

  20:   ,Col5      varchar(max)

  21:   ,Col6      varchar(200)

  22:   ,Col7      Decimal(20,2))

  23:   

  24:   GO

  25:   

  26:   -- Add test performance with start time

  27:   CREATE proc dbo.AddTestPerformance

  28:    @Type      VARCHAR(50)

  29:   ,@Iteration INT

  30:   ,@OutId     INT =NULL OUTPUT 

  31:   AS

  32:   BEGIN

  33:   INSERT INTO dbo.TestPerformance (TType,TIteration,StartTime) 

  34:   VALUES(@Type,@Iteration,SYSUTCDATETIME())

  35:   SET @OutId=@@IDENTITY

  36:   

  37:   END

  38:   GO

  39:   

  40:   -- Update the end time

  41:   create proc dbo.UpdateTestPerformance

  42:   @Id INT

  43:   AS

  44:   BEGIN

  45:   UPDATE dbo.TestPerformance SET EndTime=SYSUTCDATETIME() where ID=@Id

  46:   END

  47:   GO

  48:   --- Add test data using conventional parameters

  49:   create proc dbo.AddTestData

  50:    @Col1      varchar(20)

  51:   ,@Col2      INT

  52:   ,@Col3      Datetime

  53:   ,@Col4      varchar(50)

  54:   ,@Col5      varchar(max)

  55:   ,@Col6      varchar(200)

  56:   ,@Col7      Decimal(20,2)

  57:   AS

  58:   BEGIN

  59:   

  60:   INSERT INTO dbo.TestData(Col1,Col2,Col3,Col4,Col5,Col6,Col7) 

  61:   VALUES (@Col1,@Col2,@Col3,@Col4,@Col5,@Col6,@Col7)

  62:                           

  63:   END

  64:   GO

  65:   

  66:   -- Create a table type

  67:   CREATE TYPE dbo.TestType  AS TABLE

  68:   (Col1      varchar(20)

  69:   ,Col2      INT

  70:   ,Col3      Datetime

  71:   ,Col4      varchar(50)

  72:   ,Col5      varchar(max)

  73:   ,Col6      varchar(200)

  74:   ,Col7      Decimal(20,2))

  75:   

  76:   GO

  77:   -- add values using table type

  78:   create proc dbo.AddTestDataByTableType

  79:   @TestData dbo.TestType ReadOnly

  80:   AS

  81:   BEGIN

  82:   

  83:   INSERT INTO dbo.TestData(Col1,Col2,Col3,Col4,Col5,Col6,Col7) 

  84:   SELECT Col1,Col2,Col3,Col4,Col5,Col6,Col7 FROM @TestData

  85:   END

  86: 

Code to generate random data

   1:  public static List<TestData> GetTestDataList(int NoOfRows)

   2:          {

   3:              List<TestData> testDataList = new List<TestData>();

   4:              for (int i = 0; i < NoOfRows; i++)

   5:              {

   6:                  TestData testData = new TestData();

   7:                  testData.Col1 = Convert.ToString(Guid.NewGuid()).Substring(1,20);

   8:                  testData.Col2 = i;

   9:                  testData.Col3 = DateTime.Now.AddDays(i);

  10:                  testData.Col4 = Guid.NewGuid().ToString();

  11:                  testData.Col5 = Guid.NewGuid().ToString() +

  12:                                  Guid.NewGuid().ToString() + 

  13:                                  Guid.NewGuid().ToString() + 

  14:                                  Guid.NewGuid().ToString();

  15:                  testData.Col6 = Guid.NewGuid().ToString() + Guid.NewGuid().ToString();

  16:                  testData.Col7 = 1000000 + i;

  17:                  testDataList.Add(testData);            

  18:              }

  19:              return testDataList;        

  20:          }

  21:   

  22:          public static DataTable GetTestDataTable(int NoOfRows)

  23:          {

  24:              List<TestData> testDataList = new List<TestData>();

  25:              testDataList = GetTestDataList(NoOfRows);

  26:              DataTable dataTable = new DataTable("TestData");

  27:   

  28:              dataTable.Columns.Add("Col1", typeof(string));

  29:              dataTable.Columns.Add("Col2", typeof(int));

  30:              dataTable.Columns.Add("Col3", typeof(DateTime));

  31:              dataTable.Columns.Add("Col4", typeof(string));

  32:              dataTable.Columns.Add("Col5", typeof(string));

  33:              dataTable.Columns.Add("Col6", typeof(string));

  34:              dataTable.Columns.Add("Col7", typeof(decimal));

  35:              foreach (var data in testDataList)

  36:              {

  37:                  dataTable.Rows.Add(  data.Col1

  38:                                      ,data.Col2

  39:                                      ,data.Col3

  40:                                      ,data.Col4 

  41:                                      ,data.Col5

  42:                                      ,data.Col6

  43:                                      ,data.Col7);

  44:              }

  45:              return dataTable;  

  46:          }

SQL Server Management Studio Custom Quarry Shortcuts

How to create a custom quarry short cut ?

Step 1: Go to Tools>Options>Environment >Keyboard

Step 2: Now Create your own short cut (e.g. SELECT  TOP 100 * FROM)

Step 3: Refresh SSMS intellisense by pressing Ctrl + Shift + R 

Step 4: Test the short cut, highlight the table and press Ctrl +3

Like wise you can create your own shortcuts...

C#.net Conditional Operator Usage Tip

The && and || operators both “short circuit” when necessary. This means that after a complex expression
has been determined to be false, the remaining subexpressions will not be checked. If you require all expressions
to be tested regardless, you can use the related & and | operators.

Don't Depend on SQL Sever Sorting

Resonantly i have found a bug in production system. Where i have to uploaded data to temporary data base and import that data to another data  structure.
Data import part will happen in pre-define time schedule but not in same time. In my case data will be imported by using order of data upload . I have read data from data base without ordering the data. It work for some time more than one year. But suddenly system has generated errors due to some reason. I have done some digging. In addition i have found out that data has imported but not in intended order.
I have put order by clause in to select statement.That has resolved the my production issue. Unfortunately this error was not able to generate in local environment.
Therefore its always good to retrieve data with order by clause

Sample Code to test this scenario

 USE tempdb;
 -- Create Table

 CREATE TABLE dbo.TempData
 (ID INT IDENTITY(1,1) PRIMARY KEY
  ,Col1 INT)
 -- Insert Random data
  DECLARE @Count INT =0

  WHILE @Count < 1000
  BEGIN
 INSERT INTO dbo.TempData(Col1) VALUES(rand()*100000)
 SET @Count= @Count+1
  END
 --Select Top 10 records

  SELECT top 10* FROM dbo.TempData
















--Create Nonclusterd index on Col1

CREATE  NONCLUSTERED INDEX [TempData_Col1] ON dbo.TempData (Col1 ASC)

 --Select Top 10 records

SELECT top 10* FROM dbo.TempData