SQL Server merge statement

The merge statement allows you to perform CRUD operations in a single query:

MERGE
	[Current_Offer_Counts] AS [Target]
USING
	[Get_Total_Usage] (param, NULL) AS [Source]
ON
	[Target].[ID] = [Source].[ID]
WHEN MATCHED AND [Target].[Count_days] != [Source].[total_usage] THEN
	UPDATE SET [Count_days] = [Source].[total_usage]
WHEN NOT MATCHED BY TARGET THEN 
	INSERT ([ID], [Count_days]) VALUES ([Source].[ID], [Source].[total_usage])
WHEN NOT MATCHED BY SOURCE THEN 
	DELETE

SQL server query demonstrating inner and outer joins

Here’s an example of a SQL Server query demonstrating inner and outer joins.


SELECT
q.[field1]
,REPLACE(REPLACE(CONVERT(nvarchar(max),q.[field2]), CHAR(13), ''), CHAR(10), '') AS 'friendly column name'
,(u.field3 + ' ' + u.field4) AS 'Name'
,ld.[field5]
,doc.[field6] AS 'Document ref'
,doc.[field7]
FROM [FreedomOfInformation].[dbo].[Table1] q
INNER JOIN [Database1].[dbo].[Table2] u ON u.[UserID] = q.[UserID]
INNER JOIN [Database1].[dbo].[Table3] l ON l.[LogID] = q.[LogID]
INNER JOIN [Database1].[dbo].[Table4] lt ON lt.[topicId] = q.[TopicID]
LEFT OUTER JOIN [Database1].[dbo].[Table5] ld ON ld.[qaid] = q.[QAID]
LEFT OUTER JOIN [Database2].[dbo].[Table6] doc ON ld.[DocumentID] = doc.[DocumentID]
WHERE q.[DatetimeAdded] BETWEEN DATEADD(YEAR, -3, GETDATE()) AND GETDATE()
ORDER BY q.[DatetimeAdded]

.Net simplest possible MSSQL database connection

We’re all using ORMS/Micro ORMS/some other framework to abstract away the data layer so I had to remind myself of how to connect to a database the ‘old-fashioned’ way.

using System;
using System.Data.SqlClient;

namespace DBConnTest
{
    class DataBaseConnection
    {
        static void Main(string[] args)
        {
            const string connStr = @"Data Source=XXXX\XXXX, [XXX];Database=XXXXX; Integrated Security=SSPI;";

            using (var conn = new SqlConnection(connStr))
            {
                var cmd = new SqlCommand("select..", conn) { CommandTimeout = 400 };
                conn.Open();

                var dataReader = cmd.ExecuteReader();

                while (dataReader.Read())
                {
                    Console.WriteLine(dataReader[0].ToString());
                    // ...
                }

            }
        }
    }
}

Installing VoltDB on Ubuntu 13 (64 bit)

VoltDB is an example of a NewSQL type of database, it’s based on the H-Store DBMS.

This is an example of how to get going with VoltDB on Ubuntu 13 (64 bit only), This is assuming a pre existing installation of Ubuntu 13 64 bit edition.

1) Register and download the software on the VoltDB website, I’ve selected the VoltDB Open Source Edition Download for Linux. There is an Enterprise edition which offers a wider feature set.

The zip archive is named: LINUX-voltdb-3.7.0.4.tar.gz

2) Copy the file to your installation directory, I’ve chosen /opt

Change directory to /opt: cd /opt

3) Inflate the archive file

Use the command: tar zxvf LINUX-voltdb-3.7.0.4.tar.gz

4) Check the prerequisites are installed

Install aptitude

apt-get install aptitude

Install the build essentials package

aptitude install build-essential

Install a Java jdk (openJDK in this instance)

aptitude install openjdk-7-jdk

5) We should be able to run the VoltDB demos, the voter demo in this instance:

Open the file: file:///opt/voltdb-3.7/tools/demo/index.htm in your browser

To run the Voter demo:

Start the Server: sh /opt/voltdb-3.7/examples/voter/run.sh

Start the client: sh /opt/voltdb-3.7/examples/voter/run.sh client

Volt1

Open the performance monitor to view the running demo application: file:///opt/voltdb-3.7/examples/voter/LiveStats.html

Volt2

View the data via SQL queries with the built in studio file:///opt/voltdb-3.7/tools/studio.web/index.htm?startup=query – You can issue SQL queries against the VoltDB database.

Volt3

SQL Server TCP settings and why not using connection pooling is a bad idea

I’ve been talking to a contact who has been experiencing some connection issues with a large scale SQL Server implementation. The database is serving as the back-end to a well used Internet site. They reported that during the course of normal operation that they’d received dropped TCP connections and difficulty in achieving automatic failover at the database layer. They’d investigated the possibility of running without database connection pooling enabled. I advised that this was not a suitable idea. Afterwards this support article was found: support.microsoft.com/kb/328476

