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
2answers
578 views

Registration: Email hash and verifying only one account per email

I'm using a registration function that hashes the email in PBKDF2 with a random and unique Salt each time. The hashes email and his salt are saved in the DB. No problem with that. The problem is that ...
0
votes
1answer
2k views

Best place to write SQL queries

I've been working on this project for my company. Currently I am embedding my SQL statements inside the program itself as and when they are needed. I do have two seperate classes - QueryBuilder ...
1
vote
1answer
109 views

Visual Studio DBProjects and problem with it running post-build SQL (file too big)

What I am doing is creating a DBProject.. importing a DB.. then creating scripts in the PostBuild that are DATA scripts for each table (so that the post build can populate the tables after the schema ...
2
votes
2answers
352 views

Best strategy in SQL

Recently, a colleague told me that it wasn't advisable to make conditions in the join clauses. Instead he suggested to make conditions in the where clause. He told me that the SQL engine was optimized ...
0
votes
2answers
212 views

ACID compliant Database that isn't NoSQL? [closed]

I'm not necessarily asking if a NoSQL database can be ACID compliant, which has been asked here: Is there any NoSQL that is ACID compliant? I'm wondering if we have a database either now or in the ...
1
vote
2answers
216 views

Are error codes wrong in SQL queries too?

I have an SQL query which does several things at once¹: it does multiple checks and then, if the checks pass, updates some data. Without entering in the domain-specific details, here's an imaginary ...
2
votes
3answers
159 views

Referencing countries via Foreign Key Constraint

To make things simple to explain: I've got two tables: Table1 and Countries. The Countries table contains a nice flag and some information, such as DisplayName, the ISO3166 ALPHA3 code and the phone ...
2
votes
2answers
315 views

Not using SQL Transactions

I am looking at a codebase where a developer never uses SQL transactions i.e. each update/insert into the SQL database is an atomic operation. I believe the codebase would benefit from transactions. ...
0
votes
1answer
641 views

TFS SQL Deployment Data Script

We are using TFS and SQL Server 2005 (looking to upgrade to SQL Server 2012 if that makes a difference). We store our database schema in a Visual Studio Database project (VS 2010). When code is ...
9
votes
4answers
1k views

Will working in IT limit your career prospects as a programmer? [duplicate]

I took a job working as an IT guy (SQL programming, helpdesk, etc.) because I had need of a job (to pay back student loans accumulated from school). I'm very happy to have a job, but I eventually ...
6
votes
4answers
7k views

why are noSQL databases more scalable than SQL? [duplicate]

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 ...
0
votes
3answers
180 views

How to maintain user relationship when user unfollows another user

I have a table of user relationship which stores who follows whom, the table structure is as follows -id -follower -following when someone follows any other user, I add a record with follower user ...
5
votes
1answer
587 views

OpenID implementation - PHP, Javascript, MySQL

I've started doing some research on the technologies that I will need for my website. I'm trying to implement a really simple website with OpenID user registration. The website will store a block of ...
10
votes
4answers
1k views

Why do relational databases only accept SQL queries?

As far as I know, most relational databases do not offer any driver-level API for queries, except a query function which takes an SQL string as an argument. I'm thinking how easier it would be if one ...
3
votes
1answer
757 views

Calling MSSQL stored procedure from Zend Controller ? Any other approaches?

MSSQL and DB, Zend as PHP Framework, I am using this way to call SP with I/P Parameters and to get O/p Parameters. It seems I am writing SQL code in PHP. Any other good approaches? $str1 = ...
1
vote
2answers
439 views

Why OTServs have an item cloning problem if the server crashes? [closed]

OTServs are open source MMORPGs with a huge community. Mostly all of them have a serious problem: if the server crashes, people can clone items. This is a dirty trick that can be executed because the ...
12
votes
6answers
1k 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? ...
15
votes
6answers
26k 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 ...
9
votes
7answers
3k 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 , ...
1
vote
2answers
158 views

Is NoSQL a good fit for a graphing app, and how can I compare different NoSql Servers?

I am traditionally a MsSQL or MySQL developer - I have been prototyping a new application that involves storing data from many targets. I have seen that the app appears to get slow after scaling to ...
1
vote
1answer
230 views

Database data structures for RESTful api

I am creating a RESTful API. I am struggling to decide on the best way to design my database tables around my resources. Initially, I though a table per resource would be a good way to go, but I'm ...
1
vote
1answer
396 views

Established coding standards for pl/pgsql code

I need to standardize coding practices for project that compromises, among others, of pl/pgsql database, that has some amount of nontrivial code. I look for: Code formatting guidelines, ...
6
votes
3answers
2k 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 ...
3
votes
2answers
1k views

Why is NoSQL better for this scenario?

Hypothetical scenario: Let's say we are downloading JSON from Facebook with details of a user's friend's checkins, posts, etc... These come in as one document per friend per activity, so with 8 ...
2
votes
1answer
468 views

Internationalization through SQL Database and performance issues

I'm using .Net technologies and because ASP.Net "instant translation" is not really easy to do with ResX, since it has to be compiled after every change, there are a few hacks available ...
4
votes
2answers
561 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
4answers
435 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 ...
3
votes
4answers
956 views

What has been your experience with SQL CLR for complex business logic?

So I thought I had a perfect use-case for a CLR SQL Procedure. I've search the Net for perhaps a similar example where data is retrieved, records added and updated. I have not looked at a SQL CLR ...
4
votes
2answers
173 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 ...
3
votes
1answer
543 views

Is Clojure a 3GL or a 4GL?

A bit of background (in case I'm mistaken)... I think I understand that (it's an oversimplification): manually entering codes into memory (or on a punchcard) is "first generation language" using ...
4
votes
1answer
297 views

Algorithm to denormalize database

I have built a QlikView script generator (QVS), that can be used to generate large script files for data imports from SQL Server to QlikView. QlikView does not like 'fully' normalised database ...
1
vote
1answer
177 views

Is Neo4j graph database sufficient to itself for all query types?

Neo4j eases relationship traversals => especially useful for a social network for instance. But are there any scenarios when it's advised to associate an additional relational database (or other ...
8
votes
4answers
3k 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 ...
1
vote
1answer
295 views

For PL/SQL, do large companies prefer ANSI SQL joins or old Oracle joins? [closed]

I am interviewing for a PL/SQL position with a large corporation. I will have to write a multiple choice exam. I am wondering whether the exam will likely use old-style joins (joining happens in the ...
2
votes
2answers
2k views

SQL Auto Formatting and Auto Capatilize in Vim

I'd like to use vim for writing SQL queries, but I haven't figured out yet how to get two key features that I use all the time in programs like SQL Yog. I usually just start up an instance of Yog in ...
1
vote
1answer
465 views

Static Tables vs Dynamic Entity Attributes Storage Configurations

I am trying to figure out a better way to decide table storage configurations whether it will use static configurations using tables or dynamic configurations using EAV (entity-attribute-value) When ...
4
votes
2answers
458 views

Best practice to represent SQL in program source code

Where SQL statements are executed from a program, what is the best practice to store the statement text? Most often it is in a read only string, in the source file it is executed in. I think a ...
8
votes
4answers
590 views

Should database queries be abstracted out of the page itself?

When writing page generation in PHP, I often find myself writing a set of files littered with database queries. For example, I might have a query to fetch some data about a post directly from the ...
121
votes
19answers
29k 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?
7
votes
4answers
348 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. ...
22
votes
5answers
894 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 ...
4
votes
3answers
701 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 ...
0
votes
1answer
232 views

Concurrency checking with Last Change Time

I have an app with the following three tables Email (emailNumber, Address) Recipients (reportNumber, emailNumber, lastChangeTime, status) Report (reportNumber, reportName) I have a C# application ...
-1
votes
1answer
248 views

Address search from large text file

Basically I want to develop a Address lookup(part of my project) using C# (and I can use SQL if necessary). I have a very large text file which have all the UK address and postcodes. Addresses needs ...
12
votes
3answers
20k 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 ...
1
vote
1answer
254 views

Structure of a correctly implemented JTable with TableModel and Listeners?

I am pretty new to Java and its JTables and this is where I am struggling at the moment. I need to create a GUI which shows me results of a sql query like SELECT * FROM tblPeople WHERE name='Doe'. My ...
1
vote
5answers
1k views

Is it Considered Good SQL practice to use GUID to link multiple tables to same Id field?

I want to link several tables to a many-to-many(m2m) table. One table would be called location and this table would always be on one side of the m2m table. But I will have a list of several tables ...
6
votes
2answers
1k views

Best method to implement a filtered search

I would like to ask you, your opinion when it comes to implement a filtered search form. Let's imagine the following case: 1 Big table with lots of columns It might be important to say that this SQL ...
-1
votes
1answer
1k views

How to use PostgreSQL on AWS - Ubuntu 11.10 [closed]

I'm extremely new to cloud-computing, Linux, and PostgreSQL, so if this is a stupid question, I apologize. I've managed to create an m1.large instance running Ubuntu 11.10, connect via Putty SSH, and ...
0
votes
0answers
110 views

Benchmarking ORM associations

I am trying to benchmark two cases of self referential many to many as described in datamapper associations. Both cases consist of an Item clss, which may require many other items. In both cases, I ...