Friday, June 7, 2013

[T-SQL] Combine columns into One with Delimiter

[T-SQL] Combine columns into One with Delimiter

Combine columns into One with Delimiter

Posted: 06 Jun 2013 06:09 AM PDT

I have the following:[code="plain"]Create table Table_1 ([col1] varchar(10) null,[col2] varchar(10) null,)Insert Table_1(col1, col2)values ('Bill','Smith'),('Bill',null),(null,'Smith')select rtrim(isnull(col1+'/',''))+rtrim(isnull(col2+'','')) as firsttry from Table_1[/code]This returns:Bill/SmithBill/SmithI am trying to remove the trailing '/' if col2 is null from col1.Was playing around with Reverse but couldn't get it to work...Any ideas would be appreciated.Thanks

help getting return of function and loop into stored proc

Posted: 07 Jun 2013 12:35 AM PDT

Hi,I have a tricky issue I am struggly with on a mental level.In our db we have a table showing the UK Holidays for the next few years, and a stored function returns a recordset to my front end.I have a flag in my recordset called 'deletable' which allows the frontend to decide if a context menu can be shown in the data grid, thus allowing that record to be deleted.Currently the test (in my stored proc) just checks if the date column has a date from three days ago or more.[code="other"]case when DATEDIFF(d,a.[date],GETDATE()) > 3 then 1 else 0 end as [deletable][/code]how can I modify that to find the previous working date by checking weekends and the Holidays table 'Holiday' column (which is a Datetime) and see if the [date] column in my recordset row is 3 working days before, taking into account Holidays from the Holidays table and weekends?Is there an elegent way to do that?thanksPhilip

Update Performance

Posted: 06 Jun 2013 10:58 PM PDT

Hi Guys,Any Ideas what the best way would be to perform this update?UPDATE DSET Track_ID = P.Track_ID, Territory_ID = P.Territory_ID, Major = P.Major FROM Staging.Track_Mapping P INNER JOIN Staging.Track_Play D ON P.ISRC = D.ISRC_Code AND P.Event_Date = D.Event_DateBoth tables have around 63 million rows and have numerous indexes.Any ideas appreciatedThanks,Rich

APPLY versus ROW_NUMBER versus double JOIN to retrieve most recent record from a foreign key table

Posted: 06 Jun 2013 01:52 AM PDT

