Database

Jörgen Andersson14-Jul-14 22:20

14-Jul-14 22:20

Wrong is evil and must be defeated. - Jeff Ello[^]

Ambertje14-Jul-14 22:12

Ambertje

14-Jul-14 22:12

Eddy,

This example made it clear to me, I think I understand what it's doing.
Now I can try to rewrite the code to my needs.
Thank you for taking the time to help me Thumbs Up | :thumbsup:

Ambertje

jschell14-Jul-14 10:07

jschell

14-Jul-14 10:07

Ambertje wrote:
I want to make a dayset so that we only read one single day into the DB on top of the existing data and not the whole year.

I don't see what that has to do with partitions - the database of course wouldn't have anything to do with reading files.

Ambertje wrote:
and takes a long time to process.

Someone should optimize that process. I wouldn't be surprised if that means moving it out of SSIS but maybe it can be sped up as is.

Normally one profiles a process by actually timing the various parts and then using that data to determine where things can be made faster.

Although your description might suggest that every day you read the previous years data. If so one obvious optimization there would be to some producing a file that has a years worth of data. How one does that depends on the data.

GuyThiebaut14-Jul-14 21:33

GuyThiebaut

14-Jul-14 21:33

The best way to understand this is to create a couple of tables and experiment.

It is quite difficult to explain what is going on in a partition statement - the closest I can get is transposing data in Excel.
However that does not explain how to go about transposing the data.

Think of it as changing column headings into row contents and row contents into column headings.

“That which can be asserted without evidence, can be dismissed without evidence.”

― Christopher Hitchens

Error: Can't delete row or update row in SQL Server ?

Ambertje14-Jul-14 22:13

Ambertje

14-Jul-14 22:13

Many thanks to everyone who is helping me, I really appreciate the effort you put into it to help newbe's.

Ambertje

taibc11-Jul-14 18:29

taibc

11-Jul-14 18:29

Hi everyone,

I am creating a query to my database using SQL Server Management Studio

There is a data error in my image field of my row. I still can select another fields, but when I select content of the image field I get below error:

My query:

SQL

select diagram from Mytable where id = 5

Error 1:

A transport-level error has occurred when receiving results from the server. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.)

When I tried to delete this row. I got another error (Error 2):

SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0x4da7af2c; actual: 0xda5e9af7). It occurred during a read of page (1:40403) in database ID 6 at offset 0x00000013ba6000 in file 'D:\CLCData\23_01_2013\CLC_LIS_Data.mdf'. Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.

When I select all data from the table, I got the error 1 at the above row position.

So, now I can't update or delete this row. Do you know this problem and how to fix it ?

Thank you very much,

Tai

Re: Error: Can't delete row or update row in SQL Server ?

Mycroft Holmes11-Jul-14 23:32

Re: Error: Can't delete row or update row in SQL Server ?

11-Jul-14 23:32

Have you done what the error message suggested?

Never underestimate the power of human stupidity
RAH

taibc13-Jul-14 20:36

taibc

13-Jul-14 20:36

Hi,

I checked the log error file. For the select query, I got the below error:

Error: 7886, Severity: 20, State: 1.
2014-07-14 11:27:01.50 spid52 A read operation on a large object failed while sending data to the client. A common cause for this is if the application is running in READ UNCOMMITTED isolation level. This connection will be terminated.

For the delete statement, I got the message:

Error: 824, Severity: 24, State: 2.
2014-07-14 13:34:10.12 spid51 SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0x4da7af2c; actual: 0xda5e9af7). It occurred during a read of page (1:40403) in database ID 6 at offset 0x00000013ba6000 in file 'D:\CLCData\23_01_2013\CLC_LIS_Data.mdf'. Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online

But, I don't know how to fix it.

Re: Error: Can't delete row or update row in SQL Server ?

Mycroft Holmes13-Jul-14 21:06

Re: Error: Can't delete row or update row in SQL Server ?

