[T-SQL] Table Value Function VS ORs

---

• Table Value Function VS ORs
• Conditional join update
• 6 places after decimal without rounding
• Does a SP get precompiled?
• Database Mail + sysmail_start_sp
• How to report period wise data ?
• Help with execution Plan
• date problem
• how to write a column expression in a query based on other table value exist
• TabLock and TablLockX Confusion
• Advise on working with XML
• need to return the last value
• JOIN problems
• Index filled after created
• Number of days between two integer date columns
• Working on MERGE statement to Handle SCD

Table Value Function VS ORs Posted: 04 Mar 2013 09:22 PM PST Hi,I'm executing the following queries:[code="sql"]UPDATE AcumuladosContas SET MES12DB = MES12DB + 5000.00 WHERE TipoLancamento = '000' AND Ano=2012 AND Moeda = 'EUR' AND (Conta = '111' OR Conta = '11')UPDATE AcumuladosContas SET MES12DB = MES12DB + 5000.00 FROM (SELECT SubConta FROM dbo.GetSubContasTVF('111')) t WHERE TipoLancamento = '000' AND Ano = 2012 AND Moeda = 'EUR' AND Conta = t.SubContaUPDATE AcumuladosContas SET MES12DB = MES12DB + 5000.00 WHERE TipoLancamento = '000' AND Ano = 2012 AND Moeda = 'EUR' AND Conta IN (SELECT SubConta FROM dbo.GetSubContasTVF('111'))[/code]The TVF is:[code="sql"]ALTER FUNCTION [dbo].[GetSubContasTVF](@Conta NVARCHAR(20))RETURNS @SubContas TABLE (SubConta NVARCHAR(20) NOT NULL PRIMARY KEY)ASBEGIN INSERT INTO @SubContas SELECT SUBSTRING(@Conta, ID, LEN(@Conta)) SubConta FROM PriTally WHERE ID BETWEEN 1 AND LEN(@Conta) - 1 RETURN;END[/code]In SSMS the execution plan for the queries states that the OR takes 30% and the other two 35% each.In SQL Sentry Plan Explorer the OR takes 22%, the 2nd 52% and the 3rd 26%...When using SQLStress the OR is faster with only one thread but when using multiple threads the other ones are faster...What's the best query?Is there any other query to perform the operation?Thanks,Pedro

Conditional join update Posted: 04 Mar 2013 11:57 PM PST Is there a better way of doing this?IF object_id('tempdb..#Temp1') IS NOT NULLBEGIN DROP TABLE #Temp1ENDCREATE TABLE #temp1(Source VARCHAR(5),MPANcore BIGINT,EffectiveFromDate DATETIME,DatafileID bigint,DataSiteConfigATableName VARCHAR(60))Insert Into #temp1 Select Distinct Source, MPANCore,EffectiveFromSettlementDate, DataFileID, 'DataSiteConfigAMPANConfig' From DataSiteConfigAMPANConfigInsert Into #temp1 Select Distinct Source, MPANCore,EffectiveFromDate, DataFileID, 'DataSiteConfigADataAggregator' From DataSiteConfigADataAggregatorInsert Into #temp1 Select Distinct Source, MPANCore,EffectiveFromDate, DataFileID, 'DataSiteConfigADataCollector' From DataSiteConfigADataCollectorInsert Into #temp1 Select Distinct Source, MPANCore,EffectiveFromDate, DataFileID, 'DataSiteConfigAEnergisationStatus' From DataSiteConfigAEnergisationStatusInsert Into #temp1 Select Distinct Source, MPANCore,EffectiveFromDate, DataFileID, 'DataSiteConfigAGSPGroup' From DataSiteConfigAGSPGroupInsert Into #temp1 Select Distinct Source, MPANCore,EffectiveFromDate, DataFileID, 'DataSiteConfigALineLossfactorClass' From DataSiteConfigALineLossfactorClassInsert Into #temp1 Select Distinct Source, MPANCore,EffectiveFromDate, DataFileID, 'DataSiteConfigAMeasurementClass' From DataSiteConfigAMeasurementClassInsert Into #temp1 Select Distinct Source, MPANCore,EffectiveFromsettlementDate, DataFileID, 'DataSiteConfigAMeteringAddress' From DataSiteConfigAMeteringAddressInsert Into #temp1 Select Distinct Source, MPANCore,EffectiveFromDate, DataFileID, 'DataSiteConfigAMeterOperator' From DataSiteConfigAMeterOperatorInsert Into #temp1 Select Distinct Source, MPANCore,EffectiveFromDate, DataFileID, 'DataSiteConfigAMeterTimeSwitchCode' From DataSiteConfigAMeterTimeSwitchCodeInsert Into #temp1 Select Distinct Source, MPANCore,EffectiveFromDate, DataFileID, 'DataSiteConfigAProfileClass' From DataSiteConfigAProfileClassInsert Into #temp1 Select Distinct Source, MPANCore,EffectiveFromDate, DataFileID, 'DataSiteConfigAStandardSettlementConfiguration' From DataSiteConfigAStandardSettlementConfiguration-- 19 seconds/*Need to use DataSettlementBData At this point as well*/CREATE TABLE [dbo].[DataSiteConfigBMPANConfiguration]( [DataSiteConfigBMPANConfigurationID] [bigint] IDENTITY(1,1) NOT NULL, [Source] [varchar](5) NULL, [MPANCore] [bigint] NULL, [DataSiteMPANID] [bigint] NULL, [CreationTime] [datetime] NULL, [EffectiveFromSettlementDate] [datetime] NULL, [EffectiveToSettlementDate] [datetime] NULL, [EnergisationStatus] [varchar](2) NULL, [MeasurementClassRef] [varchar](2) NULL, [ProfileClass] [varchar](2) NULL, [StandardSettlementConfigurationRef] [int] NULL, ) INSERT INTO dbo.DatasiteConfigBMPANConfiguration(MpanCore, EffectiveFromSettlementDate)SELECT DISTINCT MPANCore, EffectiveFromDateFROM #temp1 WHERE EffectiveFromDate IS NOT NULL AND MPANcore IS NOT nullORDER BY MPANCore, EffectiveFromDate ASC------Create Unique set of dataIF object_id('tempdb..#Temp2') IS NOT NULLBEGIN DROP TABLE #Temp2ENDSelect MPANCore, EffectiveFromDate, DataSiteConfigATableName, Max(DataFileID) as maxfileIDInto #temp2 from #temp1Group by MPANCore, EffectiveFromDate, DataSiteConfigATableName----UPDATE dbo.DatasiteConfigBMPANConfigurationSET EnergisationStatus = b.EnergisationStatusFROM dbo.DatasiteConfigBMPANConfigurationinner JOIN DataSiteConfigAEnergisationStatus b ON b.MPANCore = DatasiteConfigBMPANConfiguration.MPANCore AND b.EffectiveFromDate = DatasiteConfigBMPANConfiguration.EffectiveFromSettlementDateInner join #Temp2 c on b.MPANCore = c.MPANcore and B.DataFileID = c.maxFileIDWhere c.DataSiteConfigATableName = 'DataSiteConfigAEnergisationStatus'----UPDATE dbo.DatasiteConfigBMPANConfigurationSET MeasurementClassRef= b.measurementClassRefFROM dbo.DatasiteConfigBMPANConfigurationinner JOIN DataSiteConfigAMeasurementClass b ON b.MPANCore = DatasiteConfigBMPANConfiguration.MPANCoreAND b.EffectiveFromDate = DatasiteConfigBMPANConfiguration.EffectiveFromSettlementDateInner join #Temp2 c on b.MPANCore = c.MPANcore and B.DataFileID = c.maxFileIDWhere c.DataSiteConfigATableName = 'DataSiteConfigAMeasurementClass'UPDATE dbo.DatasiteConfigBMPANConfigurationSET ProfileClass = b.ProfileClassFROM dbo.DatasiteConfigBMPANConfigurationINNER JOIN dbo.DataSiteConfigAProfileClass b ON b.MPANCore = DatasiteConfigBMPANConfiguration.MPANCoreAND b.EffectiveFromDate = DatasiteConfigBMPANConfiguration.EffectiveFromSettlementDateInner join #Temp2 c on b.MPANCore = c.MPANcore and B.DataFileID = c.maxFileIDWhere c.DataSiteConfigATableName = 'DataSiteConfigAProfileClass'any help is really appreciated.Thanks

6 places after decimal without rounding Posted: 04 Mar 2013 09:58 AM PST I have a sample tableCREATE TABLE dbo.Analysis(ID INT IDENTITY,Margin [numeric](21, 6) NULL,Gallons INT,Freight [numeric](21, 6) NULL,AccMargin [numeric](21, 6) NULL)INSERT INTO dbo.Analysis ( Margin,Gallons,Freight)SELECT 0.050220,5022,-30.180000INSERT INTO dbo.Analysis ( Margin,Gallons,Freight)SELECT 0.050220,-5022,318.260000UPDATE dbo.AnalysisSET AccMargin = (MArgin/Gallons + Freight/Gallons) I want the AccMargin to be -0.005999 and -0.063383 without rounding . Currently it is rounding up to -0.006000and -0.063383 for -0.005999 . I want 6 places after the decimal without rounding.select *,(MArgin/Gallons + Freight/Gallons) As CorrectAccMargin from dbo.Analysis

