|
|
Resolution to SQL Server problems. From the front line.
-
You often want to create a Numbers (sometimes called) Tally Table in SQL Server, to help with Set Based calculations.
To see what a Numbers table is read:
http://www.sqlservercentral.com/articles/T-SQL/62867/
Prior to SQL Server 2008 I have also used a Dervived UNION ALL Statement to create a simple Numbers table at run time, along the lines of
SELECT 1 AS Number
UNION ALL
SELECT 2 AS Number
UNION ALL
SELECT 3 AS Number
UNION ALL
SELECT 4 AS Number
UNION ALL
SELECT 5 AS Number;
However, there is an easier way of doing this now... using the new VALUES Clause in SQL Server 2008, This does NOT have to be used simply for Inserting multiple rows into a table, without writing the INSERT Statement loits of times, you can use it for other T-SQL uses.. like:
You can simply write:
SELECT Number FROM (VALUES(1),(2),(3),(4),(5)) AS D (Numbers);
I don't know the performance impact of this, but it is another way of doing this...
|
-
Sometimes you need to create some 'permanent' data in a View (rather than a Table), to be used for coding purposes. This is often used to 'get around' a problem due to lack of data or time to create a table that might only be used for a small period of time.. On tmy current project we have a number of 'look up Views' for data we may need to change quickly before we go live. An example of how you woudl noirmally do this is shown below.. This can be quite tiresome to code if you have 2000 rows rather than 20..
CREATE VIEW PremershipClubs20092010 AS
SELECT 'Arsenal' AS ClubName, 1 AS NameOrder UNION ALL
SELECT 'Aston Villa', 2 UNION ALL
SELECT 'Birmingham City', 3 UNION ALL
SELECT 'Blackburn Rovers', 4 UNION ALL
SELECT 'Bolton Wanderers', 5 UNION ALL
SELECT 'Burnley', 6 UNION ALL
SELECT 'Chelsea', 7 UNION ALL
SELECT 'Everton', 8 UNION ALL
SELECT 'Fulham', 9 UNION ALL
SELECT 'Hull City', 10 UNION ALL
SELECT 'Liverpool', 11 UNION ALL
SELECT 'Manchester City', 12 UNION ALL
SELECT 'Manchester United', 13 UNION ALL
SELECT 'Portsmouth', 14 UNION ALL
SELECT 'Stoke City', 15 UNION ALL
SELECT 'Sunderland', 16 UNION ALL
SELECT 'Tottenham Hotspur', 17 UNION ALL
SELECT 'West Ham United', 18 UNION ALL
SELECT 'Wigan Athletic', 19 UNION ALL
SELECT 'Wolverhampton Wanderers', 20;
SELECT * FROM PremershipClubs20092010;
How much easier, quicker and simplier is it to maintain the following (if you have add and remove teams, and re-order the daatfor 2010 /2011 Season) ? Again using the VALUES Clause, this is slightly less code!!..
CREATE VIEW PremershipClubs20092010_New
AS
SELECT ClubName, NameOrder FROM (VALUES
( 'Arsenal', 1),
( 'Aston Villa', 2),
( 'Birmingham City', 3),
( 'Blackburn Rovers', 4),
( 'Bolton Wanderers', 5),
( 'Burnley', 6),
( 'Chelsea', 7),
( 'Everton', 8),
( 'Fulham', 9),
( 'Hull City', 10),
( 'Liverpool', 11),
( 'Manchester City', 12),
( 'Manchester United', 13),
( 'Portsmouth', 14),
( 'Stoke City', 15),
( 'Sunderland', 16),
( 'Tottenham Hotspur', 17),
( 'West Ham United', 18),
( 'Wigan Athletic', 19),
( 'Wolverhampton Wanderers', 20)) AS D (ClubName, NameOrder);
SELECT * FROM PremershipClubs20092010_New;
Again I do not know performance impacts?
|
-
Had the following error today
Error 42 SQL03006: Column: [General].[Check_Transform].[A_COLUMN_NAME] contains an unresolved reference to an object. Either the object does not exist or the reference is ambiguous because it could refer to any of the following objects: [INFORMATION_SCHEMA].[COLUMNS]. ::[COLUMN_NAME] or [INFORMATION_SCHEMA].[COLUMNS].[COLUMN_NAME].
Same on 7 other rows..
Googled the life out of it no luck :-(
The query was this
CREATE VIEW TEST
AS
A .TABLE_SCHEMA +'.' + A.TABLE_NAME + '<->' + B.TABLE_SCHEMA + '.' + B.TABLE_NAME AS Pair,
A.COLUMN_NAME AS A_COLUMN_NAME,
B.COLUMN_NAME AS B_COLUMN_NAME,
A.DATA_TYPE AS A_DATA_TYPE,
B.DATA_TYPE AS B_DATA_TYPE,
A.COLLATION_NAME AS A_COLLATION_NAME,
B.COLLATION_NAME AS B_COLLATION_NAME,
A.CHARACTER_OCTET_LENGTH AS A_CHARACTER_OCTET_LENGTH,
B.CHARACTER_OCTET_LENGTH AS B_CHARACTER_OCTET_LENGTH
FROM INFORMATION_SCHEMA.COLUMNS A
INNER JOIN INFORMATION_SCHEMA.COLUMNS B
ON A.TABLE_SCHEMA = B.TABLE_SCHEMA
AND A.ORDINAL_POSITION = B.ORDINAL_POSITION
WHERE A.TABLE_SCHEMA = @TABLE_SCHEMA
AND A.TABLE_NAME = @A_TABLE_NAME
AND B.TABLE_NAME = @B_TABLE_NAME
AND (
A.COLUMN_NAME <> B.COLUMN_NAME
OR A.DATA_TYPE <> B.DATA_TYPE
OR A.COLLATION_NAME <> B.COLLATION_NAME
OR A.CHARACTER_OCTET_LENGTH<> B.CHARACTER_OCTET_LENGTH
);
I noticed we didn't get anything on first line which had some sort of function applied to the column (it passed). There must be a bug in VS 2010 RC. Solved it by adding 'false function'
SELECT A.TABLE_SCHEMA +'.' + A.TABLE_NAME + '<->' + B.TABLE_SCHEMA + '.' + B.TABLE_NAME AS Pair,
LTRIM(A.COLUMN_NAME) AS A_COLUMN_NAME, -- Does not change result
LTRIM(B.COLUMN_NAME) AS B_COLUMN_NAME,
LTRIM(A.DATA_TYPE) AS A_DATA_TYPE,
LTRIM(B.DATA_TYPE) AS B_DATA_TYPE,
LTRIM(A.COLLATION_NAME) AS A_COLLATION_NAME,
LTRIM(B.COLLATION_NAME) AS B_COLLATION_NAME,
0 + A.CHARACTER_OCTET_LENGTH AS A_CHARACTER_OCTET_LENGTH, -- Does not change result (Null still a Null)
0 + B.CHARACTER_OCTET_LENGTH AS B_CHARACTER_OCTET_LENGTH
FROM INFORMATION_SCHEMA.COLUMNS A
INNER JOIN INFORMATION_SCHEMA.COLUMNS B
ON A.TABLE_SCHEMA = B.TABLE_SCHEMA
AND A.ORDINAL_POSITION = B.ORDINAL_POSITION
WHERE A.TABLE_SCHEMA = @TABLE_SCHEMA
AND A.TABLE_NAME = @A_TABLE_NAME
AND B.TABLE_NAME = @B_TABLE_NAME
AND (
A.COLUMN_NAME <> B.COLUMN_NAME
OR A.DATA_TYPE <> B.DATA_TYPE
OR A.COLLATION_NAME <> B.COLLATION_NAME
OR A.CHARACTER_OCTET_LENGTH<> B.CHARACTER_OCTET_LENGTH
);
And it passed Build in VS 2010 OK.. Hope it helps someone else!
|
-
Well it's 2008 so I better post another Blog entry. Just a quick note though as after tearing my hair out for best part of a day, I have found an interesting 'feature' with BIT Fields in SQL Server 2005 and a SSIS Data Flow OLE-DB Source (though I suspect it occurs elsewhere within SSIS and maybe SSMS, and the behaviour did not occur when I ran something interactively, it only occurred when the SSIS Package was executed as Job i.e a real **&^&%$ to find Basically, OLE_DB Source query was doing the following in a derived table against a SQL Server 2005 database SELECT Column FROM dbo.Table WHERE ColumnFlag = 1; The ColumnFlag was defined as a BIT field on a Table. However, when I ran the code via a Job I was finding it effectively ignored the WHERE ColumnFlag = 1 filter. I wondered if this was because in SQL Server 2005 Microsoft have changed the behaviour of BIT fields and introduced the new keywords 'TRUE' and 'FALSE' - Sure enough, as soon as I changed the code to be: SELECT Column FROM dbo.Table WHERE ColumnFlag = 'TRUE'; It worked as I wanted. So I have decided that whenever I use BIT Fields in SQL Server 2005 I will now use 'TRUE' and 'FALSE' - Not 1 and 0, another convention change I will just have to get use to until it becomes second nature Like ANSI 92 and Statement Termination; Note: 'TRUE' and 'FALSE' will n ot work against a SQL Server 2000 database, you will need to use the old convention
|
-
We have the following in SQL Server 2000
27 Partition Fact Tables containing Sales Data: Supplier, Product, Store and Day held in a Union All View SupplierProductStoreDay (over 1.35 Billion records)
We have another table that says ‘ProductPromotion’ which has a Start Date and End Date Promotion Period, when a Product is on Promotion
We require a Flag (ProductSaleWithinPeriodFlag that answers the question, this Product is on a Promotion, but did it have any sales YES/NO (1/0) on any Date during the Promotion Period
So the question that is needed to be answered is ‘Did we sell this Product in any Store during the Period?’ – The point is we know as SOON as we find the Sale in any Store (and in fact for any Supplier and Date within the Promotion Period), we can STOP searching for anymore.
However, if we do a MIN, MAX we still have to read all the data to determine the MIN or MAX figure, we just want a FIRST or ANY function (to check the existence), in SQL 2005 we could use a CLR Function to do this, but we need it in SQL 2000. And we want it as performant as we can get and we need to do it in a View, so the answer is determined at Run Time for loading overnight into an Analysis Services 2005 Dimension.
If anyone is interested I have a solution that solved this via UDF (however, using Table UDF – But as not as an In-line Function and not in the Where or Join Condition)
The time to return the Flag (Yes/No Sales) data for 86,000 ProductPromotions (Products on Promotion) against 27 Months worth of Sales Data (1,350,000,000 rows) is 25 Minutes – Which is OK as it will be loaded into an Analysis Services Cube, so we only need it at Dimension Creation Time
The way I did was as follows
SELECT X.ProductID, X.PromotionID, X.PromotionPeriodStartDateID, X.PromotionPeriodEndDateID, CASE WHEN MAX(Y.ProductID) IS NULL THEN 0 ELSE 1 END As ProductSaleWithinPeriodFlag FROM dbo.MyProductPromotionUDF AS X LEFT OUTER JOIN ( SELECT ProductID, BusinessDateID FROM dbo.SupplierProductStoreDay – Union ALL View GROUP BY ProductID, BusinessDateID ) AS Y ON Y.ProductID = X.ProductID AND Y.BusinessDateID >= X.PromotionPeriodStartDateID AND Y.BusinessDateID <= X.PromotionPeriodEndDateID GROUP BY X.ProductID, X.PromotionID, X.PromotionPeriodStartDateID, X.PromotionPeriodEndDateID It is important to use the dbo.MyProductPromotionUDF as a UDF Function to get the ‘working/filtered’ Product Promotion list as if you do not the Query Engine will evaluate ProductPromotion/SupplierProductStoreDay combination and then choose a ‘really inefficient query engine’ and the time to work it out is INCREASED from 25 minutes to 3 hours.
Note: A key point is that the use of a Table UDF ‘forces’ SQL Server to process/execute that part of the Execution plan first then use the results to generated to process against the LEFT OUTER JOIN against the SupplierProductStoreDay – Union ALL View. Which is a neat way of ‘forcing’ some order into your execution plan!
BTW. I needed to use a GETDATE() in my dbo.MyProductPromotionUDF Function and you can’t (it is a non-deterministic function)! – A work around to put the SELECT GetDate() AS CurrentDateTime into a View: vGetDate and call the View in the UDF. I am not worried out any inconsistency as I am only after the current date, and not the time component: See: http://sqlserver2000.databases.aspfaq.com/how-do-i-use-getdate-within-a-user-defined-function-udf.html
Also I have an added filters into dbo.MyProductPromotionUDF as I do not want to process ALL the Promotions that have ever been run, only those that exist for those dates we have data in the SupplierProductStoreDay for (reduces the number of Products and Periods to Join against in the Left Outer Join)
To understand, the Business requirement to set this Flag, even though we have around 90,000 ProductPromotions only 86,000 ProductPromotions are valid in the 27 months, and from that we only have sales for around 50% (44,000) of these. Thus we are currently wasting Analysis Services processing time ‘looking for sales data’ which we will do not have. By using this Flag, we can filter out looking for these Products beforehand.
|
-
I will be using this medium for occasional musings on SQL Server. Especially when I resolve a problem which I believe may help others…
My first posting concerns an MSDTC Error 8522 that a client was experiencing. This is a common problem with many causes (just try googling it), I believe I have identified one problem and resolution and I am posting it here to help others. The problem could be summarised as due to Transactional Scope.
A client was encountering a periodic problem on a number of child nodes in the upstream data transfer load process, whereby data is transferred from a child node to a parent node under the control of a Linked Server, MSDTC and SQL Server Agent. They were running SQL Server 2000 Personal Edition running on XP SP1.
The client has a number of these child nodes and was getting, the following error message in SQL Server. Once a child node started to generate the error message, then it would repeatedly fail. Other child nodes with exactly the same code base would continue to work OK (i.e. the worse problems to solve because they are intermittent!)
Executed as user: <Domain_Name>\<SQL_Server_Service_Account>. Distributed transaction aborted by MSDTC. [SQLSTATE 42000] (Error 8522). The step failed.
MSDTC is an acronym for Microsoft Distributed Transaction Coordinator which is a Windows service providing transaction infrastructure for distributed systems.
The client had undertaken a number of attempts to resolve the problem, including using tools such as DTCPing and DTCTester. Please see Florin Lazar's Blog for these:
http://blogs.msdn.com/florinlazar/archive/2005/09/16/469064.aspx
I was asked to investigate the issue and try and identify a solution. Normally, I would have recommended using the tools listed above, and looked at Security settings (notably TurnOffRpcSecurity) to resolve this problem, as this had worked successfully at other clients but this only applies to XP SP2 (not SP1). Again see Florin Lazar's Blog for this
http://blogs.msdn.com/florinlazar/archive/2004/06/18/159127.aspx
It was soon evident that this was different problem entirely and required more radical thought to resolve.
The problem was isolated to a stored procedure I will call TransferDataToParent. I reviewed this stored procedure and discovered a couple of areas which gave me concern:
- It was over engineered for its purpose, as it was invoking superfluous local transactions
- It was undertaking combined local and distributed transactions in a single unit of work.
I wondered if this was the second point was cause of the problem, and that MSDTC was getting ‘confused’ over what was the scope of a local transaction and the scope of a distributed transaction.
Basically (at a high level) the stored procedure performed 3 functions:
1) Obtained data to transfer up the hierarchy in a series of SQL Server table variables (@table) under the control of a local transaction
2) Obtained secondary data into a local table variable (@table) and attempted to transfer this data to the parent node all under the control of a Distributed Transaction
3) Transferred the data obtained in Function 1) to the parent node in a Distributed Transaction
It was determined that the process was failing at Function 2). However, I was concerned with Functions 1) and 2) for the following reasons
- Function 1) - This is in a superfluous Transaction. The data was being stored into a series of table variables (@tables). @tables and # temporary tables are both non-persistent data stores. In the case of @tables, these are located in SQL Server memory and not on disk. Therefore a transaction is not needed, because if the code fails, then the data is automatically lost anyway. This does not need transactional control.
- Function 2) – This process met two different requirements:
a) Obtaining data in local memory tables (@tables) but within a Distributed Transaction
b) Transferring the data to the parent node within the same Distributed Transaction
I realised that a) should NOT be in a Distributed Transaction as it is an extension of the process in Function 1 above. Furthermore, MSTDC was probably also getting confused with attempting to perform a Distributed Transaction on local data (and with a @table variable as well!) – all within the same transactional scope.
Therefore, I rewrote the stored procedure to remove these areas of concern, namely:
- Obtain all data in local table variables outside of any transaction (local or distributed)
- Ensured all data transfers up the hierarchy were purely under the control of a Distributed Transaction (i.e. reduced transaction scope)
This worked successfully on two child nodes, that were previously having this problem and I now believe this problem to be resolved.
In summary, this may not be the cause of your particular MSDTC Error 8522 problem, but I would recommend that you look at the scope of the distributed transaction, are you combining it with a local transaction and/or non-persistent data?
|
|
|
|