The Stack Overflow podcast is back! Listen to an interview with our new CEO.

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
9
votes
3answers
996 views

How do you handle constantly changing database dimensions?

For the last two months or so I have been looking for solutions or practices to handle release management within databases. I'm looking for what people view as the best process for handling this. We ...
9
votes
2answers
3k views

How to safely run database migrations with multiple app instances?

We have an application that has a mix of both fast (< 1 second) and slow database migrations (> 30 seconds). Right now, we're running database migrations as a part of CI, but then our CI tool has ...
9
votes
1answer
448 views

Can relational algebra/calculus proofs be used to test/verify SQL?

Is it feasible or even possible to use relational algebra and/or relational calculus in the form of proofs to test/verify the correctness of SQL statements, functions, and stored procedures? It seems ...
8
votes
8answers
674 views

Is there a compelling reason why columns in SQL are nullable by default?

As a CS student, I've learned a decent number of programming languages over the years, most of which have had some concept of a "nullable" or "optional" type. Note that I'm not talking about null ...
8
votes
9answers
393 views

Is it unnecessary to learn the kind of data structures and objects inside sql only because we are using another language to access db indirectly?

Suppose we are using java or python to access the database. Then is it considered a wastage of time and unnecessary to learn the kind of data structures and objects being used inside sql? Please ...
8
votes
5answers
5k views

Two database fields for date and time - should they be merged?

In the following question, field and table names have been changed to protect their identities. If I have two database columns: MONKEY_DATE DATETIME NULL (with data e.g. 2012-05-14 00:00:00.000) ...
8
votes
2answers
8k views

Why should I use foreign keys in database? [closed]

In my 10+ years of experience in the IT field, I have never used foreign keys in any of my projects and I never felt the need. I did work with professional databases that had foreign keys constraints. ...
8
votes
4answers
864 views

Good idea to move logic out of SQL statements?

I'll preface this question by saying that I am very new to professional software dev. I work on a team that takes data in from other groups in my company and turns this data into reports usable by ...
8
votes
5answers
9k views

Is SQLite a sensible option for data logging?

I am trying to setup a small data-logging application under linux. The data arrives via serial port, and from there it should be pumped to logging and to graphical display. The logging functionality ...
8
votes
1answer
2k views

Using MySql 5.7 JSON columns for EAV

I am developing an e-commerce product and I have been able to implement all functionality and am left with allowing users to create additional attributes for a product. Right Now I have two options. ...
8
votes
1answer
6k views

How to translate SQL query into REST API requests?

Let's say I have a machine-readable description (such as in WADL, Swagger or RAML) of a REST API that provides interface to a database. My users submit queries about underlying database in form of ...
7
votes
7answers
8k views

Why do beginners still write SQL? Why do we not push them to use a higher level abstraction? [closed]

