Efficient SQL Backup?

Hi all,
I am having issues of efficiency of backing up data from one SQL data
base to another.
The two servers in questions are on different networks , behind
different firewalls. We have MS SQL 2000.
On the source data i run a job with the following steps:
1> take trans backup every 4 hrs
2> ftp to the remote server
3> if ftp fails , disable the whole job
On the target server I run a job which does the following
1> restore the trans backup with NORECOVERY.
If the job fails at target. I will have to go through the whole process
of doing a complete backup of the source , restoring it at the other
ens and then starting trans-backup again.
Also, if we do a failover to the target server, then when we roll back
to the source server again we have to da a back-up of the target and
restore it on the source server.
Is ther a more efficent way of doing this'Actually, no. This is pretty much what Log Shipping does. If you failover
to the remote and then want to failback to the primary, then you have to get
all of the data from the remote to the primary which means a backup/restore
cycle.
--
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
<bajaj.vishal@.gmail.com> wrote in message
news:1145456580.413687.286110@.t31g2000cwb.googlegroups.com...
> Hi all,
> I am having issues of efficiency of backing up data from one SQL data
> base to another.
> The two servers in questions are on different networks , behind
> different firewalls. We have MS SQL 2000.
> On the source data i run a job with the following steps:
> 1> take trans backup every 4 hrs
> 2> ftp to the remote server
> 3> if ftp fails , disable the whole job
> On the target server I run a job which does the following
> 1> restore the trans backup with NORECOVERY.
> If the job fails at target. I will have to go through the whole process
> of doing a complete backup of the source , restoring it at the other
> ens and then starting trans-backup again.
> Also, if we do a failover to the target server, then when we roll back
> to the source server again we have to da a back-up of the target and
> restore it on the source server.
> Is ther a more efficent way of doing this'
>

Efficient SQL Backup?

Hi all,
I am having issues of efficiency of backing up data from one SQL data
base to another.
The two servers in questions are on different networks , behind
different firewalls. We have MS SQL 2000.
On the source data i run a job with the following steps:
1> take trans backup every 4 hrs
2> ftp to the remote server
3> if ftp fails , disable the whole job
On the target server I run a job which does the following
1> restore the trans backup with NORECOVERY.
If the job fails at target. I will have to go through the whole process
of doing a complete backup of the source , restoring it at the other
ens and then starting trans-backup again.
Also, if we do a failover to the target server, then when we roll back
to the source server again we have to da a back-up of the target and
restore it on the source server.
Is ther a more efficent way of doing this'Sounds like a typical log shipping scenario. If the job fails at source you
do may have the option of applying the logs manually to bring the standby
server up-to-date without restoring the complete backup.
The link below show the process using Microsoft's log shipping solution but
a manual method will achive the same result.
http://www.microsoft.com/technet/pr...fr=
true
"Vishal" wrote:

> Hi all,
> I am having issues of efficiency of backing up data from one SQL data
> base to another.
> The two servers in questions are on different networks , behind
> different firewalls. We have MS SQL 2000.
> On the source data i run a job with the following steps:
> 1> take trans backup every 4 hrs
> 2> ftp to the remote server
> 3> if ftp fails , disable the whole job
> On the target server I run a job which does the following
> 1> restore the trans backup with NORECOVERY.
> If the job fails at target. I will have to go through the whole process
> of doing a complete backup of the source , restoring it at the other
> ens and then starting trans-backup again.
> Also, if we do a failover to the target server, then when we roll back
> to the source server again we have to da a back-up of the target and
> restore it on the source server.
> Is ther a more efficent way of doing this'
>|||I suggest you try in a different forum for server or admin things.
David
"Vishal" <bajaj.vishal@.gmail.com> wrote in message
news:1145456687.737152.87130@.g10g2000cwb.googlegroups.com...
> Hi all,
> I am having issues of efficiency of backing up data from one SQL data
> base to another.
> The two servers in questions are on different networks , behind
> different firewalls. We have MS SQL 2000.
> On the source data i run a job with the following steps:
> 1> take trans backup every 4 hrs
> 2> ftp to the remote server
> 3> if ftp fails , disable the whole job
> On the target server I run a job which does the following
> 1> restore the trans backup with NORECOVERY.
> If the job fails at target. I will have to go through the whole process
> of doing a complete backup of the source , restoring it at the other
> ens and then starting trans-backup again.
> Also, if we do a failover to the target server, then when we roll back
> to the source server again we have to da a back-up of the target and
> restore it on the source server.
> Is ther a more efficent way of doing this'
>|||Thanks a lot. let me try somthing else here .

Efficient SQL Backup?

Hi all,
I am having issues of efficiency of backing up data from one SQL data
base to another.
The two servers in questions are on different networks , behind
different firewalls. We have MS SQL 2000.
On the source data i run a job with the following steps:
1> take trans backup every 4 hrs
2> ftp to the remote server
3> if ftp fails , disable the whole job
On the target server I run a job which does the following
1> restore the trans backup with NORECOVERY.
If the job fails at target. I will have to go through the whole process
of doing a complete backup of the source , restoring it at the other
ens and then starting trans-backup again.
Also, if we do a failover to the target server, then when we roll back
to the source server again we have to da a back-up of the target and
restore it on the source server.
Is ther a more efficent way of doing this'Actually, no. This is pretty much what Log Shipping does. If you failover
to the remote and then want to failback to the primary, then you have to get
all of the data from the remote to the primary which means a backup/restore
cycle.
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
<bajaj.vishal@.gmail.com> wrote in message
news:1145456580.413687.286110@.t31g2000cwb.googlegroups.com...
> Hi all,
> I am having issues of efficiency of backing up data from one SQL data
> base to another.
> The two servers in questions are on different networks , behind
> different firewalls. We have MS SQL 2000.
> On the source data i run a job with the following steps:
> 1> take trans backup every 4 hrs
> 2> ftp to the remote server
> 3> if ftp fails , disable the whole job
> On the target server I run a job which does the following
> 1> restore the trans backup with NORECOVERY.
> If the job fails at target. I will have to go through the whole process
> of doing a complete backup of the source , restoring it at the other
> ens and then starting trans-backup again.
> Also, if we do a failover to the target server, then when we roll back
> to the source server again we have to da a back-up of the target and
> restore it on the source server.
> Is ther a more efficent way of doing this'
>

