This seems easy at first but after thinking about it for the day I’m stuck. Imagine you have a global block list for veggies. create table blocked_veggie(veggieid int primary key, veggiename varchar(…
Tag: sql-server
WHERE IN performs much better with Table Type than with hardcoded values in SQL Server
I have 2 queries that are essentially the same (at least if I didn’t miss something). I was thinking that one cannot beat constants declared in the query itself, but apparently the first query is few times slower than the second one. It seems that SQL server is not smart enough to provide a good plan fo…
RawSQL and auto-generated keys in EF Core 3.1
I have a Model with a Guid primary key. I want the Database to generate a key on insert so I added the following annotations: Now I expected that inserts with RawSQL wouldn’t expect a primary key, however the folllowing statement doesn’t work when executred through ExecuteSqlRaw: An error is cause…
How to loop through JSON array to insert rows in SQL Table using TSQL?
I have following query where I would like to insert data from JSON: INSERT INTO EncExt ( reference ,system ,code ,display ) SELECT JSON_VALUE(record, ‘$.id’) AS reference , JSON_VALUE(…
How exactly transactions work in Microsoft SQL Server
I’ve read that creating for insert trigger creates transaction implicitly. So I can rollback transaction from within trigger and “undo” the insert. Is that so? create trigger TRIGGER_NAME …
Delete rows from 2 tables using a single query
I have the following database-schema: I have the following example data: CREATE TABLE computermapping ( ComputerMappingID int NOT NULL, PrinterGUID char(36) NOT NULL, ComputerGUID char(36) NOT …
How to show only rows where there is no match between strings at all?
I have to compare two columns and be able to extract only rows where there is not even a partial match between strings. For example, I have this table: Col1 Col2 John Smith John Smith ltd Pepper Row Whatever Pepper red Blue The only row I should be seeing after the filtration is the last one: Col1 Col2 red Bl…
Passing parameters from Python to SQL Server
I have code like this: I am getting this error: ‘The SQL contains 1 parameter markers, but 2 parameters were supplied’, ‘HY000’ Is there any way to pass this parameter? I don’t want to create a dynamic array by concatenation. Is there any way to name the parameter marker inside t…
How to get the sum of a column in SQL Server and display in a single query?
I have this table where I have three particular transactions: Billing, Penalty and Discount. I want to compute the billed amount ((Billing – Discount)+Penalty). This should be the output What I have tried so far: I managed to get the billed amount for each individual account: Answer I would use conditio…
Selecting specified query from multiple select statement inside a single stored procedure
Good Day everyone! I’ve been searching everywhere on how to get a specified query using if-else statement inside a single stored procedure but still no luck on how to achieve those function. Here’s the T-Sql When exec command is called, it only return Command(s) completed successfully instead the …