Questions tagged [sql]

Structured Query Language (SQL) is a language for managing data in relational database management systems. This tag is for general SQL programming questions; it is not for Microsoft SQL Server (for this, use the sql-server tag), nor does it refer to specific dialects of SQL on its own.

Filter by
Sorted by
Tagged with
1
vote
5answers
120 views

How do B-Trees in databases to sort data based on different fields?

I know majority of databases uses B-Trees, and I can see how using a balanced binary tree will give fast sort times, for ordering by ID or whatever else the primary key is; but how are databases able ...
1
vote
2answers
47 views

How to design a process and adopt to changing business rules and sending alerts/notifications

Currently working on designing a process which demands me to send alerts like email notifications to the users which meet a business criteria(we can also call business rules). I want to make this ...
0
votes
2answers
38 views

Keeping up logical order in a messaging environment

My setting is the following (Please assume that this points are entirely rock solid unchangeable, some for good reasons some: "just because"): In a super scalable microservice environment i receive ...
17
votes
2answers
19k views

Best method to implement a filtered search

I would like to ask you, your opinion when it comes to implement a filtered search form. Let's imagine the following case: 1 Big table with lots of columns It might be important to say that this SQL ...
2
votes
2answers
190 views

Should POST and PUT handlers do pre flight check for 404?

When implementing GET on a resource is makes sense to respond with 404 if the resource cannot be found. For POST and PUT verbs it is a little more complicated. To respond with 404 in that case I ...
5
votes
1answer
109 views

Are legacy C# DataTable/SqlDataAdapters exponentially faster than SqlConnection/SqlCommand and/or LINQ to SQL DataContext.ExecuteCommand calls? [closed]

I can't believe DataTable/SqlDataAdapter massively beat out System.Data.Linq.DataContext.ExecuteCommand and ExecuteNonQuery (tried with both Stored Procedures and command text) and just straight ...
0
votes
1answer
41 views

Splitting 1-1 relationships across lots of diffrent tables?

I've come across a table that has about 200 columns. About 150 of these can be grouped into 5-10 tables that make real world "sense", and seeing as most of these entries are never used I figured it ...
-5
votes
0answers
25 views

How to load initial data, except SQL

