October 29, 2010 at 7:44 am
GSquared (10/29/2010)
To answer the original question, yes, wrapping a script into a UDF does usually cause a performance hit all by itself. But, it's usually in terms of about a 3-5% increase in runtime, not the huge increase you're running into. Does the UDF take as long to run the second time as it does the first time?
Interesting. I ran the code against some random data (5000 Agreements and 20000 djustments) and found the TVF ran 3.5 times faster :crazy:
Far away is close at hand in the images of elsewhere.
Anon.
October 29, 2010 at 8:19 am
I didn't have any data to run it against, but this is the kind of look you're shooting for.
CREATE PROCEDURE YourProcedureName
-- Add the parameters for the stored procedure here
@MonthDate date
@office int
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @PrevMonth date
--Simpler than a UDF
SET @MonthDate = dateadd(mm, datediff(mm, 0, @MonthDate), 0)
-- Don't repeatedly use dateadd, do it once here
SET @PrevMonth = dateadd(mm, datediff(mm, 0, @MonthDate) - 1, 0)
SELECT IsNull(C.AgrPKey, P.AgrPKey) as AgrPKey, ISNULL(C.Customer, P.Customer) as Customer,
P.PTotal, C.CTotal, ISNULL(C.PriorPeriod, P.PriorPeriod) as PriorPeriod, P.PTotal - C.CTotal as Variance
FROM
(SELECT FMonth.AgrPKey as AgrPKey, FMonth.Customer, FMonth.FTotal + FAdd.TotaPP as CTotal, 0 as PriorPeriod
FROM
(SELECT Agr.AgrPKey, RTRIM(LTRIM(Agr.AgrName)) as Customer, SUM(Adj.BiaAmount) AS FTotal
FROM BillBillingAgreement Agr
INNER JOIN BillBillingAdjustments Adj
ON Agr.BaiPKey = Adj.BaiPKey
WHERE Agr.BaiOffice = @office
AND Adj.BiaInvoiceApproved = 1
AND @MonthDate BETWEEN Adj.BiaStartDate and Adj.BiaEndDate
AND Adj.BiaBillingDate <= @MonthDate
GROUP BY Agr.AgrPKey, Agr.AgrName) FMonth
LEFT JOIN
(SELECT Agr.AgrPKey, SUM(
Case When @MonthDate <= BiaEndDate Then
(DateDiff(m,BiaStartDate,@MonthDate)) * Adj.BiaAmount
Else
(DateDiff(m,BiaEndDate,BiaStartDate)+1) * Adj.BiaAmount
end) AS TotaPP
FROM BillBillingAgreement Agr
INNER JOIN BillBillingAdjustments Adj
ON Agr.BaiPKey = Adj.BaiPKey
WHERE Adj.BiaBillingDate = @MonthDate
AND Adj.BiaInvoiceApproved = 1
AND Adj.BiaStartDate < Adj.BiaBillingDate
AND Agr.BaiOffice = @office
GROUP BY Agr.AgrPKey) FAdd
ON FMonth.AgrPKey = FAdd.AgrPKey) C
FULL OUTER JOIN
(SELECT SMonth.AgrPKey as AgrPKey, SMonth.Customer, SMonth.FTotal + SAdd.TotaPP as PTotal, 1 as PriorPeriod
FROM
(SELECT Agr.AgrPKey, RTRIM(LTRIM(Agr.AgrName)) as Customer, SUM(Adj.BiaAmount) AS FTotal
FROM BillBillingAgreement Agr
INNER JOIN BillBillingAdjustments Adj
ON Agr.BaiPKey = Adj.BaiPKey
WHERE Agr.BaiOffice = @office
AND Adj.BiaInvoiceApproved = 1
AND @PrevMonth BETWEEN Adj.BiaStartDate and Adj.BiaEndDate
AND Adj.BiaBillingDate <= @PrevMonth
GROUP BY Agr.AgrPKey, Agr.AgrName) SMonth
LEFT JOIN
(SELECT Agr.AgrPKey, SUM(
Case When @MonthDate <= BiaEndDate Then
(DateDiff(m,BiaStartDate,@PrevMonth)) * Adj.BiaAmount
Else
(DateDiff(m,BiaEndDate,BiaStartDate)+1) * Adj.BiaAmount
end) AS TotaPP
FROM BillBillingAgreement Agr
INNER JOIN BillBillingAdjustments Adj
ON Agr.BaiPKey = Adj.BaiPKey
WHERE Adj.BiaBillingDate = @PrevMonth
AND Adj.BiaInvoiceApproved = 1
AND Adj.BiaStartDate < Adj.BiaBillingDate
AND Agr.BaiOffice = @office
GROUP BY Agr.AgrPKey) SAdd
ON SMonth.AgrPKey = SAdd.AgrPKey) P
ON C.AgrPKey = P.AgrPKey
WHERE P.PTotal - C.CTotal <> 0
END
GO
Please forgive the aliasing, it makes it a lot easier for me to read the code.
I hope this helps or at least gives you a starting point for how to put together this kind of query without temp tables.
If I've gone horribly wrong here, please someone else point it out. I don't want to be steering him down the wrong path.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
October 29, 2010 at 8:49 am
I really think that the code can be much simpler. I'll wai for the OP to post the DDL for the underlying tables, sample data, and expected results.
Hopefully he will include data outside of the reporting range as well as data only in the current month or the previous month but not both as well as data in both months.
October 29, 2010 at 8:59 am
Lynn Pettis (10/29/2010)
I really think that the code can be much simpler. I'll wai for the OP to post the DDL for the underlying tables, sample data, and expected results.Hopefully he will include data outside of the reporting range as well as data only in the current month or the previous month but not both as well as data in both months.
I think it can be simpler too. I'm hoping to get rid of the FULL OUTER JOIN. What I posted is just a preliminary go through and already it is much simpler than the start.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
October 29, 2010 at 11:51 am
Ya i guess i really don't know the difference between a temp table and a variable table. I like using Variable Tables cause they are like a scratch pad. Is this not correct?
Here is some sample data and code. I hope this helps shed some light. Thanks for all your help. Also is there a good place to go or somewhere to get at least 20% of some of this base knowledge?
CREATE TABLE [dbo].[aa_BillBillingAgreement](
[BbaPkey] [int] IDENTITY(1,1),
[BaiPKey] [int] NOT NULL,
[AgrPKey] [int] NOT NULL,
[AgrName] [nvarchar](100) NOT NULL,
[BaiOffice] [nvarchar](50) NOT NULL
)
CREATE TABLE [dbo].[aa_BillBillingAdjustments](
[BbiPkey] [int] IDENTITY(1,1),
[BaiPKey] [int] NOT NULL,
[BiaAmount] [money] NOT NULL,
[BiaBillingDate] [datetime] NOT NULL,
[BiaStartDate] [datetime] NOT NULL,
[BiaEndDate] [datetime] NOT NULL,
[BiaInvoiceApproved] [bit] NOT NULL
)
Insert into aa_BillBillingAgreement
(BaiPKey,AgrPKey, AgrName, BaiOffice)
Values
(56077,1096,'Customer 76','Chicago'),
(59199,1753,'Customer 24','Chicago'),
(60448,1977,'Customer 80','Chicago'),
(66879,3370,'Customer 45','Chicago'),
(67561,3566,'Customer 100','Chicago'),
(67797,3695,'Customer 1','Chicago'),
(67951,3793,'Customer 31','Chicago'),
(67951,3793,'Customer 31','Chicago'),
(67951,3793,'Customer 31','Chicago'),
(68015,3829,'Customer 46','Chicago'),
(102116,3868,'Customer 56','Chicago'),
(68100,3900,'Customer 55','Chicago'),
(68100,3900,'Customer 55','Chicago'),
(68100,3900,'Customer 55','Chicago'),
(68251,4025,'Customer 87','Chicago'),
(68389,4191,'Customer 10','Chicago'),
(68389,4191,'Customer 10','Chicago'),
(68389,4191,'Customer 10','Chicago'),
(68389,4191,'Customer 10','Chicago'),
(68391,4192,'Customer 11','Chicago'),
(68391,4192,'Customer 11','Chicago'),
(68396,4205,'Customer 2','Chicago'),
(68396,4205,'Customer 2','Chicago'),
(105832,4224,'Customer 12','Chicago'),
(101625,4318,'Customer 25','Chicago'),
(106241,4333,'Customer 88','Chicago'),
(106125,4738,'Customer 4','Chicago'),
(106125,4738,'Customer 21','Chicago'),
(105485,4739,'Customer 3','Chicago'),
(105485,4739,'Customer 3','Chicago'),
(105485,4739,'Customer 3','Chicago'),
(106246,4783,'Customer 26','Chicago'),
(106170,5011,'Customer 19','Chicago'),
(106170,5011,'Customer 19','Chicago'),
(106262,5172,'Customer 57','Chicago'),
(106262,5172,'Customer 102','Chicago'),
(106278,5187,'Customer 66','Chicago'),
(106291,5249,'Customer 58','Chicago'),
(106376,5256,'Customer 85','Chicago'),
(106316,5291,'Customer 59','Chicago'),
(106327,5309,'Customer 60','Chicago'),
(106357,5349,'Customer 77','Chicago'),
(106357,5349,'Customer 77','Chicago'),
(106384,5408,'Customer 89','Chicago'),
(106386,5414,'Customer 83','Chicago'),
(106381,5418,'Customer 82','Chicago'),
(107236,5463,'Customer 93','Chicago'),
(106595,5597,'Customer 5','Chicago'),
(106595,5597,'Customer 5','Chicago'),
(106595,5597,'Customer 5','Chicago'),
(106629,5772,'Customer 17','Chicago'),
(107221,5821,'Customer 14','Chicago'),
(107221,5821,'Customer 14','Chicago'),
(106605,5939,'Customer 13','Chicago'),
(106746,6037,'Customer 62','Chicago'),
(106678,6116,'Customer 32','Chicago'),
(106714,6132,'Customer 61','Chicago'),
(106681,6149,'Customer 33','Chicago'),
(106681,6149,'Customer 33','Chicago'),
(106681,6149,'Customer 33','Chicago'),
(106681,6149,'Customer 33','Chicago'),
(106726,6156,'Customer 47','Chicago'),
(106726,6156,'Customer 47','Chicago'),
(106710,6186,'Customer 36','Chicago'),
(106710,6186,'Customer 36','Chicago'),
(106751,6259,'Customer 27','Chicago'),
(106751,6259,'Customer 27','Chicago'),
(106757,6307,'Customer 78','Chicago'),
(106763,6325,'Customer 63','Chicago'),
(106763,6325,'Customer 63','Chicago'),
(106854,6403,'Customer 90','Chicago'),
(106858,6502,'Customer 91','Chicago'),
(106865,6519,'Customer 92','Chicago'),
(107328,6609,'Customer 97','Chicago'),
(107122,7050,'Customer 28','Chicago'),
(107122,7050,'Customer 71','Chicago'),
(107170,7107,'Customer 34','Chicago'),
(107170,7107,'Customer 34','Chicago'),
(107170,7107,'Customer 34','Chicago'),
(107178,7150,'Customer 35','Chicago'),
(107185,7151,'Customer 38','Chicago'),
(107185,7151,'Customer 38','Chicago'),
(107185,7151,'Customer 38','Chicago'),
(107185,7151,'Customer 38','Chicago'),
(107185,7151,'Customer 38','Chicago'),
(107185,7151,'Customer 38','Chicago'),
(107185,7151,'Customer 38','Chicago'),
(107185,7151,'Customer 38','Chicago'),
(107195,7156,'Customer 64','Chicago'),
(107231,7176,'Customer 64','Chicago'),
(107247,7297,'Customer 94','Chicago'),
(107273,7321,'Customer 79','Chicago'),
(107304,7397,'Customer 95','Chicago'),
(107305,7405,'Customer 96','Chicago'),
(107501,7677,'Customer 7','Chicago'),
(107509,7755,'Customer 15','Chicago'),
(107542,7776,'Customer 8','Chicago'),
(107542,7776,'Customer 8','Chicago'),
(107563,7911,'Customer 18','Chicago'),
(107827,8152,'Customer 84','Chicago'),
(107827,8152,'Customer 84','Chicago'),
(107828,8227,'Customer 98','Chicago'),
(56077,1096,'Customer 1','3754'),
(59199,1753,'Customer 2','374'),
(60448,1977,'Customer 3','1476'),
(66341,3243,'Customer 4','514'),
(66879,3370,'Customer 5','644'),
(67452,3520,'Customer 6','1581'),
(67452,3520,'Customer 6','-1581'),
(67561,3566,'Customer 8','3581.5'),
(67720,3645,'Customer 9','5834'),
(67720,3645,'Customer 9','-5834'),
(67797,3695,'Customer 11','120'),
(67951,3793,'Customer 12','1559'),
(67951,3793,'Customer 12','117'),
(67951,3793,'Customer 12','-624'),
(68015,3829,'Customer 15','1261'),
(102116,3868,'Customer 16','2240'),
(68100,3900,'Customer 17','485'),
(68100,3900,'Customer 17','40'),
(68100,3900,'Customer 17','40'),
(68251,4025,'Customer 20','17'),
(68368,4144,'Customer 21','1122'),
(68368,4144,'Customer 21','237'),
(68368,4144,'Customer 21','96'),
(68368,4144,'Customer 21','158'),
(105928,4188,'Customer 25','140'),
(68389,4191,'Customer 26','644'),
(68389,4191,'Customer 26','305'),
(68389,4191,'Customer 26','124'),
(68389,4191,'Customer 26','35'),
(68391,4192,'Customer 30','685'),
(68391,4192,'Customer 30','-19'),
(68396,4205,'Customer 30','454'),
(68396,4205,'Customer 33','-98'),
(105832,4224,'Customer 34','89'),
(101625,4318,'Customer 35','1394'),
(106241,4333,'Customer 36','72'),
(106125,4738,'Customer 37','1406'),
(106125,4738,'Customer 37','-1406'),
(105485,4739,'Customer 39','3263'),
(105485,4739,'Customer 39','96'),
(105485,4739,'Customer 39','-259'),
(106171,4781,'Customer 42','1782.2'),
(106171,4781,'Customer 42','-1782.2'),
(106246,4783,'Customer 44','42'),
(106170,5011,'Customer 45','2380'),
(106170,5011,'Customer 45','-38'),
(106262,5172,'Customer 47','1116'),
(106278,5187,'Customer 48','177'),
(106291,5249,'Customer 49','745'),
(106376,5256,'Customer 50','283'),
(106316,5291,'Customer 51','975'),
(106327,5309,'Customer 52','499'),
(106357,5349,'Customer 53','6967.3'),
(106357,5349,'Customer 53','-6967.3'),
(106384,5408,'Customer 55','796'),
(106386,5414,'Customer 56','1056'),
(106381,5418,'Customer 57','2056'),
(107236,5463,'Customer 58','20'),
(106595,5597,'Customer 59','649'),
(106595,5597,'Customer 59','116'),
(106595,5597,'Customer 59','58'),
(106532,5710,'Customer 62','369'),
(106629,5772,'Customer 63','868'),
(107221,5821,'Customer 64','50'),
(107221,5821,'Customer 64','-50'),
(106605,5939,'Customer 66','320'),
(106746,6037,'Customer 67','400'),
(106678,6116,'Customer 68','1050'),
(106714,6132,'Customer 69','773'),
(106681,6149,'Customer 70','2031'),
(106681,6149,'Customer 70','75'),
(106681,6149,'Customer 70','30'),
(106681,6149,'Customer 70','52'),
(106726,6156,'Customer 74','32'),
(106726,6156,'Customer 74','0'),
(106710,6186,'Customer 76','647'),
(106710,6186,'Customer 76','-30'),
(106751,6259,'Customer 78','300'),
(106751,6259,'Customer 78','-300'),
(106757,6307,'Customer 80','223'),
(106763,6325,'Customer 81','210'),
(106763,6325,'Customer 81','-210'),
(106854,6403,'Customer 83','156'),
(106858,6502,'Customer 84','253'),
(106865,6519,'Customer 85','301'),
(107328,6609,'Customer 86','934'),
(107035,6835,'Customer 87','4264'),
(107035,6835,'Customer 88','-4264'),
(107122,7050,'Customer 89','542'),
(107122,7050,'Customer 89','37'),
(107170,7107,'Customer 91','375'),
(107170,7107,'Customer 91','39'),
(107170,7107,'Customer 91','59'),
(107178,7150,'Customer 94','3483'),
(107185,7151,'Customer 95','3271'),
(107185,7151,'Customer 95','123'),
(107185,7151,'Customer 95','123'),
(107185,7151,'Customer 95','-123'),
(107185,7151,'Customer 95','430'),
(107185,7151,'Customer 95','0'),
(107185,7151,'Customer 95','96'),
(107185,7151,'Customer 95','102'),
(107195,7156,'Customer 103','778'),
(107231,7176,'Customer 103','1395'),
(107247,7297,'Customer 105','496'),
(107273,7321,'Customer 106','3503'),
(107304,7397,'Customer 107','553'),
(107305,7405,'Customer 108','376'),
(107436,7631,'Customer 109','17667.15'),
(107436,7631,'Customer 109','-2503.25'),
(107436,7631,'Customer 109','510.15'),
(107436,7631,'Customer 109','1922.8'),
(107436,7631,'Customer 109','1563.7'),
(107436,7631,'Customer 109','5529'),
(107436,7631,'Customer 109','1283.45'),
(107436,7631,'Customer 109','1256.85'),
(107501,7677,'Customer 117','1807'),
(107509,7755,'Customer 118','32'),
(107542,7776,'Customer 119','903'),
(107542,7776,'Customer 119','-903'),
(107561,7831,'Customer 121','292'),
(107563,7911,'Customer 122','220'),
(107827,8152,'Customer 123','84'),
(107827,8152,'Customer 123','814'),
(107828,8227,'Customer 125','742.9')
insert into aa_BillBillingAdjustments
(BaiPKey,BiaAmount,BiaStartDate,BiaEndDate,BiaBillingDate,BiaInvoiceApproved)
Values
(56077,3754,'05/01/2010','04/01/2011','03/01/2010',1),
(59199,374,'01/01/2010','12/01/2010','11/01/2009',1),
(60448,1476,'05/01/2010','04/01/2011','04/01/2010',1),
(66879,644,'03/01/2010','02/01/2011','01/01/2010',1),
(67561,3581.5,'07/01/2010','06/01/2011','04/01/2010',1),
(67797,120,'11/01/2009','10/01/2010','09/01/2009',1),
(67951,1559,'02/01/2010','01/01/2011','12/01/2009',1),
(67951,117,'03/01/2010','01/01/2011','02/01/2010',1),
(67951,-624,'04/01/2010','01/01/2011','03/01/2010',1),
(68015,1261,'03/01/2010','02/01/2011','01/01/2010',1),
(102116,2240,'04/01/2010','03/01/2011','02/01/2010',1),
(68100,485,'04/01/2010','03/01/2011','02/01/2010',1),
(68100,40,'04/01/2010','03/01/2011','04/01/2010',1),
(68100,40,'05/01/2010','03/01/2011','03/01/2010',1),
(68251,17,'06/01/2010','05/01/2011','04/01/2010',1),
(68389,644,'12/01/2009','11/01/2010','10/01/2009',1),
(68389,305,'01/01/2010','11/01/2010','12/01/2009',1),
(68389,124,'04/01/2010','11/01/2010','01/01/2010',1),
(68389,35,'05/01/2010','11/01/2010','03/01/2010',1),
(68391,685,'12/01/2009','11/01/2010','10/01/2009',1),
(68391,-19,'12/01/2009','11/01/2010','11/01/2009',1),
(68396,454,'11/01/2009','10/01/2010','09/01/2009',1),
(68396,-98,'11/01/2009','10/01/2010','10/01/2009',1),
(105832,89,'12/01/2009','11/01/2010','10/01/2009',1),
(101625,1394,'01/01/2010','12/01/2010','11/01/2009',1),
(106241,72,'06/01/2010','05/01/2011','04/01/2010',1),
(106125,1406,'11/01/2009','10/01/2010','09/01/2009',1),
(106125,-1406,'01/01/2010','10/01/2010','11/01/2009',1),
(105485,3263,'11/01/2009','10/01/2010','09/01/2009',1),
(105485,96,'04/01/2010','10/01/2010','03/01/2010',1),
(105485,-259,'05/01/2010','10/01/2010','03/01/2010',1),
(106246,42,'01/01/2010','12/01/2010','11/01/2009',1),
(106170,2380,'12/01/2009','11/01/2010','12/01/2009',1),
(106170,-38,'01/01/2010','11/01/2010','12/01/2009',1),
(106262,1116,'04/01/2010','03/01/2011','02/01/2010',1),
(106262,-924,'10/01/2010','03/01/2011','04/01/2010',1),
(106278,177,'04/01/2010','03/01/2011','03/01/2010',1),
(106291,745,'04/01/2010','03/01/2011','02/01/2010',1),
(106376,283,'05/01/2010','04/01/2011','05/01/2010',1),
(106316,975,'04/01/2010','03/01/2011','02/01/2010',1),
(106327,499,'04/01/2010','03/01/2011','02/01/2010',1),
(106357,6967.3,'05/01/2010','04/01/2011','03/01/2010',1),
(106357,-6967.3,'05/01/2010','04/01/2011','04/01/2010',1),
(106384,796,'06/01/2010','05/01/2011','04/01/2010',1),
(106386,1056,'05/01/2010','04/01/2011','04/01/2010',1),
(106381,2056,'05/01/2010','04/01/2011','04/01/2010',1),
(107236,20,'06/01/2010','05/01/2011','04/01/2010',1),
(106595,649,'11/01/2009','10/01/2010','09/01/2009',1),
(106595,116,'02/01/2010','10/01/2010','01/01/2010',1),
(106595,58,'03/01/2010','10/01/2010','01/01/2010',1),
(106629,868,'12/01/2009','11/01/2010','11/01/2009',1),
(107221,50,'12/01/2009','11/01/2010','10/01/2009',1),
(107221,-50,'12/01/2009','11/01/2010','12/01/2009',1),
(106605,320,'12/01/2009','11/01/2010','10/01/2009',1),
(106746,400,'04/01/2010','03/01/2011','02/01/2010',1),
(106678,1050,'02/01/2010','01/01/2011','12/01/2009',1),
(106714,773,'04/01/2010','03/01/2011','02/01/2010',1),
(106681,2031,'02/01/2010','01/01/2011','12/01/2009',1),
(106681,75,'03/01/2010','01/01/2011','02/01/2010',1),
(106681,30,'04/01/2010','01/01/2011','03/01/2010',1),
(106681,52,'05/01/2010','01/01/2011','04/01/2010',1),
(106726,32,'03/01/2010','02/01/2011','01/01/2010',1),
(106726,0,'03/01/2010','02/01/2011','03/01/2010',1),
(106710,647,'02/01/2010','01/01/2011','01/01/2010',1),
(106710,-30,'05/01/2010','01/01/2011','04/01/2010',1),
(106751,300,'01/01/2010','12/01/2010','11/01/2009',1),
(106751,-300,'01/01/2010','12/01/2010','12/01/2009',1),
(106757,223,'05/01/2010','04/01/2011','03/01/2010',1),
(106763,210,'04/01/2010','03/01/2011','02/01/2010',1),
(106763,-210,'04/01/2010','03/01/2011','04/01/2010',1),
(106854,156,'06/01/2010','05/01/2011','04/01/2010',1),
(106858,253,'06/01/2010','05/01/2011','04/01/2010',1),
(106865,301,'06/01/2010','05/01/2011','04/01/2010',1),
(107328,934,'06/01/2010','05/01/2011','04/01/2010',1),
(107122,542,'01/01/2010','12/01/2010','11/01/2009',1),
(107122,37,'05/01/2010','12/01/2010','04/01/2010',1),
(107170,375,'02/01/2010','01/01/2011','12/01/2009',1),
(107170,39,'02/01/2010','01/01/2011','01/01/2010',1),
(107170,59,'08/01/2010','01/01/2011','05/01/2010',1),
(107178,3483,'02/01/2010','01/01/2011','12/01/2009',1),
(107185,3271,'02/01/2010','01/01/2011','01/01/2010',1),
(107185,123,'02/01/2010','01/01/2011','02/01/2010',1),
(107185,123,'03/01/2010','01/01/2011','01/01/2010',1),
(107185,-123,'03/01/2010','01/01/2011','02/01/2010',1),
(107185,430,'04/01/2010','01/01/2011','01/01/2010',1),
(107185,0,'04/01/2010','01/01/2011','02/01/2010',1),
(107185,96,'05/01/2010','01/01/2011','04/01/2010',1),
(107185,102,'06/01/2010','01/01/2011','05/01/2010',1),
(107195,778,'04/01/2010','03/01/2011','02/01/2010',1),
(107231,1395,'04/01/2010','03/01/2011','02/01/2010',1),
(107247,496,'06/01/2010','05/01/2011','04/01/2010',1),
(107273,3503,'05/01/2010','04/01/2011','03/01/2010',1),
(107304,553,'06/01/2010','05/01/2011','04/01/2010',1),
(107305,376,'06/01/2010','05/01/2011','04/01/2010',1),
(107501,1807,'11/01/2009','10/01/2010','10/01/2009',1),
(107509,32,'12/01/2009','11/01/2010','10/01/2009',1),
(107542,903,'12/01/2009','10/01/2010','11/01/2009',1),
(107542,-903,'12/01/2009','10/01/2010','12/01/2009',1),
(107563,220,'12/01/2009','11/01/2010','11/01/2009',1),
(107827,84,'05/01/2010','04/01/2011','04/01/2010',1),
(107827,814,'07/01/2010','04/01/2011','04/01/2010',1),
(107828,742.9,'06/01/2010','05/01/2011','04/01/2010',1),
(56077,3754,'05/01/2010','04/01/2011','03/01/2010',1),
(59199,374,'01/01/2010','12/01/2010','11/01/2009',1),
(60448,1476,'05/01/2010','04/01/2011','04/01/2010',1),
(66879,644,'03/01/2010','02/01/2011','01/01/2010',1),
(67561,3581.5,'07/01/2010','06/01/2011','04/01/2010',1),
(67797,120,'11/01/2009','10/01/2010','09/01/2009',1),
(67951,1559,'02/01/2010','01/01/2011','12/01/2009',1),
(67951,117,'03/01/2010','01/01/2011','02/01/2010',1),
(67951,-624,'04/01/2010','01/01/2011','03/01/2010',1),
(68015,1261,'03/01/2010','02/01/2011','01/01/2010',1),
(102116,2240,'04/01/2010','03/01/2011','02/01/2010',1),
(68100,485,'04/01/2010','03/01/2011','02/01/2010',1),
(68100,40,'04/01/2010','03/01/2011','04/01/2010',1),
(68100,40,'05/01/2010','03/01/2011','03/01/2010',1),
(68251,17,'06/01/2010','05/01/2011','04/01/2010',1),
(68389,644,'12/01/2009','11/01/2010','10/01/2009',1),
(68389,305,'01/01/2010','11/01/2010','12/01/2009',1),
(68389,124,'04/01/2010','11/01/2010','01/01/2010',1),
(68389,35,'05/01/2010','11/01/2010','03/01/2010',1),
(68391,685,'12/01/2009','11/01/2010','10/01/2009',1),
(68391,-19,'12/01/2009','11/01/2010','11/01/2009',1),
(68396,454,'11/01/2009','10/01/2010','09/01/2009',1),
(68396,-98,'11/01/2009','10/01/2010','10/01/2009',1),
(105832,89,'12/01/2009','11/01/2010','10/01/2009',1),
(101625,1394,'01/01/2010','12/01/2010','11/01/2009',1),
(106241,72,'06/01/2010','05/01/2011','04/01/2010',1),
(106125,1406,'11/01/2009','10/01/2010','09/01/2009',1),
(106125,-1406,'01/01/2010','10/01/2010','11/01/2009',1),
(105485,3263,'11/01/2009','10/01/2010','09/01/2009',1),
(105485,96,'04/01/2010','10/01/2010','03/01/2010',1),
(105485,-259,'05/01/2010','10/01/2010','03/01/2010',1),
(106246,42,'01/01/2010','12/01/2010','11/01/2009',1),
(106170,2380,'12/01/2009','11/01/2010','12/01/2009',1),
(106170,-38,'01/01/2010','11/01/2010','12/01/2009',1),
(106262,1116,'04/01/2010','03/01/2011','02/01/2010',1),
(106262,-924,'10/01/2010','03/01/2011','04/01/2010',1),
(106278,177,'04/01/2010','03/01/2011','03/01/2010',1),
(106291,745,'04/01/2010','03/01/2011','02/01/2010',1),
(106376,283,'05/01/2010','04/01/2011','05/01/2010',1),
(106316,975,'04/01/2010','03/01/2011','02/01/2010',1),
(106327,499,'04/01/2010','03/01/2011','02/01/2010',1),
(106357,6967.3,'05/01/2010','04/01/2011','03/01/2010',1),
(106357,-6967.3,'05/01/2010','04/01/2011','04/01/2010',1),
(106384,796,'06/01/2010','05/01/2011','04/01/2010',1),
(106386,1056,'05/01/2010','04/01/2011','04/01/2010',1),
(106381,2056,'05/01/2010','04/01/2011','04/01/2010',1),
(107236,20,'06/01/2010','05/01/2011','04/01/2010',1),
(106595,649,'11/01/2009','10/01/2010','09/01/2009',1),
(106595,116,'02/01/2010','10/01/2010','01/01/2010',1),
(106595,58,'03/01/2010','10/01/2010','01/01/2010',1),
(106629,868,'12/01/2009','11/01/2010','11/01/2009',1),
(107221,50,'12/01/2009','11/01/2010','10/01/2009',1),
(107221,-50,'12/01/2009','11/01/2010','12/01/2009',1),
(106605,320,'12/01/2009','11/01/2010','10/01/2009',1),
(106746,400,'04/01/2010','03/01/2011','02/01/2010',1),
(106678,1050,'02/01/2010','01/01/2011','12/01/2009',1),
(106714,773,'04/01/2010','03/01/2011','02/01/2010',1),
(106681,2031,'02/01/2010','01/01/2011','12/01/2009',1),
(106681,75,'03/01/2010','01/01/2011','02/01/2010',1),
(106681,30,'04/01/2010','01/01/2011','03/01/2010',1),
(106681,52,'05/01/2010','01/01/2011','04/01/2010',1),
(106726,32,'03/01/2010','02/01/2011','01/01/2010',1),
(106726,0,'03/01/2010','02/01/2011','03/01/2010',1),
(106710,647,'02/01/2010','01/01/2011','01/01/2010',1),
(106710,-30,'05/01/2010','01/01/2011','04/01/2010',1),
(106751,300,'01/01/2010','12/01/2010','11/01/2009',1),
(106751,-300,'01/01/2010','12/01/2010','12/01/2009',1),
(106757,223,'05/01/2010','04/01/2011','03/01/2010',1),
(106763,210,'04/01/2010','03/01/2011','02/01/2010',1),
(106763,-210,'04/01/2010','03/01/2011','04/01/2010',1),
(106854,156,'06/01/2010','05/01/2011','04/01/2010',1),
(106858,253,'06/01/2010','05/01/2011','04/01/2010',1),
(106865,301,'06/01/2010','05/01/2011','04/01/2010',1),
(107328,934,'06/01/2010','05/01/2011','04/01/2010',1),
(107122,542,'01/01/2010','12/01/2010','11/01/2009',1),
(107122,37,'05/01/2010','12/01/2010','04/01/2010',1),
(107170,375,'02/01/2010','01/01/2011','12/01/2009',1),
(107170,39,'02/01/2010','01/01/2011','01/01/2010',1),
(107170,59,'08/01/2010','01/01/2011','05/01/2010',1),
(107178,3483,'02/01/2010','01/01/2011','12/01/2009',1),
(107185,3271,'02/01/2010','01/01/2011','01/01/2010',1),
(107185,123,'02/01/2010','01/01/2011','02/01/2010',1),
(107185,123,'03/01/2010','01/01/2011','01/01/2010',1),
(107185,-123,'03/01/2010','01/01/2011','02/01/2010',1),
(107185,430,'04/01/2010','01/01/2011','01/01/2010',1),
(107185,0,'04/01/2010','01/01/2011','02/01/2010',1),
(107185,96,'05/01/2010','01/01/2011','04/01/2010',1),
(107185,102,'06/01/2010','01/01/2011','05/01/2010',1),
(107195,778,'04/01/2010','03/01/2011','02/01/2010',1),
(107231,1395,'04/01/2010','03/01/2011','02/01/2010',1),
(107247,496,'06/01/2010','05/01/2011','04/01/2010',1),
(107273,3503,'05/01/2010','04/01/2011','03/01/2010',1),
(107304,553,'06/01/2010','05/01/2011','04/01/2010',1),
(107305,376,'06/01/2010','05/01/2011','04/01/2010',1),
(107501,1807,'11/01/2009','10/01/2010','10/01/2009',1),
(107509,32,'12/01/2009','11/01/2010','10/01/2009',1),
(107542,903,'12/01/2009','10/01/2010','11/01/2009',1),
(107542,-903,'12/01/2009','10/01/2010','12/01/2009',1),
(107563,220,'12/01/2009','11/01/2010','11/01/2009',1),
(107827,84,'05/01/2010','04/01/2011','04/01/2010',1),
(107827,814,'07/01/2010','04/01/2011','04/01/2010',1),
(107828,742.9,'06/01/2010','05/01/2011','04/01/2010',1)
here is the T-SQL that I used in the TVF or SP
Declare @MonthDate date
Declare @office varchar(100)
Declare @PreviousMonth date
Set @MonthDate='10/01/2010'
Set @PreviousMonth=DATEADD(m,-1,@MonthDate)
Set @office='Chicago'
Declare @TempTable Table
(
AgrPkey int,
CurrentMonthAmount money,
PreviousMonthAmount money
)
Insert into
@TempTable
(AgrPkey,CurrentMonthAmount, PreviousMonthAmount)
SELECT BillBillingAgreement.AgrPKey, SUM(BillBillingAdjustments.BiaAmount) AS Total,0
FROM aa_BillBillingAgreement as BillBillingAgreement INNER JOIN
aa_BillBillingAdjustments as BillBillingAdjustments ON BillBillingAgreement.BaiPKey = BillBillingAdjustments.BaiPKey
WHERE (BillBillingAgreement.BaiOffice = @office) AND (BillBillingAdjustments.BiaInvoiceApproved = 1) AND (BillBillingAdjustments.BiaStartDate <=@MonthDate)
and (BillBillingAdjustments.BiaEndDate >= @MonthDate) AND (BillBillingAdjustments.BiaBillingDate <=@MonthDate)
GROUP BY BillBillingAgreement.AgrPKey
--Add Prior Periods
Merge into @TempTable as target
using(
SELECT BillBillingAgreement.AgrPKey, SUM(
Case When @MonthDate<=BiaEndDate Then
(DateDiff(m,BiaStartDate,@MonthDate))*BillBillingAdjustments.BiaAmount
Else
(DateDiff(m,BiaEndDate,BiaStartDate)+1)*BillBillingAdjustments.BiaAmount
end) AS TotaPP
FROM aa_BillBillingAgreement as BillBillingAgreement INNER JOIN aa_BillBillingAdjustments as BillBillingAdjustments ON BillBillingAgreement.BaiPKey = BillBillingAdjustments.BaiPKey
WHERE (BillBillingAdjustments.BiaBillingDate = @MonthDate) AND (BillBillingAdjustments.BiaInvoiceApproved = 1) AND
(BillBillingAdjustments.BiaStartDate < BillBillingAdjustments.BiaBillingDate) and (BillBillingAgreement.BaiOffice = @office)
GROUP BY BillBillingAgreement.AgrPKey
) as source
on target.AgrPkey=source.AgrPkey
when matched then
update set target.CurrentMonthAmount=target.CurrentMonthAmount+source.TotaPP
;
Declare @TempTable2 Table
(
AgrPkey int,
CurrentMonthAmount money,
NextMonthAmount money
)
Insert into
@TempTable2
(AgrPkey,CurrentMonthAmount,NextMonthAmount)
SELECT BillBillingAgreement.AgrPKey, SUM(BillBillingAdjustments.BiaAmount) AS Total,0
FROM aa_BillBillingAgreement as BillBillingAgreement INNER JOIN
aa_BillBillingAdjustments as BillBillingAdjustments ON BillBillingAgreement.BaiPKey = BillBillingAdjustments.BaiPKey
WHERE (BillBillingAgreement.BaiOffice = @office) AND (BillBillingAdjustments.BiaInvoiceApproved = 1) AND (BillBillingAdjustments.BiaStartDate <=@PreviousMonth)
and (BillBillingAdjustments.BiaEndDate >= @PreviousMonth) AND (BillBillingAdjustments.BiaBillingDate <=@PreviousMonth)
GROUP BY BillBillingAgreement.AgrPKey
--Add Prior Periods
Merge into @TempTable2 as target
using(
SELECT BillBillingAgreement.AgrPKey, SUM(
Case When @PreviousMonth<=BiaEndDate Then
(DateDiff(m,BiaStartDate,@PreviousMonth))*BillBillingAdjustments.BiaAmount
Else
(DateDiff(m,BiaEndDate,BiaStartDate)+1)*BillBillingAdjustments.BiaAmount
end) AS TotaPP
FROM aa_BillBillingAgreement as BillBillingAgreement INNER JOIN aa_BillBillingAdjustments as BillBillingAdjustments ON BillBillingAgreement.BaiPKey = BillBillingAdjustments.BaiPKey
WHERE (BillBillingAdjustments.BiaBillingDate = @PreviousMonth) AND (BillBillingAdjustments.BiaInvoiceApproved = 1) AND
(BillBillingAdjustments.BiaStartDate < BillBillingAdjustments.BiaBillingDate) and (BillBillingAgreement.BaiOffice = @office)
GROUP BY BillBillingAgreement.AgrPKey
) as source
on target.AgrPkey=source.AgrPkey
when matched then
update set target.CurrentMonthAmount=target.CurrentMonthAmount+source.TotaPP
;
merge into @TempTable as Target
using (select AgrPkey,CurrentMonthAmount,NextMonthAmount from @TempTable2) as source
on target.AgrPkey=source.AgrPkey
When matched then
update set target.PreviousMonthAmount=source.CurrentMonthAmount
when not matched by target then
insert (AgrPkey,CurrentMonthAmount, PreviousMonthAmount)
values(source.AgrPkey, 0,source.CurrentMonthAmount)
;
select AgrPkey,PreviousMonthAmount,CurrentMonthAmount
, (CurrentMonthAmount-PreviousMonthAmount) as Variance
from @TempTable
order by (CurrentMonthAmount-PreviousMonthAmount)
result
AgrPkeyPreviousMonthAmountCurrentMonthAmountVariance
51724464.00768.00-3696.00
5187354.00354.000.00
52491490.001490.000.00
5256566.00566.000.00
52911950.001950.000.00
5309998.00998.000.00
53490.000.000.00
54081592.001592.000.00
54142112.002112.000.00
54184112.004112.000.00
546340.0040.000.00
55974938.004938.000.00
57721736.001736.000.00
58210.000.000.00
5939640.00640.000.00
6037800.00800.000.00
61162100.002100.000.00
61321546.001546.000.00
614917504.0017504.000.00
6156128.00128.000.00
61862468.002468.000.00
62590.000.000.00
6307446.00446.000.00
63250.000.000.00
6403312.00312.000.00
6502506.00506.000.00
6519602.00602.000.00
66091868.001868.000.00
70502316.002316.000.00
71072838.002838.000.00
71506966.006966.000.00
715164352.0064352.000.00
71561556.001556.000.00
71762790.002790.000.00
7297992.00992.000.00
73217006.007006.000.00
73971106.001106.000.00
7405752.00752.000.00
76773614.003614.000.00
775564.0064.000.00
77760.000.000.00
7911440.00440.000.00
81523592.003592.000.00
82271485.801485.800.00
10967508.007508.000.00
1753748.00748.000.00
19772952.002952.000.00
33701288.001288.000.00
35667163.007163.000.00
3695240.00240.000.00
37936312.006312.000.00
38292522.002522.000.00
38684480.004480.000.00
39003390.003390.000.00
402534.0034.000.00
41918864.008864.000.00
41922664.002664.000.00
42051424.001424.000.00
4224178.00178.000.00
43182788.002788.000.00
4333144.00144.000.00
47380.000.000.00
473918600.0018600.000.00
478384.0084.000.00
50119368.009368.000.00
October 29, 2010 at 12:00 pm
mbender (10/29/2010)
Ya i guess i really don't know the difference between a temp table and a variable table. I like using Variable Tables cause they are like a scratch pad. Is this not correct?Here is some sample data and code. I hope this helps shed some light. Thanks for all your help. Also is there a good place to go or somewhere to get at least 20% of some of this base knowledge?
There are a few differences between temp tables and table variables, but I'd concentrate on not using either right now. You shouldn't need them for this query and the differences between them might not make much sense to you yet.
As for where to go to get the beginnings, I'd suggest starting with books. Look for books that are either "Beginning T-SQL", "Beginning SQL Server" or "SQL Server for Programmers". I like the WROX series, like the one in the link, but there are many good series out there.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
October 29, 2010 at 7:02 pm
mbender (10/29/2010)
Ya i guess i really don't know the difference between a temp table and a variable table.
I'd suggest this article that I wrote: Comparing Table Variables to Temporary Tables[/url]
BTW, excellent job of posting your DDL/DML - Thanks! (Now to start working on it)
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
October 29, 2010 at 7:08 pm
First of all, I'd recommend these two indexes (your code benefits from these also!):
CREATE INDEX [Cover1] ON [dbo].[aa_BillBillingAgreement](BaiOffice) INCLUDE (BaiPKey, AgrPKey);
CREATE INDEX [Cover1] ON [dbo].[aa_BillBillingAdjustments](BiaBillingDate, BiaInvoiceApproved, BiaStartDate) INCLUDE (BaiPKey, BiaEndDate, BiaAmount);
This code produces the same results, with no temp tables (or table variables). Doing this saves a bit of IO - in fact, when running both your query and this one together, this one is 1/3 of the total cost.
Oh - one other thing. The @office variable - I've changed it from varchar(100) to nvarchar(50) to match the datatype of the table (this will avoid an implicit conversion to nvarchar - and allow the use of an index on that column).
SET STATISTICS IO,TIME ON;
Declare @MonthDate date
Declare @office nvarchar(50)
Declare @PreviousMonth date
Set @MonthDate='10/01/2010'
Set @PreviousMonth=DATEADD(m,-1,@MonthDate)
Set @office=N'Chicago'
WITH CurrentMonth (AgrPKey, CurrentMonthAmount) AS
(
SELECT AgrPKey, SUM(Total)
FROM (
SELECT BillBillingAgreement.AgrPKey,
SUM(BillBillingAdjustments.BiaAmount) AS Total
FROM aa_BillBillingAgreement as BillBillingAgreement
JOIN aa_BillBillingAdjustments as BillBillingAdjustments
ON BillBillingAgreement.BaiPKey = BillBillingAdjustments.BaiPKey
WHERE (BillBillingAgreement.BaiOffice = @office)
AND (BillBillingAdjustments.BiaInvoiceApproved = 1)
AND (BillBillingAdjustments.BiaStartDate <=@MonthDate)
AND (BillBillingAdjustments.BiaEndDate >= @MonthDate)
AND (BillBillingAdjustments.BiaBillingDate <=@MonthDate)
GROUP BY BillBillingAgreement.AgrPKey
UNION ALL
SELECT BillBillingAgreement.AgrPKey,
SUM(Case When @MonthDate<=BiaEndDate Then (DateDiff(m,BiaStartDate,@MonthDate))*BillBillingAdjustments.BiaAmount
Else (DateDiff(m,BiaEndDate,BiaStartDate)+1)*BillBillingAdjustments.BiaAmount
end) AS TotaPP
FROM aa_BillBillingAgreement as BillBillingAgreement
JOIN aa_BillBillingAdjustments as BillBillingAdjustments
ON BillBillingAgreement.BaiPKey = BillBillingAdjustments.BaiPKey
WHERE (BillBillingAdjustments.BiaBillingDate = @MonthDate)
AND (BillBillingAdjustments.BiaInvoiceApproved = 1)
AND (BillBillingAdjustments.BiaStartDate < BillBillingAdjustments.BiaBillingDate)
AND (BillBillingAgreement.BaiOffice = @office)
GROUP BY BillBillingAgreement.AgrPKey) ds
GROUP BY AgrPKey
), PreviousMonth (AgrPKey, CurrentMonthAmount) AS
(
SELECT AgrPKey, SUM(Total)
FROM (
SELECT BillBillingAgreement.AgrPKey,
SUM(BillBillingAdjustments.BiaAmount) AS Total
FROM aa_BillBillingAgreement as BillBillingAgreement
JOIN aa_BillBillingAdjustments as BillBillingAdjustments
ON BillBillingAgreement.BaiPKey = BillBillingAdjustments.BaiPKey
WHERE (BillBillingAgreement.BaiOffice = @office)
AND (BillBillingAdjustments.BiaInvoiceApproved = 1)
AND (BillBillingAdjustments.BiaStartDate <=@PreviousMonth)
AND (BillBillingAdjustments.BiaEndDate >= @PreviousMonth)
AND (BillBillingAdjustments.BiaBillingDate <=@PreviousMonth)
GROUP BY BillBillingAgreement.AgrPKey
UNION ALL
SELECT BillBillingAgreement.AgrPKey,
SUM(Case When @PreviousMonth<=BiaEndDate Then (DateDiff(m,BiaStartDate,@PreviousMonth))*BillBillingAdjustments.BiaAmount
Else (DateDiff(m,BiaEndDate,BiaStartDate)+1)*BillBillingAdjustments.BiaAmount
end) AS TotaPP
FROM aa_BillBillingAgreement as BillBillingAgreement
JOIN aa_BillBillingAdjustments as BillBillingAdjustments
ON BillBillingAgreement.BaiPKey = BillBillingAdjustments.BaiPKey
WHERE (BillBillingAdjustments.BiaBillingDate = @PreviousMonth)
AND (BillBillingAdjustments.BiaInvoiceApproved = 1)
AND (BillBillingAdjustments.BiaStartDate < BillBillingAdjustments.BiaBillingDate)
AND (BillBillingAgreement.BaiOffice = @office)
GROUP BY BillBillingAgreement.AgrPKey) ds
GROUP BY AgrPKey
), Merged AS
(
SELECT pm.AgrPKey,
PreviousMonthAmount = pm.CurrentMonthAmount,
CurrentMonthAmount = IsNull(cm.CurrentMonthAmount,0)
FROM PreviousMonth pm
LEFT JOIN CurrentMonth cm
ON cm.AgrPKey = pm.AgrPKey
)
SELECT AgrPKey,
PreviousMonthAmount,
CurrentMonthAmount,
Variance = (CurrentMonthAmount - PreviousMonthAmount)
FROM Merged
ORDER BY Variance;
SET STATISTICS IO,TIME OFF;
It won't surprise me if someone can tweak this a bit further - but it's already running quite a bit better.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
November 1, 2010 at 10:18 am
awesome I will take a look at this. Thank you very much, this is exactly why i come to this site.
Thanks for all your help!
November 1, 2010 at 10:48 am
mbender (11/1/2010)
awesome I will take a look at this. Thank you very much, this is exactly why i come to this site.Thanks for all your help!
I'm glad that I could help. Post back if you don't understand anything.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
November 3, 2010 at 8:17 am
What does indexing do? None of our tables are indexed. By doing the Create Index, what will that do?
November 3, 2010 at 8:53 am
this is really cool code, would never have thought to do this. The one thing i'm missing is what happens if there is values in the previous month but not in the current month. I still need to show the value from the previous month. This doesn't show it.
November 3, 2010 at 8:57 am
mbender (11/3/2010)
this is really cool code, would never have thought to do this. The one thing i'm missing is what happens if there is values in the previous month but not in the current month. I still need to show the value from the previous month. This doesn't show it.
Who are you responding to?
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
November 3, 2010 at 9:00 am
mbender (11/3/2010)
What does indexing do? None of our tables are indexed. By doing the Create Index, what will that do?
Indexing helps improve the performance of queries on your tables. It organizes the data in such a way that SQL Server has an easier time finding what you're looking for.
There are quite a few articles on-line about what Indexing does, this is one, but if you search the web for SQL Server Indexing, you should find quite a bit of information.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
November 3, 2010 at 9:04 am
Also what is the difference between the IsNull and Coalesce?
Viewing 15 posts - 31 through 45 (of 52 total)
You must be logged in to reply to this topic. Login to reply