SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Simple SQL Question...


Simple SQL Question...

Author
Message
tooba111
tooba111
SSC Rookie
SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)

Group: General Forum Members
Points: 47 Visits: 174
Hi Guys,

Simple question but don't know the answer, In my database i have 99 table that don't have any rows. My question is, it would be good idea
if i delete those table or leave it there. For Example, if i delete/remove these table, my users can see any application performance or not?
I know they are not using space, but just wondering....

Thank You.
Evil Kraig F
Evil Kraig F
SSCrazy Eights
SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)

Group: General Forum Members
Points: 8753 Visits: 7660
You'll get no performance increases from their removal, but you'll probably get readability and maintenance improvements from having less objects to keep track of.

EDIT: I should say no significant performance differences. 99 less tables to review during index rebuild reviews and the like will get you a few milliseconds or something back, just in case someone gets picky. :-)


- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)

Group: General Forum Members
Points: 88948 Visits: 41136
CELKO (12/5/2012)
In my database I have 99 table that don't have any rows. My question is, it would be good idea if I delete those tables or leave them there.


Better question: how did this happen in the first placew00t?


+1 on that! I've seen this happen with many 3rd party installations though. They sometimes install everything although the product level paid for may not use certain tables because certain options aren't available. Still, if you try to cleanup the mess they've left behind, it could break a maintenance contract, support contract, or some form of warranty. It would be like deleting unused job related tables from MSDB if you never ran any jobs.

More to your point (and I whole heartedly agree), these rowless tables are frequently development artifacts that have been forgotten. It's a part of the reason why I strongly suggest that no Developer be allowed any form of "write" access to production and that DBAs do code reviews and excercise change controls.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)

Group: General Forum Members
Points: 88948 Visits: 41136
tooba111 (12/5/2012)
Hi Guys,

Simple question but don't know the answer, In my database i have 99 table that don't have any rows. My question is, it would be good idea
if i delete those table or leave it there. For Example, if i delete/remove these table, my users can see any application performance or not?
I know they are not using space, but just wondering....

Thank You.


I don't care for rowless tables because they just clutter up the works especially when looking at tables through the Object Explorer of SSMS.

That notwithstanding, I wouldn't delete any of the tables unless I knew for sure who owned them. I'd never delete 3rd party product tables because, like I said above, it could void a maintenance contract, support contract, or maybe even break code because their GUI interface may still have objects for those tables and you just haven't used (or purchased) certain functionality, yet.

Even if the tables are from in-house applications, you could run into breaking code if you remove the tables.

Even when I think I've clearly identified that a rowless table is a true orphan, I'll script out the table (including all keys, fks, indexes, constraints, triggers, extended properties, privs, etc, etc), send out multiple emails asking if anyone knows what it's used for (along with a message of my intent to eventually delete it), store that scripts in a revision control product such as SubVersion or SourceSafe, rename the table, and let it sit for two or three months waiting for the phone to ring. 2 days prior to deletion, I send out 4 emails over those two days. Two "intent to delete" emailsat the beginning of each day with a list of the tables to be deleted, an "intent to delete" an hour before I delete them along with a final warning, and a "here's what I deleted" message after the fact.

I also tell people in that fourth email that if they want a deleted table to be restored after all of those warnings and the 3 month renaming period, that they should submit their request to reinstantiate each table on a separate $20 bill stapled to a paper bag with a #9 Jimmy John's sandwich, a Coke, and two Tylenol in the bag. :-P

As a side bar, I suppose it is possible that you could see a bit of performance improvement in the app but only by removing or streamlining the objects that refer to such rowless tables. I don't believe that it would be worth it, though, except to have a cleaner and leaner app that might not require as much memory to operate.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
rocky_498
rocky_498
SSC-Addicted
SSC-Addicted (457 reputation)SSC-Addicted (457 reputation)SSC-Addicted (457 reputation)SSC-Addicted (457 reputation)SSC-Addicted (457 reputation)SSC-Addicted (457 reputation)SSC-Addicted (457 reputation)SSC-Addicted (457 reputation)

Group: General Forum Members
Points: 457 Visits: 1381
Hi Guys,

Thank you so much to give me answer in detialll...

Appreciate all.

Have a nice weekend!
bev.kaufman
bev.kaufman
SSC Rookie
SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)

Group: General Forum Members
Points: 45 Visits: 48
I'd be cautious if you don't know how the tables got there in the first place. We have an accounting system that utilizes work tables, which are generally empty unless a particular application is running, loading it with data that can then be used in reports and processes. Maybe the programmers wrote code to recreate the tables if they accidentally get dropped, but that's not an assumption I'd care to risk.
ScottPletcher
ScottPletcher
SSCrazy Eights
SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)

Group: General Forum Members
Points: 8134 Visits: 7163
One or more them could also be exception/override tables. They may be empty in your case because currently you don't have any exceptions to normal processing rules/conditions. But existing code might still reference the tables to check for such exceptions.

Since they're virtually zero overhead, I wouldn't worry about them until you have everything else in the db fully tuned up.

SQL DBA,SQL Server MVP(07, 08, 09)[size=2]Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.[/size]
sunshine-587009
sunshine-587009
SSC Eights!
SSC Eights! (853 reputation)SSC Eights! (853 reputation)SSC Eights! (853 reputation)SSC Eights! (853 reputation)SSC Eights! (853 reputation)SSC Eights! (853 reputation)SSC Eights! (853 reputation)SSC Eights! (853 reputation)

Group: General Forum Members
Points: 853 Visits: 1273
I would replicate it in Development and/or backup in development if it already exists, then delete. If application has errors, restore. If not, migrate to production.

¤ §unshine ¤
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search