Efficient SQL Backup?

Hi all,

I am having issues of efficiency of backing up data from one SQL data
base to another.

The two servers in questions are on different networks , behind
different firewalls. We have MS SQL 2000.

On the source data i run a job with the following steps:

1> take trans backup every 4 hrs
2> ftp to the remote server
3> if ftp fails , disable the whole job

On the target server I run a job which does the following

1> restore the trans backup with NORECOVERY.

If the job fails at target. I will have to go through the whole process
of doing a complete backup of the source , restoring it at the other
ens and then starting trans-backup again.

Also, if we do a failover to the target server, then when we roll back
to the source server again we have to da a back-up of the target and
restore it on the source server.

Is ther a more efficent way of doing this??Vishal (bajaj.vishal@.gmail.com) writes:
> I am having issues of efficiency of backing up data from one SQL data
> base to another.
> The two servers in questions are on different networks , behind
> different firewalls. We have MS SQL 2000.
> On the source data i run a job with the following steps:
> 1> take trans backup every 4 hrs
> 2> ftp to the remote server
> 3> if ftp fails , disable the whole job
> On the target server I run a job which does the following
> 1> restore the trans backup with NORECOVERY.
> If the job fails at target. I will have to go through the whole process
> of doing a complete backup of the source , restoring it at the other
> ens and then starting trans-backup again.
> Also, if we do a failover to the target server, then when we roll back
> to the source server again we have to da a back-up of the target and
> restore it on the source server.
> Is ther a more efficent way of doing this??

I'm not sure exactly the purpose of this home-made log shipping is,
but could replication be a better alternative?

(Although, I have no idea whether replication can cross your network
and firewalls.)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||As per my understanding you are concerned with

3> if ftp fails , disable the whole job

* If you want to save time then you can go ahead and use SQL Litespeed
tool.

You can go through this article for more information.
http://www.sql-server-performance.c...d_spotlight.asp

Thanks
Ajay Rengunthwar (Ajay)|||thnks a lot, but will i be able to do a failover easily ? and can i do
transactional backups and keep restoring it at the other end. I cannot
do a complete backup due to bandwidth constraints.

Efficiency with INNER JOINs

Hi,
Is there an efficiency issue with INNER JOINs? For example, is there a
difference betweeen tableA INNER JOIN tableB and tableB INNER JOIN tableA?
What's the performance doing tableA INNER JOIN tableB INNER JOIN tableC etc?
Thanks,
Mats-LennartMats
> Is there an efficiency issue with INNER JOINs? For example, is there a
> difference betweeen tableA INNER JOIN tableB and tableB INNER JOIN tableA?
>
Actually there is no difference. The optimizer will choose the order to join
tables.
> What's the performance doing tableA INNER JOIN tableB INNER JOIN tableC
> etc?
>
I have always tried to create an index on foreign key column ( participate
in JOIN) to improve performance
http://www.sql-server-performance.com/tuning_joins.asp
"Mats-Lennart Hansson" <ap_skallen@.hotmail.com> wrote in message
news:%23RygRo2OGHA.1288@.TK2MSFTNGP09.phx.gbl...
> Hi,
> Is there an efficiency issue with INNER JOINs? For example, is there a
> difference betweeen tableA INNER JOIN tableB and tableB INNER JOIN tableA?
> What's the performance doing tableA INNER JOIN tableB INNER JOIN tableC
> etc?
> Thanks,
> Mats-Lennart
>|||Differnent writing of the queries often doesn=B4t interfer the
performance difference in query, though the query pocessor and
optimizer will make a plan on his own which tabe to query first and so
on to get the best performance for your statement.
HTH, Jens Suessmeyer.

Efficiency Question

In terms of the time a query would take to return, is there any difference
if a table has, say, 200 records and 100 columns vs. 100 records and 200
columns? Ultimately, I'm trying to figure out if speed needs to be a
consideration in determining whether to orient a new table horizontally or
vertically.
Thanks,
James
Size of the data returned rather than number of columns and rows is a more
relevant statistic but overall performance depends on other factors (such as
indexing) that are at least as important.

> Ultimately, I'm trying to figure out if speed needs to be a
> consideration in determining whether to orient a new table horizontally or
> vertically.
I would suggest that you're going about your table design the wrong way!
Normalize your design in (at minimum) Third Normal Form to start with. Once
you have done that you can test performance and make any optimizations
necesssary. Correct design doesn't normally involve choosing between
"horizontal" and "vertical" orientations of data.
David Portas
SQL Server MVP
|||Well, the design of this table is relatively simple and straightforward...if
we had even reasonable hardware. I've been having to design tables around
poor hardware performance...trying to keep as little data as possible in any
given table, in order to prevent a meltdown.
The design was fine until the hardware hit the proverbial wall and
everything started crashing when even a moderate size query was run. So
instead of sinking a few thousand dollars into new hardware, they sank a few
thousand dollars into man hours, trying to solve the problem with intellect.
So far it's been relatively unsuccessful.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:DJOdnT3qqPhoSmbdRVn-ow@.giganews.com...
> Size of the data returned rather than number of columns and rows is a more
> relevant statistic but overall performance depends on other factors (such
as[vbcol=seagreen]
> indexing) that are at least as important.
or
> I would suggest that you're going about your table design the wrong way!
> Normalize your design in (at minimum) Third Normal Form to start with.
Once
> you have done that you can test performance and make any optimizations
> necesssary. Correct design doesn't normally involve choosing between
> "horizontal" and "vertical" orientations of data.
> --
> David Portas
> SQL Server MVP
> --
>
|||> In terms of the time a query would take to return, is there any difference
> if a table has, say, 200 records and 100 columns vs. 100 records and 200
> columns? Ultimately, I'm trying to figure out if speed needs to be a
> consideration in determining whether to orient a new table horizontally or
> vertically.
Your table design should be driven by normalizing the entities you are
trying to represent, not optimizing number of columns vs. number of rows.
If performance is a problem due to hardware constraints, fix the hardware.
Anything you do in the context of the above will be a bandaid at best, and
will only serve to yield *different* performance problems down the line,
IMHO.
http://www.aspfaq.com/
(Reverse address to reply.)

