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.

learn more… | top users | synonyms

0
votes
0answers
22 views

Optimizing instant notifications

Scenario To deliver instant notifications to the client, I'm sending an AJAX request to a PHP page that checks if there are new notifications: if there are, it outputs them, otherwise it sleeps for ...
1
vote
1answer
84 views

Web framework in pure SQL

I heard that SQL is Turing complete language (for example: http://stackoverflow.com/a/7580013/2604170) I am just curious if would be possible to create independent web framework like Ruby on rails or ...
1
vote
0answers
60 views

Why does convention say DB table names should be singular but RESTful resources plural?

It's a pretty established convention that database table names, in SQL at least, should be singular. SELECT * FROM user; See this question and discussion. It's also a pretty established convention ...
-3
votes
0answers
26 views

Utilise SQL server tools [closed]

I have a question regarding SQL Server. My question is how can I utilise SQL Server tools to identify query optimisations, missing indexes etc?
-3
votes
2answers
37 views

Specifications from huge SQL script [closed]

I inherited a huge sql script which performs a variety of steps and is run on a regular basis. The script consists of 10 steps of inserts/updates with many joins. I also see repeated portions of logic ...
2
votes
1answer
79 views

What is the best way to store a formula in a database for a table driven formula? [closed]

So I am changing my code over to a table driven formula system to make it sustainable in the long term. So basically I have 5 foreign keys that will get me to a single formula to use. However I'm ...
1
vote
0answers
34 views

Do I want an index if all I care to query is field not null [migrated]

In SQL, do all the common considerations about indexes apply if everything I'll ever want to query (in the where condition) is when a field is not null? Is there a special configuration of the index ...
1
vote
2answers
40 views

Partial Update of Database Table

I have a database table with numerous fields including "category". The source of this table is an xml file which the system receiving occasionally. Each xml file contains data for one category. The ...
1
vote
0answers
40 views

Storing/caching SQL query

If I have a very long-winded SQL query (that has multiple subqueries), but the result of which is unlikely to change from hour to hour (or even day-to-day, or possibly even week-to-week) is there some ...
2
votes
1answer
56 views

Best practices for scope of returned data per stored procedure

I have the following situation: table1 ------------ ID Name Param1 Param2 Param3 RegistrationID 1 Test Value1 Value2 Value3 101 table2 ------------ RegistrationID LangID Value 101 ...
1
vote
1answer
41 views

Is it a good practice to write distinct queries in a single stored procedure based on input parameters

I am going through the stored procedures written throughout our project and see that multiple distinct queries are written inside a single stored procedure and are called by passing different values ...
-5
votes
1answer
47 views

confused with ms sql nomenclature [closed]

Question 1. What is the language used in Microsoft SQL Server? Question 2. I get confused with the name Microsoft SQL Server since its a RDBMS why its name ends as "Server". Because server is used to ...
1
vote
1answer
84 views

What's the quickest and most maintainable way to implement SQL querying of disparate C++ variables?

I've recently joined a team working on radar observations. The team has access to a repository of software (written in C++) used for dealing with these observations, but their work is focused towards ...
-3
votes
0answers
43 views

set() is the value in a key:value pair?

I'm working on dictionary comprehension for a database so that I can save various database tables as nested dictionaries for easier data manipulation. SQLAlchemy Core has a good tutorial with an ...
2
votes
3answers
119 views

At which point using string-based enums can become a real problem?

This is a direct follower to this question. An answer given to that questions says (my understanding), that nowadays using string-based enums instead of integers isn't a much problem, because hosting ...
1
vote
0answers
32 views

Adding fields a good use case for EAV? [duplicate]

Is being able to add fields quickly to a system a good use case for EAV? What would be the frequency or number that would justify using an EAV model?
2
votes
6answers
435 views

What is faster? Using REST API or querying a database directly?

What is faster performance wise? Creating a REST API and having your web app use the REST API to do all interactions with your database OR querying your database directly (i.e. using whatever typical ...
4
votes
2answers
129 views

Are (basic) SQL queries semantically equivalent to Higher Order Functions?

Is SQL basically a domain specific instance of map + fold + filter? It seems to me that the following SQL: SELECT name FROM fruits WHERE calories < 100 is just syntactic sugar for the ...
2
votes
0answers
81 views

The best way to convert Dynamic SQL to C#/Linq while joining multiple tables

I am trying to find the best way to convert the following dynamic SQL snippet to C# / Linq. The question is complicated by the use of multiple tables within the dynamic SQL. I've examined the ...
4
votes
1answer
83 views

Status Bar Timetracking Design

I'm currently developing an application in ASP.NET using SQL as the backend database. In my header, I have a status bar that displays the current status of the user and a timer that shows how long ...
0
votes
1answer
60 views

How to handle rating? [closed]

I want to implement like/dislike system, but I don't know how to achieve best performance. I have to tables: blog posts and comments that need rating. Should I store all data in one rating table? Or ...
0
votes
0answers
16 views

One Enum vs Multiple Enums [duplicate]

I am developing an application where a user submits a mission and other users accept the mission. Pretty simple. I want to keep a track of the mission progress status and store it into a database. ...
2
votes
3answers
117 views

Database view performance - good practice?

Often times at work we opt to create views in the database to expose the data that we want to work with instead of building some monster query in our code. Being somewhat new to this field my ...
2
votes
1answer
64 views

Storing values in SQL whose types are determined in runtime

I have come across a need of storing runtime determined values in a SQL database. For example, there is a GUI where a user can add new editable fields. So the user adds a field Name, chooses the ...
1
vote
1answer
95 views

When the result set doesn't matter should Left Join or Inner Join be used

When you have a query where you're joining from a non-null foreign key to the foreign table it doesn't matter if you use LEFT JOIN or INNER JOIN from a result standpoint (since there won't be any ...
6
votes
3answers
331 views

Do common relational database systems work 'out of the box'? [closed]

I'm quite new to software development and whenever I come across small projects which involve storing (relational) data I always ask myself if something like a micro-blog (or any other project with ...
11
votes
2answers
504 views

Why store flags/enums in a database as strings instead of integers?

I've been browsing SQL dumps of some famous CMSes, including Drupal 7, Wordpress (some quite very old version), and some custom application based on Python. All of these dumps contained data with ...
1
vote
1answer
76 views

Knowing when a SQL table is done updating before Querying

I am using a self hosted windows service WEB API as the layer between my Windows 8 App and a SQL Server Database because Windows 8 apps can't directly connect to SQL server like win forms or pretty ...
1
vote
2answers
39 views

Using a masterID in sql to merge different item types

I am designing tables in sql for a small ticketing system. One of the demands is that a user/agent can post a ticket to either a department or an agent. Now if I have a child parent table where each ...
0
votes
1answer
64 views

Do Temporary tables have any form of index in SQL?

Consider a simple query like this: SELECT * FROM DATA JOIN ( SELECT * FROM DATA )TEMPORARY_DATA ON TEMPORARY_DATA.DATA_IDN = DATA.DATA_IDN What is the performance of this? Is it O(n2) because ...
1
vote
2answers
128 views

Is there a difference between SQL INSERT patterns

SQL inserts are usually documented like so: INSERT TableName (Column1, Column2) VALUES ('Value1', 'Value2') However, in TSQL it's also possible to write the (seemingly) same statement like so: ...
1
vote
1answer
63 views

SQL - Designing a Threshold value table

I need to design an SQL table to be used for "threshold" or special values, which in general would have the following properties: - key - value - comparer (less than and greater than as a start, ...
0
votes
1answer
61 views

Is there any reason not to allow negative numbers to be entered into a JSP field labeled for positive numbers?

Our application, a fairly complicated money ledger, has a JSP field for entering a certain type of money due, balanced by payments made towards that amount due. In the application, this field is ...
4
votes
2answers
475 views

Where to put a common database connection for my classes

I have several classes (Repositories) which do the task of saving/retrieving some objects in/from database; all of them need to establish a connection to one database. I thought in order to avoid ...
1
vote
0answers
57 views

Where should business logic involving SQL go? [duplicate]

I have this question that's been bugging me all day. I have a function like this: public void voidUsersByUserId(List<Integer> userIds) { Query query = ...
3
votes
2answers
302 views

How do you unit test \ use TDD methods for ETL's and reporting projects?

ETL projects are projects created using an ETL (Extract - Transform - Load) tool such as SSIS, PowerCenter,etc These typically involve reading data from an external source, loading it to a staging ...
22
votes
2answers
2k views

NoSQL within SQL Server

This question is not about the difference between SQL and NoSQL. I am looking for some rationale for something that really does not make sense to me at the moment (maybe because of my lack of ...
3
votes
2answers
115 views

How to infer the result of a query?

To understand what a query does, I have always thought the following is the procedure on evaluating a query: Form the table as specified in the FROM clause. Pick the rows from that table as ...
0
votes
2answers
40 views

SQL - for some attributes specific to different clients' users, how to handle schema?

I have clients, each of whom have an app with a bunch of users. Their user data could be pretty different, but there is also a lot of overlap. Ex: all their users have "gender" and "age" and plenty ...
1
vote
1answer
53 views

Object Storage where Object Type is variable

From time to time, we come across a problem where we must store an object, but we don't know in advance what kind of object we're going to store. I'm going to give a very simple example. Requirement: ...
1
vote
1answer
79 views

Sanity check SQL [closed]

Is there any tools/processes to check SQL does not contain any possible issues. For example we had an SQL ran that forgot to put a where clause on an update and mistakenly updated the entire table. ...
0
votes
0answers
57 views

How to deal with requests that don't conform to the existing data structure?

The system I'm working on has a list of assets that belong to different customers of the company. This data is saved on an SQL database. Removing irrelevant data, let's say the table is like this: ...
2
votes
2answers
99 views

Is using triggers best solution for this scenario

A large SQL transactional database has more than 100 tables (and it will grow). One of them is called Order. Then, there is another table WorkLoad which derives from Order and many other joined table ...
6
votes
7answers
465 views

How would you design a user database with custom fields

This question is around how should I design a database, it can be relational / nosql databases, depending on what will be the better solution Given a requirement where you'll need to create a ...
0
votes
1answer
103 views

Always set form input max length?

Background I'm looking to put together a single page web application with many form inputs. I've run into issues with other web pages where there is an ugly SQL error sent to the user if the ...
0
votes
1answer
159 views

Injectable, Poppable Stack

Background A project involves converting a relational expression map (rxm) to an XML/SQL statement. The map resembles: root > people, # "root" keyword starts the document ...
5
votes
3answers
347 views

SQL - Algorithm for finding availability of a resource

I'm having trouble creating a mysql compatible algorithm for this. Background App with mysql, perl and JS. It's a booking system where each booking is comprised of a start, end and qty. Start and ...
1
vote
0answers
197 views

Preserving Pre-formatted Multi-Line Strings in Node.js Scripts

There is a lot I don't like about PHP, but one thing I love is multi-line strings: $query = <<<EOT select field1 ,field2 ,field3 from tableName where field1 = 123 EOT; ...
1
vote
0answers
225 views

What is the “correct” way to store functions in a database?

Note: Yes, I know that storing functions in databases should be punishable by law. We are developing a financial web application using PostgreSQL, Sinatra and AngularJS. As you may have guessed, a ...
3
votes
2answers
89 views

Integration tests of SQL logic

In my daily work, I use a lot of SQL logic (oracle stored procedures and SQL functions). To limit the code regression on the implemented code I make what I call "integration scripts" : C# scripts that ...