Can we alter an existing synonym

No, SQL Server does not support ALTER SYNONYM. A synonym is a simple metadata alias stored in system catalogs that points to another object. Since it has no schema binding, no dependencies, and no executable definition, Microsoft designed it to be replaced rather than altered. To change a synonym target, we must DROP and recreate it.

A synonym is just a metadata pointer (name mapping) and stored in system catalog with no schema binding and no dependency tracking. It’s NOT like a view or proc with definition logic.

So, SQL Server designers kept it simple Change = replace pointer.

Let’s see the demo

Creating a table and inserting few records.

CREATE TABLE dbo.Employees (     EmpID INT PRIMARY KEY,     EmpName VARCHAR(50) );  insert into Employees(EmpID,EmpName) values (1,'Bagesh'),(2,'Rajesh'),(3,'Mahesh'),(4,'Amit')

 Now creating synonym

CREATE SYNONYM dbo.EmpSyn FOR dbo.Employees;

Let’s alter this.

ALTER SYNONYM dbo.EmpSyn FOR dbo.Employees;

Getting an error.

Correct way is to drop first and create it again

DROP SYNONYM dbo.EmpSyn; CREATE SYNONYM dbo.EmpSyn FOR dbo.Employees;

Run it

Can synonyms be encrypted

No, SQL Server synonyms cannot be encrypted because they only store metadata references, not executable definitions. There is no WITH ENCRYPTION option available when creating a synonym. A synonym is just a metadata alias not executable code. There are no SQL body and no module definition so nothing to encrypt. Encryption is unsupported.

Let’s try to create

CREATE TABLE dbo.Products (     ProductID INT PRIMARY KEY,     ProductName VARCHAR(50) );  INSERT INTO dbo.Products VALUES (1,'Laptop'),(2,'Mouse');  CREATE SYNONYM dbo.ProductSyn1 FOR dbo.Products;

Normal synonyms created successfully.

Let’s create an encrypted synonym.

CREATE SYNONYM TestSyn WITH ENCRYPTION FOR dbo.Products;

So we cannot encrypt  synonyms.

Can a synonym reference another synonym

SQL Server does not allow synonym chaining, meaning a synonym cannot reference another synonym, to avoid recursive resolution, runtime complexity, and hidden dependency issues. Each synonym must point directly to a base object. A synonym cannot reference another synonym.

This is called synonym chaining, and SQL Server explicitly blocks it.

Let’s see the demo

Creating a table and inserting few data

CREATE TABLE dbo.Products (     ProductID INT PRIMARY KEY,     ProductName VARCHAR(50) );  INSERT INTO dbo.Products VALUES (1,'Laptop'),(2,'Mouse');

 Creating a synonym

CREATE SYNONYM dbo.ProductSyn1 FOR dbo.Products;

Creating another synonym using first one.

CREATE SYNONYM dbo.ProductSyn2 FOR dbo.ProductSyn1;

Synonyms created successfully.

Let’s run this

Ooo! Getting an error.

So we cannot referrer one synonym to another one.

Can a synonym reference an object in another database

This is an interview question. Can we refer synonym in another database or another server. Answer is yes. Only we cannot refer on Azure SQL DB. A synonym in SQL Server can reference objects in another database or even on a linked server by using three-part or four-part naming. The synonym simply stores the object name string and resolves it at runtime, with permissions enforced on the base object.

The 3-part or 4-part name as plain text

Ø  Database.Schema.Object

Ø  Server.Database.Schema.Object

See the demo

use Test_DB go CREATE TABLE dbo.Orders (     OrderID INT,     Amount  INT ); GO  insert into dbo.Orders(OrderID,Amount) values (1,20),(2,25),(3,35),(4,45)

Table created on the TEST_DB. Now we are creating synonym in another Database.

USE AdventureWorks2019; GO  CREATE SYNONYM syn_Orders FOR Test_DB.dbo.Orders; GO

Now see the data

User must have permission on the base object in other database. Synonym itself doesn’t grant access.

FileTables in SQL server

A FileTable is a special table built on top of FILESTREAM that Stores files & folders inside SQL Server But exposes them directly through Windows File System Meaning we can Insert files via SQL or drag-drop in Windows Explorer or SQL Server keeps them in sync automatically. It is a bridge between SQL Server relational engine and NTFS file system.

In other word we can say that FileTable is a SQL Server feature built on FILESTREAM that allows storing files and directories in a special table while exposing them directly through the Windows file system. Internally, SQL Server stores file metadata in relational format and the actual file content in NTFS outside the MDF file, ensuring transactional consistency between SQL operations and file system access. It supports folder hierarchy, native file operations like copy and delete, and can be queried using T-SQL like a normal table.

See the demo

Prerequisites for this table

Ø  Enable FILESTREAM

Ø  Restart the SQL Service.

Enable FILESTREAM

EXEC sp_configure 'filestream access level', 2; RECONFIGURE;

 Create database with FILESTREAM

CREATE DATABASE TestDB ON PRIMARY (     NAME = TestDB_Data,     FILENAME = 'D:\Data\TestDB.mdf' ), FILEGROUP FileStreamFG CONTAINS FILESTREAM (     NAME = TestDB_FS,     FILENAME = 'D:\Data\TestDB_FS' ) LOG ON (     NAME = TestDB_Log,     FILENAME = 'D:\Data\TestDB.ldf' ) WITH FILESTREAM (     NON_TRANSACTED_ACCESS = FULL,     DIRECTORY_NAME = 'Docs' );

Database created

Sometime after running the above setting and restarting the SQL server but still getting the below error

FILESTREAM feature is disabled when we are creating the database.

To enable this, go to the SQL server Configuration Manager and select the server instance. First we need to stop this server.

Select this server and go to the properties.

Select FILESTREAM and enable the check.

 

Click apply and then ok.

Then we are able to create the database.

Enable FileTable in DB

ALTER DATABASE FileTableDemo SET FILESTREAM ( NON_TRANSACTED_ACCESS = FULL );

 Create FileTable

CREATE TABLE MyFileTable AS FileTable WITH (     FileTable_Directory = 'Docs',     FileTable_Collate_Filename = database_default ); GO

Access from Windows

Open it \\localhost\MSSQLSERVER\Docs\Docs

Now we can

Ø  Drag files

Ø  Create folders

Ø  Delete files

SQL Server tracks everything.

We have opened this path and copying one file

Now running the below query.

Query files from SQL

SELECT     name,     file_type,     creation_time,     last_write_time,     file_stream.PathName() AS FilePath FROM MyFileTable;

Insert file via SQL

INSERT INTO MyFileTable (name, file_stream) SELECT     'SQL_Server_Version_stores.docx',     BulkColumn FROM OPENROWSET(     BULK 'D:\SQL_Server_Version_stores.docx',     SINGLE_BLOB ) AS FileData;

It is working on only windows. not working on UNIX or another Platform.

One row inserted successfully.

See the file in directory

We can see this information using this table

 We can store any kind of files like doc, txt,jpge, mp4 , audio or video files.

See in the table

 

Sparse Columns Can Use More Space compare to normal null column

Many of us know that Sparse columns always save space because NULL uses 0 bytes but we may wrong Sparse columns save space only when NULL density is high. If null density is low medium, then it uses more space compare to normal nullable column.

Sparse columns can use more space than normal NULL columns because every non-NULL sparse value carries a 4-byte overhead, which outweighs the ~2-byte cost of a normal NULL column when the NULL percentage is low.

If Sparse column is not null the it will store Data + 4 bytes overhead and if value is null then it will store zero. That 4-byte overhead is 2 bytes for column ID and 2 bytes for sparse vector metadata.

Best choice to use sparse column

NULL %	Sparse Result
< 40%	Wastes space
~50%	Break-even
> 60%	Saves space
> 90%	Massive savings

Let’s see the demo

Creating two table with same structure normal and with sparse column.

