Regarding indexes (I think)

I have an issue where i can do the following
Select count(*) from table and it returns 181,000
Select count(*) from table where ncolumn is not null and it returns 174,000
Select count(*) from table where ncolumn is null and it returns 153,000
Now, those numbers should at up to the count of the top one,
I've ran dbcc checkdb, and it returns no errors. I've also tried dbcc
dbreindex with the same problem.
Anyone have any suggestions?Try,
DBCC UPDATEUSAGE ('db_name', 'table_name')
go
select count(*), count(case when ncolumn is not null then 1 end),
count(ncolumn is null then 1 end)
from table
go
AMB
"Gary" wrote:

> I have an issue where i can do the following
> Select count(*) from table and it returns 181,000
> Select count(*) from table where ncolumn is not null and it returns 174,00
0
> Select count(*) from table where ncolumn is null and it returns 153,000
> Now, those numbers should at up to the count of the top one,
> I've ran dbcc checkdb, and it returns no errors. I've also tried dbcc
> dbreindex with the same problem.
> Anyone have any suggestions?
>
>|||Correction,
DBCC UPDATEUSAGE ('db_name', 'table_name')
go
select count(*), count(case when ncolumn is not null then 1 end),
count(case when ncolumn is null then 1 end)
from table
go
AMB
"Alejandro Mesa" wrote:
> Try,
> DBCC UPDATEUSAGE ('db_name', 'table_name')
> go
> select count(*), count(case when ncolumn is not null then 1 end),
> count(ncolumn is null then 1 end)
> from table
> go
>
> AMB
> "Gary" wrote:
>|||Are there inserts into the table between each select?
"Gary" <clgary@.yahoo.com> wrote in message
news:unQeCtRGFHA.1188@.tk2msftngp13.phx.gbl...
> I have an issue where i can do the following
> Select count(*) from table and it returns 181,000
> Select count(*) from table where ncolumn is not null and it returns
174,000
> Select count(*) from table where ncolumn is null and it returns 153,000
> Now, those numbers should at up to the count of the top one,
> I've ran dbcc checkdb, and it returns no errors. I've also tried dbcc
> dbreindex with the same problem.
> Anyone have any suggestions?
>|||Thanks for the help, it sort of worked
Select count(*) from table and it returns 181,000
Select count(*) from table where ncolumn is not null and it returns 174,000
Select count(*) from table where ncolumn is null and it returns 153,000
Same results there, however,
select count(*), count(case when ncolumn is not null then 1 end),
count(ncolumn is null then 1 end)
from table
returns
181,000 159,000 22,000
which is correct,.but I'm still not sure whats going on there.
Another poster I saw asked if there were inserts happening during the
selects, and its not.
So, I'm still baffled. Something else to note, even if i run them
individually, instead of all 3 as a batch in QA, it still returns the wrong
count.
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:61BE7216-D2F7-435D-B1E4-656E4D683C5E@.microsoft.com...
> Try,
> DBCC UPDATEUSAGE ('db_name', 'table_name')
> go
> select count(*), count(case when ncolumn is not null then 1 end),
> count(ncolumn is null then 1 end)
> from table
> go
>
> AMB
> "Gary" wrote:
>
174,000|||If you are running parallel queries:
http://support.microsoft.com/kb/814509/en-us
http://support.microsoft.com/default.aspx/kb/838166 (for getting the 878
build).
S. L.
"Gary" <clgary@.yahoo.com> wrote in message
news:unQeCtRGFHA.1188@.tk2msftngp13.phx.gbl...
>I have an issue where i can do the following
> Select count(*) from table and it returns 181,000
> Select count(*) from table where ncolumn is not null and it returns
> 174,000
> Select count(*) from table where ncolumn is null and it returns 153,000
> Now, those numbers should at up to the count of the top one,
> I've ran dbcc checkdb, and it returns no errors. I've also tried dbcc
> dbreindex with the same problem.
> Anyone have any suggestions?
>|||Perhaps uncommitted inserts or updates are being held open by other
processes and your current transaction isolation level allows your query to
include "dirty reads". Sometimes a large number of updates (100,000s) can
take 1/2 hour or more to rollback if a batch transaction fails. Try running
the following, it will lock any records as it reads and will not include
"dirty reads".
Run sp_lock to see what other processes may have records locked. Also, if
the queries seems to take longer than usual to run using this locking
method, then you can use sp_who2 to determine if your SPID is blocked by
another process.
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
Select count(*) from table
Select count(*) from table where ncolumn is not null
Select count(*) from table where ncolumn is null
"Gary" <clgary@.yahoo.com> wrote in message
news:uDpR8ISGFHA.4004@.tk2msftngp13.phx.gbl...
> Thanks for the help, it sort of worked
> Select count(*) from table and it returns 181,000
> Select count(*) from table where ncolumn is not null and it returns
174,000
> Select count(*) from table where ncolumn is null and it returns 153,000
> Same results there, however,
> select count(*), count(case when ncolumn is not null then 1 end),
> count(ncolumn is null then 1 end)
> from table
> returns
> 181,000 159,000 22,000
> which is correct,.but I'm still not sure whats going on there.
> Another poster I saw asked if there were inserts happening during the
> selects, and its not.
> So, I'm still baffled. Something else to note, even if i run them
> individually, instead of all 3 as a batch in QA, it still returns the
wrong
> count.
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in
message
> news:61BE7216-D2F7-435D-B1E4-656E4D683C5E@.microsoft.com...
> 174,000
153,000
>|||Thanks,
That seems to do the trick
"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
wrote in message news:OOqE3YSGFHA.2296@.TK2MSFTNGP15.phx.gbl...
> If you are running parallel queries:
> http://support.microsoft.com/kb/814509/en-us
> http://support.microsoft.com/default.aspx/kb/838166 (for getting the 878
> build).
> S. L.
> "Gary" <clgary@.yahoo.com> wrote in message
> news:unQeCtRGFHA.1188@.tk2msftngp13.phx.gbl...
>sql

