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).

learn more… | top users | synonyms (1)

-1
votes
0answers
9 views

UPDATE FROM that overwrites matches [on hold]

I'm reviewing code by another developer. I'm not too familiar with the semantics of UPDATE FROM when used with a JOIN, but here ...
-1
votes
0answers
35 views

SQL query to include one column value for one condition and exclude other columns

I am learning writing T-SQL queries. The ITEMS table has a column named mfg possible values (R, K, I, Z). The ...
4
votes
2answers
32 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
2answers
40 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
41 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 ...
1
vote
0answers
27 views

SQL query with nested subqueries

The following query is taking over 800ms to run, and returning 300 rows. ...
2
votes
1answer
29 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
81 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
24 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
36 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 ...
0
votes
1answer
55 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
32 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
19 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
211 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
1answer
30 views

Get parent folder ids by folder id(s)

My Scenario: #TmpDistinctFolderIds table contains one or many FolderId. ...
2
votes
2answers
67 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
140 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
58 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
63 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
44 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
37 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
0answers
22 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. The ...
1
vote
1answer
18 views

Get top row for muliple registers

I came across a piece of code that generates SELECT queries from a template. The template: ...
0
votes
0answers
29 views

Validating an NPI in T-SQL (Luhn's Algorithm)

The requirements for validating a national provider identifier can be found here. Here's my attempt at a T-SQL implementation: ...
1
vote
0answers
71 views

WCF service architecture when SQL Server direct table access is not allowed

Part of my job is to maintain an old application written in Silverlight talking to a WCF service which in turn fetch/persist data into a SQL Server database and in a BI cube. Most of the security is ...
3
votes
1answer
58 views

Stored procedure to query custom data tables as dynamic SQL

There's a lot going on here, but the background for why this is necessary is that there is a set schema, or 'core' set of tables that are prefixed with 'bu', and any core table can have a custom table ...
0
votes
1answer
26 views

Finding colors that have not been assigned to a given user

Goal: make this code as efficient as possible. Given a finite dataset (Colors - this isn't Crayola here :D) how do I know what colors are not assigned to a given user? Additional requirement - the ...
-2
votes
1answer
60 views

Sample SQL Server code using pivot table

I'm using a dynamic pivot example. This SQL code has the purpose of perform transposing data from rows to columns, using a temporary table in SQL Server. The data is initially presented this way: ...
3
votes
1answer
77 views

MSSQL query to look for duplicate record

This query took 6 seconds to complete. How can I optimize it? Total records in table is 166803. ...
3
votes
2answers
127 views

Insert or update user data

I inherited an application that uses some stored procedures. Here is a sample of one of the stored procedures used to insert or modify user data. would like to get the group's opinion on the code. ...
1
vote
2answers
71 views

Powershell script to iterate through folder and restore SQL backups

I am working on a PowerShell script which will loop through a directory containing SQL Server database backup .bak files; move the files to a different location; restore each of those files to the ...
3
votes
1answer
52 views

Stored procedure to classify policies by sales date

Currently this is the store procedure I am working on, it is working according to some testing that was done. I feel that it could be improved though, could use some optimization, feedback or ...
6
votes
2answers
72 views

Deduplicating value pairs between 2 columns

This was asked in the SQL Helpline room: ...
0
votes
3answers
42 views

Counting pending bookings using a subselect

Can this query be improved? Is there a way to eliminate the duplicate function call? ...
4
votes
2answers
59 views

Top Python badged users from NYC

I forked this StackExchange Data Explorer (SEDE) query from another one, and made it a bit more complex. I'm looking for feedback on my style, as well as answers to other questions (see below). ...
0
votes
1answer
35 views

Display the count of field in a view

I have a table that look like this ...
2
votes
1answer
30 views

Reportable Sp_Who2

One of my issues with sp_who2 is that you need to be in the console to run it as it recalls spid twice and most reporting tools throw this as an error. My question is, would using the code below to ...
1
vote
1answer
77 views

Shredding XML into an SQL table

I have a few "given" things: An SQL Server 2012 table with certain structure Data provided as XML with certain structure My code has to shred that XML into a ...
7
votes
2answers
214 views

Replacement for sp_MSforeachdb

Having been informed that sp_MSforeachdb is undocumented and unsupported, I have been working on creating a replacement stored procedure based off the work done ...
1
vote
0answers
32 views

Stored procedure to compare stored procedure result sets

Originally posted as an answer to a SOF question, this is about a generic procedure that allows back-end developers (e.g. SQL developers) to compare the results sets obtained by running two procedures ...
3
votes
1answer
73 views

Loading a database table using a promise chain

I am creating a util module that I'm using to communicate with a MS-SQL database. I want each public method to return a promise. I started with a private function that executes a DB query and returns ...
1
vote
2answers
131 views

Get Word Doc from SQL table, save temp file, edit that file, save new doc in a different table

I have Frankensteined the crap out of this method and just wanted to make sure of two things. I'd like to make sure that this is the best route to take. If it isn't, I would welcome with open arms ...
6
votes
3answers
134 views

Class Module to wrap up classic ADO call to SQL-server

I do a lot of copying excel vba code related to classic ADO from workbook to workbook, so I've decided to add the code to a Class Module in a single utility workbook and then in all other books just ...
3
votes
3answers
296 views

SQL Procedure that will calculate the Last Price paid for a certain Part

I am trying to write an SQL Procedure that will calculate the Last Price paid for a certain Part. ...
6
votes
2answers
110 views

Finding customer searches and graphing their items

One of the reports I am working on requires me to locate searches a customer makes, and graph them in certain manners. I have one query I'm particularly concerned about. This one takes the most ...
5
votes
1answer
59 views

Report for a restaurant showing hourly sales information for a given timespan

I have been tasked with creating a report for a high-volume restaurant that shows hourly sales information for a given timespan. When generating this report for a single day, it's very quick, but when ...
18
votes
1answer
56 views

Cross-posts from Stack Overflow to CR

What this code does it explained in detail in the top portion of the query. I have left out part of the list of language tags as I felt it was just noise. You can see the full collection here. You ...
12
votes
2answers
91 views

Query to count interactions between users

The source data represents interaction between people. Some are internal, others are external. The internals are recorded in the Users table (represented as the ...
8
votes
3answers
148 views

XML splitting query very slow

I have a query to split a delimited string into to multiple columns. The query is extremely slow and takes more than 5 minutes to handle 80444 rows. How can I improve execution time? ...
4
votes
1answer
158 views

4 database queries, multiple loops in loops, slow processing time

The octoberCMS(based on laravel) controller below is what I use to select the lowest price in 4 pricelists in the given daterange. Prices can vary and it's not guaranteed that the more expensive ...