CREATE TABLE Sparse_Demo_Normal (     ID                                                               INT IDENTITY(1,1) not null PRIMARY KEY,               columnone                                   TINYINT null,               columntwo                                   SMALLINT null ,               columnthree                                INT        null,               columnfour                                   BIGINT null,               columnfive                                    DECIMAL(30,10) null ,               columnsix                                      MONEY null,               columnseven                               SMALLMONEY null,               columneight                                 char(10) null,               columnnine                                  varchar(max)null,               columnten                                    nvarchar(max)null,               columneleven               DATE null,               columntwelve               TIME null,               columnthirteen                           DATETIME null,               columnfourteen                          SMALLDATETIME null,               columnfifteen                DATETIME2 null,               columnxixteen              DATETIMEOFFSET null,               columnseventeen                       UNIQUEIDENTIFIER null,               columneighteen                          NVARCHAR(MAX) null,               columnninteen                            BIT null,               columntwenty               VARBINARY(MAX) null               ); CREATE TABLE Sparse_Demo ( ID                                                                    INT IDENTITY(1,1) not null PRIMARY KEY,               columnone                                   TINYINT SPARSE null,               columntwo                                   SMALLINT SPARSE null              ,               columnthree                                INT        SPARSE null,               columnfour                                   BIGINT SPARSE null,               columnfive                                    DECIMAL(30,10) SPARSE null ,               columnsix                                      MONEY SPARSE null,               columnseven                               SMALLMONEY SPARSE null,               columneight                                 char(10) SPARSE null,               columnnine                                  varchar(max)SPARSE null,               columnten                                    nvarchar(max)SPARSE null,               columneleven               DATE SPARSE null,               columntwelve               TIME SPARSE null,               columnthirteen                           DATETIME SPARSE null,               columnfourteen                          SMALLDATETIME SPARSE null,               columnfifteen                DATETIME2 SPARSE null,               columnxixteen              DATETIMEOFFSET SPARSE null,               columnseventeen                       UNIQUEIDENTIFIER SPARSE null,               columneighteen                          NVARCHAR(MAX) SPARSE null,               columnninteen                            BIT SPARSE null,               columntwenty               VARBINARY(MAX) SPARSE null    );   --inserting 1M records into this table declare @i int=1; while (@i<=100000) begin insert into Sparse_Demo_Normal (columnone,columntwo,columnthree,columnfour,columnfive,columnsix,columnseven,columneight,columnnine                            ,columnten,columneleven,columntwelve,columnthirteen,columnfourteen,columnfifteen,columnxixteen,columnseventeen              ,columneighteen,columnninteen,columntwenty        ) values (null,null,null,null,null,null,null,null,null,null,null,null,null,null,null, null,null,null,null,null) insert into Sparse_Demo(columnone,columntwo,columnthree,columnfour,columnfive,columnsix,columnseven,columneight,columnnine                             ,columnten,columneleven,columntwelve,columnthirteen,columnfourteen,columnfifteen,columnxixteen,columnseventeen              ,columneighteen,columnninteen,columntwenty        ) values (null,null,null,null,null,null,null,null,null,null,null,null,null,null,null, null,null,null,null,null); set @i=@i+1; end;   go

 

  Both table created and inserting 100K records in both table.

As of now all data null on both table. See the Spaces of both tables.

EXEC sp_spaceused 'Sparse_Demo_Normal'; EXEC sp_spaceused 'Sparse_Demo';

Here we are seeing the Massive savings of space.

Now we are updating 20% data with not null value and see the space.

declare @i int=1; while (@i<=20000) begin update Sparse_Demo_Normal set columnone  =1 ,columntwo=1 ,columnthree=1 ,columnfour=100 ,columnfive=10.10 ,columnsix=100.00 ,columnseven=5.50 ,columneight='Bagi' ,columnnine='Bagesh' ,columnten='Bagesh kumar Singh' ,columneleven=getdate() ,columntwelve=getdate() ,columnthirteen=getdate() ,columnfourteen=getdate() ,columnfifteen=getdate() ,columnxixteen=getdate() ,columnseventeen=NEWID() ,columneighteen='Singh' ,columnninteen=1 where id=@i ; update Sparse_Demo set columnone  =1 ,columntwo=1 ,columnthree=1 ,columnfour=100 ,columnfive=10.10 ,columnsix=100.00 ,columnseven=5.50 ,columneight='Bagi' ,columnnine='Bagesh' ,columnten='Bagesh kumar Singh' ,columneleven=getdate() ,columntwelve=getdate() ,columnthirteen=getdate() ,columnfourteen=getdate() ,columnfifteen=getdate() ,columnxixteen=getdate() ,columnseventeen=NEWID() ,columneighteen='Singh' ,columnninteen=1 where id=@i   set @i=@i+1;   set @i=@i+1; end;   go

Now see the space for both table

Good!

Now updating 40% records.

Still ok.

Now we will update 60% data in both tables.

Upto 60% its look good. Now updating more than 90% records.

See the deference. We have taken the small sample size due to that we are not seeing much effect but if the data is big then we can easily we can see.

Let’s update 100% records and see.

Size is increase.