Does a SP get precompiled? Posted: 04 Mar 2013 07:09 PM PST Hi,I have a large Stored Procedure. One tiny part of it is dynamic: SET @qry = 'SELECT something'; EXEC(@qry) . Is the result of this that the SP will be compiled every time it is called? And if so, would it be better to move the dynamic part to a new, small SP, and call that one from the large SP? Or wouldn't that give any performance improvement?Thanks,Raymond

Database Mail + sysmail_start_sp Posted: 04 Mar 2013 09:53 PM PST Hi,My Database Mail works fine, but every time i open SSMS, in order to send mail. I have to execute sysmail_start_sp to shoot the mail.What should i do so that it will never stops, as i am suppose to put this Email in a JOB.

How to report period wise data ? Posted: 04 Mar 2013 09:16 PM PST Hi ,I have a table which holds the employee details : /*******************************************************************//****** Object: Table [dbo].[empleaves] Script Date: 05-03-2013 17:42:59 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TABLE [dbo].[empleaves]( [empid] [int] NULL, [leavedate] [date] NULL) ON [PRIMARY]GOINSERT [dbo].[empleaves] ([empid], [leavedate]) VALUES (1, CAST(0xCE360B00 AS Date))GOINSERT [dbo].[empleaves] ([empid], [leavedate]) VALUES (1, CAST(0xD0360B00 AS Date))GOINSERT [dbo].[empleaves] ([empid], [leavedate]) VALUES (2, CAST(0xD0360B00 AS Date))GOINSERT [dbo].[empleaves] ([empid], [leavedate]) VALUES (2, CAST(0xBA360B00 AS Date))GOINSERT [dbo].[empleaves] ([empid], [leavedate]) VALUES (2, CAST(0xBB360B00 AS Date))GOINSERT [dbo].[empleaves] ([empid], [leavedate]) VALUES (2, CAST(0xBC360B00 AS Date))GOINSERT [dbo].[empleaves] ([empid], [leavedate]) VALUES (3, CAST(0xC9360B00 AS Date))GO/*******************************************************************/I need a query which will report the leaves of employees as :empid StartingDate EndingDate NoOfDays1 28-02-2013 28-02-2013 11 02-03-2013 02-03-2013 12 02-03-2013 02-03-2013 12 08-02-2013 10-02-2013 33 23-02-2013 23-02-2013 1How can I achieve this .?

Help with execution Plan Posted: 04 Mar 2013 02:06 PM PST Hi all,I am trying to learn Execution Plan reading. I executed the below query[code="sql"]USE [AdventureWork2008] GOSELECT *FROM Sales.SalesOrderHeader H INNER JOINSales.SalesOrderDetail D ON H.SalesOrderID=D.SalesOrderID[/code]The above script is a normal Select script where as its execution plan shows that the query is performing Compute Scalar operation , i am not able to understand what is the need that a simple select query needs to Compute scalar operation.

date problem Posted: 04 Mar 2013 01:45 AM PST Hi not sure if this is the correct forum but I need some help with dates in SQL.I have been given a database that has the dates stored in 3 different parts (week of year, year and day number) and I need to change it back to a proper datefor example if today is Monday 04/03/2013 then the data will be stored as week no = 10 , year = 2013 , daynumber = 2but I need to change it back to Monday 04/03/2013please helpthanksJames

how to write a column expression in a query based on other table value exist Posted: 04 Mar 2013 11:23 AM PST Hello Please help me here.,i have below tables calledstudent (sno, sname, address)StudentDrillEnrolled (sno, DrillClass)StudentDrillNotEnrolled (Sno, Reason, Viever)how to write a query, so that i will have sno,sname,address, Enrolled (Y/N/NA)enrolled will be calculatedif sno exist in StudentDrillEnrolled table then Yif sno exist in StudentDrillNotEnrolled table then it is Nif not exist in both tables enrolled & not enrolled then N/Aplease help me hereThanks in advanceasitti

TabLock and TablLockX Confusion Posted: 04 Mar 2013 12:54 PM PST I am trying to figure out how this works. Locking difference between TabLock and TablLockX. They seem to do the same thing. I did my queries on the tally table for simplicity (1 mill +)[code="sql"]BEGIN BEGIN TRANSACTION -- UPDATE Common.Tally WITH (TABLOCK) --SET N = 4 --WHERE N = 4 SELECT N FROM Common.Tally WITH (TABLOCK) WHERE N = 4 WAITFOR DELAY '00:15' COMMIT TRANSACTIONEND[/code]You can use the update or the Select it has the same effect. In another SSMS window I now run a Select[code="sql"]Select * FROM Common.TallyWHERE N=100[/code]When I execute SP_Lock I get Tally X (exclusive Lock)Tally IS (Intent to share)I thought LockX locked it down to where it could not be multi-user. I would like to Update/Insert/Delete and as long as it is not the same records. People should be able to ping the table with selects. Can someone help with what I am missing?

