Indexes and Deadlocks: null

Showing posts with label null. Show all posts

Friday, March 30, 2012

INFORMATION_SCHEMA again

Why isn't INFORMATION_SCHEMA.TABLES seeing the master database tables?
use tempdb
go
if object_id('temp_proc') is not null drop proc temp_proc
go
create proc temp_proc
as
SELECT TABLE_CATALOG , TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE
FROM INFORMATION_SCHEMA.TABLES
go
exec tempdb.dbo.temp_proc
use master
exec tempdb.dbo.temp_procThe procedure is still executed in the context of tempdb, not of master. I
think it would be very unexpected behavior if you changed information_schema
views to real user tables, and calling pubs.dbo.someprocedure from tempdb,
tried to find an authors table in tempdb?
A
"Jay" <nospan@.nospam.org> wrote in message
news:uihhguqCIHA.1168@.TK2MSFTNGP02.phx.gbl...
> Why isn't INFORMATION_SCHEMA.TABLES seeing the master database tables?
> use tempdb
> go
> if object_id('temp_proc') is not null drop proc temp_proc
> go
> create proc temp_proc
> as
> SELECT TABLE_CATALOG , TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE
> FROM INFORMATION_SCHEMA.TABLES
> go
> exec tempdb.dbo.temp_proc
> use master
> exec tempdb.dbo.temp_proc
>|||Huh?
Are you saying that a procedure will always execute within the database it
was created in?
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:utSMLyqCIHA.1204@.TK2MSFTNGP03.phx.gbl...
> The procedure is still executed in the context of tempdb, not of master.
> I think it would be very unexpected behavior if you changed
> information_schema views to real user tables, and calling
> pubs.dbo.someprocedure from tempdb, tried to find an authors table in
> tempdb?
> A
>
>
> "Jay" <nospan@.nospam.org> wrote in message
> news:uihhguqCIHA.1168@.TK2MSFTNGP02.phx.gbl...
>> Why isn't INFORMATION_SCHEMA.TABLES seeing the master database tables?
>> use tempdb
>> go
>> if object_id('temp_proc') is not null drop proc temp_proc
>> go
>> create proc temp_proc
>> as
>> SELECT TABLE_CATALOG , TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE
>> FROM INFORMATION_SCHEMA.TABLES
>> go
>> exec tempdb.dbo.temp_proc
>> use master
>> exec tempdb.dbo.temp_proc
>>
>|||Would something else make more sense?
USE Pubs;
GO
CREATE PROCEDURE dbo.GetAuthors
AS
SELECT * FROM Authors;
GO
USE master;
GO
EXEC Pubs.dbo.GetAuthors;
You expect this to return rows from master.dbo.Authors?
"Jay" <nospan@.nospam.org> wrote in message
news:uqcmv$qCIHA.4228@.TK2MSFTNGP02.phx.gbl...
> Huh?
> Are you saying that a procedure will always execute within the database it
> was created in?
>
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
> message news:utSMLyqCIHA.1204@.TK2MSFTNGP03.phx.gbl...
>> The procedure is still executed in the context of tempdb, not of master.
>> I think it would be very unexpected behavior if you changed
>> information_schema views to real user tables, and calling
>> pubs.dbo.someprocedure from tempdb, tried to find an authors table in
>> tempdb?
>> A
>>
>>
>> "Jay" <nospan@.nospam.org> wrote in message
>> news:uihhguqCIHA.1168@.TK2MSFTNGP02.phx.gbl...
>> Why isn't INFORMATION_SCHEMA.TABLES seeing the master database tables?
>> use tempdb
>> go
>> if object_id('temp_proc') is not null drop proc temp_proc
>> go
>> create proc temp_proc
>> as
>> SELECT TABLE_CATALOG , TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE
>> FROM INFORMATION_SCHEMA.TABLES
>> go
>> exec tempdb.dbo.temp_proc
>> use master
>> exec tempdb.dbo.temp_proc
>>
>>
>|||I expected to be able to write a general purpose procedure that would detect
the catalog of the database it was executed in. In the example you gave, I
would expect it to complain that there was no Authors table, not go back to
the database the procedure was created in and read the Authors table in
there while the specified database context was master.
However, looking back at the nightly maintenance procedure I wrote, do do
this I have to get cute with dynamic sql and embeded 'use' statemants.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:e%23NBYCrCIHA.4472@.TK2MSFTNGP05.phx.gbl...
> Would something else make more sense?
>
> USE Pubs;
> GO
> CREATE PROCEDURE dbo.GetAuthors
> AS
> SELECT * FROM Authors;
> GO
> USE master;
> GO
> EXEC Pubs.dbo.GetAuthors;
>
> You expect this to return rows from master.dbo.Authors?
>
> "Jay" <nospan@.nospam.org> wrote in message
> news:uqcmv$qCIHA.4228@.TK2MSFTNGP02.phx.gbl...
>> Huh?
>> Are you saying that a procedure will always execute within the database
>> it was created in?
>>
>> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
>> message news:utSMLyqCIHA.1204@.TK2MSFTNGP03.phx.gbl...
>> The procedure is still executed in the context of tempdb, not of master.
>> I think it would be very unexpected behavior if you changed
>> information_schema views to real user tables, and calling
>> pubs.dbo.someprocedure from tempdb, tried to find an authors table in
>> tempdb?
>> A
>>
>>
>> "Jay" <nospan@.nospam.org> wrote in message
>> news:uihhguqCIHA.1168@.TK2MSFTNGP02.phx.gbl...
>> Why isn't INFORMATION_SCHEMA.TABLES seeing the master database tables?
>> use tempdb
>> go
>> if object_id('temp_proc') is not null drop proc temp_proc
>> go
>> create proc temp_proc
>> as
>> SELECT TABLE_CATALOG , TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE
>> FROM INFORMATION_SCHEMA.TABLES
>> go
>> exec tempdb.dbo.temp_proc
>> use master
>> exec tempdb.dbo.temp_proc
>>
>>
>>
>

