What Is Cardinality
Jul 23, 2005
-----Cross posted on comp.databases.ms-sqlserver and
microsoft.public.sqlserver.programming-----
Can someone please explain cardinality to me? I am having a problem with an
insert from a webpage.
When a user adds a venue with an event I need both tables to populate.
Currently only the event table populates the right info and the venue does
not. I am using SELECT SCOPE IDENTITY to get the last inserted venue to
associate with each event. That is to say that each event has a column for
venue and that value is incorrectly being added as 0, the default value. The
venue is not getting added.
It worked fine until I got to ID number 87 which was the 62nd venue.
When I look at my table structure in php myadmin my cardinality is different
depending on what field. ID is 67 and Address is 6. Something seems wierd,
but I don't understand cardinality so much.
Thanks!
View 2 Replies
Sep 7, 2006
The problem is in one-to-many cardinality and UPDATE statement :
( SQL server 2005 developer edition )
create table #table1( id int, firstCol int)
insert #table1 values ( 1,0)
insert #table1 values ( 2,0)
go
create table #table2( id int, secondCol int)
insert #table2 values ( 1,10)
insert #table2 values ( 1,20)
insert #table2 values ( 1,30)
insert #table2 values ( 3,100)
go
-----------------------------------------------------------------------------
1. shape UPDATE
update #table1 set firstCol = firstCol +( select secondCol from #table2
where #table1.id = #table2.id )
return error message :
Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.
2. shape UPDATE
update r set firstCol = firstCol + s.secondCol
from #table1 r
join ( select id,secondCol from #table2 ) s
on r.id = s.id
return message : (1 row(s) affected)
and #table1 is updated only one of row ( generally random ) from #table2 for ID = 1
select * from #table1
id firstCol
----------- -----------
1 10
2 0
3. shape UPDATE
update #table1 set firstCol = firstCol + secondCol
from #table2 where #table1.id = #table2.id
return message : (1 row(s) affected)
and #table1 is updated only one of row ( generally random ) from #table2 for ID = 1
-----------------------------------------------------------------------------
Is it normal ? I expected error message or summary value for ID = 1 from #table2 .
View 1 Replies
View Related
Sep 14, 2006
sql server 2005
sql server mangement studio
Hi I am using the databse diagrammer to create a database.
creating the tables and fields is no problem but there seems to be a lack of choice with joins and I cannot see how to set cardinality.
any help appreciated.
B
View 1 Replies
View Related
May 8, 2007
I am trying to understand constraints and minimum cardinality.
In a relationship between 2 tables, t1 and t2, with a parent to child relationship of
1 to 1 or more
how is the minimum cardinality enforced?
If both sides of the relationship require at least one occurrence, how would the insert be done? How can you insert into table t1 when it has a constraint that there must be an occurrence in table t2, or vice versa?
Any help in understanding this is greatly appreciated!
View 4 Replies
View Related
Jul 22, 2013
DELETE FROM [SCS_NAV2009R2_PROD].[dbo].[Payroll Ledger Entry]
gives this error
Msg 9002, Level 17, State 4, Line 1
The transaction log for database 'SCS_NAV2009R2_PROD' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases
~ 7,000,000 records
now i have set the log to autogrow and set the max size to 2tb
what else can i do to get this going?
View 10 Replies
View Related
Mar 27, 2006
Hello all. I'm running SQL Server 2000 and I'm trying to get a veryfew, recent rows of data from a table based on an indexed datetimecolumn. Here's my predicate:where order_date > dateadd(hour, -1, getdate())i.e. everything more recent than one hour ago. This corresponds to the3 or 4 rows in which I'm interested. I have order_date indexed and Ihave current statistics. When I check the explain plan for this queryI see expected rows returned: 114,000. When I go on to join to severalother tables I end up with unnecessary hash joins -- due to theinaccurate cardinality estimates on this table.However, if I use the following predicate (which corresponds to datawithin the last 3 days):where order_date > '2006-03-24'then I see an estimated rows returned: 6 -- which is pretty accurate.[color=blue]>From there the rest of the query's explain plan falls together nicely.[/color]So my question is: how do I get the optimizer to realize that one hourago is pretty recent?Many thanks,Scott
View 2 Replies
View Related
May 18, 2015
Over the weekend I decided to give it the ability to do a case sensitive character swap. Updating the code was pretty straight forward but when I was through, I noticed that I was getting Cardinality Estimate warnings that I wasn't getting before.
Anyway, here is some test data and two versions of the executed SQL (the base code is all dynamic and the two code versions are the result of toggling the @MatchCase parameter).
/* ========================================
CREATE TABLE
======================================== */
CREATE TABLE [dbo].[PersonInfoSmall](
[PersonID] [BIGINT] NOT NULL,
[FirstName] [NVARCHAR](50) NOT NULL,
[MiddleName] [NVARCHAR](50) NULL,
[LastName] [NVARCHAR](50) NOT NULL,
[code]....
View 8 Replies
View Related
