February 5, 2008 at 11:50 am
I am very new to sql and am learning new things as I am working with this program.
I am trying to create a sql program that will be used to help us with inventory and pricing from many different suppliers of ours - I have created a program previously in access, but now that it's getting too big - we need to go to sql. The table that I need to create has over 550k records that get deleted and appended daily (and then there are views that run off of this table)- there is currently no real unique key - as some suppliers have the same sku for 2 different products - with different names, so I would need to do create a primary key with a numeric increment-but it would get too high - if I delete all records and append them again daily. The views seem to take very long to run without a real primary key.
Would it make sense to have a script that would recreate the table before appending the data each day, so that when the rest of the program runs - it will run efficiently because it has a primary key?
Would I be able to leave the views in place and only drop and create the table with same name and indexes thru a script or stored procedure?
Thanks for your help.
February 5, 2008 at 12:20 pm
If you truncate the table before you start the load, the starting seed value of the identity column will reset to 1.
February 5, 2008 at 12:30 pm
Truncating will also help prevent excessive log growth.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 5, 2008 at 12:38 pm
After you get the truncate working, you'll want to look at the query plans for the views to ensure that good indexes are in place.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 5, 2008 at 2:14 pm
Thank you everyone for your responses.
How would I initialize the seed for the id (primary key) field? Do I need to include it in the insert statement?
Thanks,
Sarah
February 5, 2008 at 2:30 pm
Assuming you're using an identity field as your PK, use
DBCC CHECKIDENT('mytable',RESEED,1)
will reset this back to 1
Note that you only need to do that if you use DELETE from mytable. If you use TRUNCATE TABLE mytable, like has been mentioned before - the identity will automatically be reset.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply