Primary key vs Unique key

SQL allows two interesting keywords to decorate a table - Primary key and Unique key. Differences or similarities are as follows,

• primary key does not allow nulls but unique key does
• primary key does clustered indexing, whereas unique key does non clustered
• both can be used for constraints
• multiple unique keys can be defined for a table

MSSQL Management Studio: Database Diagram problem.

Recently I got an error message in Management Studio with database diagram. Error message is

To solve this:

1. Right Click on interested database, choose properties
2. Goto the Files Page
3. Enter "sa" in the owner textbox.
4. Save changes.

Alternative (haven't tried yet) is to give privilege by

ALTER AUTHORIZATION ON DATABASE::YourDatabaseName TO sa

GO

Using SqlDataReader on multiple results.

Suppose we plan to execute multiple select query and how do we fetch result using SqlDataReader. Well this can be achieved by using SqlDataReader.NextResult

Example

Create test database and sample tables

create table employee (id int identity(1,1), name varchar(256), primary key(id))
create table employeeaddress (id int identity(1,1), empaddress varchar(256), primary key(id))


insert into employee values('Johan')
insert into employee values('Leon')


insert into employeeaddress values('Eindhoven, The Netherlands')

Now lets fetch results using SqlDataReader, C# program as follows

string cmdText = @"select * from employee;select * from employeeaddress";

            using (SqlConnection sqlCon = new SqlConnection(@"Data Source=.\sqlexpress;Initial Catalog=testDB;Integrated Security=SSPI;"))

            {

                using(SqlCommand sqlCmd = new SqlCommand(cmdText,sqlCon))

                {

                    sqlCon.Open();

                    SqlDataReader reader = sqlCmd.ExecuteReader();

                    int ResultIndex =1;

                    do

                    {

                        Console.WriteLine("Results from table - " + ResultIndex);

                        while (reader.Read())

                        {

                            Console.WriteLine(reader[0]);

                        }

                        

                        ++ResultIndex;

                    } while (reader.NextResult());

                    reader.Close();

                }

            }

Simple shared_ptr implementation

auto_ptr does not allow sharing object, i.e. when auto_ptr is shared ownership transfers to rhs. This ensures that we have only one copy of pointer and pointer will be destroyed when scope is over. A simple auto_ptr implementation

However there are situations where we like to share our object pointer. For such need boost has shared_ptr implementation, my version of shared_ptr implementation is as follows



template <class T>
class MySharedPtr
{


public:
//constructor creates the dynamic interger to hold ref counting
explicit MySharedPtr(T* p = 0):m_ptr(p)
{
pRefCount = new int();
*pRefCount = 1;
}


~MySharedPtr()
{
//destroy only if ref count is 1 else null set member
if(1 == *pRefCount)
{
if(NULL != m_ptr)
delete m_ptr;
delete pRefCount;
}
else
{
m_ptr = NULL;
//decrement the counter when not deleteing object
--*pRefCount;
}

}


MySharedPtr(MySharedPtr<T> & rhs)
{
//during copy construction share the object pointer
// and ref counting pointer
this->m_ptr = rhs.m_ptr;
this->pRefCount = rhs.pRefCount;
//increment the counter as we have one more object now
++*pRefCount;
}


MySharedPtr<T>& operator = (MySharedPtr<T>& rhs)
{
if(this->m_ptr != rhs.m_ptr)
{
if(this->m_ptr != NULL)
{
//if rhs object counter is 1 then delete it otherwise
//decrement rhs counter
if(*this->pRefCount == 1)
{
delete m_ptr;
m_ptr = NULL;
delete pRefCount; 
pRefCount = NULL;
}
else
{
--*(this->pRefCount);
}
}
//assing rhs and increment counter
this->m_ptr = rhs.m_ptr;
this->pRefCount = rhs.pRefCount;
++*(rhs.pRefCount);
}
return *this;
}


private:
T* m_ptr;
//pointer to hold ref counter
int* pRefCount;
};

MS SQL AutoClose

Something that solved one of our big problems AutoClose.

Recently in one of our project we exposed a stateless service with SQL 2005 Express back end. And we ended up with a crazy issue.

Issue is after 5-6 minutes the performance of the service went down. Thought it was in milli seconds, when clients made 100-200 calls, performance hit was visible.

Its like SQL maintains resources for a connection or execution of query, which SQL by default will recycle when there is no activity. This is a good feature, however in our case it proved very costly.

So turned the feature OFF.
create database employees;
alter database employee set AUTO_CLOSE OFF;

Post increment vs Pre increment

Every time when the post increment operator is used it performs two tasks firstly it stores the value of the variable in temporary location (say in a register) and then it increments the value of the variable and save it to the memory location. The incremented value is then transferred from the register to wherever it was assigned.

Apart from that the pre increment does not have to transfer the value That's why it is quite faster than the post increment operator

//pre increment
int a = 1;
int b = ++a; // Now a is 2 and b is also 2.


//post increment
a = 1;
int b = a++; // Now a is 2 but b is 1.

auto_ptr concept extended to work with pool of memory.

Based on the concept of Simple auto_ptr implementation, following example shows class reusing allocated heap memory with the aid of a pool.

Features of this implementation are

• consumers need not worry of destroying memory.
• Assignment and copy constructor transfer ownership like auto_ptr
• Destructor doesn't delete allocated memory, instead pushes memory to pool for reuse.
• static function ClearPool clears memory pool in end.

class MemMgr
{
public:
MemMgr(char* p)
{
if(NULL == memPool.size())
{
//allocate memory only if pool is not having entries
cout<<"Pool size is zero"<<endl;
pMemory = new char[MemSize];

}
else
{
//dont allocate, reuse from pool
cout<<"Pool size is "<<memPool.size()<<endl;
pMemory = memPool.front();
memset(pMemory,0,MemSize);
memPool.pop_front();
}


strncpy((char*)pMemory, p, strlen(p));
((char*)pMemory)[strlen(p)]=0;
}


MemMgr(MemMgr& rhs)
{
//in copy constructor make sure the ownership is transfered
this->pMemory = rhs.pMemory;
rhs.pMemory = NULL;
}


MemMgr& operator =(MemMgr& rhs)
{
if(this->pMemory != rhs.pMemory)
{
//if rhs is different, push lhs to pool
if(NULL != this->pMemory)
{
memPool.push_back(this->pMemory);
this->pMemory = 0;
}
}
//assign rhs memory to lhs
this->pMemory = rhs.pMemory;
//set rhs pointer to null
rhs.pMemory = NULL;
return *this;
}


~MemMgr()
{
if(NULL != this->pMemory)
{
//dont delete memory, push to pool
memPool.push_back(this->pMemory);
this->pMemory = 0;
}
}
void Print()
{
cout<<(char*)pMemory<<endl;
}


//static function to clear pool in end
static void ClearPool()
{
cout<<"Pool size is "<<memPool.size()<<endl;
if(NULL != memPool.size())
{
void* pMem = 0;
for(list<void*>::iterator iter = memPool.begin(); iter != memPool.end(); iter++)
{
delete [] *(iter);
}
}
}


private:
static const int MemSize = 100;
       //our memory pool implemented with stl::list
static list<void*> memPool;
void* pMemory;
};


//don't forget to initialize static member
list<void*> MemMgr::memPool;