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.
769
questions
242
votes
17
answers
102k
views
Is it good practice to always have an autoincrement integer primary key?
In my databases, I tend to get into the habit of having an auto-incrementing integer primary key with the name id for every table I make so that I have a unique lookup for any particular row.
Is this ...
- 3,008
241
votes
13
answers
193k
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 ...
- 5,850
230
votes
19
answers
149k
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?
217
votes
14
answers
108k
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 ...
162
votes
4
answers
112k
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 ...
- 1,739
128
votes
14
answers
43k
views
Is there any technical reason why, in programming, the default date format is YYYYMMDD and not something else?
Is there any engineering reason why is it like that? I was wondering in the case of a RDBMS that it had something to do with performance, since a "YEAR" is more specific than a "MONTH", for instance: ...
- 1,387
101
votes
10
answers
55k
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 ...
- 1,121
101
votes
9
answers
26k
views
Is it ever okay to use lists in a relational database?
I've been trying to design a database to go with a project concept and ran into what seems like a hotly debated issue. I've read a few articles and some Stack Overflow answers that state it's ...
- 951
99
votes
14
answers
43k
views
Is it considered an anti pattern to write SQL in the source code?
Is it considered an anti pattern to hardcode SQL into an application like this:
public List<int> getPersonIDs()
{
List<int> listPersonIDs = new List<int>();
using (...
- 7,119
81
votes
12
answers
148k
views
SQL: empty string vs NULL value
I know this subject is a bit controversial and there are a lot of various articles/opinions floating around the internet. Unfortunatelly, most of them assume the person doesn't know what the ...
- 2,244
73
votes
6
answers
12k
views
Why is the Select before the From in an SQL query? [closed]
This is something that bothered me a lot at school.
Five years ago, when I learned SQL, I always wondered why we first specify the fields we want and then where we want them from.
According to my ...
- 1,336
63
votes
15
answers
6k
views
Co-worker renamed all of my queries [closed]
I don't know if I should be very irritated or what. I single handedly built over 300 queries for a large database, and developed a naming convention so I could find them later. No one else in my ...
62
votes
6
answers
4k
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 ...
- 1,997
60
votes
16
answers
14k
views
Why did SQL injection prevention mechanism evolve into the direction of using parameterized queries?
The way I see it, SQL injection attacks can be prevented by:
Carefully screening, filtering, encoding input (before insertion into SQL)
Using prepared statements / parameterized queries
I suppose ...
- 8,247
58
votes
4
answers
61k
views
Why is SQL's BETWEEN inclusive rather than half-open?
Semi-open (or Half-Open, Half-Closed, Half-Bounded) intervals ([a,b), where xbelongs to the interval iff a <= x < b) are pretty common on programming, as they have many convenient properties.
...
- 2,904
54
votes
8
answers
20k
views
Is the use of NoSQL Databases impractical for large datasets where you need to search by content?
I've been learning about NoSQL Databases for a week now.
I really understand the advantages of NoSQL Databases and the many use cases they are great for.
But often people write their articles as if ...
- 862
54
votes
7
answers
119k
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 ...
- 651
52
votes
8
answers
14k
views
Is domain driven design an anti-SQL pattern?
I am diving in the domain driven design (DDD) and while I go more deeply in it there are some things that I don't get. As I understand it, a main point is to split the Domain Logic (Business Logic) ...
- 674
51
votes
14
answers
5k
views
Is SQL important if I know ORM frameworks well? [closed]
I don't have any serious experience in SQL and I even hate to write SQL instead of LINQ. I am happy enough with ORMs.
From the employers and sector view point, is it important to know SQL? Do I have ...
- 907
50
votes
9
answers
15k
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 ...
- 1,239
50
votes
3
answers
13k
views
Why is most SQL written in YELLING? [duplicate]
On MySQL at least, the following two queries are functionally identical:
select * from users limit 0, 1000;
SELECT * FROM users LIMIT 0, 1000;
However, most example sites and most developers I've ...
- 1,631
50
votes
3
answers
45k
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 ...
- 609
49
votes
12
answers
5k
views
Why don't relational databases support returning information in a nested format?
Suppose I'm building a blog that I want to have posts and comments. So I create two tables, a 'posts' table with an autoincrementing integer 'id' column, and a 'comments' table that has a foreign key '...
48
votes
2
answers
27k
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 ...
- 2,406
47
votes
9
answers
38k
views
Using JOIN keyword or not
The following SQL queries are the same:
SELECT column1, column2
FROM table1, table2
WHERE table1.id = table2.id;
SELECT column1, column2
FROM table1 JOIN table2
ON table1.id = table2.id;
And ...
46
votes
9
answers
4k
views
What do relational databases gain by setting a predefined data type for each column?
I'm working with an SQL database right now, and this has always made me curious, but Google searches don't turn much up: Why the strict data types?
I understand why you'd have a few different data ...
- 1,007
45
votes
5
answers
7k
views
SQL sanitizing in code with no user input
In my company's codebase, we hardcode sql queries without using an ORM.
Here's an example of a query we would run:
UPDATE client SET status="active" WHERE client_id=123
Since the query is ...
43
votes
6
answers
18k
views
Why is SQL the only database query language? [duplicate]
For general-purpose programming there are literally hundreds of programming languages. But for interacting/querying the databases, why is SQL pretty much the only used language?
- 529
43
votes
3
answers
49k
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 - ...
- 4,670
41
votes
6
answers
13k
views
Why not return dates as a string from the database?
In a typical web application, dates are retrieved from the database layer strongly typed (e.g. in c# as a System.DateTime as opposed System.String).
When a date needs to be expressed as a string (e....
- 26.8k
41
votes
6
answers
102k
views
How do I make complex SQL queries easier to write? [closed]
I'm finding it very difficult to write complex SQL queries involving joins across many (at least 3-4) tables and involving several nested conditions. The queries I'm being asked to write are easily ...
- 568
41
votes
3
answers
169k
views
Why do we need to put N before strings in Microsoft SQL Server?
I'm learning T-SQL. From the examples I've seen, to insert text in a varchar() cell, I can write just the string to insert, but for nvarchar() cells, every example prefix the strings with the letter N....
- 551
40
votes
6
answers
9k
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 ...
- 3,190
40
votes
2
answers
83k
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.....
- 745
39
votes
6
answers
5k
views
Why Isn’t SQL More Refactorable? [closed]
Everyone knows that new developers write long functions. As you progress, you get better at breaking your code into smaller pieces and experience teaches you the value of doing so.
Enter SQL. Yes, ...
- 477
37
votes
13
answers
8k
views
Are database unique indexes a mask on bad scripting?
I am working with a coworker on a project that uses Inductive Automation software. If you don't know what it is, all you need to know is it provides a drag-and-drop GUI designer (based in java swing) ...
35
votes
2
answers
16k
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-...
- 8,229
34
votes
6
answers
31k
views
At what size of data does it become beneficial to move from SQL to NoSQL?
As a relational database programmer (most of the time), I read articles about how relational databases don't scale, and NoSQL solutions such as MongoDB do.
As most of the databases I have developed ...
- 1,881
33
votes
11
answers
55k
views
Why is prefixing column names considered bad practice?
According to a popular SO post is it considered a bad practice to prefix table names. At my company every column is prefixed by a table name. This is difficult for me to read. I'm not sure the ...
- 11.1k
33
votes
7
answers
135k
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 ...
- 449
32
votes
11
answers
3k
views
Is there any material difference between queries joined by WHERE clauses, and queries using an actual JOIN?
In Learn SQL the Hard Way (exercise six), the author presents the following query:
SELECT pet.id, pet.name, pet.age, pet.dead
FROM pet, person_pet, person
WHERE
pet.id = person_pet.pet_id ...
- 200k
32
votes
4
answers
14k
views
How do you decide what kind of database to use?
I really dislike the name "NoSQL", because it isn't very descriptive. It tells me what the databases aren't where I'm more interested in what the databases are. I really think that this category ...
- 9,625
31
votes
4
answers
9k
views
Why many designs ignore normalization in RDBMS?
I got to see many designs that normalization wasn't the first consideration in decision making phase.
In many cases those designs included more than 30 columns, and the main approach was "to put ...
- 612
31
votes
6
answers
84k
views
Using a relational database vs JSON objects for event/activity data
I am working on a project where I am trying to decide between using a standard SQL relational database or JSON objects to store data about an event or activity.
The project will store data on ...
- 353
30
votes
9
answers
12k
views
Should a primary key be immutable?
A recent question on stackoverflow provoked a discussion about the immutability of primary keys. I had thought that it was a kind of rule that primary keys should be immutable. If there is a chance ...
- 403
30
votes
6
answers
2k
views
Does the advent of the SSD have any implication for database optimization?
Today I was browsing through a book on SQL Server optimization and it seemed that a certain amount of the ideas were based on a linear model of storage. As SSDs have a completely different storage ...
user23157
29
votes
3
answers
15k
views
Is SQL declarative?
I ask because so many of the questions I see in SQL amount to: "This is slow. How do I speed it up"? Or are tutorials stating "Do this this way and not that way as it's faster".
It seems to me that a ...
- 617
28
votes
6
answers
6k
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 ...
- 136k
28
votes
2
answers
5k
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 ...
- 415
27
votes
4
answers
15k
views
Is inline SQL still classed as bad practice now that we have Micro ORMs?
This is a bit of an open ended question but I wanted some opinions, as I grew up in a world where inline SQL scripts were the norm, then we were all made very aware of SQL injection based issues, and ...
- 863