13-Jul-14 21:06

Execute DBCC CHECKDB int SQL Server Management Studio, just like the error message tells you, then see what the results of the check are.

Never underestimate the power of human stupidity
RAH

ZurdoDev14-Jul-14 10:14

ZurdoDev

14-Jul-14 10:14

You have a damaged data file or the disk is damaged. You'll want to run the DBCC as the error suggests and see if you get additional details.

There are only 10 types of people in the world, those who understand binary and those who don't.

JOIN vs. WHERE

Klaus-Werner Konrad11-Jul-14 9:22

11-Jul-14 9:22

I quite don't understand why noone is using the WHERE-Syntax for table joining ...

I always see something like

SQL

SELECT  O.Order_No, C.Address, F.Address 
FROM    Orders O
JOIN    Address C
    ON  O.Customer = C.ID
JOIN    Address F 
    ON  O.Forwarder = C.ID

For me, the most irritating thig is the spreading of table names and aliases all over the Statement.

I prefer

SQL

SELECT O.Order_No, C.Address, F.Address
FROM Orders O, Address C, Address F 
WHERE O.Customer = C.ID 
AND O.Forwarder = F.ID

- It's shorter
- All table names and aliases are together

Arguments, anyone ?

Mycroft Holmes11-Jul-14 14:24

11-Jul-14 14:24

Klaus-Werner Konrad wrote:
Arguments,

Nope, I call them Oracle joins (I loathe Oracle) because that is the way I was taught in the 90s then I moved to SQL SErver and was introduced to the JOIN and have never used it since.

I will refactor any procedure I see using them and have a short, sharp discussion with the dev that uses it. Use join or quit.

Never underestimate the power of human stupidity
RAH

Klaus-Werner Konrad12-Jul-14 9:08

12-Jul-14 9:08

Mycroft Holmes wrote:
Nope, I call them Oracle joins

Yeah - a really qualified Response ...
I ask for arguments, an your resposne is

Mycroft Holmes wrote:
I will [...] have a short, sharp discussion with the dev that uses it

THIS is my Intention - to have a DISCUSSION about it !
But you have obvoiously no arguments, but prejudices against it - not the ideal base for discussions.
What would be your arguments in a discussion with your developer - do you even have any arguments ?
Note: I asked politely for arguments for - or against - my preferation, because (as every of us) I want to improve my abilities, and also to give some impressions to SQL newbies to decide what to do, and not to do.
So ...
HAVE VOU ARGUMENTS, or are you just felt to open your mouth ... ?

Mycroft Holmes12-Jul-14 14:13

12-Jul-14 14:13

Klaus-Werner Konrad wrote:
THIS is my Intention - to have a DISCUSSION about it !

So we are supposed to be telepathic so we can identify your INTENTIONS.

You stated that you prefer the JOIN format, then you asked for arguments to that position. Nowhere did you mention discussion.

I stated that I had no argument and qualified why. In other words I was supporting your preference!

I did not down vote your question or response (was not even aware you had been) as the question was valid and the response just means you are a bit pissy this morning.

Never underestimate the power of human stupidity
RAH

Klaus-Werner Konrad13-Jul-14 0:08

13-Jul-14 0:08

Well - the title should be clear enough, and I presented JOIN syntax and WHERE syntax,
expressing my worry about noone seems using WHERE.

THEN I asked for arguments - obviously (I thought so) WHY noone is using WHERE syntax,
and NOT to support my preference.

In other words: Are there any points (other than preference) to not using WHERE syntax ??

data modeling guy11-Jul-14 19:53

data modeling guy

11-Jul-14 19:53

Klause,

theoretically, the first approach is more optimized and can give better performance while working with large data sets.

WHY?
In first approach, the intermediate tables are expressed clearly and the steps to arrive the result is evident.

eg:
step1 -> Create Intermediate table I : O join C on ID
step2 -> Result R : I join F on ID

this is optimum route to reach the result.