Efficiency Question

In terms of the time a query would take to return, is there any difference
if a table has, say, 200 records and 100 columns vs. 100 records and 200
columns? Ultimately, I'm trying to figure out if speed needs to be a
consideration in determining whether to orient a new table horizontally or
vertically.
Thanks,
JamesSize of the data returned rather than number of columns and rows is a more
relevant statistic but overall performance depends on other factors (such as
indexing) that are at least as important.
> Ultimately, I'm trying to figure out if speed needs to be a
> consideration in determining whether to orient a new table horizontally or
> vertically.
I would suggest that you're going about your table design the wrong way!
Normalize your design in (at minimum) Third Normal Form to start with. Once
you have done that you can test performance and make any optimizations
necesssary. Correct design doesn't normally involve choosing between
"horizontal" and "vertical" orientations of data.
--
David Portas
SQL Server MVP
--|||Well, the design of this table is relatively simple and straightforward...if
we had even reasonable hardware. I've been having to design tables around
poor hardware performance...trying to keep as little data as possible in any
given table, in order to prevent a meltdown.
The design was fine until the hardware hit the proverbial wall and
everything started crashing when even a moderate size query was run. So
instead of sinking a few thousand dollars into new hardware, they sank a few
thousand dollars into man hours, trying to solve the problem with intellect.
So far it's been relatively unsuccessful.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:DJOdnT3qqPhoSmbdRVn-ow@.giganews.com...
> Size of the data returned rather than number of columns and rows is a more
> relevant statistic but overall performance depends on other factors (such
as
> indexing) that are at least as important.
> > Ultimately, I'm trying to figure out if speed needs to be a
> > consideration in determining whether to orient a new table horizontally
or
> > vertically.
> I would suggest that you're going about your table design the wrong way!
> Normalize your design in (at minimum) Third Normal Form to start with.
Once
> you have done that you can test performance and make any optimizations
> necesssary. Correct design doesn't normally involve choosing between
> "horizontal" and "vertical" orientations of data.
> --
> David Portas
> SQL Server MVP
> --
>|||> In terms of the time a query would take to return, is there any difference
> if a table has, say, 200 records and 100 columns vs. 100 records and 200
> columns? Ultimately, I'm trying to figure out if speed needs to be a
> consideration in determining whether to orient a new table horizontally or
> vertically.
Your table design should be driven by normalizing the entities you are
trying to represent, not optimizing number of columns vs. number of rows.
If performance is a problem due to hardware constraints, fix the hardware.
Anything you do in the context of the above will be a bandaid at best, and
will only serve to yield *different* performance problems down the line,
IMHO.
--
http://www.aspfaq.com/
(Reverse address to reply.)

Efficiency Question

In terms of the time a query would take to return, is there any difference
if a table has, say, 200 records and 100 columns vs. 100 records and 200
columns? Ultimately, I'm trying to figure out if speed needs to be a
consideration in determining whether to orient a new table horizontally or
vertically.
Thanks,
JamesSize of the data returned rather than number of columns and rows is a more
relevant statistic but overall performance depends on other factors (such as
indexing) that are at least as important.

> Ultimately, I'm trying to figure out if speed needs to be a
> consideration in determining whether to orient a new table horizontally or
> vertically.
I would suggest that you're going about your table design the wrong way!
Normalize your design in (at minimum) Third Normal Form to start with. Once
you have done that you can test performance and make any optimizations
necesssary. Correct design doesn't normally involve choosing between
"horizontal" and "vertical" orientations of data.
David Portas
SQL Server MVP
--|||Well, the design of this table is relatively simple and straightforward...if
we had even reasonable hardware. I've been having to design tables around
poor hardware performance...trying to keep as little data as possible in any
given table, in order to prevent a meltdown.
The design was fine until the hardware hit the proverbial wall and
everything started crashing when even a moderate size query was run. So
instead of sinking a few thousand dollars into new hardware, they sank a few
thousand dollars into man hours, trying to solve the problem with intellect.
So far it's been relatively unsuccessful.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:DJOdnT3qqPhoSmbdRVn-ow@.giganews.com...
> Size of the data returned rather than number of columns and rows is a more
> relevant statistic but overall performance depends on other factors (such
as
> indexing) that are at least as important.
>
or[vbcol=seagreen]
> I would suggest that you're going about your table design the wrong way!
> Normalize your design in (at minimum) Third Normal Form to start with.
Once
> you have done that you can test performance and make any optimizations
> necesssary. Correct design doesn't normally involve choosing between
> "horizontal" and "vertical" orientations of data.
> --
> David Portas
> SQL Server MVP
> --
>|||> In terms of the time a query would take to return, is there any difference
> if a table has, say, 200 records and 100 columns vs. 100 records and 200
> columns? Ultimately, I'm trying to figure out if speed needs to be a
> consideration in determining whether to orient a new table horizontally or
> vertically.
Your table design should be driven by normalizing the entities you are
trying to represent, not optimizing number of columns vs. number of rows.
If performance is a problem due to hardware constraints, fix the hardware.
Anything you do in the context of the above will be a bandaid at best, and
will only serve to yield *different* performance problems down the line,
IMHO.
http://www.aspfaq.com/
(Reverse address to reply.)

Efficiency problems using PRINT in CURSORs