Advise on working with XML Posted: 04 Mar 2013 07:43 AM PST *advice I have a big nasty query I have been assigned to look at that basically is pulling varchar data into a temp xml table as such the query performance is terrible at best. [code="sql"] --DECLARE @XmlFilter xml DECLARE @WhereClause VARCHAR(MAX) DECLARE @CartPrice VARCHAR(2000) DECLARE @ExpandedCartPrice VARCHAR(2000) DECLARE @Operator VARCHAR(10) DECLARE @VariableID VARCHAR(10) DECLARE @Variance VARCHAR(10) DECLARE @Unit VARCHAR(10) DECLARE @SqlVar VARCHAR(50) = 'ProdDesc.Map' DECLARE @ElementCount INT DECLARE @StateParm VARCHAR(2000) DECLARE @UseCaEvent bit = 0 CREATE TABLE #MyParameters (TableAlias VARCHAR(200) , Parm VARCHAR(200), ParmValue VARCHAR(MAX) , ParmFilter VARCHAR(MAX), ParmWhere VARCHAR(MAX)) DECLARE @PriceElements AS TABLE (ID INT IDENTITY(1,1) ,VALUE VARCHAR(50)) --******* Build Dynamic WHERE Clause ******* BEGIN --******* Shread The XML ******* BEGIN --XML Root Attributes SELECT ParamValues.Parm.value('@Type[1]', 'VARCHAR(200)') QueryType , ParamValues.Parm.value('@UserID[1]', 'VARCHAR(200)') UserID INTO #FilterAttributes FROM @XmlFilter.nodes('/Filter') AS ParamValues(Parm) --Collect Query Parameters /* NOTES: Rating is not Used right now */ INSERT #MyParameters (TableAlias,Parm, ParmValue, ParmFilter ) --ProductSeller Parameters SELECT 'ProdEx','ISBN', ParamValues.Parm.value('ISBN[1]', 'VARCHAR(Max)'), 'ProdEx.ISBN = ''[VALUE]''' FROM @XmlFilter.nodes('/Filter/ProductSeller') AS ParamValues(Parm) UNION SELECT 'ProdEx','MfgSKU', ParamValues.Parm.value('MfgSKU[1]', 'VARCHAR(Max)'), 'ProdEx.MfgSKU = ''[VALUE]''' FROM @XmlFilter.nodes('/Filter/ProductSeller') AS ParamValues(Parm) UNION SELECT 'ProdEx','UPC', ParamValues.Parm.value('UPC[1]', 'VARCHAR(Max)'), 'ProdEx.UPC = ''[VALUE]''' FROM @XmlFilter.nodes('/Filter/ProductSeller') AS ParamValues(Parm) UNION SELECT 'ProdEx','VendorID', ParamValues.Parm.value('VendorID[1]', 'VARCHAR(Max)'), 'ProdEx.VendorID = [VALUE]' FROM @XmlFilter.nodes('/Filter/ProductSeller') AS ParamValues(Parm) UNION SELECT 'ProdEx','ProductID', ParamValues.Parm.value('ProductID[1]', 'VARCHAR(Max)'), 'ProdEx.ProductID in ([VALUE])' FROM @XmlFilter.nodes('/Filter/ProductSeller') AS ParamValues(Parm) UNION SELECT 'ProdEx','CVSKU', ParamValues.Parm.value('CVSKU[1]', 'VARCHAR(Max)'), 'ProdEx.CVSKU like ''%[VALUE]%''' FROM @XmlFilter.nodes('/Filter/ProductSeller') AS ParamValues(Parm) UNION SELECT 'ProdDesc','ProductName',ParamValues.Parm.value('ProductName[1]', 'VARCHAR(Max)'), 'ProdDesc.ProductName like ''%[VALUE]%''' FROM @XmlFilter.nodes('/Filter/ProductSeller') AS ParamValues(Parm) UNION SELECT 'comp','CompetitorName',ParamValues.Parm.value('CompetitorName[1]', 'VARCHAR(Max)'), 'comp.CompetitorName like ''%[VALUE]%''' FROM @XmlFilter.nodes('/Filter/Seller') AS ParamValues(Parm) UNION SELECT 'comp','CompetitorID', ParamValues.Parm.value('CompetitorID[1]', 'VARCHAR(Max)'), 'comp.CompetitorID in ([VALUE])' FROM @XmlFilter.nodes('/Filter/Seller') AS ParamValues(Parm) UNION SELECT 'compMar','SellerState', ParamValues.Parm.value('SellerState[1]', 'VARCHAR(Max)'), 'compMar.SellerState in ([VALUE])' FROM @XmlFilter.nodes('/Filter/Seller') AS ParamValues(Parm) UNION SELECT 'compMar','Rating', ParamValues.Parm.value('Rating[1]', 'VARCHAR(Max)'), 'compMar.Rating >= [VALUE]' FROM @XmlFilter.nodes('/Filter/Seller') AS ParamValues(Parm) UNION SELECT 'compMar','MarketID', ParamValues.Parm.value('MarketID[1]', 'VARCHAR(Max)'), 'compMar.MarketID in ([VALUE])' FROM @XmlFilter.nodes('/Filter/Seller') AS ParamValues(Parm) UNION --dg: commenting this out, it does not seem to be used and if it comes through it will break SELECT 'compListing','Reviews', ParamValues.Parm.value('Reviews[1]', 'VARCHAR(Max)'), 'compListing.ReviewCnt >= [VALUE]' FROM @XmlFilter.nodes('/Filter/Seller') AS ParamValues(Parm) UNION SELECT 'compListing','SuspectStatus', ParamValues.Parm.value('SuspectStatus[1]', 'VARCHAR(Max)'), 'compListing.SuspectStatusID = [VALUE]' FROM @XmlFilter.nodes('/Filter/Seller') AS ParamValues(Parm) UNION SELECT 'compMP','IgnoreInCart', ParamValues.Parm.value('IgnoreInCart[1]', 'VARCHAR(Max)'), 'compMP.InCart = 0' FROM @XmlFilter.nodes('/Filter/Seller') AS ParamValues(Parm) UNION SELECT 'CompMp','CartPrice', ParamValues.Parm.value('CartPrice[1]', 'VARCHAR(Max)'), '[VALUE] and compMP.Price > 0' FROM @XmlFilter.nodes('/Filter/Seller') AS ParamValues(Parm) UNION SELECT 'CompMp','Condition', ParamValues.Parm.value('Condition[1]', 'VARCHAR(Max)'), 'CompMp.ConditionId IN ([VALUE])' FROM @XmlFilter.nodes('/Filter/ProductSeller') AS ParamValues(Parm) UNION SELECT 'CompMp','PriceRankUpper',ParamValues.Parm.value('PriceRankUpper[1]', 'VARCHAR(Max)'), 'compMP.PricePosition <= [VALUE]' FROM @XmlFilter.nodes('/Filter/Seller') AS ParamValues(Parm) UNION SELECT 'CompMp','PriceRankLower',ParamValues.Parm.value('PriceRankLower[1]', 'VARCHAR(Max)'), 'compMP.PricePosition >= [VALUE]' FROM @XmlFilter.nodes('/Filter/Seller') AS ParamValues(Parm) UNION SELECT 'CompMp','SellerDT', ParamValues.Parm.value('SellerDT[1]', 'VARCHAR(Max)'), 'compMP.DT between ''[VALUE]''' FROM @XmlFilter.nodes('/Filter/Seller') AS ParamValues(Parm) UNION SELECT 'CompMp','PriceLocationID',ParamValues.Parm.value('PriceLocationID[1]', 'VARCHAR(Max)'),'compMP.PriceLocationID in ([VALUE])' FROM @XmlFilter.nodes('/Filter/Seller') AS ParamValues(Parm) UNION SELECT 'CompMp','PriceAttributeID',ParamValues.Parm.value('PriceAttributeID[1]', 'VARCHAR(Max)'),'compMP.PriceAttributeID in ([VALUE])' FROM @XmlFilter.nodes('/Filter/Seller') AS ParamValues(Parm) UNION SELECT 'CompMp', 'Active', ParamValues.Parm.value('Active[1]', 'VARCHAR(Max)'), 'compMP.LatestData = 1' FROM @XmlFilter.nodes('/Filter/Seller') AS ParamValues(Parm) [/code]Here is my query plan for the above statement. any suggestions would be much appreciated![code="xml"]<?xml version="1.0" encoding="utf-16"?><ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.1.1" Build="10.0.5500.0"> <BatchSequence> <Batch> <Statements> <StmtSimple StatementText="INSERT #MyParameters (TableAlias,Parm, ParmValue, ParmFilter ) --ProductSeller Parameters SELECT 'ProdEx','ISBN', ParamValues.Parm.value('ISBN[1]', 'VARCHAR(Max)'), 'ProdEx.ISBN = ''[VALUE]''' FROM @XmlFilter.nodes('/Filter/ProductSeller') AS ParamValues(Parm) UNION SELECT 'ProdEx','MfgSKU', ParamValues.Parm.value('MfgSKU[1]', 'VARCHAR(Max)'), 'ProdEx.MfgSKU = ''[VALUE]''' FROM @XmlFilter.nodes('/Filter/ProductSeller') AS ParamValues(Parm) UNION SELECT 'ProdEx','UPC', ParamValues.Parm.value('UPC[1]', 'VARCHAR(Max)'), 'ProdEx.UPC = ''[VALUE]''' FROM @XmlFilter.nodes('/Filter/ProductSeller') AS ParamValues(Parm) UNION SELECT 'ProdEx','VendorID', ParamValues.Parm.value('VendorID[1]', 'VARCHAR(Max)'), 'ProdEx.VendorID = [VALUE]' FROM @XmlFilter.nodes('/Filter/ProductSeller') AS ParamValues(Parm) UNION SELECT 'ProdEx','ProductID', ParamValues.Parm.value('ProductID[1]', 'VARCHAR(Max)'), 'ProdEx.ProductID in ([VALUE])' FROM @XmlFilter.nodes('/Filter/ProductSeller') AS ParamValues(Parm) UNION SELECT 'ProdEx','CVSKU', ParamValues.Parm.value('CVSKU[1]', 'VARCHAR(Max)'), 'ProdEx.CVSKU like ''%[VALUE]%''' FROM @XmlFilter.nodes('/Filter/ProductSeller') AS ParamValues(Parm) UNION SELECT 'ProdDesc','ProductName',ParamValues.Parm.value('ProductName[1]', 'VARCHAR(Max)'), 'ProdDesc.ProductName like ''%[VALUE]%''' FROM @XmlFilter.nodes('/Filter/ProductSeller') AS ParamValues(Parm) UNION SELECT 'comp','CompetitorName',ParamValues.Parm.value('CompetitorName[1]', 'VARCHAR(Max)'), 'comp.CompetitorName like ''%[VALUE]%''' FROM @XmlFilter.nodes('/Filter/Seller') AS ParamValues(Parm) UNION SELECT 'comp','CompetitorID', ParamValues.Parm.value('CompetitorID[1]', 'VARCHAR(Max)'), 'comp.CompetitorID in ([VALUE])' FROM @XmlFilter.nodes('/Filter/Seller') AS ParamValues(Parm) UNION SELECT 'compMar','SellerState', ParamValues.Parm.value('SellerState[1]', 'VARCHAR(Max)'), 'compMar.SellerState in ([VALUE])' FROM @XmlFilter.nodes('/Filter/Seller') AS ParamValues(Parm) UNION SELECT 'compMar','Rating', ParamValues.Parm.value('Rating[1]', 'VARCHAR(Max)'), 'compMar.Rating >= [VALUE]' FROM @XmlFilter.nodes('/Filter/Seller') AS ParamValues(Parm) UNION SELECT 'compMar','MarketID', ParamValues.Parm.value('MarketID[1]', 'VARCHAR(Max)'), 'compMar.MarketID in ([VALUE])' FROM @XmlFilter.nodes('/Filter/Seller') AS ParamValues(Parm) UNION --dg: commenting this out, it does not seem to be used and if it comes through it will break SELECT 'compListing','Reviews', ParamValues.Parm.value('Reviews[1]', 'VARCHAR(Max)'), 'compListing.ReviewCnt >= [VALUE]' FROM @XmlFilter.nodes('/Filter/Seller') AS ParamValues(Parm) UNION SELECT 'compListing','SuspectStatus', ParamValues.Parm.value('SuspectStatus[1]', 'VARCHAR(Max)'), 'compListing.SuspectStatusID = [VALUE]' FROM @XmlFilter.nodes('/Filter/Seller') AS ParamValues(Parm) UNION SELECT 'compMP','IgnoreInCart', ParamValues.Parm.value('IgnoreInCart[1]', 'VARCHAR(Max)'), 'compMP.InCart = 0' FROM @XmlFilter.nodes('/Filter/Seller') AS ParamValues(Parm) UNION SELECT 'CompMp','CartPrice', ParamValues.Parm.value('CartPrice[1]', 'VARCHAR(Max)'), '[VALUE] and compMP.Price > 0' FROM @XmlFilter.nodes('/Filter/Seller') AS ParamValues(Parm) UNION SELECT 'CompMp','Condition', ParamValues.Parm.value('Condition[1]', 'VARCHAR(Max)'), 'CompMp.ConditionId IN ([VALUE])' FROM @XmlFilter.nodes('/Filter/ProductSeller') AS ParamValues(Parm) UNION SELECT 'CompMp','PriceRankUpper',ParamValues.Parm.value('PriceRankUpper[1]', 'VARCHAR(Max)'), 'compMP.PricePosition <= [VALUE]' FROM @XmlFilter.nodes('/Filter/Seller') AS ParamValues(Parm) UNION SELECT 'CompMp','PriceRankLower',ParamValues.Parm.value('PriceRankLower[1]', 'VARCHAR(Max)'), 'compMP.PricePosition >= [VALUE]' FROM @XmlFilter.nodes('/Filter/Seller') AS ParamValues(Parm) UNION SELECT 'C" StatementId="4" StatementCompId="18" StatementType="INSERT" StatementSubTreeCost="1278.45" StatementEstRows="4013.67" StatementOptmLevel="FULL" QueryHash="0x39653846CAF10C9C" QueryPlanHash="0x85B7E36B41C9C5DF"> <StatementSetOptions QUOTED_IDENTIFIER="true" ARITHABORT="false" CONCAT_NULL_YIELDS_NULL="true" ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" NUMERIC_ROUNDABORT="false" /> <QueryPlan DegreeOfParallelism="1" MemoryGrant="23608" CachedPlanSize="1072" CompileTime="512" CompileCPU="512" CompileMemory="22344"> <RelOp NodeId="0" PhysicalOp="Table Insert" LogicalOp="Insert" EstimateRows="4013.67" EstimateIO="27.3643" EstimateCPU="0.00401367" AvgRowSize="9" EstimatedTotalSubtreeCost="1278.45" Parallel="0" EstimateRebinds="0" EstimateRewinds="0"> <OutputList /> <RunTimeInformation> <RunTimeCountersPerThread Thread="0" ActualRows="23" ActualEndOfScans="1" ActualExecutions="1" /> </RunTimeInformation> <Update DMLRequestSort="0"> <Object Database="[tempdb]" Schema="[dbo]" Table="[#MyParameters]" /> <SetPredicate> <ScalarOperator ScalarString="[#MyParameters].[TableAlias] = [Expr1514],[#MyParameters].[Parm] = [Expr1515],[#MyParameters].[ParmValue] = [Union1512],[#MyParameters].[ParmFilter] = [Expr1516],[#MyParameters].[ParmWhere] = NULL"> <ScalarExpressionList> <ScalarOperator> <MultipleAssign> <Assign> <ColumnReference Table="[#MyParameters]" Column="TableAlias" /> <ScalarOperator> <Identifier> <ColumnReference Column="Expr1514" /> </Identifier> </ScalarOperator> </Assign> <Assign> <ColumnReference Table="[#MyParameters]" Column="Parm" /> <ScalarOperator> <Identifier> <ColumnReference Column="Expr1515" /> </Identifier> </ScalarOperator> </Assign> <Assign> <ColumnReference Table="[#MyParameters]" Column="ParmValue" /> <ScalarOperator> <Identifier> <ColumnReference Column="Union1512" /> </Identifier> </ScalarOperator> </Assign> <Assign> <ColumnReference Table="[#MyParameters]" Column="ParmFilter" /> <ScalarOperator> <Identifier> <ColumnReference Column="Expr1516" /> </Identifier> </ScalarOperator> </Assign> <Assign> <ColumnReference Table="[#MyParameters]" Column="ParmWhere" /> <ScalarOperator> <Const ConstValue="NULL" /> </ScalarOperator> </Assign> </MultipleAssign> </ScalarOperator> </ScalarExpressionList> </ScalarOperator> </SetPredicate> <RelOp NodeId="1" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="4013.67" EstimateIO="0" EstimateCPU="0.000401367" AvgRowSize="8265" EstimatedTotalSubtreeCost="1251.08" Parallel="0" EstimateRebinds="0" EstimateRewinds="0"> <OutputList> <ColumnReference Column="Union1512" /> <ColumnReference Column="Expr1514" /> <ColumnReference Column="Expr1515" /> <ColumnReference Column="Expr1516" /> </OutputList> <ComputeScalar> <DefinedValues> <DefinedValue> <Co

need to return the last value Posted: 04 Mar 2013 09:12 AM PST Hello comunityI have write this TSQL, but my problema that is return more than one LastPrice for the same REF:select distinct sl.ref as 'Ref', epcpond as 'epcpond',max(ousrdata + ousrhora) as 'Maxdate'from sl inner join planOref x on Rtrim(sl.ref) = RTRIM(x.ref)where epcpond <> 0and sl.datalc <= '20130104'group by sl.ref,epcpondorder by ref ascI know the problem is on the Group by, but i can´t use SUM, MAX or MIN on field LastPrice, the result is for exemple:REF LastPrice MaxDate2009461 3.642000 2012-09-04 16:14:11.0002009461 3.640000 2012-09-04 16:19:24.000 -- This is the correct value and lastEntryThe unique and correct value is the last because the MaxDate is greather than the first.Any idea to solve my problem!Many thanksLuis Santos

JOIN problems Posted: 04 Mar 2013 06:49 AM PST Hey guys,I have these result codes from one table...There are like 12 distinct ones...When I join to the another table to get how many accounts were associated with each result code, Im getting back the correct numbers that I am looking for...But the requestor has asked to show all the result codes even if there are 0 accounts associated with them...But for some reason I am just getting back the result code that has a number attached to it...If it is zero it just doesnt show up...I only am getting back like 6 rows where I should be getting back 12...Let me also say, I have to join to this other table to get a specific type I tried putting all the distinct codes in a temp table, then selecting from the temp table and doing a left outer join on the other table to get the number accounts and inner join the other table to get the correct types but it still only returns the 6 codes that have data associated to them...Any suggestions?

Index filled after created Posted: 04 Mar 2013 04:03 AM PST If you have a table that is already populated (say 900,000 rows) and then you create an index, is the index automatically filled at that point?Thanks,Tom

Number of days between two integer date columns Posted: 04 Mar 2013 04:07 AM PST Hi Friends,I have a requirement where i have to write a Sql for :basically i have 2 columns 'startdate_SK' and 'enddate_SK' columns in table 'A''startdate_SK' is a integer ex:99971231'enddate_SK' is a integer ex: 17530101requirement: need to find the number of days between the above two columnsex: 'startdate_SK' - 20130101 and 'enddate_SK' - 20130201 then my result should be 31IMP:: both columns are integers in the table.i can use datediff but they are not date columns instead they are integers.Please suggest, any help is appreciated,--Thanks,Sam.

Working on MERGE statement to Handle SCD Posted: 04 Mar 2013 12:56 AM PST Hi All,Could you please provide me an example or step, to handle both slowly changing dimension type 1 & type 2 in a single MERGE tsql query in SQL Server 2008.Please help.. :)ThanksSam

You are subscribed to email updates from SQLServerCentral / SQL Server 2008 / T-SQL (SS2K8) To stop receiving these emails, you may unsubscribe now.	Email delivery powered by Google
Google Inc., 20 West Kinzie, Chicago IL USA 60610

[SQL Server 2008 issues] transactions

---

