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).
2
votes
0answers
22 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
7 views
Database stored procedure with a “preview mode”
A fairly common pattern in the database application I work with is a need to create a stored procedure for a report or utility that has a "preview mode". When such a procedure does updates, this ...
0
votes
0answers
16 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
16 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
15 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
22 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
51 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
55 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
69 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
119 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
52 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
48 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
69 views
0
votes
3answers
41 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
2
votes
1answer
27 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
70 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
160 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
20 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
43 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
66 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
78 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
281 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
108 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
51 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
53 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
86 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
139 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
105 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
524 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
78 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
79 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
100 views
2
votes
1answer
356 views
2
votes
2answers
35 views
Sql Server: Getting the highest value based on two uniqueidentifiers - possible to avoid union?
I have two riscProfileTypeIds (both of type uniqueidentifier) of which I need the one with the highest sequence number
(the ...
3
votes
2answers
67 views
University Database
I am building a database for a hypothetical university. This is my SQL definition, with generated data added for my future use, such as demonstrating queries:
...
2
votes
1answer
145 views
DbGeography search query
I have a situation in which I need to get the closest road to a DbGeography point.
This takes 5 - 8 seconds to run in some cases.
I have a ...
0
votes
3answers
109 views
14
votes
1answer
102 views
Example script for teaching DDL and CRUD/DML operations
I have taught some SQL to others before, and I thought of making a script that has these attributes, for the purposes of teaching:
Fully functional to run on local DB instance with no fuss
Easy to ...
1
vote
1answer
53 views
Optimise MSSQL Connection .NET Helper Library
I recently posted my old .NET MSSQL Wrapper to be code reviewed: .NET MSSQL Wrapper
I had some good feedback and based on that, I was able to re-factor it quite a bit and ended up with the following ...
3
votes
1answer
151 views
.NET MSSQL Wrapper
Back in late 2012, I wrote the following C# .NET MSSQL Wrapper, which offered the following operations:
ExecuteScalar (for selecting single field from a row)
...
2
votes
1answer
73 views
T-SQL Constant Time Comparison
Explanation is in the function. Please point out any egregious errors:
...
1
vote
0answers
19 views
T-SQL Totp (Rfc6238 SHA2_256) Implementation
Using my previous function as a base I've come up the following:
...
3
votes
1answer
46 views
Returning two dates based on input
I created a function that will return 2 dates that depend on an input parameter.
When the parameter is:
1 - return start date of current week and end date of current week
2 - return start date of ...
4
votes
2answers
50 views
Basic Bill of Materials schema
I've been working on Bill of Materials mini schema for a while. At first I had single Part table where I've referenced itself. I was told it would be better to have ...
16
votes
2answers
1k views
Searching for bad Minecraft questions on Gaming.SE
To preface this post a little bit, I'll explain a little bit of the "backstory" here. The Stack Exchange site, Gaming.SE, also known as Arqade, often has a large influx of bad questions about the ...
1
vote
1answer
40 views
Managing employee photos in a database
I'm looking for a better way to manage employee photos for employee records in our database, and a way to query for the employee photos while having them on disk I have used a ...
3
votes
0answers
40 views
T-SQL Pbdfk2 (Rfc2898 SHA2_256) Implementation
Using my previous function as a base I've come up the following:
...
