script to remove nulls from DB?

Hi all.

I've been tasked with "speeding up" a mid-sized production system. It
is riddled with nulls... "IsNull" all over the procs, etc.

Is it worth it to get rid of the nulls and not allow them in the
columns anymore?

If so, how to go about removing the nulls with a script?

Thanks so much,

Steve in Norcal"Steve Walker" <swalker@.ainet.com> wrote in message
news:bde87b71.0405240929.326abc53@.posting.google.c om...
> Hi all.
> I've been tasked with "speeding up" a mid-sized production system. It
> is riddled with nulls... "IsNull" all over the procs, etc.
> Is it worth it to get rid of the nulls and not allow them in the
> columns anymore?
> If so, how to go about removing the nulls with a script?
> Thanks so much,
> Steve in Norcal

You need to consider the data model first - if it has a good reason for
requiring certain columns to be NULLable, then you should leave them.
Although in general, if you have a large number of NULLable columns, then
your data model probably needs to be reviewed, as it suggests you may have
issues such as incomplete data, or denormalized tables.

To make a column NOT NULL from a script, you can use ALTER TABLE:

ALTER TABLE dbo.MyTable
ALTER COLUMN SomeColumn INT NOT NULL

Simon|||[posted and mailed, please reply in news]

Steve Walker (swalker@.ainet.com) writes:
> I've been tasked with "speeding up" a mid-sized production system. It
> is riddled with nulls... "IsNull" all over the procs, etc.
> Is it worth it to get rid of the nulls and not allow them in the
> columns anymore?

Probably not. As Simon said, the columns are likely to be nullable for
a reason.

Then again, I would not be surprised if some columns are nullable only
because of lax design.

It's better to track down slow-running queries, and see how you can
improve them, either by rewriting or better indexing.

Lots of isnull does not have to be bad, but it can be. Take these
two examples:

SELECT col1, col2, col3
FROM tbl
WHERE thiscol = isnull(@.par, ' ')

SELECT col1, col2, col3
FROM tbl
WHERE isnull(thiscol, ' ') = isnull(@.par, ' ')

The first is OK, and the cost of isnull is likely to be minimal. The
second, however, is potentially bad. This is because, if there is an
index on thiscol, then SQL Server can not seek this index to find
the data. As soon as a column is embedded into an expression, it
is disqualified from index seeks. This is because the index is
organized after the values of the column, not the values of the
expression.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.aspsql

Script SQL 2005 database to individual files?

I am trying to script the DROP(IF EXISTS) and CREATE for all of my
tables, views, stored procs, and functions to individual SQL text files
(one per object). This was trivially done in SQL 2000 with Enterprise
Manager, but when I try in SQL 2005 through Management Studio my only
"script mode" options are:

- Script to file (which is one huge file with everything)
- Script to Clipboard
- Script to New Query Window

FYI, I get to this screen through Management Studio by right clicking
on a database and selecting Tasks > Generate Scripts... > Next (doesn't
seem to matter what combo of objects I select to script or what other
options). I am using SQL Server 2005 Developer (which is Microsoft SQL
Server Management Studio 9.00.1399.00).

Any solution to this (i.e. via Management Studio, command line, etc.)
would be greatly appreciated.

Thanks.
TedMSDN/BOL indicates that this is possible here
http://msdn2.microsoft.com/en-us/library/ms191299.aspx. However, it
doesn't give any clue on how to accomplish it.

Quote from that page: "The schema for generated objects can be saved in
a single SQL Script file, or in several files with each file containing
the schema of just one object."|||Ted O'Connor (toconnor@.gmail.com) writes:
> I am trying to script the DROP(IF EXISTS) and CREATE for all of my
> tables, views, stored procs, and functions to individual SQL text files
> (one per object). This was trivially done in SQL 2000 with Enterprise
> Manager, but when I try in SQL 2005 through Management Studio my only
> "script mode" options are:
> - Script to file (which is one huge file with everything)
> - Script to Clipboard
> - Script to New Query Window
> FYI, I get to this screen through Management Studio by right clicking
> on a database and selecting Tasks > Generate Scripts... > Next (doesn't
> seem to matter what combo of objects I select to script or what other
> options). I am using SQL Server 2005 Developer (which is Microsoft SQL
> Server Management Studio 9.00.1399.00).
> Any solution to this (i.e. via Management Studio, command line, etc.)
> would be greatly appreciated.

Rather than hackiong SMO on your own, I don't think there is one.
Note that there are really two features from SQL 2000 you are missing:
1) One object per file.
2) A script that performs both DROP and CREATE.

There is this suggestion on MSDN Product Feedback Centre,
http://lab.msdn.microsoft.com/produ...px?feedbackid=9
eb6c773-2dbb-4a27-b9d8-225d6ed4385a
the by far most voted-on item for SQL Server.