I've been mulling over this for a while, and there's something about using the APPLY function (OUTER APPLY in this case) that bugs me in that it seems to force the contents of the apply to be executed as many times as there are input rows. I'm sure I've heard people say this shouldn't be the case, so I've finally got round to getting all the sample code and such together to find out where I'm going wrong.Basically, I have two tables, ServicerHistory and ServicerHistoryCommissionRate. ServicerHistory contains details of an account and who is servicing it (I've only bothered including the ServicerHistoryID in the sample below as the rest isn't pertinent to this example), whilst ServicerHistoryCommissionRate contains the effective date of the commission rates (if there are any) applied to that account.DDL for the tables and indexes:-[code="sql"]--create objectsIF OBJECT_ID('dbo.ServicerHistoryCommissionRate') IS NOT NULL DROP TABLE dbo.ServicerHistoryCommissionRateIF OBJECT_ID('dbo.ServicerHistory') IS NOT NULL DROP TABLE dbo.ServicerHistoryCREATE TABLE dbo.ServicerHistory(ServicerHistoryID int IDENTITY(1,1) PRIMARY KEY)GOCREATE TABLE dbo.ServicerHistoryCommissionRate(ServicerHistoryCommissionRateID int IDENTITY(1,1) PRIMARY KEY, ServicerHistoryID int NOT NULL FOREIGN KEY REFERENCES dbo.ServicerHistory(ServicerHistoryID), CommissionRate decimal(5,2) NOT NULL, EffectiveDate date NOT NULL)GOCREATE INDEX idx_ServicerHistoryID_EffectiveDateON dbo.ServicerHistoryCommissionrate (ServicerHistoryID, EffectiveDate DESC)INCLUDE (CommissionRate)GO[/code]And some data population (adjust the executions as you feel fit; :-[code="sql"]--populate with some sample dataINSERT INTO dbo.ServicerHistoryDEFAULT VALUESGO 100000--set up some datesDECLARE @Dates TABLE(SomeDate date NOT NULL)INSERT INTO @Dates (SomeDate)SELECT '2011-07-12'UNION ALLSELECT '2013-03-02'UNION ALLSELECT '2010-08-13'UNION ALLSELECT '2011-01-02'UNION ALLSELECT '2013-05-03'UNION ALLSELECT '2009-12-18'--set up commission ratesDECLARE @CommRates TABLE(Commissionrate decimal(5,2))INSERT INTO @CommRates (Commissionrate)SELECT 32.35UNION ALLSELECT 25UNION ALLSELECT 12.8UNION ALLSELECT 10UNION ALLSELECT 7.4--populate some servicer history ids with valid dates and commission ratesINSERT INTO dbo.ServicerHistoryCommissionRateSELECT TOP 20000 sh.ServicerHistoryID, Commrate.CommissionRate, Dates.SomeDateFROM dbo.ServicerHistory shCROSS APPLY (SELECT TOP 1 CommissionRate FROM @CommRates AS cr ORDER BY NEWID()) CommrateCROSS APPLY (SELECT TOP 1 SomeDate FROM @Dates AS d ORDER BY NEWID()) DatesLEFT OUTER JOIN dbo.ServicerHistoryCommissionRate shcr ON sh.ServicerHistoryID = shcr.ServicerHistoryID AND dates.SomeDate = shcr.EffectiveDateWHERE shcr.ServicerHistoryCommissionRateID IS NULLORDER BY NEWID()GO 50[/code]The three methods of retrieving the data that I've come up with are below:-[code="sql"]--retrieve the most recent commission rate for each row using outer apply (there may not be a rate)--uh oh, the subquery executed onece for each row in ServicerHistory tableSELECT sh.ServicerHistoryId, shrc.CommissionRateFROM dbo.ServicerHistory AS shOUTER APPLY (SELECT TOP 1 SHRC.CommissionRate FROM dbo.ServicerHistoryCommissionRate AS SHRC WHERE SHRC.ServicerHistoryID = SH.ServicerHistoryId ORDER BY SHRC.EffectiveDate DESC ) SHRC --try using ROW_NUMBER, whoo each table only touched once SELECT sh.ServicerHistoryId, shrc.CommissionRateFROM dbo.ServicerHistory AS shLEFT OUTER JOIN (SELECT SHRC.CommissionRate , ServicerHistoryId , ROW_NUMBER() OVER (PARTITION BY ServicerHistoryID ORDER BY EffectiveDate DESC) RowNum FROM dbo.ServicerHistoryCommissionRate AS SHRC ) SHRC ON sh.ServicerHistoryId = shrc.ServicerHistoryID AND SHRC.Rownum = 1 --try old fashioned double join method, SErvicerHistoryCommissionRate table touched twice so more expensiveSELECT sh.ServicerHistoryID, shrc.CommissionRateFROM dbo.ServicerHistory AS shLEFT OUTER JOIN (SELECT commid.ServicerHistoryID , MAX(ServicerHistoryCommissionRateID) ServicerHistoryCommissionRateID FROM dbo.ServicerHistoryCommissionRate commid LEFT OUTER JOIN (SELECT ServicerHistoryID ,MAX(EffectiveDate) MaxDate FROM dbo.ServicerHistoryCommissionRate GROUP BY ServicerHistoryID) commdate ON commid.ServicerHistoryId = commdate.ServicerHistoryID GROUP BY commid.ServicerHistoryID) comm ON sh.ServicerHistoryId = comm.servicerHistoryIDLEFT OUTER JOIN dbo.ServicerHistoryCommissionRate shrc ON comm.ServicerHistoryCommissionRateID = shrc.ServicerHistoryCommissionRateID[/code]Statistics time and io return the following:-[code="plain"](100000 row(s) affected)Table 'ServicerHistoryCommissionRate'. Scan count 100000, logical reads 319323, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Table 'ServicerHistory'. Scan count 7, logical reads 484, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. SQL Server Execution Times: CPU time = 639 ms, elapsed time = 845 ms.(100000 row(s) affected)Table 'ServicerHistoryCommissionRate'. Scan count 1, logical reads 1852, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Table 'ServicerHistory'. Scan count 1, logical reads 163, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. SQL Server Execution Times: CPU time = 327 ms, elapsed time = 1040 ms.(100000 row(s) affected)Table 'ServicerHistory'. Scan count 7, logical reads 484, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Table 'ServicerHistoryCommissionRate'. Scan count 14, logical reads 4098, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. SQL Server Execution Times: CPU time = 767 ms, elapsed time = 1847 ms.[/code]The execution plans for the above are attached; as you can see, the first query using the APPLY method executes the index seek on the ServicerHistoryCommissionRate 100k times (for the actual data this comes from there are several million rows in each table so it just gets worse!), making this seem to be by far the most inefficient method of the three when returning a set.Am I missing something with the way in which to implement using APPLY when dealing with sets, or is it just not good for that kind of query? I like APPLY from a "readable code" point of view, but for performance it just seems to be a bit horrible unless you're working with a very small set of rows.Any tips, pointers or observations are most welcome!