Hi,
I'm having a CURSOR running through ~45000 entries in a table. If I, for
example, use PRINT inside the cursor, can that be a performance hit? Are
there other performance things to think about when using cursors?
Thanks,
Mats-LennartCursors in general are not recommended. If you could discuss more about why
you are using cursors there could be alternate solutions that this newsgroup
can provide you with.
--
HTH,
SriSamp
Email: srisamp@.gmail.com
Blog: http://blogs.sqlxml.org/srinivassampath
URL: http://www32.brinkster.com/srisamp
"Mats-Lennart Hansson" <ap_skallen@.hotmail.com> wrote in message
news:ePBVg2ASGHA.5736@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I'm having a CURSOR running through ~45000 entries in a table. If I, for
> example, use PRINT inside the cursor, can that be a performance hit? Are
> there other performance things to think about when using cursors?
> Thanks,
> Mats-Lennart
>|||Thanks for your answer.
I know that it's not recommended, but in this case there is no other
(managable) solution. There is also no time for a redesign, so this is the
solution that will be used. However, I still wonder:
Can a lot of PRINTs be a performance problem? Are there other things that
can decrease performance, like user defined functions?
Thanks,
Mats-Lennart
"SriSamp" <ssampath@.sct.co.in> wrote in message
news:epxWI9ASGHA.5900@.tk2msftngp13.phx.gbl...
> Cursors in general are not recommended. If you could discuss more about
> why you are using cursors there could be alternate solutions that this
> newsgroup can provide you with.
> --
> HTH,
> SriSamp
> Email: srisamp@.gmail.com
> Blog: http://blogs.sqlxml.org/srinivassampath
> URL: http://www32.brinkster.com/srisamp
> "Mats-Lennart Hansson" <ap_skallen@.hotmail.com> wrote in message
> news:ePBVg2ASGHA.5736@.TK2MSFTNGP10.phx.gbl...
>|||Mats-Lennart Hansson wrote:
> Thanks for your answer.
> I know that it's not recommended, but in this case there is no other
> (managable) solution. There is also no time for a redesign, so this is the
> solution that will be used. However, I still wonder:
> Can a lot of PRINTs be a performance problem?
Of course. I don't know why you would use PRINT in a production system
however. PRINT is typically just debug code or for ad hoc stuff. Does
it matter if it's too late to change anyway? If performance is your
concern then PRINT may be insignificant next to the overhead of using a
cursor.

> Are there other things that
> can decrease performance, like user defined functions?
Generally speaking the more you do in a cursor loop the more processing
is required. One reason to prefer set-based solutions rather than
cursors is that the same isn't always true in declarative code -
performance doesn't necessarily degrade in line with complexity.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Hi,
I have no intentions in having a lot of PRINTs in our production system, why
I'm wondering is simply because of curiosity. Can I expect a performance
boost when going into production without PRINT's compared to now, when I'm
in development? Are there other things to do to improve efficiency? These
are questions I'm interested in. I want to learn from my mistakes to improve
my future projects :)
Why I'm using CURSORs in this project is because we are converting data from
one database into another. Unfortunately, the original data is not
consistent and needs to be checked before being converted. Of course there
are other solutions than using CURSORs, but this seemed to be the most
straightforward way.
Instead of looping through 50000 entries, can it be more efficient to run
through 5000 at a time, opening and closening the cursor in between? Could
this reduce some "overhead" costs?
Thanks for replying,
Mats-Lennart
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1142425512.174670.273740@.i39g2000cwa.googlegroups.com...
> Mats-Lennart Hansson wrote:
> Of course. I don't know why you would use PRINT in a production system
> however. PRINT is typically just debug code or for ad hoc stuff. Does
> it matter if it's too late to change anyway? If performance is your
> concern then PRINT may be insignificant next to the overhead of using a
> cursor.
>
> Generally speaking the more you do in a cursor loop the more processing
> is required. One reason to prefer set-based solutions rather than
> cursors is that the same isn't always true in declarative code -
> performance doesn't necessarily degrade in line with complexity.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>

efficiency of sql server on searching on text field

Hi

We have a application running on Sql server 2005, which require to browse/search text field. Does anyone know if Sql server's search/browse performance on text field is better than oracle?

The table the application will search on is a customer table that has a 10000 records in it, does this size of table casue a performance problem for sql server 2005 if I index the text field?

Please advise, thanks for your help!

Li

Id it a TEXT field or a field with characters stored in it ? How much data can be stored in the attribute ?

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

Actually it is text field with 10 character length

|||

Well 10k rows is not that much. It could be that SQL Server will even cache the rows or do a table scan rather than touch the index. but in terms of planning for the future, you should consider using an index if the attribute is queried a lot.

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||did u tried to create an index that match the search query

find below steps of choosing the most needed indexes

The following query will get the 10 missing indexes would produce the highest anticipated cumulative improvement, in descending order, for user queries.

SELECT TOP 10 *

FROM sys.dm_db_missing_index_group_stats

ORDER BY avg_total_user_cost * avg_user_impact * (user_seeks + user_scans)DESC

You can get the missing index details in the following way:

The following query determines which missing indexes comprise a particular missing index group, and displays their column details.

For the sake of this example, the missing index group handle is 24.(You will need to change the handle value with handle values which comes up from the earlier query)

SELECT migs.group_handle, mid.*

FROM sys.dm_db_missing_index_group_stats migs

INNER JOIN sys.dm_db_missing_index_groups mig

ON (migs.group_handle = mig.index_group_handle)

INNER JOIN sys.dm_db_missing_index_details mid

ON (mig.index_handle = mid.index_handle)

WHERE migs.group_handle = 24 <<put your handle value here>>

For details on this refer to the following articles:

http://msdn2.microsoft.com/en-us/library/ms345421.aspx

Using Missing Index Information to Write CREATE INDEX Statements

http://msdn2.microsoft.com/en-us/library/ms345405.aspx

Efficiency of SmallInt vs. SmallDatetime

I have a scheduling db (sql2000) that stores a lot of 'time' data and does
a lot of operations on this data. I'm storing event dates in a smalldatetime
field. I'm storing a 'starttime' in another smalldatetime field (eg. as
'1/1/1900 9:30') and 'endtime' in a third smalldatetime field ( eg. as
'1/1/1900 11:30').
Does anyone have any information/ideas on what performance improvements I
might see if was to instead store the 2 'time' fields as smallints (storing
number of minutes since midnight) rather than as smalldatetimes? I
understand I would halve the storage required, what about performance per
se?
Thanks,
Paul.
Hi
Every row in a Table has an overhead of about 20 bytes. Saving a byte here
or there does not help much. You have to do a lot more processing to convert
your time to smallint and back (in your code).
Have you tested the difference? Functions like dateadd and datediff and not
avilable to you if you don't use DateTime datatypes.
Regards
Mike
"Paul W" wrote:

