How would i express a relation between different kinds of XML segments
in the same table with an XML datatype column.
Further, can i do this using dataset designer? Do you know where I can
get more information on how to use the dataset designer, or how to
write xsd for sql server 2005?
thanks,
Joyce
If the relationship is between elements in the same instance (i.e. same "cell"
in your table) then you can write an XML schema and make use of types xsd:ID
anjd xsd:IDREF. If the relationship is between XML fragments stored in different
rows or columns then this won't work.
Here's a link to the Books On Line page about creating an XML schema collection:
http://msdn2.microsoft.com/en-us/library/ms176009.aspx
If you can post more information on what you're trying to accomplish I'd be glad
to help.
Denis Ruckebusch
http://blogs.msdn.com/denisruc
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"joyce chan" <joyceschan@.fastmail.fm> wrote in message
news:1169140712.945912.315860@.q2g2000cwa.googlegro ups.com...
> How would i express a relation between different kinds of XML segments
> in the same table with an XML datatype column.
> Further, can i do this using dataset designer? Do you know where I can
> get more information on how to use the dataset designer, or how to
> write xsd for sql server 2005?
> thanks,
> Joyce
>
|||hi Denis
This is a helpful start. If I would like to relate between elements in
different row, but in the same column, is there a way to do that?
I'm looking at using Visual Studio dataset designer, and writing out
some queries that can perhaps write the schema out for me.
thanks,
Joyce
Denis Ruckebusch [MSFT] wrote:[vbcol=seagreen]
> If the relationship is between elements in the same instance (i.e. same "cell"
> in your table) then you can write an XML schema and make use of types xsd:ID
> anjd xsd:IDREF. If the relationship is between XML fragments stored in different
> rows or columns then this won't work.
> Here's a link to the Books On Line page about creating an XML schema collection:
> http://msdn2.microsoft.com/en-us/library/ms176009.aspx
> If you can post more information on what you're trying to accomplish I'd be glad
> to help.
>
> Denis Ruckebusch
> http://blogs.msdn.com/denisruc
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
>
> "joyce chan" <joyceschan@.fastmail.fm> wrote in message
> news:1169140712.945912.315860@.q2g2000cwa.googlegro ups.com...
|||I'm not familiar with the dataset designer.
I'd look at using relational mechanisms such as id columns to express the
dependency.
Denis Ruckebusch
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"joyce chan" <joyceschan@.fastmail.fm> wrote in message
news:1169218663.655366.182930@.v45g2000cwv.googlegr oups.com...
> hi Denis
> This is a helpful start. If I would like to relate between elements in
> different row, but in the same column, is there a way to do that?
> I'm looking at using Visual Studio dataset designer, and writing out
> some queries that can perhaps write the schema out for me.
> thanks,
> Joyce
> Denis Ruckebusch [MSFT] wrote:
>
|||Hello joyce,
No thats not possible. Schema validation is only on an individual cell.
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
[vbcol=seagreen]
> hi Denis
> This is a helpful start. If I would like to relate between elements
> in different row, but in the same column, is there a way to do that?
> I'm looking at using Visual Studio dataset designer, and writing out
> some queries that can perhaps write the schema out for me.
> thanks,
> Joyce
> Denis Ruckebusch [MSFT] wrote:
Showing posts with label relation. Show all posts
Showing posts with label relation. Show all posts
Wednesday, March 28, 2012
relationship between xml in xml data type column
How would i express a relation between different kinds of XML segments
in the same table with an XML datatype column.
Further, can i do this using dataset designer? Do you know where I can
get more information on how to use the dataset designer, or how to
write xsd for sql server 2005?
thanks,
JoyceIf the relationship is between elements in the same instance (i.e. same "cel
l"
in your table) then you can write an XML schema and make use of types xsd:I
D
anjd xsd:IDREF. If the relationship is between XML fragments stored in diffe
rent
rows or columns then this won't work.
Here's a link to the Books On Line page about creating an XML schema collect
ion:
http://msdn2.microsoft.com/en-us/library/ms176009.aspx
If you can post more information on what you're trying to accomplish I'd be
glad
to help.
Denis Ruckebusch
http://blogs.msdn.com/denisruc
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"joyce chan" <joyceschan@.fastmail.fm> wrote in message
news:1169140712.945912.315860@.q2g2000cwa.googlegroups.com...
> How would i express a relation between different kinds of XML segments
> in the same table with an XML datatype column.
> Further, can i do this using dataset designer? Do you know where I can
> get more information on how to use the dataset designer, or how to
> write xsd for sql server 2005?
> thanks,
> Joyce
>|||hi Denis
This is a helpful start. If I would like to relate between elements in
different row, but in the same column, is there a way to do that?
I'm looking at using Visual Studio dataset designer, and writing out
some queries that can perhaps write the schema out for me.
thanks,
Joyce
Denis Ruckebusch [MSFT] wrote:
> If the relationship is between elements in the same instance (i.e. same "c
ell"
> in your table) then you can write an XML schema and make use of types xsd
:ID
> anjd xsd:IDREF. If the relationship is between XML fragments stored in dif
ferent
> rows or columns then this won't work.
> Here's a link to the Books On Line page about creating an XML schema colle
ction:
> http://msdn2.microsoft.com/en-us/library/ms176009.aspx
> If you can post more information on what you're trying to accomplish I'd b
e glad
> to help.
>
> Denis Ruckebusch
> http://blogs.msdn.com/denisruc
> --
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
>
> "joyce chan" <joyceschan@.fastmail.fm> wrote in message
> news:1169140712.945912.315860@.q2g2000cwa.googlegroups.com...|||I'm not familiar with the dataset designer.
I'd look at using relational mechanisms such as id columns to express the
dependency.
Denis Ruckebusch
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"joyce chan" <joyceschan@.fastmail.fm> wrote in message
news:1169218663.655366.182930@.v45g2000cwv.googlegroups.com...
> hi Denis
> This is a helpful start. If I would like to relate between elements in
> different row, but in the same column, is there a way to do that?
> I'm looking at using Visual Studio dataset designer, and writing out
> some queries that can perhaps write the schema out for me.
> thanks,
> Joyce
> Denis Ruckebusch [MSFT] wrote:
>|||Hello joyce,
No thats not possible. Schema validation is only on an individual cell.
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
> hi Denis
> This is a helpful start. If I would like to relate between elements
> in different row, but in the same column, is there a way to do that?
> I'm looking at using Visual Studio dataset designer, and writing out
> some queries that can perhaps write the schema out for me.
> thanks,
> Joyce
> Denis Ruckebusch [MSFT] wrote:
>sql
in the same table with an XML datatype column.
Further, can i do this using dataset designer? Do you know where I can
get more information on how to use the dataset designer, or how to
write xsd for sql server 2005?
thanks,
JoyceIf the relationship is between elements in the same instance (i.e. same "cel
l"
in your table) then you can write an XML schema and make use of types xsd:I
D
anjd xsd:IDREF. If the relationship is between XML fragments stored in diffe
rent
rows or columns then this won't work.
Here's a link to the Books On Line page about creating an XML schema collect
ion:
http://msdn2.microsoft.com/en-us/library/ms176009.aspx
If you can post more information on what you're trying to accomplish I'd be
glad
to help.
Denis Ruckebusch
http://blogs.msdn.com/denisruc
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"joyce chan" <joyceschan@.fastmail.fm> wrote in message
news:1169140712.945912.315860@.q2g2000cwa.googlegroups.com...
> How would i express a relation between different kinds of XML segments
> in the same table with an XML datatype column.
> Further, can i do this using dataset designer? Do you know where I can
> get more information on how to use the dataset designer, or how to
> write xsd for sql server 2005?
> thanks,
> Joyce
>|||hi Denis
This is a helpful start. If I would like to relate between elements in
different row, but in the same column, is there a way to do that?
I'm looking at using Visual Studio dataset designer, and writing out
some queries that can perhaps write the schema out for me.
thanks,
Joyce
Denis Ruckebusch [MSFT] wrote:
> If the relationship is between elements in the same instance (i.e. same "c
ell"
> in your table) then you can write an XML schema and make use of types xsd
:ID
> anjd xsd:IDREF. If the relationship is between XML fragments stored in dif
ferent
> rows or columns then this won't work.
> Here's a link to the Books On Line page about creating an XML schema colle
ction:
> http://msdn2.microsoft.com/en-us/library/ms176009.aspx
> If you can post more information on what you're trying to accomplish I'd b
e glad
> to help.
>
> Denis Ruckebusch
> http://blogs.msdn.com/denisruc
> --
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
>
> "joyce chan" <joyceschan@.fastmail.fm> wrote in message
> news:1169140712.945912.315860@.q2g2000cwa.googlegroups.com...|||I'm not familiar with the dataset designer.
I'd look at using relational mechanisms such as id columns to express the
dependency.
Denis Ruckebusch
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"joyce chan" <joyceschan@.fastmail.fm> wrote in message
news:1169218663.655366.182930@.v45g2000cwv.googlegroups.com...
> hi Denis
> This is a helpful start. If I would like to relate between elements in
> different row, but in the same column, is there a way to do that?
> I'm looking at using Visual Studio dataset designer, and writing out
> some queries that can perhaps write the schema out for me.
> thanks,
> Joyce
> Denis Ruckebusch [MSFT] wrote:
>|||Hello joyce,
No thats not possible. Schema validation is only on an individual cell.
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
> hi Denis
> This is a helpful start. If I would like to relate between elements
> in different row, but in the same column, is there a way to do that?
> I'm looking at using Visual Studio dataset designer, and writing out
> some queries that can perhaps write the schema out for me.
> thanks,
> Joyce
> Denis Ruckebusch [MSFT] wrote:
>sql
Monday, March 26, 2012
Relations without contrains
If I created relation between 2 tables without any constarins what will be the value of this relation or when SQL Server will use this relationI'm not sure what you mean. When you create a relation there is always a constraint being created.sql
Relations without contrains
If I created relation between 2 tables without any constarins what will be the value of this relation or when SQL Server will use this relationI'm not sure what you mean. When you create a relation there is always a constraint being created.
Relation to dbid in sysdatabases
What if i update a dbid from 14 to 24 say for example, is there a reference
to it in any system tables in the corresponding user database itself that i
would also need to update. Just curious.
Will the db go into suspect mode or will it just continue to function as
normal ?I don't think there are references in the database itself to the dbid, but
there are plenty of references in master to the database id, including in
sysxlogins, which you also asked about. Some of the tables are
pseudo-tables, so they would probably be fine, but many are real tables what
could be corrupted if you updated a dbid.
I have never done this, so I can't tell you for sure what might break.
This query will show you all the columns in tables in master that reference
dbid:
use master
select name, object_name(id), objectproperty(id, 'tableisfake') from
syscolumns where name = 'dbid'
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:ORSqfECdDHA.372@.TK2MSFTNGP11.phx.gbl...
> What if i update a dbid from 14 to 24 say for example, is there a
reference
> to it in any system tables in the corresponding user database itself that
i
> would also need to update. Just curious.
> Will the db go into suspect mode or will it just continue to function as
> normal ?
>|||Hi Hassan,
I've never tried this, a lot of the system uses the database name as it's
key, so maybe you would get away without breaking too much
......but any update to system tables is unsupported..........
Why would you want to do this ?
Regards,
Clive Challinor [MSFT]
This posting is provided "AS IS" with no warranties, and confers no rights.
to it in any system tables in the corresponding user database itself that i
would also need to update. Just curious.
Will the db go into suspect mode or will it just continue to function as
normal ?I don't think there are references in the database itself to the dbid, but
there are plenty of references in master to the database id, including in
sysxlogins, which you also asked about. Some of the tables are
pseudo-tables, so they would probably be fine, but many are real tables what
could be corrupted if you updated a dbid.
I have never done this, so I can't tell you for sure what might break.
This query will show you all the columns in tables in master that reference
dbid:
use master
select name, object_name(id), objectproperty(id, 'tableisfake') from
syscolumns where name = 'dbid'
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:ORSqfECdDHA.372@.TK2MSFTNGP11.phx.gbl...
> What if i update a dbid from 14 to 24 say for example, is there a
reference
> to it in any system tables in the corresponding user database itself that
i
> would also need to update. Just curious.
> Will the db go into suspect mode or will it just continue to function as
> normal ?
>|||Hi Hassan,
I've never tried this, a lot of the system uses the database name as it's
key, so maybe you would get away without breaking too much
......but any update to system tables is unsupported..........
Why would you want to do this ?
Regards,
Clive Challinor [MSFT]
This posting is provided "AS IS" with no warranties, and confers no rights.
relation schema SQL Server 2005
Hi Group,
Forget my posting. :-)
I found it.
(I am just starting with SQL Server 2005.)
On SQL Server 7 I used often the nice relation schema, where I used to
draw out the whole database, especially the Foreign Keys constraints.
I found these relational schemas very handy to study an old database I
build a few years earlier that needs some updating.
I tried to find such an utility in SQL Server 2005, but cannot find it.
I did found the FK-constraints, but it is just an popup where I can
define them.
The overview such a visual schema gave was really great and I miss it.
Question: Is it gone in SQL Sevrer 2005, or do I just not know where to
look? If the latter, please guide me. :)
Thanks for your time.
Regards,
Erwin MollerErwin Moller wrote:
Quote:
Originally Posted by
Hi Group,
>
(I am just starting with SQL Server 2005.)
On SQL Server 7 I used often the nice relation schema, where I used to
draw out the whole database, especially the Foreign Keys constraints.
>
I found these relational schemas very handy to study an old database I
build a few years earlier that needs some updating.
>
I tried to find such an utility in SQL Server 2005, but cannot find it.
I did found the FK-constraints, but it is just an popup where I can
define them.
>
The overview such a visual schema gave was really great and I miss it.
>
Question: Is it gone in SQL Sevrer 2005, or do I just not know where to
look? If the latter, please guide me. :)
>
Thanks for your time.
>
Regards,
Erwin Moller
Forget my posting. :-)
I found it.
I had to make an owner of the database before I could proceed.
Did that, and now I have my nice Database Diagram.
(Note to myself: It is named 'Database Diagram', not 'Schema'.)
Sorry for the noise. :P
Regards,
Erwin Mollersql
Relation in transactional Replication
I'm using Transactional Replication.
If i publish a referencing table in my receiving DB i don't maintainig the
references. In my test with option "Merge Pubblication" i realise no
referencing but i 'm not sure for my tuning.
When i publish the articles (tables), SQL message me a warning, but i don't
understand if it's only a warning for advise me to publish all tables
related or it's a error that compromise my Replication
Thanks in advance
Stper> If i publish a referencing table in my receiving DB i don't maintainig the
> references. In my test with option "Merge Pubblication" i realise no
> referencing but i 'm not sure for my tuning.
> When i publish the articles (tables), SQL message me a warning, but i
don't
> understand if it's only a warning for advise me to publish all tables
> related or it's a error that compromise my Replication
This is just a warning.
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
If i publish a referencing table in my receiving DB i don't maintainig the
references. In my test with option "Merge Pubblication" i realise no
referencing but i 'm not sure for my tuning.
When i publish the articles (tables), SQL message me a warning, but i don't
understand if it's only a warning for advise me to publish all tables
related or it's a error that compromise my Replication
Thanks in advance
Stper> If i publish a referencing table in my receiving DB i don't maintainig the
> references. In my test with option "Merge Pubblication" i realise no
> referencing but i 'm not sure for my tuning.
> When i publish the articles (tables), SQL message me a warning, but i
don't
> understand if it's only a warning for advise me to publish all tables
> related or it's a error that compromise my Replication
This is just a warning.
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
Relation in transactional Replication
I'm using Transactional Replication.
If i publish a referencing table in my receiving DB i don't maintainig the
references. In my test with option "Merge Pubblication" i realise no
referencing but i 'm not sure for my tuning.
When i publish the articles (tables), SQL message me a warning, but i don't
understand if it's only a warning for advise me to publish all tables
related or it's a error that compromise my Replication
Thanks in advance
Stper
> If i publish a referencing table in my receiving DB i don't maintainig the
> references. In my test with option "Merge Pubblication" i realise no
> referencing but i 'm not sure for my tuning.
> When i publish the articles (tables), SQL message me a warning, but i
don't
> understand if it's only a warning for advise me to publish all tables
> related or it's a error that compromise my Replication
This is just a warning.
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
If i publish a referencing table in my receiving DB i don't maintainig the
references. In my test with option "Merge Pubblication" i realise no
referencing but i 'm not sure for my tuning.
When i publish the articles (tables), SQL message me a warning, but i don't
understand if it's only a warning for advise me to publish all tables
related or it's a error that compromise my Replication
Thanks in advance
Stper
> If i publish a referencing table in my receiving DB i don't maintainig the
> references. In my test with option "Merge Pubblication" i realise no
> referencing but i 'm not sure for my tuning.
> When i publish the articles (tables), SQL message me a warning, but i
don't
> understand if it's only a warning for advise me to publish all tables
> related or it's a error that compromise my Replication
This is just a warning.
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
Relation in transactional Replication
I'm using Transactional Replication.
If i publish a referencing table in my receiving DB i don't maintainig the
references. In my test with option "Merge Pubblication" i realise no
referencing but i 'm not sure for my tuning.
When i publish the articles (tables), SQL message me a warning, but i don't
understand if it's only a warning for advise me to publish all tables
related or it's a error that compromise my Replication
Thanks in advance
Stper> If i publish a referencing table in my receiving DB i don't maintainig the
> references. In my test with option "Merge Pubblication" i realise no
> referencing but i 'm not sure for my tuning.
> When i publish the articles (tables), SQL message me a warning, but i
don't
> understand if it's only a warning for advise me to publish all tables
> related or it's a error that compromise my Replication
This is just a warning.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
If i publish a referencing table in my receiving DB i don't maintainig the
references. In my test with option "Merge Pubblication" i realise no
referencing but i 'm not sure for my tuning.
When i publish the articles (tables), SQL message me a warning, but i don't
understand if it's only a warning for advise me to publish all tables
related or it's a error that compromise my Replication
Thanks in advance
Stper> If i publish a referencing table in my receiving DB i don't maintainig the
> references. In my test with option "Merge Pubblication" i realise no
> referencing but i 'm not sure for my tuning.
> When i publish the articles (tables), SQL message me a warning, but i
don't
> understand if it's only a warning for advise me to publish all tables
> related or it's a error that compromise my Replication
This is just a warning.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
Relation between tables
Guys,
I have a "PABX' that records all callls did..and I send it to a table and my call operator send me my account details eletronically so I can import it to another table
So in both tables I have
Date and time
Telephone number
Location(of the call city - state)
Call type(DDD, DDI e LOCAL, VC* used for cell phone)
Duration(in minutes)
For both cases i need to
-recover all rows that the operator table has and mine don't
-recover all rows that the my table has and operator don't
-recover all rows that are in both tables
But there is some problens
- the duration of the calls are different from one table to another
- the datetime is different(the day is the same but the time don't)
Any suggestions examples I would appreciate...I have spent a long time on it and did'nt got a good result..
Hi,
Let's me analyze the situation :
- The operator time is different from your local time. So the time cannot be in the matching condition. However the Date can be included in the criteria. Of course for calls between 00:00 +- n seconds u can have problems because u can have 1 day difference between the two dates. But i think we can assume this.
- The Telephone number, the call city and the call type can be included in the matching criteria.
- The duration can be also included in the matching criteria because if there is some difference you want it to be logged in the differences list.
Correct me if i am wrong.
I Created two tables : OperatorLog and PABXLog with theses columns (CallDate, PhoneNumber, Location, CallType, Duration).
Here the sql need for each request :
- all rows that the operator table has and yours don't :
Code Snippet
SELECT dbo.OperatorLog.PhoneNumber, dbo.OperatorLog.Location, dbo.OperatorLog.CallType, dbo.OperatorLog.Duration, dbo.OperatorLog.CallDateTime
FROM dbo.OperatorLog LEFT OUTER JOIN
dbo.PABXLog ON DAY(dbo.OperatorLog.CallDateTime) = DAY(dbo.PABXLog.CallDateTime) AND MONTH(dbo.OperatorLog.CallDateTime)
= MONTH(dbo.PABXLog.CallDateTime) AND YEAR(dbo.OperatorLog.CallDateTime) = YEAR(dbo.PABXLog.CallDateTime) AND
dbo.OperatorLog.Duration = dbo.PABXLog.Duration AND dbo.OperatorLog.CallType = dbo.PABXLog.CallType AND
dbo.OperatorLog.Location = dbo.PABXLog.Location AND dbo.OperatorLog.PhoneNumber = dbo.PABXLog.PhoneNumber
WHERE (dbo.PABXLog.PhoneNumber IS NULL)
- all rows that the your table has and operator don't (reversed query) :
Code Snippet
SELECT dbo.PABXLog.PhoneNumber, dbo.PABXLog.Location, dboPABXLog.CallType, dbo.PABXLog.Duration, dbo.PABXLog.CallDateTimeFROM dbo.PABXLog LEFT OUTER JOIN
dbo.OperatorLog ON DAY(dbo.PABXLog.CallDateTime) = DAY(dbo.OperatorLog.CallDateTime) AND MONTH(dbo.PABXLog.CallDateTime)
= MONTH(dbo.OperatorLog.CallDateTime) AND YEAR(dbo.PABXLog.CallDateTime) = YEAR(dbo.OperatorLog.CallDateTime) AND
dbo.PABXLog.Duration = dbo.OperatorLog.Duration AND dbo.PABXLog.CallType = dbo.OperatorLog.CallType AND
dbo.PABXLog.Location = dbo.OperatorLog.Location AND dbo.PABXLog.PhoneNumber = dbo.OperatorLog.PhoneNumber
WHERE (dbo.OperatorLog.PhoneNumber IS NULL)
- All matching rows :
Code Snippet
SELECT dbo.OperatorLog.PhoneNumber, dbo.OperatorLog.Location, dbo.OperatorLog.CallType, dbo.OperatorLog.Duration, dbo.OperatorLog.CallDateTime
FROM dbo.OperatorLog LEFT OUTER JOIN
dbo.PABXLog ON DAY(dbo.OperatorLog.CallDateTime) = DAY(dbo.PABXLog.CallDateTime) AND MONTH(dbo.OperatorLog.CallDateTime)
= MONTH(dbo.PABXLog.CallDateTime) AND YEAR(dbo.OperatorLog.CallDateTime) = YEAR(dbo.PABXLog.CallDateTime) AND
dbo.OperatorLog.Duration = dbo.PABXLog.Duration AND dbo.OperatorLog.CallType = dbo.PABXLog.CallType AND
dbo.OperatorLog.Location = dbo.PABXLog.Location AND dbo.OperatorLog.PhoneNumber = dbo.PABXLog.PhoneNumber
HTH.
Hayder Marzouk
|||Thanks
Yes...you're correct
I did something similar...I'm gonna test it tomorrow and give you the answer..tahnks a lot
|||OK thanks...Did'nt work for all registries but it's a good beginning...Relation between reads and duration
I have a query that performs strangely under different conditions as
follows:
When ran normally, it uses a clustered index scan. From the Profiler and
the IO statistics, this makes ~250,000 reads. This takes about 4 minutes to
run.
When ran with an index hint, it uses a clusted index seek and makes a
bookmark lookup of about 180,000 rows. Reads are ~1,000,000. However, this
takes only 1 minute to run.
Why is it like that? I have tried defragging the clustered index, but to no
avail. Running another query that also makes a clustered index scan makes
~250,000 reads, and takes ~ 4 minutes too.
What else can I check? Why does a query that makes less reads takes longer
to run than another that makes 4 times as many reads? I would like to avoid
having to force the hint, if I can help it.
Thx in advance.
Regards
Ray MondRay,
Do the 250K reads from the clustered index involve wide rows, so they
would access many data pages (maybe even 250,000)? With the hint, maybe
most of the million reads are from a relatively small number of pages in
the nonclustered index, and therefore from memory, not disk.
There are lots of factors involved, and sometimes there just isn't
enough information for the optimizer to choose the best plan. If you
provide some more information about your query (such as create table
statements, indexes, the actual queries and maybe even the good and bad
plans you're seeing), maybe we can give more specific help.
SK
Ray Mond wrote:
>I have a query that performs strangely under different conditions as
>follows:
>When ran normally, it uses a clustered index scan. From the Profiler and
>the IO statistics, this makes ~250,000 reads. This takes about 4 minutes t
o
>run.
>When ran with an index hint, it uses a clusted index seek and makes a
>bookmark lookup of about 180,000 rows. Reads are ~1,000,000. However, thi
s
>takes only 1 minute to run.
>Why is it like that? I have tried defragging the clustered index, but to n
o
>avail. Running another query that also makes a clustered index scan makes
>~250,000 reads, and takes ~ 4 minutes too.
>What else can I check? Why does a query that makes less reads takes longer
>to run than another that makes 4 times as many reads? I would like to avoi
d
>having to force the hint, if I can help it.
>Thx in advance.
>Regards
>Ray Mond
>
>|||Steve,
Average row size is 1,248 bytes. You are probably right, in that the
execution plan using the non-clustered index is reading data from pages
already in memory, because the data rows requested by the query are bunched
up together and not randomly distributed. Is there a way to know the actual
number of unique pages actually looked up by a query, short of peeking into
the contents of each page?
Thanks.
Regards
Ray Mond
"Steve Kass" <skass@.drew.edu> wrote in message
news:OBrtou$BEHA.2620@.TK2MSFTNGP12.phx.gbl...
> Ray,
> Do the 250K reads from the clustered index involve wide rows, so they
> would access many data pages (maybe even 250,000)? With the hint, maybe
> most of the million reads are from a relatively small number of pages in
> the nonclustered index, and therefore from memory, not disk.
> There are lots of factors involved, and sometimes there just isn't
> enough information for the optimizer to choose the best plan. If you
> provide some more information about your query (such as create table
> statements, indexes, the actual queries and maybe even the good and bad
> plans you're seeing), maybe we can give more specific help.
> SK
> Ray Mond wrote:
>
to
this
no
makes
longer
avoid
>|||I don't know of a way, though maybe there's something in profiler that
I've never seen. I don't recall the details now, but I think the beta 1
release of SQL Server 2005 had some extra show statistics io column -
maybe the situation will improve in future versions of SQL Server.
SK
Ray Mond wrote:
>Steve,
>Average row size is 1,248 bytes. You are probably right, in that the
>execution plan using the non-clustered index is reading data from pages
>already in memory, because the data rows requested by the query are bunched
>up together and not randomly distributed. Is there a way to know the actua
l
>number of unique pages actually looked up by a query, short of peeking into
>the contents of each page?
>Thanks.
>
>|||Steve,
Thx. I'm using the SET STATISTICS IO output to get a ballpark figure of the
number of unique pages read.
Regards
Ray Mond
"Steve Kass" <skass@.drew.edu> wrote in message
news:%23RMkgJFCEHA.3064@.tk2msftngp13.phx.gbl...
> I don't know of a way, though maybe there's something in profiler that
> I've never seen. I don't recall the details now, but I think the beta 1
> release of SQL Server 2005 had some extra show statistics io column -
> maybe the situation will improve in future versions of SQL Server.
> SK
> Ray Mond wrote:
>
bunched
actual
into
>sql
follows:
When ran normally, it uses a clustered index scan. From the Profiler and
the IO statistics, this makes ~250,000 reads. This takes about 4 minutes to
run.
When ran with an index hint, it uses a clusted index seek and makes a
bookmark lookup of about 180,000 rows. Reads are ~1,000,000. However, this
takes only 1 minute to run.
Why is it like that? I have tried defragging the clustered index, but to no
avail. Running another query that also makes a clustered index scan makes
~250,000 reads, and takes ~ 4 minutes too.
What else can I check? Why does a query that makes less reads takes longer
to run than another that makes 4 times as many reads? I would like to avoid
having to force the hint, if I can help it.
Thx in advance.
Regards
Ray MondRay,
Do the 250K reads from the clustered index involve wide rows, so they
would access many data pages (maybe even 250,000)? With the hint, maybe
most of the million reads are from a relatively small number of pages in
the nonclustered index, and therefore from memory, not disk.
There are lots of factors involved, and sometimes there just isn't
enough information for the optimizer to choose the best plan. If you
provide some more information about your query (such as create table
statements, indexes, the actual queries and maybe even the good and bad
plans you're seeing), maybe we can give more specific help.
SK
Ray Mond wrote:
>I have a query that performs strangely under different conditions as
>follows:
>When ran normally, it uses a clustered index scan. From the Profiler and
>the IO statistics, this makes ~250,000 reads. This takes about 4 minutes t
o
>run.
>When ran with an index hint, it uses a clusted index seek and makes a
>bookmark lookup of about 180,000 rows. Reads are ~1,000,000. However, thi
s
>takes only 1 minute to run.
>Why is it like that? I have tried defragging the clustered index, but to n
o
>avail. Running another query that also makes a clustered index scan makes
>~250,000 reads, and takes ~ 4 minutes too.
>What else can I check? Why does a query that makes less reads takes longer
>to run than another that makes 4 times as many reads? I would like to avoi
d
>having to force the hint, if I can help it.
>Thx in advance.
>Regards
>Ray Mond
>
>|||Steve,
Average row size is 1,248 bytes. You are probably right, in that the
execution plan using the non-clustered index is reading data from pages
already in memory, because the data rows requested by the query are bunched
up together and not randomly distributed. Is there a way to know the actual
number of unique pages actually looked up by a query, short of peeking into
the contents of each page?
Thanks.
Regards
Ray Mond
"Steve Kass" <skass@.drew.edu> wrote in message
news:OBrtou$BEHA.2620@.TK2MSFTNGP12.phx.gbl...
> Ray,
> Do the 250K reads from the clustered index involve wide rows, so they
> would access many data pages (maybe even 250,000)? With the hint, maybe
> most of the million reads are from a relatively small number of pages in
> the nonclustered index, and therefore from memory, not disk.
> There are lots of factors involved, and sometimes there just isn't
> enough information for the optimizer to choose the best plan. If you
> provide some more information about your query (such as create table
> statements, indexes, the actual queries and maybe even the good and bad
> plans you're seeing), maybe we can give more specific help.
> SK
> Ray Mond wrote:
>
to
this
no
makes
longer
avoid
>|||I don't know of a way, though maybe there's something in profiler that
I've never seen. I don't recall the details now, but I think the beta 1
release of SQL Server 2005 had some extra show statistics io column -
maybe the situation will improve in future versions of SQL Server.
SK
Ray Mond wrote:
>Steve,
>Average row size is 1,248 bytes. You are probably right, in that the
>execution plan using the non-clustered index is reading data from pages
>already in memory, because the data rows requested by the query are bunched
>up together and not randomly distributed. Is there a way to know the actua
l
>number of unique pages actually looked up by a query, short of peeking into
>the contents of each page?
>Thanks.
>
>|||Steve,
Thx. I'm using the SET STATISTICS IO output to get a ballpark figure of the
number of unique pages read.
Regards
Ray Mond
"Steve Kass" <skass@.drew.edu> wrote in message
news:%23RMkgJFCEHA.3064@.tk2msftngp13.phx.gbl...
> I don't know of a way, though maybe there's something in profiler that
> I've never seen. I don't recall the details now, but I think the beta 1
> release of SQL Server 2005 had some extra show statistics io column -
> maybe the situation will improve in future versions of SQL Server.
> SK
> Ray Mond wrote:
>
bunched
actual
into
>sql
Relation between datawarehousing and OLAP
I am currently looking to get some insight into the BI-world (in the context
of SQL Server), and I find the distinction between datawarehousing and OLAP
somewhat vague.
My say would be that OLAP could be a part of a data warehouse, but it is not
clear to me where the between the two is to be drawn:
What are your thoughts?
Tor B?dshaug
tor.badshaug(AT)bekk.noOLAP is the analytical portion those overlays you warehouse. It allows
for extremely fast & easy access to your warehouse for both IT and more
importantly your NON-IT personnel.
For the most part business analysts are not required to be proficient
in warehouse programming languages like SQL. So, OLAP data cubes give
them what they need without wasting time (money) and it aids in
insuring consistency & integrity of the data.|||The flow of data is typically as follows:
Production Database Servers -> (Staging DB/DTS/SSIS) -> Relational Data
Warehouse -> Analysis Services (OLAP) -> End User Reporting Application
The relational data warehouse is used to store data in a de-normalised
format (dimensional model), optimised for query. The relational data
warehouse can be used by reporting applications, but you will probably do
most of your reporting from the Analysis Services database.
Analysis Services databases are fast, intelligent and use an efficient,
compressed storage structure. Many reporting tools have been developed that
make use of OLAP databases - The metadata exposed by analysis services
enables them to understand the data. This allows you to buy a 3rd party
reporting tool such as Cognos, Business Objects, Pro Clarity and set them up
to connect to your analysis services database with minimal configuration.
These tools offer great flexibility and should allow end users (with
appropriate training) to answer 99% of their own queries. Most these tools
have simple to use, drag and drop interfaces.
A simple example of intelligence - You can define a display format for a
measure so it displays with a ￡ (or $) sign and comma separators - client
tools can read this metadata and know how to display the information to the
user. This example is really only the tip of the ice berg - thing get more
interesting with calculated measures, drill throughs and drill actions.
Another example is that OLAP databases are aggregate aware - creating
aggregates is possible in a relational database, but difficult to manage and
typically involves hard coding applications to use aggregate tables (unless
you write your own aggregate navigator)
Analysis Services is a part of the data warehouse - you can have a data
warehouse without an Analysis Services/OLAP database. You can also have an
analysis services database without the relational data warehouse component,
although this approach is not recommended. Some queries are still easier to
answer from a relational database and the relational database provides a
level of insurance if there is a problem with your OLAP database.
Re-Building your Analysis Services database is a simple task if you have a
relational data warehouse and a cube definition. Also, your task of
developing the Analysis Services database is greatly simplified if your data
is structured in a star/snowflake schema in a relational data warehouse.
Hope this helps,
David
PS
The BOL tutorials for Analysis Services 2005 are quite good to get you up to
speed with the basics of SSAS 2005.
David Wiseman
MCSE, MCSA, MCDBA
http://www.wisesoft.co.uk
"Tor B?dshaug" wrote:
> I am currently looking to get some insight into the BI-world (in the conte
xt
> of SQL Server), and I find the distinction between datawarehousing and OLA
P
> somewhat vague.
> My say would be that OLAP could be a part of a data warehouse, but it is n
ot
> clear to me where the between the two is to be drawn:
> What are your thoughts?
> Tor B?dshaug
> tor.badshaug(AT)bekk.no
of SQL Server), and I find the distinction between datawarehousing and OLAP
somewhat vague.
My say would be that OLAP could be a part of a data warehouse, but it is not
clear to me where the between the two is to be drawn:
What are your thoughts?
Tor B?dshaug
tor.badshaug(AT)bekk.noOLAP is the analytical portion those overlays you warehouse. It allows
for extremely fast & easy access to your warehouse for both IT and more
importantly your NON-IT personnel.
For the most part business analysts are not required to be proficient
in warehouse programming languages like SQL. So, OLAP data cubes give
them what they need without wasting time (money) and it aids in
insuring consistency & integrity of the data.|||The flow of data is typically as follows:
Production Database Servers -> (Staging DB/DTS/SSIS) -> Relational Data
Warehouse -> Analysis Services (OLAP) -> End User Reporting Application
The relational data warehouse is used to store data in a de-normalised
format (dimensional model), optimised for query. The relational data
warehouse can be used by reporting applications, but you will probably do
most of your reporting from the Analysis Services database.
Analysis Services databases are fast, intelligent and use an efficient,
compressed storage structure. Many reporting tools have been developed that
make use of OLAP databases - The metadata exposed by analysis services
enables them to understand the data. This allows you to buy a 3rd party
reporting tool such as Cognos, Business Objects, Pro Clarity and set them up
to connect to your analysis services database with minimal configuration.
These tools offer great flexibility and should allow end users (with
appropriate training) to answer 99% of their own queries. Most these tools
have simple to use, drag and drop interfaces.
A simple example of intelligence - You can define a display format for a
measure so it displays with a ￡ (or $) sign and comma separators - client
tools can read this metadata and know how to display the information to the
user. This example is really only the tip of the ice berg - thing get more
interesting with calculated measures, drill throughs and drill actions.
Another example is that OLAP databases are aggregate aware - creating
aggregates is possible in a relational database, but difficult to manage and
typically involves hard coding applications to use aggregate tables (unless
you write your own aggregate navigator)
Analysis Services is a part of the data warehouse - you can have a data
warehouse without an Analysis Services/OLAP database. You can also have an
analysis services database without the relational data warehouse component,
although this approach is not recommended. Some queries are still easier to
answer from a relational database and the relational database provides a
level of insurance if there is a problem with your OLAP database.
Re-Building your Analysis Services database is a simple task if you have a
relational data warehouse and a cube definition. Also, your task of
developing the Analysis Services database is greatly simplified if your data
is structured in a star/snowflake schema in a relational data warehouse.
Hope this helps,
David
PS
The BOL tutorials for Analysis Services 2005 are quite good to get you up to
speed with the basics of SSAS 2005.
David Wiseman
MCSE, MCSA, MCDBA
http://www.wisesoft.co.uk
"Tor B?dshaug" wrote:
> I am currently looking to get some insight into the BI-world (in the conte
xt
> of SQL Server), and I find the distinction between datawarehousing and OLA
P
> somewhat vague.
> My say would be that OLAP could be a part of a data warehouse, but it is n
ot
> clear to me where the between the two is to be drawn:
> What are your thoughts?
> Tor B?dshaug
> tor.badshaug(AT)bekk.no
relation & query
Hello,
I'm relative new to sql and databases and the last few weeks I learned
myself a lot. I'm trying to make a hotel reservation application.
I have a database with a table Booking, a table Room, a table
RoomsPerBooking. So a booking contains date/time etc and a field
RoomsPerBookingID. The table RoomsPerBooking contains number of
persons, unitprice etc. and a field ID and a field RoomID. The table
Room contains data like name, notes etc.
now i have two questions:
First about relations:
The table Booking has relationship: PK table RoomsPerBooking - ID <-->
FK table Booking - RoomsPerBookingID.
The table RoomsPerBookingID has relationship: PK table Room - ID <-->
FK table RoomsPerBooking - RoomID
Is this relationset good for my purpose? I think it is, but I am not
sure.
The second question is:
How do I get available rooms per night
I came this far... what are the "some statements"?
CREATE PROCEDURE dbo.GetAvailableRooms
(
@.BeginDate DATETIME,
@.EndDate DATETIME
)
AS
SELECT Room.*
FROM Room
WHERE Room.ID NOT IN (
SELECT DISTINCT room.ID
FROM Room room JOIN RoomsPerBooking roomsPerBooking
ON room.ID = roomsPerBooking.RoomID
--Some statements--
WHERE booking.FromDate <= @.EndDate
AND booking.ToDate >= @.BeginDate)
GOPlease post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are. Sample data is also a good idea, along with clear
specifications.
Why do you have multiple names for the same data element? Why do you
use a singular name for a set of Rooms? Why did you use id and
room_id when the standard way of referencing a room is a "room number"?
Why do you use aliases that are the same as the base table names?
After you clean up the schema a bit, look at using a Calendar table.
Subscribe to:
Posts (Atom)