Update Versus Rebuild
Jul 23, 2005Hi,
Is there ever a case where a total rebuilt is preferable to an
update? I am talking in the context of a data warehouse.
Many thanks
Sam
ADVERTISEMENT
Update Versus Append
Jul 23, 2005I have a database that is being used as sort of a reports datawarehouse. I use DTS packages to upload data from all the differentsources. Right now I have it truncating the tables and appending withfresh data. I was considering using updates instead and my question waswhich is more efficent?
View 3 Replies View RelatedOLEDB Versus ADO.NET For UPDATE Statement Using Parms
Jun 14, 2006In SSIS, I have an Execute SQL Task that runs a direct SQL statement as follows to update an MS/SQL database table:
Update auditTable SET PackageName = @pkgname
In the parameters mapping, @pkgname is mapped to the System variable System::PackageName (as input).
This task runs fine if I use ADO.NET as the data source without any error.
But if I use OLEDB as the source it fails miserably and requires that the @pkgname variable be defined (as if it's a user variable, when in fact it was intended as a parm for the update statement only).
Any idea why that happens?
Thanks
Tommy
Update Statistics And Rebuild Index
Feb 26, 2008Is it neccessary to schedule a update statistics on index in sql server 2005 on daily basis
Is it neccessary to schedule a rebuild index on index in sql server 2005 on daily basis
SQL Tools :: Update Stats And Rebuild Indexes
Sep 28, 2015We are planning to standardize our newly deployed sql server, As a part of it we have configured 2 maintenance plans 1) Update Statistics which runs daily and 2) Index Reorganize which runs on weekly.
Apart from above, any other things to be in place for better maintenance of the sql server.
Also, how to Index Rebuild activity for clustered indexes requires any downtime.
Online Index Rebuild And Update Statistics
Dec 12, 2006Hi ..
Please advice whether i have to do update statistics along with Index rebuild (online /off line)
Thanks in advance
SQL 2012 :: Index Rebuild Job Fails Periodically At Update Stats?
Mar 18, 2015I have a job that runs nightly, rebuild index. job runs fine every night but every few weeks it fails.
USE msdb
GO
EXECUTE dbo.IndexOptimize
@Databases = 'USER_DATABASES',
@FragmentationLow = NULL,
@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 30,
@SortInTempdb = 'Y',
@UpdateStatistics = 'ALL',
@OnlyModifiedStatistics = 'Y'
This calls the Sp that does the Reindex. It fails at the update statistics with a very generic message. like " Command: UPDATE STATISTICS [xxxx_DB].[dbo].[xxxx_xxx] [_WA_Sys_00000007_49C3F6B7] [SQ... The step failed."
I suspect it has more error but this is all it is showing me when I right click on the job history. therefore, I updated the job step in the advance tab with log to a txt file. Am I on the right track or there is another way to see error some where else.
I looked at the logs but they didn't show any thing.
SQL Server Admin 2014 :: Update Stats After Rebuild And Reorganize Index
Jun 26, 2015We face slow performance issue for like taking long time for same query execution after We apply index rebuild and reorganize index. But, after execution of query or procedure for 2 -3 times, performance will be faster. I have following questions
1 do we need to update stats after we rebuild an reorganize index.
2. is it will be slow for 1-2 times for every query and stored procedure execution after we rebuild and reorganize index?
SQL Server 2008 :: Update Statistics On Tables Of Database After Rebuild Indexes?
Nov 5, 2015If I rebuild some indexes that are above 30% of average fragmentation, should I after that update statistics?
Also, How can I see if I Need to update statistics^on the tables of my database?
SQL 2012 :: Sleeping Queries Blocking Rebuild Index And Update Statistics Job In AlwaysOn
Feb 3, 2015At one of your client sides we have configured Always on with synchronous mode.Also we have schedule rebuild index and update statistics job which runs in night every alternate day. the issue is there are more then 100 sleeping queries which is blocking update statistics job.
I have to stop update statistics job manually once i come to office manually.
Once I have killed blocking sleeping query but then other sleeping query blocked it and so on.
SQL Server 2008 :: Logic To Rebuild Only Clustered Indexes / Skipping To Rebuild Non Clustered Indexes In Same Table
Jun 25, 2015I have a requirement to only rebuild the Clustered Indexes in the table ignoring the non clustered indexes as those are taken care of by the Clustered indexes.
In order to do that, I have taken the records based on the fragmentation %.
But unable to come up with a logic to only consider rebuilding the clustered indexes in the table.
create table #fragmentation
(
FragIndexId BigInt Identity(1,1),
--IDENTITY(int, 1, 1) AS FragIndexId,
DBNAME nvarchar(4000),
TableName nvarchar(4000),
[Code] ....
@@ Versus @ In T-SQL
Apr 13, 2006I'm sorry to be ignorant on this point. It seems trivial, but what's the difference between @@ and @ when using variables in T-SQL? I have a developer that always uses @@ for local variables and @ for reference variables (meaning variables declared as parameters for a stored procedure or function).
Is that purely stylistic? Is it a holdover from some previous version? Or is it a legitimate best practice that I've not seen before?
My google-shui is weak today; I found nothing when searching.
Regards,
hmscott
SP2 Versus SP1
Feb 21, 2007Do you need SP1 installed before installing SP2?
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/servicepacks/sp2.mspx
SQL - SP1 Versus SP2
Apr 13, 2007Hi everybody. I am just confused. What is the difference between SQL SP1 and SP2 ? Can anyone explain further to me.
Thanks.
-Ron-
NOT EXISTS Versus NOT IN
Jan 30, 2004I have two tables :
tableA
a_id (int)
value (varchar 255)
tableB
id (int)
a_id (int)
b_id (int)
c_id (int)
d_id (int)
Both these tables contain considerable amounts of rows, but over time tableA will end up containing orphaned values (i.e. the a_id is not used in tableB) and this problem cannot be rectified by setting, for example, cascade deletes.
To fix this problem I decided to write a simple stored procedure to purge all values in tableA where its a_id is not used in tableB :
DELETE FROM tableA WHERE a_id NOT IN (SELECT a_id FROM tableB)
Now although the following document relates to postgres :
http://archives.postgresql.org/pgsql-sql/2003-12/msg00174.php
I was interested to find out if I should be wary of using NOT IN in my query.
SET Versus SELECT
Feb 12, 2007Hello,
I have an Output parameter as follows:
@Feedback INT OUTPUT
I want to give it a value and return it.
What is the difference between using:
SELECT @Feedback = -1
RETURN @Feedback
And
SET @Feedback = -2
RETURN @Feedback
Thanks,
Miguel
SSIS Versus DTS
Jun 20, 2007Hello,
I wonder if anyone else out there has the same impression that I have: I find that DTS works much better than SSIS.
I find that DTS is so easy to use and reliable: it gets the job done and fast! On the other hand, SSIS seems to be so needlessly complex that it takes hours of troubleshooting just to get it to work, and sometimes it doesn't work at all. For example I have just spent hours trying to get SSIS to import a flat file with 300,000 rows. It just crashes and doesn't even give an error message so that one can fix it. On the other hand I have just now successfully accomplished the same task with DTS and it took me 5 minutes!
I honestly don't see a valid reason for using SQL Server 2005 instead of 2000. So far it's much more productive to use 2000.
I hope Microsoft can clarify this issue.
Regards,
Jerome Smith
ADO.NET DATAVIEWS VERSUS SQL ENGINE
Jan 4, 2006Hi All,
Any suggestions / views / help on below question would be welcomed.
I am building an asp.net 2.0 application with sql 2005 express as back end. My back end has 3 major tables which are:
tblArticles - saves basic info on articles posted by user (like articleid, title, short desc, rating, views, etc)
tblCategories - saves various categories and their hierarchies (id, parented, name, etc)
tblArticleCategories - saves info on which articles fall in which categories (like articleid, categoryid)
as of now, i am caching all rows from the first 2 tables, but i am in a bit of doubt for caching the third table (tblArticleCategories), although data in this table wont change very often and also this table will just have 2 columns and not many rows as well and this is a good target for caching,
but the reason I am in a bit of doubt to cache this table is, when my website visitor clicks on any category link in the category tree view, I need to use an inner join across all these 3 tables to locate and return all articles found in that particular category.
But I can do the same thing without hitting the database as I already have 2 of the required 3 tables in my cache, I can simply add the third table to my cache and then using the dataview objects rowfilter property on these 3 cached tables, I can very well get the appropriate results.
But I wonder which of the 2 methods would you prefer and suggest, I mean do you feel that just to save hits against the database, I am going to far and doing a lot of crap using the dataview (which might not be as efficient as sql engine) or you feel that the inefficiency of the dataview will still win compared to the cost of hitting the database for this
Thanks in advance, bye take care
Raj Chaudhari, Mumbai, India (MCAD.NET)
www.xtremebiz.biz
Trigger Versus SQL Insert
Feb 16, 2006Hello all,
I have table 'statistics' which holds information about another table, i.e. number of rows belonging to each user.
Would I be better off using a trigger after each insert to increment a certain row.
Or would I be better off selecting the data by means of an sql statement and updating the column whenever the statisitcs page is requested.
Does sql provide any methods which allow a column to count other rows or columns?
SQL Server Versus Informix
Oct 25, 2000Hi all,
Anyone here ever used the Informix database and can give me some differences between Informix and SQL.
One of our users is thinking about purchasing a COTS product that only supports an Informix database. I need to convince the user to evaluate other rival applications that can support SQL and need some arguments in favor of not going with Informix.
Any ideas appreciated,
Faustina
ArcServe Versus BackupExec
Jun 21, 1999We currently use CA ArcServe (ArcServe 6.5 Enterprise and Single Server Editions) to backup our Windows NT files and MS-SQL Server databases. We have experienced significant reliability issues with ArcServe. Many times we have found ourselves rebuilding a corrupt ArcServe Job (ArcServe’s backup schedule) database. One of our NT server occasionally NT bug checks when ArcServe is performing backups. Occasionally ArcServe Jobs incorrectly reschedule themselves. Sometimes the Jobs do not complete but stay executing, not performing any work, and to cancel them may require a lot of effort. The ArcServe job DB repair utility generally does not work. The user interface is lacking. For example, the job scheduling options are very limited. CA tech support for this product has been poor. Because we have issues with ArcServe stability we are now evaluating Veritas (formally Seagate) Backup Exec for NT. What are other people’s experiences with these 2 products?
View 1 Replies View RelatedNEtwork Techs Versus DBA's
Jun 25, 2004I gotta network tech that I work with from time to time. Hes gonna migrate a access database over to sql. He says it should be easy its a flat file can just do it through enterprise manager. I warned him that datatypes can become an issue (kinda have to know your db) he looked at me like I'm an idiot and proceeded to migrate the tables over to sql...Needless to say he got alot of error messages and is now totally confused. Now let me ask some experts who really Know Databases, do you ever have problems with Network Techs who think they know all
View 14 Replies View RelatedSize Versus Space Available
Dec 7, 2005We are planning hardware purchases (more is better). One of our databases is 131 gigs in size and has 45 gigs of 'space available'. I'm not a very experienced SQL Server person, but this seems like quite a bit of 'space available'
1) Is there a way to regulate the amount of 'space available'?
2) are there any rules of thumb for how 'space available' there should be?
Appreciate any feedback or help.
Ray
SQL Server 2005 And 32-bit Versus 64-bit
Dec 14, 2006Hi all,
we are about to purchase new database servers and have been offered a good deal on 64-bit Xeon machines. At present we run SQL 2000 on Windows Server 2003 both of which are 32-bit versions.
Is there any problem using our current 32-bit Server software on the 64-bit machines (apart from not being able to utilise its full power)? I'm assuming the SQL 2005 licenses are the same price regardless of 32-bit or 64-bit version. If we buy a 64-bit SQL Server version license are we going to get the best out of it on a 32-bit Windows Server edition?
tia - Matt
Cursor Versus While Loop
Oct 29, 2007I have always been told that Cursors create a lot of overhead and consume a lot of system resources. Is it faster to store the data in a temp table and loop through it by using Select Top 1 and Delete statements or by using a static, Forward-Only Cursor? Both ways store the data in TempDB, but doesn't the While Loop statement generate more IO's than the Cursor? In theory, I am thinking that the Cursor is better. Any info will be appreciated.
Thanks!!
Nvarchar Versus Varchar
Oct 10, 2007I have table with a field defined as nvarchar. I want to change it to varchar. I have a stored procedure which defines the parameter @strCall_desc as nvarchar(4000). Are there going to be ay problems with running this sp if I just change the field type as described.
TIA
Return Versus Raiserror
Jul 20, 2005I am trying to get a better understanding of when to use return (witha print statement) and when to use raiserror.* Both statements can be used with stored procedures while only returncan be used with functions.* With raiserror it is easy to have multiple errors thrown. (If boththe calling procedure and called procedure both try to handle error)Wow. Thought I could think of more. So that really leaves me with verylittle info on the proper use of these two statements.
View 2 Replies View RelatedSp_addextendedproperty Versus Sysproperties
Jul 20, 2005I have been searching for a way to associate a description with acolumn name. I have come across multiple posts regarding thisquestion. Problem is that I have seen two different answers.One post mentioned using the undocumented system table namedsysproperties while other posts mentioned using thesp_addextendedproperty (and fn_listextendedproperty).Which one and why one over the other?Thanks.
View 1 Replies View RelatedJoins Versus Relationships
Jul 20, 2005If a database has relationships establshed between all of the tablesvia primary and foreign key constraints, why isn't is possible to makea SELECT statement across multiple tables without using a JOIN?If the system knows the relationsip schema already why are JOINSrequired?Thanks,HC
View 4 Replies View RelatedSSIS Versus Coding.
Jun 25, 2007This question probably overlaps a few different topic areas.
As I will be required to work with both Oracle and SQL Server I will be in a difficult position with SSIS(due to it's change in distribution).
Therefore I am having to look at alternatives.
With coding a can open a text file and parse it reasonably to my satisfaction. However getting the data into the database is incredibly slow.
I am using an Insert into for each line, which I am sure everone will shake their head over. This seems to be pretty slow even using transactions.
Is there any scope in using data tables or have the read on one thread and write on another.
Other than that is there an Oracle equivalent of SSIS which comes (probably get shot for asking that on a microsoft web site, but would probably get shot if I asked on Oracle forums as well).
In the past we had reasonable results in outputting to csv and then doing some sort of bulk insert, messy and irritating though this may be.
Any ideas on this area will be gratefully accepted.
DT_I8 Versus DT_UI8
Mar 13, 2007Can some one tell me in basic terms the difference between a signed and unsigned integer? When would you decided to use one over the other? I'm looking for it more in layman terms than a technical bit level discussion.
View 9 Replies View RelatedCertificates Versus Keys
Jul 14, 2005Hi,
View 3 Replies View RelatedAuthentication - Windows Versus Sql
Feb 23, 2008Can anyone give me some advice on using authentication. What is the best way to go with a database on a server and why? And in order for you to use sql authentication, do you have to change the registry? I have seen some posts that seem to say you can only use it by changing the registry.
So if anyone can gie me the pro's and con's i would appreciate it.
Thanks
Jeff