Monday, March 26, 2012

Infinity problem

Hi,

I have a table with some database fields and some calculated values. Sometimes it happens that I divide by 0 or null. As a result I get 'Infinity' in my textbox, is it possible to get rid of this 'message'?

greetz

Im not sure what the return value of that message is .... but if its a string containing the word "Infinity" you could try something like this:

Your field that sometimes returns infinity is: CalculatedField
IIf(CalculatedField = "Infinity", "Write your expression when true", CalculatedField)

That expression is used for a new calculated field and that field you can use in a textbox

|||

That could idd be a solution, but isn't there any way to use formatting. I don't like changing the value of my textbox.

greetz

|||

I recommend to add a custom code function for the division (in Report -> Report Properties -> Code). Call that custom code function inside of performing the division directly in the expression.

Public Function Divide(ByVal first As Double, ByVal second As Double) As Double
If second = 0 Then
Return 0
Else
Return first / second
End If
End Function

-- Robert

Friday, March 23, 2012

inesert null value

Hi All,

I am using c# (asp.net 2.0) and sql 2005.

I need to check whether the checkbox is checked then insert the current date or insert a empty/null values.

I used this condition chkTFR.Checked ?DateTime.Now :SqlDateTime.Null.Value or chkTFR.Checked ?DateTime.Now :System.DBNull.Value

In the database table I create a field with datetime type. I am not able to insert a null value to the field. If anybody had an experience pls post your code.

I am using strongly typed dataset & tableadapter to insert the values to the database table. (for your reference C this link:http://www.asp.net/learn/data-access/tutorial-01-cs.aspx)

ThanX in advance

Brotherly

~FAAS

The article in the following link covers your issue:

http://www.c-sharpcorner.com/UploadFile/sd_patel/EnterNullValuesForDateTime11222005015742AM/EnterNullValuesForDateTime.aspx

|||

Hi,

I used

SqlDateTime.Null.Value

I got this error message when I press the submit button.

Data is Null. This method or property cannot be called on Null values.

Remember I am using the strongly Typed DataSet & TableAdapter method.

Regards

~FAAS

|||

Hi faas1,

I guess you are using tableadpter database direct methods to insert data (like, tableadapter.Insert(your insert value)).

You can try to use "null" instread ofSqlDateTime.Null.Value /DBNull.value in your Insert() method. I've tested in my side and it works fine.

Hope my suggestion helps

Inequality comparisons against null question

This seems very very strange. We thougth it was only an issue with
uniqueidentifiers, but we have now determined that any inequality
comparison between a value and a null does not result in true or false.
The row with that comparison just disappears!
Here is a scrip that demos this:
Declare @.tmp table (
a int,
b int
)
Insert @.tmp Values (1, 1)
Insert @.tmp Values (2, 3)
Insert @.tmp Values (4, null)
Select * From @.tmp
-- Returns what I expected
Select * From @.tmp Where a = b
-- Returns what I expected
Select * From @.tmp Where a <> b
-- Hmmm... does not return the row (4,null)
Select * From @.tmp Where !(a = b)
-- This does not return the row (4,null) either
This seems to be the case with any inequality comparisons agains NULL.
Very very strange. I had assumed all along that:
1 != NULL
would be True.
I could be dumb, but the documentation in SQL did not explain this very
well. The issues is only with inequality it seems, and doing the
ANSI_NULLS does not change the issue. So lifes goes... I guess.Comparisons against NULL evaluates to the logical state UNKNOWN. But it was deemed impractical for
the DBMS to raise its virtual hands in the air and say "I don't know" (throw an error) as soon as a
query has a comparison against NULL. So, for a WHERE clause, UNKNOWN will be FALSE. That is the same
for <> as well as =. An interesting point is that for a GROUP BY and a constraint, UNKNOWN will be
TRUE.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Daniel Santa Cruz" <dstcruz@.gmail.com> wrote in message
news:1136492061.330974.56460@.o13g2000cwo.googlegroups.com...
> This seems very very strange. We thougth it was only an issue with
> uniqueidentifiers, but we have now determined that any inequality
> comparison between a value and a null does not result in true or false.
> The row with that comparison just disappears!
> Here is a scrip that demos this:
> Declare @.tmp table (
> a int,
> b int
> )
> Insert @.tmp Values (1, 1)
> Insert @.tmp Values (2, 3)
> Insert @.tmp Values (4, null)
> Select * From @.tmp
> -- Returns what I expected
> Select * From @.tmp Where a = b
> -- Returns what I expected
> Select * From @.tmp Where a <> b
> -- Hmmm... does not return the row (4,null)
> Select * From @.tmp Where !(a = b)
> -- This does not return the row (4,null) either
> This seems to be the case with any inequality comparisons agains NULL.
> Very very strange. I had assumed all along that:
> 1 != NULL
> would be True.
> I could be dumb, but the documentation in SQL did not explain this very
> well. The issues is only with inequality it seems, and doing the
> ANSI_NULLS does not change the issue. So lifes goes... I guess.
>|||Thanks for you input Tibor!
Tibor Karaszi wrote:
> ... So, for a WHERE clause, UNKNOWN will be FALSE. That is the same
> for <> as well as =.
I assumed this too, but the last statement in the sample script shows
something different:
--
Declare @.tmp table (
a int,
b int
)
Insert @.tmp Values (1, 1)
Insert @.tmp Values (2, 3)
Insert @.tmp Values (4, null)
Select * From @.tmp Where !(a = b)
--
Let's see, by row:
1 = 1 : True (negated -> False)
2 = 3 : False (negated -> True)
4 = NULL : UNKNOWN [or False if I understand your post] (negated ->
True)
But the actual result is only row (2, 3), and not (2, 3) AND (4, null)
Just weird, if you ask me.|||You need to either convert the null to something that equality comparisions
work for ie(isnull(b,0) > 0) or use IS NULL AND IS NOT NULL instead of = NULL
and <> NULL if you want to look for values in this field.
--
Ryan Powers
Clarity Consulting
http://www.claritycon.com
"Tibor Karaszi" wrote:
> Comparisons against NULL evaluates to the logical state UNKNOWN. But it was deemed impractical for
> the DBMS to raise its virtual hands in the air and say "I don't know" (throw an error) as soon as a
> query has a comparison against NULL. So, for a WHERE clause, UNKNOWN will be FALSE. That is the same
> for <> as well as =. An interesting point is that for a GROUP BY and a constraint, UNKNOWN will be
> TRUE.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Daniel Santa Cruz" <dstcruz@.gmail.com> wrote in message
> news:1136492061.330974.56460@.o13g2000cwo.googlegroups.com...
> > This seems very very strange. We thougth it was only an issue with
> > uniqueidentifiers, but we have now determined that any inequality
> > comparison between a value and a null does not result in true or false.
> > The row with that comparison just disappears!
> >
> > Here is a scrip that demos this:
> >
> > Declare @.tmp table (
> > a int,
> > b int
> > )
> >
> > Insert @.tmp Values (1, 1)
> > Insert @.tmp Values (2, 3)
> > Insert @.tmp Values (4, null)
> >
> > Select * From @.tmp
> > -- Returns what I expected
> >
> > Select * From @.tmp Where a = b
> > -- Returns what I expected
> >
> > Select * From @.tmp Where a <> b
> > -- Hmmm... does not return the row (4,null)
> >
> > Select * From @.tmp Where !(a = b)
> > -- This does not return the row (4,null) either
> >
> > This seems to be the case with any inequality comparisons agains NULL.
> > Very very strange. I had assumed all along that:
> > 1 != NULL
> > would be True.
> >
> > I could be dumb, but the documentation in SQL did not explain this very
> > well. The issues is only with inequality it seems, and doing the
> > ANSI_NULLS does not change the issue. So lifes goes... I guess.
> >
>|||Sorry, I have a type on all the scripts
All lines with:
!(a = b)
Should be:
Not (a = b)|||Ryan Powers wrote:
> You need to either convert the null to something that equality comparisions
> work for ie(isnull(b,0) > 0) or use IS NULL AND IS NOT NULL instead of = NULL
> and <> NULL if you want to look for values in this field.
We do that now. The issue was not in figuring out a way around this
"feature" but rather finding this feature. I always assumed (yes, I
assumed!) that equality and inequality results where a complement of
each other... well, seems I was wrong. Equality and inequality
comparisons are only complements of each other if ALL values compared
are non-null. There is the catch.|||> Equality and inequality
> comparisons are only complements of each other if ALL values compared
> are non-null. There is the catch.
Yes, this often causes confusion. We naturally think of predicates
evaluating to true/false rather than true/false/unknown. You need to
consider that:
'not true' can indicate either false or unknown
'not false' can indicate true or unknown
'not unknown' can result in true or false
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Daniel Santa Cruz" <dstcruz@.gmail.com> wrote in message
news:1136495151.092087.185130@.z14g2000cwz.googlegroups.com...
> Ryan Powers wrote:
>> You need to either convert the null to something that equality
>> comparisions
>> work for ie(isnull(b,0) > 0) or use IS NULL AND IS NOT NULL instead of =>> NULL
>> and <> NULL if you want to look for values in this field.
> We do that now. The issue was not in figuring out a way around this
> "feature" but rather finding this feature. I always assumed (yes, I
> assumed!) that equality and inequality results where a complement of
> each other... well, seems I was wrong. Equality and inequality
> comparisons are only complements of each other if ALL values compared
> are non-null. There is the catch.
>|||On 5 Jan 2006 12:52:37 -0800, Daniel Santa Cruz wrote:
>Thanks for you input Tibor!
>Tibor Karaszi wrote:
>> ... So, for a WHERE clause, UNKNOWN will be FALSE. That is the same
>> for <> as well as =.
>I assumed this too, but the last statement in the sample script shows
>something different:
>--
>Declare @.tmp table (
> a int,
> b int
>)
>Insert @.tmp Values (1, 1)
>Insert @.tmp Values (2, 3)
>Insert @.tmp Values (4, null)
>Select * From @.tmp Where !(a = b)
>--
>Let's see, by row:
> 1 = 1 : True (negated -> False)
> 2 = 3 : False (negated -> True)
> 4 = NULL : UNKNOWN [or False if I understand your post] (negated ->
>True)
>But the actual result is only row (2, 3), and not (2, 3) AND (4, null)
>Just weird, if you ask me.
Hi Daniel,
A better way to say it is that rows will be rejected if the WHERE clause
evaluates to FALSE or UNKNOWN. That is, UNKNOWN is not the same as FALSE
(which is how you appear to interpret Tibor), but they are treated the
same -- AFTER evaluating the complete WHERE condition.
Of course, this means that the truth tables for AND, OR and NOT have to
be extended to allow for three values instead of two: TRUE (T), FALSE
(F) and UNKNOWN (U). Here are the three-valued truth tables (use a
non-proportional font to display):
AND | T | U | F OR | T | U | F NOT | T | U | F
--+--+--+-- --+--+--+-- --+--+--+--
T | T | U | F T | T | T | T | F | U | T
U | U | U | F U | T | U | U
F | F | F | F F | T | U | F
Now let's reconsider your last examples:
> 4 = NULL : UNKNOWN [or False if I understand your post] (negated ->
>True)
becomes
4 = NULL : UNKNOWN (negated -> UNKNWON) and hence not included in the
result set.
--
Hugo Kornelis, SQL Server MVP|||> AND | T | U | F OR | T | U | F NOT | T | U | F
> --+--+--+-- --+--+--+-- --+--+--+--
> T | T | U | F T | T | T | T | F | U | T
> U | U | U | F U | T | U | U
> F | F | F | F F | T | U | F
I believe these tables are only accurate when
SET ANSI_NULLS OFF
See the example below.
set ansi_nulls off
select
case
when not (0 = null) then 'true'
else 'false'
end test1
That code above will return 'true' as the test1 column value.
However...
set ansi_nulls on
select
case
when not (0 = null) then 'true'
else 'false'
end test1
Will return 'false' as the test1 column value.
Please correct me if I'm wrong but it seems that if ansi nulls are on
then anything having anything to do with null automatically becomes
null.
Therefore
F AND U -> U (Unlike the table above.)|||On 5 Jan 2006 14:43:06 -0800, Rob wrote:
>> AND | T | U | F OR | T | U | F NOT | T | U | F
>> --+--+--+-- --+--+--+-- --+--+--+--
>> T | T | U | F T | T | T | T | F | U | T
>> U | U | U | F U | T | U | U
>> F | F | F | F F | T | U | F
>I believe these tables are only accurate when
>SET ANSI_NULLS OFF
>See the example below.
(snip)
Hi Rob,
The tables are the same, but the result of (0 = null) is different with
ANSI_NULLS off or on.
Note that the ANSI_NULLS OFF option should only be used to solve
compatibility issues with older applications that were made before SQL
Server complied with ANSI-standard treatment of NULL. And that those
applications should be either replaced or updated to observe ANSI
standard NULL behaviour as soon as feasible. Allowing non-ANSI treatment
of NULLS is a maintenance disaster waiting to happen, if only because
less and less programmers and DBAs are familiar with the old non-ANSI
behaviour and nobody expects it anymore.
--
Hugo Kornelis, SQL Server MVP|||> A better way to say it is that rows will be rejected if the WHERE clause
> evaluates to FALSE or UNKNOWN. That is, UNKNOWN is not the same as FALSE
> (which is how you appear to interpret Tibor), but they are treated the
> same -- AFTER evaluating the complete WHERE condition.
That is a better way of putting it. :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info> wrote in message
news:ql4rr1ls73p5rigv4v66je0ljqj5ndml6b@.4ax.com...
> On 5 Jan 2006 12:52:37 -0800, Daniel Santa Cruz wrote:
>>Thanks for you input Tibor!
>>Tibor Karaszi wrote:
>> ... So, for a WHERE clause, UNKNOWN will be FALSE. That is the same
>> for <> as well as =.
>>I assumed this too, but the last statement in the sample script shows
>>something different:
>>--
>>Declare @.tmp table (
>> a int,
>> b int
>>)
>>Insert @.tmp Values (1, 1)
>>Insert @.tmp Values (2, 3)
>>Insert @.tmp Values (4, null)
>>Select * From @.tmp Where !(a = b)
>>--
>>Let's see, by row:
>> 1 = 1 : True (negated -> False)
>> 2 = 3 : False (negated -> True)
>> 4 = NULL : UNKNOWN [or False if I understand your post] (negated ->
>>True)
>>But the actual result is only row (2, 3), and not (2, 3) AND (4, null)
>>Just weird, if you ask me.
> Hi Daniel,
> A better way to say it is that rows will be rejected if the WHERE clause
> evaluates to FALSE or UNKNOWN. That is, UNKNOWN is not the same as FALSE
> (which is how you appear to interpret Tibor), but they are treated the
> same -- AFTER evaluating the complete WHERE condition.
> Of course, this means that the truth tables for AND, OR and NOT have to
> be extended to allow for three values instead of two: TRUE (T), FALSE
> (F) and UNKNOWN (U). Here are the three-valued truth tables (use a
> non-proportional font to display):
> AND | T | U | F OR | T | U | F NOT | T | U | F
> --+--+--+-- --+--+--+-- --+--+--+--
> T | T | U | F T | T | T | T | F | U | T
> U | U | U | F U | T | U | U
> F | F | F | F F | T | U | F
> Now let's reconsider your last examples:
>> 4 = NULL : UNKNOWN [or False if I understand your post] (negated ->
>>True)
> becomes
> 4 = NULL : UNKNOWN (negated -> UNKNWON) and hence not included in the
> result set.
> --
> Hugo Kornelis, SQL Server MVP

Inequality comparisons against null question

Indicating the NULL value in Report Expression Syntax

Hi,

I would like to know how I can indicate a NULL value in a report expression in SSRS / Report Designer.

I am trying to code :

IIF(Value_A = 0, <NULL>, Value_A)

It may look weird but I am trying to return NULL values when Value_A is 0 (zero), in the sample scenario above.

I have tried using the keyword "NULL", but it is highlighted as a syntax error, and suggested to use System.DBNull. I tried it and then it says that components of the System collection cannot be used in an expression, so I am left drawing blanks.

Thanks.

regards,
Kenny

Use the VB keyword "Nothing"

=Iif(Fields!Value_A.Value = 0, Nothing, Fields!Value_A.Value)

|||Hi Adam,

Thanks for the tip. Worked like a charm.

Where else can I get a list of VB keywords I can use in Report Designer / SSRS / SQL Server 2005. I have some background with VB6 prior and was hoping I could port a whole bunch of them over to be used here.

Kenny
|||Most global functions were ported to VB.NET and that's what's supported in RS. For more details about RS expressions and the functions start from here http://msdn2.microsoft.com/en-us/library/ms159238.aspx

Wednesday, March 21, 2012

Indicating the NULL value in Report Expression Syntax

Hi,

I would like to know how I can indicate a NULL value in a report expression in SSRS / Report Designer.

I am trying to code :

IIF(Value_A = 0, <NULL>, Value_A)

It may look weird but I am trying to return NULL values when Value_A is 0 (zero), in the sample scenario above.

Thanks.

regards,
Kenny

Use the VB keyword "Nothing"

=Iif(Fields!Value_A.Value = 0, Nothing, Fields!Value_A.Value)

|||Hi Adam,

Thanks for the tip. Worked like a charm.

Indexing with null or zero

I am adding an int field to a table that will be used to link it to another
table when it has a number in it. I plan to index it for faster access but
wondered if my default for that table should be NULL or zero or if it makes
a difference. Thanks.
DavidTo an index, NULL is just a value (as is 0). So use whatever makes most sens
e to you.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"David C" <dlchase@.lifetimeinc.com> wrote in message news:uy%238XzdSFHA.252@.TK2MSFTNGP12.ph
x.gbl...
>I am adding an int field to a table that will be used to link it to another
table when it has a
>number in it. I plan to index it for faster access but wondered if my defa
ult for that table
>should be NULL or zero or if it makes a difference. Thanks.
> David
>|||I have to say that it would be nice if there was a built-in means to create
a
unique constraint on a column such that nulls are ignored. Going the route o
f
indexed views or triggers is a colossal pain.
Thomas
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OmrRbKeSFHA.580@.TK2MSFTNGP15.phx.gbl...
> To an index, NULL is just a value (as is 0). So use whatever makes most se
nse
> to you.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "David C" <dlchase@.lifetimeinc.com> wrote in message
> news:uy%238XzdSFHA.252@.TK2MSFTNGP12.phx.gbl...
>|||Tibor,
I don't think this is quite true. A column's nullability (assuming
that is part of the choice here) affects what query plans can be
considered. Also, indexes aside, there can be different and
unexpected results depending on whether NULL or 0 is used.
An example we often see in the newsgroups is
select this, that
from T
where T.parent not in (
select parent
from T_children
)
which returns no rows even when there are T.parent values
not present in the T_children table.
There are other differences less likely to be an issue, like
calculating percentages:
select
T.parent,
sum(case when parent = T_children.parent then 1.00 else 0.00 end) /
count(T_children.parent) as percentage
from T join T_children
on T.parent = T_children.parent
group by T.parent
Unfortunately, while using NULL is probably truer to the principles
of good modeling, using 0 may have some practical advantages, if
the referring column can then be declared as NOT NULL. To maintain
referential integrity via a foreign key constraint, NULL is a better choice,
since 0 requires putting a dummy entry into the referenced table.
Steve Kass
Drew University
Tibor Karaszi wrote:

>To an index, NULL is just a value (as is 0). So use whatever makes most sen
se to you.
>
>|||
Thomas wrote:

>I have to say that it would be nice if there was a built-in means to create
a
>unique constraint on a column such that nulls are ignored. Going the route
of
>indexed views or triggers is a colossal pain.
>
>
There is at least one solution using an indexed column instead of
an indexed view. Functionally, it is much the same, but you might
find it easier to maintain.
http://groups.google.co.uk/groups?q...B8-B7567063D1CC
SK

>Thomas
>
>"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
>message news:OmrRbKeSFHA.580@.TK2MSFTNGP15.phx.gbl...
>
>
>|||Hmm..That's an clever solution. For the purposes of other people reading thi
s
thread, the idea is to create a computed column that either equals the colum
n on
which you really want a unique index or the primary key when that value is n
ull.
You would then put the unique index on this computed column.
Granted, it does mean dealing with computed columns which can be a persnicke
ty
but it does get around the issue.
Thomas
"Steve Kass" <skass@.drew.edu> wrote in message
news:uSJlWXeSFHA.3788@.tk2msftngp13.phx.gbl...
>
> Thomas wrote:
>
> There is at least one solution using an indexed column instead of
> an indexed view. Functionally, it is much the same, but you might
> find it easier to maintain.
> http://groups.google.co.uk/groups?q...B8-B7567063D1CC
> SK
>|||If a Foreign Key column allows null, then this is definitely NOT the same as
putting a zero in the column. A value of zero MUST exist in the reference
table, a null value does NOT have to (indeed it cannot exist) as PK in the
reference table. The distinction is critical, it is the dfference between a
[one]-to-[zero or many] relationship, and a [zero or one]-to-[zero or many]
relationship.
Example, In a [one]-to-[zero or many] Employees have zero o many timecard
punches, but for each time card punch there must be one employee - and only
one an employee.
In a [zero or one]-to-[zero or many] relatonship, Each Library patron can
have zero or many books checked out to them, and each book can be checked
out to zero or one library patron...
The only way to model this distinction is by allowing, (and using) null
values in the Foreign Key column on the many side of the DRI constraint.
"Tibor Karaszi" wrote:

> To an index, NULL is just a value (as is 0). So use whatever makes most se
nse to you.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "David C" <dlchase@.lifetimeinc.com> wrote in message news:uy%238XzdSFHA.25
2@.TK2MSFTNGP12.phx.gbl...
>
>|||When I want a Foreign Key Column to be unique, but Allow any number of Nulls
,
(that's a [One]-to-[Zero or One] Relationship, I use an extra table
CREATE TABLE [TabA] (
[AID] [int] NOT NULL ,
[Name] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
CONSTRAINT [PK_TabA] PRIMARY KEY CLUSTERED
([AID]) ON [PRIMARY]
) ON [PRIMARY]
GO
-- ****************************************
***
CREATE TABLE [TabB] (
[BID] [int] NOT NULL ,
[AID] [int] NULL ,
CONSTRAINT [PK_TabB] PRIMARY KEY CLUSTERED
( [BID]) ON [PRIMARY] ,
CONSTRAINT [FK_TabB_TabA] FOREIGN KEY
([AID]) REFERENCES [TabA] ([AID])
) ON [PRIMARY]
GO
-- ****************************************
***
CREATE TABLE [TabC] (
[BID] [int] NOT NULL ,
[AID] [int] NOT NULL ,
CONSTRAINT [PK_TabC] PRIMARY KEY CLUSTERED
([BID]) ON [PRIMARY] ,
CONSTRAINT [IX_TabCUniqueAID] UNIQUE NONCLUSTERED
([AID]) ON [PRIMARY] ,
CONSTRAINT [FK_TabC_TabB] FOREIGN KEY
([BID]) REFERENCES [TabB] ([BID])
) ON [PRIMARY]
GO
-- Then I add ALL the child records to TabB, Both those with null values of
AID, and Non-Null values of AID, but only add the Non-Null AID Records to
TabC... TabB is teh real table, and has all the other attriobutes in it,
TabC is only there to enforce uniqueness on the Non-Null Values of AID in Ta
bB
"Thomas" wrote:

> Hmm..That's an clever solution. For the purposes of other people reading t
his
> thread, the idea is to create a computed column that either equals the col
umn on
> which you really want a unique index or the primary key when that value is
null.
> You would then put the unique index on this computed column.
> Granted, it does mean dealing with computed columns which can be a persnic
kety
> but it does get around the issue.
>
> Thomas
>
>
> "Steve Kass" <skass@.drew.edu> wrote in message
> news:uSJlWXeSFHA.3788@.tk2msftngp13.phx.gbl...
>
>|||Perhaps I was in a bit too much hurry when responding. I was looking at it p
urely from the index'
perspective, how the data is stored in the index. You definitely want to thi
nk through semantics
carefully. :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
news:EC2328B5-9A67-4E1A-A096-9B071AE179EB@.microsoft.com...
> If a Foreign Key column allows null, then this is definitely NOT the same
as
> putting a zero in the column. A value of zero MUST exist in the referenc
e
> table, a null value does NOT have to (indeed it cannot exist) as PK in the
> reference table. The distinction is critical, it is the dfference between
a
> [one]-to-[zero or many] relationship, and a [zero or one]-to-[zero or many]
> relationship.
>
> Example, In a [one]-to-[zero or many] Employees have zero o many timecard
> punches, but for each time card punch there must be one employee - and on
ly
> one an employee.
> In a [zero or one]-to-[zero or many] relatonship, Each Library patron can
> have zero or many books checked out to them, and each book can be checked
> out to zero or one library patron...
> The only way to model this distinction is by allowing, (and using) null
> values in the Foreign Key column on the many side of the DRI constraint.
> "Tibor Karaszi" wrote:
>|||Tibor,
Neglected to make clear that details in my post,were, of course, not
directed at you... figured you just overlooked that. I slip into
pedanticLand way too easily...
Respectfully, Charly
"Tibor Karaszi" wrote:

> Perhaps I was in a bit too much hurry when responding. I was looking at it
purely from the index'
> perspective, how the data is stored in the index. You definitely want to t
hink through semantics
> carefully. :-)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
> news:EC2328B5-9A67-4E1A-A096-9B071AE179EB@.microsoft.com...
>
>

Friday, March 9, 2012

INDEXES: Is this correct

Wednesday, March 7, 2012

Indexes confusion

I have a temp table and created two indexes on it.
Please See below
CREATE TABLE #Invoice (
InvoiceID int NOT NULL,
CustomerKey varchar(20),
DebtorId int,
Reference varchar(50),
BalanceDue money,
DebtID int,
Reason varchar(100),
DebtStatus int,
)
CREATE INDEX idxInvoice ON #Invoice (InvoiceID)
CREATE INDEX idxInvoice2 ON #Invoice (DebtID)
I use DebtID as index because I use it again as a inner join to this table
in another query. So I assume it will make much faster. All I want to know is
would this code work?
Already answered, please see your other thread, and these articles:
http://www.aspfaq.com/5007
http://www.aspfaq.com/5003
http://www.aspfaq.com/
(Reverse address to reply.)
"raj" <raj@.discussions.microsoft.com> wrote in message
news:0C4B1D69-3A7F-4202-B2DD-059127C6175C@.microsoft.com...
> I have a temp table and created two indexes on it.
> Please See below
> --
> CREATE TABLE #Invoice (
> InvoiceID int NOT NULL,
> CustomerKey varchar(20),
> DebtorId int,
> Reference varchar(50),
> BalanceDue money,
> DebtID int,
> Reason varchar(100),
> DebtStatus int,
> )
> CREATE INDEX idxInvoice ON #Invoice (InvoiceID)
> CREATE INDEX idxInvoice2 ON #Invoice (DebtID)
> I use DebtID as index because I use it again as a inner join to this table
> in another query. So I assume it will make much faster. All I want to know
is
> would this code work?
>
|||I'm sorry , but I couldn't find the answer in the newsgroup.
"Aaron [SQL Server MVP]" wrote:

