sql server – Read-Scale Availability Group between a Transactional subscriber and another machine

I am trying to configure Read-Scale replica between a transactional subscriber and a 3rd machine. I could create the group. But, when i attempted to add the DB (which is a transactional – subscriber) to be part of my read scale replica group, I am getting the following message and not able to complete my read scale replica. My replication is already configured and working fine. Configured the AG as well. All i am doing is, trying to add the DB to the AG.

Here is the message:
Special considerations exist when adding a replication Subscriber database to an availability group. For more information, see “Replication and Availability Groups” in SQL Server Books Online.

But the BOL is not saying anything about AG on a subscriber. Any help on this would be highly appreciated.

Does SQL Server upgrade to table locks at total locks or session locks?

I’ve a table that both has mass sequential inserts at the end of the CI and random (very distributed) reads+updates. Naturally, the mass inserts should not block the random access. RCSI is used, so the read-only queries shouldn’t affect the lock count (?) in relation to the sequential insert.

My concern is that, even when limiting the maximum number of locks taken during the insert (eg. inserting in batches), it is possible for one (or more) of the OLTP updates to bypass this limit. If the lock count heuristic is per-session then it is less of potential issue.

Given the answer to the question in the title, then, what is the “best” way to prevent table lock escalation here?

My current approach/thought is to select a row count (eg. arbitrary of 1-4k) during the mass inserts to allow “some slack”, although this feels overall imprecise. While batches are essential anyway to deal with replication and such, it would be nice to specify a batch size of 5k rows and move on. (To be fair, quick table locks aren’t really the issue: the intent of the question is more about finding the edge such that table lock escalation doesn’t happen.)

There has been DBA pushback on both 1) disabling row locks (to ensure page locks and thus reduce lock courts) and 2) disabling table lock escalation (with forced page locks to minimize worst-case). Are there any other relevant database properties to consider with respect to lock escalation? (Increasing the lock limit to say, 10k would then allow a
much larger “slack” batch size.)

database design – Best practices when designing SQL DB with “redundant” tables

I have a design dilemma for a DB I’m creating for an e-commerce platform I want to develop.

I have 3 different actors interacting with the website:

  • Customer
  • Manager
  • Supplier

Those 3 actors have the same table structure: email, username, address…

My initial design for the DB was to create a single table (StoreUser), with an additional field to distinguish between the 3 different types of actors.

The issue I see with this design is that when referencing a Customer in the “Order” table for instance, to assign an Order to a Customer, it would be technically possible to assign a “Manager” or a “Supplier” to the order, even though it isn’t wanted. Same thing for the “Product” table, to which a Supplier’s foreign key should be provided, the “StoreUser” FK would not distinguish between the 3 actors.

