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

131
votes
14answers
41k views

“Never do in code what you can get the SQL server to do well for you” - Is this a recipe for a bad design?

It's an idea I've heard repeated in a handful of places. Some more or less acknowledging that once trying to solve a problem purely in SQL exceeds a certain level of complexity you should indeed be ...
25
votes
2answers
7k views

What's the history of the non-official pronunciation of SQL?

SQL is officially pronounced as /ˌɛskjuːˈɛl/ like "S-Q-L", as stated in Beaulieu, Alan (April 2009). Mary E. Treseler. ed. Learning SQL (2nd ed.). Sebastapol, CA, USA: O'Reilly. ISBN 978-0-596-...
142
votes
13answers
79k views

Why use a database instead of just saving your data to disk?

Instead of a database I just serialize my data to JSON, saving and loading it to disk when necessary. All the data management is made on the program itself, which is faster AND easier than using SQL ...
23
votes
7answers
4k views

Why is filesystem preferred for logs instead of RDBMS?

Question should be clear from its title. For example Apache saves its access and error logs in files instead of RDBMS no matter on how large or small scale it is being utilized. For RDMS we just have ...
9
votes
3answers
3k views

XML vs SQL for small projects?

I'm working on a small project which will only have one user at a time since it's a local application (developed in WPF and C#). To store data I was thinking of using an XML file but I'm wondering if ...
91
votes
10answers
26k views

Why is “Select * from table” considered bad practice

Yesterday I was discussing with a "hobby" programmer (I myself am a professional programmer). We came across some of his work, and he said he always queries all columns in his database (even on/in ...
20
votes
5answers
2k views

What good reasons are there to capitalise SQL keywords?

There seem to be a lot of developers who write their SQL by capitalising the keywords: SELECT column FROM table INNER JOIN table ON condition WHERE condition GROUP BY clause HAVING ...
13
votes
3answers
779 views

Is reliance on parametrized queries the only way to protect against SQL injection?

All I have seen on SQL injection attacks seems to suggest that parametrized queries, particularly ones in stored procedures, are the only way to protect against such attacks. While I was working (back ...
12
votes
2answers
7k views

Why NoSQL over SQL? [duplicate]

Possible Duplicate: When would someone use MongoDB (or similar) over traditional RDMS? How well the SQL and NoSQL go head-to-head. I read somewhere that SQL databases are not well for data ...
14
votes
6answers
6k views

Code formatting SQL queries

Should I break SQL queries in different lines? For example in the project I am working on, we have a query that is taking 1600 columns! 1600 + tab chars. I wrote queries like this: "SELECT bla , ...
4
votes
2answers
4k views

Database model for keeping track of likes/shares/comments on blog posts over time

My goal is to keep track of the popular posts on different blog sites based on social network activity at any given time. The goal is not to simply get the most popular now, but instead find posts ...
2
votes
3answers
197 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 ...
159
votes
19answers
61k views

Why is naming a table's Primary Key column “Id” considered bad practice? [closed]

My t-sql teacher told us that naming our PK column "Id" is considered bad practice without any further explanations. Why is naming a table PK column "Id" is considered bad practice?
13
votes
5answers
9k views

What are questions to test a programmers knowledge of SQL? [closed]

What are questions to test a programmers knowledge of SQL? What is the answer to the question? And what would a lack of an correct answer mean in terms of time likely to understand the concept(s) ...
26
votes
6answers
56k views

SQL Triggers and when or when not to use them.

When I was originally learning about SQL I was always told, only use triggers if you really need to and opt to use stored procedures instead if possible. Now unfortunately at the time (a good few ...
60
votes
6answers
3k views

Is this a ridiculous way to structure a DB schema, or am I completely missing something?

I have done a fair bit of work with relational databases, and think I understand the basic concepts of good schema design pretty well. I recently was tasked with taking over a project where the DB was ...
13
votes
4answers
9k views

Maintaining referential integrity between a mobile client and a server

So I have a relatively simple system. A mobile client creates records in a sqlite database that I would like to have synced to a remote SQL server (that is shared with other mobile clients). So when I ...
9
votes
5answers
8k views

Foreign key restrictions -> yes or no? [closed]

I would like to hear some "real life experience" suggestions if foreign key restrictions are good or bad thing to enforce in DB. I would kindly ask students/beginners to refrain from jumping and ...
15
votes
2answers
5k views

SQLite with two python processes accessing it: one reading, one writing

I'm developing a small system with two components: one polls data from an internet resource and translates it into sql data to persist it locally; the second one reads that sql data from the local ...
7
votes
6answers
1k views

Is the “One Description Table to rule them all” approch good?

Long ago, I worked (as a client) with a software which use a centralized table for it's codified element. Here, as far as I remember, how the table look like : Table_Name (PK) Field_Name (PK) Code (...
4
votes
4answers
1k views

What are the arguments for/against Code Generation?

I'm in a position where we've got some brittle code that constructs SQL-like queries via text concatenation with parameters for inputs. The data source that it queries is fast and scalable but lacking ...
24
votes
6answers
3k views

Why SQL is not so widespread in large desktop applications?

As a software developer, I've worked on projects ranging from tiny home-made apps to medium-size enterprise applications. In nearly every project I used a database or regretted choosing not to use it ...
23
votes
3answers
14k 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 - ...
10
votes
6answers
1k views

What kind of Web development projects benefit from using ORM?

I'll start by saying that I've done 95% of my database work using SQL. Recently, I did some investigation of various ORMs, such as NHibernate and Doctrine. I can see the advantages of not needing to ...
7
votes
1answer
3k views

Raw Sql vs SqlAlchemy when Django ORM is not enough

In a django project, if a situation comes that Django ORM is not able to execute some complex queries then there are 2 options: Use Raw Sql queries Use SqlAlchemy I want to know if there are ...
3
votes
2answers
3k views

Why is NoSQL better for this scenario?

Hypothetical scenario: Let's say we are downloading JSON from Facebook with details of a user's friend's checkins, posts, etc... These come in as one document per friend per activity, so with 8 ...
12
votes
2answers
2k 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 ...
4
votes
6answers
3k views

Confusion about proper use of * wildcard in SQL [duplicate]

Suppose I was writing an address book application where the details of each contact were stored in an SQL database. A user wants to know all the details for a person whose name is "Bob [Someone]". ...
2
votes
4answers
3k views

Should I be learning Linq, Direct SQL Commands (in .net), EF or other? [closed]

Basically, I have a very good knowledge of plain old SQL coming from Classic ASP programming. Over the past couple of months, I have been learning C# and today was my first full day at MVC 3 (Razor) ...
2
votes
2answers
516 views

Where should SQL/DB Queries be encapsulated in a software system?

I frequently write small applications (either web based or otherwise) that require heavy database usage. i've attempted various ways of handling where to put the actual sql queries (sort of ad-hoc ORM ...
2
votes
1answer
767 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 ...
5
votes
2answers
264 views

Storing stop/start points in a database

Let's say I am storing start and stop points per user into a database table. For example... let's say in a chat system, a user only needs to see lines 24-293, and 500-512. (Let's say he logged off ...
5
votes
2answers
252 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 ...
4
votes
3answers
359 views

Rule of thumb for field sizes

What VARCHAR sizes should be used for first and last names, phone numbers (preferably international), email adresses, urls, dictionary words and file names? Edit: Zapped the introductory phrase to ...
4
votes
2answers
198 views

When and how much should we validate input when working with (C)AP storage?

Herein, we consider a server-client architecture. Suppose we keep the details flexible, but there's more than one layer of servers supporting our hypothetical service. The general question is "When ...
4
votes
3answers
539 views

Is it better to use a switch statement or database to look through 5,000 to 10,000 instances?

I have some JSON data in this format (5,000 instances for now): {"id":"123456","icon":"icon.png","caseName":"name of case"} I am allowing the user to search for the name of the case and then return ...
3
votes
1answer
687 views

How would one build a relational database on a key-value store, a-la Berkeley DB's SQL interface?

I've been checking out Berkeley DB and was impressed to find that it supported a SQL interface that is "nearly identical" to SQLite. http://docs.oracle.com/cd/E17076_02/html/bdb-sql/dbsqlbasics.html#...