Could someone please tell about ways to load initial data? Most obvious way is to use SQL script. But in case if ORM used in project, is it will be better to use Dao to write to database(or initial ...
0
votes
1answer
50 views

MVC database connection .NET

Introduction I intend to create a .NET WinForms application (this will be a toy application) which connects to a SQL Server database backend. I have designed and implemented an object-oriented model ...
1
vote
1answer
40 views

Query by NoSQL keys versus SQL hash

I am trying to decide between a NoSQL (MongoDB) or a SQL (Oracle) database for my project. This database will be used to store output files from a set of parameters. I can model this as a MongoDB ...
-2
votes
2answers
158 views

Best Practices for Managing SQL Code

I have a issue that keeps coming up as a developer using any .NET (C# mostly) and SQL. I personally feel it is bad practice to build SQL statements in code. There are too many case scenarios that may ...
12
votes
4answers
3k views

Storing n-gram data

I was hoping to brainstorm a little bit on the subject of storing n-gram data. In my project, I am trying to solve linguistic problems where I know all (n-1) data items and want to statistically guess ...
2
votes
1answer
339 views

Managing heterogeneous data

The underlying problem is one I seem to run into a lot. You have a collection of objects with some things in common, but other information about the objects may apply to some of them but not to others....
4
votes
1answer
276 views

Preferred approach to a matching process

I am currently in the process of putting together a matching algorithm. The matching process is as follows: Query data is used to perform a "lookup" on a set of reference data in order to determine ...
2
votes
1answer
89 views

Designing Models from complex ERP SQL tables

I have a project coming up where I'm supposed to upgrade an existing access app to a .net architecture. I'm wondering how to design object-oriented models from the giant tables and multiple ...
7
votes
5answers
836 views

Data layer without ORM - how to work with it in efficient way?

I work on a big .NET project. It started more than 10 years ago, there is no ORM (NHibernate or EF I mean) and there is no chance to migrate to some widely used ORM (and for complex SQL queries ORM ...
6
votes
4answers
572 views

Relational integrity without relations

I have about 15 years software engineering experience, writing business software with relational databases. Mainly SQL Server and Oracle. I've always been of the opinion that you should define ...
1
vote
2answers
181 views

What's more expensive on my server-side processing?

So I want to be processing my security protocols server-side, mainly token checks and then role authorisations for certain actions. I'm using c# .net core connecting to an MS SQL database and ...
3
votes
2answers
286 views

Database design - maintaining versions of an object

Suppose I've a Person object. This Person object in turn is having name, address, accountNumber and many other fields. Now my requirement is that I want to maintain multiple version for each person ...
1
vote
2answers
69 views

Bidirectional sync between two databases with differing schemas

I'm embarking upon a project where the goal is to create a bidirectional sync mechanism between two database systems with differing schemas. One is a custom CRM app build on MongoDB, the other is ...
1
vote
3answers
57 views

Having unique identifier for user while Updating user entity (by deleting and adding new one)

I am adding authentication to my web app and I consider the following case: I have USERS table which look like this simplified version Id {int} | Username {nvarchar(100)} | CollectedPoints {int} | ...
1
vote
0answers
29 views

What approach can be used to automate SQL views generation?

Let's say I have to create tons of different SQL views. Having actual and target table DDLs, is it possible to programmatically automate view creation? Example: I have 2 table DDLs: CREATE TABLE ...
1
vote
2answers
117 views

SQL Finding duplicates across databases across machines

I have multiple databases (MySQL, all identical except data, can delete arbitrarily from any) across multiple machines which contain large quantities of sentences (~ 1e+11 per database, ~ 4e+12 in the ...
4
votes
2answers
509 views

Is using spacing effectively equivalent to the long method code smell?

There's a common code smell involving long methods with the most common answer being that methods should be really small, less than 50 lines per say (or 20). I understand why this is because it ...
1
vote
3answers
109 views

How to make a data warehouse of a single full Database?

After learning about the data warehouse concepts whole day, I couldn't found a source that can actually say when you should not do data warehousing. In most tutorials I read, it says that DW is for ...
9
votes
2answers
11k views

Why should I use foreign keys in database? [closed]

In my 10+ years of experience in the IT field, I have never used foreign keys in any of my projects and I never felt the need. I did work with professional databases that had foreign keys constraints. ...
0
votes
0answers
154 views

Tradeoffs between Testability and Performance when filtering using SQL vs LINQ

My team has recently decided to adopt the CQRS pattern similar to the one used in the famous Microsoft EShopsOnContainers repository. In this solution, the queries are made in Query classes using ...
4
votes
4answers
395 views

Object Oriented Design and Relational Databases: Where to place behavior that pertains to more than one tables?

I have been trying to learn Object Oriented Design, but I find it very difficult to model objects that access a relational database and do JOINs. For example, I am building a REST API, it has User, ...
29
votes
1answer
55k views

JOIN vs. INNER JOIN and FULL OUTER JOIN

I know there is a difference between INNER JOIN and FULL OUTER JOIN, I can see it, but, what is the difference between the two following: JOIN ... ON... and INNER JOIN...ON... and still yet JOIN...ON.....
38
votes
3answers
37k views

self referencing tables, good or bad? [closed]

Representing geographical locations within an application, the design of the underlying data model suggests two clear options (or maybe more?). One table with a self referencing parent_id column uk - ...
1
vote
3answers
120 views

How do I query something when I don't know how its structured?

How does one write & run queries against a database/warehouse if they have no knowledge of how the data is structured? By "structure" I mean table names, fields, etc. Unless I have table names, ...
110
votes
4answers
74k views

why are noSQL databases more scalable than SQL?

Recently I read a lot about noSQL DBMSs. I understand CAP theorem, ACID rules, BASE rules and the basic theory. But didn't find any resources on why is noSQL scalable more easily than RDBMS (e.g. in ...
3
votes
1answer
119 views

How to Design for Monthly Database Refreshes?

I get a database every month with the prior month's data in it. There are three types of data: flag settings, demographic and transactional. I need to import the data into my standing database. ...
1
vote
2answers
101 views

How to keep track of db tables used in various apps [closed]

I have N applications using ORM, SQL-statements and stored procedures to access M tables from an MSSQL Server 2017. There are some shared tables that are used by various apps. Let's say I am forced to ...
11
votes
4answers
593 views

How to review sql changes more effectively? [duplicate]

From my experience, sql code changes almost always tend to be NOT incremental: someone creates a new stored procedure, or modifies an entire embedded sql query for optimization purposes, or creates a ...
3
votes
1answer
915 views

Is a series of OR or a single IN SQL statement better?

I'm using MS SQL 2008 R2. I have been using a series of OR and have an issue where my app periodically give the following error: Exception message: Timeout expired. The timeout period elapsed prior ...
-7
votes
6answers
14k views

How to not let anyone inspect elements of a webpage?

I was thinking to make some jQuery functions that will use ajax to control mysql. The whole idea is to make separate files of PHP (e.g query.php, addRow.php, update.php and delete.php) provide data to ...
0
votes
0answers
19 views

Manipulating a large dataset before inserting in Azure Search

I have an Azure SQL database with 20 million + records that i need to manipulate (including joining other tables, etc) before inserting into Azure Search. The procedure needs to be reproducible in ...
3
votes
7answers
2k views

Should I use entity framework for CRUD and let the database handle the complexity that comes with high end queries?

I am new to ef and liking it since it reduces the overhead of writing common queries by replacing it with simple add, remove functions. Agreed. Today I got into the argument with my colleague who has ...
0
votes
2answers
129 views

Is there an industry precedent for SQL pass-through endpoints? [closed]

There is an increasing interest toward GraphQL and Falcor across the Web. Each time I see an article or discussion about those, I get reminded about SQL, at least its DQL part. Obviously, sending raw ...
3
votes
3answers
3k views

Should I use foreign keys in my database if I use laravel?

I'm creating a website with Laravel for the first time. I checked relationships documentation today and it seems that Laravel just uses simple SQL queries. class User extends Eloquent { public ...
1
vote
1answer
122 views

Using Entity Framework with third party applications

In my current situation I am one of two developers at a manufacturer. We have been extending the functionality of existing applications (ERP and some others). These third party applications do not ...
1
vote
2answers
384 views

Using UNIQUE (col1, col2) or using a composite primary key for a relation table in SQLite/SQL?

i’m working on a project where i’m using SQLite and where i have a few relation tables looking similar to this one: CREATE TABLE tag_entry_relation ( id INTEGER PRIMARY KEY, tag_id INTEGER REFERENCES ...
2
votes
2answers
215 views

Is it better to have a single query that returns all related entities, or separate queries for each case?

Summary When writing methods to query for related entities, is it better to have a single method for all related entities or separate methods for each combination? Full Problem I'm writing ...
-1
votes
3answers
145 views

General SQL Question [duplicate]

Let's say a user goes to the log in page of my website and creates an account. Instead of entering their real name, they decide to write some sql code and try to gain access to my database. I have ...
0
votes
2answers
114 views

Ensure unicity of a 10 characters alphanumeric String

I have a requirement of developing a service that must generate each day few millions of random and unique alphanumeric String of length 10 (I can't increase this length, this is a customer ...
0
votes
2answers
4k views

Size of database a factor for Hibernate vs JDBC?

Do you know if the size of the database (number of tables used) is a factor when choosing between Hibernate and JDBC? Why or why not? In my particular case, I am evaluating Hibernate and JDBC for ...
0
votes
1answer
128 views

Database Design Structure

I am building a WebApp which has a list of Assets. I have a peculiar requirement and I am unsure of the best way to store the data in SQL. Requirement: My Asset Headers currently have a relation to ...
0
votes
1answer
115 views

Bridge/Join Table for a Many to Many Relationship between One Table and Multiple Others

Background I have a relational database an I want to have a table that contains flags. I want multiple tables in my database to have a many to many relationship with the flag table. Options I ...
2
votes
1answer
95 views

Can I reduce locking for this scenario using Hibernate and H2

My Java application uses Hibernate and H2. It process music files within folders. Typically for a folder it would read the files in the folder and store each file as a Song class in database, then ...

1 2 3 4 5 13