Database Discussion Boards

"select column1 as column1_112006,colum2 as column2_112006 from table1"

I don't want to use keyword "as" for 150 columns. Is there any way to rename using "select *" ?

Nope.

You could build your query dynamically in a stored procedure (Use schema to get the column names).

150 column table doesn't sound like a good design...

Rob Graham wrote:
150 column table doesn't sound like a good design...

Normalization could be a good idea Roll eyes | :rolleyes:

Some people have a memory and an attention span, you should try them out one day. - Jeremy Falcon

Hi... looking for some insight here...

Another site has a SQLServer 2000 database that huge... There data collection mechanism has been modified and the growth rate is now severely reduced, but, in the mean time, I still have to delete approximately 3/4 of the data.

The process has been started, but, over the weekend, the database added another extent (20%). There is concern that the deletion process is causing the database to grow.

To date, I have deleted approximately 1 1/2 % of the data.

My questions are:
what happens to the deleted space? is it automatically reused?
what effect will a shrink have on the datbase?

Ultimately, we will delete no longer needed data, archive to another database anything over 2 years old and reindex the entire database, but, that is still a long way off.

Any ideas? If possible, links to reference material would be helpful.

And, I am not the DBA, just the person tasked with the cleanup.

Thanks,

Tim

Tim Carmichael wrote:
I still have to delete approximately 3/4 of the data.

Wouldn't it make more sense to copy the 25% you want to keep to a new database, then drop the old database?

Tim Carmichael wrote:
, over the weekend, the database added another extent (20%). There is concern that the deletion process is causing the database to grow.

Does the data you are removing reside in tables with clustered indexes? If so, is the order in which you are deleting the old date likely to free whole pages in such a way that new data can recycle the pages, or will new pages still be required to maintain physical page order of the clustered index?

Rob Graham wrote:
Wouldn't it make more sense to copy the 25% you want to keep to a new database, then drop the old database?

If I had that option, it would be wonderful, however, this is a 24/7 production environment and I cannot recreate and move the data.

There are both clustered and unclustered indexes on the tables.

Tim Carmichael wrote:
s is a 24/7 production environment and I cannot recreate and move the data.