• transactions
• Update and Insert Performance
• SQL 2008 enterprise to standard degradation
• SQL SERVER database encryption.
• SSIS OLEDB source executing but not returning results
• SQL Server 2008 R2 not starting after moving"model" database
• Copying from one column to another with truncation.
• date format 7/31/2013
• Copy a big table (250G) form one server to another
• query performance
• How to maintain two digit values in sp?
• SSRS Reporting assitance- consolidated report using 3 Stroed PROC
• Transaction log in Subscriber database for Transactional Replication
• Including additional columns in a unique clustered index
• sp_updatestats and the default sampling rate
• database metric
• How to uncommit the transaction and execute the next statement in the cursor?
• Huge Transaction on Database with Merge Replication, Simple Recovery Model
• Primary Key is not AutoGenerating
• CLR Stored Procedures
• Automated Index Maintenance
• Querying the ringbuffer target - only returning the first half of the data
• how to get list of databases and updte support group for them in SQL CMS
• Date Add in table
• Left join a query result to a table
• Primary key or not to primary key, that is the question
• Choose between two date fileds
• Remove "SQL Server 2005 (64-bit)" after in-place upgrade to 2008 R2?
• performance issues in parsing multiselect string paramters in SSRS, pfb the code
• Install SQL Server 2008 R2 on new PC
• (Could not find stored procedure 'msdb.dbo.sp_dts_getfolder'. - sql 2005 - > 2008 R2
• Accessing Linked Server using PowerPivot
• Question on Replication..
• Searching for Solution
• SELECT list block with CASE?
• An issue with my Execution plan

transactions Posted: 04 Mar 2013 04:46 PM PST how to update 2 tables in 2 different Db within same transaction ?what if 2 Db are in different server ?

Update and Insert Performance Posted: 04 Mar 2013 04:55 PM PST In Insert and Update, Multiple Index and Inculude columns is effect performance.

SQL 2008 enterprise to standard degradation Posted: 03 Mar 2013 11:38 PM PST Hi Team,I have request from client to degrade few servers due to cost of enterprise edition.Can you suggest me the plan and things to take care.My Plan is as below.1. Backup all the databases.2. Script logins and jobs.3. Dettach the user databases.4. Uninstall the enterprise edition.5. Install the standard edition.6. Attach databases.7. Run login and job scripts.Please suggest.

SQL SERVER database encryption. Posted: 04 Mar 2013 07:22 AM PST Hello All,I am in a bind here and hoping someone will be able to help me out in this regard.There is a database in our environment that is using database encryption. The database server cert file and servercerthkey is avialable but I cant find the password. I am totally confused, of all the materiel red online I cant seems to find the answer.how can I restore this database?All the material online explain how to do it will password. what is the password going to be. I don't know who created the password at the time of setting up the encryption, all I have is the database, backup and servercert file and servercertkey file. what are my option. Many thanks in this regard.Is it possible to change/reset the password?Thanks a lot.B

SSIS OLEDB source executing but not returning results Posted: 04 Mar 2013 04:38 PM PST Hi There,I have a query in a SSIS package. it has been running fine and returning required results to the destination, suddenly it runs and completes but does not return results.Took it out of the package and ran it on the server it does return results.any idea what could be causing this?it is a straigh forward query with only the period as a parameter.[code="sql"]Select Col1, Col2, Col3 From Table1 Where Period = ?[/code]Period is an int in this format 201302. I tried it as string still got no joy.Please help as the only other option I'm thinking of is putting it in a stored proc.Kind Regards

SQL Server 2008 R2 not starting after moving"model" database Posted: 04 Mar 2013 10:16 AM PST SQL Server 2008 R2 not starting after moving model database. Any ideas? Error: 945, Severity: 14, State: 2. Any ideas how to correct without reinstalling?Also Sql 2008 R2 will not start with trace -f -T3608 or single user mode -m. Need params to start SMS to move Model back.Need resolution please.

Copying from one column to another with truncation. Posted: 04 Mar 2013 02:14 PM PST Hi GuysI have a table called Mytable.In it are 2 columns, Description(50 Char) and Longdescription(200 char)I need to copy the content of longdescritption(data with more tha 50 characters) into my descritption table.I dont mind if it copys the first 50 characters of the data and truncates the rest.How do I acheive this ?Thanks in advance.A

date format 7/31/2013 Posted: 04 Mar 2013 03:52 AM PST Hi friends,Im new to TSql -I have requirement to display date as 7/31/2013 but currently I'm getting it from DB as 2013-03-01 18:40:24.000.any help on this will he highly appreciated,ThanksDhananjay

Copy a big table (250G) form one server to another Posted: 04 Mar 2013 04:56 AM PST Hi,I need to copy a big table (about 250G, basically used to store images) that I have on a MSSQL Server to a MSSQL Server. I don't need to transform data but I don't if this command will worksINSERT INTO Temp SELECT * FROM [SourceServerName].DBName.dbo.TempThanks for your help

query performance Posted: 04 Mar 2013 08:53 AM PST I have a table with 6 million rows. The table has around 200 columns. The data can be sliced and diced by the user in a number of ways. Are there any tips of improving query performance other than adding indexes?Currently some of the queries take around 40 seconds.thanksKK

How to maintain two digit values in sp? Posted: 15 Mar 2012 03:43 AM PDT Hi friends,IF OBJECT_ID('Sp_Coded') IS NOT NULL BEGIN Drop procedure Sp_Coded EndgoCREATE PROCEDURE [dbo].[Sp_Coded] (@ParentTableName varchar(50), @ColumnName varchar(40), @CodeID int, @Number bigint, @MaxLength int, @SetID varchar(10), @Flag varchar(10), @SrcTablename varchar(100), @SrcCodeValueColumn varchar(100), @SrcDescColumn varchar(100), @SQL varchar(5000)) ASBEGIN DECLARE @MySql varchar(5000) IF ( @Flag = '2' ) BEGIN SET @MySql = 'SELECT Distinct 0 as PnxJurisID , ' + Cast(@CodeID as Varchar(15)) + ' as CodedID, B.' + Cast(@SrcCodeValueColumn as varchar(50)) + ' as SrcCodeValueRef, B.'+ CAST( @SrcCodeValueColumn as varchar(50)) + ' as SrcCodeValue, B.'+ CAST( @SrcDescColumn as varchar(50)) +' as SrcCodeDescr ' + ' From dbo.' + @ParentTableName + ' A left outer join SrcCodeMap on A.' + @ColumnName + ' = SrcCodeMap.SrcCodeValueRef and SrcCodeMap.CodeID = '+ Cast(@CodeID as Varchar(15)) + ' left outer join '+ @SrcTablename +' B on B.'+ @SrcCodeValueColumn +' = A.' + @ColumnName + ' WHERE SrcCodeMap.JurisID is Null AND A.' + @ColumnName + ' Is Not Null ' END else IF ( @Flag = '1' ) BEGIN SET @MySql = 'SELECT Distinct 0 as PnxJurisID , ' + Cast(@CodeID as Varchar(15)) + ' as CodedID, ' + @ParentTableName+ '.' + @ColumnName + ' as SrcCodeValueRef, CASE WHEN SCTABE.TECOD# IS NULL THEN LTRIM(RTRIM(' + @ParentTableName+ '.' + @ColumnName + ')) ELSE LTRIM(RTRIM(SCTABE.TECOD#)) END as SrcCodeValue, SCTABE.TEDESC as SrcCodeDescr ' + ' From dbo.' + @ParentTableName + ' left outer join SrcCodeMap on ' + @ParentTableName + '.' + @ColumnName + ' = SrcCodeMap.SrcCodeValueRef and SrcCodeMap.CodeID = '+ Cast(@CodeID as Varchar(15)) + ' left outer join SCTABE on rtrim(ltrim(SCTABE.TECOD#)) = rtrim(ltrim('+ @ParentTableName+ '.' + @ColumnName + ')) and SCTABE.TEMST# = ' + Cast( @SetID as Varchar(8)) + ' WHERE SrcCodeMap.JurisID is Null ' END else IF ( @Flag = '3' ) BEGIN SET @MySql = @SQL END print @MySql IF OBJECT_ID('tempSrcCodeDB', 'U') IS NOT NULL DROP TABLE tempSrcCodeDB; CREATE TABLE tempSrcCodeDB (JurisID int, CodedID varchar(15), SrcCodeValueRef Varchar(50), SrcCodeValue varchar(60), SrcCodeDescr varchar(60)) INSERT INTO tempSrcCodeDB (JurisID,CodedID, SrcCodeValueRef, SrcCodeValue, SrcCodeDescr) EXEC(@MySql) DECLARE @JurisID int, @CodedID varchar(15), @SrcCodeValueRef varchar(50), @SrcCodeDescr varchar(60), @SrcCodeValue varchar(40), @DecNumber bigint SET @DecNumber = @Number DECLARE MySql CURSOR FOR SELECT * FROM tempSrcCodeDB OPEN Mysql FETCH NEXT FROM Mysql INTO @JurisID, @CodedID, @SrcCodeValueRef, @SrcCodeValue, @SrcCodeDescr WHILE @@FETCH_STATUS = 0 BEGIN IF ( (LEN(@SrcCodeValue) <= @MaxLength) and ((@SrcCodeValueRef) <> '') and ((@SrcCodeValueRef) <> null) ) BEGIN insert into SrcCodeMap (JurisID, CodeID, SrcCodeValueRef, SrcCodeValue, SrcCodeDescr, PnxCodeValue) Values (@JurisID, @CodedID, @SrcCodeValueRef, @SrcCodeValue, @SrcCodeDescr, @SrcCodeValue) END else IF (((@SrcCodeValueRef) <> '') and ((@SrcCodeValueRef) is not null) ) BEGIN insert into SrcCodeMap (JurisID, CodeID, SrcCodeValueRef, SrcCodeValue, SrcCodeDescr, PnxCodeValue, SysCodeValue) Values (@JurisID, @CodedID, @SrcCodeValueRef, @SrcCodeValue, @SrcCodeDescr,@DecNumber, @DecNumber) SET @DecNumber = @DecNumber + 1 END FETCH NEXT FROM Mysql INTO @JurisID, @CodedID, @SrcCodeValueRef, @SrcCodeValue, @SrcCodeDescr END CLOSE Mysql DEALLOCATE MysqlENDThis is my stored procedure..here i have a parameter of variable @MaxLength where i pass the value like 2 or 3 or 4 and depends upon the need.if i pass 2, the @DecNumber would start from 10 and gets incremented(SET @DecNumber = @DecNumber + 1) up to the total row countso obviously if the row count exceeds 99, it would go to 3 digit values like 100,101,102 and so on...my problem is if the @maxLength is 2, at any cost it should maintain distinct two digit values in a sequence...like 0 to 99 and instead of 100 it could be A1 ,A2,A3.....A9, B1....B9,C1..C9 and so on...i am not asking that i really want this kinda sequence..it may be any kinda sequence only with 2 digits...I know this question may look odd..but this is my situation..so please help me friends...

SSRS Reporting assitance- consolidated report using 3 Stroed PROC Posted: 04 Mar 2013 10:48 AM PST Hi,I have 3 Stored Procedures 1) SP_JobPostingID,2)SP_WorkerOrder, and3) SP_WorkOrderRevisionNote- all the 3 have same number and signature of Columns, what I need to know is how develop an SSRS report using them combined in Microsoft Visual Studio.one way I can think of is creating 3 Datasets for the same Reportdata but wished to know if any other way out .ThanksDhananjay

