Questions tagged [sql-server]
Microsoft's SQL Server is a relational database management system (RDBMS) that runs as a server providing multi-user access to a number of databases. It originated from the Sybase SQL Server codebase, which is why both products use the extension of SQL called Transact-SQL (T-SQL).
568
questions
-1
votes
1answer
50 views
IF SELECT statement will break SQL ACID Compliance?
My QA told me that I should not use IF SELECT before doing an UPDATE statement because it will breaks the atomicity of a ...
2
votes
1answer
30 views
Alter authorization on all non-system SQL Server databases
I am not very experienced with T-SQL and feel this can be done better. The script changes ownership of all "user" (non-system) databases to sa.
Usage of a ...
2
votes
1answer
36 views
Complex reporting query suggestion needed for different approach as per latest Tsql format
We have recently migrated from SQL Server 2008 R2 to SQL Server 2017 web edition. I am not a SQL developer but enthuses to learn.
Since many of our scrips are of old SQL format, I am searching for ...
3
votes
3answers
93 views
3
votes
2answers
31 views
PySpark SCD Type 1
I am using PySpark in Azure DataBricks to try to create a SCD Type 1.
I would like to know if this is an efficient way of doing this?
Here is my SQL table:
...
0
votes
0answers
15 views
Re-organizing indices in case of fragmentation is T-SQL
In order to keep the fragmentation ratio of indices low, you should re-organize the indices from time to time on MSSQL. Microsoft describes in the link below how you can check the fragmentation of ...
5
votes
1answer
177 views
Basic schemas for database and tables creation, bulk import from .csv and basic queries
The following SQL code serves the purpose of assessing basic SQL Server literacy.
I am creating database and tables schemas, inserting data from .csv files.
Constructing 8 simple queries as answers to ...
0
votes
1answer
61 views
SQL Query to perform a “reverse exclusion” on a LEFT JOIN with a select from the left side table
I have a set of tables that I want to execute a LEFT JOIN on and bring back "excluded" rows. In addition, I would like the left table's ID included as ...
1
vote
2answers
99 views
Optimizing database access using LINQ [closed]
I have the following method that I am trying to optimize. Currently, the method works great, however, it takes a little over 3 hours to complete. I feel like, using some nifty LINQ joins, it should be ...
1
vote
1answer
69 views
Retrieve data from two different rows in a single SQL query?
I've a table with the following data
and I need to find the number of minutes between two jobs (Say A and C).
The following query works but wondering, if there is a simpler way to achieve the same.
<...
3
votes
1answer
67 views
all appointment based on interval distance
Currently with a given Date and an Interval in Day, I can calculate all of the appointment in the future. for example if you are ...
2
votes
0answers
36 views
Basic login by username logic with SQL Server
My goal is to make a very simple library with the authentications basics: create a user and save its hashed password, change its password, have a method to check if the password is correct for login.
...
2
votes
1answer
114 views
How to make this query better (e.g. avoid repeating subquery in a query)?
Stack Exchange provides access to query their MS SQL databases. They have a table Posts where each post is either a question or an answer (see the schema on right ...
2
votes
1answer
35 views
Performance stored procedure update multi-coulmn sql
I have a stored procedure the every 5 min run in sqlserver
high cost iO/cpu
do you have any idea to tune this query
...
2
votes
2answers
103 views
Receive data from SQL and CSV and send it to MySQL database using Python
Details:
I have a code, which does the following:
Get data from CSV and insert it into MySQL table
Get data from SQL Server and insert it into MySQL table
Run this code every 240 seconds - to ...
1
vote
0answers
28 views
Code for tracking changes and deletions to tables
I have written a SQL server stored procedure to generate "log" tables which store the old values of rows when a table is updated, and also record when rows are deleted ( and by who ). It's quite ...
2
votes
0answers
32 views
API Layer ClientId and Security style
Im at the point where I am designing my api security and I was going with a custom attribute I don't want to use third party systems as they cost money.
I was going to use the client id and the ...
1
vote
2answers
79 views
Faster/Better Processing for SQL While Loop
I have a list of about 55k Accounts that I am cleaning up. 95% of them are from US/Canada which is what I'm most concerned with. I am going through record by record and will perform various checks ...
1
vote
1answer
31 views
Calling a function within stored procedure to derive a value
I am trying to write a sql storeprocedure that basically returns a derived value from an outcome of a function call. The derived field returns the difference in days and if the difference is less ...
1
vote
1answer
62 views
Get average post score of TOP 10 tags on StackOverflow using Stack Exchange Data Explorer
I want to use the Stack exchange data explorer in order to display some basic information about the top tags.
The query should print the number of questions and answers, the average number of answers ...
3
votes
3answers
295 views
Export sql data to xml and csv
I have the below code that takes the sql data and exports it to xml or csv. The xml is processing at good speed but my csv sometimes takes 1 hour depending on how many data rows it exports. My ...
3
votes
0answers
283 views
Creating SQL Server Database Users Dynamically from User Input
The Application
I have an application in testing that runs off of an MS SQL Server database. The app allows users to authenticate using Windows authentication (that is, using the Windows username the ...
5
votes
1answer
118 views
Excel to SQL server
I am self-taught when it comes to coding. I want to improve, so please give me advice if I have some bad habits or if there is in general a better way of doing this.
The idea of this code is to look ...
5
votes
2answers
121 views
NewSequentialId - pure net core 2.2 implementation of sql server function
i'm currently trying to rewrite sql server newSequentialId() function in pure c# that is performing as fast as possible and eats minimum memory.
Question is can I it be done better - current ...
2
votes
1answer
89 views
Reading customer data
This is the first time I am writing R code to access/manipulate and save data. The SQL query I would normally run then save to a spreadsheet, read it into R, save to another sheet then copy and paste ...
0
votes
0answers
674 views
Chart.js - Getting data from database using sql server and asp.net mvc5
I displayed and styled the chart.js and am also using mock data in the array, I just can't get my head around how to populate the data in the chart.js using MVC 5. I have tried lots of different ...
2
votes
1answer
951 views
Database Model for Feedback Module
I wish to generate an online feedback system. Requirements are
Admin can enter feedback questions
Admin will select these questions and create a feedback session
One Question can be used in many ...
1
vote
2answers
1k views
Cancel Long Running Query using Cancellation Token AND SqlCommand.Cancel()
I need to be able to cancel a long running query programmatically through our application. The code below will kick off a long running query and give control back to the main thread. At any ...
2
votes
0answers
49 views
SQL Code That Looks for Dupes with Confidence Score, 3hr / 1.2 BILLION Rows Returned. Optimize?
OK, I have a customer list that has approx 80k records. I need to search and compare looking for duplicates, but duplicates could be an exact match, or a similar match (only some data matches) so it ...
1
vote
0answers
29 views
Searching for Duplicates in SQL Table with Confidence Score
Sometimes I'm not sure if my questions should be here or StackOverflow, so I appologize if this is the wrong place.
I have a SQL (MS SQL 2016) stored procedure that is evaluating data looking for ...
0
votes
1answer
72 views
database connection, fetching rows and handling error in csharp
Fetching database records and displaying as JSON. Asking for revision to ensure if everything's ok, i.e. connections are properly handled and closed in case of error.
...
5
votes
2answers
129 views
Query with multiple foreign keys to the same table
I have a primary table, REP_ASSIST.ES_LOG with multiple columns that are foreign keys to another table, ...
3
votes
2answers
165 views
Function to get current value or default from a string of two values
I have SQL scripts that I want to be executable directly in the editor (like DataGrip or SSMS) and also in automated integration tests. However, in each use-case they require different environment and ...
6
votes
1answer
73 views
Zombie killers ranking
Code Review has a Zombie Problem and, in comparison to The Walking Dead TV Show, our survival doesn't depend on killing them all day every day.
As a potential solution, I though it would be ...
3
votes
1answer
64 views
SQL Sub-query with running total optimization
The codes below get the rows of data until it reaches the total running quantity limit.
Notice that the SQL uses sub-query. ...
1
vote
1answer
28 views
T-SQL Getting Sequential events with first even criteria
I have a query I am writing where I want output if a person has some service provided, then I want all the services they had provided after that and I don't want the individual returned if that is the ...
3
votes
1answer
387 views
Daily SQL job to delete records older than 24 hours
Below is a delete job to delete 1M+ records daily and takes 13 hours to complete and sometimes more than that. I need to optimize this.
The table tblcalldatastore is being inserted 24*7 through a ...
3
votes
1answer
116 views
Deleting millions of logs in batches
I have a fast growing table with logs that I frequently have to delete (the server doesn't have much resources). It grows by at least two million entries every day. The database is a SQL Server 2012.
...
1
vote
1answer
36 views
Updating field in Temporary table
I have a more massive sp, in which I use some temporary tables, below is a part of the sp where I am updating a field in a temporary table two times.
...
3
votes
1answer
90 views
Calculating all flight connections
I am working on an API to return all the possible flight route between point A and B. I have a set of data which includes the flight number, airline, departure airport, destination airport, departure ...
1
vote
1answer
477 views
Do these BLL-DAL comply with the Dependency-Inversion Principle and is this valid Dependency Injection?
I am working on a very simplified proof of concept (POC).
Separation of concerns should be achieved in this project, so that in the future we have flexibility in respect of what data source we use (...
11
votes
1answer
201 views
Safe dynamic SQL for generic search
Prompted by discussion about SQL injection, I wanted to put a proof of concept forward to get feedback about whether this is in fact safe and protected against SQL injection or other malicious use. ...
2
votes
1answer
85 views
My first c# program: Get Data from Sql Server and show it in Textboxes
So I have been programming in VB.net and want to learn C#.
I "successfully" created a simple program that retrieves data from Sql Server during Form1_Load. The ...
1
vote
1answer
80 views
T-SQL query Choose name of clients, concluded more than one loan agreement in 2010
QUESTION: Choose the name of clients,
concluded more than one loan agreement in 2010,
as the numbers and dates of issue of these contracts.
Sort by name of the client and date of issue of the loan.
...
0
votes
0answers
64 views
Parties and Messages in SQL e-Commerce db design
I'm posting a follow-up question based on my first question. My goal is to, as an exercise for myself, create a complex (in my opinion) e-commerce application. The explanation of the application can ...
3
votes
1answer
37 views
T-SQL Query remove duplicates except for original row from the table
Question:
There is an Exam_Merge table into which to import
records from a similarly structured table in another database.
In this case, the Exam_Merge table contains the values of the primary key ID
...
4
votes
1answer
48 views
JSON from SQL Server: Reduce lists of single dicts to list of values
SQL Server 2016 introduced some JSON features, but they're far from robust. As far as I can tell, there's no way yet to output a simple array; everything must be in ...
4
votes
1answer
517 views
SQL e-commerce database design
I want as an exercise to create an e-commerce application from bottom up. My main goal here is to gain a lot of knowledge. I have experience in web development, but never have been there from the ...
1
vote
1answer
57 views
Query to generate payroll based on attendance for a month
I have written a query to generate payroll based on attendance for a month, for about 4000 employees. It takes quite a long time to execute. Basically, what I am doing is to joining two different ...
1
vote
1answer
207 views
Insert multiple rows without using CURSOR SQL Server 2014
I have a fairly complex query which insert multiple rows into a table from multiple tables. Its works fine but I really don't want to use cursor as some blog says cursor took long time for execution (...