> I have a scheduling db (sql2000) that stores a lot of 'time' data and does
> a lot of operations on this data. I'm storing event dates in a smalldatetime
> field. I'm storing a 'starttime' in another smalldatetime field (eg. as
> '1/1/1900 9:30') and 'endtime' in a third smalldatetime field ( eg. as
> '1/1/1900 11:30').
> Does anyone have any information/ideas on what performance improvements I
> might see if was to instead store the 2 'time' fields as smallints (storing
> number of minutes since midnight) rather than as smalldatetimes? I
> understand I would halve the storage required, what about performance per
> se?
> Thanks,
> Paul.
>
>

Efficiency of SmallInt vs. SmallDatetime

I have a scheduling db (sql2000) that stores a lot of 'time' data and does
a lot of operations on this data. I'm storing event dates in a smalldatetime
field. I'm storing a 'starttime' in another smalldatetime field (eg. as
'1/1/1900 9:30') and 'endtime' in a third smalldatetime field ( eg. as
'1/1/1900 11:30').
Does anyone have any information/ideas on what performance improvements I
might see if was to instead store the 2 'time' fields as smallints (storing
number of minutes since midnight) rather than as smalldatetimes? I
understand I would halve the storage required, what about performance per
se?
Thanks,
Paul.Hi
Every row in a Table has an overhead of about 20 bytes. Saving a byte here
or there does not help much. You have to do a lot more processing to convert
your time to smallint and back (in your code).
Have you tested the difference? Functions like dateadd and datediff and not
avilable to you if you don't use DateTime datatypes.
Regards
Mike
"Paul W" wrote:
> I have a scheduling db (sql2000) that stores a lot of 'time' data and does
> a lot of operations on this data. I'm storing event dates in a smalldatetime
> field. I'm storing a 'starttime' in another smalldatetime field (eg. as
> '1/1/1900 9:30') and 'endtime' in a third smalldatetime field ( eg. as
> '1/1/1900 11:30').
> Does anyone have any information/ideas on what performance improvements I
> might see if was to instead store the 2 'time' fields as smallints (storing
> number of minutes since midnight) rather than as smalldatetimes? I
> understand I would halve the storage required, what about performance per
> se?
> Thanks,
> Paul.
>
>

efficiency of query

I have the following 2 tables:

location:
placelftrgt
------
Europe099
England110
France1120
Italy2130
Asia100199
London1212

staff:
namelocLft
-----
Edwards0
Smith1
Leveil11
Rossi21
Lee12
Chan100

location uses the Celko hierarchy model.

I wish to retrieve for a location the names of all staff within it and
the hierarchy of place associated with that member of staff, eg a
query for Europe should return all staff in Europe, and for Lee I wish
to return Lee-London, Lee-England, Lee-Europe etc.

I can achieve this using a subquery, ie

SELECT name, place
FROM staff, location
WHERE name IN (SELECT name
FROM staff, location
WHERE place='Europe' And locLft>=location.lft And
locLft<=location.rgt)
AND locLft>=lft AND locLft<=rgt

But is this the most efficient way of doing so?

ThanksOn Thu, 08 Apr 2004 21:08:16 GMT, abracad wrote:

>I have the following 2 tables:
>location:
>placelftrgt
>------
>Europe099
>England110
>France1120
>Italy2130
>Asia100199
>London1212
>staff:
>namelocLft
>-----
>Edwards0
>Smith1
>Leveil11
>Rossi21
>Lee12
>Chan100
>location uses the Celko hierarchy model.
>I wish to retrieve for a location the names of all staff within it and
>the hierarchy of place associated with that member of staff, eg a
>query for Europe should return all staff in Europe, and for Lee I wish
>to return Lee-London, Lee-England, Lee-Europe etc.
>I can achieve this using a subquery, ie
>SELECT name, place
>FROM staff, location
>WHERE name IN (SELECT name
> FROM staff, location
> WHERE place='Europe' And locLft>=location.lft And
>locLft<=location.rgt)
> AND locLft>=lft AND locLft<=rgt
>But is this the most efficient way of doing so?
>Thanks

Maybe I am missing something, but it seems as if this would also do
the trick:

SELECT name, location
FROM staff
INNER JOIN location
ON locLft BETWEEN lft AND rgt

