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

5
votes
2answers
201 views

Why store flags/enums in a database as strings instead of integers? [on hold]

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 ...
-1
votes
1answer
36 views

Qt connection Sql Server (universal connection string)

I am developing a software to connect to sql server. The problem is, ¿i don't know wich sql server is used?, and my question is ¿if exist a universal connection String?. I read many cases and in ...
1
vote
0answers
44 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 ...
1
vote
2answers
33 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
61 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 ...
12
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 ...
-4
votes
0answers
34 views

How to check if a date is within a range in SQL? [closed]

I would like to compare/check if a date is within a range of dates. Essentially, I would like the date formats to match as well. Say @date1 is 20150520 DECLARE @startdate DATE DECLARE @enddate DATE ...
2
votes
2answers
160 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 ...
1
vote
1answer
92 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: ...
-2
votes
0answers
18 views

SQL code gives an foreign key error [migrated]

It's my first time posting in here and I'm a bit stuck with a small piece of SQL code. Hopefully one of you master programmers have a solution to it. I have set up a Wordpress childtheme and I am ...
2
votes
1answer
191 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
0answers
66 views

SQL Query based on URL [migrated]

I have a sql query which brings back information based on the Sessions Cookie. I want to make a link to a users profile and have a url like account.php?id=Danny Then i want to run a sql query but ...
1
vote
1answer
36 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, ...
137
votes
19answers
42k 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?
0
votes
1answer
38 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 ...
2
votes
2answers
103 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 ...
3
votes
2answers
237 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 ...
22
votes
9answers
7k 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 ...
-3
votes
1answer
139 views

Finding next free IP in a network

I have an IP network and want to automatically find a free IP address to provision a new server. Due to frequent server additions/removals the used IPs might be quite fragmented over the whole address ...
-4
votes
4answers
3k views

How to not let anyone inspect elements of a webpage?

I was thinking to make some jQuery functions that will use ajax to control mysql. The whole idea is to make separate files of PHP (e.g query.php, addRow.php, update.php and delete.php) provide data to ...
22
votes
2answers
2k 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 ...
2
votes
2answers
79 views

Is using triggers best solution for this scenario

A large SQL transactional database has more than 100 tables (and it will grow). One of them is called Order. Then, there is another table WorkLoad which derives from Order and many other joined table ...
1
vote
0answers
57 views

Where should business logic involving SQL go? [duplicate]

I have this question that's been bugging me all day. I have a function like this: public void voidUsersByUserId(List<Integer> userIds) { Query query = ...
1
vote
0answers
25 views

How to use an Address Gazatteer in an application that contains addresses [closed]

