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

1
vote
1answer
82 views

Web framework in pure SQL

I heard that SQL is Turing complete language (for example: http://stackoverflow.com/a/7580013/2604170) I am just curious if would be possible to create independent web framework like Ruby on rails or ...
19
votes
7answers
2k 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
9answers
3k views

Why don't RDBMSes return joined tables in a nested format?

For example, say I want to fetch a User and all of his phone numbers and email addresses. The phone numbers and emails are stored in separate tables, One user to many phones/emails. I can do this ...
1
vote
0answers
56 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 ...
-3
votes
0answers
26 views

Utilise SQL server tools [closed]

I have a question regarding SQL Server. My question is how can I utilise SQL Server tools to identify query optimisations, missing indexes etc?
-3
votes
2answers
37 views

Specifications from huge SQL script [closed]

I inherited a huge sql script which performs a variety of steps and is run on a regular basis. The script consists of 10 steps of inserts/updates with many joins. I also see repeated portions of logic ...
2
votes
1answer
75 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 ...
1
vote
0answers
34 views

Do I want an index if all I care to query is field not null [migrated]

In SQL, do all the common considerations about indexes apply if everything I'll ever want to query (in the where condition) is when a field is not null? Is there a special configuration of the index ...
1
vote
2answers
40 views

Partial Update of Database Table

I have a database table with numerous fields including "category". The source of this table is an xml file which the system receiving occasionally. Each xml file contains data for one category. The ...
1
vote
0answers
40 views

Storing/caching SQL query

If I have a very long-winded SQL query (that has multiple subqueries), but the result of which is unlikely to change from hour to hour (or even day-to-day, or possibly even week-to-week) is there some ...
1
vote
1answer
41 views

Is it a good practice to write distinct queries in a single stored procedure based on input parameters

I am going through the stored procedures written throughout our project and see that multiple distinct queries are written inside a single stored procedure and are called by passing different values ...
2
votes
1answer
56 views

Best practices for scope of returned data per stored procedure

I have the following situation: table1 ------------ ID Name Param1 Param2 Param3 RegistrationID 1 Test Value1 Value2 Value3 101 table2 ------------ RegistrationID LangID Value 101 ...
-5
votes
1answer
47 views

confused with ms sql nomenclature [closed]

Question 1. What is the language used in Microsoft SQL Server? Question 2. I get confused with the name Microsoft SQL Server since its a RDBMS why its name ends as "Server". Because server is used to ...
1
vote
1answer
83 views

What's the quickest and most maintainable way to implement SQL querying of disparate C++ variables?

I've recently joined a team working on radar observations. The team has access to a repository of software (written in C++) used for dealing with these observations, but their work is focused towards ...
3
votes
1answer
324 views

Separation of concerns between repository and service in DDD with complex entities

This probably seems as an example of opinion-based question, but I'm actually looking for rationale on how to decide correctly, I believe there is a correct solution that can be backed by solid ...
4
votes
10answers
8k 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 ...
1
vote
2answers
128 views

Is there a difference between SQL INSERT patterns

SQL inserts are usually documented like so: INSERT TableName (Column1, Column2) VALUES ('Value1', 'Value2') However, in TSQL it's also possible to write the (seemingly) same statement like so: ...
-3
votes
0answers
43 views

set() is the value in a key:value pair?

I'm working on dictionary comprehension for a database so that I can save various database tables as nested dictionaries for easier data manipulation. SQLAlchemy Core has a good tutorial with an ...
11
votes
4answers
8k views

Database History Table / Tracking Table

Currently I want to structure a tracking/history table like this: PrimaryKey - ID OtherTableId - fk fieldName - name of the field its tracking OldValue NewValue UserName CreateDateTime So ...
143
votes
19answers
45k 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?
2
votes
3answers
119 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 ...
3
votes
4answers
1k views

Database is performing slow, even all the tables are having normalization

An interviewer asked me this question: Tables are created with appropriate normalization rules, However the database is performing slow. [Ie.: The select, insert statements are taking time to do ...
1
vote
0answers
32 views

Adding fields a good use case for EAV? [duplicate]

Is being able to add fields quickly to a system a good use case for EAV? What would be the frequency or number that would justify using an EAV model?
2
votes
6answers
428 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 ...
1
vote
1answer
76 views

Knowing when a SQL table is done updating before Querying

I am using a self hosted windows service WEB API as the layer between my Windows 8 App and a SQL Server Database because Windows 8 apps can't directly connect to SQL server like win forms or pretty ...
4
votes
2answers
129 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 ...
2
votes
0answers
80 views

The best way to convert Dynamic SQL to C#/Linq while joining multiple tables

I am trying to find the best way to convert the following dynamic SQL snippet to C# / Linq. The question is complicated by the use of multiple tables within the dynamic SQL. I've examined the ...
4
votes
1answer
83 views

Status Bar Timetracking Design

I'm currently developing an application in ASP.NET using SQL as the backend database. In my header, I have a status bar that displays the current status of the user and a timer that shows how long ...
37
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 ...
33
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 ...
0
votes
1answer
60 views

How to handle rating? [closed]

I want to implement like/dislike system, but I don't know how to achieve best performance. I have to tables: blog posts and comments that need rating. Should I store all data in one rating table? Or ...
0
votes
0answers
16 views

One Enum vs Multiple Enums [duplicate]

I am developing an application where a user submits a mission and other users accept the mission. Pretty simple. I want to keep a track of the mission progress status and store it into a database. ...
3
votes
2answers
295 views

How do you unit test \ use TDD methods for ETL's and reporting projects?

ETL projects are projects created using an ETL (Extract - Transform - Load) tool such as SSIS, PowerCenter,etc These typically involve reading data from an external source, loading it to a staging ...
2
votes
3answers
116 views

Database view performance - good practice?

Often times at work we opt to create views in the database to expose the data that we want to work with instead of building some monster query in our code. Being somewhat new to this field my ...
1
vote
1answer
63 views

SQL - Designing a Threshold value table

I need to design an SQL table to be used for "threshold" or special values, which in general would have the following properties: - key - value - comparer (less than and greater than as a start, ...
2
votes
1answer
64 views

Storing values in SQL whose types are determined in runtime

I have come across a need of storing runtime determined values in a SQL database. For example, there is a GUI where a user can add new editable fields. So the user adds a field Name, chooses the ...
1
vote
1answer
95 views

When the result set doesn't matter should Left Join or Inner Join be used

When you have a query where you're joining from a non-null foreign key to the foreign table it doesn't matter if you use LEFT JOIN or INNER JOIN from a result standpoint (since there won't be any ...
4
votes
2answers
851 views

How deeply programmer should know about internals of database engine? [closed]

I am learning SQL Server 2012 along with C#. In the past, I took database class in college, and have good grasp on SQL language and can write queries. However, looking at SQL Server documentation (or ...
11
votes
2answers
503 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 ...
6
votes
3answers
330 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 ...
1
vote
2answers
39 views

Using a masterID in sql to merge different item types

I am designing tables in sql for a small ticketing system. One of the demands is that a user/agent can post a ticket to either a department or an agent. Now if I have a child parent table where each ...
0
votes
1answer
64 views

Do Temporary tables have any form of index in SQL?

Consider a simple query like this: SELECT * FROM DATA JOIN ( SELECT * FROM DATA )TEMPORARY_DATA ON TEMPORARY_DATA.DATA_IDN = DATA.DATA_IDN What is the performance of this? Is it O(n2) because ...
14
votes
5answers
3k 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 ...
2
votes
2answers
204 views

Implementing “Trending Items” using linear regression in SQL Server

I'm building an MVC .NET website where people can rate and review movies (similar to IMDb). I'm using SQL Server for the back-end. I'll be implementing a feature where the homepage displays trending ...
2
votes
1answer
202 views

Mental schema for SQL queries [closed]

While preparing SQL introductory material, I've ended asking myself about the line of thought followed by a developer when writing a query. I believe it could be too valuable from a beginner's point ...
0
votes
1answer
60 views

Is there any reason not to allow negative numbers to be entered into a JSP field labeled for positive numbers?

Our application, a fairly complicated money ledger, has a JSP field for entering a certain type of money due, balanced by payments made towards that amount due. In the application, this field is ...
7
votes
4answers
2k 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 ...
4
votes
2answers
471 views

Where to put a common database connection for my classes

I have several classes (Repositories) which do the task of saving/retrieving some objects in/from database; all of them need to establish a connection to one database. I thought in order to avoid ...
23
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 ...
24
votes
9answers
9k 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 ...