AI in SQL Server vs PostgreSQL: Vector Search, Embeddings, and RAG Compared

Both SQL Server and PostgreSQL are moving fast into AI territory — and if you manage either (or both), you've probably wondered how they compare when it comes to storing embedding, running vector searches, and building RAG pipelines. The short answer: the capabilities are converging, but the approach is very different. Here's a practical breakdown.

1. The VECTOR data type: built-in vs extension

SQL Server 2025 ships with a native VECTOR data type, part of the core engine — no extra install needed. It stores embedding as optimized binary, surfaced as JSON arrays, with support for up to 1,998 dimensions. See the official type reference for the full syntax.

PostgreSQL takes the extension route via pgvector. You install it once and get a vector type that integrates natively with your existing tables. It's open source, available on RDS, Azure Database for PostgreSQL, Google Cloud SQL, and self-hosted instances.

-- SQL Server 2025
CREATE TABLE articles (
    id        INT PRIMARY KEY,
    title     NVARCHAR(500),
    embedding VECTOR(1536)
);
 
-- PostgreSQL + pgvector
CREATE EXTENSION IF NOT EXISTS vector;
 
CREATE TABLE articles (
    id        SERIAL PRIMARY KEY,
    title     TEXT,
    embedding VECTOR(1536)
);

2. Similarity search: VECTOR_DISTANCE vs the <=> operator

SQL Server 2025 uses the VECTOR_DISTANCE() function with explicit metric names ('cosine', 'euclidean', 'dot'). PostgreSQL with pgvector uses operator syntax: <=> for cosine, <-> for L2 (Euclidean), <#> for negative inner product. Both let you combine similarity with regular WHERE filters in the same query.

-- SQL Server 2025: top 5 similar articles
SELECT TOP 5
    title,
    VECTOR_DISTANCE('cosine', embedding, @query_vector) AS distance
FROM   articles
ORDER BY distance ASC;
 
-- PostgreSQL + pgvector: top 5 similar articles
SELECT
    title,
    embedding <=> $1 AS distance
FROM   articles
ORDER BY distance
LIMIT  5;

3. Approximate nearest neighbor (ANN) indexing

For large datasets, exact KNN scans get expensive. SQL Server 2025 introduces DiskANN-powered vector indexes via CREATE VECTOR INDEX (currently in preview, requires enabling PREVIEW_FEATURES). pgvector supports two ANN index types: ivfflat (IVF with flat quantization) and hnsw (Hierarchical Navigable Small Worlds). HNSW is generally preferred for query latency.

-- SQL Server 2025: DiskANN vector index (preview)
CREATE VECTOR INDEX idx_articles_embedding
    ON articles (embedding)
    WITH (METRIC = 'cosine');
 
-- PostgreSQL: HNSW index via pgvector
CREATE INDEX idx_articles_embedding
    ON articles
    USING hnsw (embedding vector_cosine_ops)
    WITH (m = 16, ef_construction = 64);

4. External model integration

SQL Server 2025 introduces EXTERNAL MODEL, which lets you register AI models (Azure OpenAI, OpenAI, Ollama, etc.) as first-class T-SQL objects and call them directly from queries. PostgreSQL doesn't have this built into the core engine — you typically generate embeddings outside the DB (Python, LangChain, application layer) and then insert the resulting vectors. Azure Database for PostgreSQL does offer an azure_ai extension that bridges this gap for Azure-hosted instances.

5. Which one fits your use case?

If your workload is already on SQL Server and you want everything — embeddings, vector search, and model calls — inside the engine with zero extra infrastructure, SQL Server 2025 is compelling. The DiskANN index is designed to scale to billions of vectors without offloading to a dedicated vector store.

If you're on PostgreSQL, pgvector gets you 90% of the way there with far less licensing cost and broader managed service support. HNSW indexes are mature and production-proven. The trade-off is that embedding generation stays outside the database — you own that pipeline.

Bottom line

Both platforms now let you store and query embeddings directly alongside relational data — which is the real win, because it eliminates the data synchronization headache of maintaining a separate vector store. SQL Server 2025 bets on deeper engine integration and managed model calls; PostgreSQL bets on ecosystem flexibility and open-source momentum. The syntax is surprisingly similar. Pick the one you're already running, and start small: create one table with a VECTOR column, generate embeddings for a real dataset, and measure your query latency with and without an ANN index. That benchmark will tell you more than any comparison article.

5 SQL Tricks Worth Remembering Before You Close the Laptop

