How do I optimize table after delete many records
I deleted many records from my table but the DB size (Firebird) left the same. How do I decrease it? I am looking for something similar to vacuum in PostgreS. ...
sql
What MySQL datatype & attributes should be used to store large amounts of html formatted data?
I'm setting up a database using PHPMyAdmin and many fields will be large chunks of HTML. What MySQL datatype & attributes should be used for fields that store large amounts of HTML data? ...
When to use EXCEPT as opposed to NOT EXISTS in Transact SQL?
Hi, I just recently learned of the existence of the new "EXCEPT" clause in SQL Server (a bit late, I know...) thru reading code written by a coworker. It truly amazed me! But then I have some questions regarding its usage: when is it recommended to be employed? Is there a difference, performance-wise, between using it versus a correlate...
Stream with a lot of UPDATEs and Postgres
I'm quite a newbie with PostgreSQL optimization and chosing whatever's appropriate job for it and whatever's not. So, I want to know whenever I'm trying to use PostgreSQL for inappropriate job, or it is suitable for it and I should set everything up properly. Anyway, I have a need for a database with a lot of data that changes frequentl...
Out of a sample of 100 demands what else was asked for?
I am doing some work on an inbound call demand capture system where each call could have one or more than one demands linked to it. There is a CaptureHeader table with CallDate, CallReference and CaptureID and a CaptureDemand table with CaptureID and DemandID. EDIT: I have added some representative data to show what would be expected i...
Best SQL Server isolation level for web application?
We have a website using ASP.NET MVC and SQL Server 2008 and we are using the default transactionscope isolation level which is Serializable. But it makes the application unusable if any transaction is opened as we have a table that being used by almost everything and it runs like select * from table1 where id = 1 So I think it locks ...
Design of Databases for storing the details of the recurrent occurrence of an event
I need to implement a feature similar to the one provided by Microsoft Outlook to make your meeting appointment recurrent. I am trying to figure out the optimized Database design that I will be requiring for implementing this feature. The requirement is something like that each run or task entered by the user will also be applicable fo...
Sqlite3: Need to Cartesian On date
I have a table which is a list of games that have been played in a sqlite3 database. The field "datetime" is the a datetime of when game ended. The field "duration" is the number of seconds the game lasted. I want to know what percent of the past 24 hours had at least 5 games running simutaniously. I figured out to tell how many game...
Denormalizing Data (Maybe A Pivot?)
I have a simple table containing Student Numbers and corresponding Teacher Numbers, and I need to denormalize it for input to a legacy system. For example, here's what the data looks like now: StudNumber TeacherNumber 445 57315 445 88584 445 95842 858 88115 858 65443 858 57315 858 211...
Update table without using Cursor
I have data like this I want to update minP, maxP and agvP columns on the basis of Id. My desired result is; Please help. ...
SQL Server Where Clause Case Statement?
I have a Where Clause that checks the existence of rows in a subquery, but I only want to execute that check if a bit is set to 1. So for example: Select * from Listing l Where l.IsDeleted=1 AND CASE WHEN @MustHasPicture = 1 THEN ( EXISTS ( SELECT NULL AS [EMPTY] FROM [dbo].[ListingPictures] AS [lp] INNER JOIN...
MySQL Join Syntax
I'm having problems understanding the MYSQL join syntax. I can't seem to manipulate it for my purpose. I have had to use work arounds for months which have resulted in extra queries just for retrieving one extra column. Scenario: I have two tables.. Table.Stories - containing stories and the user ID of which whom added it. id, story,...
can i get procedure sample in oracle ?
hi i try to understand procedure..... but still dont..... i need simple sample for procedure. i need procedure that i'll insert Fname and Lname, and i get table with the result search how i can do it ? i need package ????..... or cursor ????........ work on oracle 10g thank's in advance ...
Two SUM queries together
I have a table "Events" and a table “Slots” where Slots.SlotID = Events.MainSlot OR Events.ExtraSlot. I need to sum the number of “Attendees” per time slot (as MainSlot and as ExtraSlot - ExtraSlot is optional) Table "Events" ID------Name----------MainSlot-------ExtraSlot-------Attendees 1-------Event1--------1 -------------n/a-----...
Split Column with MySQL
Hello, I recently took over a project that has a pretty dirty database... The table "users" has a "phone" column... The problem is that the column holds multiple phone numbers separated by "/". I would like to put all of these in separate columns (phone 1, phone2, phone3 etc) But have no idea what the best way to do it is. Is it possibl...
Replace Default Null Values Returned From Left Outer Join
I have a Microsoft SQL Server 2008 query that returns data from three tables using a left outer join. Many times, there is no data in the second and third tables and so I get a null which I think is the default for left outer join. Is there a way to replace the default values in the select statement? I have a workaround in that I can ...
Should I split this table into two?
I am trying to wrap my head around database normalization. This is my first time trying to create a working database so please forgive me for my ignorance. I am trying to create an automated grad Check system for a class project. The following table keeps track of all options for a major for a set number of catalog years. The table is...
How can I use REF to reference row objects?
I have two entities, Customer and Order, both of which I have created types for. The SQL type declarations are: CREATE OR REPLACE TYPE "CUSTOMERTYPE" AS OBJECT ( customerId CHAR(6), name VARCHAR2(50), address VARCHAR2(255), telephone CHAR(11) ); CREATE OR REPLACE TYPE "ORDERTYPE" AS OBJECT ( customer REF CustomerType, orderId CHAR(10...
Migrating Oracle DATE columns to TIMESTAMP with timezone
Bakground: I've got a legacy app I'm working on that uses DATE types for most time storage in the database. I'd like to try update some of these tables so that they can utilize time zones since this is causing problems with users in different areas from where the db is(see A below). This is for Oracle 10g. Quetions: 1) Can I migrate ...
Indexed CHAR(255) field in MySQL... Is it optimal?
MySQL table with couple of fields: id - PRIMARY KEY url - CHAR(255). url field is also unique and indexed. Currently have couple of hundreds of thousands entries in that table. MySQL gets really slow. The idea is: if I add hash BIGINT UNIQUE INDEXED field, and create composite index hash/url - will it work faster? Means will MySQ...