2

Using: SQL Server 2016+

Oracle supports both public and private synonyms. I.e. you can configure a synonym to be available to all connections or to the current connection only - like a temp table.

I know that SQL server does not support private synonyms, but I'm looking for the most efficient alternatives.

As example of data structure:

CREATE TABLE [dbo].[Session1] ([ID] [UNIQUEIDENTIFIER] NOT NULL, [Val1] [INT] NULL, [Val2] [INT] NULL);
GO

CREATE TABLE [dbo].[Session2] ([ID] [UNIQUEIDENTIFIER] NOT NULL, [Val1] [INT] NULL, [Val2] [INT] NULL);
GO

CREATE TABLE [dbo].[Session3] ([ID] [UNIQUEIDENTIFIER] NOT NULL, [Val1] [INT] NULL, [Val2] [INT] NULL);
GO

CREATE SYNONYM MySession FOR dbo.Session1;

NB. This is an example only, the tables hold many rows and we have some clients with 1000's of simultaneous sessions.

I could now access the Session1 table as MySession but unfortunately so would every other connection.

DECLARE @SessionID NVARCHAR(255)
,       @strSQL    NVARCHAR(MAX);

SET @SessionID = N'1';
SET @strSQL = 'SELECT * FROM dbo.Session' + @SessionID;

EXEC sp_executesql @stmt = @strSQL;

The Dynamic SQL is probably the most efficient but from past experience I can say that it does require more effort to both code and support.

We have also tested partitioned views and tables, both of which have drawbacks.

Working under the assumption that I am not the first person to have this style of data structure, I'm looking for some advice on additional approaches that I may not have considered. Are there any other options available?

Matthew Baker
  • 2,637
  • 4
  • 24
  • 49
  • 1
    You are not the first. Search for "multi-tenant database design". All solutions have pros and cons. See https://stackoverflow.com/questions/2213006/how-to-create-a-multi-tenant-database-with-shared-table-structures. – Dan Guzman Apr 03 '19 at 11:34
  • Hi Dan. That's a good read and does address the overall design options. We actually have a multi-schema design (although my example doesn't show this). From my end I'm actually looking more at the direct access side of this. How to best retrieve the data. +1 for the advice though. – Matthew Baker Apr 03 '19 at 12:31

0 Answers0