The article advises:

Note that Microsoft strongly recommends that you always use pooling with the SQL Server drivers. Using pooling greatly improves overall performance on both the client side and SQL Server side when you use the SQL Server drivers. Using pooling also considerably reduces network traffic to the computer that is running SQL Server. For example, a sample test that used 20,000 SQL Server connection opens and closes with pooling enabled used about 160 TCP/IP network packets, for a total of 23,520 bytes of network activity. With pooling disabled, the same sample test generated 225,129 TCP/IP network packets, for a total of 27,209,622 bytes of network activity.

The moral of the story – connection pooling is advised and think carefully before turning off.

.Net C# PetaPoco inserting a row

I’ve been experimenting with the PetaPoco framework again. Below is an update to the earlier example with a simple row insertion routine.

using System;
using System.Linq;
using PetaPoco;

namespace CsharpPetaPocoDemo
{
    /// <summary>
    /// Simple PetaPoco example
    /// </summary>
    class CsharpPetaPocoExample
    {
        /// <summary>
        /// Defines the entry point of the application.
        /// </summary>
        static void Main()
        {
            // Nuget Install-Package PetaPoco

            using (var db = new Database("PetaExample"))
            {
                try
                {
                    // Insert a new data row
                    var employee = new EmployeeInfo
                                       {
                                           EmpName = "Person d",
                                           Salary = 10000,
                                           DeptName = "Service",
                                           Designation = "Supervisor"
                                       };

                    db.Insert("EmployeeInfo", "EmpNo", employee);

                    // Select the rows
                    var result = db.Query<EmployeeInfo>("select * from EmployeeInfo").ToList();

                    result.ForEach(PrintResults);
                }
                catch (Exception ex)
                {
                    Console.WriteLine(ex.Message);
                }
            }

            Console.ReadKey();
        }

        private static void PrintResults(EmployeeInfo employeeInfo)
        {
            Console.WriteLine("{0} {1} {2} {3} {4}", employeeInfo.EmpNo, employeeInfo.EmpName, employeeInfo.Designation, employeeInfo.DeptName, employeeInfo.Salary);
        }
    }

    /// <summary>
    /// Employee class, using properties for PetaPoco
    /// </summary>
    [TableName("EmployeeInfo")]
    [PrimaryKey("EmpNo")]
    public class EmployeeInfo
    {
        public int EmpNo { get; set; }
        public string EmpName { get; set; }
        public int Salary { get; set; }
        public string DeptName { get; set; }
        public string Designation { get; set; }
    }
}

Returns:

1 Person a Agent Sales 20000
2 Person b Officer Security 30000
3 Person c Geek IT 40000
4 Person d Supervisor Service 10000

.Net C# using the PetaPoco Micro ORM

Micro ORMS are increasingly popular because of their simplicity and generally lower overhead compared to monolithic ORM frameworks. PetaPoco is a commonly used Micro ORM, the code below shows how to get up and running.

1) Create a new project in Visual Studio (2012 in this example).

2) Add thePetaPoco reference to your project, if you’re using nuget from the command line do:

install-package PetaPoco

3) Add a connection string setting to your config file:

<connectionStrings>
    <add name="PetaExample" connectionString="Data Source=.\SQLEXPRESS;Initial Catalog=Company;Integrated Security=True;Connect Timeout=300;" providerName="System.Data.SqlClient" />
</connectionStrings>

4) Create a new class CsharpPetaPocoDemo.cs with the following contents:

using System;
using System.Linq;
using PetaPoco;

namespace CsharpPetaPocoDemo
{
    /// <summary>
    /// Simple PetaPoco example
    /// </summary>
    class CsharpPetaPocoExample
    {
        /// <summary>
        /// Defines the entry point of the application.
        /// </summary>
        static void Main()
        {
            using (var db = new Database("PetaExample"))
            {
                try
                {
                    var result = db.Query<EmployeeInfo>("select * from EmployeeInfo").ToList();

                    result.ForEach(PrintResults);
                }
                catch (Exception ex)
                {
                    Console.WriteLine(ex.Message);
                }
            }

            Console.ReadKey();
        }

        private static void PrintResults(EmployeeInfo employeeInfo)
        {
            Console.WriteLine("{0} {1} {2} {3} {4}", employeeInfo.EmpNo, employeeInfo.EmpName, employeeInfo.Designation, employeeInfo.DeptName, employeeInfo.Salary);
        }
    }

    /// <summary>
    /// Employee class, using properties for PetaPoco
    /// </summary>
    [TableName("EmployeeInfo")]
    [PrimaryKey("EmpNo")]
    public class EmployeeInfo
    {
        public int EmpNo { get; set; }
        public string EmpName { get; set; }
        public int Salary { get; set; }
        public string DeptName { get; set; }
        public string Designation { get; set; }
    }
}