Language-Agnostic Embedding-Based Cross-Lingual Retrieval in SQL Server

Full report

On this page

Executive Summary: We design a normalized SQL Server schema and UI to support cross-lingual concept search. The data model separates documents, sentences, translations, and concepts (see ER diagram below) so each piece of text can be indexed by language and linked to a concept. We store embeddings either in SQL Server’s new VECTOR column (SQL 2025+) or an external vector DB (Faiss/Pinecone). Querying is done via a hybrid of full-text (BM25) and vector search, with results merged by Reciprocal Rank Fusion【74†L135-L143】【74†L168-L177】. The UI presents a search box with language selector, groups results by concept, and shows multilingual snippets and provenance. We plan robust ETL pipelines (staging tables, translation, canonicalization) and UPSERT transactions to ingest data. Performance scales via partitioning and caching, with backups, monitoring, and security (TDE encryption, GDPR flags) in place. SQL best practices (indexes, MERGE statements, parameterization) are used throughout. In the sections below, we detail schema design, indexing, embedding storage, API/UI, and operational procedures, with concrete SQL DDL, queries, and flow diagrams.

Data Model & Schema Design

We use a normalized schema with separate tables for content, translations, concepts, and metadata. Core tables include:

• Documents: One row per source document/page. Key fields: DocumentID (PK), SourceID (FK), Title, LicenseFlag, PII_Flag, etc.
• Sentences: Each sentence (or paragraph) from a document. Fields: SentenceID (PK), DocumentID (FK), Text, LanguageID (FK to Languages), position/order.
• Translations: Stores translated versions of sentences. Fields: TranslationID (PK), SentenceID (FK), LanguageID, TranslatedText. (This follows the “subordinate table for translations” best practice【72†L519-L528】.)
• Concepts: Canonical concepts or entities. Fields: ConceptID (PK), Name, Description, etc.
• SentenceConcept: Many-to-many linking sentences to concepts. Fields: SentenceID (FK), ConceptID (FK).
• Embeddings: Stores vectors. Fields: EmbeddingID (PK), EntityType (e.g. 'Sentence' or 'Concept'), EntityID (references SentenceID or ConceptID), ModelVersion, Vector (SQL VECTOR(n) column).
• Sources: Metadata about data origin (e.g. seed site). Fields: SourceID (PK), Name, URLBase, etc.
• Languages: List of supported languages (e.g. en, fr).
• Users/Annotations: User accounts and annotation/feedback links. E.g. Annotations(AnnotationID, UserID, SentenceID, Comment, Rating).

A conceptual ER diagram (normalized) is shown below. Foreign keys link related tables (e.g. Sentences.DocumentID -> Documents.DocumentID, Embeddings.EntityID -> SentenceID/ConceptID). Text fields use Unicode (NVARCHAR or NTEXT) with language tags on each sentence/translation.

