David, I'm curious over the overheads. As well as the owner-prefixing you guys have been discussing, I understand you shouldn't name stored procs beginning with "sp_" since the MASTER database is searched before the actual catalog the proc is in.
Do have any article references or any numbers on how much faster/more efficient the different referencing is? For example, how much faster would cmd.Commandtext="dbo.sttg_FileStatus" cmd.CommandType = adCmdStoredProc cmd.Parameters.Append ...etc etc be over cmd.Commandtext="sp_FileStatus" cmd.CommandType = adCmdStoredProc cmd.Parameters.Append ...etc etc over say 1000 executions? cheers Scott - who *loves* optimising stuff... ----- Original Message ----- From: "David L. Penton" <[EMAIL PROTECTED]> To: "ActiveServerPages" <[EMAIL PROTECTED]> Sent: Wednesday, September 04, 2002 3:16 AM Subject: RE: SQL related question : Stop using EM to create and alter your objects. : : Use Query Analyzer and run the scripts. EM does *too much* for you and can : cause problems in the long run (quoted identifiers, ansi settings...) : : BUT...you need to preface with the owner name, whoever you want it to be, : when you create and when you execute in your code. There is an order of : searching that happens in SQL Server when you do not specify the owner. Be : explicit with what you intend rather than make SQL Server decide for you. : : : David L. Penton, Microsoft MVP : JCPenney Technical Specialist / Lead : "Mathematics is music for the mind, and Music is Mathematics for the : Soul. - J.S. Bach" : [EMAIL PROTECTED] : : Do you have the VBScript Docs or SQL BOL installed? If not, why not? : VBScript Docs: http://www.davidpenton.com/vbscript : SQL BOL: http://www.davidpenton.com/sqlbol : : -----Original Message----- : From: Jim MacDiarmid [mailto:[EMAIL PROTECTED]] : : I understand what you're saying, however, when in my current situation when : creating a view in the Enterprise Manager, SQL will automatically preface : the object with the current owner name or "dbo". If I remove it, when I go : to save the view, SQL will re-add the owner name. : : Thanks again, : Jim : : : |-----Original Message----- : |From: Ken Schaefer [mailto:[EMAIL PROTECTED]] : | : |When you create a query you do not need to use the fully qualified : |name *if* : |the object is owned by the user running the query -or- the object is owned : |by dbo : | : |When you run a query that references an unqualified object SQL Server will : |attempt to see if there are any objects owned by the current user with that : |name, and if not, will attempt to see if there are any objects owned by dbo : |that have that name. Only if there are no objects owned by either will it : |bomb with an object not found error message. : | : |So, if a table, or sproc or whatever is owned by dbo, you don't need to put : |that in when writing your query. : | : |Cheers : |Ken : | : |~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ : |From: "Jim MacDiarmid" <[EMAIL PROTECTED]> : |Subject: RE: SQL related question : | : | : |: I don't want to "get rid" of it literally, I just didn't want it showing : |up : |: in the query : |: since it makes it confusing and hard to read. I know from working with : |SQL : |: 7, that when the user : |: created a Database, or Object in the Database, it would belong to that : |user, : |: and show their name : |: unless you did something to that user or to the database or to : |the object, : |: but I can't remember what it was. : |: I just remember that the "dbo" or the user's name did not preface the : |table : |: or object name once : |: something was changed. Sorry if this doesn't make any sense. : |: : |: Thanks for any assistance you can give on this, : |: Jim : | : |~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ : : : --- : You are currently subscribed to activeserverpages as: [EMAIL PROTECTED] : To unsubscribe send a blank email to %%email.unsub%% : --- You are currently subscribed to activeserverpages as: [email protected] To unsubscribe send a blank email to [EMAIL PROTECTED]
