4

Quick remarks: Please follow the Microsoft naming guidelines. Parameters should be camelcase, same with local variables. Don't write your own ORM or use ADO.NET. Instead, use Dapper. In the middle of retrieving your data from the DB, you start filling MenuItemsXml. Don't do that. Simply retrieve all the values, close the connection, and then start ...


3

You can greatly simplify things as well as wildly improve performance by using something like the following... DECLARE @start_date date = '2020-09-24', @interval_in_days int = 30, @appt_count int = 100; WITH cte_n1 (n) AS (SELECT 1 FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) n (n)), -- 10 cte_n2 (n) AS (SELECT 1 FROM cte_n1 ...


2

The following are a few suggestions on how I'd write the SQL as a stored procedure. Then call it from R. Source Control If you don't already have a database project, create one in Visual Studio. Then check it in to source control. Microsoft Azure DevOps Services is free & private for teams of 5 or less (this is per project, so 5 developers per project). ...


2

Some quick remarks: DemoGraphic isn't a compound word so there shouldn't be a capital letter in the middle. However, Barcaccount is so it should be called BarcAccount. Same for Sridentifier, Updatedate, Aridentifier. Use meaningful variable names. I have no idea what ar is, or sr. UpdateEmailTable(IEnumerable<StudentEmail> students) and then var ...


2

Simple answer is yes. when you open a connection in a Using statement, once the scope leaves that statement, it automatically disposes of the connection, there really isn't a reason to explicitly close the connection when implementing a Using statement. Reference: Microsoft Reference to the IDisposable Interface that is implemented by the DbConnection Class -...


2

General Observations After reviewing this code it is clear that the 2 functions come from 2 different classes, one class called OrderData which contains the first function shown (with the actual database query) and a second class currently unknown that contains the second function shown. To really help you optimize the code we would need to see the full ...


2

I should not use IF SELECT before doing an UPDATE statement because it will breaks the atomicity of a transaction This is wrong. The if exists / update is itself a non-atomic operation, but since it exists within the context of a transaction, ACID will be preserved. If there were no transaction, no isolation level would be applied and integrity would not be ...


2

You could use INFORMATION_SCHEMA.TABLES instead (note that INFORMATION_SCHEMA is found in other ANSI-compliant DBMS and should be the preferred way to explore tables and other structures in a database). For example: SELECT * from INFORMATION_SCHEMA.TABLES WHERE TABLE_CATALOG NOT IN ('master', 'tempdb', 'model', 'msdb') AND TABLE_TYPE = 'BASE TABLE' Then you ...


1

To start, here is a potential formatting that makes the query less of a tragedy to read: SELECT TOP 200 'Recoup', ERH_DOC_DATE, ERH_DOCNO, ERH_RECV_FROM, ERD_BLH_ID, BLD_CODE, A.ERD_ALLOWED_AMT, A.ERD_PAID_INS_AMT FROM TRN_ERA_HEAD, TRN_ERA_DET A, TRN_BILLING_DET WHERE ERH_STATUS = 'P' AND ERH_BOOL_INACTIVE = 0 AND A.ERD_BOOL_RECALLED = 1 ...


1

Some observations about your sql table and data types. In most relational databases, and definitely SQL Server, choosing an appropriate data type is important. Selecting an inappropriate data type, or being lazy about things such as string length, can result in unexpected and simply unnecessary performance degradation. Data types should be chosen such that ...


1

Data location This is probably a weirdness from MSSQL itself and not specifically your setup, but your database location: FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\SQLAssessment.mdf', as well as MSSQL's own default Database Engine data file location: \Program Files\Microsoft SQL Server\MSSQL{nn}.<InstanceID>...


1

You already have the table2_vals you need. Just cross join them with table1. No need for long nested stuff and group by: select t1x.Table1_Id,q.Table2_Val from Table1 t1x cross join ( SELECT t2.Table2_Val FROM Table2 t2 LEFT JOIN Table1 t1 ON t2.Table2_ID = t1.Table1_ID WHERE t1.Table1_ID IS NULL )q


1

You can use Sql Server's window functions LEAD and LAG to get access to two rows in one SELECT statement. Here's just one way to get the results you're after: DECLARE @jobs TABLE ( Job char(1) primary key, Start_time datetime, End_time datetime ); INSERT @jobs VALUES ('A', '2020/01/10 8:00', '2020/01/10 8:15'), ('B', '2020/01/10 8:15', '2020/01/...


1

It both cancels the Task as well as cancels the query in SQL Server. I can check the status of the query in SQL Server with select * from sys.query_store_runtime_stats to verify that the query was in fact aborted. This is important as I need to make sure it's not just canceled in the app but in the database as well. Baaaack up a few steps. Your strategy to ...


Only top voted, non community-wiki answers of a minimum length are eligible