(Since you did not provide DDL and INSERT statements to recreate and
populate your tables, I can't test this)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

Efficiency of is null v\s nullif

hi ,
i am querying for data in which i compare nulls . And null = null for my
scenario. Thus i need to either use extra where conditions in the query to
check if the two compared fields are nulls. This i can do either by using is
null or nullif function. Which of the two options will be more efficient for
SQL to execute considering large data scenario?
eg:
Use NULLIF function
where (nullif(col1,col2) is null and nullif(col2,col1) is null
where not(nullif(col1,col2) is null and nullif(col2,col1) is null)
Use IS NULL construct
where ((col1 = col2) or (col1 is null and col2 is null)) where ((col1 <>
col2) or (col1 is null and col2 is not null) or (col1 is not null and col2
is null))prabhakar wrote:
> hi ,
> i am querying for data in which i compare nulls . And null = null for
> my scenario. Thus i need to either use extra where conditions in the
> query to check if the two compared fields are nulls. This i can do
> either by using is null or nullif function. Which of the two options
> will be more efficient for SQL to execute considering large data
> scenario? eg:
> Use NULLIF function
> where (nullif(col1,col2) is null and nullif(col2,col1) is null
> where not(nullif(col1,col2) is null and nullif(col2,col1) is null)
>
> Use IS NULL construct
> where ((col1 = col2) or (col1 is null and col2 is null)) where ((col1
> <> col2) or (col1 is null and col2 is not null) or (col1 is not null
> and col2 is null))
Not sure I understand. You have two WHERE clauses in each example. Can
you post the real SELECTs you are comparing. You can also check the
execution plans to see which one is cleaner.
David Gugick
Imceda Software
www.imceda.com|||I am comparing two fields in a table to check if they are not equal.This can
be done in following two ways
1) select * from tablefornull where ((col1 <> col2) or (col1 is null and
col2 is not null) or (col1 is not null and col2 is null))
2) select * from tablefornull where not(nullif(col1,col2) is null and
nullif(col2,col1) is null)
which one will be more efficient? The qeury execution plan is same for both
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:uJFAyhzEFHA.2572@.tk2msftngp13.phx.gbl...
> prabhakar wrote:
> Not sure I understand. You have two WHERE clauses in each example. Can you
> post the real SELECTs you are comparing. You can also check the execution
> plans to see which one is cleaner.
>
> --
> David Gugick
> Imceda Software
> www.imceda.com|||prabhakar wrote:
> I am comparing two fields in a table to check if they are not
> equal.This can be done in following two ways
> 1) select * from tablefornull where ((col1 <> col2) or (col1 is
> null and col2 is not null) or (col1 is not null and col2 is null))
> 2) select * from tablefornull where not(nullif(col1,col2) is null
> and nullif(col2,col1) is null)
> which one will be more efficient? The qeury execution plan is same
> for both
If the execution plan is the same, they will run exactly the same. I'm
guessing you are assuming for this exercise that NULL = NULL. Is that
correct?
How about this one:
Select * from tablefornull where isnull(col1, -999) = isnull(col2, -999)
David Gugick
Imceda Software
www.imceda.com|||prabhakar,
The performance difference in the actual processing of an individual row
not differ much between the two syntaxis. What could make a real
difference is using a syntax in a way that the optimizer can use indexes
so only the relevant rows need to be processed. Unfortunately, both
syntaxis will not achieve that.
But why do you want to compare NULLs? NULL is intended to mean something
like "unknown". In any case, the purpose of using NULLs is that one
row's NULL is not (never) equal to another row's NULL, by definition.
That is the definition that the ANSI SQL committee gave it. If you
reject that idea, you could take a look at "SET ANSI_NULLS OFF" in BOL,
and see if that works for you.
If you acknowledge the 'proper' use of NULLs, then you should never use
something like "nullif(col2,col1) is null". If there are some NULLs that
you want to treat as a values, then you could replace them with an
actual value, for example a predefined 'special' value. For varchar
columns this value could be '**Empty'. For int columns this could be
-2,147,483,648, etc.
After that you can use the following query. It is more complex than your
original query, but is likely to perform better.
SELECT *
FROM MyTable T1
WHERE NOT EXISTS (
SELECT 1
FROM MyTable T2
WHERE T2.KeyColumn = T1.KeyColumn
AND T2.Col1=T2.Col2
)
Hope this helps,
Gert-Jan
prabhakar wrote:
> hi ,
> i am querying for data in which i compare nulls . And null = null for my
> scenario. Thus i need to either use extra where conditions in the query to
> check if the two compared fields are nulls. This i can do either by using
is
> null or nullif function. Which of the two options will be more efficient f
or
> SQL to execute considering large data scenario?
> eg:
> Use NULLIF function
> where (nullif(col1,col2) is null and nullif(col2,col1) is null
> where not(nullif(col1,col2) is null and nullif(col2,col1) is null)
> Use IS NULL construct
> where ((col1 = col2) or (col1 is null and col2 is null)) where ((col1 <>
> col2) or (col1 is null and col2 is not null) or (col1 is not null and col2
> is null))

Efficiency of INSERT with multiple rows

Let's say that I am inserting rows into a table via a statement like this
INSERT INTO A SELECT * FROM B
And let's say that table B has 1,000,000 rows
I was just wondering if SQL Server rebuilds the indexes on table A after
each row is inserted or if it waits until all 1,000,000 rows are inserted
and then rebuilds the indexes at the end? If it rebuilds the indexes upon
each insert then I would probably drop the indexes first and then just add
them at the end.
Does anybody know?
Thanks
Richard Speiss"Richard Speiss" <rspeiss@.mtxinc.com> wrote in message
news:utEtTH6NEHA.2468@.TK2MSFTNGP11.phx.gbl...
> Let's say that I am inserting rows into a table via a statement like this
> INSERT INTO A SELECT * FROM B
> And let's say that table B has 1,000,000 rows
> I was just wondering if SQL Server rebuilds the indexes on table A after
> each row is inserted or if it waits until all 1,000,000 rows are inserted
> and then rebuilds the indexes at the end? If it rebuilds the indexes upon
> each insert then I would probably drop the indexes first and then just add
> them at the end.
THe index is not 'rebuild' upon each insert. It is updated on each insert.
Since 'table splits' on indexes can be costly, it is sometimes, a good idea
to remove the index.
But that depends, if you have a very large table with bilions of rows and
remove and reapply the index :<<
ps: For batch inserting and bulking rows, always initiate an explicit
transation!

> Does anybody know?
> Thanks
> Richard Speiss
>|||Oops, I did mean does the index get updated (bad wording on my part. I
didn't expect the entire thing to be rebuilt).
Thanks for the reply
Richard

> THe index is not 'rebuild' upon each insert. It is updated on each insert.
> Since 'table splits' on indexes can be costly, it is sometimes, a good
idea
> to remove the index.
> But that depends, if you have a very large table with bilions of rows and
> remove and reapply the index :<<
> ps: For batch inserting and bulking rows, always initiate an explicit
> transation!
>|||Richard,
SQL Server does not rebuild the indexes as data is inserted, rather the
indexes are "maintained". i.e. if you insert a new row into the table, then
all indexes on the table will need to have the index pages maintained at the
same time.
Sometimes you will find that dropping all indexes, doing a large insert, and
then creating the index after the load is quicker than doing the load with
the indexes defined. Sometimes you won't. Only testing will reveal which way
will be quick for your environment.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
"Richard Speiss" <rspeiss@.mtxinc.com> wrote in message
news:utEtTH6NEHA.2468@.TK2MSFTNGP11.phx.gbl...
> Let's say that I am inserting rows into a table via a statement like this
> INSERT INTO A SELECT * FROM B
> And let's say that table B has 1,000,000 rows
> I was just wondering if SQL Server rebuilds the indexes on table A after
> each row is inserted or if it waits until all 1,000,000 rows are inserted
> and then rebuilds the indexes at the end? If it rebuilds the indexes upon
> each insert then I would probably drop the indexes first and then just add
> them at the end.
> Does anybody know?
> Thanks
> Richard Speiss
>

Efficiency of INSERT with multiple rows

Let's say that I am inserting rows into a table via a statement like this
INSERT INTO A SELECT * FROM B
And let's say that table B has 1,000,000 rows
I was just wondering if SQL Server rebuilds the indexes on table A after
each row is inserted or if it waits until all 1,000,000 rows are inserted
and then rebuilds the indexes at the end? If it rebuilds the indexes upon
each insert then I would probably drop the indexes first and then just add
them at the end.
Does anybody know?
Thanks
Richard Speiss
"Richard Speiss" <rspeiss@.mtxinc.com> wrote in message
news:utEtTH6NEHA.2468@.TK2MSFTNGP11.phx.gbl...
> Let's say that I am inserting rows into a table via a statement like this
> INSERT INTO A SELECT * FROM B
> And let's say that table B has 1,000,000 rows
> I was just wondering if SQL Server rebuilds the indexes on table A after
> each row is inserted or if it waits until all 1,000,000 rows are inserted
> and then rebuilds the indexes at the end? If it rebuilds the indexes upon
> each insert then I would probably drop the indexes first and then just add
> them at the end.
THe index is not 'rebuild' upon each insert. It is updated on each insert.
Since 'table splits' on indexes can be costly, it is sometimes, a good idea
to remove the index.
But that depends, if you have a very large table with bilions of rows and
remove and reapply the index :<<
ps: For batch inserting and bulking rows, always initiate an explicit
transation!

> Does anybody know?
> Thanks
> Richard Speiss
>
|||Oops, I did mean does the index get updated (bad wording on my part. I
didn't expect the entire thing to be rebuilt).
Thanks for the reply
Richard

> THe index is not 'rebuild' upon each insert. It is updated on each insert.
> Since 'table splits' on indexes can be costly, it is sometimes, a good
idea
> to remove the index.
> But that depends, if you have a very large table with bilions of rows and
> remove and reapply the index :<<
> ps: For batch inserting and bulking rows, always initiate an explicit
> transation!
>
|||Richard,
SQL Server does not rebuild the indexes as data is inserted, rather the
indexes are "maintained". i.e. if you insert a new row into the table, then
all indexes on the table will need to have the index pages maintained at the
same time.
Sometimes you will find that dropping all indexes, doing a large insert, and
then creating the index after the load is quicker than doing the load with
the indexes defined. Sometimes you won't. Only testing will reveal which way
will be quick for your environment.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
"Richard Speiss" <rspeiss@.mtxinc.com> wrote in message
news:utEtTH6NEHA.2468@.TK2MSFTNGP11.phx.gbl...
> Let's say that I am inserting rows into a table via a statement like this
> INSERT INTO A SELECT * FROM B
> And let's say that table B has 1,000,000 rows
> I was just wondering if SQL Server rebuilds the indexes on table A after
> each row is inserted or if it waits until all 1,000,000 rows are inserted
> and then rebuilds the indexes at the end? If it rebuilds the indexes upon
> each insert then I would probably drop the indexes first and then just add
> them at the end.
> Does anybody know?
> Thanks
> Richard Speiss
>

Efficiency of INSERT with multiple rows

Let's say that I am inserting rows into a table via a statement like this
INSERT INTO A SELECT * FROM B
And let's say that table B has 1,000,000 rows
I was just wondering if SQL Server rebuilds the indexes on table A after
each row is inserted or if it waits until all 1,000,000 rows are inserted
and then rebuilds the indexes at the end? If it rebuilds the indexes upon
each insert then I would probably drop the indexes first and then just add
them at the end.
Does anybody know?
Thanks
Richard Speiss"Richard Speiss" <rspeiss@.mtxinc.com> wrote in message
news:utEtTH6NEHA.2468@.TK2MSFTNGP11.phx.gbl...
> Let's say that I am inserting rows into a table via a statement like this
> INSERT INTO A SELECT * FROM B
> And let's say that table B has 1,000,000 rows
> I was just wondering if SQL Server rebuilds the indexes on table A after
> each row is inserted or if it waits until all 1,000,000 rows are inserted
> and then rebuilds the indexes at the end? If it rebuilds the indexes upon
> each insert then I would probably drop the indexes first and then just add
> them at the end.
THe index is not 'rebuild' upon each insert. It is updated on each insert.
Since 'table splits' on indexes can be costly, it is sometimes, a good idea
to remove the index.
But that depends, if you have a very large table with bilions of rows and
remove and reapply the index :<<
ps: For batch inserting and bulking rows, always initiate an explicit
transation!
> Does anybody know?
> Thanks
> Richard Speiss
>|||Oops, I did mean does the index get updated (bad wording on my part. I
didn't expect the entire thing to be rebuilt).
Thanks for the reply
Richard
> THe index is not 'rebuild' upon each insert. It is updated on each insert.
> Since 'table splits' on indexes can be costly, it is sometimes, a good
idea
> to remove the index.
> But that depends, if you have a very large table with bilions of rows and
> remove and reapply the index :<<
> ps: For batch inserting and bulking rows, always initiate an explicit
> transation!
>|||Richard,
SQL Server does not rebuild the indexes as data is inserted, rather the
indexes are "maintained". i.e. if you insert a new row into the table, then
all indexes on the table will need to have the index pages maintained at the
same time.
Sometimes you will find that dropping all indexes, doing a large insert, and
then creating the index after the load is quicker than doing the load with
the indexes defined. Sometimes you won't. Only testing will reveal which way
will be quick for your environment.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
"Richard Speiss" <rspeiss@.mtxinc.com> wrote in message
news:utEtTH6NEHA.2468@.TK2MSFTNGP11.phx.gbl...
> Let's say that I am inserting rows into a table via a statement like this
> INSERT INTO A SELECT * FROM B
> And let's say that table B has 1,000,000 rows
> I was just wondering if SQL Server rebuilds the indexes on table A after
> each row is inserted or if it waits until all 1,000,000 rows are inserted
> and then rebuilds the indexes at the end? If it rebuilds the indexes upon
> each insert then I would probably drop the indexes first and then just add
> them at the end.
> Does anybody know?
> Thanks
> Richard Speiss
>

Efficiency of a ''SELECT TOP'' style GROUP BY query: FREETEXT vs. FREETEXTTABLE

Hi,

Please have a look at the following two queries, the purpose of which is to find which ten users (represented by 'Username') have created the most records which contain the term 'foo':

SELECT TOP 10 Username, COUNT(*) AS [Count] FROM Options

WHERE FREETEXT(*, 'foo')

GROUP BY Username

ORDER BY [Count] DESC

SELECT TOP 10 Username, COUNT(*) AS [Count] FROM Options

JOIN FREETEXTTABLE (Options, *, 'foo', 500) ct

ON OptionID = ct.[KEY]

GROUP BY Username

ORDER BY [Count] DESC

They both produce the same result set. However, I am wondering which is more performant. At first glance, it would seem the first one would be. It doesn't involve a JOIN and should, therefore, be more efficient.

But this depends on how the FREETEXT expression is evaluated. My concern is that internally, SQL Server would generate an entire recordset based on 'WHERE FREETEXT(*, 'foo')', which could be thousands of records, and only then restrict this to the TOP 10 by COUNT.

If this does happen, then it would be better to join to a FREETEXTTABLE, where I can at least restrict the result set using the 'top_n_by_rank' parameter (which is set as '500' in this case, as this seems a good balance of performance against the likely number of duplicates I will get in my FREETEXTTABLE results).

So... I am worrying about this unnecessarily? Should I just use the simpler first version?

Any thoughts appreciated.

Thanks

They are almost identical. Implicitly when you do freetext(*,'foo') the system will convert it to a join based on the key. That means the first query will be implicitly converted to:

Code Snippet

select top 10 username, count(*) [count]

from options o join freetexttable(options,*,'foo') ct on o.optionid=ct.key

group by username

order by [count] desc

which is obviously not as efficient as the second query. However, the result between the two will also differ. For the first query, you're doing the join, group by, and then select the top N. The second you limit the first 500 from FTS query before doing the join, group by, and then select top N. So, you've been lucky thus far because the top 500 returned by the FTS contain the top desired 10 for your second query.

|||

Hi,

Thanks very much - that is exactly the info I was looking for.

I chose '500' as the top_n_by_rank parameter based on a calculation that, having considered all the other factors in the project I am working on, I am likely to get at least 10 results in the second query in the vast majority of cases.

Thanks for your help.

Efficiency issue with Parameters

Hi all.

I am new to reporting services and I am having an efficiency problem when loading my report.

I would like to know how Reporting Services handles its datasets.

1: Lets say I have 3 parameters. All set to retrieve data from the same dataset. Does reporting services execute the Query 3 times to get the results for each parameter ? If so, is there a way around this ? I am having a great performance hit with this if it is the case.

2: I am also having an issue with a data processing extensions, when my multi-valued parameter reads the fields from the dataset.. it inserts duplicates and not distinct values, Do i need to explicitly select distinct values in the data processing extension or should Reporting Services automatically do this ?

Any help is greatly appreciated.
Regards,
Neil

1. No, AFAIK this is the behaviour by design. The dataset will be executed several times.

2. I did not get you with that one, could you explain this a bit more ?

Jens k. Suessmeyer.

http://www.sqlserver2005.de

|||Hi Jens K,

thanks so much for the reply.

To explain my 2nd question.
When i retrieve a query lets say [Select names from Name] and it retrives
- Neil
- Jens
- Neil
- Jens

The multi-valued parameter should only contain Neil and Jens .. i.e not show repeate values.
This seems to be the case when I call the query from a standard sql query in the query designer

But when executing the same command in a data processing extension (I return a dataTable with the values) the parameter contains i.e ( Neil , Jens, Neil, Jens)

Does this sound right ? I could be doing something wrong ... just not sure..

Regards,
Neil
|||Yes, and this is also by design. Imagine a situation where you have several Names but different id associated with the names, then you probably want the names to appear, although they are the same. If you only want to use a single instance of each name, you will have to find a way to get only one instance per name like using DISTINCT, Group by or anything else on the basequery. if you already have a datatable you could use following hints:

Creating a DataTable from a DataView
http://msdn2.microsoft.com/en-us/library/73kk32zz(vs.80).aspx

http://support.microsoft.com/kb/325685/en-us

Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||Thanks Jens K.

Really appreciate your help!!
|||Hi Jens K.

I have a follow up question quickly relating to my first question asked.

If reporting services executes the dataset multiple times. Say I set the available values of a parameter to Dataset A and the Default values to Dataset A. Does this mean the dataset is executed twice ? This seems highly inefficient....

once again, much appreciate all your help.

Regards,
Neil
|||Yes, this is (sort of) by design as the resultsets could be different based on (non-)determinsitic parameters.

Jens K. Suessmeyer.

http://www.sqlserver2005.de

Efficiency in inserting Null Values into fields which allow nulls.

Hi,
I have fields in my table which allow nulls. Is it efficient to not insert anything (the field automatically shows up as null in this case) and leave or store some value into it. The field is a smallint field?

Thanksheres some info from BOL :

Allowing Null Values
The nullability of a column determines if the rows in the table can contain a null value for that column. A null value, or NULL, is not the same as zero (0), blank, or a zero-length character string such as ""; NULL means that no entry has been made. The presence NULL usually implies that the value is either unknown or undefined. For example, a null value in the price column of the titles table of the pubs database does not mean that the book has no price; NULL means that the price is unknown or has not been set.In general, avoid permitting null values because they incur more complexity in queries and updates and because there are other column options, such as PRIMARY KEY constraints, that cannot be used with nullable columns.

If a row is inserted but no value is included for a column that allows null values, Microsoft® SQL Server? 2000 supplies the value NULL (unless a DEFAULT definition or object exists). A column defined with the keyword NULL also accepts an explicit entry of NULL from the user, no matter what data type it is or if it has a default associated with it. The value NULL should not be placed within quotation marks because it will be interpreted as the character string 'NULL', rather than the null value.

Specifying a column as not permitting null values can help maintain data integrity by ensuring that a column in a row always contains data. If null values are not allowed, the user entering data in the table must enter a value in the column or the table row cannot be accepted into the database.

Note Columns defined with a PRIMARY KEY constraint or IDENTITY property cannot allow null values.
|||To be honest I'm not sure. Once you've made the decision to use NULLs (and there are lots of pros/cons to that) I doubt there is much in it. However, if you have a choice *and* you're really trying to eek out the last drop of performance then I'd avoid NULLs. Having said that I'm sure there are thousands of other places to look for better optimisations before you go here.