erDiagram
    DOCUMENTS {
      int DocumentID PK
      int SourceID FK
      varchar(200) Title
      bit LicenseFlag
      bit PII_Flag
    }
    SENTENCES {
      int SentenceID PK
      int DocumentID FK
      nvarchar(4000) Text
      int LanguageID FK
      int Position
    }
    TRANSLATIONS {
      int TranslationID PK
      int SentenceID FK
      int LanguageID FK
      nvarchar(4000) TranslatedText
    }
    CONCEPTS {
      int ConceptID PK
      varchar(100) Name
      nvarchar(1000) Description
    }
    SENTENCE_CONCEPT {
      int SentenceID FK
      int ConceptID FK
    }
    EMBEDDINGS {
      int EmbeddingID PK
      varchar(10) EntityType  /* 'Sentence' or 'Concept' */
      int EntityID           /* FK to SentenceID or ConceptID */
      varchar(50) ModelVersion
      vector(1536) EmbeddingVector
    }
    SOURCES {
      int SourceID PK
      varchar(100) Name
      varchar(200) BaseURL
    }
    LANGUAGES {
      int LanguageID PK
      char(5) Code
      varchar(50) Name
    }
    USERS {
      int UserID PK
      varchar(50) UserName
      varchar(100) Email
      /* ... */
    }
    ANNOTATIONS {
      int AnnotationID PK
      int UserID FK
      int SentenceID FK
      nvarchar(2000) Comment
      int Rating
    }

    DOCUMENTS ||--o{ SENTENCES : contains
    SENTENCES ||--o{ TRANSLATIONS : translates
    SENTENCES }o--o{ SENTENCE_CONCEPT : pertains_to
    CONCEPTS ||--o{ SENTENCE_CONCEPT : identified_by
    SENTENCES ||--o{ EMBEDDINGS : generates
    CONCEPTS ||--o{ EMBEDDINGS : generates
    SOURCES ||--o{ DOCUMENTS : provides
    LANGUAGES ||--o{ SENTENCES : written_in
    LANGUAGES ||--o{ TRANSLATIONS : written_in
    USERS ||--o{ ANNOTATIONS : annotates
    SENTENCES ||--o{ ANNOTATIONS : has_annotation

This design isolates multilingual text in child tables. For example, Sentences holds the original text per language; Translations holds alternate languages for each sentence【72†L519-L528】. This preserves normalization and allows adding languages without schema changes【72†L519-L528】. Concept/entity extraction populates Concepts and links through SentenceConcept.

Schema Design Trade-offs: Keeping one table per language (not used here) would break normalization. Using JSON/XML fields (another pattern) is possible but complicates full-text search and indexes【72†L337-L346】. The chosen subordinate translation tables minimize redundancy and ease querying by language (SQL 2025 supports OPENJSON() if needed【72†L337-L346】). Key columns (PK, FKs) should be indexed (clustered PK on IDs) and foreign keys enforced.

SQL DDL Examples: (Core tables simplified)

-- Documents table
CREATE TABLE Documents (
  DocumentID INT IDENTITY PRIMARY KEY,
  SourceID    INT NOT NULL REFERENCES Sources(SourceID),
  Title       NVARCHAR(200),
  LicenseFlag BIT DEFAULT 0,
  PII_Flag    BIT DEFAULT 0
);

-- Sentences table
CREATE TABLE Sentences (
  SentenceID  INT IDENTITY PRIMARY KEY,
  DocumentID  INT NOT NULL REFERENCES Documents(DocumentID),
  Text        NVARCHAR(4000) NOT NULL,
  LanguageID  INT NOT NULL REFERENCES Languages(LanguageID),
  Position    INT
);

-- Translations table (each sentence-language pair)
CREATE TABLE Translations (
  TranslationID INT IDENTITY PRIMARY KEY,
  SentenceID    INT NOT NULL REFERENCES Sentences(SentenceID),
  LanguageID    INT NOT NULL REFERENCES Languages(LanguageID),
  TranslatedText NVARCHAR(4000) NOT NULL
);

-- Concepts table
CREATE TABLE Concepts (
  ConceptID    INT IDENTITY PRIMARY KEY,
  Name         VARCHAR(100) NOT NULL,
  Description  NVARCHAR(1000)
);

-- SentenceConcept link table
CREATE TABLE SentenceConcept (
  SentenceID INT NOT NULL REFERENCES Sentences(SentenceID),
  ConceptID  INT NOT NULL REFERENCES Concepts(ConceptID),
  CONSTRAINT PK_SentenceConcept PRIMARY KEY (SentenceID, ConceptID)
);

-- Embeddings table
CREATE TABLE Embeddings (
  EmbeddingID     INT IDENTITY PRIMARY KEY,
  EntityType      VARCHAR(10) NOT NULL,  -- e.g. 'Sentence' or 'Concept'
  EntityID        INT NOT NULL,
  ModelVersion    VARCHAR(50),
  EmbeddingVector vector(1536)           -- SQL Server VECTOR type (1536 floats)
);
-- We may add a filtered FK or enforce EntityID via application logic.

-- Sources, Languages, Users, Annotations omitted for brevity

Each DDL example would be completed with proper constraints and indexes in practice. (For example, create a FULLTEXT index on Sentences(Text) for BM25 search; see below.)

Embedding Storage & Indexing Strategy

We must store high-dimensional embeddings (e.g. 1024–1536 dimensions). SQL Server 2025 introduces a built-in VECTOR type, which stores float arrays efficiently【69†L92-L100】. Thus, one can store sentence and concept vectors in the Embeddings.EmbeddingVector column as shown above. This enables native vector search via VECTOR_DISTANCE() (exact kNN)【69†L92-L100】【69†L150-L159】 and (in preview) approximate search with CREATE VECTOR INDEX【70†L11-L19】.

Vector vs. External DB: Storing vectors in SQL centralizes data and allows hybrid queries in one engine【74†L135-L143】. However, external vector DBs (Faiss, Qdrant, Pinecone) offer specialized indexes and scaling. A common pattern is to keep vectors in both systems: SQL for relational queries and a separate vector index for fast ANN. We plan to support both: use SQL’s vector type for simple cases, and integrate an external ANN DB for large-scale or older SQL Server versions.

Quantization & Dimensionality: For model-agnostic retrieval, we keep full precision vectors. If scale is huge, consider columnstore or vector index compression (Faiss can do PQ). Embedding dimension depends on the chosen model (e.g. LaBSE=768 or 1024, OpenAI-ada2=1536). This design uses 1536 as an example. Store the ModelVersion with each vector for version control.

Indexing: In SQL, create a CREATE INDEX (vector) on Embeddings.EmbeddingVector for approximate ANN (if SQL 2025). For full-text, create a full-text catalog/index on Sentences(Text) (see Microsoft docs) to support BM25 retrieval. Ensure PII_Flag is indexed if needed for filtering.

Trade-offs: Native vector support (e.g. [69]) is convenient but may not scale well to hundreds of millions of vectors. External stores handle that scale but require data sync. We will implement a sync process (see later). In-cloud scenarios, Azure SQL Managed Instance also supports VECTOR type.

SQL DDL (Index examples):

-- Full-Text index on Sentences.Text for BM25 keyword search
CREATE FULLTEXT CATALOG ftCatalog AS DEFAULT;
CREATE FULLTEXT INDEX ON Sentences(Text)
   KEY INDEX PK_Sentences ON ftCatalog;

-- (Preview) Create approximate vector index on embeddings (SQL Server 2025+)
-- This is syntax in preview as of 2025:
CREATE VECTOR INDEX idx_embeddings_vector
ON Embeddings(EmbeddingVector)
WITH (DIMENSION = 1536, STORAGE = ANN);

Query & Retrieval Patterns

We implement hybrid search: full-text + vector. For an input query, we do:

1. Full-Text Search: Use FREETEXTTABLE or CONTAINSTABLE on Sentences(Text) to get top-K sentence IDs and BM25 rank.
2. Vector Search: Generate query embedding (via an external AI model or SQL AI_GENERATE_EMBEDDINGS() function). Then use VECTOR_DISTANCE('cosine', queryVec, EmbeddingVector) to find nearest sentences (top-K). (Example in [69†L150-L159].)
3. Merge Results (RRF): Combine ranked lists via Reciprocal Rank Fusion (RRF) in SQL【74†L206-L214】. This gives higher weight to items that rank high in either method【74†L206-L214】.

A simplified hybrid query (pseudocode) is:

-- Example hybrid search with RRF (k=10)
DECLARE @k INT = 10;
DECLARE @query NVARCHAR(200) = N'neural networks in AI';
DECLARE @lang CHAR(2) = 'en';

-- 1. Full-text search
SELECT TOP(@k)
    s.SentenceID,
    1.0 / (ROW_NUMBER() OVER (ORDER BY ft.RANK) + 60.0) AS score_ft
INTO #FT
FROM Sentences s
INNER JOIN FREETEXTTABLE(Sentences, Text, @query) AS ft
    ON s.SentenceID = ft.[KEY]
WHERE s.LanguageID = (SELECT LanguageID FROM Languages WHERE Code = @lang);

-- 2. Vector search
DECLARE @qv VECTOR(1536) = AI_GENERATE_EMBEDDINGS(@query USE MODEL Ada2Embeddings);
SELECT TOP(@k)
    SentenceID,
    1.0 / (ROW_NUMBER() OVER (ORDER BY cos.distance) + 60.0) AS score_vec
INTO #NN
FROM (
    SELECT TOP(@k) s.SentenceID,
           VECTOR_DISTANCE('cosine', @qv, e.EmbeddingVector) AS dist
    FROM Embeddings e
    JOIN Sentences s ON e.EntityType='Sentence' AND e.EntityID = s.SentenceID
    WHERE s.LanguageID = (SELECT LanguageID FROM Languages WHERE Code = @lang)
    ORDER BY dist
) AS cos;

-- 3. RRF merge
SELECT TOP(@k)
    COALESCE(nn.SentenceID, ft.SentenceID) AS SentenceID,
    COALESCE(score_vec, 0) + COALESCE(score_ft, 0) AS rrf_score
FROM #NN nn
FULL OUTER JOIN #FT ft ON nn.SentenceID = ft.SentenceID
ORDER BY rrf_score DESC;

This example adapts the one from [74] to our schema. Results are then joined back to retrieve Sentence.Text, Concepts, and Document info for display. We add pagination via OFFSET/FETCH as needed.

Common Queries:

• Ingest/Upsert: Use MERGE to insert or update Documents/Sentences. Example:

  MERGE INTO Documents AS T
  USING (VALUES(@id, @src, @title)) AS S(DocumentID, SourceID, Title)
    ON T.DocumentID = S.DocumentID
  WHEN MATCHED THEN
    UPDATE SET Title = S.Title
  WHEN NOT MATCHED THEN
    INSERT (DocumentID, SourceID, Title) VALUES (S.DocumentID, S.SourceID, S.Title);

• Hybrid search: as above.
• Fetch concept cluster: Query sentences by concept and language:

  SELECT s.Text, s.LanguageID
  FROM SentenceConcept sc
  JOIN Sentences s ON sc.SentenceID = s.SentenceID
  WHERE sc.ConceptID = @conceptID AND s.LanguageID = @userLang;

• Pagination: use OFFSET ... FETCH NEXT ....

Indexes: Ensure indexes on Sentences(LanguageID), Embeddings(EntityType,EntityID), and full-text as shown. For SentenceConcept, the primary key covers lookups by sentence or concept.

Transactions & Consistency

Ingestion and updates should use transactions to maintain consistency. We recommend MERGE statements inside explicit BEGIN TRAN/COMMIT blocks when inserting or updating text and embeddings. Use unique keys (e.g. unique on URL or a business key) to dedupe. For concurrent inserts, consider setting READ COMMITTED SNAPSHOT isolation to avoid blocking.

• Upserts: The MERGE example above shows an atomic UPSERT. Ensure proper indexing to avoid race conditions. Use error handling (TRY/CATCH) for merge failures.
• Deduplication: Place UNIQUE constraints where logical (e.g. on original text content, or source-URL) to prevent duplicates. Remove duplicates via preprocessing if needed.
• Consistency Model: We tolerate eventual consistency for embedding indexes. For example, when new documents are added, their embeddings can be populated asynchronously: first insert text, then compute/store embedding in a subsequent transaction. The search index can lag slightly. For user annotations and translations, use strong consistency (all writes inside a transaction).

Trade-offs: Synchronous insertion of embeddings is slow; async batching can speed ingestion. But this means a brief window where a new sentence has no embedding for search. Mitigation: background job to backfill.

ETL & Ingestion Pipeline

We use an ETL pipeline to load data from the seed sites and other sources:

1. Staging Tables: Create staging tables (StageDocuments, StageSentences) mirroring core tables. Bulk-load raw data (CSV/JSON) or use tools (SQLBulkCopy, SSIS) into staging.
2. Language Detection: If needed, detect language of each sentence (could use Azure ML or CLD library) and set LanguageID.
3. Translation & Canonicalization: For each new sentence in the pipeline:

• If LanguageID ≠ target languages, call machine translation API (Azure OpenAI, AWS Translate) and insert into Translations.
• Extract entities/concepts (via NLP or list of keywords) and map to Concepts. Insert new concepts or reuse existing.

1. Batch Merges: Periodically MERGE data from staging to main tables in batches. Example:

   MERGE INTO Sentences AS T
   USING StageSentences AS S
     ON T.SourceID = S.SourceID AND T.Position = S.Position
   WHEN NOT MATCHED THEN
     INSERT (DocumentID, Text, LanguageID, Position) VALUES (S.DocumentID, S.Text, S.LanguageID, S.Position);

1. Embedding Generation: After inserting sentences, a background job generates embeddings (e.g. calling an external ML service or AI_GENERATE_EMBEDDINGS) and inserts into Embeddings. If using external DB, run a sync script to load new vectors.
2. Error Handling: Log any pipeline errors to an ETLLog table, skip invalid records, and send alerts. Use transactions so that partial failures don’t corrupt data.

Required Resources: SQL Server Agent jobs or external scheduler (e.g. Azure Data Factory) to orchestrate. Azure OpenAI or ML services for translation/embedding. Adequate CPU/Memory for batch operations. For large volumes, SSIS or bulk loading (BCP) can help.

UI/UX Design

The UI presents a multilingual search interface and grouped results:

• Search Box & Language Selector: At the top, a text input for the query and a dropdown of target language (e.g. “English (en)”, “Español (es)”, etc.). User enters query in their language of choice.
• Result Grouping by Concept: Results are grouped by concept/entity. Each group header shows the concept name (as a link) and possibly a definition. Under each, list matching documents or sentences. For example:

Quantum Entanglement: (Concept) “Group of protons in a state such that their spin states cannot be described independently...”

– Document: Physics Today, “Quantum Loopholes”, snippet ...

– Document: Articles, “Basic Physics”, snippet ...

• Snippet Generation: Display a snippet of the sentence with highlighted query terms (from full-text search). If translated, show snippet in user’s language.
• Provenance Display: For each result, show source info (document title, source site, and link) and confidence score. If result is a translation, indicate original language.
• Disambiguation UI: If a query matches multiple concepts, show a disambiguation list (e.g. “Did you mean Concept A or B?”) at top.
• Feedback/Annotation Tools: Provide buttons (thumbs-up/down) so users can mark relevance or incorrect results. Also an admin interface to edit/correct concept assignments.
• Wireframe Example: (Descriptive) A search bar at top with a language dropdown. Below, a filter sidebar for languages or sources (optional). Main pane shows concept groups: each concept header is clickable. Under it, result rows with title, snippet, and source. At bottom, pagination controls.

flowchart TD
    A[User enters query + selects language] --> B[Backend embeds query]
    B --> C{Perform Searches}
    C --> |Full-text| D[SQL FTS]
    C --> |Vector| E[Vector Search]
    D --> F[RRF rank results]
    E --> F
    F --> G[Group by Concept & Rank]
    G --> H[Display results on UI]
    H --> I[User clicks result or refines query]

This flowchart illustrates: query → embeddings → parallel BM25 and vector searches → RRF merging → grouping → UI display.

API Design

We expose a JSON-based REST API:

• POST /search: Accepts { "query": "...", "language": "en", "topK": 10 }. Returns ranked results: list of { conceptID, conceptName, score, snippet, documentID, sourceURL, language }. Supports pagination (params page/pageSize).
• GET /concepts/{id}: Returns concept details and related info.
• GET /documents/{id}: Returns full document/sentence content and metadata.
• POST /feedback: {userID, sentenceID, relevanceScore} to log user feedback.
• Authentication: Use JWT tokens or API keys. Rate-limiting (e.g. 100 req/min per user) to prevent abuse.
• Payload Example (Search Response):

  {
    "results": [
      {
        "conceptID": 42,
        "conceptName": "Quantum Entanglement",
        "score": 0.87,
        "snippet": "Protons were put in an entangled state where their spin...",
        "documentID": 17,
        "source": "protocol5",
        "language": "en"
      },
      ...
    ]
  }

• Trade-offs: A REST API is simple; GraphQL could allow flexible queries but is more complex to implement. We assume internal users, so strict auth is needed only if exposed externally.

Performance & Scaling

• Partitioning/Sharding: For very large data, partition tables by language or by date/source (e.g. PARTITION BY YEAR(DocumentDate) or LanguageID) to improve query locality.
• Indexes: Use proper indexes: clustered PKs, nonclustered on foreign keys, full-text, and on Sentence(LanguageID). For concept filtering, index SentenceConcept(ConceptID).
• Caching: Use Redis or in-memory caches for frequent queries (especially for concept metadata).
• Connection Pooling: Ensure the application pool or ORM uses pooled connections to SQL Server.
• Hardware: Run on a beefy SQL instance (plenty of RAM for vector search). If on Azure, use Managed Instance with Gen5v3 or better.
• Monitoring: Track query performance (Query Store, DMVs). Set alerts for slow queries or high CPU. Scale out reads with replicas if needed (always read-only for search).
• Failover: Consider AlwaysOn Availability Groups for high availability.

Risks: Vector search is CPU-intensive; monitor and add resources as needed. Full-text indexing requires maintenance (see below).

Backup, Restore & Migrations

• Backups: Perform regular full/differential backups of the SQL database (with BACKUP DATABASE). For Azure SQL, rely on automated backups. Ensure backups include all tables and indexes (they do). For the embedding models and vector store, keep versioned checkpoints (if local).
• Restores: Have scripts to restore to a point-in-time. Test restores quarterly.
• Migrations: Use SQL Server Data Tools (SSDT) or scripts for schema changes. If adding a language or new concept attributes, apply migrations safely with zero-downtime if possible (use ALTER TABLE with ONLINE=ON for large tables).
• Embedding Model Changes: If embedding model is updated (new dimension or version), plan a re-index: add a new ModelVersion, create new vector rows, then retire old.

Security & Privacy

• Encryption: Enable Transparent Data Encryption (TDE) for data-at-rest. Use TLS for client connections (SQL Server supports encrypted endpoints). If using Azure, enable Always Encrypted for highly sensitive columns (e.g. user email).
• PII Handling: Use the PII_Flag column to mark records containing PII. For flagged content, either exclude from embeddings/indexing or store hashed. Comply with GDPR: support DELETE user data by cascade-deleting annotations/feedback for that user; remove or anonymize any user-generated text on request.
• Authentication/Authorization: Use Windows Authentication or Azure AD for DB access. The API should authenticate users (token-based) and authorize only allowed actions (e.g. only admins can modify schema or see raw data).
• Risk Mitigation: Regularly run security scans (e.g. SQL vulnerability assessment). Log and audit schema changes and admin logins.

Operations & Monitoring

• Observability: Monitor key metrics:
• Query latency (use Extended Events or DMVs).
• ETL success/failure (log table and alerts).
• Search result counts (to catch zero-result anomalies).
• System resources (CPU, memory, disk I/O).
• Maintenance Tasks:
• Reindexing: Rebuild SQL indexes (including full-text) nightly or weekly depending on churn.
• Statistics: Update statistics after large loads for query optimizer.
• Vector Index Rebuild: If using external vector DB, schedule a rebuild or refresh (e.g. at low-traffic times). For SQL vector, recreate the index if updated.
• Data Cleanup: Archive or purge old logs/records periodically.
• Alerts: Set alerts for job failures, deadlocks, or slow queries. Use SQL Server Agent jobs to send email/Teams notifications.
• Sample Monitoring Query: Check recent long-running searches:

  SELECT TOP(10)
    qs.total_elapsed_time/qs.execution_count AS avg_duration_ms,
    qs.execution_count,
    qs.last_execution_time,
    SUBSTRING(st.text, qs.statement_start_offset/2, 50) AS QueryText
  FROM sys.dm_exec_query_stats qs
  CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
  WHERE st.text LIKE '%VECTOR_DISTANCE%' OR st.text LIKE '%FREETEXTTABLE%'
  ORDER BY avg_duration_ms DESC;

By following this design, we achieve a robust, language-agnostic search system. We leverage SQL Server’s vector features【69†L92-L100】 and full-text engine, ensure normalization【72†L519-L528】, and adopt hybrid search with RRF【74†L206-L214】. The UI and API provide a user-friendly multilingual experience, while ETL and monitoring ensure data quality and performance.

Sources: We used Microsoft documentation for vector search and hybrid search patterns【69†L92-L100】【74†L135-L143】【74†L206-L214】, and database design best practices for multilingual content【72†L519-L528】, to guide our schema and query choices.