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

129
votes
19answers
35k 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?
117
votes
13answers
45k 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 ...
105
votes
14answers
14k 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 ...
84
votes
10answers
12k 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 ...
61
votes
16answers
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 ...
56
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 ...
53
votes
6answers
3k views

Why the Select is before the From in a SQL Query? [closed]

This is something that bothered me a lot at school. 5 years ago, when I learned SQL, I always wondered why we specify first the fields we want and then where we want them from. According to my idea, ...
47
votes
14answers
3k 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 ...
43
votes
11answers
43k 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 ...
36
votes
11answers
2k 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 ...
31
votes
3answers
1k 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 ...
26
votes
7answers
40k views

How do I make complex SQL queries easier to write?

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 ...
25
votes
8answers
5k views

Are NoSQL databases going to take the place of relational databases? Is SQL going away? [closed]

Does anyone have experience with NoSQL databases (CouchDB, MongoDB, Google's BigTable, Dynamo, Cassandra...)? What was it like? Was it actual production/on the job experience? What will happen to ...
22
votes
7answers
2k 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 ...
22
votes
5answers
930 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 ...
22
votes
2answers
1k 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
4k 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 ...
20
votes
11answers
3k 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 ...
20
votes
6answers
1k 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 ...
19
votes
2answers
4k 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 ...
19
votes
2answers
3k 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 ...
18
votes
9answers
3k 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 ...
18
votes
2answers
6k views

self referencing tables, good or bad?

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 - ...
18
votes
5answers
1k 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 ...
17
votes
8answers
6k 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 ...
17
votes
4answers
2k 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 ...
16
votes
9answers
4k views

Whether to put the business logic in Stored Procedure or Not?

There is always a debate over the topic - "Whether to put the business logic in Stored Procedure or Not?". If we decide not to use the ORM Tool and not to put the Business Logic in Stored Procedure ...
16
votes
4answers
440 views

Database abstraction — is it being overdone?

After being exposed to numerous database abstraction layers, I'm starting to wonder what the point is of every library inventing their own different paradigm to access data. Picking up a new DAL feels ...
16
votes
6answers
32k 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 ...
16
votes
2answers
2k views

Does F# offer anything particularly useful for database-driven web development?

My organization finally upgraded to MS Visual Studio 2010 this year. One of the big new features that Visual Studio 2010 offers is the F# programming language. I understand that F# offers a ...
15
votes
4answers
3k views

Databases: Where should the application logic run? [duplicate]

http://highscalability.com/blog/2010/11/9/facebook-uses-non-stored-procedures-to-update-social-graphs.html talks about how Facebook moved logic out of the database into the application in order to ...
15
votes
7answers
2k views

How do you version/track changes to SQL tables?

When working in a team of developers, where everyone is making changes to local tables, and development tables, how do you keep all the changes in sync? A central log file where everyone keeps their ...
15
votes
7answers
713 views

To Domain or not to Domain

The SQL92 and SQL99 standards define CREATE DOMAIN DDL constructs. Not all databases support this, or have a different name for it (SQL Server has User Defined Types, for example). These allow one to ...
14
votes
5answers
1k 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 ...
13
votes
13answers
1k views

Do SQL Devs create SQL queries using SQL Query Designers?

I was just curious if SQL Devs write their code freehand or do they make use of the visual query designer to generate queries? In the majority of the cases, the query designer can create most of ...
13
votes
3answers
29k 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 ...
13
votes
6answers
3k views

Interacting With Data Using Multiple Databases/Servers

All of the projects that I've had to deal with so far have only required a single database on a single server. I'm interested in learning more about how projects that need to scale move to multiple ...
13
votes
3answers
2k views

Best way to organize SQL queries stored in your code ? (or should you?) [closed]

I am sure I am not the only one who gets frustrated when they see a page of code littered with SQL queries. The ActiveRecord and other ORM patterns help mitigate a good amount of SQL used in a ...
12
votes
6answers
2k views

Do You Still Need Indexing After Database Normalization

After you have done a good normalization, do you still need to index the table? How is this going to affect the performance? Will it even affect the performance in some way after a good normalization? ...
12
votes
11answers
12k 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 ...
12
votes
3answers
692 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
2k 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 ...
11
votes
17answers
2k views

Is there still a need for writing SQL?

With so many ORM tools for most modern languages, is there still a use case for writing and executing SQL in a program, in a language/environment that supports them? If so why? For clarity: I'm not ...
11
votes
7answers
928 views

How can I get programmers to stop writing code vulnerable to SQL injection?

Sometimes you get busy and delegate small tasks to junior programmers. But if you don't pay close enough attention you find yourself with this kind of code in production: class DivtoggleController ...
11
votes
4answers
7k 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. ...
11
votes
5answers
7k 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) ...
11
votes
5answers
2k 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 ...
11
votes
4answers
1k views

TDD with SQL and data manipulation functions

While I'm a professional programmer, I've never been formally trained in software engineering. As I'm frequently visiting here and SO, I've noticed a trend for writing unit tests whenever possible ...
10
votes
9answers
4k views

Foreign key restrictions -> yes or no?

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 ...
10
votes
6answers
4k 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 , ...