> Already answered, please see your other thread, and these articles:
> http://www.aspfaq.com/5007
> http://www.aspfaq.com/5003
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "raj" <raj@.discussions.microsoft.com> wrote in message
> news:0C4B1D69-3A7F-4202-B2DD-059127C6175C@.microsoft.com...
> is
>
>
|||It should work fine. But bear in mind you're creating a temporary table
(with the # prefix on the tablename) so it will get created in tempdb and
will be automatically dropped when it goes out of scope (most likely at the
end of your batch). I don't know how much faster the indexes will really
make your queries - the best way to find out is trial and error (set a
couple session variables ("set statistics io on" and "set statistics time
on") at the beginning of your batch and turn on the execution plans to see
what real difference it makes).
Most temp table solutions don't benefit too much from indexes (since they're
usually pretty small tables, they're very short lived anyway and it takes
some resources to maintain the indexes). Both indexes you create in your
script are nonclustered which means your data itself is unsorted (it's a
"heap"). You might benefit from creating a clustered index on your table on
a suitable column, but as I just mentioned it'll probably take more
resources to maintain these indexes than it would to query the table without
the indexes in the first place. Trial & error is the only way to tell.
Cheers,
Mike
"raj" <raj@.discussions.microsoft.com> wrote in message
news:86C3A5FC-2F1E-4717-9130-7D372FB1B66E@.microsoft.com...[vbcol=seagreen]
> I'm sorry , but I couldn't find the answer in the newsgroup.
> "Aaron [SQL Server MVP]" wrote:
|||Thank you very much for your help. Greatly appreciate.
raj
"Mike Hodgson" wrote:

> It should work fine. But bear in mind you're creating a temporary table
> (with the # prefix on the tablename) so it will get created in tempdb and
> will be automatically dropped when it goes out of scope (most likely at the
> end of your batch). I don't know how much faster the indexes will really
> make your queries - the best way to find out is trial and error (set a
> couple session variables ("set statistics io on" and "set statistics time
> on") at the beginning of your batch and turn on the execution plans to see
> what real difference it makes).
> Most temp table solutions don't benefit too much from indexes (since they're
> usually pretty small tables, they're very short lived anyway and it takes
> some resources to maintain the indexes). Both indexes you create in your
> script are nonclustered which means your data itself is unsorted (it's a
> "heap"). You might benefit from creating a clustered index on your table on
> a suitable column, but as I just mentioned it'll probably take more
> resources to maintain these indexes than it would to query the table without
> the indexes in the first place. Trial & error is the only way to tell.
> --
> Cheers,
> Mike
> "raj" <raj@.discussions.microsoft.com> wrote in message
> news:86C3A5FC-2F1E-4717-9130-7D372FB1B66E@.microsoft.com...
>
>

Indexes confusion

I have a temp table and created two indexes on it.
Please See below
--
CREATE TABLE #Invoice (
InvoiceID int NOT NULL,
CustomerKey varchar(20),
DebtorId int,
Reference varchar(50),
BalanceDue money,
DebtID int,
Reason varchar(100),
DebtStatus int,
)
CREATE INDEX idxInvoice ON #Invoice (InvoiceID)
CREATE INDEX idxInvoice2 ON #Invoice (DebtID)
---
I use DebtID as index because I use it again as a inner join to this table
in another query. So I assume it will make much faster. All I want to know is
would this code work?Already answered, please see your other thread, and these articles:
http://www.aspfaq.com/5007
http://www.aspfaq.com/5003
--
http://www.aspfaq.com/
(Reverse address to reply.)
"raj" <raj@.discussions.microsoft.com> wrote in message
news:0C4B1D69-3A7F-4202-B2DD-059127C6175C@.microsoft.com...
> I have a temp table and created two indexes on it.
> Please See below
> --
> CREATE TABLE #Invoice (
> InvoiceID int NOT NULL,
> CustomerKey varchar(20),
> DebtorId int,
> Reference varchar(50),
> BalanceDue money,
> DebtID int,
> Reason varchar(100),
> DebtStatus int,
> )
> CREATE INDEX idxInvoice ON #Invoice (InvoiceID)
> CREATE INDEX idxInvoice2 ON #Invoice (DebtID)
> ---
> I use DebtID as index because I use it again as a inner join to this table
> in another query. So I assume it will make much faster. All I want to know
is
> would this code work?
>|||I'm sorry , but I couldn't find the answer in the newsgroup.
"Aaron [SQL Server MVP]" wrote:
> Already answered, please see your other thread, and these articles:
> http://www.aspfaq.com/5007
> http://www.aspfaq.com/5003
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "raj" <raj@.discussions.microsoft.com> wrote in message
> news:0C4B1D69-3A7F-4202-B2DD-059127C6175C@.microsoft.com...
> > I have a temp table and created two indexes on it.
> > Please See below
> > --
> > CREATE TABLE #Invoice (
> > InvoiceID int NOT NULL,
> > CustomerKey varchar(20),
> > DebtorId int,
> > Reference varchar(50),
> > BalanceDue money,
> > DebtID int,
> > Reason varchar(100),
> > DebtStatus int,
> > )
> > CREATE INDEX idxInvoice ON #Invoice (InvoiceID)
> > CREATE INDEX idxInvoice2 ON #Invoice (DebtID)
> > ---
> >
> > I use DebtID as index because I use it again as a inner join to this table
> > in another query. So I assume it will make much faster. All I want to know
> is
> > would this code work?
> >
> >
>
>|||It should work fine. But bear in mind you're creating a temporary table
(with the # prefix on the tablename) so it will get created in tempdb and
will be automatically dropped when it goes out of scope (most likely at the
end of your batch). I don't know how much faster the indexes will really
make your queries - the best way to find out is trial and error (set a
couple session variables ("set statistics io on" and "set statistics time
on") at the beginning of your batch and turn on the execution plans to see
what real difference it makes).
Most temp table solutions don't benefit too much from indexes (since they're
usually pretty small tables, they're very short lived anyway and it takes
some resources to maintain the indexes). Both indexes you create in your
script are nonclustered which means your data itself is unsorted (it's a
"heap"). You might benefit from creating a clustered index on your table on
a suitable column, but as I just mentioned it'll probably take more
resources to maintain these indexes than it would to query the table without
the indexes in the first place. Trial & error is the only way to tell.
--
Cheers,
Mike
"raj" <raj@.discussions.microsoft.com> wrote in message
news:86C3A5FC-2F1E-4717-9130-7D372FB1B66E@.microsoft.com...
> I'm sorry , but I couldn't find the answer in the newsgroup.
> "Aaron [SQL Server MVP]" wrote:
>> Already answered, please see your other thread, and these articles:
>> http://www.aspfaq.com/5007
>> http://www.aspfaq.com/5003
>> --
>> http://www.aspfaq.com/
>> (Reverse address to reply.)
>>
>>
>> "raj" <raj@.discussions.microsoft.com> wrote in message
>> news:0C4B1D69-3A7F-4202-B2DD-059127C6175C@.microsoft.com...
>> > I have a temp table and created two indexes on it.
>> > Please See below
>> > --
>> > CREATE TABLE #Invoice (
>> > InvoiceID int NOT NULL,
>> > CustomerKey varchar(20),
>> > DebtorId int,
>> > Reference varchar(50),
>> > BalanceDue money,
>> > DebtID int,
>> > Reason varchar(100),
>> > DebtStatus int,
>> > )
>> > CREATE INDEX idxInvoice ON #Invoice (InvoiceID)
>> > CREATE INDEX idxInvoice2 ON #Invoice (DebtID)
>> > ---
>> >
>> > I use DebtID as index because I use it again as a inner join to this
>> > table
>> > in another query. So I assume it will make much faster. All I want to
>> > know
>> is
>> > would this code work?
>> >
>> >
>>|||Thank you very much for your help. Greatly appreciate.
raj
"Mike Hodgson" wrote:
> It should work fine. But bear in mind you're creating a temporary table
> (with the # prefix on the tablename) so it will get created in tempdb and
> will be automatically dropped when it goes out of scope (most likely at the
> end of your batch). I don't know how much faster the indexes will really
> make your queries - the best way to find out is trial and error (set a
> couple session variables ("set statistics io on" and "set statistics time
> on") at the beginning of your batch and turn on the execution plans to see
> what real difference it makes).
> Most temp table solutions don't benefit too much from indexes (since they're
> usually pretty small tables, they're very short lived anyway and it takes
> some resources to maintain the indexes). Both indexes you create in your
> script are nonclustered which means your data itself is unsorted (it's a
> "heap"). You might benefit from creating a clustered index on your table on
> a suitable column, but as I just mentioned it'll probably take more
> resources to maintain these indexes than it would to query the table without
> the indexes in the first place. Trial & error is the only way to tell.
> --
> Cheers,
> Mike
> "raj" <raj@.discussions.microsoft.com> wrote in message
> news:86C3A5FC-2F1E-4717-9130-7D372FB1B66E@.microsoft.com...
> > I'm sorry , but I couldn't find the answer in the newsgroup.
> >
> > "Aaron [SQL Server MVP]" wrote:
> >
> >> Already answered, please see your other thread, and these articles:
> >>
> >> http://www.aspfaq.com/5007
> >> http://www.aspfaq.com/5003
> >>
> >> --
> >> http://www.aspfaq.com/
> >> (Reverse address to reply.)
> >>
> >>
> >>
> >>
> >> "raj" <raj@.discussions.microsoft.com> wrote in message
> >> news:0C4B1D69-3A7F-4202-B2DD-059127C6175C@.microsoft.com...
> >> > I have a temp table and created two indexes on it.
> >> > Please See below
> >> > --
> >> > CREATE TABLE #Invoice (
> >> > InvoiceID int NOT NULL,
> >> > CustomerKey varchar(20),
> >> > DebtorId int,
> >> > Reference varchar(50),
> >> > BalanceDue money,
> >> > DebtID int,
> >> > Reason varchar(100),
> >> > DebtStatus int,
> >> > )
> >> > CREATE INDEX idxInvoice ON #Invoice (InvoiceID)
> >> > CREATE INDEX idxInvoice2 ON #Invoice (DebtID)
> >> > ---
> >> >
> >> > I use DebtID as index because I use it again as a inner join to this
> >> > table
> >> > in another query. So I assume it will make much faster. All I want to
> >> > know
> >> is
> >> > would this code work?
> >> >
> >> >
> >>
> >>
> >>
>
>

Friday, March 30, 2012

INFORMATION_SCHEMA again

Monday, March 26, 2012

Infinity problem

Friday, March 23, 2012

inesert null value

Inequality comparisons against null question

Inequality comparisons against null question

Inequality comparisons against null question

Indicating the NULL value in Report Expression Syntax

Wednesday, March 21, 2012

Indicating the NULL value in Report Expression Syntax

Indexing with null or zero

Friday, March 9, 2012

INDEXES: Is this correct

INDEXES: Is this correct

INDEXES: Is this correct

Wednesday, March 7, 2012

Indexes confusion

Indexes confusion

Indexes confusion

Indexes and Deadlocks

Blog Archive

About Me