Errors:
Connection failed:
SQL State: '01000'
SQL Server Error: 14
[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen (Invalid
Instance())
Connection failed:
SQL State: '08001'
SQL Server Error: 14
[Microsoft][ODBC SQL Server Driver][DBNETLIB]Invalid Connection
Rosolution:
Use servername\instancename,portnumber
Portnumber is 1433
URL: http://explainextended.com/2009/07/16/inner-join-vs-cross-apply/
URL: http://stackoverflow.com/questions/1139160/when-should-i-use-cross-apply-over-inner-join
How to create fast database queries
My latest article on SQL in general: Happy New Year!
. You're welcome to read and comment on it.
Comments enabled. I *really* need your comment
From Stack Overflow:
Can anyone give me a good example of when
CROSS APPLYmakes a difference in those cases whereINNER JOINwill work as well?
This is of course SQL Server.
A quick reminder on the terms.
INNER JOIN is the most used construct in SQL: it joins two tables together, selecting only those row combinations for which a JOIN condition is true.
This query:
1.SELECT *
2.FROM table1
3.JOIN table2
4.ON table2.b = table1.a
reads:
For each row from
table1, select all rows fromtable2where the value of fieldbis equal to that of fielda
Note that this condition can be rewritten as this:
1.SELECT *
2.FROM table1, table2
3.WHERE table2.b = table1.a
, in which case it reads as following:
Make a set of all possible combinations of rows from
table1andtable2and of this set select only those rows where the value of fieldbis equal to that of fielda
These conditions are worded differently, but they yield the same result and database systems are aware of that. Usually both these queries are optimized to use the same execution plan.
The former syntax is called ANSI syntax, and it is generally considered more readable and is recommended to use.
However, it didn’t get into Oracle until recently, that’s why there are many hardcore Oracle developers that are just used to the latter syntax.
Actually, it’s a matter of taste.
To use JOINs (with whatever syntax), both sets you are joining must be self-sufficient, i. e. the sets should not depend on each other. You can query both sets without ever knowing the contents on another set.
But for some tasks the sets are not self-sufficient. For instance, let’s consider the following query:
We table
table1andtable2.table1has acolumncalledrowcount.For each row from
table1we need to select firstrowcountrows fromtable2, ordered bytable2.id
We cannot formulate a join condition here. The join condition, should it exists, would involve the row number, which is not present in table2, and there is no way to calculate a row number only from the values of columns of any given row in table2.
That’s where the CROSS APPLY can be used.
CROSS APPLY is a Microsoft’s extension to SQL, which was originally intended to be used with table-valued functions (TVF‘s).
The query above would look like this:
01.SELECT *
02.FROM table1
03.CROSS APPLY
04.(
05.SELECT TOP (table1.rowcount) *
06.FROM table2
07.ORDER BY
08.id
09.) t2
For each from
table1, select firsttable1.rowcountrows fromtable2ordered byid
The sets here are not self-sufficient: the query uses values from table1 to define the second set, not to JOINwith it.
The exact contents of t2 are not known until the corresponding row from table1 is selected.
I previously said that there is no way to join these two sets, which is true as long as we consider the sets as is. However, we can change the second set a little so that we get an addicional calculated field we can later join on.
The first option to do that is just count all preceding rows in a subquery:
01.SELECT *
02.FROM table1 t1
03.JOIN (
04.SELECT t2o.*,
05.(
06.SELECT COUNT(*)
07.FROM table2 t2i
08.WHERE t2i.id <= t2o.id
09.) AS rn
10.FROM table2 t2o
11.) t2
12.ON t1.rowcount = t2.rn
The second option is to use a window function, also available in SQL Server since version 2005:
1.SELECT *
2.FROM table1 t1
3.JOIN (
4.SELECT t2o.*, ROW_NUMBER() OVER (ORDER BY id) AS rn
5.FROM table2 t2o
6.) t2
7.ON t1.rowcount = t2.rn
This functions returns the ordinal number a row would have be the ORDER BY condition used in the function applied to the whole query.
This is essentially the same result as the subquery used in the previous query.
Now, let's create the sample tables and check all these solutions for efficiency:
01.SET NOCOUNT ON
02.GO
03.DROP TABLE [20090716_cross].table1
04.DROP TABLE [20090716_cross].table2
05.DROP SCHEMA [20090716_cross]
06.GO
07.CREATE SCHEMA [20090716_cross]
08.CREATE TABLE table1
09.(
10.id INT NOT NULL PRIMARY KEY,
11.row_count INT NOT NULL
12.)
13.CREATE TABLE table2
14.(
15.id INT NOT NULL PRIMARY KEY,
16.value VARCHAR(20) NOT NULL
17.)
18.GO
19.BEGIN TRANSACTION
20.DECLARE @cnt INT
21.SET @cnt = 1
22.WHILE @cnt <= 100000
23.BEGIN
24.INSERT
25.INTO [20090716_cross].table2 (id, value)
26.VALUES (@cnt, 'Value ' + CAST(@cnt AS VARCHAR))
27.SET @cnt = @cnt + 1
28.END
29.INSERT
30.INTO [20090716_cross].table1 (id, row_count)
31.SELECT TOP 5
32.id, id % 2 + 1
33.FROM [20090716_cross].table2
34.ORDER BY
35.id
36.COMMIT
37.GO
table2 contains 100,000 rows with sequential ids.
table1 contains the following:
id
row_count
1
2
2
1
3
2
4
1
5
2
Now let's run the first query (with COUNT):
01.SELECT *
02.FROM [20090716_cross].table1 t1
03.JOIN (
04.SELECT t2o.*,
05.(
06.SELECT COUNT(*)
07.FROM [20090716_cross].table2 t2i
08.WHERE t2i.id <= t2o.id
09.) AS rn
10.FROM [20090716_cross].table2 t2o
11.) t2
12.ON t2.rn <= t1.row_count
13.ORDER BY
14.t1.id, t2.id
id
row_count
id
value
rn
1
2
1
Value 1
1
1
2
2
Value 2
2
2
1
1
Value 1
1
3
2
1
Value 1
1
3
2
2
Value 2
2
4
1
1
Value 1
1
5
2
1
Value 1
1
5
2
2
Value 2
2
8 rows fetched in 0.0000s (498.4063s)
Table 'table1'. Scan count 2, logical reads 200002, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'Worktable'. Scan count 100000, logical reads 8389920, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'table2'. Scan count 4, logical reads 1077, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. SQL Server Execution Times: CPU time = 947655 ms, elapsed time = 498385 ms.
This query, as was expected, is very unoptimal. It runs for more than 500 seconds.
Here's the query plan:
SELECT
Sort
Compute Scalar
Parallelism (Gather Streams)
Inner Join (Nested Loops)
Inner Join (Nested Loops)
Clustered Index Scan ([20090716_cross].[table2])
Compute Scalar
Stream Aggregate
Eager Spool
Clustered Index Scan ([20090716_cross].[table2])
Clustered Index Scan ([20090716_cross].[table1])
For each row selected from table2, it counts all previous rows again an again, never recording the intermediate result. The complexity of such an algorithm is O(n^2), that's why it takes so long.
Let's run he second query, which uses ROW_NUMBER():
01.SELECT *
02.FROM [20090716_cross].table1 t1
03.JOIN (
04.SELECT t2o.*, ROW_NUMBER() OVER (ORDER BY id) AS rn
05.FROM [20090716_cross].table2 t2o
06.) t2
07.ON t2.rn <= t1.row_count
08.ORDER BY
09.t1.id, t2.id
id
row_count
id
value
rn
1
2
1
Value 1
1
1
2
2
Value 2
2
2
1
1
Value 1
1
3
2
1
Value 1
1
3
2
2
Value 2
2
4
1
1
Value 1
1
5
2
1
Value 1
1
5
2
2
Value 2
2
8 rows fetched in 0.0006s (0.5781s)
Table 'Worktable'. Scan count 1, logical reads 214093, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'table2'. Scan count 1, logical reads 522, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'table1'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. SQL Server Execution Times: CPU time = 578 ms, elapsed time = 579 ms.
This is much faster, only 0.5 ms.
Let's look into the query plan:
SELECT
Inner Join (Nested Loops)
Clustered Index Scan ([20090716_cross].[table1])
Lazy Spool
Sequence Project (Compute Scalar)
Compute Scalar
Segment
Clustered Index Scan ([20090716_cross].[table2])
This is much better, since this query plan keeps the intermediate results while calculating the ROW_NUMBER.
However, it still calculates ROW_NUMBERs for all 100,000 of rows in table2, then puts them into a temporary index over rn created by Lazy Spool, and uses this index in a nested loop to range the rns for each row fromtable1.
Calculating and indexing all ROW_NUMBERs is quite expensive, that's why we see 214,093 logical reads in the query statistics.
Finally, let's try a CROSS APPLY:
01.SELECT *
02.FROM [20090716_cross].table1 t1
03.CROSS APPLY
04.(
05.SELECT TOP (t1.row_count) *
06.FROM [20090716_cross].table2
07.ORDER BY
08.id
09.) t2
10.ORDER BY
11.t1.id, t2.id
id
row_count
id
value
1
2
1
Value 1
1
2
2
Value 2
2
1
1
Value 1
3
2
1
Value 1
3
2
2
Value 2
4
1
1
Value 1
5
2
1
Value 1
5
2
2
Value 2
8 rows fetched in 0.0004s (0.0008s)
Table 'table2'. Scan count 5, logical reads 10, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'table1'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 1 ms.
This query is instant, as it should be.
The plan is quite simple:
SELECT
Inner Join (Nested Loops)
Clustered Index Scan ([20090716_cross].[table1])
Top
Clustered Index Scan ([20090716_cross].[table2])
For each row from table1, it just takes first row_count rows from table2. So simple and so fast.
Summary:
While most queries which employ CROSS APPLY can be rewritten using an INNER JOIN, CROSS APPLY can yield better execution plan and better performance, since it can limit the set being joined yet before the join occurs.
Reference
URL: http://www.bidn.com/blogs/TomLannen/bidn-blog/2265/using-hashbytes-to-compare-columns
posted 10/17/2011 by TomLannen - Views: [11671]
Recently, while at a client engagement, I was building some SSIS packages an issue came up where they didn?t want to use the CHECKSUM() function in TSQL to do column comparisons because the results can be inaccurate on some rare occasions. I personally have never come across this but others here at Pragmatic Works have. So we have two options freely available to work around this issue. The first is the third party component plugin that you can get free at codeplex called Multiple Hash. The client wasn?t comfortable with having to install this component on multiple servers throughout the environment so that option wasn?t available to me. Instead I had to use the HASHBYTES() function in TSQL.
HASHBYTES() is much more reliable than checksum when it comes to producing accurate results, but it comes at a slight cost.
The first thing to note is how to construct the HASHBYTES() function. In the first part you tell the function which algorithm you are going to use. I?m using SHA1, but be aware that they single tics ? ? are required followed by a comma. Then you must concatenate the columns you wish to use together as seen below.
There you can see its already a bit more arduous than using CHECKSUM(), but not that big of a deal to concatenate a bunch of columns. Lets look at the results.
Uh-Oh here is our first problem. HASHBYTES() doesn?t work with NULL values inside any columns. So we?ve got to handle the Nulls in our query using the ISNULL() function.
Now the results look like this:
Then next thing that you have to look at is how HASHBYTES() handles(or more accurately doesn?t handle) Data types. Here the ID column is an INT data type, but the same holds true for any non-string data type.
We get an error saying that the data type is wrong for the HASHBYTES() function
So now we have to CAST every column that is a non-string data type.
Now after this fix our results look better.
So as you can see already there will be a good deal more T-SQL coding involved with using HASHBYTES then with CHECKSUM(). But this isn?t all. The last little gotcha isn?t quite as obvious as the first two. Lets go back to our Null handling query.
I?ve gone and edited the data some for this example. Please also note that HASHBYTES() is case sensitive meaning that if you have the same spelling but different casing at an individual character level the hash value returned will be different.
The rows are different from one another but when concatenated together for the HASBYTES() function they produce the same exact value. So to handle this we have to update our code again. We are going to add a rarely used character to the concatenation so ensure that they results will return correctly.
Here I chose to use a pipe to basically delimit the columns thereby making them different from each other. The result is much better.
If we take a quick look at results of the two concatenations we can see why we get the different results
The delimited column is obviously different from one row to the next, and the Non-Delimited column is exactly the same for each row.
While HASHBYTES() is definitely more accurate and reliable than using CHECKSUM(), there are some hoops to jump through to insure you get the results you need. I hope this helps you guys out.
I’ve created a report project solution and imported a few report rdl files. First, BIDS 2008 asked me to identify the StartItem.
Then When I try to run a report, I just click the green triangle to debugging but did not realize bids was trying to run that default report I've
picked as the StartItem. This error bothered me for a few hours until I finally figured it out .
I have to right click the report I want to run under designer and run it . Keep that in mind!!!
Excellent article on this topic:
http://www.mssqltips.com/sqlservertip/2561/querying-sql-server-agent-job-information/
Sometimes, I need to compare two columns to find out missing value
such as column a contains list of orderid from one script while column b contains list of orders from another script
Since two scripts are running on different env, the only way to compare is to copy and paste to excel.
Now if list contains thousands of orders, it is inhumane to go through each row
Here is the solution: http://en.kioskea.net/forum/affich-456715-find-missing-data-in-2-columns-excel
use COUNTIF function in excel
Suppose A column contains less orders than E
You want to find out how many are missing in column
=IF(COUNTIF(A$2:A$3730,E2521)=0,E2521,"")
Remember to use $
What it does it to take each value in E column and go through the whole A column, if there is no match, display that value from Column E
Neat !
When using dynamic sql to create a temp table, you need to create the temp table first, then alter columns in the dynamic sql
see this blog below:
When programming in SQL on occasion there is a need to generate temporaty tables inside a sproc without knowing the colums or type when the sproc is written. One such situation would be when you want to pivot a table. You can create a cursor over a column result set and create a new column for each value.
When you try to create temporary tables with dynamic SQL you will run into a scoping problem.
for example:
DECLARE @SQL nvarchar(4000)
SELECT @SQL = 'CREATE TABLE #Temp (col1 int)'
EXEC (@SQL)
SELECT * FROM #Temp
This will cause an error:
Msg 208, Level 16, State 0, Line 4
Invalid object name '#Temp'.
The problem here is the scope of the session. When we execute dynamic sql via EXEC or sp_executesql a new scope is created for a child session. Any objects created in that session are dropped as soon as the session is closed.
One solution I have found for this problem is creating the table in the "parent" scope and then just using dynamic sql to modify the table. For this to work a table is created with a minimum set of colums. And then we use the ALTER TABLE statement with dynamic SQL. The Child session has access to the objects created in the parent session so the table can be modified with dynamic sql:
DECLARE @SQL NVARCHAR(4000)
CREATE TABLE #Temp ( id int null)
SELECT @SQL = 'ALTER #Temp ADD Col1 int null'
EXEC (@SQL)
SELECT * FROM #Temp
DROP TABLE #Temp
This table is visible and both columns will show up.
Sometimes, there are needs to execute SSRS directly from a sql server job or inside a SSIS package.
If it is from a SSIS package, you need to use script component and use the web service exposed by SSRS
If you need to execute it through a Sql server job agent, below is the step
1. Create a one time subscription for that report.
2. Find out the Subscription ID for that report
SELECT s.SubscriptionID
FROM dbo.Catalog AS c
INNER JOIN dbo.ReportSchedule AS s
ON c.ItemID = s.ReportID
WHERE
c.Name = ‘your report name’
3. Execute dbo.AddEvent proc on ReportServer
EXEC ReportServer.dbo.AddEvent @EventType = 'TimedSubscription', @EventData = @SubscriptionID
This SSRS rpt will be executed by this job.
The SQL Server Reporting Services Report Manager is functional, but it's not very customizable. The ASPX pages just reference compiled assemblies, so the only real way to modify them is via CSS..msrs-uppertitle { BACKGROUND: url(http://www.sitename.com/images/logo.gif) no-repeat; HEIGHT: 35px; WIDTH: 120px; TEXT-INDENT: -5000px; }
addLoadEvent(SetLogoUrl); function addLoadEvent(fn) { if (window.addEventListener) window.addEventListener('load', fn, false) else if (window.attachEvent) window.attachEvent('onload', fn); } function SetLogoUrl() { var header = document.getElementById('ui_sharedArea'); if (!header) return; var headerDivs = header.getElementsByTagName('div'); for (var i=0;i<headerDivs.length;i++) { if(headerDivs[i].className == 'msrs-uppertitle') { headerDivs[i].onclick = new Function('top.location="/"'); headerDivs[i].style.cursor = 'pointer'; //headerDivs[i].style.backgroundImage = 'url(http://images.slashdot.org/topics/topiccommunications.gif)'; //headerDivs[i].style.backgroundRepeat = 'no-repeat'; //headerDivs[i].style.width = '96px'; //headerDivs[i].style.height = '62px'; } } }