Transaction log in Subscriber database for Transactional Replication Posted: 04 Mar 2013 08:31 AM PST I have created replication between two SQL Server 2008R2 databases and it has been functioning well since the initial setup 3 months ago. The publisher database is the back-end for an OLTP application with 50 users who are working constantly, making changes and add new information. The Subscriber database is used for reporting. Today I noticed that the Subscriber database was set to Full Recovery Mode with no transaction log backups. I panicked for a minute and took a quick look at the Transaction Log drive on the Subscriber server - it has 50% free space, 24 GB. I was amazed that I had been avoiding what normally would cause a drive-full issue.My question is this. Since replication is constantly making changes to the Subscriber database (updating various tables as they change in the Publisher database), why is the Subscriber log file not filling up and giving me grief since I don't run backups on the Subscriber database - and haven't done for the entire three months this has been running? Gail Shaw mentioned in another forum question while I was researching this, that 'Replication does not in any way use the Subscriber's log file'. I'm not sure that I understand the seeming contradiction been the updating going on in the Subscriber database, Full Recovery mode, no Transaction Log backups taking place and the Transaction Log not filling up.Can anyone enlighten me and/or point me to articles that might help me understand this more thoroughly??Thank you. Willem

Including additional columns in a unique clustered index Posted: 04 Mar 2013 02:58 AM PST I am creating an indexed view from a business unit table. The goal of the view is to pre-filter the unit list to only those supported by our division. The business hierarchy goes from unit to district to region, with unit # being unique on the table.However, we rarely ever use unit in our applications and instead focus on the district number (as per business requirements.) One of the most common queries performed is to get a distinct list of districts based upon region selections. Only occasionally would the unit # be included too.Should the region and district columns be included in the unique clustered index of the view? Or should they be limited to a separate nonclustered index? I'm leaning toward the latter, but if anyone has a good reason to include them in the clustered index, I'd love to hear it. :-)

sp_updatestats and the default sampling rate Posted: 04 Mar 2013 08:51 AM PST Using SQL Server 2008R2As my Production database approaching 500GB and potentially can grow up to 1TB in 2 years, issue with sp_updatestats (after re-indexing part of maint step) using the default sampling rate (which potentially can skew the performance) bothers me.It has been a discussion earlier at that link: http://www.sqlservercentral.com/Forums/Topic1310877-146-2.aspx, but I still confused about using sp_updatestatsCurrently I am performing the following steps during weekend maintenance:1. ALTER Database MyDBSET RECOVERY Simple,AUTO_CREATE_STATISTICS OFF,AUTO_UPDATE_STATISTICS OFF2. My index maintenance routine based on the following criteria:Currently I Reindex Clustered and Non-Clustered Indexes when avg_page_space_used_in_percent < 75 and avg_fragmentation_in_percent > 10 and page_count > 500.Of those selected, if Fragmentation <=30, than I reorganize those Indexes. If Fragmentation > 30, than I rebuild those Indexes. So at the end of the Reindex Maint I have Non-Clustered and Clustered Indexes either Rebuilt or Reorganized.3. Currently I am running the Update Statistics on a whole database after previous reindex step:[b]sp_updatestats[/b]Since sp_updatestats updates statistics by using the default sampling rate, it possibly can deteriorate all my indexes after running reindex routine.[b]3A.[/b] So I was thinking about "… update statistics for all tables which are defragged and don't run update statistics for all tables which are REBUILD"http://sqlserverpedia.com/wiki/Updating_StatisticsSo here is my logic for performing routine in [b]3A[/b]Select indexes which were "REORGANIZE"d (no statistics update) during reindex maintenance along with other indexes, where statistics were either not updated for the last, say, few weeks and run the following:UPDATE STATISTICS Table_Name (IndexName) WITH FULLSCAN, NORECOMPUTE against indexes selected above.By running above I will be able to update statistics without running sp_updatestats4. ALTER Database MyDBSET RECOVERY Full,AUTO_CREATE_STATISTICS ON,AUTO_UPDATE_STATISTICS ONPlease let me know if you have any comments, suggestions, recommendations on [b]step 3A[/b].It has been a suggestion earlier to run: USE dbname;EXEC sys.sp_MSforeachtable @command1 = N'UPDATE STATISTICS ? WITH FULLSCAN, COLUMNS;';But unfortunately it takes way to long on my Production Database, given the time frame I have for the weekend maintenance.

database metric Posted: 04 Mar 2013 12:34 AM PST Is it possible to produce any sort of database level performance metric in SQL Server. Let's say how many queries are being processed by that database? what's the response time. average response time or processing time. Anything that can help me track performance of a specific database on the SQL Server instance.thanks

How to uncommit the transaction and execute the next statement in the cursor? Posted: 03 Mar 2013 10:17 PM PST Hi Friends, [code="sql"]DECLARE @JurisID int, @CodedID int, @SrcCodeValueRef varchar(100), @SrcCodeDescr varchar(100), @SrcCodeValue varchar(100), @DecNumber bigintSET @DecNumber = @Numberdeclare @result varchar(50), @chars varchar(36), @IntFlag intSET @chars = N'0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ' DECLARE MySql CURSOR FOR SELECT * FROM #tempSrcCodeDB where SrcCodeValueRef is not null and SrcCodeValueRef not like '' and LEN(LTRIM(RTRIM(SrcCodeValue))) > @MaxLengthOPEN MysqlFETCH NEXT FROM Mysql INTO @JurisID, @CodedID, @SrcCodeValueRef, @SrcCodeValue, @SrcCodeDescrWHILE @@FETCH_STATUS = 0 BEGIN IF ((LEN(@DecNumber)= @MaxLength ) and LEN(LTRIM(Rtrim(@SrcCodeValue))) > @MaxLength ) /* If Number is greater than the maxlength */ BEGIN insert into SrcCodeMap (JurisID, CodeID, SrcCodeValueRef, SrcCodeValue, SrcCodeDescr, PnxCodeValue, SysCodeValue, VendorCode,CreatedDttm) Values (@JurisID, @CodedID, @SrcCodeValueRef, @SrcCodeValue, @SrcCodeDescr,@DecNumber, @DecNumber,@VendorCode,GETDATE()) set @DecNumber = @DecNumber - 1 GOTO FETCHNEXT ENDELSE BEGIN set @IntFlag = 0 WHILE (@IntFlag = 0) /* We used to generate random(alpha-numeric) values when we run out of Number */ BEGIN IF @MaxLength = 1 BEGIN SET @result = SUBSTRING(@chars, CAST((RAND() * LEN(@chars)) AS INT) + 1, 1) END IF @MaxLength = 2 BEGIN SET @result = SUBSTRING(@chars, CAST((RAND() * LEN(@chars)) AS INT) + 1, 1) + SUBSTRING(@chars, CAST((RAND() * LEN(@chars)) AS INT) + 1, 1) END IF @MaxLength = 3 BEGIN SET @result = SUBSTRING(@chars, CAST((RAND() * LEN(@chars)) AS INT) + 1, 1) + SUBSTRING(@chars, CAST((RAND() * LEN(@chars)) AS INT) + 1, 1) + SUBSTRING(@chars, CAST((RAND() * LEN(@chars)) AS INT) + 1, 1) END IF NOT EXISTS(select * from SrcCodeMap where CodeID = @CodeID and LTRIM(RTRIM(PnxCodeValue)) = @result ) --IF (select COUNT(distinct PnxCodeValue) from SrcCodeMap where CodeID = @CodeID and LTRIM(RTRIM(PnxCodeValue)) = @result ) = 0 BEGIN insert into SrcCodeMap (JurisID, CodeID, SrcCodeValueRef, SrcCodeValue, SrcCodeDescr, PnxCodeValue, SysCodeValue, VendorCode,CreatedDttm) Values (@JurisID, @CodedID, @SrcCodeValueRef, @SrcCodeValue, @SrcCodeDescr,@result, @result, @VendorCode,GETDATE()) GOTO FETCHNEXT END ELSE continue END ENDFETCHNEXT:FETCH NEXT FROM Mysql INTO @JurisID, @CodedID, @SrcCodeValueRef, @SrcCodeValue, @SrcCodeDescrENDprint @@ROWCOUNTCLOSE Mysql DEALLOCATE MysqlEND[/code] I have written this cursor(I showed you part of it) to get some distinct random values based on the length given by user (this cursor is called by a stored procedure). As you see, i declared a variable containing '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ' . So the function will generate random values from here. My problem is, if i have to generate random values of one char length, i can generate upto 36 different values...If the input given by the customer, I mean the input is always going to be coming from the select statement like "select column1 from table. " So if the count of the data exceeds more than 36, this cursor can't generate any more values of length 1. So the cursor is keep on looping and it is uncommitted...So i am not able to move to the next input given by the end user. So gimme your suggestions to avoid if any of the input is keep on looping due to this kinda problem....i want to uncommit it and move to the next statement. Please gimme any kinda your suggestion if you really think there might be another way to handle his. Any suggestions would be really appreciated.

