UNION ALL within a view

I have a client who has viewes that contain UNION ALL command for number of very large tables. The logic is to apply a WHERE condition to that view to get the data. Even having indexes on those tables SQL Server applies a WHERE condition to each SELECT statement within a view that may lead to performance proble. To demonstate it please considetr AdwentureWork data and two tables Sales.SalerDetails and SalesOrderHeader

CREATE VIEW v1
AS
SELECT S.SalesOrderID
FROM
Sales.SalesOrderDetail S
UNION ALL
SELECT S.SalesOrderID
,S.CreditCardID FROM
Sales.SalesOrderHeader S

SET STATISTICS IO ON
SELECT TOP 100 * FROM v1 WHERE SalesOrderDetailID >40000 AND SalesOrderDetailID<45000

Table 'SalesOrderDetail'. Scan count 3, logical reads 1359, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'SalesOrderHeader'. Scan count 1, logical reads 34, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

You can see that SQL Server 'touched' those tables by using Clustered Index Scan on
Sales.SalerDetails and Clustered Index Seek on Sales.SalesOrderHeader. Indeed I realy simplified the logic as in reality it has horrible performance. How to improve?

I create a Multi-Statement Table-Valued UDF that accepts a parameter
CTREATE FUNCTION dbo.udf1
(
@SalesOrderID INT
)
RETURNS @t TABLE (c1 INT,c2 INT)
AS
BEGIN
INSERT INTO @t
SELECT S.SalesOrderID
FROM
Sales.SalesOrderDetail S
WHERE SalesOrderID > @SalesOrderID AND SalesOrderID<@SalesOrderID+5000
UNION ALL
SELECT S.SalesOrderID
FROM
Sales.SalesOrderHeader S
WHERE SalesOrderID > @SalesOrderID AND SalesOrderID<@SalesOrderID+5000
RETURN
END
Table '#74AE54BC'. Scan count 1, logical reads 12, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

See , I applied a WHERE condition within an UDF for every statement as UDF must get parameters.This is another argument to use Table-Valued UDF ...

SQL Server 2005/2008 on Windows 7

SQL Server MVP John Paul Cook has already mentioned on www.sqlblog.com that SQL Server 2008 will install on Windows 7 and it works just fine. I would like to add that SQL Server 2005 is also working just fine along with SQL Server 2008.
Having said that I'm told by many people that Reporting Services does not work on W7.Well the phrase 'does not work' does not say too much without additional info however I decided to do some testing and found out that SSRS works just fine as well, so the problem could be because the SSRS/SQL Server account does not have enough credentials to run SSRS. More over I took old reports which were deployed to our dedicated SSRS server (Windows 2003) and redeployed to Windows 7 and that also worked just fine. I'm really happy with Windows 7 which is faster than Vista and more comfortable than XP.....

Report on Indexes on FKs

This question had been asked in NG yesterday.I think it is a good exercise so I take a script too get the column usage from both sides (parent/child) written by Aaron Bertrand and modified a little bit to return the table/column that does not have an index on.

WITH fk_no_indexes
AS
(
SELECT
[constraint_name] = f.[name],
[child_table] = OBJECT_NAME(f.parent_object_id),
[child_table_id]=f.parent_object_id,
[child_column] = cc.name,
[child_column_id]=cc.[column_id],
[parent_table] = OBJECT_NAME(f.referenced_object_id),
[parent_column] = pc.name
FROM
sys.foreign_keys f
INNER JOIN
(
SELECTc.[object_id],c.name,c.column_id,ic.index_id
FROM sys.columns c INNER JOIN sys.index_columns ic
ON c.[object_id] = ic.[object_id] AND c.column_id = ic.column_id
) AS pc
ON
f.key_index_id = pc.index_id
INNER JOIN sys.foreign_key_columns fkc
ON f.[object_id] = fkc.constraint_object_id
AND pc.[object_id] = fkc.referenced_object_id
AND fkc.referenced_column_id = pc.column_id
INNER JOIN sys.columns cc
ON fkc.parent_object_id = cc.[object_id]
AND fkc.parent_column_id = cc.column_id
)
SELECT [constraint_name],[child_table],[child_column],[parent_table] FROM
fk_no_indexes WHERE NOT EXISTS
(
SELECT * FROM sys.index_columns i
WHERE i.[object_id]=[child_table_id]
AND [child_column_id]=column_id
)
ORDER BY
constraint_name,
child_table;

Have you applied latest sevice pack to the client tools?

Well, I have seen a huge number of developers who have not heard aboit it at all. Yes,I'm serious.Recently, I had helped out tuning SQL Server performance of the database in pretty big and to be held in respect company with more than 10 developers. One day one of them asked me about the query ,which restuns the data without the error on his machine ,however , throws the error on the server.If I remember well it was conversion error.I checked both queries and indeed on the dev.machine it runs without the error. I know there is no magic here..and as you can imagine the dev.machine has installed NO service pack at all,but Production SQL Server 2000 has latest service pack (SP4).That's all story. The 'little' difference could have made a big error witout testing as each developer has installed server and client tools on their machines.The SQL Server query optimizer is free to move/change expressions with new release of service packs or hotfix thus please make sure that you have latest service pack on your client tools and test the query as well.

How to upload/modify more than one BLOB column in single statement

Recently, I have been visited our client who has one table with two columns defined as VARBINARY(MAX)to store the images. How can we insert/upade these columns with single statements? I have not used blobs too much ,so I ended up with the following

CREATE TABLE Blobs (id INT NOT NULL IDENTITY(1,1) PRIMARY KEY ,b1 VARBINARY(MAX),b2 VARBINARY(MAX))

---Single INSERT statement

INSERT INTO Blobs (b1,b2)
(SELECT *,(SELECT * FROM
Openrowset(Bulk 'N:\OnePicture.JPG', SINGLE_BLOB) AS d1)
FROM
Openrowset(Bulk 'N:\AnotherPicture.JPG', SINGLE_BLOB) AS d2)

--Update

UPDATE Blobs SET b1=(SELECT * FROM
Openrowset(Bulk 'N:\AnotherPicture.JPG', SINGLE_BLOB) AS d2),
b2=(SELECT * FROM
Openrowset(Bulk 'N:\OnePicture.JPG', SINGLE_BLOB) AS d2)
---WHERE condition put here

My wife gave birth to our third child on 4 May!

A son, 3 kg 370 grams!

We have replaced ourselves and are now done.

How to eat ASYNC_NETWORK_IO ?

Last time I was visiting our client the person showed me one process which was in suspended status for a long time. Using Adam's great script(http://sqlblog.com/blogs/adam_machanic/archive/2009/03/30/who-is-active-v8-40-now-with-delta-power.aspx) I identified the SELECT statement which had ASYNC_NETWORK_IO wait type. MS said that this type occurs on network writes when the task is blocked behind the network and we need to verify that the client is processing data from SQL Server. The problem was that SELECT statement retrievs more than 50.000 rows , then the application manipulates (FOR ..NEXT loop) with that data and finally only 200 rows that a client had seen at the end. Well,thanks to SQL Server 2005 we changed the statement to accept a parameter for TOP clause and this wait type has gone. We have not seen a great improvment of the application as we did not expect it but it is another way to identify poor written queries.