I did actually only find one item that brings up one file per object,
http://lab.msdn.microsoft.com/produ...px?feedbackid=2
7695db7-cef6-42c3-9cb0-ac30583bbee9
there are no votes here, beside the submitted, but that is because the
bug has not been validated.

In any case, that would be better as a suggestion, as it is not a bug
that you can't script per object, just a poor design. So if you can't
find a suggestion on that theme - submit one.

--
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

script for trace form Query analyzer ?

I want to know which procs and triggers that are taking a lot of time to run. I cannot run the trace coz its slowing down. There are lot of procs and triggers written over several years.
TIA
Are you saying that Profiler is resulting in performance degradation? On the
client or server?
You might want to trace using system stored procedures, on the server side.
This is much better than running Profiler interactively.
For more info:
http://vyaskn.tripod.com/server_side...sql_server.htm
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"DallasBlue" <DallasBlue@.discussions.microsoft.com> wrote in message
news:DB0143FE-769C-486D-8473-EA2CD95DBE17@.microsoft.com...
I want to know which procs and triggers that are taking a lot of time to
run. I cannot run the trace coz its slowing down. There are lot of procs
and triggers written over several years.
TIA
|||Do you by any chance know which systables can I look up to get the same info as from the server procs.
or else by any chance do you know how to look at the script contained in the below procs
sp_trace_create
sp_trace_generateevent
sp_trace_setevent
sp_trace_setfilter
sp_trace_setstatus
fn_trace_getfilterinfo
fn_trace_getinfo Returns
fn_trace_gettable Returns
thanks !!
"Narayana Vyas Kondreddi" wrote:

> Are you saying that Profiler is resulting in performance degradation? On the
> client or server?
> You might want to trace using system stored procedures, on the server side.
> This is much better than running Profiler interactively.
> For more info:
> http://vyaskn.tripod.com/server_side...sql_server.htm
> --
> HTH,
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
> Is .NET important for a database professional?
> http://vyaskn.tripod.com/poll.htm
>
> "DallasBlue" <DallasBlue@.discussions.microsoft.com> wrote in message
> news:DB0143FE-769C-486D-8473-EA2CD95DBE17@.microsoft.com...
> I want to know which procs and triggers that are taking a lot of time to
> run. I cannot run the trace coz its slowing down. There are lot of procs
> and triggers written over several years.
> TIA
>
>
|||This info is not stored in any of the system tables.
I think the server side trace stored procs are implemented as extended
stored procs, so the source code is not available
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"DallasBlue" <DallasBlue@.discussions.microsoft.com> wrote in message
news:AA76511D-FD1C-4670-B083-78FF5A3F65E7@.microsoft.com...
Do you by any chance know which systables can I look up to get the same info
as from the server procs.
or else by any chance do you know how to look at the script contained in the
below procs
sp_trace_create
sp_trace_generateevent
sp_trace_setevent
sp_trace_setfilter
sp_trace_setstatus
fn_trace_getfilterinfo
fn_trace_getinfo Returns
fn_trace_gettable Returns
thanks !!
"Narayana Vyas Kondreddi" wrote:

> Are you saying that Profiler is resulting in performance degradation? On
the
> client or server?
> You might want to trace using system stored procedures, on the server
side.
> This is much better than running Profiler interactively.
> For more info:
> http://vyaskn.tripod.com/server_side...sql_server.htm
> --
> HTH,
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
> Is .NET important for a database professional?
> http://vyaskn.tripod.com/poll.htm
>
> "DallasBlue" <DallasBlue@.discussions.microsoft.com> wrote in message
> news:DB0143FE-769C-486D-8473-EA2CD95DBE17@.microsoft.com...
> I want to know which procs and triggers that are taking a lot of time to
> run. I cannot run the trace coz its slowing down. There are lot of procs
> and triggers written over several years.
> TIA
>
>

script for trace form Query analyzer ?

I want to know which procs and triggers that are taking a lot of time to run
. I cannot run the trace coz its slowing down. There are lot of procs and t
riggers written over several years.
TIAAre you saying that Profiler is resulting in performance degradation? On the
client or server?
You might want to trace using system stored procedures, on the server side.
This is much better than running Profiler interactively.
For more info:
http://vyaskn.tripod.com/server_sid..._sql_server.htm
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"DallasBlue" <DallasBlue@.discussions.microsoft.com> wrote in message
news:DB0143FE-769C-486D-8473-EA2CD95DBE17@.microsoft.com...
I want to know which procs and triggers that are taking a lot of time to
run. I cannot run the trace coz its slowing down. There are lot of procs
and triggers written over several years.
TIA|||Do you by any chance know which systables can I look up to get the same info
as from the server procs.
or else by any chance do you know how to look at the script contained in the
below procs
sp_trace_create
sp_trace_generateevent
sp_trace_setevent
sp_trace_setfilter
sp_trace_setstatus
fn_trace_getfilterinfo
fn_trace_getinfo Returns
fn_trace_gettable Returns
thanks !!
"Narayana Vyas Kondreddi" wrote:

> Are you saying that Profiler is resulting in performance degradation? On t
he
> client or server?
> You might want to trace using system stored procedures, on the server side
.
> This is much better than running Profiler interactively.
> For more info:
> http://vyaskn.tripod.com/server_sid..._sql_server.htm
> --
> HTH,
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
> Is .NET important for a database professional?
> http://vyaskn.tripod.com/poll.htm
>
> "DallasBlue" <DallasBlue@.discussions.microsoft.com> wrote in message
> news:DB0143FE-769C-486D-8473-EA2CD95DBE17@.microsoft.com...
> I want to know which procs and triggers that are taking a lot of time to
> run. I cannot run the trace coz its slowing down. There are lot of procs
> and triggers written over several years.
> TIA
>
>|||This info is not stored in any of the system tables.
I think the server side trace stored procs are implemented as extended
stored procs, so the source code is not available
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"DallasBlue" <DallasBlue@.discussions.microsoft.com> wrote in message
news:AA76511D-FD1C-4670-B083-78FF5A3F65E7@.microsoft.com...
Do you by any chance know which systables can I look up to get the same info
as from the server procs.
or else by any chance do you know how to look at the script contained in the
below procs
sp_trace_create
sp_trace_generateevent
sp_trace_setevent
sp_trace_setfilter
sp_trace_setstatus
fn_trace_getfilterinfo
fn_trace_getinfo Returns
fn_trace_gettable Returns
thanks !!
"Narayana Vyas Kondreddi" wrote:

> Are you saying that Profiler is resulting in performance degradation? On
the
> client or server?
> You might want to trace using system stored procedures, on the server
side.
> This is much better than running Profiler interactively.
> For more info:
> http://vyaskn.tripod.com/server_sid..._sql_server.htm
> --
> HTH,
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
> Is .NET important for a database professional?
> http://vyaskn.tripod.com/poll.htm
>
> "DallasBlue" <DallasBlue@.discussions.microsoft.com> wrote in message
> news:DB0143FE-769C-486D-8473-EA2CD95DBE17@.microsoft.com...
> I want to know which procs and triggers that are taking a lot of time to
> run. I cannot run the trace coz its slowing down. There are lot of procs
> and triggers written over several years.
> TIA
>
>

SCOPE_IDENTITY() vs. @@IDENTITY

I have a basic C# application that is trying to INSERT a row and get the ID. Really simple; there are no triggers, no stored procs or functions were involved, the app is single threaded, there is currently only one user. I have a really basic table with a INTEGER IDENTITY PK column. All very standard.

If I do the INSERT followed by a "SELECT @.@.IDENTITY" on the same connection, it works correctly.
If I use SCOPE_IDENTITY() instead, it returns NULL. I use SCOPE_IDENTITY on a variety of other occassions and it works fine. Why would this be? I thought SCOPE_IDENTITY() was the preferred replacement to @.@.IDENTITY.

I guess what I have is satisfactory but this was frustrating and I want to know why.

This is on SQL Server 2000 Standard edition with version SP3a + hot fixeshai roger,

SCOPE_IDENTITY and @.@.IDENTITY will return last identity values generated in any table in the current session. However, SCOPE_IDENTITY returns values inserted only within the current scope. @.@.IDENTITY is not limited to a specific scope.

This is the example given in BOL

Suppose if you have two tables, T1 and T2, and an INSERT trigger defined on T1. When a row is inserted to T1, the trigger fires and inserts a row in T2. This scenario illustrates two scopes: the insert on T1, and the insert on T2 as a result of the trigger.

Assuming that both T1 and T2 have IDENTITY columns, @.@.IDENTITY and SCOPE_IDENTITY will return different values at the end of an INSERT statement on T1.

@.@.IDENTITY will return the last IDENTITY column value inserted across any scope in the current session, which is the value inserted in T2.

SCOPE_IDENTITY() will return the IDENTITY value inserted in T1, which was the last INSERT that occurred in the same scope. The SCOPE_IDENTITY() function will return the NULL value if the function is invoked before any insert statements into an identity column occur in the scope.

Hope this relives u of ur frustration|||Yes, I read BOL and understand the documented theoretical and conceptual differences between the two. However, those differences don't apply to my situation.

There is only one thread, one process, one identity value, and one table involved. There are no triggers or functions or stored procs involved. It's a simple INSERT/get ID situation. And @.@.IDENTITY works and SCOPE_IDENTITY doesn't work which just doesn't make any sense according to what I've read.