Every day I see SO questions with some variation on this theme (C#-ey pseudocode) method MyButtonClickHandler(){ string sql = "SELECT * FROM table WHERE column = " + myTextBox_Text sqlcommand cmd ...
7
votes
7answers
24k views

What is the best approach for database design with lots of columns?

I am writing a query based financial application. It lets the user to write complicated equations (much like WHERE part of an SQL query) and find companies matching those criteria. For the above, I ...
7
votes
6answers
2k 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 (...
7
votes
4answers
3k views

C# database communication technique

A few days ago, I read an answer stating that writing queries within your c# code are long gone. I'm not sure what the specific person meant with the comment but, it got me thinking. At the company ...
7
votes
7answers
421 views

Logistically, how do you manage embedded SQL?

My current application is running into issues with its ORM, and we're relying on some pretty hairy SQL to express queries that the ORM can't. Are there best practices in terms of how to manage the ...
7
votes
3answers
498 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 ...
7
votes
4answers
1k views

Direct database manipulation an anti-pattern?

Recently I learned that some teams have moved all their database manipulation to the actual database through the use of stored procedures. I thought that was pretty clever, since the database becomes ...
7
votes
2answers
4k views

How can I design a model layer without ORM, and when should I use it?

I'm a PHP developer who started with CodeIgniter. In it, designing models was easy: it seemed like each method defined in models was the equivalent of static methods in a normal, objected-oriented ...
7
votes
3answers
3k views

Managing SQL Stored Procedures' Version

I am working with a team of 5 persons. We are using SQL Server as our database. Since long time I want to store the Stored Procedures in SVN so that the versions can be maintained. Is there any tool ...
7
votes
1answer
14k views

Entity Framework vs. Linq to SQL? [closed]

I'm a little bit confused about these two terms "Linq to SQL" and "Entity Framework". I have the general idea that both of them somehow allow you to refer to database tables as .net objects or ...
7
votes
4answers
725 views

Storing Dynamic SQL in text files vs inline code

Our Architecture team is proposing a framework that would see our SQL queries moved from coded strings within our applications, into a file based system where we would invoke them with function calls. ...
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 some ...
7
votes
4answers
426 views

In retrospect, has it been a good idea to use three-valued logic for SQL NULL comparisons?

In SQL, NULL means "unknown value". Thus, every comparison with NULL yields NULL (unknown) rather than TRUE or FALSE. From a conceptional point of view, this three-valued logic makes sense. From a ...
7
votes
4answers
2k views

Encapsulation for complex queries

I tried asking this question first on StakOverflow in a more concrete manner, but after being pointed here I realized I should rephrase it in more general terms; however, you can still review the ...
6
votes
8answers
5k views

Are Bools/Bits Faster Than Integers

As the title suggests I'm wondering whether bools or bits are faster than using integers (1 and 0)? Specifically I thinking about their speed in CASE statements, even more specifically in T-SQL (...
6
votes
10answers
17k views

Is executing SQL through a WebService a really bad idea?

Typically when creating a simple tool or something that has to use a database, I go through the fairly long process of first creating a webservice that connects to a database then creating methods on ...
6
votes
5answers
16k views

Why SQL numeric precision is specified as the number of digits?

I understand that this might be a loaded question. For example, in Sql Server 2008, doing DECLARE @someNum NUMERIC(10); means that the range of the variable will be limited by 10 digits. In other ...
6
votes
2answers
5k views

What kind of unit tests should be written for a call to a database update function?

I'm dealing with a lot of functions like the one below and I'm unsure of what kind of unit tests should be written for them. public void UpdateEmployeeClockIn(int employeeId) { var sql = string....
6
votes
3answers
3k views

SQL RDBMS : one query or multiple calls

After looking around the internet, I decided to create DAOs that returned objects (POJOs) to the calling business logic function/method. For example: a Customer object with a Address reference would ...
6
votes
2answers
203 views

How would I make a suggestion for a change to the SQL standard

If I wanted to make a suggestion to a change to how the UPDATE statement works in SQL, how would I go about it? Is there a website for the next standard? I googled, but just kept getting the ...
6
votes
3answers
542 views

Types of databases

I read that there exists three main types of databases * Transactional (Client-Server database// OLTP database) * Decision support system (DSS) (Data warehouse database // Data mart // Reporting ...
6
votes
5answers
748 views

Data layer without ORM - how to work with it in efficient way?

I work on a big .NET project. It started more than 10 years ago, there is no ORM (NHibernate or EF I mean) and there is no chance to migrate to some widely used ORM (and for complex SQL queries ORM ...
6
votes
3answers
5k 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 ...
6
votes
2answers
192 views

Seeking benefits of both SQL and binary files in a data storage scheme

I'm looking for some input on how to keep certain benefits of both a binary file and a SQL database in an interesting data storage problem. I have a legacy custom binary file format that is ...
6
votes
2answers
506 views

Slight extension for SQL prepared statements syntax. Need advice

In my database abstraction library I am extending SQL prepared statements syntax to hint a parser with expected literal type. I take it as a very essential improvement, my reasoning you can read here. ...
6
votes
5answers
14k views

Externalising SQL Queries in Java

What are the advantages and disadvantages of externalising SQL queries in Java (or similar general-purpose object-oriented programming language. Externalizing means storing all SQL in a separate non-...
6
votes
4answers
1k views

What are the options when Sql generated by ORM needs tuning?

I am starting working on a web project using django. While researching whether to use Sqlalchemy or raw sql when django orm is not sufficient which is also a question I asked here Raw Sql vs ...
6
votes
4answers
927 views

Database structure to save 'tables'

I'm working on a project in which a user can create tables to save some data. What would be the best way to implememt this? Right now I'm doing it like this: Table 'tables' - contains stuff the user ...
6
votes
1answer
2k views

Clean Architecture - How to go from “Database Driven” to “Independent of Database” [closed]

I am looking for some clarity and hopefully some advice on writing clean architecture for a large system. My Companies "Web Solution" is +-10 years old, my job is to rewrite it. It is written across a ...
6
votes
2answers
2k views

Formatting SQL Based on an AST

I am working on my .NET open source project for generating SQL... Initially, I wrote the project so it spit out exactly one SQL format... ugly. I started using this project in the real world and ...
6
votes
2answers
12k 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 ...
6
votes
1answer
220 views

Replacing Dynamic SQL w/ a Filtering Pipeline?

As part of a work assignment I've long dealt with a giant, dynamic SQL-creating stored procedure that's used for retrieving a bunch of inventory items. This stored procedure accepts a ton of ...
6
votes
4answers
3k views

SQL Query and Java Constant Abuse?

I am currently charged with taking over a lot of code that is written with fields names placed into Java constant at the top of the file, and then the SQL queries constructed using string ...
6
votes
2answers
425 views

What are some useful resources for learning tuple calculus?

I've been trying to go over my tuple calculus over and over again. What's worse is that my professor and tutors are not much help during their hours. Does anyone know a good place to learn about ...
6
votes
2answers
2k views

Clustering Strings on the basis of Common Substrings

I have around 10000+ strings and have to identify and group all the strings which looks similar(I base the similarity on the number of common words between any two give strings). The more number of ...
5
votes
6answers
5k 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]". ...
5
votes
2answers
394 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
3answers
21k views

Sequel vs S-Q-L [duplicate]

Possible Duplicate: What's the history of the non-official pronunciation of SQL? I hear it every so often, "In sequel server...", and for some reason I cringe every time. Maybe it's because ...
5
votes
7answers
8k views

Getting started with C# and Databases [closed]

I want to start using Databases in my C# Applications, unfortunately there appears to be lots of different ways of going about this. Which is the 'best' way from a learning perspective? (IE: What is ...