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).
3
votes
0answers
21 views
SQL Trigger - Audting Config Table - Send Email with update details
Keep in mind i'm not an expert or developer, working in IT operations and improving my coding skills.
Goal: Trigger to send email when a specific configuration table is updated or deleted. Should ...
1
vote
0answers
27 views
First unique record from update queue SQL
The goal is to select the first change in the Update Queue on change of Unit or UnitsRelative
The grouping is performed on productId, from and to
Using row_number and looking at the next row in ...
1
vote
2answers
134 views
Creating a random code and saving if it does not exist
I have created a script to generate 5 random codes, check if any one of them exists in the database, and if not, insert the record. The script will throw an exception if a single code could not be ...
6
votes
2answers
166 views
SQL query to compare two XML columns
I need to compare two XML columns. I currently have about 1000 rows of data that this is running against and it takes about 20min. Is there anyway I can improve this query to run faster.
...
1
vote
1answer
25 views
Correlated subquery slow
I have a table that looks like this
Directory nvarchar(max)
Extension nvarchar(10)
Length bigint
I also have another table that provides the type of file (lookup table)
extension nvarchar(10)
...
6
votes
3answers
835 views
7
votes
1answer
45 views
Load and Parse Stack Exchange data dump XML into DB table
I have put together a stored procedure to load and parse the Stack Exchange Data Dump into a relational database (akin to Stack Exchange Data Explorer). Each site has 8 XML files like these:
The ...
2
votes
1answer
43 views
Find items in a table that match a set of conditions in another table
I have two tables. Table 1 has sets of EXPORT conditions: e.g., SET 1 has FOOD, GARMENT, and TECHNOLOGY, and SET 2 contains GARMENT and TECHNOLOGY.
...
10
votes
2answers
206 views
Batch file to backup and update SQL Server DBs
This is my first windows batch file. The script basically goes through this basic flow of tasks, in order to update some specific SQL Server DBs to a new DB version:
Backup all SQL server DBs that ...
1
vote
0answers
38 views
3
votes
3answers
668 views
Fetching busy students and their latest product
I use SQL Server 2014 and I have three tables:
Student (ID, Name, Age)
Product (ID, Name)
StudentProduct (StudentID, ProductID, Date)
...
2
votes
2answers
82 views
Create database connection and run the insert, delete, update queries class-Update
Based on the feedback I have got from the guys reviewing the code as posted in my previous question.
Create database connection and run the insert, delete, update queries class
I have used ...
2
votes
2answers
139 views
Create database connection and run the insert, delete, update queries class
Recently while developing our demo project I had to a write a lot of repetitive code for executing the database queries (insert, update, delete). So, I have put all the repetitive code into a class ...
0
votes
0answers
38 views
Generic Polling Class and Using it to Poll SqlConnection
I have the following poller class that can be used to "poll the required action":
...
3
votes
1answer
54 views
Finding the Nth occurrence of character in string
I wrote an SQL Server function which returns the substring before the Nth occurrence of a character.
For example:
...
1
vote
1answer
56 views
Generate SQL for translated resources from Excel Spreadsheet
I do some work that involves translating resources for a webpage. These are all stored in a SQL Server table that looks like this (plus a few irrelevant constraints).
...
3
votes
1answer
88 views
Entity Framework query optimization
A user can track a show, and mark episodes and seasons of that show as watched. To support this I have the models below:
Models
...
0
votes
0answers
47 views
SQL BulkCopy - XML to SQL
The application we've built occasionally needs to load XML data into a SQL database. The code works well until the files reach 600kb or greater at which point out-of-memory exceptions occur. The ...
3
votes
0answers
59 views
Query for a ticket system involving cases and casts on many attributes
I have this SELECT statement that inserts into a temp table and it is slow! Right now it takes ~1500 ms to run and only deals with ~7000 rows. As the data grows ...
7
votes
2answers
49 views
Retrieve high views per day questions with few answers
I've been messing around with the SE Data Explorer and I've come up with a query that basically returns information about questions that have a high number of views per day, that have few or no ...
-1
votes
2answers
109 views
Calculating price with a sub query
So I have this sub query:
*(select p.value1 From Params as p where p.PAR_Field = 'Taxe' and p.PAR_KEY = 'TVA')*
And I was using it 3 times to get the same value.
...
2
votes
1answer
28 views
Stored procedure to swap account holders
I'm doing an exercise, and would like to know if what I'm doing is correct and follows the correct structure for rollback in case a transaction fails. I would like to know as well if my ...
3
votes
1answer
49 views
Database design for users' postcards
I would like to a review of my database design.
There are 3 tables + ASP.NET Identity tables.
The Users could have many postcards.
Here's my tables without Identity.
I attached also my scripts ...
1
vote
0answers
63 views
0
votes
1answer
37 views
Repair orders query in SQL Server with subselects
I was wondering if you guys know how to reduce the amount of subselects in this SQL Server Query? I think I'm using bad practices in this:
...
7
votes
2answers
99 views
Database credentials and connector including encryption
I'm designing a small Java desktop application to interact with my database and this is a very important part of it as a majority of the operations will be involving the SQL Server 2012 database. I am ...
4
votes
0answers
70 views
Stored procedure to insert a new person
I have not written very many stored procedures before, and this is the first one I have written to be used in a real business application to write new data into the database, so I would like to get ...
10
votes
0answers
56 views
Parameterized Chutes & Ladders simulator using SQL stored procedure
I thought this would be a fun exercise to use SQL to participate in the June 2016 Community Challenge, so I did. Yes, you may call me crazy!
I had to write a few little things to help me, I needed a ...
3
votes
2answers
46 views
Finding maxDate for each entity
I have a table with (CompanyNumber, File_Date, [various financial results])
I wanted to get only the most recent set of accounts for each company.
After various ...
4
votes
2answers
51 views
Adding dynamic column names to a table
I'm using Microsoft SQL Server 2014 Express.
I found myself wanting to add the Columns CompanyNameChangeDate (1 through 10) and ...
4
votes
3answers
54 views
Data import “upsert” from a SSIS package
I have the following "upsert" trigger in SQL Server 2005, which is handling a large data import from a SSIS package. It is taking a very long time to complete - I suspect due to the concatenation and ...
1
vote
1answer
44 views
Demographics information for EDI report
I am going through some of my companies EDI reports to see if I can make them faster. The query below runs in less than one second in our AWS RDS environment, but it takes ~55 seconds on a blade in ...
3
votes
2answers
111 views
SQL query with nested subqueries
The following query is taking over 800ms to run, and returning 300 rows. When deployed to SQL Azure, it takes much longer on an affordable price tier.
...
2
votes
1answer
50 views
Query column names and then using them in statements
I currently have a table with over 250 columns, in which ~10-15 columns have column names that have a specific prefix, namely EQP_.
Assume that you do not want to ...
2
votes
2answers
116 views
Copy Data from Informix to SQL Server
The following method copies data from Informix db to SQL Server db and it's so slow, I want to refactor it so it could be more readable and faster:
...
1
vote
0answers
25 views
Exclusive row lock
I'm in a situation where I need to manage a kind of FIFO process within a SQL procedure. The procedure is called by a multithreaded windows application.
I've created a table which look like this:
<...
1
vote
0answers
76 views
Current .NET session username injection in SQL Server stored procedures
I have a legacy WCF service that is ported from a security model using database logins and roles to a custom security. One effect of this change is that stored procedure are no longer executed using ...
1
vote
1answer
70 views
Finding all delivery orders with multiple purchase orders
I have a SQL query to find all delivery orders with multiple purchase orders (one-to-many). Is there a better or shorter way to write this?
...
0
votes
1answer
56 views
Convert MS SQL datetime to Oracle varchar(32) interval
I have a query against an MS SQL database that takes the resulting dataset and stores it in an Oracle database table which then gets joined to another Oracle table using the column ...
2
votes
0answers
20 views
Generating days in a specific range
I have a query in MSSQL, where I get a summary by days from a range in a table, and this is in union with other queries. This query is used to generate days in a specific range, then add the registry ...
2
votes
2answers
216 views
Update user country based on IPv4 decimal address
I have UserSignatures, a table with clientIPs, some of which have CountryIds that are null ...
0
votes
2answers
56 views
Get parent folder ids by folder id(s)
My Scenario:
#TmpDistinctFolderIds table contains one or many FolderId.
...
2
votes
2answers
72 views
TSQL query to gather amounts for an employees, spouses, and children in one row
I have a query that works as intended, kind of pulling from multiple records in a table, and flattening the data into one row. I am wondering if there is a faster/more semantically correct way to do ...
1
vote
1answer
467 views
Python Pandas Apply with a Lambda Function
I have a table in pandas that has two columns, QuarterHourDimID and StartDateDimID ; these columns give me an ID for each date / ...
1
vote
2answers
61 views
Pivot script for sales numbers
I've recently been tasked with migrating some code stacks for an applicatin that is moving from using Access to using SQL-Server.
I don't usually use PIVOT all ...
4
votes
1answer
74 views
SQL procedure for Archiving and Deleting
I have a requirement for archiving 5 huge tables from PROD to ARCHIVE server without losing the integrity of the tables.
The query makes use of the Linked Server functionality and current idea is to ...
3
votes
1answer
165 views
Database trigger to monitor insert/update events and update another table
I am working on a MSSQL Database (for a program called Sage 200). There are many tables in the database, however, I want to be notified of changes (new record inserted or existing record updated) via ...
2
votes
0answers
49 views
T-SQL Secure String Comparison
The previous version of my function was scalar-valued and employed a WHILE loop to do the comparison, this is SLOW. This new version is tabled-valued and uses a typical tally table in place of the ...
1
vote
1answer
32 views
Stored Procedure enhancement for updating columns in a subset
The purpose of this store procedure is to reach into a large database (test), query for a subset, and update a column value, but with a big constraint - I can only update one row at a time in order to ...
1
vote
1answer
24 views
Get top row for muliple registers
I came across a piece of code that generates SELECT queries from a template.
The template:
...
