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
209
votes
19answers
122k 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?
202
votes
14answers
99k 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 ...
192
votes
13answers
143k 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 ...
190
votes
17answers
55k 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 ...
118
votes
14answers
34k 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: ...
96
votes
4answers
69k 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 ...
95
votes
10answers
51k 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 ...
94
votes
9answers
16k 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 ...
87
votes
13answers
30k 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 (...
72
votes
12answers
121k 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 ...
67
votes
6answers
6k 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 ...
63
votes
15answers
5k 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 ...
61
votes
6answers
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 ...
59
votes
14answers
13k 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 ...
51
votes
8answers
16k 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 ...
50
votes
14answers
4k 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 ...
46
votes
12answers
4k 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 '...
45
votes
9answers
33k 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 ...
45
votes
4answers
45k 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. ...
44
votes
9answers
3k 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 ...
44
votes
9answers
11k 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 ...
43
votes
8answers
8k 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) ...
43
votes
7answers
93k 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 ...
42
votes
6answers
98k 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 ...
41
votes
6answers
10k 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....
41
votes
3answers
6k 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 ...
39
votes
6answers
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, ...
36
votes
6answers
13k 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?
34
votes
3answers
35k 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 - ...
33
votes
3answers
130k 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....
32
votes
11answers
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 ...
32
votes
2answers
13k 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-...
32
votes
4answers
13k 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 ...
28
votes
6answers
4k 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 ...
28
votes
2answers
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 ...
28
votes
6answers
61k 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 ...
28
votes
2answers
14k 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 ...
28
votes
6answers
5k 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 ...
26
votes
9answers
7k 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 ...
26
votes
3answers
11k 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 ...
25
votes
11answers
43k 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 ...
25
votes
5answers
3k views

Multiple database accesses or one massive access?

What is a better approach when it comes to performance and optimal resource utilization: accessing a database multiple times through AJAX to only get the exact information needed when it is needed, or ...
25
votes
1answer
44k 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.....
25
votes
6answers
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 ...
25
votes
2answers
19k 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 ...
23
votes
11answers
5k views

My coworker created a 96 columns SQL table

Here we are in 2010, software engineers with 4 or 5 years or experience, still designing tables with 96 fracking columns. I told him it's gonna be a nightmare. I showed him that we have to use ...
23
votes
4answers
5k 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 ...
23
votes
2answers
21k views

SQL IN() versus OR

I was working with a query I wrote today had to change the code from the WHERE clause to use a IN(list of stuff) filter instead of using something like item_desc = 'item 1' OR item_desc = 'item 2' OR ...
23
votes
2answers
2k views

Internal-use websites: Is there a compelling case against SQLite?

Many web frameworks, such as Flask or Django use SQLite as their default database. SQLite is compelling because it's included in python, and administrative overhead is pretty low. However, most high ...
22
votes
4answers
3k views

Why not just make non-parameterized queries return an error?

SQL injection is a very serious security issue, in large part because it's so easy to get it wrong: the obvious, intuitive way to build a query incorporating user input leaves you vulnerable, and the ...