@@ Versus @ In T-SQL
I'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
View Complete Forum Thread with Replies
Sponsored Links:
Related Messages:
NOT EXISTS Versus NOT IN
I 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.
View Replies !
View Related
SSIS Versus DTS
Hello, 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
View Replies !
View Related
SET Versus SELECT
Hello, 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
View Replies !
View Related
ADO.NET DATAVIEWS VERSUS SQL ENGINE
Hi 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
View Replies !
View Related
Generic Arc Versus Explicit Arc
in my database model I have a entityType ATTACHMENT where I relate my entityTypes to documents such as pictures,word documents,excel etc. Person,Company and Requirement tables have one to many relationships to ATTACHMENT table.I have to implements joins to that table. I have two ways to implement the database design. 1.Put RelatedPersonID , RelatedCompanyID ,RelatedRequirementID into ATTACHMENT table.(explicit ARC) 2.Put RelatedEntityID and RelatedentityType to ATTACHMENT table.(generic ARC) What are the advantages and disadvantages of these two approaches? Which one is faster with lots of data and lots of concurrent users?
View Replies !
View Related
Trigger Versus SQL Insert
Hello 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?
View Replies !
View Related
Update Versus Append
I 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 Replies !
View Related
Return Versus Raiserror
I 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 Replies !
View Related
Sp_addextendedproperty Versus Sysproperties
I 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 Replies !
View Related
Joins Versus Relationships
If 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 Replies !
View Related
NEtwork Techs Versus DBA's
I 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 Replies !
View Related
Size Versus Space Available
We 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
View Replies !
View Related
SQL Server 2005 And 32-bit Versus 64-bit
Hi 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
View Replies !
View Related
Cursor Versus While Loop
I 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!!
View Replies !
View Related
ArcServe Versus BackupExec
We 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 Replies !
View Related
SQL Server Versus Informix
Hi 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
View Replies !
View Related
DT_I8 Versus DT_UI8
Can 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 Replies !
View Related
SSIS Versus Coding.
This 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.
View Replies !
View Related
HTML Versus SSRS
Hi we are currently working in a project where we need to create reports in Visual Studio 2005. the parent screen has a drop down which specifies the name of the report along with some other parameters. then the report is displayed in the same screen. now the issue is we are pretty much confused as to what to use to generate these reports? HTML or SSRS? the input fileds are only from date and to date and the displayed fileds are also not that many. can some one please throw light on this? Thanks, Kumaraswamy
View Replies !
View Related
Ntext Versus Nvarchar
Hi, my question concerns both desktop and device apps. I'm using sql compact to store some data. I often have to store strings (descriptions, url, etc.) but I don't know when to use nvarchar or ntext. Nvarchar needs to have a size limit, but I often set it to 8092 when I don't know the actual limit (urls can be very long !). I fear Ntext because I suppose there is performances impact. Is there any "rules" to help to choose which data type I'd use ? Thanks, Steve
View Replies !
View Related
Authentication - Windows Versus Sql
Can 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
View Replies !
View Related
Nvarchar Versus Varchar
I 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
View Replies !
View Related
A Question About Udf Versus Sp In A Specific Context...
Given the following objective: 1. Assume that I have a table that contains two fields: an auto-numbered id and an integer value2. Check to see if a record exists in a table based on a parameter query of the integer value3. If the record exists, return the record id4. If the record does not exist, insert a new record into the table (using the parameter value as data) and return the auto-numbered id of the new record I can do each of these things as a sequence of individual steps, of course, but it seems to me that I ought to be able to do it with a single udf (or perhaps a specialized query) that would be more efficient. I couldn't find something like this in the beginning SQL Express books I have on hand and I also didn't find anything exactly on point on this newsgroup or a search of Google. However, I am sure the answer is 'out there' and I am hoping that someone can point me in the right direction. Thanks! Duncan
View Replies !
View Related
Int Versus Char Primay Key Performance
Hi,My company has a scenario where we would like to change the data typeof an existing primary key from an integer to a char, but we areconcerned about the performance implications of doing so. The scriptfor the two tables that we need to modify is listed below. TableFR_Sessions contains a column named TransmissionID which is currentlyan integer. This table contains about 1 million rows of data. TableFR_VTracking table also contains the TransmissionID as part of it'sprimary key and it contains about 35 millions rows of data. These twotables are frequently joined on TransmissionID (FR_Sessions is theparent). The TransmissionID column is used primarily for joins and isnot typically displayed.We need like to change the TransmissionID data type from int tochar(7), and I had a few questions:1) Would this introduce significant performance degradation? I haveread that char keys/indexes are slower than int/numeric.2) Are there collation options (or any other optimizations) that wecould use to minimize the performance hit of the char(7)...if so whichones?I am a software architect by trade, not a database guru, so please goeasy on my if I overlooked something obvious :)Any suggestions or information would be greatly appreciated.Thanks,Tim-------------------CREATE TABLE [FR_Sessions] ([TransmissionID] [int] IDENTITY (1, 1) NOT NULL ,[PTUID] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,[PortNum] [numeric](6, 0) NOT NULL CONSTRAINT [DF_FR_Sessions_PortNum]DEFAULT (0),[CloseStatus] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[RecvBytes] [int] NULL ,[SendBytes] [int] NULL ,[EndDT] [datetime] NULL CONSTRAINT [DF_FR_Sessions_EndDT] DEFAULT(getutcdate()),[LocalEndDT] [datetime] NULL ,[TotalTime] [int] NULL ,[OffenderID] [numeric](9, 0) NULL ,[UploadStatus] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOTNULL CONSTRAINT [DF_FR_Sessions_UploadStatus] DEFAULT ('N'),[SchedBatchID] [numeric](18, 0) NULL ,[SWVersion] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[DLST] [bit] NULL ,[TZO] [smallint] NULL ,[Processed] [bit] NOT NULL CONSTRAINT [DF_FR_Sessions_Processed]DEFAULT (0),[CallerID] [varchar] (13) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[PeerIP] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[XtraInfo] [varchar] (1024) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[IdType] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,CONSTRAINT [PK_FR_Sessions] PRIMARY KEY CLUSTERED([TransmissionID]) WITH FILLFACTOR = 90 ON [PRIMARY]) ON [PRIMARY]CREATE TABLE [FR_VTracking] ([TransmissionID] [int] NOT NULL ,[FrameNum] [int] NOT NULL ,[LatDegrees] [float] NOT NULL ,[LonDegrees] [float] NOT NULL ,[Altitude] [float] NOT NULL ,[Velocity] [float] NOT NULL ,[NumPositions] [smallint] NOT NULL ,[NavMode] [smallint] NOT NULL ,[Units] [smallint] NOT NULL ,[GPSTrackingID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,[dtStamp] [datetime] NULL ,CONSTRAINT [PK_FR_VTracking] PRIMARY KEY CLUSTERED([TransmissionID],[FrameNum]) WITH FILLFACTOR = 90 ON [PRIMARY]) ON [PRIMARY]
View Replies !
View Related
Tempdb Data Versus Log Size
Against my better judgement, we are using fixed allocations of tempdb on some of our servers. This is to deal with specific limitations of our applicaitons and hardware configuration that I'm not allowed to discuss in much detail. The problem that I have is that the present plan is to configure the data file at around 18 Gb and the log file at around 2 Gb. This seems just plain wrong to me, but I haven't been able to find a formal recommendation that gives any relative sizing. I would expect to have about twice as much log as data space, especially for tempdb. Does anyone know of a formal citation (preferably from Microsoft) that discusses this? -PatP
View Replies !
View Related
Named Pipes Versus TCP/IP Sockets
We are having some problems using TCP/IP connection and was wondering what the benefits are to using this versus Named Pipes. The company that provides our software stated that this would improve performance but I do not see it and I am getting a lot of errors connecting. This also locks many of the users and I end up having to stop and start the database to clear the locks because there are so many. Would it be better to place the clients on Multi-protocol. It seems more powerful to use but I wanted to get some feedback from some people that may have dealt with this issue before. Thanks,
View Replies !
View Related
Enterprise Manager Versus Sp_spaceused
When comparing the data space available (Edit Database dialog within Enterprise Manager) to sp_spaceused, I occasionally generate numbers that have discrepancies. Example Enterprise Manager shows data space available = 160.43 MB sp_spaceused (Unallocated space = 397 MB) less log space (200 MB) = 197 MB Even after I run (sp_spaceused @updateusage = 'true'), the problem is not resolved. Are there any known issues with space being calculated incorrectly from within Enterprise Manager? Should I not rely on Enterprise Manager and stick with sp_spaceused to find out database usage? Are there any other methods which will resolve this problem? Any help would be appreciated. Thanks, Karl
View Replies !
View Related
ODBC Versus Security Problem
Ive got SQL Server 7.0 running in one domain and i'm trying to access it from a workstation belonging to another domain. There is no trust between the domains but well it worked fine with SQL 6.5. Ive tried several combi nations with out success. My best result is to get an error message followed by a login box every time the application, spreadsheet etc... tries to access its data source. Works fine from workstations belonging to the domain (SQL) Any ideas ? Thanks Jan We are what we repeatedly do. Excellence, then, is not an act, but a habit. -- Aristotle
View Replies !
View Related
View Performance Versus SQL Statement
I have a complex(long) SQL statement inside of a stored procedure which feeds several variables from 2 tables. Something like Select @var1, @var2, etc from table1, table2 where table1.id = table2.id Is there any advantage to creating a view for this statement and selecting from that, even though this resides in a stored procedure?
View Replies !
View Related
Temp Table Versus Set Many Variables
I have a large stored proc that a programmer wrote which utilizes almost 100 set variable statements (there is a valid reason for this) created by a select statement. All these variables are then utilized later in the stored procedure. I was wondering if creating a temp table from the select might be a better approach. Is there less overhead involved in the temp table versus utilizing all these variables or is it about the same? Thanks
View Replies !
View Related
ActiveX&&VB.net Versus Transformation Tasks
Hello all - Working on DTS packages we used to have two different ways of exporting data to spreadsheet... We could do it by a single transformation task or either writing an ActiveX script, after running a procedure. I am able to cover the first way already, while dealing with SSIS... But, I wonder if it is worth to research on how I would write a vb.net code to load data into a spreadsheet (considering we are advised to try vb.net instead of ActiveX while working on these new packages). It is always a huge discussion to know what is the best way of doing something so my point here is to hear some of you and decide if I just keep exporting data by doing transformation tasks or if I will ever go trying to deploy a vb.net code to do that... What is the best on performance, etc...? Thanks in advance for all your help as always.
View Replies !
View Related
Error Messages Versus Exceptions
I am using .NET v2.0 and SqlServer2005 SB in 90 compatibility mode. From my brief experience it appears that if I try to subscribe to SB with incorrect SQL or wrong names .NET will immediately throw an exception. However, if my code is correct but there is an error on the server (for example incorrect options as per http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=883992&SiteID=1) then SB will immediately fire the notification message but with €œerror€? instead of €œupdate€? content. How do I detect this? My code is based on the http://msdn2.microsoft.com/en-us/library/3ht3391b(vs.80).aspx example. (I had too much trouble with the SqlDependency class, including overflowing error logs. So unlike the post at http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=903713&SiteID=1 I am NOT receiving an SqlNotificationEventArgs object. I wish I were getting one!) I€™ve included my Listen() method below. The debug portion of this code gives me message body that looks like the following when everything is good. What should it look like, if for example my SET OPTIONS (first link above) were wrong? messageBody: <qn:QueryNotification xmlns:qn="http://schemas.microsoft.com/SQL/Notifications/QueryNotification" id="129" type="change" source="data" info="update" database_id="23" sid="0xF4F50461FD43CA4191A1173B9350632A"><qn:Message>7fd415c7-2986-411d-abfd-615cbce0af28</qn:Message></qn:QueryNotification> Thanks. Code follows: private void Listen() { // infinite loop on this thread: keep listening even after // receiving notification while( true ) { // connects to SqlServer queue and sleeps until a notification msg arrives using( SqlConnection conn = new SqlConnection( _connect ) ) { WriteMessage( "RequestNotification: Listen connection opened." ); using( SqlCommand cmd = conn.CreateCommand() ) { int msgBodyId = InvalidSubscriptionId; cmd.CommandText = waitFor; cmd.CommandTimeout = 0; // _notificationTimeout + 100; try { conn.Open(); // the thread is blocked until the waitfor event occurs. SqlDataReader reader = cmd.ExecuteReader(); // the waitfor has triggered when we've reached here. WriteMessage( "RequestNotification: Notification received." ); // there may be mulitple notifications waiting, but there seems only // ever to be a single record to read, but we still use the while // construct to ensure we get them all... while( reader.Read() ) { // the messageBody is actually XML with a GUID embedded as the msgId, eg: // <qn:Message>82120744-f2bf-4c62-a17c-867cc6eb040e</qn:Message> String messageBody = FixupMessageText( System.Text.ASCIIEncoding.ASCII.GetString( (byte[]) reader.GetValue( 13 ) ).ToString() ); msgBodyId = MessageBodyId( messageBody ); // doesn't work, sqlbinary... // String messageBody = reader.GetSqlXml( 13 ).ToString(); #if __dbg // used for debugging // messageText = System.Text.ASCIIEncoding.ASCII.GetString( (byte[]) reader.GetValue( 13 ) ).ToString(); Byte status = reader.GetByte( 0 ); Byte priority = reader.GetByte( 1 ); Int64 queuingOrder = reader.GetInt64( 2 ); Guid conversationGroupId = reader.GetGuid( 3 ); Guid conversationHandle = reader.GetGuid( 4 ); Int64 messageSeqNo = reader.GetInt64( 5 ); String serviceName = reader.GetString( 6 ); Int32 serviceId = reader.GetInt32( 7 ); String serviceContractName = reader.GetString( 8 ); Int32 serviceContractId = reader.GetInt32( 9 ); String messageTypeName = reader.GetString( 10 ); Int32 messageTypeId = reader.GetInt32( 11 ); String validation = reader.GetString( 12 ); StringBuilder sb = new StringBuilder( "MESSAGE", 1024 ); sb.AppendLine(); sb.Append( " " ); sb.Append( BuildMessage( "status", status ) ); sb.Append( BuildMessage( "priority", priority ) ); sb.Append( BuildMessage( "queuingOrder", queuingOrder ) ); sb.Append( BuildMessage( "conversationGroupId", status ) ); sb.Append( BuildMessage( "conversationHandle", conversationHandle ) ); sb.Append( BuildMessage( "messageSeqNo", messageSeqNo ) ); sb.Append( BuildMessage( "serviceName", serviceName ) ); sb.Append( BuildMessage( "serviceId", serviceId ) ); sb.Append( BuildMessage( "serviceContractName", serviceContractName ) ); sb.Append( BuildMessage( "serviceContractId", serviceContractId ) ); sb.Append( BuildMessage( "messageTypeName", messageTypeName ) ); sb.Append( BuildMessage( "messageTypeId", messageTypeId ) ); sb.Append( BuildMessage( "validation", validation ) ); sb.AppendLine(); sb.Append( " " ); sb.Append( BuildMessage( "messageBody", messageBody ) ); WriteMessage( sb.ToString() ); #endif } reader.Close(); // and now we need to hand things back to the user's passed delegate. // orphan notifications can be left behind from previous runs, // determine if this notification is one we want to pass on if( _subscriptionId == msgBodyId ) { WriteMessage( "Message " + msgBodyId.ToString() + " calling delegate." ); // see http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlnotificationinfo.aspx SqlNotificationEventArgs e = new SqlNotificationEventArgs( SqlNotificationType.Change, SqlNotificationInfo.AlreadyChanged, SqlNotificationSource.Database ); _delegate( this, e ); } else { WriteMessage( "Message " + msgBodyId.ToString() + " ignored." ); } } catch( SqlException ) { // if the queue name doesn't match one in the db an exception will be thrown throw; } finally { conn.Close(); } } } WriteMessage( "RequestNotification: Listen connection closed." ); } }
View Replies !
View Related
Conversation Timer Versus LIFETIME
I need to follow up on a message and check on its status. I am planning on using Conversation Timers (self addressed). I've tried it and they do work well. I am wondering if the LIFETIME parameter can be used for the same purpose. If the dialog has not been closed and the LIFETIME expires, will a message be queued into the service's queue? It does not seem that this is the case, but it is worth checking, as it could be a much desired feature. Thanks,
View Replies !
View Related
SQL Server Versus Pervasive SQL/2000
Hi there, Don't know if anyone can help, There used to be a Whitepaper on the Microsoft website comparing SQL server (2000?) against Pervasive SQL or 2000, the link was http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechnol/sql/. But it's not working anymore. Does anyone have a copy of this paper, or know where it's now located? I've been looking for hours and can't find it. Thanks in advance. Jezza
View Replies !
View Related
Error Handling In SQL 2000 Versus Sybase ASE 11
I am converting 700+ stored procs from Sybase to SQL 2000. I am noticing a pattern here that Sybase 'continues' after errors that would blow up an SP in SQL 2000. The previous programmers were, let's just say, not into error handling. But now we have tons of errors in SQL 2000 that Sybase seems to let slide. This is a conversion only and 'when' is more important than 'how'. Any functions, clauses, statements, etc. in a SQL 2000 stored proc that would step SQL down to Sybase's less than rigorous standards and allow the SP to continue on errors to simply mirror the Sybase output? Thanks.
View Replies !
View Related
Cursor Looping Versus Set-based Queries
I know this question has been asked. And the usual answer is don't usecursors or any other looping method. Instead, try to find a solutionthat uses set-based queries.But this brings up several questions / senarios:* I created several stored procedures that take parameters and insertsthe data into the appropriate tables. This was done for easy access/usefrom client side apps (i.e. web-based).Proper development tactics says to try and do "code reuse". So, if Ialready have stored procs that do my logic, should I be writing asecond way of handling the data? If I ever need to change the way thedata is handled, I now have to make the same change in two (or more)places.* Different data from the same row needs to be inserted into multipletables. "Common sense" (maybe "gut instinct" is better) says to handleeach row as a "unit". Seems weird to process the entire set for onetable, then to process the entire set AGAIN for another table, and thenYET AGAIN for a third table, and so on.* Exception handling. Set based processing means that if one row failsthe entire set fails. Looping through allows you to fail a row butallow everything else to be processed properly. It also allows you togather statistics. (How many failed, how many worked, how many wereskipped, etc.)?? Good idea ?? The alternative is to create a temporary table (sandboxor workspace type thing), copy the data to there along with "status" or"valdation" columns, run through the set many times over looking forany rows that may fail, marking them as such, and then at the end onlydealing with those rows which "passed" the testing. Of course, in orderfor this to work you must know (and duplicate) all constraints so youknow what to look for in your testing.
View Replies !
View Related
Sql Server 2005 Versus Oracle 10g Certification
hi guys, I am joining IT industry in august,my main intrest is in database(readed some stuff of warehousing oracle 9i and sql). However to move my career in that direction i need certification. So here is the point,should i get MCTS(in sql server 2005) or OCP(oracle associate 10g). My main goal is to become DBA. which of the above is more benificial. I mean more oppurtunites. please reply.
View Replies !
View Related
Sql 2000 Mem Setting Versus Real Memory
I have a client server that has win2000 on it with sql 2000 enterprise edition. The box has 4 gig of memory on it. I noticed today that the sql server was set to use all 4 gig (even though I know sql can't really access that memory because sql can't really utilize the 2-4 gig range). Is there overhead or a downside to leaving it at this, or should I set it to 2 gig
View Replies !
View Related
Problem With Sp Running As Job Versus In Query Analyzer
Hello, Specifically, I'm having an issue that I can't resolve using the database space utilization procedures recently submitted by Paul Matthews. The servers are appropriately linked and the procedures have been created on the required servers (mix of 7.0 and 2000 systems). Executing the sp_dbspaceall procedure from query analyzer is successful but it fails when called from a SQL Agent job. It only returns the information of the local server in that instance. The error message tells nothing of the problem and the logs show nothing at all about the incident. Can someone help me out?
View Replies !
View Related
Performance Issues On Sql 2005 Versus Sql 2000 - AGAIN!
I was hoping I wouldn't be another poster with performance issues after migrating to SQl 2005 from SQL 2000 but here I am. I am in the process of testing out our databases on Sql Server 2005 for migration from SQL Server 2000 and there are certain portions of code that have been affected negatively. I have read thru many of the posts here and have tried out most of the recommendations. I will start out with things I've done and then provide the actual SQL. 1) I have rebuilt all indexes ( using the DBCC REINDEX using the table option). 2) Updated the db engine to latest hot fix (build 3239) that addresses speed related fixes. 3) I also ran sp_createstats using the 'fullscan' option to create stats on all columns of all tables (minus indexed columns) 4) Since nothing seemed to work, I even ran UPDATE STATICS with FULL SCAN on all tables even though I did not need it as the REBUILD woudl have created stats. But I was willing to try anything. I have confirmed that the execution plans are different even though the data on both sql 2000 and sql 2005 are identical (i put a copy on 2005). The plans themselves are huge as the queries are huge. Here is the query. SELECT InterimView.* ,TestView.* FROM View_LabDataExport_TestFormData_55 TestView RIGHT OUTER JOIN ( SELECT ReqView.*, CDView.* FROM View_LabDataExport_FormData_55 ReqView LEFT OUTER JOIN View_LabDataExport_FormData_CD_55 CDView ON ( CDView.DB_SubjectID_CD = ReqView.DB_SUbjectID ) ) InterimView ON ( InterimView.DB_FormID = TestView.DB_FormID_T AND InterimView.DB_LabSampleID = TestView.DB_LabSampleID_T ) The above query takes abotu 8 secs to run on 2000 and about 1 minute to run on 2005. This is for a small dataset and on larger datasets this is only going to more pronounced ( as confirmed by other teams that have already migrated in my company). Another point worth mentioning might be if I remove the TestView.* from the select list, it works in 5 to 6 seconds. Is there an issue with Sql 2005 and a large number of columns or anything of that sort? On 2000, the time remains the same , about 8 seconds if I remove this from the select list. Here is the statistics ion on 2005 (21234 row(s) affected) Table 'Worktable'. Scan count 75490, logical reads 3676867, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'LabTestToReportPanel'. Scan count 476, logical reads 1524, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'LabReportPanel'. Scan count 0, logical reads 260, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'DiscreteValue'. Scan count 1, logical reads 176106, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'LabReleasedSampleTest'. Scan count 1, logical reads 2078, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'LabSample'. Scan count 1360, logical reads 18567, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'Form'. Scan count 2302, logical reads 8225, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'LabTest'. Scan count 1, logical reads 23, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'LabSampleDef'. Scan count 1, logical reads 10530, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'LabArea'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'Lab'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'Location'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'Study'. Scan count 0, logical reads 6, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'Item'. Scan count 1335, logical reads 32940, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'ObjectState'. Scan count 1, logical reads 10972, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'Object'. Scan count 0, logical reads 20674, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'Subject'. Scan count 0, logical reads 3293, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'FormDef'. Scan count 2, logical reads 70, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'PrintedLabSampleLabel'. Scan count 0, logical reads 13144, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'PrintedForm'. Scan count 0, logical reads 4219, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'StudySite'. Scan count 0, logical reads 2756, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'StudyEvent'. Scan count 18, logical reads 40, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'StudyEventDef'. Scan count 0, logical reads 36, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'FormDefToStudyEventDef'. Scan count 1, logical reads 43, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'LabSampleDefToFormDef'. Scan count 1, logical reads 255, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Here is the statistics ion on 2000 Table 'LabTestToReportPanel'. Scan count 2123, logical reads 4820, physical reads 44, read-ahead reads 0. Table 'LabReportPanel'. Scan count 130, logical reads 260, physical reads 0, read-ahead reads 0. Table 'DiscreteValue'. Scan count 103914, logical reads 208214, physical reads 0, read-ahead reads 0. Table 'Location'. Scan count 19031, logical reads 38062, physical reads 2, read-ahead reads 0. Table 'Lab'. Scan count 19031, logical reads 38062, physical reads 0, read-ahead reads 0. Table 'LabArea'. Scan count 19031, logical reads 38062, physical reads 0, read-ahead reads 0. Table 'LabSampleDef'. Scan count 24670, logical reads 49340, physical reads 0, read-ahead reads 0. Table 'LabTest'. Scan count 19406, logical reads 39575, physical reads 0, read-ahead reads 0. Table 'LabReleasedSampleTest'. Scan count 4289, logical reads 73865, physical reads 1014, read-ahead reads 24. Table 'Study'. Scan count 4291, logical reads 8582, physical reads 0, read-ahead reads 0. Table 'LabSample'. Scan count 5647, logical reads 31382, physical reads 308, read-ahead reads 4. Table 'Form'. Scan count 4291, logical reads 9272, physical reads 2, read-ahead reads 10. Table 'PrintedLabSampleLabel'. Scan count 4289, logical reads 17097, physical reads 114, read-ahead reads 308. Table 'ObjectState'. Scan count 6860, logical reads 13760, physical reads 1, read-ahead reads 0. Table 'Object'. Scan count 6860, logical reads 23559, physical reads 90, read-ahead reads 701. Table 'PrintedForm'. Scan count 1375, logical reads 4505, physical reads 40, read-ahead reads 16. Table 'StudySite'. Scan count 1378, logical reads 2756, physical reads 4, read-ahead reads 0. Table 'Subject'. Scan count 1599, logical reads 3332, physical reads 2, read-ahead reads 0. Table 'StudyEvent'. Scan count 18, logical reads 52, physical reads 0, read-ahead reads 0. Table 'StudyEventDef'. Scan count 18, logical reads 54, physical reads 0, read-ahead reads 2. Table 'FormDefToStudyEventDef'. Scan count 1, logical reads 69, physical reads 0, read-ahead reads 23. Table 'FormDef'. Scan count 2, logical reads 78, physical reads 1, read-ahead reads 4. Table 'LabSampleDefToFormDef'. Scan count 1, logical reads 308, physical reads 1, read-ahead reads 306. Table 'Item'. Scan count 1335, logical reads 36510, physical reads 140, read-ahead reads 1047. (21234 row(s) affected) (147 row(s) affected) One difference between the two is the work table that 2005 creates versus 2000. I can attach the plans but they are huge. I will attach it if you ask. What I was looking for was suggestions on what I could do short of rewriting code or any suggestions in general. FYI, this has also been posted on the SQL Server Engine forum. Thanks
View Replies !
View Related
|
TRACKER