reg error in sql

CREATE TABLE [dbo].[ContactInfo] (
[ContactID] [int] IDENTITY (1, 1) NOT NULL ,
[Title] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FirstName] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[MiddleName] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LastName] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[JobTitle] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Company] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Website] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[OfficePhone] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[HomePhone] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Mobile] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Fax] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[OfficialEmail] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PersonalEmail] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL

) ON [PRIMARY]

In the above code i get the error"Line 3: Incorrect syntax near 'COLLATE'. " while compiling in sql

What is the error?

GO

i just ran the whole thing in my SQL Server (Sql Server 2000) and it worked fine.

hth,

mcm

|||

I ran this script in SQL server 2005 & it run without any problems :-) which version of SQL server are you using ?

Referential Integrity with empty string and ZERO

Hi,
How could I define referential integrity using FK constraint which allow me to have empty string/ZERO number instead of NULL value ?

Thank you

If you place a foreign key constraint on a column, zero is only a valid value if there is a zero in foreign key table. The same applies to empty strings.

If the foreign key value of a particular row is unknown (or it doesnt have one), that's where the use of null comes in. Of course a NOT NULL constraint used in conjunction with the FK constraint can prevent the use of nulls (forcing a valid FK value).

To the best of my knowledge, it's not possible to change this behavior.

Referential Integrity with empty string and ZERO

Hi,
How could I define referential integrity using FK constraint which allow me to have empty string/ZERO number instead of NULL value ?

Thank you

If you place a foreign key constraint on a column, zero is only a valid value if there is a zero in foreign key table. The same applies to empty strings.

If the foreign key value of a particular row is unknown (or it doesnt have one), that's where the use of null comes in. Of course a NOT NULL constraint used in conjunction with the FK constraint can prevent the use of nulls (forcing a valid FK value).

To the best of my knowledge, it's not possible to change this behavior.

Referential Integrity constraints

Dear Sir,
Yesterday during an Interview...i have faced some problem. I would like to
clearify that NN (NOT NULL), ND (NOT DUPLICATE) AND NC(NOT CHANGE)
constraints work ...when we define a Primary Key on any Table column. If,
Yes( as i hope) then how and what's the meaning of this NC. Can You Plz. giv
e
me briefly explain and can we use this in ORACLE .> ...me briefly explain and can we use this in ORACLE .
This is a SQL Server forum. You probably get (better) help in an Oracle foru
m.
In SQL Server, defining a PK doesn't prohibit you to change the value of the
PK column for one or
several rows.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Sanjeev Kumar Sinha" <Sanjeev Kumar Sinha@.discussions.microsoft.com> wrote
in message
news:E7240299-73E3-4C56-891D-66F4CB533174@.microsoft.com...
> Dear Sir,
> Yesterday during an Interview...i have faced some problem. I would like to
> clearify that NN (NOT NULL), ND (NOT DUPLICATE) AND NC(NOT CHANGE)
> constraints work ...when we define a Primary Key on any Table column. If,
> Yes( as i hope) then how and what's the meaning of this NC. Can You Plz. g
ive
> me briefly explain and can we use this in ORACLE .
>
>|||Yeah, well now I would like it to :)
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OHOkklxHFHA.156@.TK2MSFTNGP10.phx.gbl...
> This is a SQL Server forum. You probably get (better) help in an Oracle
> forum.
> In SQL Server, defining a PK doesn't prohibit you to change the value of
> the PK column for one or several rows.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Sanjeev Kumar Sinha" <Sanjeev Kumar Sinha@.discussions.microsoft.com>
> wrote in message
> news:E7240299-73E3-4C56-891D-66F4CB533174@.microsoft.com...
>|||You have to talk to the ANSI SQL committee about that, Louis. :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
news:OCdomJ1HFHA.3076@.tk2msftngp13.phx.gbl...
> Yeah, well now I would like it to :)
> --
> ----
--
> Louis Davidson - drsql@.hotmail.com
> SQL Server MVP
> Compass Technology Management - www.compass.net
> Pro SQL Server 2000 Database Design - http://www.apress.com/book/bookDisp...r />
l?bID=266
> Blog - http://spaces.msn.com/members/drsql/
> Note: Please reply to the newsgroups only unless you are interested in con
sulting services. All
> other replies may be ignored :)
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:OHOkklxHFHA.156@.TK2MSFTNGP10.phx.gbl...

Reference Empty Data Set

I have a dataset that sometimes is empty. I have it as a detail row in a table on the report, but since the dataset is null the row does not appear. What I would like to do is have it say "No results" if there are no results, otherwise have it show the results.

I tried to add a textbox and reference the textbox on the table, but it was a no go.

Here is what I had in my textbox

<code>
=code.noRows(ReportItems!table8!textbox30)
</code>

and this is what I was using in the code

<code>
Function noRows(byVal result) as string
if result = "" then
noRows = "NO Results"
else
noRows = result
end if
end function
</code>

Can anyone assist me with how to do this?

Thanks

Try this:

Data regions (matrix, table, list, chart) have a so-called NoRows property. If set, this is the message to display in a textbox (that replaces the data region when no rows of data are available).

Note: the NoRows property is available in the VS properties window.

-- Robert

|||When I use that my header disappears. Is there a way around this?
I need the "No Results" to appear on the detail line and the header to stay regardless.

Thanks

|||

No. If you use the NoRows message property, it will replace the entire table.

-- Robert