Huge Transaction on Database with Merge Replication, Simple Recovery Model Posted: 03 Mar 2013 11:41 PM PST We have a database which normally has a 100GB transaction log.This database is a publisher in a merge replication with one subscriber.The transaction log has grown to 235GBWhen I query sys.databases for log_reuse_wait_desc I get "REPLICATION" for this database.We recently had a corruption issue (CHECKSUM ERROR) in this database, which was fixed last thursday by running DBCC CHECKDB with REPAIR_ALLOW_DATA_LOSS option (only 1 page was flagged as corrupt and business decided it was better to lose this than have to restore the database)The affected table is part of the publication.dbcc opentran with tableresults gives:<databasename>OPENTRANREPL_DIST_OLD_LSN (0:0:0)REPL_NONDIST_OLD_LSN (4582486:1093:1)Checking the transaction log: select [Current LSN],[Operation],[Transaction ID], Left([Description],20) from::fn_dblog('0x0045EC56:00000445:0001','0x0045EC56:00000445:0010')GivesCurrent LSN Operation Transaction ID (No column name)0045ec56:00000445:0001 LOP_BEGIN_XACT 0000:903b4a9b CheckDb;0x01050000000045ec56:00000445:0002 LOP_MODIFY_ROW 0000:903b4a9b Deallocated 0001:0000045ec56:00000445:0003 LOP_LOCK_XACT 0000:903b4a9b 0045ec56:00000445:0004 LOP_MODIFY_ROW 0000:903b4a9b Deallocated 0001:0020045ec56:00000445:0005 LOP_HOBT_DELTA 0000:903b4a9b 0045ec56:00000445:0006 LOP_MODIFY_ROW 0000:903b4a9b Deallocated 0001:0070045ec56:00000445:0007 LOP_HOBT_DELTA 0000:903b4a9b 0045ec56:00000445:0008 LOP_MODIFY_ROW 0000:903b4a9b Deallocated 0001:0070045ec56:00000445:0009 LOP_HOBT_DELTA 0000:903b4a9b 0045ec56:00000445:000a LOP_MODIFY_ROW 0000:903b4a9b Deallocated 0001:0070045ec56:00000445:000b LOP_HOBT_DELTA 0000:903b4a9b 0045ec56:00000445:000c LOP_MODIFY_ROW 0000:903b4a9b Deallocated 0001:0000045ec56:00000445:000d LOP_HOBT_DELTA 0000:903b4a9b 0045ec56:00000445:000e LOP_MODIFY_ROW 0000:903b4a9b Deallocated 0001:0010045ec56:00000445:000f LOP_HOBT_DELTA 0000:903b4a9b 0045ec56:00000445:0010 LOP_MODIFY_ROW 0000:903b4a9b Deallocated 0001:002As far as I understood, merge replication isn't supposed to have an impact on transaction log. The transaction log appears to be growing ever since.Can anyone tell me why this is happening?Thanks

Primary Key is not AutoGenerating Posted: 04 Mar 2013 07:49 AM PST I am new to SQL Server, but have exposure to databases on Mainframe & MS Access, but this is giving me a headache ...I am using VB2010 Express and used Project Add New Item to add a SQLCE 3.5 Database to my small program.I then created 2 Tables, called "Leagues" & "Teams", by right clicking on the Tables tab in Database Explorer.I have used the Solution Explorer to open the DataSet and add a Foreign Key. This is what I now have :[b]Table1 : Leagues[/b]ID - PK, AutoIncrement = True, 1, 1, DataType = System.Int32, Unique = TrueName - DataType = System.String[b]Table2 : Teams[/b]ID - PK, AutoIncrement = True, 1, 1, DataType = System.Int32, Unique = TrueName - DataType = System.StringLeagueID - FK, AutoIncrement = False, DataType = System.Int32, Unique = FalseHowever, if I right-click on the Table name in Database Explorer & choose Show Table Data, so I can enter a Test entry, it gets rejected with an error message telling me that the ID column cannot be Null ... so why is it not AutoIncrementing ?!? This happens if I try to enter a Test entry into either Table ... BTW, I have read on this site already that some people consider using UniqueKey as a bad thing, but in this case, it is a single user database, so won't be compromised and there is no chance of a lockout, it's just my own small program (that doesn't work !!!) ...

CLR Stored Procedures Posted: 04 Mar 2013 05:52 AM PST Hi All,I am trying to create a CLR stored procedure. What I was hoping for was a stored procedure that would understand what my object was. Ex: I have a MedicalRecord.dll that I developed. I want to be able to set a reference to my MedicalRecord.dll. How can this be done? I'm not simply looking for a substitute for a T-SQL insert/update statement. Thanks,Bill

Automated Index Maintenance Posted: 04 Mar 2013 06:37 AM PST I'm looking for a product to Automate our Index Maintenance. Currently we have a product that allows scheduling but uses its own proprietary scheduler. I'm looking for something that would allow for either a CLI or SQL Server Job to execute the analysis and maintenance.

Querying the ringbuffer target - only returning the first half of the data Posted: 04 Mar 2013 06:25 AM PST I am running the query below and seeing deadlocks from 2013-03-01 17:27:44.213 to 2013-03-03 06:51:22.317. It is currently 2013-03-04 15:20. You may say "Well then, there are no deadlocks after 6:51!" I wuold say that is true, except we record them in the error log and I know there are plenty. Also... If I wait a couple of minutes and run my query again, I will get deadlocks from 2013-03-01 17:30:42.153 to 2013-03-03 06:54:51.211. What is going on? Am I only able to pick up the first half (or some percent) of the ring buffer?[code="sql"]select XEventData.XEvent.value('@timestamp', 'datetime') AS DeadlockDateTime, CONVERT( xml, XEventData.XEvent.value('(data/value)[1]', 'varchar(max)')) as DeadlockGraphFROM (select CAST(target_data as xml) as TargetDatafrom sys.dm_xe_session_targets st WITH(NOLOCK)join sys.dm_xe_sessions s WITH(NOLOCK) on s.address = st.event_session_addresswhere name = 'system_health') AS DataCROSS APPLY TargetData.nodes ('//RingBufferTarget/event') AS XEventData (XEvent)where XEventData.XEvent.value('@name', 'varchar(4000)') = 'xml_deadlock_report'OPTION(MAXDOP 1)[/code]

how to get list of databases and updte support group for them in SQL CMS Posted: 04 Mar 2013 05:30 AM PST i dont know what I am askingfor is possible or not so just want to give a try here...:-)I want to get a list of all databases and servers registered in SQL Central Management server and populate a sql table "A". I will have another table "B" with the database names and database supportgroups i.e, supportgroup1, supportgroup2 and supportgroup3.After that I want to create a job that looks for the newly created databases and updates the tabe-A or creates a new table if needed , with data supportgroups and if there is no support group associated with a database, I get an email to update the table with right support group.

Date Add in table Posted: 04 Mar 2013 05:17 AM PST HelloI need one help. I need to add missing date in the table so here is one sample on some data so please let me know how is it possible.[code="sql"]create table #x1(SchoolID int,CalendarDate datetime)insert into #x1 values (101,'2012-08-18')insert into #x1 values (101,'2012-08-19')insert into #x1 values (101,'2012-08-20')insert into #x1 values (101,'2012-08-21')insert into #x1 values (101,'2012-08-29')insert into #x1 values (101,'2012-08-30')insert into #x1 values (101,'2012-08-31')[/code]I got output as below[code="plain"]SchoolID CalendarDate101 2012-08-18 00:00:00.000101 2012-08-19 00:00:00.000101 2012-08-20 00:00:00.000101 2012-08-21 00:00:00.000101 2012-08-29 00:00:00.000101 2012-08-30 00:00:00.000101 2012-08-31 00:00:00.000[/code]But desired output is[code="plain"]SchoolID CalendarDate101 2012-08-18 00:00:00.000101 2012-08-19 00:00:00.000101 2012-08-20 00:00:00.000101 2012-08-21 00:00:00.000101 2012-08-22 00:00:00.000101 2012-08-23 00:00:00.000101 2012-08-24 00:00:00.000101 2012-08-25 00:00:00.000101 2012-08-26 00:00:00.000101 2012-08-27 00:00:00.000101 2012-08-28 00:00:00.000101 2012-08-29 00:00:00.000101 2012-08-30 00:00:00.000101 2012-08-31 00:00:00.000[/code]can anyone please tell me is it possible in SQL SERVER?

Left join a query result to a table Posted: 04 Mar 2013 05:15 AM PST In Table A I have a list of projects (tbprojects).In Table B I have a list of status updates for projects with a month and year columns (tbstatus).I want to create a query that will return all projects in the list and then show all status updates for a specific month and year.If aproject has no status update for that given month, i still want it to show up in the query result, but with simply "NULL" values for the fields from the status update table.How would I write a query like that?I tried this:[b]select * from tbprojects left join tbstatus on tbprojects.id = tbstatus.projectid where month = 2 and year = 2013[/b]But this only returns the projects from tbprojects that have a status update for the given month and year. I need all of the projects to appear in the result. How can i modify my query to do that?

Primary key or not to primary key, that is the question Posted: 04 Mar 2013 02:58 AM PST Yeah, bad taste on the subject but nonetheless, you're reading my post :). I'd like to pose a scenario and see what other developers are doing out there (or would recommend at least).We have most of our tables defined with a PRIMARY KEY constraint over a single column that is defined as UNIQUEIDENTIFIER with a default of NEWID(). This is never clustered unless the table is small. We use this in our child tables as the foreign key reference like so (excuse the pseudo table definitions):Table:ParentID UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID() PRIMARY KEY,Field VARCHAR(30) NOT NULL,Code INT NOT NULLStatus VARCHAR(3) NOT NULLTable:ChildSurrogateID INT IDENTITY(1, 1) NOT NULL, --has a unique clustered index definedID UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID() PRIMARY KEY,Field1 VARCHAR(30) NOT NULL,ParentID UNIQUEIDENTIFIER NOT NULL (FK defined to Parent.ID)Obviously the primary key constraint maintains an index behind the scenes. When we query between the two tables it will look soemthing like this:SELECT P.ID, P.Field, P.Code, P.Status, C.ID, C.Field1FROM dbo.Parent AS P INNER JOIN dbo.Child AS C ON P.ID = C.ParentIDLooks simple right? This query should (and does for me) do an index seek using the primary key constraint. But it also generates an expensive key lookup (clustered) over the clustered index. The only way to resolve that is to add included columns in the primary key to resolve it.Yes... primary keys indeed *DO NOT* allow included columns to be defined. At this point I have one of two options:- Drop the PRIMARY KEY and simply make a unique index with the necessary included columns- Create a unique index over Parent.ID with the neccessary included columns. However this results in doubling the index storage required for the Parent.ID column.So the question is this, do you:- drop the primary key and create the unique index with included columns- duplicate the primary key and create the unique index with included columnsI'm leaning towards dropping the PK and creating the unique index myself. Thoughts?