But you can afford to let the database grow by 20% increments? which suggests that disk space is not a problem... Could you not select the data you wish to keep into a new table (in reasonable chunks to keep the transaction log manageable, and when you reach the last chunk, rename both old an new tables in the same transction (so new data starts going to the new table). Next just drop the old table and then shrink the database.
As others have mentioned allowing % based growth is very bad, particularly large percentages of large databases - the growth step is a very resource consuming operation. It is better to run big dbs like this at a fixed size (no growth), and run a free space monitor / purge routine on a regular basis (frequently enough to reclaim space before it can fill up by deleting or moving data).

Rob Graham wrote:
But you can afford to let the database grow by 20% increments?

No, they can't afford to let it grow at that rate; the data is being collected too frequently and steps are being taken to address that. By the time we were involved in the process, the problem was already of gargantuan size... we are just trying to effect a solution.

Rob Graham wrote:
It is better to run big dbs like this at a fixed size (no growth), and run a free space monitor / purge routine on a regular bas

Eventually, we will get to the point that we are keeping a rolling two years worth of production data in the database and the rest in an archive database, but, again, that is... eventually. How do you eat an elephant? One bite at a time... Right now, I am trying to take small bites and keep my data deletes ahead of the data additions, thereby effectively stopping the growth, but, that takes time.

BTW, Sql BOL (SQL Server 2000) says that an extent is only 8 8KB pages (a little less than 64KB data, given overhead). Unless you are using the 64bit edition (which might have larger extents, I don't know), 64KB is not really that much growth and if that represents 20% of your database, then you have a relatively small (320KB?) database...

Perhaps what you meant is that the database was configured to allow growth, and the allowed increment is 20%. In that case, if you were borderline near the next growth increment, even one more page of data could have forced the 20% step.

We are using the 32bit version, but the production database is currently at about 115Gig. The table is question has about 850,000,000 rows.

So, with a 20% growth overnight on the test instance of the database, the size increased from 98Gig to 115Gig.

Well... we soldier on...

Part of the problem here is that you should never, and I repeat NEVER, use a percentage for growth. This is a really bad thing to do, as you are finding out now.

What you need to do is to grow the database by a fixed amount.

Now, part of your problem sounds like it could well be the transaction logs. You may need to truncate your transaction logs as well because the delete will be writing to the log. Once you have truncated your logs, you can shrink the database and reclaim lost space.

Arthur Dent - "That would explain it. All my life I've had this strange feeling that there's something big and sinister going on in the world."
Slartibartfast - "No. That's perfectly normal paranoia. Everybody in the universe gets that."

Deja View - the feeling that you've seen this post before.

I agree with Pete O'Hanlon. Sounds like a transaction log issue.

See these links for reference:
Managing the Transaction Log [^]
Shrinking the Transaction Log[^]
Truncating the Transaction Log [^]

You might want to look at this too - maybe delete the transaction log file until your cleanup is finished:
Adding and Deleting Data and Transaction Log Files[^]

--EricDV Sig---------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters

Hi, Careful! Long read. Im SORRY! Frown | :(

SENARIO:

I have a senario where there are 3 related tables. One is called Request, the other Quote, and the last is called QuoteRequest.

Each quote can have many requests in it.
Each request can belong to more than one quote.

When I want to save a Quote record, I select several Requests from a Datagrid using checkboxes. Those requests will be assigned to that quote.
The Requests which are visible in that datagrid for selection, are the ones which have NOT been Quoted before.

Here's the problem!

Let's say a request has been quoted. The TimeEstimate of the request is 5 days. Then someone changes that TimeEstimate to 9 days. This means that 4 days are still unquoted!

There is one thing I MUST do before I can display those unquoted days back on the data grid. I MUST store the initial TimeEstimate of ANY quoted requestin the QuoteRequest Table. This table will keep a track of all the requests which have been quoted. For instance it may have the following entries:

ID----RequestID----QuoteID----NoOfDaysEstimated<br />
1--------1-----------1---------------5---------<br />
2--------1-----------2---------------4---------

QUESTION:

What I want to do is simply this:

I want to STORE the TimeEstimate FROM the Request table INTO the QuoteRequest Table WHENEVER the AddQUOTE Stored Procedure is executed. This will effectively be populating the QuoteRequest Table with records of all the Requests which have been Quoted, and hence will allow me to display this info in a datagrid.

I don't know how to go about doing that.

If you do go through this, I thank you. If you can suggest something, I will be forever grateful.

Dayekh wrote:
I want to STORE the TimeEstimate FROM the Request table INTO the QuoteRequest Table WHENEVER the AddQUOTE Stored Procedure is executed. This will effectively be populating the QuoteRequest Table with records of all the Requests which have been Quoted, and hence will allow me to display this info in a datagrid.

I don't know how to go about doing that.

When you run the stored procedure, does it not already populate QuoteRequest rows? If it does then it should be simple to add an extra column. The request table still contains an TimeEstimate, so that value is copied into the QuoteRequest table, so that if the TimeEstimate on the request table changes, you still have the TimeEstimate as was when the quote was created (via the QuoteRequest table)

I don't see the difficulty, so I'm guessing there is something pertinent to the problem that hasn't been explained.

*** Developer Day 4 in Reading, England on 2nd December 2006 - Registration Now Open ***

Upcoming Scottish Developers events:
* Developer Day Scotland: are you interested in speaking or attending?

My: Website | Blog | Photos

Thank you so much for the reply mate..

Ok, let me elaborate. You are right in saying what you have said. But the problem is, I'm not a good SP programmer.

I can do insert/update/delete SPs, but in this instance, I think I will need an insert SP within an insert SP if you see my meaning. If the solution is trivial, then I apologise.

Colin Angus Mackay wrote:
When you run the stored procedure, does it not already populate QuoteRequest rows?

No it doesn't, because I have not programmed it to do so yet.

So once again, 3 Tables. Request, Quote, and QuoteRequest. The first 2 have a SP each for data storage.

The Quote Request Table has the following fields:

QuoteRequestID<br />
RequestID<br />
QuoteID<br />
NoOfQuoteDays

I want the INSERT QUOTE SP to contain the code for INSERTING the data from the TimeEstimate field in the Request Table, INTO the NoOfEstimatedDays field of the QuoteRequest Table.

I also want the INSERT QUOTE SP to store the Relevant RequestID of THAT TimeEstimate, AND also the QuoteID of the Quote being created currently into the QuoteRequest Table.

I hope that made sense Unsure | :~

Dayekh wrote:
I can do insert/update/delete SPs, but in this instance, I think I will need an insert SP within an insert SP if you see my meaning.

I'm confused. What do you mean by an INSERT STORED PROCEDURE? There are stored procedures and that is it. What happens inside of them is up to you. You can perform multiple operations in a stored procedure.

e.g.

CREATE PROCEDURE dbo.MyStoredProc
@a int,
@b int,
@c int,
@d int,
@e int,
@f int,
@g int,
AS
    -- Do and insert
    INSERT MyTable(a,b,c) VALUES (@a, @b, @c);
 
    -- Do an update
    UPDATE MyOtherTable
        SET d = @d, e = @e, f = @f
        WHERE g = @g;
GO

*** Developer Day 4 in Reading, England on 2nd December 2006 - Registration Now Open ***

Upcoming Scottish Developers events:
* Developer Day Scotland: are you interested in speaking or attending?

My: Website | Blog | Photos

I'm sorry, I'm just not expressing myself correctly.

I am aware of everything you just wrote. What I meant was a SP for inserting data into a table, a SP for updating data in a table, etc..

I have managed to get things moving with the help of a colleague. I thank you very much for your attention and efforts.
If you are really curious as to what the solution is, I will post it.

Take care mate..

I want to import a txt CSV file into a view in SQL 2000.
that can be done easily using VBA but the problem is that I want to check the validity of data e.g checking the serial no if its numeric , check the date if its in correct format...and I want to use a sql trigger for that so that each time theres an insert , before that it should chk the validity of data , etc . .

i am trying to do it ,, but a little jumpstart is all i need ,, thanks in advance

imran

What you need to do is a pre-insert trigger (or INSTEAD OF).

The steps are:

Create a view that mimics the table that you are trying to insert into.
Attach a trigger to the view. This trigger will be an INSTEAD OF trigger.
In the trigger, do the pre-validation and then insert.

For example:

CREATE TRIGGER MyTrigger ON dbo.vwMyView<br />
INSTEAD OF INSERT AS<br />
<br />
SET NOCOUNT ON<br />
<br />
DECLARE @MyVal NVARCHAR(50)<br />
<br />
SELECT @MyVAL = MyVal FROM INSERTED<br />
<br />
IF @MyVAL IS NOT NULL<br />
BEGIN<br />
INSERT INTO MyTable VALUES (MyVAL) FROM INSERTED<br />
END

Arthur Dent - "That would explain it. All my life I've had this strange feeling that there's something big and sinister going on in the world."
Slartibartfast - "No. That's perfectly normal paranoia. Everybody in the universe gets that."

Deja View - the feeling that you've seen this post before.

thank you verymuch it worked...
but pardon me for bugging u again ...
actually I need to check that the serialnumber should must not have '-' sign in it and the date should be in proper format, and CSV is actually being imported by access VBA acimport thingee ..
now its ok ,, it will import and fire the trigger as soon as it sees some inserts going on ....
Now my question is could you please explain it a little more ,, as what code should I write in order to do these two things, and also I want to check that If ItemName is already present in the table (another table) then It should insert only, otherwise no inserts ...
sorry for being lazy here and over inquisitive here but .. please help me I have tried really hard but couldnt do it ..
please help

imran

I have been assigned some work to generate particular queries and am having problems on how to JOIN the tables and structure the WHERE clause appropriately. Here Goes

The System is a School Administration System with a VB6 front-end and Access 2000 Backend. The database was already designed so I cant re-design it, I'm just trying to create the queries.

I have to come up with a query that returns Exam Papers not written by a particular Student in a particular class.
(I will attach the database file minus the data and some of its structure)
Ok here us the description of the Database structure.

There are Students, each uniquely identified by StudentID.

There is a SubjectClass table which has data on every class in the school, uniquely identified by SubjectClassID

There is a SubjectClassStudents table which is a collection of all students in classes. Each Student has a corresponding
SubjectClassID,StudentID and SubjectClassStudentsID to uniquely identify each record.

There is an Exams table as well. Each Exam has its corresponding SubjectClass. Each record therefore has
ExamID,SubjectClassID to uniquely identify it.

Each Exam can have several Papers. So the Exam table also has a NumberOfPapers field

Each Paper has its own unique PaperID,and ExamID to uniquely identify it

There is a PaperMarks table to store all the marks of Papers written

Here is the structure on text (table names in bold, fields seperated by "|")

Students
StudentID | StudentName

SubjectClass
SubjectClassID | SubjectClassName

SubjectClassStudents
SubjectClassStudentsID | SubjectClassID | StudentID

Exam
ExamID | SubjectClassID | ExamName | NumberOfPapers
Paper
PaperID | ExamID | PaperName

PaperMarks
PaperMarksID |PaperID | StudentID

The problem now:

Find the Papers NOT written by a Particular Student in a Particular SubjectClass

I have a sample database but dont know how i can post it here.

try out following query it might work

select a.StudentID,a.StudentName,b.StudentID,b.SubjectClassID,c.SubjectClassID,
c.SubjectClassName,d.ExamID,d.ExamName,d.SubjectClassID,e.PaperID,e.ExamID,
e.PaperName,f.PaperID,f.StudentID from Students a,SubjectClassStudents b,SubjectClass c,Exam d,Paper e,PaperMarks f where b.StudentID=a.StudentID and
c.SubjectClassID=b.SubjectClassID and d.SubjectClassID=b.SubjectClassID and
e.ExamID=d.ExamID and f.PaperID=e.PaperID and f.StudentID <> a.StudentID group by
a.StudentID,a.StudentName,b.StudentID,b.SubjectClassID,c.SubjectClassID,
c.SubjectClassName,d.ExamID,d.ExamName,d.SubjectClassID,e.PaperID,e.ExamID,
e.PaperName,f.PaperID,f.StudentID;

rahul

Will definitely try it out.
Thanks