update for My table?

Posted: 06 Jun 2013 06:56 PM PDT

Hai friends, my table structure iscreate table journey(journey_id int identity,fromplace varchar(44),toplace varchar(44),mode nvarchar(20),seattype nvarchar(20),status_id int)insert into journey '2013-05-10','chennai','Mumbai','Air','Business','2'how to write fro update for these table depends on journey_id? i m tried its not reflecting?

Forcing a row to be related to at least another row

Posted: 06 Jun 2013 08:50 AM PDT

HalloI need to implement this:A row in table A must be in relationship with [b]at least one[/b] row in table B.Could this be achieved by constraints only?Thanks in advance

truncate error - foreign key and check constraints

Posted: 06 Jun 2013 04:25 PM PDT

Hi.could not truncate table due to FK constraintsTable defination[code="sql"]/****** Object: Table [dbo].[DiagHistory] Script Date: 06/07/2013 10:49:16 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TABLE [dbo].[DiagHistory]( [DiagHistoryKey] [uniqueidentifier] ROWGUIDCOL NOT NULL, [CreatedDateTime] [datetime] NOT NULL, [TypeID] [uniqueidentifier] NOT NULL, [DescriptionID] [uniqueidentifier] NOT NULL, [Description] [ntext] NOT NULL, [DiagSourcesKey] [uniqueidentifier] NOT NULL, [DiagDomainKey] [uniqueidentifier] NOT NULL, CONSTRAINT [PK_DiagHistory] PRIMARY KEY CLUSTERED ( [DiagHistoryKey] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]GOALTER TABLE [dbo].[DiagHistory] WITH CHECK ADD CONSTRAINT [FK_DiagHistory_DiagDescriptionIDs] FOREIGN KEY([DescriptionID])REFERENCES [dbo].[DiagDescriptionIDs] ([DescriptionID])ON DELETE CASCADEGOALTER TABLE [dbo].[DiagHistory] CHECK CONSTRAINT [FK_DiagHistory_DiagDescriptionIDs]GOALTER TABLE [dbo].[DiagHistory] WITH CHECK ADD CONSTRAINT [FK_DiagHistory_DiagDomain] FOREIGN KEY([DiagDomainKey])REFERENCES [dbo].[DiagDomain] ([DiagDomainKey])ON DELETE CASCADEGOALTER TABLE [dbo].[DiagHistory] CHECK CONSTRAINT [FK_DiagHistory_DiagDomain]GOALTER TABLE [dbo].[DiagHistory] WITH CHECK ADD CONSTRAINT [FK_DiagHistory_DiagSources] FOREIGN KEY([DiagSourcesKey])REFERENCES [dbo].[DiagSources] ([DiagSourcesKey])ON DELETE CASCADEGOALTER TABLE [dbo].[DiagHistory] CHECK CONSTRAINT [FK_DiagHistory_DiagSources]GOALTER TABLE [dbo].[DiagHistory] WITH CHECK ADD CONSTRAINT [FK_DiagHistory_DiagTypeIDs] FOREIGN KEY([TypeID])REFERENCES [dbo].[DiagTypeIDs] ([TypeID])ON DELETE CASCADEGOALTER TABLE [dbo].[DiagHistory] CHECK CONSTRAINT [FK_DiagHistory_DiagTypeIDs]GOALTER TABLE [dbo].[DiagHistory] ADD CONSTRAINT [DF_DiagHistory_DiagHistoryKey] DEFAULT (newid()) FOR [DiagHistoryKey]GO[/code]I have tried as below script but its not workingalter table [dbo].[DiagHistory] nocheck constraint DF_DiagHistory_DiagHistoryKeygotruncate table diaghistorygoalter table [dbo].[DiagHistory] check constraint DF_DiagHistory_DiagHistoryKeygocould you suggestion me for single batch script to truncate data?Error[code="sql"]Msg 11415, Level 16, State 1, Line 1Object 'DF_DiagHistory_DiagHistoryKey' cannot be disabled or enabled. This action applies only to foreign key and check constraints.Msg 4916, Level 16, State 0, Line 1Could not enable or disable the constraint. See previous errors.Msg 4712, Level 16, State 1, Line 1Cannot truncate table 'diaghistory' because it is being referenced by a FOREIGN KEY constraint.Msg 11415, Level 16, State 1, Line 1Object 'DF_DiagHistory_DiagHistoryKey' cannot be disabled or enabled. This action applies only to foreign key and check constraints.Msg 4916, Level 16, State 0, Line 1Could not enable or disable the constraint. See previous errors.[/code]

Running a DELETE Stored Proc but finding degradation in Performance in WHILE loop iterations increase

Posted: 06 Jun 2013 09:24 AM PDT

1st time/long time,Quick question in general. I have a table i'm trying to purge a table. i'm deleting using a WHILE loop with WAIT time of 50ms between batches and 2000 records per batch. The real issue is that as time progresses the number of records deleted drops over time. refer to the following:Minute Number|Number of Records Deleted:1|162,0002|116,0003|80,0004|72,0005|62,0006|38,0007|38,0008|34,0009|20,000Wondering if there is something fundamental about using WHILE loop batches for deleting records that causes the performance to degrade with iteration of the loop. we have been manually watching the performance and then stopping the proc as the performance begins to drop drastically around the 5th to 6th minute and then restarting the deployment again. We don't think its a locking issue directly b/c as we play with the batch size the performance is always dropping at around the 5 to 6 minute mark.thanks in advanceDennis

Creating hierarchical metadata based on DMVs or system tables

Posted: 06 Jun 2013 11:06 AM PDT

I have a requirement to archive & purge my OLTP data. The database is a tightly defined relational database with Primary Keys/Foreign Keys very thoroughly defined. For this process, I am planning to build hierarchical meta data on fly (before archiving and purging) so that I can go top to bottom while archiving and bottom to top while purging.For example, below are my sample table details...[img]http://www.sqlservercentral.com/Forums/Attachment13796.aspx[/img]I want to create hierarchical metadata for my transactional tables. I envision the following...[img]http://www.sqlservercentral.com/Forums/Attachment13795.aspx[/img]With the ranking I have in the "Level" column of the output, I am guessing I can start to copy data (for archiving) from Level going all the way to Level 2. Likewise, for purging I can start purging data from the bottom most level and go all the way to the top. In an environment like where all the relations are very well defined, I feel that it will work efficiently. For instance, if new tables are created at a later data (assuming their relations are defined as expected), I don't have to make modifications to my archive/purge scripts since the script will build the metadata at the beginning and use for the process.My first question is, how to build a query uisng DMVs and/or system tables to give me this output? And secondly, is there a better way to archive and purge data where I don't have to change my script everytime new tables are built.Following is the script to build the tables I cited above.[code="sql"]CREATE TABLE dbo.Customers ( CustomerID INT NOT NULL, CustomerName VARCHAR (100) NOT NULL, CustomerStreet VARCHAR (200) NOT NULL, CustomerCity VARCHAR (100) NOT NULL, CustomerState VARCHAR (50) NOT NULL, CustomerZip VARCHAR (10) NOT NULL, CustomerEmail VARCHAR (100) NOT NULL, CustomerPhone VARCHAR (20) NOT NULL, CustomerWebSite VARCHAR (100) NULL ); ALTER TABLE dbo.Customers ADD CONSTRAINT pkCustomerID PRIMARY KEY CLUSTERED (CustomerID);CREATE TABLE dbo.Orders ( OrderID INT NOT NULL, OrderCreatedDate DATETIME2 NOT NULL, OrderCreatedBy VARCHAR (50) NOT NULL, OrderLastModified DATETIME2 NOT NULL, CustomerID INT NOT NULL ); ALTER TABLE dbo.Orders ADD CONSTRAINT pkOrderID PRIMARY KEY CLUSTERED (OrderID); ALTER TABLE dbo.Orders ADD CONSTRAINT fkOrdersCustomer FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID);CREATE TABLE dbo.OrderItems ( OrderItemID INT NOT NULL, ItemID INT NOT NULL, ItemOrderQuantity INT NOT NULL, OrderID INT NOT NULL ); ALTER TABLE dbo.OrderItems ADD CONSTRAINT pkOrderItemID PRIMARY KEY CLUSTERED (OrderItemID); ALTER TABLE dbo.OrderItems ADD CONSTRAINT fkOrdersItemOrder FOREIGN KEY (OrderID) REFERENCES Orders(OrderID); CREATE TABLE dbo.OrderItemShipment ( OrderItemShipmentID INT NOT NULL, ShipmentType INT NOT NULL, OrderItemID INT NOT NULL ); ALTER TABLE dbo.OrderItemShipment ADD CONSTRAINT pkOrderItemShipmentID PRIMARY KEY CLUSTERED (OrderItemShipmentID); ALTER TABLE dbo.OrderItemShipment ADD CONSTRAINT fkOrderItemsOrderItemsShipment FOREIGN KEY (OrderItemID) REFERENCES OrderItems (OrderItemID);CREATE TABLE dbo.OrderItemException ( OrderItemExceptionID INT NOT NULL, ExceptionType INT NOT NULL, OrderItemID INT NOT NULL ); ALTER TABLE dbo.OrderItemException ADD CONSTRAINT pkOrderItemExceptionID PRIMARY KEY CLUSTERED (OrderItemExceptionID); ALTER TABLE dbo.OrderItemException ADD CONSTRAINT fkOrderItemsOrderItemException FOREIGN KEY (OrderItemID) REFERENCES OrderItems (OrderItemID);[/code]- Rex

Missing sequence where gap is 1

Posted: 06 Jun 2013 06:51 AM PDT

create table #MyTable (RecID INT IDENTITY(1,1),trxnNum int,Site varchar(10),Company VARCHAR(100))INSERT INTO #MyTable (trxnNum,Site,Company)SELECT 14418,'MySite','MyCompany'UNION ALLSELECT 14420,'MySite','MyCompany'UNION ALLSELECT 14431,'MySite','MyCompany'UNION ALLSELECT 14432,'MySite','MyCompany'UNION ALLSELECT 14436,'MySite','MyCompany'select * from #MyTableDROP TABLE #MyTableI have to return rows where TrxnNum = 14418 and 14420 because the gap is only 1 . If the gap is > than 1 example 14432 and 14436, I dont want to return those. If there is no gap as well 14431 and 14432 I dont want to return as well.In short where gap is = 1.Thanks,
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog