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)

0
votes
0answers
5 views

SQL query with nested subqueries

The following query is taking over 800ms to run, and returning 300 rows. ...
2
votes
1answer
18 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 ...
1
vote
2answers
50 views
+50

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
20 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: ...
0
votes
0answers
7 views

Associative table in SQL Server [migrated]

If I have 4 tables (Army, Navy, Air Force, Marines), all have serial number as primary key, and name. And I have a table operation which has op_num as primary key and op_name. I also have a table ...
1
vote
0answers
28 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
51 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
26 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
208 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
26 views

Get parent folder ids by folder id(s)

My Scenario: #TmpDistinctFolderIds table contains one or many FolderId. ...
2
votes
2answers
63 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
98 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
57 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
32 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
36 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 ...
0
votes
0answers
19 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
28 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
51 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
54 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
58 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
75 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
126 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
65 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
71 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
52 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
75 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
193 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 ...
0
votes
0answers
27 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
62 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
105 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 ...
5
votes
3answers
115 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
286 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
55 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
88 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
145 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
140 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 ...
4
votes
1answer
541 views

Deleting millions of rows from a MSSQL server table

This SQL query took 38 minutes to delete just 10K of rows. How can I optimize it? Index already exists for CREATEDATE from ...
5
votes
2answers
84 views

Using UPSERT to maintain word counts for subtitles

I will first explain the context. I want to read serie subtitles contained in .srt files. I have already done the part of extracting the words from the files (and how many times the word appears for a ...
4
votes
3answers
84 views

Central Database class

I have this web site I inherited as part of my job and it has some old code. I'd like to optimize it, but I don't really have the time to do it right, so I'm looking for some "low hanging fruit" to ...
5
votes
0answers
111 views

Executing dynamic SQL programmatically

Usage (all parameters are optional): ...