Choose between two date fileds Posted: 04 Mar 2013 04:39 AM PST I am trying to finish a report in BIDS that will let the user choose between two fields of the choosing, not one I specified. I am having trouble with this. The field I am using is DD.DateDesc to pull the date. PLEASE HELP?select dimUser.EmpNo AS [User Number],dimUser.EmpFullName1 [User Name],activityname [Activity Name],CONVERT(VARCHAR(10), DD.DateDesc, 101) [Completion Date],MAX(AL.CECredits) [Contributing Credits],CER.CERegion_Name License,dimUser.OptEmp_Txt1 [License Type],dimUser.OptEmp_Txt2 [License Number],CASE RTRIM(LTRIM(V.VenName)) WHEN 'BayCare Education Services' THEN '50-201' WHEN 'Morton Plant Mease Health Care' THEN '50-1378' WHEN 'St. Josephs Hospital' THEN '50-1867' END ProviderId,AL.ApprovalCode [Approval Code],AO.txt1 [Credit Category],dimUser.PrimaryJobName [Primary Job], dimUser.PrimaryOrgName From factAttempt fact INNER JOIN dimActivity A ON A.ID = fact.ActivityIDINNER JOIN TBL_TMX_ActLinks AL ON AL.ActivityFK = A.ActivityFK AND AL.LinkType = 3 INNER JOIN dimUser ON dimUser.ID = fact.UserID INNER JOIN TBL_TMX_ActOpt AO ON AO.ActivityFK = A.ActivityFK INNER JOIN dimDate DD ON DD.DateID = fact.EndDtID INNER JOIN tblEmpCERegion ER ON ER.EmpCERegion_EmpFK = dimUser.EmpFK INNER JOIN CERegion CER ON CER.CERegion_PK = ER.EmpCERegion_CERegionFK AND CER.CERegion_Name LIKE '%-%' INNER JOIN factResourceVen AV ON AV.ActivityID = A.ID INNER JOIN dimVen AS V ON V.ID = AV.VenID left outer join TBL_TMX_ActCECC CECC on CECC.ActivityFK = A.ID left outer join CreditCatType cct on cct.CreditCatType_PK = CECC.CreditCatTypeFK where CER.CERegion_PK = 5 and RegistrationStatusID = '4'and fact.EndDtID >= '-1' group by dimUser.EmpFullName1, activityname, CER.CERegion_Name, V.VenName, AL.ApprovalCode, cct.CreditCatType_Name, dimUser.EmpNo, AO.txt1, dimUser.PrimaryJobName, dimUser.PrimaryOrgName, dimUser.EmpStartDt, dimUser.OptEmp_Txt1, dimUser.OptEmp_Txt2, dd.datedesc, RegistrationStatusID

Remove "SQL Server 2005 (64-bit)" after in-place upgrade to 2008 R2? Posted: 04 Mar 2013 03:53 AM PST Hi all,When upgrading SQL, we normally either detach/reattach or backup/restore. However, we have just been handed a server that had an "in-place" (?) upgrade performed from SQL 2005 to 2008 R2. This seems to have left SQL 2005 on the Programs List. In other words, if I go to Add/Remove programs, I see all the usual entries for 2008 (such as "[b]Microsoft SQL Server 2008 (64-bit)[/b]" and "[b]Microsoft SQL Server 2008 Native Client[/b]" and "[b]Microsoft SQL Server 2008 Browser[/b]", etc...).However, there are also two entries for 2005:[b]Microsoft SQL Server 2005 (64-bit)Microsoft SQL Server 2005 Backward compatibility[/b]Note that, in the services list, I [b][i]only [/i][/b]see services for the expected 2008 R2 instance and the users have had no problems connecting to and using the upgraded SQL DBs.So, [b][u]is it safe to remove the two SQL 2005 items listed in the add/remove programs list[/u][/b]? I ask because the files are apparently throwing up red flags when we run our patch/hotfix scans.Thanks!

performance issues in parsing multiselect string paramters in SSRS, pfb the code Posted: 04 Mar 2013 02:46 AM PST i am having trouble in parsing organisation name around(2000) and getting data doing filteration on these names - please note that organisation and organisation_id are not one to one mapped, need distinct organisations in the parameters drop down and all id's for calculation, for which i am putting where condition on organisation names.select vd.Month, vd.month_No, vd.Year, vd.Week, vd.Date, vd.Organisation, vd.[Business Line], vd.[Sales Executive], count(distinct vd.CSTMR_KEY)[Total no of Lines], ( case when SUM(vd.[Total Amount Due])>0 then COUNT(distinct vd.CSTMR_KEY) else 0 end )[Total No Of Unpaid Lines], SUM(vd.[Total Amount Due])[Total Amount Due] from IV_DBT_RPT_FR_ORGNSTN VD where (vd.Date>=@FromDate and vd.Date<=@ToDate)AND VD.Organisation IN (@Organisation)AND VD.[Business Line] IN (@BusinessLine)AND VD.[Sales Executive] IN (@SalesExecutive)group by vd.AR_BHVR_KEY, vd.[Business Line], vd.C, vd.CNTRCT_DD, vd.CSTMR_KEY, VD.Year, vd.Date, vd.month_No, vd.Month, VD.Week, vd.Organisation, vd.[Sales Executive]--order by VD.Date

Install SQL Server 2008 R2 on new PC Posted: 04 Mar 2013 02:54 AM PST I replaced my PC with one that has windows 7 (Home Edition). I removed SQL Server 2008 R2 Developer application from the old PC and attempt to install the application on the new one, using the original CD supplied with my original purchase.It seems that I cannot install this version on the new PC without applying Service Pack 3. However, I can't apply Service Pack 3 until I get it installed.Can someone tell me what I am missing?Thanks in advance.

(Could not find stored procedure 'msdb.dbo.sp_dts_getfolder'. - sql 2005 - &gt; 2008 R2 Posted: 29 Aug 2011 09:12 AM PDT I have a package on 2008r2 and job on 2005 pointing to the package on 2008r2. When i run the job i get the following message 'Description: While trying to find a folder on SQL an OLE DB error was encountered with error code 0x80040E14 (Could not find stored procedure 'msdb.dbo.sp_dts_getfolder'.). Source"..Does anyone know how to fix this?

Accessing Linked Server using PowerPivot Posted: 04 Mar 2013 01:42 AM PST I am completely at a loss. Does anyone know the steps in connecting to a Linked server using Excel?Thanks

Question on Replication.. Posted: 04 Mar 2013 12:39 AM PST Can I setup replications between two disconnected networks..?? For example..my company is a Web Application Development company and they sell their products through out the country to different clients.. Clients often use own web server to host their applications.. Some times they use their own.. This is the process. So our new requirement is replicating our database to a client in differnt location of the country... We cannot give access to the client to use our main database. But in order to do replication the subscriber should be able to connect to the publisher??? I am confused. Can I set up replication between two disconnected servers.???

Searching for Solution Posted: 28 Feb 2013 04:26 PM PST Hello All, I have one query (it may sound stupid though). We have one SQL Server 2008 (Microsoft SQL Server 2008 (SP2) - 10.0.4266.0 (X64)) and on my machine I've installed the client tools. We have some process where we create soem bulky temp tables after doing some heavy processing on sybase and thereafter through a java code the data in those temp tables in Sybase are transferred to SQL batchwise. I think this is not safe and efficient strategy and somewhat time consuming and also feel that it should be handled using SSIS packages (?). Can anyone suggest on that ?Secondly, if creating SSIS package is good option then will I have to uninstall the SQL client on my machine and install the developer edition (with business studio) ? Is there license requirement (we have license for SQL Server Enterprise Edition) ?

SELECT list block with CASE? Posted: 03 Mar 2013 09:49 PM PST is something like this possible?SELECTCASE WHEN (@X = 1) THEN COLUMN1, COLUMN2, COLUMN3ELSE COLUMN4, COLUMN5, COLUMN6,ENDFROM TABLE1

An issue with my Execution plan Posted: 03 Mar 2013 09:27 PM PST I have a view. I made some changes (included few CASEs in SELECT list for a business requirement)i did a relative cost check between these old and new script with estimated and actual execution plans.it was 50-50%. so, i conclude that i didnt disturb my query performance.But, the perfo. went bad.so, where am i wrong.Help me pls.Appreciating your helps always.

You are subscribed to email updates from SQLServerCentral / SQL Server 2008 / SQL Server 2008 - General To stop receiving these emails, you may unsubscribe now.	Email delivery powered by Google
Google Inc., 20 West Kinzie, Chicago IL USA 60610