sql

SQL Server 2008 : Standard or SQL Express

Which is a better choice on a development box if you primarily develop Asp.Net applications and SSRS reports. I have never had to use the Express editions, so I don't really know the pros or cons. The cons I have listed for Standard+ editions are: toll it takes on system resources pain to attach database for projects pain to detach un...

Should I initialize my AUTO_INCREMENT id column to 2^32+1 instead of 0?

I'm designing a new system to store short text messages [sic]. I'm going to identify each message by a unique identifier in the database, and use an AUTO_INCREMENT column to generate these identifiers. Conventional wisdom says that it's okay to start with 0 and number my messages from there, but I'm concerned about the longevity of m...

Calculating variances on dates in T-SQL

Guys, I am trying to write a stored procedure in T-SQL (SQL Server) that will select records based on a date field, keeping a variance of minutes in mind. Something like this: CREATE PROCEDURE spGetCustomers(@DateRange DATETIME, @Variance int) AS -- The next line is where I need help -- I'm trying to subtract X amount of minutes from th...

MySQL multi-level parent selection/join question

Hi, I have a hopefully simple MySQL query question which is eluding me at late at night. I'm trying to do a SELECT which counts the number of instances of a set of data (orders) and groups those instances by a value which exists in a parent a couple levels above the order itself. For example: CREATE TABLE `so_test`.`categories` ( ...

Trigger to prevent infinite loop in a sql tree

I have node table with (NodeId, NodeName) and structure table (ParentNodeId, ChildNodeId). How can I write a trigger that check if an insert update or delete statement can cause infinite relation? ...

Persisting dynamic object structures

I envision that in an application we are shortly developing, there will need to be a means in which the user (developers) can specify their own custom datastructures. These structures will need to be persisted, and then reported against within the system. I know there are several way to achieve this i.e: Having your SQL database hold...

MySQL: How to find all IDs of children recursively?

I would like to get all IDs from children in a tree with MySQL only. I have a table like this: ID parent_id name 1 0 cat1 2 1 subcat1 3 2 sub-subcat1 4 2 sub-subcat2 5 0 cat2 Now I'm trying to get all child IDs for cat1 (2,3,4) recursively. Is there any way how to achieve that? Thanks for...

Weird trigger problem when I do an INSERT into a table

Hi folks, I've got a trigger attached to a table. ALTER TRIGGER [dbo].[UpdateUniqueSubjectAfterInsertUpdate] ON [dbo].[Contents] AFTER INSERT,UPDATE AS BEGIN -- Grab the Id of the row just inserted/updated DECLARE @Id INT SELECT @Id = Id FROM INSERTED END Every time a new entry is inserted or modified, I wish to update a si...

SQL select: two-dimensional select with variable column count

CREATE TABLE activities(activityid, name); CREATE TABLE activity_scores(activityid, studentid, score); CREATE TABLE students (id, name); Is there any way to write a single SELECT query that will produce one result for each student, in this format? studentid | studentname | activity1_score | activity2_score | activity3_score [...] ...

Are Update/Delete/Insert on the Linq Roadmap?

Like SQL, Linq is a great way to retrieve data from various sources, but to date I haven't heard anyone talking about the other elements that SQL provides, specifically update/insert/delete. The various DLinq providers all offer their own mechanisms, but it seems like at some point modification of a data source would become part of the l...

What is a better way to store status updates in a database?

I'm developing a web application that will enable users to post short status updates similar to Twitter. The only way I can think of storing these posts is to have a large "status_updates" table that stores EVERY user's status updates: -------------------------------------- | table: status_updates | ------------------------...

Django: Retrieve active threads by the time the last Post was created

Hallo people, I have two Models, Thread and Post, where one Thread can have many Posts. I want to retrieve the active threads by sorting by 'post_set.createtime'. In the end, I want to get exactly ten Threads that had the most recent activity. Is this possible using no own SQL? Thanks a lot in advance. [Copying the model definitions f...

Complex(?) SQL join query

I have 2 tables: 1) table Masterdates which contains all dates since Jan 1, 1900 2) table Stockdata which contains stock data in the form date, symbol, open, high, low, close, volume (primary key = date, symbol) This is what I'm looking to retrieve (presented here in CSV format) MDate,SDate,Symbol,Open,High,... 6/4/2001,6/4/2001,...

Adding redudant join conditions in Oracle results in a different plan

I have a common database joining situation involving three tables. One table, A, is the main table with a primary key named id. Tables B and C contain auxiliary data for entries and A, and each also has a column named id which is a foreign key pointing to A.id. Now, if I want all data from A, B and C in one query, I would write: SELECT ...

Executing Multiple Insert Statements, what's the Best Way?

I have a program in which I need to run multiple insert statements (dynamically generated) against a MS SQL Table. Right now, I do (pseudo-code): Loop Generate Insert Statement String SQLCommand.Text = Generated Insert Statement String SQLCommand.Execute(). End Loop Close Connection Is it better performance-wise to simply construct o...

Database consistency checking framework

I'm looking for a framework to utilize to integrate a number of database consistency checking rules into our system. This should basically be a automated test case runner for our database checks. Requirements: Easy to write new rules or checks Easy to run all rules, grouping of rule subsets would be a bonus Accurate and simple reporti...

looping over columns in rails

This is probably very easy, but I'm having a hard time figuring it out. I have a partial: <% for room in @scrape %> <tr id="page_<%= room.id %>"> <th scope="row" class="<%= cycle("spec", "specalt") -%>"><%=h room.name %></td> <td class="<%=current_cycle%>"><%=h room.day1 %></td> <td class="<%=current_cycle%>"><%=h room.da...

Best way to update in a db a list of mails

I have the following db schema. SQL> describe USERS; Name Null? Type ----------------------------------------- -------- ---------------------------- user_id NOT NULL NUMBER(38) name NOT NULL VARCHAR2(50) password ...

SQL: script to create country, state tables

Consider writing an application that requires registration for an entity, and the schema has been defined to require the country, state/prov/county data to be normalized. This is fairly typical stuff here. Naming also is important to reflect. Each country has a different name for this entity: USA = states Australia = states + territori...

T-SQL Problem

I have following table (SQL Server) Table name is LandParcels Blockid ParcelNo Stateorprivate ======================== 11001901 30 Deemana 11001901 35 Deemana 11001901 41 State 11001901 45 State 11001901 110 Private 11001901 111 Private 11001902 1 Deemana 11001902 11 Sta...