InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 23 May 2013 16:43:14 GMT20http://www.sqlservercentral.com/Forums/Topic628357-1448-1.aspx[quote][b]jmaloney-1117255 (6/7/2011)[/b][hr]Jeff, thank you for taking the time to answer. :-)I suspected that there would likely be some overhead involved. And thank you for the links, I'll check them out straight away. I also read your Tally Table article - fantastic stuff. I think I found my new religion as an anti-RBAR convert. This forum is outstanding![/quote]Apologies for the late response, again! :blush: I managed to burn the candle at both ends and at the middle. :-PThank you very much for the kind words about the Tally Table article. I'm very happy to add you to the "Anti-RBAR Movement". :-)Wed, 15 Jun 2011 17:50:58 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic628357-1448-1.aspxJeff, thank you for taking the time to answer. :-)I suspected that there would likely be some overhead involved. And thank you for the links, I'll check them out straight away. I also read your Tally Table article - fantastic stuff. I think I found my new religion as an anti-RBAR convert. This forum is outstanding!Tue, 07 Jun 2011 10:14:53 GMTj_maloneyhttp://www.sqlservercentral.com/Forums/Topic628357-1448-1.aspx[quote][b]jmaloney-1117255 (5/31/2011)[/b][hr][quote][b]Jeff Moden (1/1/2009)[/b][hr][quote][b]gryphonsclaw (1/1/2009)[/b][hr]You don't have to use a cursor to concatenate a string.DECLARE @myVar varchar(MAX)SET @myVar = '' SELECT @myVar = @myVar + mycolumn + ' 'FROM myTableSELECT @myVar[/quote]The author also pointed that out in the article.[/quote]Apologies for the newbishness, but this bit of SQL kinda blows my mind because I've been a developer for years, but had never come across code like this. How does this work? Seems like there would have to be an internal cursor at work here. Can someone please point out a link explaining how this style block of code works? It would be greatly appreciated.I've started reading Itzik Ben Gan's T-SQL Querying book (currently on Ch 4) and hope to learn new appraches and techniques. Ch 1 was indeed eye-opening on the order in which clauses are processed. Sad to say that the db's I'm currently working with at my new job are rife with cursors and udfs. You couldn't possibly design less performant dbs if you tried. Oh, and not one single comment ... anywhere. What did I get myself into?[/quote]Apologies for the late response. :blush:Think of it as a "set based loop"... something that some of us call a "Pseudo Cursor". For every row in the table, it will add "mycolumn" from the table. So, if the table had the first three letters of the English Alphabet in it, here's what would happen.1. The DECLARE and SET create the variable and turns it from containing a NULL to having an empty string in it.2. The SELECT reads the "first" row from the table and the content of row/column is added to the variable along with a "space" delimiter (the delimiter isn't important here) and reassigned to the variable kind of like say @counter = @counter + 1.The variable now contains "A ".3. The SELECT then reads the "second" row from the table and the same thing happens as in #2 above. The content of the second row is added to the variable and the result is stored back in the variable.The variable now contatins "A B ".4. The SELECT then reads the "third" row from the table and the same thing happens as in #2 and #3 above. The content of the second row is added to the variable and the result is stored back in the variable.The variable now contatins "A B C ".Unfortunately, the technique is a less-than-optimal method and can get quite slow as the variable gets bigger for reasons similar to why the old fashioned "bubble-sort" get's slower... It has to handle more and more data for each row added in. There's a trick with XML Path that you can do in a setbased fashion to concatenate related row information. Please see the following articles for more information...[url]http://www.sqlservercentral.com/articles/Test+Data/61572/[/url][url]http://www.sqlservercentral.com/articles/comma+separated+list/71700/[/url]Sun, 05 Jun 2011 10:25:32 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic628357-1448-1.aspx[quote][b]DCPeterson (5/31/2011)[/b]You are correct. SELECT is part of DML and it even starts its own transaction (assuming implicit transactions) just like INSERT, DELETE, UPDATE... I think the confusion arises from the common misinterpretation of the "M" as modification instead of manipulation as you pointed out, it also might stem in part from the fact that SELECT statements don't fire DML triggers.[/quote]Thank you for verifying the statement. I became unsure for an instant.I have so much to learn and so little (precious) time. :w00t:Best regards,Tue, 31 May 2011 20:38:07 GMTcodebyohttp://www.sqlservercentral.com/Forums/Topic628357-1448-1.aspx[quote][b]Jeff Moden (1/1/2009)[/b][hr][quote][b]gryphonsclaw (1/1/2009)[/b][hr]You don't have to use a cursor to concatenate a string.DECLARE @myVar varchar(MAX)SET @myVar = '' SELECT @myVar = @myVar + mycolumn + ' 'FROM myTableSELECT @myVar[/quote]The author also pointed that out in the article.[/quote]Apologies for the newbishness, but this bit of SQL kinda blows my mind because I've been a developer for years, but had never come across code like this. How does this work? Seems like there would have to be an internal cursor at work here. Can someone please point out a link explaining how this style block of code works? It would be greatly appreciated.I've started reading Itzik Ben Gan's T-SQL Querying book (currently on Ch 4) and hope to learn new appraches and techniques. Ch 1 was indeed eye-opening on the order in which clauses are processed. Sad to say that the db's I'm currently working with at my new job are rife with cursors and udfs. You couldn't possibly design less performant dbs if you tried. Oh, and not one single comment ... anywhere. What did I get myself into?Tue, 31 May 2011 18:28:13 GMTj_maloneyhttp://www.sqlservercentral.com/Forums/Topic628357-1448-1.aspx[quote][b]codebyo (5/31/2011)[/b][hr][quote][b]Mad Myche (5/31/2011)[/b][hr]The SELECT statement may be part of DML, it all depends on the context and who makes the decision. A straight Read-Only SELECT does no manipulation so it would be outside of DML. It is when the SELECT is conjoined with INSERT, INTO or UPDATE that SELECT is considered to be in DML.[/quote]I was just pointing that DML isn't related to modification only.The way I see it manipulation is not same as modification.I've heard people believing SELECT isn't part of DML because of the "modification" word.Selecting data is a form of manipulating data unless I'm mistaken on the concept.Best regards,[/quote]You are correct. SELECT is part of DML and it even starts its own transaction (assuming implicit transactions) just like INSERT, DELETE, UPDATE... I think the confusion arises from the common misinterpretation of the "M" as modification instead of manipulation as you pointed out, it also might stem in part from the fact that SELECT statements don't fire DML triggers.Tue, 31 May 2011 14:33:11 GMTDCPetersonhttp://www.sqlservercentral.com/Forums/Topic628357-1448-1.aspx[quote][b]Mad Myche (5/31/2011)[/b][hr]The SELECT statement may be part of DML, it all depends on the context and who makes the decision. A straight Read-Only SELECT does no manipulation so it would be outside of DML. It is when the SELECT is conjoined with INSERT, INTO or UPDATE that SELECT is considered to be in DML.[/quote]I was just pointing that DML isn't related to modification only.The way I see it manipulation is not same as modification.I've heard people believing SELECT isn't part of DML because of the "modification" word.Selecting data is a form of manipulating data unless I'm mistaken on the concept.Best regards,Tue, 31 May 2011 13:55:55 GMTcodebyohttp://www.sqlservercentral.com/Forums/Topic628357-1448-1.aspx[quote][b]Mhlewis (5/27/2011)[/b][hr]We've had to use cursors in generating notification emails using sp_send_dbmail. We have a list of 5 account execs that get notified when their data is loaded and ready for them to review. I'd love a set based version of sp_send_dbmail. [/quote]This is a bit rough... without tables, I just wrote the basic gist here. But, this will generate a huge dynamic sql statement that will send all the emails at once.[code]DECLARE @SQL nvarchar(max)SELECT@SQL = COALESCE(@SQL + N'; EXEC sp_send_dbmail @Recipients = ''' + @recipientlist + ''',@body = ''Email body'',@subject = ''Subject''',N'; EXEC sp_send_dbmail @Recipients = ''' + @recipientlist + ''',@body = ''Email body'',@subject = ''Subject''')FROM joblisttableEXEC sp_executesql @SQL[/code]Tue, 31 May 2011 08:51:22 GMTmtassinhttp://www.sqlservercentral.com/Forums/Topic628357-1448-1.aspxThe SELECT statement may be part of DML, it all depends on the context and who makes the decision. A straight Read-Only SELECT does no manipulation so it would be outside of DML. It is when the SELECT is conjoined with INSERT, INTO or UPDATE that SELECT is considered to be in DML.Tue, 31 May 2011 08:42:35 GMTMad Mychehttp://www.sqlservercentral.com/Forums/Topic628357-1448-1.aspxI haven't read all posts but...DML is not Data Modification Language. It means Data Manipulation Language and the SELECT statement is also part of it.Anyway great article. :)I've never seen someone using a cursor to concatenate strings before though.Best regards,Sun, 29 May 2011 07:32:19 GMTcodebyohttp://www.sqlservercentral.com/Forums/Topic628357-1448-1.aspx[quote][b]pete.griffiths (5/27/2011)[/b][hr]For people not trained in using set based theory it is understandable to use a rbar approach because this is all that they know. But if you do know it then please use it!Rant over ;)[/quote]Speaking of rants, I'd probably reword that to say something to the effect that if you don't know set-based theory, please stay away from my data and servers. :-DFri, 27 May 2011 23:28:52 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic628357-1448-1.aspxSorry... duplicate post removed.Fri, 27 May 2011 23:18:30 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic628357-1448-1.aspx[quote][b]David Kuhl (5/27/2011)[/b][hr][quote][b]battelofhalfwits (1/1/2009)[/b][hr]Wow...Two things 1) Never use Cursors! Looping through a temp table takes less overhead than a Cursor; especially when you are working on a 24/7/365 server where you really need to watch your resources.[/quote]I want to echo this. I have encountered situations where there was no way around doing row-by-row inserts (database conversion / merger where a stored proc needed to be run to generate values for each line) but when it was first written with cursors our conversion process was going to take about 3 days to run. After replacing the cursors with temp tables and while loops the process was cut down to a few hours. I've never seen a situation where I could not replace a cursor and benefit.D.[/quote]If you had simply changed your cursors to forward only, read only, static cursors, you would have achieved the same result. ;-)Fri, 27 May 2011 23:15:16 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic628357-1448-1.aspxWe've had to use cursors in generating notification emails using sp_send_dbmail. We have a list of 5 account execs that get notified when their data is loaded and ready for them to review. I'd love a set based version of sp_send_dbmail. We also have a batch loading routine that uses a cursor to loop through a rules table in it to write a series of SQL statements that basic unpivot data from columns into records. The SQL statements themselves are set based inserts. It probably could be rewritten to use Unpivot along with some ugly case statements. But no one sees the point since it runs at most daily and isn't even a blip on our resources.Thanks,MwiseFri, 27 May 2011 15:21:58 GMTMWisehttp://www.sqlservercentral.com/Forums/Topic628357-1448-1.aspxTwo points:First off the author, and a number of posts have suggested that "other SQL DBMS systems" are optimized for cursors and that it's only SQL Server that is not. Absolutely, positively, and categorically, this is FALSE! Oracle and DB2 are not magic, they don't somehow process cursors in such a way as to make them fine to use. A set based solution will outperform cursors in those platforms in almost direct proportion to how much it would outperform the same thing in SQL. Oracle in particular does their user community a GREAT disservice by not clarifying this issue.Second, as much as I dislike cursors, and take pride in my ability to avoid them, there are some (very rare) cases where they are in fact the best solution. Categorically stating that they are always bad or always avoidable is not helpful.Fri, 27 May 2011 12:20:05 GMTDCPetersonhttp://www.sqlservercentral.com/Forums/Topic628357-1448-1.aspx[quote][b]rothco (5/27/2011)[/b][hr]As a complete beginner I found the article badly mistitled. It was not Cursors for beginners but why not to use cursors for beginners. A good starting point in an article for beginners is to assume that your reader knows nothing about the subject. This article singularly failed to do this.[/quote]I have to agree with you. I was kind of surprised when I saw the title myself, because I knew the discussion would be cursor bashing, but wasn't expecting the article itself to be.That said, I have learned a lot here on replacements for cursors and other inefficient methods, and have implemented what I can understand and have the ability to. I will use Tally Tables for split functions as an example.The reasons that I still use cursors are a tossup between using 2000 and that the majority of the time it is one time scriptingFri, 27 May 2011 10:59:39 GMTMad Mychehttp://www.sqlservercentral.com/Forums/Topic628357-1448-1.aspx[quote][b]rothco (5/27/2011)[/b][hr]As a complete beginner I found the article badly mistitled. It was not Cursors for beginners but why not to use cursors for beginners. A good starting point in an article for beginners is to assume that your reader knows nothing about the subject. This article singularly failed to do this.[/quote]About the title you are wrigth. Although the purpose off the article seemed explained in the article to me (a non-beginner), avoiding the beginners-reflex for cursors-usage to become a bad habit when there is no need to. (By the way, I started reading this article because I wanted to know if the article also would warn newbiees about the disadvantages off cursors. It did that indeed :-P) As a experienced user reading the discussion I thought, how the hell this level off discussion comes after a 'beginner-article'. Don't think many beginners will make it to this comment.Fri, 27 May 2011 10:08:29 GMTJogoshttp://www.sqlservercentral.com/Forums/Topic628357-1448-1.aspx[quote][b]battelofhalfwits (1/1/2009)[/b][hr]Wow...Two things 1) Never use Cursors! Looping through a temp table takes less overhead than a Cursor; especially when you are working on a 24/7/365 server where you really need to watch your resources.[/quote]I want to echo this. I have encountered situations where there was no way around doing row-by-row inserts (database conversion / merger where a stored proc needed to be run to generate values for each line) but when it was first written with cursors our conversion process was going to take about 3 days to run. After replacing the cursors with temp tables and while loops the process was cut down to a few hours. I've never seen a situation where I could not replace a cursor and benefit.D.Fri, 27 May 2011 09:37:14 GMTDavid Kuhlhttp://www.sqlservercentral.com/Forums/Topic628357-1448-1.aspxI thought the article was well presented. I especially enjoyed the responses.Fri, 27 May 2011 09:30:03 GMTgregg_dnhttp://www.sqlservercentral.com/Forums/Topic628357-1448-1.aspxsomething like this would get you started on a set based solution:with sp_lines(ObjectId, SPName, CRPos, [LineNo])as (select o.id ,o.name ,t.n as CRPos ,ROW_NUMBER() over (partition by o.name order by t.n) as [LineNo]from sys.syscomments cinner join sys.sysobjects o on c.id = o.idleft join Tally t on substring(c.text, t.n, 1) = char(10)where c.text like '%varchar%'and o.xtype = 'P')select current_line.SPName ,substring(c.text, isnull(previous_line.CRPos+1, 1), current_line.CRPos - isnull(previous_line.CRPos+1, 1)) as LineText --,isnull(previous_line.CRPos+1, 1) as LineStart --,current_line.CRPos - isnull(previous_line.CRPos+1, 1) as Length from sp_lines current_lineleft join sp_lines previous_line on current_line.ObjectId = previous_line.ObjectId and current_line.[LineNo] - 1 = previous_line.[LineNo]inner join sys.syscomments c on current_line.ObjectId = c.idwhere substring(c.text, isnull(previous_line.CRPos+1, 1), current_line.CRPos - isnull(previous_line.CRPos+1, 1)) like '%varchar%'Fri, 27 May 2011 08:45:59 GMTpete.griffithshttp://www.sqlservercentral.com/Forums/Topic628357-1448-1.aspxAs a complete beginner I found the article badly mistitled. It was not Cursors for beginners but why not to use cursors for beginners. A good starting point in an article for beginners is to assume that your reader knows nothing about the subject. This article singularly failed to do this.Fri, 27 May 2011 08:32:52 GMTrothcohttp://www.sqlservercentral.com/Forums/Topic628357-1448-1.aspxWhenever possible, I use set-based solutions. But it has occurred in the past where I simply couldn't think one.For example, this is my answer to a question on this site (no-one was able to produce a set-based alternative) : -[code="sql"]CREATE TABLE #SPWithVarchar(SPName VARCHAR(MAX), --Display the stored procedure name LinePos INT, --Display the line number to where the varchar was found. TextPart VARCHAR(MAX)) --Display the code to where the varchar was found --First temporary table, required to get the names of the stored-proceduresCREATE TABLE #TestTemp (Code VARCHAR(MAX), sp_name VARCHAR(MAX))INSERT INTO #TestTemp(Code, sp_name)--sp_helptext "hides" an ugly while loop, so a set-based version would be --preferred. I can't think of a way of getting the line numbers without it--unfortunately.SELECT 'EXEC sp_helptext ''' + o.name + '''', '''' + o.name + ''''FROM sysobjects oWHERE o.xtype = 'P' AND o.category = 0GROUP BY o.nameDECLARE @code VARCHAR(MAX), @sp_name VARCHAR(MAX)DECLARE crap CURSOR FORSELECT code, sp_name FROM #TestTempOPEN crapFETCH NEXT FROM crap INTO @code, @sp_nameWHILE @@FETCH_STATUS = 0BEGIN --Second temporary table, holds the results of "sp_helptext" for each individual --stored-procedure. Uses an identity column to determine the line number of --the stored-procedure. --We then search this table before inserting into your #SPWithVarchar table. CREATE TABLE #TestTemp2 (Number INT IDENTITY,Line VARCHAR(MAX), sp_name VARCHAR(MAX)) SET @code = 'INSERT INTO #TestTemp2(Line) ' + @code --Insert results of "sp_help" into second temp table EXECUTE ('' + @code + '') --Updates second temporary table with the stored-procedure name. UPDATE #TestTemp2 SET sp_name = @sp_name --Inserts the lines from #TestTemp2 where "varchar" appears into #SPWithVarchar. INSERT INTO #SPWithVarchar(spname, linepos, TextPart) SELECT sp_name, number, line FROM #TestTemp2 WHERE line LIKE '%varchar%' --Drop #TestTemp2 so it's ready to be used for the next stored-procedure. DROP TABLE #TestTemp2 FETCH NEXT FROM crap INTO @code, @sp_nameEND--Clean upCLOSE crap DEALLOCATE crapDROP TABLE #TestTemp--Display the resultsSELECT * FROM #SPWithVarchar--DROP TABLE #SPWithVarchar[/code]Fri, 27 May 2011 08:13:07 GMTCadavrehttp://www.sqlservercentral.com/Forums/Topic628357-1448-1.aspxA huge and common mistake I have seen is when a trigger is written from the perspective that only one row is affected.For example:Select @someVar = SomeValueFrom insertedIf the statement above is in a trigger with out some sort of looping around it and the insert/update statement affects more than one row then you only select one value from one of the many rows and skipped all of the others. SQL Server won't error out if you try to jam more than one value into the variable. (Oracle would have thrown an error)Fri, 27 May 2011 06:46:18 GMTgregor.ajhttp://www.sqlservercentral.com/Forums/Topic628357-1448-1.aspxAnyone who knows what happens when a phonenumber is NULL? For 2 reasons I would add an extra filter1) don't select what you don't need as soon as possible, less records passing your filtering will also speed up your process2) working with NULL-values can give surprising effects, so you have to be aware and check ISNULL-function (or as I did here filter them out)DECLARE @AllPhones VARCHAR(1000)SET @AllPhones = ''SELECT @AllPhones = @AllPhones + CASE WHEN P.ListThisNumber = 1 THEN P.PhoneNumber ELSE '***********'END + ' & 'FROM dbo.tblPhone PWHERE codUser = 1[color=#FF0000]AND P.PhoneNumber IS NOT NULL[/color]SELECT @AllPhonesGOFri, 27 May 2011 03:58:40 GMTJogoshttp://www.sqlservercentral.com/Forums/Topic628357-1448-1.aspx[quote][b]dave.farmer (5/27/2011)[/b][hr]Is there a way to achieve any of these things in a set-based fashion? I've not found one... though I'm by no means an expert on these matters. In the latter cases the number of tables involved is small enough that the REBAR makes no odds (around 20 or so tables to count or switch keys on or off for), but the principle matters, and if I can do this a better way I'd like to know before I am faced with a much bigger similar situation![/quote]I think the frothing at the mouth against cursors does need some qualification.T-SQL can fall into two general categories; Process (DDL) and Data (DML) based.Process based could involve using a process table to determine which stored procedures to call or perhaps working through a file list stored in a table to dynamically bulk load files. Or, as you mentioned, going through a series of tables and modifying the indexes/keys. These process based operations benefit from cursors. They could be done with out cursors but any other method method wouldn't be as elegant (IMO).Data based operations, i.e. performing inserts, updates etc... should not be done using a cursorFri, 27 May 2011 03:30:06 GMTSamuel Vellahttp://www.sqlservercentral.com/Forums/Topic628357-1448-1.aspx[quote][b]Jeff Moden (1/2/2009)[/b][hr][quote][b]battelofhalfwits (1/1/2009)[/b][hr]Wow...Two things 1) Never use Cursors! Looping through a temp table takes less overhead than a Cursor; especially when you are working on a 24/7/365 server where you really need to watch your resources.[/quote]Not true... if you use Forward Only, Read Only (or sometimes Static), the cursor is just as "effecient" as the WHILE loop... and make no doubt about it, neither is effecient. If you use a WHILE loop, you've not done any better than using a cursor. ;)[/quote]I use a couple of cursors in my code... One is for retrieving messages from a service broker queue (multiple messages per conversation handle, retrieved into a temp table and then the cursor processes each retrieved message one by one as they have to be - each message will take a different process path depending on what it contains):[code="sql"]-- Declare cursor DECLARE MessageCursor CURSOR LOCAL FORWARD_ONLY READ_ONLY FOR SELECT ConversationHandle ,MessageTypeName ,MessageBody FROM @ReceiveTable ORDER BY QueuingOrder;-- and a little later in the code... WAITFOR( RECEIVE [queuing_order] ,[conversation_handle] ,[message_type_name] ,CAST([message_body] AS NVARCHAR(MAX)) FROM [EventDetailsTargetQueue] INTO @ReceiveTable ), TIMEOUT 5000;-- and process using the cursor --[/code]The other is in a utility procedure that returns record counts and a health check (are there sufficient records) for all the tables involved in a loading process - in this case a table contains the names and minimum acceptable counts for all the loading tables, I use a very simple cursor (SELECT TableName, MinimumRecordCount FROM HealthCheckTables) to retrieve these and for each one I construct a SQL statement to retrieve the count from the table and execute the statement, storing the results in a temp table. This is then used to report the counts, and to throw an error where insufficient records are found in any of the tables (this procedure is used in the load process to halt processing before the live data is affected when we haven't loaded a healthy amount of data)I do loop through a temp table in another component of my loading process to achieve almost the same thing (disabling/enabling all foreign keys on those same loading tables), driven by the same source table and again constructing a SQL statement for each key and executing it.i.e. this gets all the related foreign keys:[code="sql"] SELECT OBJECT_NAME(s.constid) AS ConstraintName ,'[' + sch.[name] + '].[' + OBJECT_NAME(s.fkeyid) + ']' AS TableName INTO #Const_Table FROM sysforeignkeys s INNER JOIN HealthCheckTables h ON h.TableName = OBJECT_NAME (s.rkeyid) INNER JOIN sys.tables t ON t.[name] = OBJECT_NAME(s.fkeyid) INNER JOIN sys.schemas sch ON sch.[schema_id] = t.[schema_id][/code]...and for each one I have to construct an "ALTER TABLE abc {NOCHECK|WITH CHECK CHECK} CONSTRAINT xyz" statement and execute it.These last two, both could use a cursor, or both could use a loop, but essentially they both have to be REBAR processes as far as I can tell, and similarly for the service broker processing.Is there a way to achieve any of these things in a set-based fashion? I've not found one... though I'm by no means an expert on these matters. In the latter cases the number of tables involved is small enough that the REBAR makes no odds (around 20 or so tables to count or switch keys on or off for), but the principle matters, and if I can do this a better way I'd like to know before I am faced with a much bigger similar situation!Dave.Fri, 27 May 2011 02:57:51 GMTdave.farmerhttp://www.sqlservercentral.com/Forums/Topic628357-1448-1.aspxIn all my years of writing database code, the only times I have ever opted to use cursors is when executing a parameterised statement across different servers or databases. I wouldn't like to try and count the number of times that I have stared into space for minutes at a time trying to work out the required complexities of the joins needed to accomodate a particular task instead of resorting to rbar, but isn't this the point of what DBA's and Database programmers do? We are specialised because the platform requires a specialised skill set and if we don't use it then we are just wasting what the RDBMS can do and, quite frankly, being lazy.For people not trained in using set based theory it is understandable to use a rbar approach because this is all that they know. But if you do know it then please use it!Rant over ;)Fri, 27 May 2011 02:54:59 GMTpete.griffithshttp://www.sqlservercentral.com/Forums/Topic628357-1448-1.aspxAgain... the reason I did what I did was I couldn't come up with a way to run[code]DBCC CHECKPRIMARYFILE (N''' + @filename + ''',2)[/code]Which gets the actual name of the database from the database itself.I had found in my testing that 90% of the databases followed our naming conventions, but the other 10% (400) did not. So I needed to probe the mdf file to find out what the actual database name was. New databases all follow the naming convention. The older databases are used by our customers as well though and we had a very small time window to do this (approx 5 hours, including copying almost 2 TB of Databases to their new homes). Tracing how SSMS 2K5 does it showed me DBCC CHECKPRIMARYFILE, which only appears to work on an mdf that isn't attached to a SQL server, it's also not documented in BOL (yet it's what MS uses).Since the DBCC command returns a small result set for a single file at a time, I couldn't come up with a good way to do it other than to run DBCC CHECKPRIMARYFILE for each database mdf file, one at a time.All told, the attach routine took less than 10 minutes to attach 1000 databases per instance to each instance. The rest of the time was spent copying mdf files to their new homes.Wed, 07 Jan 2009 09:49:40 GMTmtassinhttp://www.sqlservercentral.com/Forums/Topic628357-1448-1.aspx[quote][b]mtassin (1/5/2009)[/b][hr]I dunno if this qualifies as a weird case where you need a cursor or not.Certainly if this could be done as one big set based query, I'd go for it... but not sure how.I wrote this little puppy when we needed to shut down a SQL instance with 4000 databases on it, move the databases to 4 seperate instances (1000 per instance) and reattach them.Didn't care for using the cursor... but couldn't figure out how to use DBCC CHECKPRIMARYFILE in set based methods.[/quote]Here is something similar I wrote a while ago for a similar (though much smaller, the most I've used it for is about 10 databases, also less general since we have naming conventions in place so I could safely assume certain things about the names) situation:[code]/* written by Tim Wiseman 11 Jun 08 This attaches every database in the path folder to the server instance.It makes some assumptions about the name format of the database files,but should catch the most common cases.Useful for rebuilding servers. */sp_configure 'show advanced options', 1GOReconfigureGOsp_configure 'xp_cmdshell', 1GOReconfigureGOdeclare @path nvarchar(250)declare @cmd nvarchar(250)Declare @dbname nvarchar(250)declare @datafile nvarchar(250)declare @logfile nvarchar(250)Declare @sql varchar (8000)set @path = 'D:\DataFiles\' --Change this to reflect the actual pathdeclare @FileList Table (FileN varchar(250))set @cmd = N'dir ' + @path + ' /b'insert into @FileList (FileN)exec xp_cmdshell @cmdWhile exists (select * from @FileList where FileN like '%.mdf')Begin select @dbname = replace(replace(replace(FileN, '.mdf', ''), '_data', ''), '.mdb', '') from @FileList where FileN like '%.mdf' OR FileN like '%.mdb' select @datafile = FileN from @FileList where FileN like @dbname + '%.md%' select @logfile = FileN from @filelist where FileN like @dbname + '%.ldf' if not exists (select name from sys.databases where name like @dbname) begin set @sql = ' CREATE DATABASE [' + @Dbname + '] ON ( FILENAME = N''' + @Path + @datafile+''' ), ( FILENAME = N'''+@Path + @logfile +''' ) FOR ATTACH ' print @sql Exec (@sql) End delete from @FileList where FileN like @dbname + '%'end --End while loopselect * from @filelist[/code]This does use a loop, but it avoids explicitly using a cursor. If you wanted a truly set based solution, this could be rewritten so it generates all the need commands, separated by semicolons, as one enormous @sql string and then simply executes that string. Wed, 07 Jan 2009 08:52:27 GMTtimothyawisemanhttp://www.sqlservercentral.com/Forums/Topic628357-1448-1.aspxThanks Jeff and Wagner. I took a class on database theory that used Oracle for all examples, but for the most part I have used SQL Server. Even in that class, the focus was on the theory such as normalization and standard data storage aspects more than any practical design.Wed, 07 Jan 2009 08:45:51 GMTtimothyawisemanhttp://www.sqlservercentral.com/Forums/Topic628357-1448-1.aspxExtending a bit more on Jeff's comments, this is true not only for ORACLE (PL-SQL), but also for DB2 (SQL PL).Like ORACLE, DB2 obligates you to use cursors in situations we would not even think they should be used. Besides, DB2 official documentation has lots of examples that use cursors for nothing.I have to say this really annoyed me when I started developing code for DB2.Tue, 06 Jan 2009 19:04:20 GMTwagner crivelinihttp://www.sqlservercentral.com/Forums/Topic628357-1448-1.aspx[quote][b]timothyawiseman (1/6/2009)[/b][hr][quote][b]Jeff Moden (1/1/2009)[/b][hr]I believe that articles like this one serve as a great disservice to anyone new to databases and believe that it's horribly irresponsible of an author to try to bring any credibility to any form of RBAR programming in any RDBMS.[/quote]I am greatly opposed to cursors in T-SQL, but I have been told that in Oracle with PL SQL was optimized for cursors and that they often run faster than the set based solution. Is that correct?[/quote]It's true that cursors in Oracle have been optimized to be pretty fast. The "proper" set based solutions still beat them, though. Now, the real problem with Oracle is that you can't return a result set from Oracle to a GUI in a direct fashion like you can with SQL Server. Instead, you have to write a "reference cursor" in Oracle. Those are really pretty damned fast and that may be where most of the reported cursor speed comes from. It's been a bit since I've had to write in Oracle (THANK YOU GOD!!!), but the set based solutions I wrote were always faster than the equivalent cursor based solution someone else came up with.Tue, 06 Jan 2009 18:22:06 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic628357-1448-1.aspx[quote][b]Jeff Moden (1/1/2009)[/b][hr]I believe that articles like this one serve as a great disservice to anyone new to databases and believe that it's horribly irresponsible of an author to try to bring any credibility to any form of RBAR programming in any RDBMS.[/quote]I am greatly opposed to cursors in T-SQL, but I have been told that in Oracle with PL SQL was optimized for cursors and that they often run faster than the set based solution. Is that correct?Tue, 06 Jan 2009 14:48:41 GMTtimothyawisemanhttp://www.sqlservercentral.com/Forums/Topic628357-1448-1.aspxYou certainly do not need a cursor for this. The same technique that I used above will work here also.Mon, 05 Jan 2009 09:52:59 GMTRBarryYounghttp://www.sqlservercentral.com/Forums/Topic628357-1448-1.aspxI will go on record saying that in my book this is a situation where a cursor is perfectly acceptable if not preferred.Mon, 05 Jan 2009 09:24:10 GMTTheSQLGuruhttp://www.sqlservercentral.com/Forums/Topic628357-1448-1.aspxI dunno if this qualifies as a weird case where you need a cursor or not.Certainly if this could be done as one big set based query, I'd go for it... but not sure how.I wrote this little puppy when we needed to shut down a SQL instance with 4000 databases on it, move the databases to 4 seperate instances (1000 per instance) and reattach them.[code]CREATE PROCEDURE [dbo].[usp_attach_dir]@dirname varchar(max)ASSET NOCOUNT ON/***********************************************Procedure: usp_attach_dirAuthor: Mark TassinDate: 11/06/2008Purpose: To attach all the databases in a directory that are not already attached to the SQL instanceExecution: sp_attach_dir ' 'Outputs: DBCC execution completed. If DBCC printed error messages, contact your system administrator. The above message should repeat once for every database attached.Requirements: SQL Server 2005 SP2+ ************************************************//***** Test Data ************DECLARE @dirname varchar(max)set @dirname = 'E:\MSSQL\DATA'********************************/DECLARE @cmd nvarchar(max)DECLARE @filename varchar(max)DECLARE @dbname sysname--Test for trailing backslash in the dirname parameterIF right(@dirname,1) != '\' SET @dirname = @dirname + '\'-- First we get a list of all the files in the input directoryCREATE TABLE #results(subdir varchar(max),depth int,isfile int) set @cmd = N'insert into #results exec xp_dirtree ''' + @dirname +''',1,1'EXEC sp_executesql @cmd--Remove non-files that get picked updelete from #results where isfile != 1--Remove non-SQL data filesdelete from #results where right(subdir,4) != '.mdf'--Append the directory to the filenamesupdate #resultsset subdir = @dirname + subdir--Remove system database data files that are used by SQL server, but don't get actual database entries in sys.databasesDELETE FROM #results where subdir like '%\distmdl.mdf%' OR subdir like '%\mssqlsystemresource.mdf%'--Create a table to store the output from sys.files for each dbCREATE TABLE #sysf( [fileid] [smallint] NULL, [groupid] [smallint] NULL, [size] [int] NOT NULL, [maxsize] [int] NOT NULL, [growth] [int] NOT NULL, [status] [int] NULL, [perf] [int] NULL, [name] [sysname] NOT NULL, [filename] [nvarchar](260) NOT NULL, [dbname] [sysname] DEFAULT DB_Name()) ON [PRIMARY]--Get a list of all the files used by each db on the serverexec sp_msforeachdb 'insert into #sysf(fileid,groupid,size,maxsize,growth,status,perf,name,filename,dbname)select fileid,groupid,size,maxsize,growth,status,perf,name,filename,''?'' from ?.dbo.sysfiles'--Cursor to attach the dbs not on the server alreadyDECLARE csr_attachdbs CURSOR FAST_FORWARD FORSELECTa.subdirFROM #results aWHERENOT EXISTS (SELECT * FROM #sysf b WHERE a.subdir = b.filename)CREATE TABLE #fileinfo([property] sql_variant NULL, [value] sql_variant NULL)OPEN csr_attachdbsFETCH NEXT FROM csr_attachdbs INTO @filenameWHILE @@FETCH_STATUS = 0BEGIN SET @cmd = N'DBCC CHECKPRIMARYFILE (N''' + @filename + ''',2)' INSERT INTO #fileinfo EXEC(@cmd) SELECT @dbname = cast([value] as sysname) FROM #fileinfo WHERE cast([property] as varchar)= 'Database name' SET @cmd = N'CREATE DATABASE ' + @dbname + ' ON (FILENAME=''' + @filename + ''') FOR ATTACH_REBUILD_LOG' EXEC sp_executesql @cmd TRUNCATE TABLE #fileinfo FETCH NEXT FROM csr_attachdbs INTO @filenameENDCLOSE csr_attachdbsDEALLOCATE csr_attachdbsDROP TABLE #resultsDROP TABLE #sysfDROP TABLE #fileinfo[/code]Didn't care for using the cursor... but couldn't figure out how to use DBCC CHECKPRIMARYFILE in set based methods.Mon, 05 Jan 2009 09:05:50 GMTmtassinhttp://www.sqlservercentral.com/Forums/Topic628357-1448-1.aspxI guess I do it backwards, then. I write out one "iteration" in regular code and test the willicurs out of it. Then, I just do a search and replace to double up all apostrophes, add a starting apostrophe and one at the end, to a couple of very simple replacements for the dynamic parts (QUOTENAME can be usefull here), and I'm done.Sat, 03 Jan 2009 15:32:01 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic628357-1448-1.aspxIn my experience, the difficulty is all in figuring out how to stack and nest the strings during construction so that it comes out the way it would if you had written it directly. It takes some indirect thinking and it is a little unnatural, but not that hard, once you get used to it.The most important tip I've found for this is to always PRINT @cmd, instead of EXEC(@cmd) during development. Makes it a lot easier to get it just right.The other tough thing is counting the apostrophes, especially when you have to start nesting your contexts. I start to lose it when I get more than 2 deep and I have to throw anywhere from 3 to 16 apostrophes in a row. Ugh. :crazy:Sat, 03 Jan 2009 14:33:15 GMTRBarryYounghttp://www.sqlservercentral.com/Forums/Topic628357-1448-1.aspxYeah... you're right, of course... I didn't think about those particular things... I think it's because I dream of a database where developers won't be dropping indexes out from under the DBA... that there will actually be "change controls" for such actions. Wishful thinking, huh? :PI still don't see why people think that the method you used would make error checking all that difficult. It would basically be done the same way as if a loop were running and one of the loops had dynamic SQL that failed. Maybe, I'm missing something.Sat, 03 Jan 2009 14:14:35 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic628357-1448-1.aspxOh no, not for this kind of thing. For instance, this is rebuilding or reorging for each fragmented index that it finds, but there is still stuff that can go wrong, either:1) between when the command is generated and when it actually gets to it on the execution list: Like someone may have dropped the index. Or, 2) stuff that is impractical to check ahead of time, like there's a DDL lock on one of the tables because I've got the Table Wizard open on it and I forgot to close it.Sat, 03 Jan 2009 12:42:48 GMTRBarryYoung