Disclosure: this post may contain links to books as an affiliate link. If you purchase through it, this site may earn a small commission at no extra cost to you.

It is Friday. The sprint is done, the deploys are out, and nobody is scheduling a meeting in the next 30 minutes. This one is short — five SQL tricks that are genuinely useful, easy to forget, and satisfying to rediscover. No deep dives, no architecture decisions. Just good SQL to carry into next week.

1. Fill missing dates in a report with generate_series (PostgreSQL)

Your daily sales query skips dates with zero transactions. Instead of patching this in application code, generate a full date spine in the database and left join your data against it. The generate_series function handles this cleanly — no temp tables, no loops.

SELECT
    d.day::date,
    COALESCE(SUM(s.amount), 0) AS total_sales
FROM
    generate_series(
        '2025-05-01'::timestamp,
        '2025-05-31'::timestamp,
        '1 day'::interval
    ) AS d(day)
LEFT JOIN sales s
    ON s.sale_date = d.day::date
GROUP BY d.day
ORDER BY d.day;
  

2. Get the last day of any month without a calendar lookup (SQL Server)

Month-end reporting always needs the last day of the month, and it changes every month, and February exists. EOMONTH handles leap years, 30-day months, and everything else automatically. The optional second argument lets you offset forward or backward.

-- Last day of the current month
SELECT EOMONTH(GETDATE())  -- 2025-05-31
 
-- Last day of the previous month
SELECT EOMONTH(GETDATE(), -1) -- 2025-04-30
 
-- First day of the current month
SELECT DATEADD(DAY, 1, EOMONTH(GETDATE(), -1)) -- 2025-05-01
  

3. Use a CTE to avoid repeating yourself (PostgreSQL & SQL Server)

If you find yourself copy-pasting the same subquery twice in one statement, a Common Table Expression cleans it up immediately. CTEs are not just for readability — they also prevent you from calculating the same thing twice and getting different results if the underlying data changes mid-query.

WITH monthly_totals AS (
    SELECT
        DATE_TRUNC('month', order_date) AS month,
        SUM(amount)                       AS revenue
    FROM orders
    GROUP BY 1
)
SELECT
    month,
    revenue,
    ROUND(revenue * 100.0 / SUM(revenue) OVER (), 2) AS pct_of_total
FROM monthly_totals
ORDER BY month;
  

4. Spot duplicate rows instantly with GROUP BY and HAVING

Before running a deduplication job, it pays to know exactly which keys are duplicated and how many copies exist. This pattern works identically across PostgreSQL, SQL Server, MySQL, and Redshift — no library needed, just standard SQL.

SELECT
    customer_id,
    email,
    COUNT(*) AS occurrences
FROM customers
GROUP BY
    customer_id,
    email
HAVING COUNT(*) > 1
ORDER BY occurrences DESC;
  

Add more columns to the GROUP BY to tighten the definition of "duplicate" for your specific case. The result set tells you exactly what to target before touching any data.

5. LAG() to compare a row against the previous one (PostgreSQL & SQL Server)

Week-over-week, day-over-day, month-over-month — any time you need to compare a metric against the prior row, LAG() saves you a self-join. It is a window function available in PostgreSQL, SQL Server, MySQL 8+, and Redshift.

SELECT
    sale_date,
    daily_revenue,
    LAG(daily_revenue) OVER (ORDER BY sale_date)
        AS prev_day_revenue,
    ROUND(
        (daily_revenue - LAG(daily_revenue) OVER (ORDER BY sale_date))
        * 100.0
        / NULLIF(LAG(daily_revenue) OVER (ORDER BY sale_date), 0),
    2) AS pct_change
FROM daily_sales
ORDER BY sale_date;
  

The NULLIF(..., 0) wrapper on the denominator prevents a division-by-zero error on days where the previous revenue was zero — a small detail that saves a lot of Friday-afternoon debugging.

Wrapping Up

None of these are exotic — generate_series, EOMONTH, CTEs, HAVING, and LAG() are bread-and-butter SQL. They just tend to get forgotten under deadline pressure and rediscovered on a quiet Friday. Keep them close. The one worth memorizing cold: NULLIF on every division denominator. Division by zero is always the error you find in production.

If this kind of thing clicks for you and you want more of it — the patterns, the thinking behind the queries, the instinct for when to reach for a window function versus a subquery — Practical SQL, 2nd Edition by Anthony DeBarros is one of the best books on the subject. It is written for people who want to actually understand their data, not just run queries.