How about second approach?

In a simple case, SQL optimizer might arrive at same steps by analyzing the tables and columns used in WHERE clause but not always. Especially when the WHERE clause is relatively complex.

Mycroft Holmes11-Jul-14 23:29

11-Jul-14 23:29

I was under the impression there was no difference by the time the optimizer had done it's job so I have always based my ~~bigotry~~ opinion on the readability.

Never underestimate the power of human stupidity
RAH

Jörgen Andersson12-Jul-14 9:42

12-Jul-14 9:42

Generally there isn't, but in specific cases there are.

Wrong is evil and must be defeated. - Jeff Ello[^]

Jörgen Andersson12-Jul-14 10:25

12-Jul-14 10:25

The downvote you got is stupid because the question is valid.

The problem is though that readability isn't the same as clarity of intention, and there is more to it than you see at the first glance.

Firstly, when using an ANSI Join you specify which TABLES to join.
When using an implicit join you specify which fields to join on.

This is normally not making any difference, but it might in some specific cases.

Consider this query:

SQL

SELECT O.Order_No, C.Address, F.Address
FROM Orders O, Address C, Address F 
WHERE O.Customer = C.ID 
AND O.Forwarder = F.ID(+)
AND F.OtherID = 1

Is it the same as:

SQL

SELECT  O.Order_No, C.Address, F.Address 
FROM    Orders O
JOIN    Address C
    ON  O.Customer = C.ID
LEFT OUTER JOIN Address F 
    ON  O.Forwarder = F.ID
    AND F.OtherID = 1

or:

SQL

SELECT  O.Order_No, C.Address, F.Address 
FROM    Orders O
JOIN    Address C
    ON  O.Customer = C.ID
LEFT OUTER JOIN Address F 
    ON  O.Forwarder = F.ID
WHERE   F.OtherID = 1

The difference is crucial as the results differ.

Secondly, when using an implicit join there is no way to force the optimizer to do the joins in a certain order, which normally is a good thing. The optimizer is usually better than many people on that.
But sometime you know better than the optimizer and can force the joining order as such:

SQL

SELECT  O.Order_No, C.Address, F.Address 
FROM    (
    Orders O JOIN  Address C
    ON  O.Customer = C.ID
    )   JOIN    Address F 
    ON  O.Forwarder = C.ID

My personal pet peeve is that a field should always have the same name everywhere in a database just like the ISO standard says. Smile | :)

Wrong is evil and must be defeated. - Jeff Ello[^]

Mycroft Holmes12-Jul-14 14:22

12-Jul-14 14:22

I'm curious, and lazy, which result does the first query match. I no longer remember the relevance of the (+), left outer join I believe whereas the intention may have been to filter the forwarders.

Never underestimate the power of human stupidity
RAH

Jörgen Andersson12-Jul-14 20:44

12-Jul-14 20:44

The second one, to match the first one the query would need to look like this:

SQL

SELECT O.Order_No, C.Address, F.Address
FROM Orders O, Address C, Address F 
WHERE O.Customer = C.ID 
AND O.Forwarder = F.ID(+)
AND F.OtherID(+) = 1

Wrong is evil and must be defeated. - Jeff Ello[^]

Jörgen Andersson12-Jul-14 20:55

12-Jul-14 20:55

When it comes to the relevance of the (+), think of it as the side that allows nulls (+ nulls)

Wrong is evil and must be defeated. - Jeff Ello[^]

Klaus-Werner Konrad13-Jul-14 0:35

13-Jul-14 0:35

Wrong - it's the OPPOSITE side that can be NULL, as in O.Forwarder = F.ID(+)

Here, O.Forwarder my be NULL, but F.ID can NEVER be NULL (it's usually the primary key)

Jörgen Andersson13-Jul-14 1:09

13-Jul-14 1:09

Have a look here[^]
Want to change your mind?.

Wrong is evil and must be defeated. - Jeff Ello[^]