Dynamic SQL – Is It Good Practice to Create Tables Dynamically?

dynamic-sql

Let's say that I'm making an IRC bot, and in addition to all the other stuff that should be stored in the DB, I want to log all the messages in each channel in which the bot is present.

I already have a channels table with columns id, channel_name, and some other info.

For logging each message, what seems the most sense to me is to create a messages_channelname table for each channel, and in there store the message along with whatever other information is associated with it.

That elicits a problem, though – the name of the table is now dissociated from the channel's id (although I could alleviate this by making the table name messages_channelid).

I feel like the best practice here, rather than what makes the most sense, would be to have a master messages table that contains the messages from all channels, with columns channel_id, id and message, where id would be the index of the message per channel. But then I'd have (unfounded) concerns about table size and possibly search speed.

What's the best way to go about this?

Best Answer

There isn't any definitive answer for this problem.

You've correctly identified some of the possible tradeoffs (single table=simpler/possibly slower/more contention?, multiple tables=more complicated/faster, maybe?/harder to use in other contexts?).

The general advice is to start with the more straight-forward single-table solution, then break them out if testing shows problems.

Most of the time you're going to get good performance on a single table as long as you have decent hardware and proper indexing design.

Depending on the specific platform, you might have other technical solutions like filtered indexed views and stuff like that.

Related Solutions

SQL Server – Stored Procedure to Return Dynamically Created Table Data

Based on comments from people in chat, I decided to change my script slightly to INSERT INTO a temp table instead of creating one long SQL statement to execute at the end. So in the end my stored procedure contains the following:

create table #SurveyData
(
    tableName varchar(50),
    columnName varchar(50),
    columnId int,
    rownum int
)

create table #results
(
    SurveyId int,
    InstanceId int,
    QuestionNumber int,
    Response varchar(1000)
)

-- insert the survey table structures for use
insert into #SurveyData (tableName, columnName, columnId, rownum)
select tables1.name, cols1.name, column_id, ROW_NUMBER() over(order by tables1.name, column_id)
from sys.all_columns cols1
inner join 
(
    SELECT *
    FROM sys.all_objects
    WHERE type = 'U' 
    AND upper(name) like 'LIBRARY%' 
) Tables1
    ON cols1.object_id = tables1.object_id
WHERE cols1.name Like 'Q_%'
ORDER BY tables1.name, column_id;


declare @sql varchar(max) = '';
declare @RowCount int = 1;
declare @TotalRecords int = (SELECT COUNT(*) FROM #SurveyData);

Declare @TableName varchar(50) = '';
Declare @ColumnName varchar(50) = '';

WHILE @RowCount <= @TotalRecords
    BEGIN

        SELECT @TableName = tableName, @ColumnName = columnName
        FROM #SurveyData
        WHERE @RowCount = rownum

        SET @sql = 'INSERT INTO #results ' +
                    ' SELECT s.SurveyId
                        , s.InstanceId
                        , CASE WHEN columnName = ''' +  @ColumnName + ''' THEN REPLACE(columnName, ''Q_'', '''') ELSE '''' END as QuestionNumber
                        , Cast(s.' + @ColumnName + ' as varchar(1000)) as ''Response''
                    FROM #SurveyData t 
                    INNER JOIN ' + @TableName + ' s' +
                    ' ON REPLACE(t.tableName, ''Library_'', '''') = s.SurveyID ' +
                    ' WHERE t.columnName = ''' + @ColumnName + ''''

        exec(@sql)

        SET @RowCount = @RowCount + 1       
    END

    SELECT SurveyId, InstanceId, QuestionNumber, Response
    FROM #results

drop table #SurveyData
drop table #results

See SQL Fiddle with the final script

Sql-server – Schema for multiple identical queue tables

_{Answer originally left in comments by the question author}

After reading up on partitioned tables, it seems that I could store multiple independent ring buffers in one since the partitions can be stored in separate filegroups, but I doubt I'm going to be able to sell the requirement for Enterprise Edition.

I am going to try for a poor man's partitioned table by creating pre-allocated queue slots in a single table using a clustered index on QueueId and SlotId. As long as my queues don't grow beyond the number of allocated slots, I should be able to treat them as consecutive ring buffers for multiple logical queues.

Best Answer

Related Solutions

SQL Server – Stored Procedure to Return Dynamically Created Table Data

Sql-server – Schema for multiple identical queue tables

Related Question