InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 23 May 2013 10:06:54 GMT20http://www.sqlservercentral.com/Forums/Topic496042-203-1.aspx[quote][b]Jeff Moden (4/7/2013)[/b][hr]With the understanding that I've not seen nor have I been able to produce such a failure except when an indeterminate formula was used as the target of the ORDER BY, I guess the next question would be, is there a code example somewhere that demonstrates failure of the TOP (MaxIntValue)/ORDER BY? The one that MS published never seems to fail.[/quote]As long as you are not querying tables with more than [i]maxint[/i] rows, this method [b]currently[/b] always works. Just as the version with TOP 100 PERCENT always worked until it stopped working in SQL Server 2005. But as far as I know, it's not documented, nor guaranteed.[quote]Also, is there a work around that will produce the desired behavior other than the obvious method of shifting to FOR XML PATH? Or, is that also subject to ORDER BY failing (which would really screw up some real XML)?[/quote]There are two issues here, and we should take care not to confuse them.1: ORDER BY in a subquery (or view) - except for some specific exceptions, this is not allowed. In the situations where it is allowed (off the top of my head only TOP and FOR XML), the ORDER BY is guaranteed to be used to specify what rows to qualify/disqualify for the TOP and is also guaranteed to process the rows in the specified order when forming the XML. But not guaranteed to do anything else. If you have TOP 5 ... ORDER BY FirstName in a subquery or view, you will only get names like Aaron and Abby, but unless there is an ORDER BY in the outer query as well, you can still get them in any order. They [b]usually[/b] stay in alphabetical order, but this is not guaranteed. For the FOR XML construct, the result is just a single row, so ordering does not apply at all anymore.2: Concatenation by variable assignment (SELECT @x = @x + Column FROM ...), either with or without ORDER BY. I have once seen a website with a number of examples that were reproducible and that showed this technique to be unreliable. Unfortunately, I lost the URL and have never been able to find it again. And frankly, I have no idea if any of those examples would have reproduced on newer versions (like SQL Server 2005, or up - yes, it is THAT long ago since I found that site).There is a very weird article in the Knowledge Base: [url=http://support.microsoft.com/kb/287515]http://support.microsoft.com/kb/287515[/url]. It first says that "the correct behaviour for an aggregate concatenation query is undefined" (which as far as I know is still true). However, it then further down does provide a workaround "to achieve the expected results from an aggregate concatenation query", without bothering to specify what the "expected results" for this undefined behaviour should be.[quote]Also, is there a work around that will produce the desired behavior other than the obvious method of shifting to FOR XML PATH? Or, is that also subject to ORDER BY failing (which would really screw up some real XML)?[/quote]The only guaranteed methods I know to correctly concatenate strings are:1: FOR XML2: Procedural code (i.e. looping over the result set)3: Creating your own CLR user-defined aggregate function. When this feature was new, there was no option to preserve order in CLR user-defined aggregates (there was syntax for it, but it had no effect). I have no idea if this has changed since; I think it has not in SQL Server 2008, but I stopped paying attention after that.If you use ORDER BY and FOR XML at the same level of the query, you can rely on the results being added to the XML result in the desired order. If you use ORDER BY at a different subquery-level, it's anyone's guess.Sun, 07 Apr 2013 14:06:31 GMTHugo Kornelishttp://www.sqlservercentral.com/Forums/Topic496042-203-1.aspx[quote][b]Hugo Kornelis (4/7/2013)[/b][hr][quote][b]Jeff Moden (4/7/2013)[/b][hr][quote][b]david.holley (4/7/2013)[/b][hr]Would wrapping the SELECT inside another work if the specific order is a concern? Something like...SELECT @NewString = @NewString + NameInitials FROM(SELECT NameInitials FROM Employees WHERE OfficeId = 1001 ORDER BY NameInitials) as s[/quote]Damn. I was just looking at an MS white paper last night (for a totally different reason) that said this is no guarantee, either, and didn't save the URL. But I believe I know how to fix even that problem.First, since you're using ORDER BY in a sub-query (Derived Table, in this case), you must also use TOP. That's where most people end up making the mistake. For simplicity, most will use TOP 100%. I can't prove it but I remember one of the folks over on the SQLTeam forums proving that TOP 100% can sometimes cause the ORDER BY to be ignored in a sub-query. Instead, they recommended the use of the max integer value of 2147483647.As a sidebar, I kick myself everytime I remember something like this and forget to save the URL from where I found it some years ago.Of course, the "best" way to do it now is to use the XML hack that I provided a link for.[/quote]Until SQL Server 2000, the plan would include a sort, in order to satisfy the TOP clause. Usually, that sort order would then be retained throughout the rest of the plan - though that was never guaranteed - if the order by is in a subquery, it does not define the order in which rows of the final query are returned.Then, in SQL Server 2005, the optimizer was improved to recognize a "TOP 100 PERCENT" as superfluous. And that meant that the sort step, which was only needed to execute that TOP clause, could be removed from the plan as well. This broke an enormous amount of code that relied on this trick, in spite of it being undocumented and many experts warning against it. (Some of that code was in use by Microsoft customers with enough influence that MS even released a hotfix that implemented a trace flag you could activate return to the old behaviour, and that trace flag survived into SQL Server 2008 as well - see [url=http://support.microsoft.com/kb/926292]http://support.microsoft.com/kb/926292[/url]).I've seen the advise to use TOP with an insane high integer value, but I don't support it. First, there are tables that include more rows, and then this TOP clause could cause incorrect results to be returned. But probably more important: peoplpe should consider that a future improvement of the optimizer might also include an optimization to remove the top and associated sort if the optimizer can be sure that the actual number of rows will never come evenn near that amount. At that point, all code using this trick can stop working.[/quote]Thanks for the info and the link, as well.With the understanding that I've not seen nor have I been able to produce such a failure except when an indeterminate formula was used as the target of the ORDER BY, I guess the next question would be, is there a code example somewhere that demonstrates failure of the TOP (MaxIntValue)/ORDER BY? The one that MS published never seems to fail.Also, is there a work around that will produce the desired behavior other than the obvious method of shifting to FOR XML PATH? Or, is that also subject to ORDER BY failing (which would really screw up some real XML)?Sun, 07 Apr 2013 12:58:16 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic496042-203-1.aspx[quote][b]Jeff Moden (4/7/2013)[/b][hr][quote][b]david.holley (4/7/2013)[/b][hr]Would wrapping the SELECT inside another work if the specific order is a concern? Something like...SELECT @NewString = @NewString + NameInitials FROM(SELECT NameInitials FROM Employees WHERE OfficeId = 1001 ORDER BY NameInitials) as s[/quote]Damn. I was just looking at an MS white paper last night (for a totally different reason) that said this is no guarantee, either, and didn't save the URL. But I believe I know how to fix even that problem.First, since you're using ORDER BY in a sub-query (Derived Table, in this case), you must also use TOP. That's where most people end up making the mistake. For simplicity, most will use TOP 100%. I can't prove it but I remember one of the folks over on the SQLTeam forums proving that TOP 100% can sometimes cause the ORDER BY to be ignored in a sub-query. Instead, they recommended the use of the max integer value of 2147483647.As a sidebar, I kick myself everytime I remember something like this and forget to save the URL from where I found it some years ago.Of course, the "best" way to do it now is to use the XML hack that I provided a link for.[/quote]Until SQL Server 2000, the plan would include a sort, in order to satisfy the TOP clause. Usually, that sort order would then be retained throughout the rest of the plan - though that was never guaranteed - if the order by is in a subquery, it does not define the order in which rows of the final query are returned.Then, in SQL Server 2005, the optimizer was improved to recognize a "TOP 100 PERCENT" as superfluous. And that meant that the sort step, which was only needed to execute that TOP clause, could be removed from the plan as well. This broke an enormous amount of code that relied on this trick, in spite of it being undocumented and many experts warning against it. (Some of that code was in use by Microsoft customers with enough influence that MS even released a hotfix that implemented a trace flag you could activate return to the old behaviour, and that trace flag survived into SQL Server 2008 as well - see [url=http://support.microsoft.com/kb/926292]http://support.microsoft.com/kb/926292[/url]).I've seen the advise to use TOP with an insane high integer value, but I don't support it. First, there are tables that include more rows, and then this TOP clause could cause incorrect results to be returned. But probably more important: peoplpe should consider that a future improvement of the optimizer might also include an optimization to remove the top and associated sort if the optimizer can be sure that the actual number of rows will never come evenn near that amount. At that point, all code using this trick can stop working.Sun, 07 Apr 2013 10:07:39 GMTHugo Kornelishttp://www.sqlservercentral.com/Forums/Topic496042-203-1.aspx[quote][b]david.holley (4/7/2013)[/b][hr]Would wrapping the SELECT inside another work if the specific order is a concern? Something like...SELECT @NewString = @NewString + NameInitials FROM(SELECT NameInitials FROM Employees WHERE OfficeId = 1001 ORDER BY NameInitials) as s[/quote]Damn. I was just looking at an MS white paper last night (for a totally different reason) that said this is no guarantee, either, and didn't save the URL. But I believe I know how to fix even that problem.First, since you're using ORDER BY in a sub-query (Derived Table, in this case), you must also use TOP. That's where most people end up making the mistake. For simplicity, most will use TOP 100%. I can't prove it but I remember one of the folks over on the SQLTeam forums proving that TOP 100% can sometimes cause the ORDER BY to be ignored in a sub-query. Instead, they recommended the use of the max integer value of 2147483647.As a sidebar, I kick myself everytime I remember something like this and forget to save the URL from where I found it some years ago.Of course, the "best" way to do it now is to use the XML hack that I provided a link for.Sun, 07 Apr 2013 07:48:13 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic496042-203-1.aspx[quote][b]L' Eomot Inversé (4/6/2013)[/b][hr][quote][b]Jeff Moden (4/6/2013)[/b][hr][quote][b]L' Eomot Inversé (4/6/2013)[/b][hr]The order of elements in the result string is not guaranteed to be determined by the order by clause, so...[/quote]I've heard about that but haven't been able to cause it. Do you have a link or some code that demonstrates the problem?[/quote]No, unfortunately I don't have anything that demonstrates it. I think I have experienced it, though.I was bitten by it, I think, back in 2005 - it started happening with our system on a machine belonging to one of our customers, but not on other customers' machines and not in-house, although the code and the relevant part of the schema was identical in all the machines, both those affected and those not - only the data was different according to (a) what the customer had contracted for and (b) what use the customer had made of his stuff, and the hardware was different according to when it had been installed. We ended up changing that piece of code on all the systems to do it with a loop, since our customers expected 24 X 7 correct operation apart from a monthly break (scheduled ad hoc by the customer to fit his needs, not the same time every month) to apply MS patches and any of our fixes that would need downtime. Unfortunately this was at a time when I was swamped with too much administrative work (we didn't have enough people) so although the exact details were on file in our support record I didn't make a personal copy. Now I no longer have access - I left the company nearly 4 years ago, after transferring responsability for all technical matters from London to Beirut, and since then the company's London and Dubai operations have been reduced even further so the record may no longer exist.edit: I should probably mention that in the cases where I said there's a good chance, my personal belief is that it will always work in those cases - but people whom I probably ought to believe have told me there's no guarantee even in those cases. And where I've said there's not such a good chance, I suspect that the chance of it working is still quite high, but think I have seen it fail once.[/quote]Thanks, Tom. I've never actually seen the out-of-order problem happen either and it might be because of the precautions I normal take when doing such things. I do suspect (but haven't taken the time to prove) that the problem occurs when the unique and or clustered indexes are just right [i]and [/i]parallelism occurs. Because of the possibility of it coming out wrong, I normally add the ol' OPTION(MAXDOP 1) to such a thing when I can. I can imagine that a join within the concatenation overlay in a single variable might still gum up the works. That might also be why I've never seen it. I try to keep my code pretty simple and modular. I don't mix joins with such string tricks except for the Tally Table.I do wish that MS had two different ORDER BYs... one that occurred as the processing occurred and one after the processing completed.Sun, 07 Apr 2013 07:39:05 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic496042-203-1.aspxWould wrapping the SELECT inside another work if the specific order is a concern? Something like...SELECT @NewString = @NewString + NameInitials FROM(SELECT NameInitials FROM Employees WHERE OfficeId = 1001 ORDER BY NameInitials) as sSun, 07 Apr 2013 06:52:04 GMTdavid.holleyhttp://www.sqlservercentral.com/Forums/Topic496042-203-1.aspx[quote][b]Jeff Moden (4/6/2013)[/b][hr][quote][b]L' Eomot Inversé (4/6/2013)[/b][hr]The order of elements in the result string is not guaranteed to be determined by the order by clause, so...[/quote]I've heard about that but haven't been able to cause it. Do you have a link or some code that demonstrates the problem?[/quote]No, unfortunately I don't have anything that demonstrates it. I think I have experienced it, though.I was bitten by it, I think, back in 2005 - it started happening with our system on a machine belonging to one of our customers, but not on other customers' machines and not in-house, although the code and the relevant part of the schema was identical in all the machines, both those affected and those not - only the data was different according to (a) what the customer had contracted for and (b) what use the customer had made of his stuff, and the hardware was different according to when it had been installed. We ended up changing that piece of code on all the systems to do it with a loop, since our customers expected 24 X 7 correct operation apart from a monthly break (scheduled ad hoc by the customer to fit his needs, not the same time every month) to apply MS patches and any of our fixes that would need downtime. Unfortunately this was at a time when I was swamped with too much administrative work (we didn't have enough people) so although the exact details were on file in our support record I didn't make a personal copy. Now I no longer have access - I left the company nearly 4 years ago, after transferring responsability for all technical matters from London to Beirut, and since then the company's London and Dubai operations have been reduced even further so the record may no longer exist.edit: I should probably mention that in the cases where I said there's a good chance, my personal belief is that it will always work in those cases - but people whom I probably ought to believe have told me there's no guarantee even in those cases. And where I've said there's not such a good chance, I suspect that the chance of it working is still quite high, but think I have seen it fail once.Sat, 06 Apr 2013 20:34:58 GMTL' Eomot Inverséhttp://www.sqlservercentral.com/Forums/Topic496042-203-1.aspx[quote][b]jjturner (4/5/2013)[/b][hr]Thanks David - that was quick! I'll have a go at translating this into VBA and see what happens.Although it does look like there's no way around firing a bunch of SELECT statements with a cursor (unless I'm misreading it)...Cheers,John[/quote]Although the function that David demonstrated is a tried and true method, there's no need for a function at all. It can be done using an XML hack that is quite popular. Please see the following article.[url]http://www.sqlservercentral.com/articles/comma+separated+list/71700/[/url]Sat, 06 Apr 2013 17:20:42 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic496042-203-1.aspx[quote][b]L' Eomot Inversé (4/6/2013)[/b][hr]The order of elements in the result string is not guaranteed to be determined by the order by clause, so...[/quote]I've heard about that but haven't been able to cause it. Do you have a link or some code that demonstrates the problem?Sat, 06 Apr 2013 17:17:06 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic496042-203-1.aspx[quote][b]david.holley (4/5/2013)[/b][hr]I don't know if you can do that with a TallyTable or if a TT is neccessary. This is a function that should accomplish it. It simply adds the next selected value to the previous. And no, I didn't figure this out myself.[/quote]You need to add something to that code to check that you are getting things in the right order and do something else if not. The order of elements in the result string is not guaranteed to be determined by the order by clause, so you need to include code for each row that checks the required order has not yet been violated and force an error if it has. That is actually veru easy to do, only a small modification. But you also have to have code to deal with the error, presumably by using an explicity loop to do the job instead of the single statement.Whether order the elements of the result string are in depends on how the optimiser decides to execute that query, which potentially changes when the table content changes or when the optimiser is updated (hotfix, service pack, or new release) or when an index on the table is added, altered, or dropped.If the table is clustered on the id column and there is no covering index for the query, or the table is clustered on a covering index for the table whose first element is id, of the table is an heap with a covering index for the query and that indexes first element ois id, you stand a good chance of getting the behaviour you want (in most other cases you don't) but it's still not guaranteed. Also, if the query works today that doesn't guarantee it will work tomorrow - for example adding some rows to the table (or deleting some) may cause the optimiser to adopt a different query plan. And it may not work after applying an update to SQL Server or in a new release, because the optimiser may have changed a bit and adopt a different query plan.Sat, 06 Apr 2013 06:25:27 GMTL' Eomot Inverséhttp://www.sqlservercentral.com/Forums/Topic496042-203-1.aspx[quote][b]jjturner (4/5/2013)[/b][hr]hmm... seeing that my data is in Access and the recordsets are pretty light, I may opt to keep everything local with the VBA cursor on a custom function.Otherwise I'd have to somehow pass the recordset from Access into the whole sp call. But thanks again for laying the groundwork on the ADO parameter piece!Cheers,John[/quote]Nope. 'Pass Through Query' is only the term. From a developer standpoint, a Pass Through Query is just another recordset.We should open a new thread as this is off topic.Fri, 05 Apr 2013 14:33:59 GMTdavid.holleyhttp://www.sqlservercentral.com/Forums/Topic496042-203-1.aspxhmm... seeing that my data is in Access and the recordsets are pretty light, I may opt to keep everything local with the VBA cursor on a custom function.Otherwise I'd have to somehow pass the recordset from Access into the whole sp call. But thanks again for laying the groundwork on the ADO parameter piece!Cheers,JohnFri, 05 Apr 2013 14:21:47 GMTjjturnerhttp://www.sqlservercentral.com/Forums/Topic496042-203-1.aspx[quote][b]jjturner (4/5/2013)[/b][hr]Ok, thanks for the confirm - now I'll have my work cut out for me trying to build a solution with ADO slinging around recordsets between Access and SQL Server![/quote]This code will update a pass through query that relies on a stored procedure that requires parameters. The idea is that you would create the sp in SQL Server, then create the pass through query in Access. Your code would then call this sub to update the pass through query to change the parameters before the pass through query is executed. This might be neccessary if you have form based on the pass through query in which case it would be called on the Form_Load event. Or it might be executed before you open a Select statement on the pass through query via .OpenRecord set. strQryDefName - Name of the Access object that represents the passthrough querystrStatement - Actual statement to the update the passthrough query to, this must include any parameters such as...[code]Sample usage... This code builds the parameters passed to the UpdatePassthroughQuery sub by looking up the RecordSource from a table (eg: view_UsageByDate) and then builds the parameters by looping through the controls on a form to determine if a value has been entered. It is a rather nifty approach to supplying criteria driven reports where the criteria is not known and needs to be flexible </tooHorn>. If Me.pg1_lstReports.Column(3) = True Then'Lookup the SQL Server view/recordsource to us strRecordSourceName = Nz(DLookup("txtRecordSourceName", "qry_sp_getReportDefinitions", "[lngId] = " & [Forms]![frmReports]![pg1_lstReports]), "") strRecordSourceStatement = Nz(DLookup("txtRecordSourceStatement", "qry_sp_getReportDefinitions", "[lngId] = " & [Forms]![frmReports]![pg1_lstReports]), "") 'How to test if either are invalid 'Loop through the criteria for the report, check if it exists in strRecordSourceStatement and replace 'Not certain how I feel about this over all CriteriaArray = Split(Me.pg1_lstReports.Column(2), ";") For i = 0 To UBound(CriteriaArray) ControlSetting = Split(CriteriaArray(i), "=") If InStr(1, strRecordSourceStatement, ControlSetting(0)) > 0 Then strRecordSourceStatement = Replace(strRecordSourceStatement, ControlSetting(0), Me.Controls(ControlSetting(0))) End If Next i Call updatePassThroughQuery(strRecordSourceName, strRecordSourceStatement) End If[/code][code]Public Sub updatePassThroughQuery(strQryDefName As String, strStatement As String) 'Updates a pass through query to allow parameters to be passed in 'For ease of development the pass through is created if it doesn't exist 'http://support.microsoft.com/kb/131534 Dim MyDb As Database Dim MyQ As QueryDef Dim QryDefExists As Boolean Dim i As Integer Set MyDb = CurrentDb() QryDefExists = False For i = 0 To MyDb.QueryDefs.Count - 1 Debug.Print MyDb.QueryDefs(i).Name If MyDb.QueryDefs(i).Name = strQryDefName Then QryDefExists = True Next i If QryDefExists Then Set MyQ = MyDb.QueryDefs(strQryDefName) MyQ.SQL = strStatement MyQ.Close MyDb.Close Set MyQ = Nothing Else Set MyQ = MyDb.CreateQueryDef(strQryDefName) MyQ.Connect = "ODBC;DSN=TrailerManagementSystem;Description=Connection Trailer Management System SQL Server;Trusted_Connection=Yes" MyQ.ReturnsRecords = True MyQ.SQL = strStatement MyQ.Close MyDb.Close Set MyQ = Nothing End If Set MyQ = Nothing Set MyDb = Nothing End Sub[/code]Fri, 05 Apr 2013 13:42:44 GMTdavid.holleyhttp://www.sqlservercentral.com/Forums/Topic496042-203-1.aspxOk, thanks for the confirm - now I'll have my work cut out for me trying to build a solution with ADO slinging around recordsets between Access and SQL Server!Fri, 05 Apr 2013 12:58:20 GMTjjturnerhttp://www.sqlservercentral.com/Forums/Topic496042-203-1.aspx[quote][b]jjturner (4/5/2013)[/b][hr]Thanks David - that was quick! I'll have a go at translating this into VBA and see what happens.Although it does look like there's no way around firing a bunch of SELECT statements with a cursor (unless I'm misreading it)...Cheers,John[/quote]Its not a cursor. It was a solution that was presented as an alternative to a cursor. If you're using VBA, I'd guess that you'll have to create the sp on the SQL Server side and then execute the sp from VBA. Using the SELECT @name = @name + [NameLong]... is something that won't be available on the Access side.However, that being said you could create a recordset and then loop through each record to create the string, however the performance difference between that approach and the one I posted will be quite noticable.Fri, 05 Apr 2013 12:31:19 GMTdavid.holleyhttp://www.sqlservercentral.com/Forums/Topic496042-203-1.aspx<obligatory newbie double-post above>Fri, 05 Apr 2013 12:22:06 GMTjjturnerhttp://www.sqlservercentral.com/Forums/Topic496042-203-1.aspxThanks David - that was quick! I'll have a go at translating this into VBA and see what happens.Although it does look like there's no way around firing a bunch of SELECT statements with a cursor (unless I'm misreading it)...Cheers,JohnFri, 05 Apr 2013 12:21:15 GMTjjturnerhttp://www.sqlservercentral.com/Forums/Topic496042-203-1.aspxThanks David - that was quick! I'll have a go at translating this into VBA and see what happens.Although it does look like there's no way around firing a bunch of SELECT statements with a cursor (unless I'm misreading it)...Cheers,JohnFri, 05 Apr 2013 12:17:57 GMTjjturnerhttp://www.sqlservercentral.com/Forums/Topic496042-203-1.aspx[quote][b]jjturner (4/5/2013)[/b][hr]Tally Tables are the bomb, and confirm my own intuitions regarding the power of set-based operations. Many thanks, Jeff!Now I have another poser for you or anyone else - is there / would there be a way to somehow employ TT's to do the reverse? Meaning roll-up child records into a column of concatenated/delimited values for each parent record?Disclaimer: I have an Access2010 project I'm trying to sort out, so I don't have recourse to SQL Server CTE or FOR XML Path options...[/quote]I don't know if you can do that with a TallyTable or if a TT is neccessary. This is a function that should accomplish it. It simply adds the next selected value to the previous. And no, I didn't figure this out myself.[code]USE [Mercury]GO/****** Object: UserDefinedFunction [dbo].[getShowContactsForDepartment_AsString] Script Date: 04/05/2013 14:03:26 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO-- =============================================-- Author: <Author,,Name>-- Create date: <Create Date, ,>-- Description: <Description, ,>-- =============================================CREATE FUNCTION [dbo].[getShowContactsForDepartment_AsString] ( @ShowNumber varchar(8), @ProductionDepartmentId integer)RETURNS varchar(500)ASBEGIN DECLARE @names VARCHAR(500) SET @names = '' SELECT @names = @names + ', ' + NameInitials + Case WHEN ScheduleCommentShort Is Null THEN '' ELSE ' ' + ScheduleCommentShort End FROM vw_ShowContacts WHERE ShowNumber = @ShowNumber AND ProductionDepartmentId = @ProductionDepartmentId ORDER BY Id --Contingent if @names is a zero-length string IF Len(@names) > 0 BEGIN SET @names = Right(@names,LEN(@names)-1) End RETURN @namesENDGO[/code]Fri, 05 Apr 2013 12:08:25 GMTdavid.holleyhttp://www.sqlservercentral.com/Forums/Topic496042-203-1.aspxTally Tables are the bomb, and confirm my own intuitions regarding the power of set-based operations. Many thanks, Jeff!Now I have another poser for you or anyone else - is there / would there be a way to somehow employ TT's to do the reverse? Meaning roll-up child records into a column of concatenated/delimited values for each parent record?Disclaimer: I have an Access2010 project I'm trying to sort out, so I don't have recourse to SQL Server CTE or FOR XML Path options...Fri, 05 Apr 2013 12:00:23 GMTjjturnerhttp://www.sqlservercentral.com/Forums/Topic496042-203-1.aspxIt isn't staff scheduling/availablity oriented. Its more similar to the example of the various dates that I gave where overlap is entirely possible.Mon, 09 Jan 2012 11:37:23 GMTdavid.holleyhttp://www.sqlservercentral.com/Forums/Topic496042-203-1.aspx[quote][b]david.holley (1/7/2012)[/b][hr]Its for a scheduling module. Each date can have different start/end times. Since the majority of the start/end times will be the same, the idea is to speed entry by adding them in bulk and then allowing the user to go back and then tweek as neccessary. As opposed to the user having to enter each and every date. [/quote]Thanks for the feedback, David, but that's what I want to really know. Why does the scheduling module need to have the individual dates for each StartDate/EndDate pair? Calculating overlapping dates for scheduling purposes is pretty easy and a whole lot more effecient (although, admittedly, not as obvious), IMHO.Sun, 08 Jan 2012 00:00:21 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic496042-203-1.aspxIts for a scheduling module. Each date can have different start/end times. Since the majority of the start/end times will be the same, the idea is to speed entry by adding them in bulk and then allowing the user to go back and then tweek as neccessary. As opposed to the user having to enter each and every date. From a coolness factor, it would be a nice challenge to use a single record that represents the same start/end times for a range of dates which is then broken up as needed for the exceptions, but having to maintain the code would be hell if any changes needed to be made, not to mention the testing.For the record, the date and times are stored in separate columns using the DATE or TIME datatypes as appropriate since not every date will have an associated start/end time nor will every date that has a 'start' time have and end time. Examples:50% Deposit Due 5/1/2012Deposit Paid In Full 6/1/2012On Site Pre Con 7/6/2012 4:00 PMWelcome Reception 7/21/2012 6:00 PM - 8:00 PMSat, 07 Jan 2012 12:16:48 GMTdavid.holleyhttp://www.sqlservercentral.com/Forums/Topic496042-203-1.aspx[quote][b]david.holley (1/6/2012)[/b][hr]I just dropped it into production and it is looking very, very sweet. Not to mention that it saves me an immense amount of time. My specific need is the ability to create individual records for each date falling in between two given dates.[/quote]Very cool. Thank you for the feedback.I do have a question, though (I'm just curious). Why is it that you need to essentially duplicate rows to create a row for each date between the dates? I'm looking for the business or logical reason.Fri, 06 Jan 2012 19:03:52 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic496042-203-1.aspxI just dropped it into production and it is looking very, very sweet. Not to mention that it saves me an immense amount of time. My specific need is the ability to create individual records for each date falling in between two given dates. Now about building the ASP.NET front end...[code]CREATE TABLE [dbo].[sysNumbers]( [Value] [int] NOT NULL, CONSTRAINT [PK_sysNumbers] PRIMARY KEY CLUSTERED ( [Value] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]GOINSERT INTO sysNumbers(Value) SELECT '0'INSERT INTO sysNumbers(Value) SELECT '1'INSERT INTO sysNumbers(Value) SELECT '2'INSERT INTO sysNumbers(Value) SELECT '3'INSERT INTO sysNumbers(Value) SELECT '4'INSERT INTO sysNumbers(Value) SELECT '5'INSERT INTO sysNumbers(Value) SELECT '6'INSERT INTO sysNumbers(Value) SELECT '7'INSERT INTO sysNumbers(Value) SELECT '8'INSERT INTO sysNumbers(Value) SELECT '9'INSERT INTO sysNumbers(Value) SELECT '0'INSERT INTO sysNumbers(Value) SELECT '11'INSERT INTO sysNumbers(Value) SELECT '12'INSERT INTO sysNumbers(Value) SELECT '13'INSERT INTO sysNumbers(Value) SELECT '14'INSERT INTO sysNumbers(Value) SELECT '15'INSERT INTO sysNumbers(Value) SELECT '16'INSERT INTO sysNumbers(Value) SELECT '17'INSERT INTO sysNumbers(Value) SELECT '18'INSERT INTO sysNumbers(Value) SELECT '19'INSERT INTO sysNumbers(Value) SELECT '20'INSERT INTO sysNumbers(Value) SELECT '21'INSERT INTO sysNumbers(Value) SELECT '22'INSERT INTO sysNumbers(Value) SELECT '23'INSERT INTO sysNumbers(Value) SELECT '24'INSERT INTO sysNumbers(Value) SELECT '25'INSERT INTO sysNumbers(Value) SELECT '26'INSERT INTO sysNumbers(Value) SELECT '27'INSERT INTO sysNumbers(Value) SELECT '28'INSERT INTO sysNumbers(Value) SELECT '29'INSERT INTO sysNumbers(Value) SELECT '30'INSERT INTO sysNumbers(Value) SELECT '31'GO--Note that sysNumbers starts at 0 allowing the Start & End Dates to be used as-is without having to add 1 to get the correct number of days.Create FUNCTION [dbo].[NumbersTable](@StartDate date,@EndDate date)RETURNS TABLE AS RETURN ( Select Value, DATEADD("d", Value, @StartDate) As CalendarDate From sysNumbers Where Value <= DateDiff("d", @StartDate, @EndDate)) GOCREATE PROCEDURE [dbo].[sp_ShowSetup_ShowDate_InsertBatch]( @ShowNumber varchar(8), @DateCategoryId integer, @StartDate date, @EndDate date, @MilestoneTimeStart time, @MilestoneTimeEnd time, @ConfirmationStatus varchar(4), @Area varchar(30), @Comment varchar(30)) ASBEGININSERT INTO ShowDates ([ShowNumber],[DateCategoryId],[MilestoneDate],[ConfirmationStatus],[MilestoneTimeStart],[MilestoneTimeEnd],[EntryUserId],[EntryDateTime])SELECT @ShowNumber, @DateCategoryId, CalendarDate, @ConfirmationStatus, @MilestoneTimeStart, @MilestoneTimeEnd, SYSTEM_USER, SYSDATETIME() FROM NumbersTable (@StartDate, @EndDate) RETURN @@ErrorENDCREATE TABLE [dbo].[ShowDates]( [ShowNumber] [varchar](8) NOT NULL, [DateCategoryId] [smallint] NOT NULL, [MilestoneDate] [date] NULL, [ConfirmationStatus] [varchar](4) NOT NULL, [MilestoneTimeStart] [time](0) NULL, [MilestoneTimeEnd] [time](0) NULL, [EntryUserId] [nvarchar](128) NULL, [EntryDateTime] [datetime] NULL, [ModifiedUserId] [nvarchar](128) NULL, [ModifiedDateTime] [datetime] NULL, [Id] [int] IDENTITY(1000,1) NOT NULL, [Area] [varchar](30) NOT NULL, [Comment] [varchar](30) NOT NULL)GOexec sp_ShowSetup_ShowDate_InsertBatch '000001', '5', '2/14/2012', '2/18/2012', '8:00 AM', '4:30 PM', '', '', ''GO[/code]Fri, 06 Jan 2012 14:16:48 GMTdavid.holleyhttp://www.sqlservercentral.com/Forums/Topic496042-203-1.aspxThat would do it! Thanks for the feedback.Thu, 24 Nov 2011 09:43:07 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic496042-203-1.aspx[quote][b]Jeff Moden (11/19/2011)[/b][hr]Very cool feedback. Thanks, David.As a side bar, if you meant for both dates in your example to be inclusive, you'll need to add "1" to the DATEDIFF.[/quote]I circumvented that by starting the Tally Table with a '0'.Mon, 21 Nov 2011 06:15:44 GMTdavid.holleyhttp://www.sqlservercentral.com/Forums/Topic496042-203-1.aspxBest Idea ever Lynn :-DSat, 19 Nov 2011 13:21:59 GMTshannonjkhttp://www.sqlservercentral.com/Forums/Topic496042-203-1.aspxVery cool feedback. Thanks, David.As a side bar, if you meant for both dates in your example to be inclusive, you'll need to add "1" to the DATEDIFF.Sat, 19 Nov 2011 13:03:32 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic496042-203-1.aspx[quote][b]shannonjk (7/21/2011)[/b][hr]Eh no worries! Being a DBA rarely people actually see my face so I get it all the time with the companies I work with :-D. Ironically enough I just did the same thing with a new employee named Jamie who also happens to be male :-D. The age of non-physical presence communication has some flaws apparently![/quote]I know a Kim, Stacey, and a Shannon. I want to put together a male bowling team and call it "Girls Night Out" and the reaction of the other teams. Haven't done yet.Sat, 19 Nov 2011 12:56:37 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic496042-203-1.aspxI am *way* so liking this. After however many months, I'm actually to the point of needing to play with this and was quite surprised at just how easy it was to get four separate records one for each date between two dates. I used a function that returns a table for simplicity since it was more of a play around with it thing. The immediate application will be a stored procedure that inserts records into a table.The tally table starts at 0 and goes up to 100.[Code]CREATE TABLE [dbo].[TallyTable]( [Value] [int] NOT NULL, CONSTRAINT [PK_TallyTable] PRIMARY KEY CLUSTERED ( [Value] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]GO[/code][Code]Create FUNCTION [dbo].[TallyTableTest]( @StartDate date, @EndDate date)RETURNS TABLE ASRETURN ( Select Value, DATEADD("d", Value, @StartDate) As CalendarDate From TallyTable Where Value <= DateDiff("d", @StartDate, @EndDate))GO[/code][code]SELECT * FROM TallyTableTest ('11/22/2011', '11/26/2011')[/code]Fri, 18 Nov 2011 19:46:56 GMTdavid.holleyhttp://www.sqlservercentral.com/Forums/Topic496042-203-1.aspx[quote][b]Jeff Moden (7/21/2011)[/b][hrGood idea but the dual 11,000 row Tally Table has 30 years of "capability"... doing it directly as you have limits the functionality to 299.16666666667 days. :-) You could Cross-Join the Tally table to get more, but it actually takes 65 milliseconds per year longer.[/quote]Good spot Jeff :-)Could increase the tally table which, in my tests for 30 years, would make marginal difference in query time but 50% less cpu :crazy:One of those 'it depends' issues depending on the max date range :-DThu, 21 Jul 2011 16:12:33 GMTDavid Burrowshttp://www.sqlservercentral.com/Forums/Topic496042-203-1.aspxEh no worries! Being a DBA rarely people actually see my face so I get it all the time with the companies I work with :-D. Ironically enough I just did the same thing with a new employee named Jamie who also happens to be male :-D. The age of non-physical presence communication has some flaws apparently!Thu, 21 Jul 2011 14:40:33 GMTshannonjkhttp://www.sqlservercentral.com/Forums/Topic496042-203-1.aspxGot it! Lynn Pettis has a similar problem. Apologies for the typical American mistake on the name. :blush:Thu, 21 Jul 2011 14:31:51 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic496042-203-1.aspx[quote][b]Jeff Moden (7/21/2011)[/b][hr][quote][b]shannonjk (7/21/2011)[/b][hr]Jeff, Thanks for the tip! I have actually been focusing on that exact thing with indexes lately :). On a side note, I am actually a 'him' not a 'her' ;-)David, Great work simplifying that even more![/quote]Ah... Ok. Where does the "shannonjk" handle come from?[/quote]My first name, and the initials of my middle and last name...It's an Irish name :-DThu, 21 Jul 2011 13:27:33 GMTshannonjkhttp://www.sqlservercentral.com/Forums/Topic496042-203-1.aspx[quote][b]shannonjk (7/21/2011)[/b][hr]Jeff, Thanks for the tip! I have actually been focusing on that exact thing with indexes lately :). On a side note, I am actually a 'him' not a 'her' ;-)David, Great work simplifying that even more![/quote]Ah... Ok. Where does the "shannonjk" handle come from?Thu, 21 Jul 2011 13:24:19 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic496042-203-1.aspx[quote][b]David Burrows (7/21/2011)[/b][hr]How about a simpler approach[code="sql"]SELECT DATEADD(minute, (t.N - 1) * 30, @DateStart) FROM dbo.Tally t WHERE t.N BETWEEN 1 AND (DATEDIFF(day, @DateStart, @DateEnd) + 1) * 48 ORDER BY t.N [/code][/quote]Good idea but the dual 11,000 row Tally Table has 30 years of "capability"... doing it directly as you have limits the functionality to 299.16666666667 days. :-) You could Cross-Join the Tally table to get more, but it actually takes 65 milliseconds per year longer.Thu, 21 Jul 2011 13:23:31 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic496042-203-1.aspxJeff, Thanks for the tip! I have actually been focusing on that exact thing with indexes lately :). On a side note, I am actually a 'him' not a 'her' ;-)David, Great work simplifying that even more!Thu, 21 Jul 2011 11:17:57 GMTshannonjkhttp://www.sqlservercentral.com/Forums/Topic496042-203-1.aspxOh sure thing. Thanks to everyone for their solutions.KenThu, 21 Jul 2011 10:39:01 GMTken.trockhttp://www.sqlservercentral.com/Forums/Topic496042-203-1.aspxHow about a simpler approach[code="sql"]SELECT DATEADD(minute, (t.N - 1) * 30, @DateStart) FROM dbo.Tally t WHERE t.N BETWEEN 1 AND (DATEDIFF(day, @DateStart, @DateEnd) + 1) * 48 ORDER BY t.N [/code]Thu, 21 Jul 2011 02:09:23 GMTDavid Burrows