Here are a few possibilities to get the conversation started: Escape all input upon initialization. Escape each value, preferably when generating the SQL. The first solution is suboptimal, because you then need to unescape each value if you want to use it in anything other than SQL, like outputting it on a web page. The second solut...
In a project of mine the SQL statements that are executed against a ms SQL server are failing for some unknown reason. Some of the code is already used in production so debugging it is not an easy task. Therefore I need a way to see in the database itself what SQL statements are used, as the statements are generated at runtime by the pro...
Consider the following 2 queries: select tblA.a,tblA.b,tblA.c,tblA.d from tblA where tblA.a not in (select tblB.a from tblB) select tblA.a,tblA.b,tblA.c,tblA.d from tblA left outer join tblB on tblA.a = tblB.a where tblB.a is null Which will perform better? My assumption is that in general the join will be better except in cases whe...
Updating an old ASP/Access site for a client (and hating it) - I need SQL to add a column to an existing table and set a default value. Doesn't work - any ideas? This works fine ALTER TABLE documents ADD COLUMN membersOnly NUMBER I want this to work: ALTER TABLE documents ADD COLUMN membersOnly NUMBER DEFAULT 0 Have googled and se...
How would you design a database to support the following tagging features: items can have a large number of tags searches for all items that are tagged with a given set of tags must be quick (the items must have ALL tags, so it's an AND-search, not an OR-search) creating/writing items may be slower to enable quick lookup/reading Idea...
I have never "hand coded" creation code for SQL Server and foreign key deceleration is seemingly different from SQL Server and Postgres...here is my sql so far: drop table exams; drop table question_bank; drop table anwser_bank; create table exams ( exam_id uniqueidentifier primary key, exam_name varchar(50), ); create table qu...
I need to be able to retrieve a CREATE TABLE script to recreate a specific table in a SQL Server (2000) database, programmatically (I'm using C#). Obviously you can do this with Enterprise Manager but I would like to automate it. I've been playing around with SQLDMO which offers a Backup command, but I'm not sure if this will give a SQ...
In Oracle you can limit the number of rows returned in an arbitrary query by filtering on the "virtual" rownum column. e.g. select * from all_tables where rownum <= 10 will return at most 10 rows. Is there a simple generic way to do something similar in Ingres? ...
What is the difference between UNION and UNION ALL. ...
If any of you have worked with a cool tool for viewing/querying the SQL Transaction logs, please let me know. This should show all the transactional sql statements which are committed or rolled back. For Database files, if it has some additional graphical capabilities like showing the internal Binary Tree structure of the indexes, that ...
I wrote a SQL function to convert a datetime value in SQL to a friendlier "n Hours Ago" or "n Days Ago" etc type of message. And I was wondering if there was a better way to do it. (Yes I know "don't do it in SQL" but for design reasons I have to do it this way). Here is the function I've written: CREATE FUNCTION dbo.GetFriendlyDateT...
I am designing a contact management system and have come across an interesting issue regarding modeling geographic locations in a consistent way. I would like to be able to record locations associated with a particular person (mailing address(es) for work, school, home, etc.) My thought is to create a table of locales such as the followi...
One of my co-workers claims that even though the execution path is cached, there is no way parameterized SQL generated from an ORM is as quick as a stored procedure. Any help with this stubborn developer? Edit: I won't pick an answer but instead vote up all the great replies ...
I need to come up with an analysis of simultaneus events, when having only starttime and duration of each event. Details I've a standard CDR call detail record, that contains among others: calldate (timedate of each call start duration (int, seconds of call duration) channel (a string) What I need to come up with is some sort of an...
Consider the Oracle "emp" table. I'd like to get the employees with the top salary with department = 20 and job = clerk. Also assume that there is no "empno" column, and that the primary key involves a number of columns. You can do this with: select * from scott.emp where deptno = 20 and job = 'CLERK' and sal = ( selec...
I have a table in a MySql database that stores user accounts. One of the columns, expires, stores an expiration date but defaults to NULL. I need to be able to remove an expiration date and set it back to the default value. Currently, all of my CRUD routines are written using MySqlCommand with parameters. Can this be done directly with ...
What object do you query against to select all the table names in a schema in Oracle? ...
I'm a bit stuck on this. Basically I want to do something like the following SQL query in LINQ to SQL: SELECT f.* FROM Foo f WHERE f.FooId IN ( SELECT fb.FooId FROM FooBar fb WHERE fb.BarId = 1000 ) Any help would be gratefully received. Thanks. ...
I need a real DBA's opinion. Postgres 8.3 takes 200 ms to execute this query on my Macbook Pro while Java and Python perform the same calculation in under 20 ms (350,000 rows): SELECT count(id), avg(a), avg(b), avg(c), avg(d) FROM tuples; Is this normal behaviour when using a SQL database? The schema (the table holds responses to a s...
I want to ask how other programmers are producing Dynamic SQL strings for execution as the CommandText of a SQLCommand object. I am producing parameterised queries containing user generated "WHERE" clauses and SELECT fields. Sometimes the queries are complex and I need a lot of control over how the different parts are built. Currently...