If you work in an environment where your SQL Server database procedures need to frequently reference data in other databases or on other servers, it can be difficult to implement development, test, and production environments while still maintaining a single code-base because you find you have to change your 4-part names in each Dev, Test and Production environment. One solution that can seriously help manage that situation is by employing SQL Server Synonyms within your code to point to the user-defined tables in the other environments/databases using the 4-part name of those tables, such as: ServerName.DatabaseName.SchemaName.TableName.
In the server named DevServerName1, you would reference DevServerName2 objects with synonyms such as:
CREATE SYNONYM dbo.syn_TableName FOR DevServerName2.DatabaseName2.dbo.TableName;
Then in TestSeverName1, you would redefine the same synonym name to point to the appropriate object in that environment:
CREATE SYNONYM dbo.syn_TableName FOR TestServerName2.DatabaseName2.dbo.TableName;
And the same pattern would apply in Production:
CREATE SYNONYM dbo.syn_TableName FOR ProductionServerName2.DatabaseName2.dbo.TableName;
The beauty of this technique is that you maintain the synonym definitions as a single script all in one place in each environment and use the constant synonym names in your stored procedures, views, and other objects so that you’re not having to redefine each 4-part name in each SQL object in each environment.
While you can reference synonyms that point to tables with INSERT, UPDATE, DELETE, UPSERT, and JOIN, you’ll find one limitation of synonyms is that you cannot TRUNCATE tables by their synonym name out of the box. This is partially because synonyms can reference more than just user-defined tables and the TRUNCATE TABLE syntax expects a table name, not a synonym name.
Truncating a table is much faster than deleting all the rows from the tables because of the logging involved in each
If you attempt to truncate a table via a valid synonym name for that table, you’ll see the following error where syn_MyTablePointer is your synonym name:
Msg 4708, Level 16, State 2, Line 21
Could not truncate object ‘dbo.syn_MyTablePointer’ because it is not a table.
EXAMPLE:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
CREATE TABLE dbo.TEMPTEST (ID INT) INSERT INTO dbo.TEMPTEST ( ID ) VALUES ( 0 ), ( 1 ), ( 2 ); CREATE SYNONYM dbo.syn_TEMPTEST FOR dbo.TEMPTEST; SELECT * FROM dbo.syn_TEMPTEST TRUNCATE TABLE dbo.syn_TEMPTEST –Results in an error |
RESULT:
Msg 4708, Level 16, State 2, Line 21
Could not truncate object ‘dbo.syn_TEMPTEST’ because it is not a table.
The following procedure will TRUNCATE a table from a SYNONYM name.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 |
CREATE PROCEDURE dbo.spr_TruncateTableBySynonymName ( @SchemaName AS sysname , @SynonymName AS sysname ) AS BEGIN –Procedure SET NOCOUNT ON; DECLARE @BaseObjectName AS NVARCHAR(1035) = NULL; DECLARE @SQL NVARCHAR(MAX); DECLARE @ErrorMessage NVARCHAR(255); SELECT TOP 1 @BaseObjectName = base_object_name FROM sys.synonyms INNER JOIN sys.schemas ON schemas.schema_id = synonyms.schema_id WHERE schemas.name = @SchemaName AND synonyms.name = @SynonymName AND type = ‘SN’ AND is_ms_shipped = 0; IF @BaseObjectName IS NOT NULL BEGIN SET @SQL = N‘TRUNCATE TABLE ‘ + @BaseObjectName; BEGIN TRY EXECUTE sys.sp_executesql @SQL; IF OBJECT_ID(‘tempdb..#RowCount’, ‘U’) IS NOT NULL DROP TABLE #RowCount; CREATE TABLE #RowCount ( ReturnValue INT ); SET @SQL = N‘INSERT INTO #RowCount SELECT Count(*) FROM ‘ + @SchemaName + ‘.’ + @SynonymName; EXECUTE sys.sp_executesql @SQL; DECLARE @RowCount INT = 0; SELECT TOP 1 @RowCount = ReturnValue FROM #RowCount; IF @RowCount > 0 BEGIN SET @ErrorMessage = ‘The base table for synonym ‘ + @SchemaName + ‘.’ + @SynonymName + ‘ was not truncated.’; RAISERROR(@ErrorMessage,16,1); END; END TRY BEGIN CATCH THROW; END CATCH; END; ELSE BEGIN SET @ErrorMessage = ‘The base table for synonym ‘ + @SchemaName + ‘.’ + @SynonymName + ‘ was not found.’; THROW 50000,@ErrorMessage,1; END; END; |
After defining that stored procedure, you can TRUNCATE a table via the synonym name using this code:
EXECUTE dbo.spr_TruncateTableBySynonymName @SchemaName = ‘dbo’, @SynonymName = ‘syn_TEMPTEST’
Here’s a simple test case you can use to show that the procedure works as expected:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
IF OBJECT_ID(‘dbo.syn_TEMPTEST’, ‘SN’) IS NOT NULL DROP SYNONYM dbo.syn_TEMPTEST IF OBJECT_ID(‘dbo.TEMPTEST’, ‘U’) IS NOT NULL DROP TABLE dbo.TEMPTEST CREATE TABLE dbo.TEMPTEST (ID INT) INSERT INTO dbo.TEMPTEST ( ID ) VALUES ( 0 ), ( 1 ), ( 2 ); CREATE SYNONYM dbo.syn_TEMPTEST FOR dbo.TEMPTEST; SELECT * FROM dbo.syn_TEMPTEST –Returns three rows. –TRUNCATE SYNONYM: EXECUTE dbo.spr_TruncateTableBySynonymName @SchemaName = ‘dbo’, @SynonymName = ‘syn_TEMPTEST’ SELECT * FROM dbo.syn_TEMPTEST –Returns zero rows. IF OBJECT_ID(‘dbo.syn_TEMPTEST’, ‘SN’) IS NOT NULL DROP SYNONYM dbo.syn_TEMPTEST IF OBJECT_ID(‘dbo.TEMPTEST’, ‘U’) IS NOT NULL DROP TABLE dbo.TEMPTEST |