How to create table in MSSQL only if it does not exist

Often you wanna make sure some tables in database exist, but you want to avoid getting the error message “There is already an object named ‘yourtable’ in the database.”, that you would get by simply issuing the CREATE TABLE command.

For these cases, MSSQL offers keyword EXISTS (most SQL engines provide similar facilities; e.g. MySQL C API allows you to directly check for the existance of database and/or table).

All you have to do in MSSQL is make sure the table is not yet registered in the sysobjects table, using the following SQL command:

IF NOT EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[tablename]')
AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
CREATE TABLE [dbo].[tablename] ( columns specification );

where “tablename” is name of table you want to create, and “columns specification” is the usual definition of table’s columns. Feel free to extend the table definition to your liking.

Or you might want to DROP the table before re-creating it:

IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[tablename]')
AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[tablename];
CREATE TABLE [dbo].[tablename] ( columns specification );

Yes, that’s all. Good luck!

Precise time measuring on Win32

Windows provide means for a more precise time measurement than just the standard C-function clock().

The basic definitions and declarations are as follows (typedef is for readability):

#define WIN32_LEAN_AND_MEAN
#include <windows.h>

typedef LARGE_INTEGER timeStamp;

void getCurrentTimeStamp(timeStamp& _time);
timeStamp getCurrentTimeStamp();
double getTimeMili(const timeStamp& start, const timeStamp& end);
double getTimeSecs(const timeStamp& start, const timeStamp& end);

Notes:
* LARGE_INTEGER is 8B big, so it’s faster to use reference than direct copy, but this may vary compiler to compiler – test before using!
* Value stored in LARGE_INTEGER variable is by itself useless – it’s something of the sort “number of ticks since the computer booted”; but two of them define interval with quite a useable resolution. (check MSDN for more info)

Usage is trivial – to measure intervals, simply use one of getCurrentTimeStamp() functions to get current “timestamp” of beginning and end of interval, and then use getTimeMili() go get interval’s length in miliseconds, or getTimeSecs() for length in seconds.

Implementation pretty much just uses what’s readily available (both versions of getCurrentTimeStamp() are obvious candidates for inline‘s):

void getCurrentTimeStamp(timeStamp& _time)
{
    QueryPerformanceCounter(&_time);
}

timeStamp getCurrentTimeStamp()
{
    timeStamp tmp;
    QueryPerformanceCounter(&tmp);
    return tmp;
}

double getTimeMili(const timeStamp& start, const timeStamp& end)
{
    timeStamp dwFreq;
    QueryPerformanceFrequency(&dwFreq);
    return double(end.QuadPart - start.QuadPart) /
        double(dwFreq.QuadPart) * 1000;
}

double getTimeSecs(const timeStamp& start, const timeStamp& end)
{
    timeStamp dwFreq;
    QueryPerformanceFrequency(&dwFreq);
    return double(end.QuadPart - start.QuadPart) / double(dwFreq.QuadPart);
}

Note:
* The QueryPerformanceFrequency(&dwFreq); in getTimeMili() returns the same value on each call – storing this value at the start of the program might be a good idea. (most probably – on multiprocessor computers this might be false!)