Say I had access to the post office address file (http://en.wikipedia.org/wiki/Postcode_Address_File) and wanted to cleanse about one million addresses. I am trying to think of an "algorithm" to do ...
3
votes
2answers
100 views

How to infer the result of a query?

To understand what a query does, I have always thought the following is the procedure on evaluating a query: Form the table as specified in the FROM clause. Pick the rows from that table as ...
10
votes
6answers
1k views

Why is SQL known as a relation-based / functional language?

We're learning that most languages are classified as either of the two, "relation based" or "high level". I've never used SQL before, but from reading its syntax it seems more like the ...
0
votes
2answers
36 views

SQL - for some attributes specific to different clients' users, how to handle schema?

I have clients, each of whom have an app with a bunch of users. Their user data could be pretty different, but there is also a lot of overlap. Ex: all their users have "gender" and "age" and plenty ...
1
vote
1answer
39 views

Object Storage where Object Type is variable

From time to time, we come across a problem where we must store an object, but we don't know in advance what kind of object we're going to store. I'm going to give a very simple example. Requirement: ...
1
vote
1answer
68 views

Sanity check SQL [closed]

Is there any tools/processes to check SQL does not contain any possible issues. For example we had an SQL ran that forgot to put a where clause on an update and mistakenly updated the entire table. ...
0
votes
0answers
55 views

How to deal with requests that don't conform to the existing data structure?

The system I'm working on has a list of assets that belong to different customers of the company. This data is saved on an SQL database. Removing irrelevant data, let's say the table is like this: ...
6
votes
7answers
320 views

How would you design a user database with custom fields

This question is around how should I design a database, it can be relational / nosql databases, depending on what will be the better solution Given a requirement where you'll need to create a ...
0
votes
1answer
66 views

Always set form input max length?

Background I'm looking to put together a single page web application with many form inputs. I've run into issues with other web pages where there is an ugly SQL error sent to the user if the ...
0
votes
1answer
158 views

Injectable, Poppable Stack

Background A project involves converting a relational expression map (rxm) to an XML/SQL statement. The map resembles: root > people, # "root" keyword starts the document ...
1
vote
1answer
348 views

Creating a online database creator

I've been thinking of creating an online database creator (think DabbleDB, Zoho Creator, Intuit Quickbase, etc.). I've been thinking of a couple solutions: Have 4 database tables: Tables, Rows, ...
5
votes
3answers
269 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 ...
1
vote
0answers
99 views

Preserving Pre-formatted Multi-Line Strings in Node.js Scripts

There is a lot I don't like about PHP, but one thing I love is multi-line strings: $query = <<<EOT select field1 ,field2 ,field3 from tableName where field1 = 123 EOT; ...
1
vote
2answers
833 views

Database Context and Singleton injection with IoC

All of the below relates to a ASP.NET c# app. I have a Singleton Settings MemoryCache that reads values from database on first access and caches these, then invalidates them using SQL Service Broker ...
4
votes
2answers
97 views

Use of NOLOCK on Microsoft SQL Server

The (NOLOCK) optimizer hint is well known by most SQL developers for setting the isolation level so that tables with rows or escalated page locks can be read. I've always known about the bad and ugly; ...
3
votes
2answers
76 views

Integration tests of SQL logic

In my daily work, I use a lot of SQL logic (oracle stored procedures and SQL functions). To limit the code regression on the implemented code I make what I call "integration scripts" : C# scripts that ...
1
vote
0answers
177 views

What is the “correct” way to store functions in a database?

Note: Yes, I know that storing functions in databases should be punishable by law. We are developing a financial web application using PostgreSQL, Sinatra and AngularJS. As you may have guessed, a ...
3
votes
1answer
222 views

Is a series of OR or a single IN SQL statement better?

I'm using MS SQL 2008 R2. I have been using a series of OR and have an issue where my app periodically give the following error: Exception message: Timeout expired. The timeout period elapsed prior ...
1
vote
3answers
298 views

Best OOP pattern to use for creating a commandline SQL API for a proprietary json based company datastore

We need to create a SQL commandline tool for querying a proprietary json based datastore. I'll be using java. Any suggestions on the primary OOP design pattern I should use for building this API? ...
1
vote
2answers
337 views

How should I compare two database tables - with SQL or using Java? [closed]

I have two tables with different structure. I should compare ID from 1st table with ID of the intermediate table and then comapre TXT field of the intermediate table with TXT field of the 2nd table. ...
2
votes
1answer
304 views

Should I use foreign keys in my database if I use laravel?

I'm creating a website with Laravel for the first time. I checked relationships documentation today and it seems that Laravel just uses simple SQL queries. class User extends Eloquent { public ...
3
votes
2answers
231 views

Software development - The industry & general trends / Bad practices [duplicate]

I am a Web Developer and part of a small team working on an abundance of projects. This is my first "actual" real company after graduating with a degree in computer Science and I have about 2 years ...
33
votes
10answers
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
36 views

Merging user activities into one stream with a common representation

My social network website currently has forum threads and posts gallery pictures albums on the users' profiles group discussions classifieds I would like to create one "stream" of activity. It ...
1
vote
2answers
809 views

SQL injection attacks, how do I test and secure coldfusion queries?

I'm running Coldfusion 8 and SQL server 2008. I've been building serveral forms that insert data into the database from external users, we have a custom built security module built by the guy who ...