On the other hand, creating 3 tables, containing the exact same datafields, seems really redundant, especially from the code perspective (I’m using Django for the website, and I really don’t like the idea of having 3 different classes with the same structure.

Which one seems the most logical to you? What’s the good practice here?

sql server – Check if contract conflict with another contract query

I have a database with these tables as the bellow picture showed:

enter image description here

The user can create a new contract each contract may have one or more shops and these shops status changes depending on the contracts, everything works perfectly until now.
my problem in renewing the contract, I have a feature that allows the user to renew a contract, in this case, I get all the original contract info and make the new contract start from the end date of the original contract + 1 day with the same original contract info.

my question here is how to check if this renewal contract does not conflict with other contracts for the same customer and the same shops (customer can have many contracts)

E.G.:

I have contracts like this:

con_id    shop_id   start_date    end_date
--------------------------------------------
  1         1       14-04-2021    14-04-2022
  2         1       15-04-2022    15-04-2023

If the user clicked on contract number 1 and tried to renew it, I want a query to check if the new contract start date does not conflict with another contract for this user and these shops.

Like in my above data I want to prevent the user from renewing contract number 1 again because there is already a contract renewed before in that period.

This is what I tried:

--IF EXISTS (SELECT * FROM contracts_view where cust_id=123456789 and @date_start >= date_start and @date_start <= date_end and shop_id in (select shop_id from contracts_shops where contract_id =@old_contract_id)) 
--BEGIN
--    SELECT @ErrorMessage = ERROR_MESSAGE()
--  RAISERROR ('asdasd', 10, 1)
--  ROLLBACK TRAN
--   return
--END

and here is my stored procedure for renewing a contract:

ALTER PROCEDURE (dbo).(contract_renew)
-- Add the parameters for the stored procedure here
@cust_id int,
@duration int,
@price decimal(10,2),
@tax decimal(10,2),
@usage nvarchar(20),
@rent_type nvarchar(10),
@price2 decimal(10,2),
@note2 nvarchar(max),
@date_start date,
@date_end date,
@note nvarchar(max),
@app_user nvarchar(20),
@old_contract_id int
AS
BEGIN

DECLARE @ErrorMessage NVARCHAR(MAX)
DECLARE @ID int

BEGIN TRAN
BEGIN TRY

-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

--insert data
INSERT INTO (dbo).(contracts)
       ((cust_id)
       ,(duration)
       ,(price)
       ,(tax)
       ,(usage)
       ,(rent_type)
       ,(price2)
       ,(note2)
       ,(date_start)
       ,(date_end)
       ,(note)
       ,(app_user))
 VALUES
       (@cust_id,
       @duration,
       @price,
       @tax,
       @usage,
       @rent_type,
       @price2,
       @note2,
       @date_start,
       @date_end,
       @note,
       @app_user) SELECT SCOPE_IDENTITY();

       SET @ID = SCOPE_IDENTITY();


insert into contracts_shops (contract_id, shop_id)
select @ID, shop_id
FROM contracts_shops WHERE contract_id = @old_contract_id;

COMMIT

END TRY
BEGIN CATCH
    SELECT @ErrorMessage = ERROR_MESSAGE()
    RAISERROR (@ErrorMessage, 10, 1)
    ROLLBACK TRAN
END CATCH
END

subquery – Subqueries in SQL Server

I am taking a SQL course and am completely stumped on this question.

Using a subquery have a query that returns:
CustomerID, EmailAddress, FirstName, LastName, ‘#3: Customers who have ordered than 1 product’ as
queryInfo
The subquery from this query will be: All customers with who have ordered more than 1 product

Note: More than one Product, not Quantity > 1 So a customer who ordered 10 of the same
product is NOT what I’m looking for.

Here is the full query I used:

SELECT c.CustomerID
     , c.EmailAddress
     , c.FirstName
     , c.LastName
     , '#3: Customers who have ordered more than 1 product' AS queryInfo
  FROM Customers c
 WHERE c.CustomerID IN (
           SELECT p.ProductID 
             FROM Customers c 
             JOIN Orders o
                  ON c.CustomerID = o.CustomerID
             JOIN OrderItems oi
                  ON o.OrderID = oi.OrderID
             JOIN Products p
                  ON oi.ProductID = p.ProductID 
         GROUP BY p.ProductID 
           HAVING COUNT(*) > 1
         )
     ;

Not sure what I am doing wrong, but any input is greatly appreciated. Thanks!

sql server – How to create table from two alias tables with sql

I want to create a table that shows a name, position week1, position week2. I created the following queries that give the correct result for each week separately.
All the data is in one table results.
Server version: 10.4.14-MariaDB

 SELECT
    name, team, points,week, 
    @curRank := @curRank + 1 AS position
    FROM results,  (SELECT @curRank := 0) r
    WHERE week = 1
    order by points DESC

and

SELECT
    name, team, points,week, 
    @prevRank := @prevRank + 1 AS position2
    FROM results,  (SELECT @prevRank := 0) r2
    WHERE week = 2
ORDER BY points DESC

But when I combine the with UNION I get an incorrect result.

(SELECT
    name, team, points,week, 
    @curRank := @curRank + 1 AS position
    FROM results,  (SELECT @curRank := 0) r
    WHERE week = 1)
    
 UNION
 (SELECT
    name, team, points,week, 
    @secondRank := @secondRank + 1 AS position2
    FROM results,  (SELECT @secondRank := 0) r2
    WHERE week = 2)
ORDER BY points

So how would I combine the two select statements to get the table with just name, position week1, position week2? I do not need points in the table, but points are used to calculate the position.

sql – Getting new vs. returning customers in Postgres

I have this work task, to write a SQL query that would show how many of the active customers are new (= do not have prior transactions) as opposed to returning per a given period.

There is nothing particular in the DB to get this, so my solution takes a transaction and compares customer_id to the pool of customer_ids that belong to all transactions that took place prior. Here’s how it looks:

SELECT allthem.period, allthem.c "all", newonly.c "new", (allthem.c - newonly.c) "returning"
FROM (
    SELECT date_trunc('week', t.paid::timestamptz) AS period, COUNT(DISTINCT(t.customer_id)) AS c
    FROM transactions t
    WHERE t.status = 'paid' AND (t.price->>'payment_total')::real > 35
    GROUP BY date_trunc('week', t.paid::timestamptz)
    ORDER BY date_trunc('week', t.paid::timestamptz) DESC) AS allthem
JOIN (
    SELECT date_trunc('week', b.paid::timestamptz) AS period, COUNT(DISTINCT(t.customer_id)) AS c
    FROM transactions t
    WHERE t.status = 'paid' AND (t.price->>'payment_total')::real > 35
    AND t.customer_id NOT IN (SELECT customer_id FROM transactions WHERE status='paid' AND (price->>'payment_total')::real > 35 AND paid::timestamptz < t.paid::timestamptz)
    GROUP BY date_trunc('week', t.paid::timestamptz)
    ORDER BY date_trunc('week', t.paid::timestamptz) DESC) AS newonly ON allthem.period=newonly.period
WHERE allthem.period > date_trunc('week', now()::timestamptz at time zone 'pst') - interval '12 months'

It works, but the problem is that it is quite slow.

Is there any way to compute the required data with less server load?

sql – Como utilizar EF Core Code First no Azure Synapse

Estou tentando utilizar migrações para gerenciar esquemas de banco de dados com o Entity Framework Core e C# no Azure Synapse, quando executo o comando update-database recebo uma mensagem de erro conforme descrito abaixo:

Enforced unique constraints are not supported. To create an unenforced unique constraint you must include the NOT ENFORCED syntax as part of your statement.

Esse erro ocorre pelo fato do Azure Synapse não aceitar o uso de PRIMARY KEY sem que a mesma esteja acompanhada por NONCLUSTERED e NOT ENFORCED. O script abaixo funciona perfeitamente quando executado no msqlms:

CREATE TABLE dbo.sample_table
(
    c1 int IDENTITY(1,1) NOT NULL,
    c2 char(10) NULL,
    c3 datetime NULL
)

ALTER TABLE t1 ADD CONSTRAINT PK_t1_c1 PRIMARY KEY NONCLUSTERED (c1) NOT ENFORCED

Para incluir NONCLUSTERED pelo mapeamento do EF basta apenas definir IsClustered(false) conforme exemplo abiaxo.

public class ManifestationMap : IEntityTypeConfiguration<Manifestation>
{
    public void Configure(EntityTypeBuilder<Manifestation> builder)
    {
        builder.ToTable("Manifestation");

        builder.HasKey(prop => prop.Id)
           .IsClustered(false); // Include NONCLUSTERED 
        ...

Não encontrei nenhuma solução para incluir pelo EF o NOT ENFORCED.

Obs: Não tenho esse problema quando estou utilizando o Azure SQL Server, nem quando utilizo Sql Server instalado localmente, apenas no Azure Synapse (Sql Server DW).