changing the types of particular columns in particular table in the database

hi,
i have more than 300 tables in the database. Out of that tables some table has column whose type is "char".I want to change only those tables' column to the type "nvarchar".
Is it possible to make the modification all at a once?
since i am changing each and every table , this takes a lots of time.

thanks in advance,
by
singam

Hi,

first of all there is no built in function for this. YOu have to do that one by one in the script, there is sure a chance to do this automagically but I am no fan of a full automatic procedure to modify the schema of a bunch of tables.

You can (as I always do ) let the SQL Server create the script for you and execute this on your own in bacthes for better error handling (as you didn′t point out if error handling is applicable for you like in SQL Server 2005)

SELECT 'ALTER TABLE ' + C.TABLE_NAME +' ALTER COLUMN ' + C.COLUMN_NAME + ' VARCHAR(50)' --New data type and length
FROM INFORMATION_SCHEMA.Columns C
INNER JOIN INFORMATION_SCHEMA.Tables T
ON
T.TABLE_CATALOG = C.TABLE_CATALOG AND
T.TABLE_SCHEMA = C.TABLE_SCHEMA AND
T.TABLE_NAME = C.TABLE_NAME
WHERE
DATA_TYPE = 'NVARCHAR' AND --Old Type
CHARACTER_MAXIMUM_LENGTH = 50 AND --Old lenght
TABLE_TYPE = 'BASE TABLE'

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

Changing the schema ownership on subscriber

Hi,

I have a replication in which publisher, distributer and subscriber all runing on sql server 2005. all the tables that needs to be replicated are under 'dbo' schema on publisher.

Subscriber is a datawarehouse so i dont want to put tables coming from a system to go under dbo schema as there might be other application replicating same name tables to warehouse. i have created a schema for my application on warehouse but dont know how to tell replication to create tables under application schema created on subscriber. I am using snapshot replication that can be reinitialized if required.

any help will be appriciated.

Cheers,

Furrukh baig

Just found it myself. go to publication properties. click articles and then right click on any article and select "set properties for all tables" ... this will open the property window ... look for "destination object owner" and specify the value of schema name on target.

thanks,

Furrukh Baig

Changing the columns of a SELECT statement.

I have been "noramlizing" some of the tables that we have been using and I
ran into a problem in one of our stored procedures.
It used to be (with the old table) that you could do something like:
SELECT A, B FROM OldTable
The stored procedure essentially does this.
Now with the new table(s) it is something like:
SELECT *
FROM Table
LEFT OUTER JOIN Attributes ON Attributes.AttributesId = Table.AttributesId
There is in the Attributes table an AttibutesTypeId and an AttributesValue.
So I get 'A' only when AttributesTypeId = 1 and 'B' when AttributesTypeId =
2. The question is how do I return A and B from the stored procedure given
this new table structure? I am tempted to create a temporary table and use a
cursor to move row by row through the table, but there must be a more
efficient way.
Thank you.Kevin
I need a bit more clarification, if you don't mind
You used to have One table called "Big Daddy" now there are 3 tables I
presume.
Table, Attributes and Attributetype
if that assumption is correct, let us know how the columns(old table) have
been distributed in the normalized structure(have the static columns A and B
been converted to AttributeType) , there is probably a way to do it without
the cursor, but right now it is very hard to understand your problem.
Also can you give me the query again that you are trying to run from the OLD
table.
In the query you have specified , it's confusing when you are say the
Attribute has AttributeTypeId but you join it to Table using AttributesId
column, so seems like there is a 3rd table called AttributesType.
it might be just simpler to post the DDL , you don't mind doing it.
Rakesh Ajwani
MCSD, MCSD.NET
"Kevin Burton" wrote:

> I have been "noramlizing" some of the tables that we have been using and I
> ran into a problem in one of our stored procedures.
> It used to be (with the old table) that you could do something like:
> SELECT A, B FROM OldTable
> The stored procedure essentially does this.
> Now with the new table(s) it is something like:
> SELECT *
> FROM Table
> LEFT OUTER JOIN Attributes ON Attributes.AttributesId = Table.AttributesId
> There is in the Attributes table an AttibutesTypeId and an AttributesValue
.
> So I get 'A' only when AttributesTypeId = 1 and 'B' when AttributesTypeId
=
> 2. The question is how do I return A and B from the stored procedure given
> this new table structure? I am tempted to create a temporary table and use
a
> cursor to move row by row through the table, but there must be a more
> efficient way.
> Thank you.
>|||Find the moron that did this to you and kill him. This is called a EAV
design and it is totally wrong in an RDBMS. It confuses data and meta
data and makes your most basic queries run several orders of magnitude
slower. But the lack of speed does not matter; you will have no data
integrity so you cannot trust the answers anyway.
You might also want to get any book on data modeling. Then you will
know that there cannot be such a creature is a "type_id" -- the data
element is either a type with a known code or it is an identifier of
some kind of entity.
I am on a borrowed computer in Brazil, so I cannot cut & paste the
painful details; you can Google old postings on EAV, OTLT and MUCK as
bad design decisions frequently made by non-SQL programmers.|||Kevin Burton wrote:
> I have been "noramlizing" some of the tables that we have been using and I
> ran into a problem in one of our stored procedures.
> It used to be (with the old table) that you could do something like:
> SELECT A, B FROM OldTable
> The stored procedure essentially does this.
> Now with the new table(s) it is something like:
> SELECT *
> FROM Table
> LEFT OUTER JOIN Attributes ON Attributes.AttributesId = Table.AttributesId
> There is in the Attributes table an AttibutesTypeId and an AttributesValue
.
> So I get 'A' only when AttributesTypeId = 1 and 'B' when AttributesTypeId
=
> 2. The question is how do I return A and B from the stored procedure given
> this new table structure? I am tempted to create a temporary table and use
a
> cursor to move row by row through the table, but there must be a more
> efficient way.
> Thank you.
That is what you call "normalization"?!! Go lookup normalization in a
good book is my advice. This is a design problem of your own making I'm
afraid.
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
--|||Thank you for your reply. In an effort to make things simpler by removing
detail I probably confused things.
The OldTable had columns that looked like:
Id, A, B, C, D
A, B, C, D are types of attributes for the node of Id. We are adding new
types and in the future even more will be added. So rather than keep adding
columns to the OldTable a new table was thought up like:
Id, AttributeId
The AttributeId column is an index to the attributes of Id. It indexes
another new table where the attributes are stored called Attributes. It has
columns like:
AttributeId, Type, Value
So a human will be able to tell what 'Type' is there is a third table
AttributeTypes that has columns like:
Type, Description
So the OldTable would have a row like:
1000, 'A Attribute', 'B Attribute', 'C Attribute', 'D Attribute'
This would translate to the NewTable having content like:
1000, 10
1000, 20
1000, 30
1000, 40
The Attributes table would have content like:
10, 1, 'A Attribute'
20, 2, 'B Attribute'
30, 3, 'C Attribute'
40, 4, 'D Attribute'
The contents of the "lookup" table (AttributeTypes) would look like:
1, 'A'
2, 'B'
3, 'C'
4, 'D'
So to make the query return the same result set as before I am trying:
SELECT Id,
CASE Attributes,Type
WHEN 1 THEN Attributes.Value
ELSE NULL
END AS A,
CASE Attributes,Type
WHEN 2 THEN Attributes.Value
ELSE NULL
END AS B,
CASE Attributes,Type
WHEN 3 THEN Attributes.Value
ELSE NULL
END AS C,
CASE Attributes,Type
WHEN 4 THEN Attributes.Value
ELSE NULL
END AS D
FROM NewTable
INNER JOIN Attributes ON Attributes.AttributeId = NewTable.AttributeId
ORDER BY Id
The problem is that this returns
1000, 'A Attribute', NULL, NULL, NULL
1000, NULL, 'B Attribute', NULL, NULL
1000, NULL, NULL, 'C Attribute',NULL
1000, NULL, NULL, NULL, 'D Attribute'
So I either need to merge the above (preferred) so that it returns just one
row as before:
1000, 'A Attribute', 'B Attribute', 'C Attribute', 'D Attribute'
Or I need to be prepared to add a column any time a new attribute is
speicfied.
Suggestions?
Thank you.
Kevin
"Rakesh Ajwani" wrote:
[vbcol=seagreen]
> Kevin
> I need a bit more clarification, if you don't mind
> You used to have One table called "Big Daddy" now there are 3 tables I
> presume.
> Table, Attributes and Attributetype
> if that assumption is correct, let us know how the columns(old table) have
> been distributed in the normalized structure(have the static columns A and
B
> been converted to AttributeType) , there is probably a way to do it withou
t
> the cursor, but right now it is very hard to understand your problem.
> Also can you give me the query again that you are trying to run from the O
LD
> table.
> In the query you have specified , it's confusing when you are say the
> Attribute has AttributeTypeId but you join it to Table using AttributesId
> column, so seems like there is a 3rd table called AttributesType.
> it might be just simpler to post the DDL , you don't mind doing it.
> --
> Rakesh Ajwani
> MCSD, MCSD.NET
>
> "Kevin Burton" wrote:
>

Changing the columns of a SELECT statement.

I have been "noramlizing" some of the tables that we have been using and I
ran into a problem in one of our stored procedures.
It used to be (with the old table) that you could do something like:
SELECT A, B FROM OldTable
The stored procedure essentially does this.
Now with the new table(s) it is something like:
SELECT *
FROM Table
LEFT OUTER JOIN Attributes ON Attributes.AttributesId = Table.AttributesId
There is in the Attributes table an AttibutesTypeId and an AttributesValue.
So I get 'A' only when AttributesTypeId = 1 and 'B' when AttributesTypeId = 2. The question is how do I return A and B from the stored procedure given
this new table structure? I am tempted to create a temporary table and use a
cursor to move row by row through the table, but there must be a more
efficient way.
Thank you.Kevin
I need a bit more clarification, if you don't mind
You used to have One table called "Big Daddy" now there are 3 tables I
presume.
Table, Attributes and Attributetype
if that assumption is correct, let us know how the columns(old table) have
been distributed in the normalized structure(have the static columns A and B
been converted to AttributeType) , there is probably a way to do it without
the cursor, but right now it is very hard to understand your problem.
Also can you give me the query again that you are trying to run from the OLD
table.
In the query you have specified , it's confusing when you are say the
Attribute has AttributeTypeId but you join it to Table using AttributesId
column, so seems like there is a 3rd table called AttributesType.
it might be just simpler to post the DDL , you don't mind doing it.
--
Rakesh Ajwani
MCSD, MCSD.NET
"Kevin Burton" wrote:
> I have been "noramlizing" some of the tables that we have been using and I
> ran into a problem in one of our stored procedures.
> It used to be (with the old table) that you could do something like:
> SELECT A, B FROM OldTable
> The stored procedure essentially does this.
> Now with the new table(s) it is something like:
> SELECT *
> FROM Table
> LEFT OUTER JOIN Attributes ON Attributes.AttributesId = Table.AttributesId
> There is in the Attributes table an AttibutesTypeId and an AttributesValue.
> So I get 'A' only when AttributesTypeId = 1 and 'B' when AttributesTypeId => 2. The question is how do I return A and B from the stored procedure given
> this new table structure? I am tempted to create a temporary table and use a
> cursor to move row by row through the table, but there must be a more
> efficient way.
> Thank you.
>|||Find the moron that did this to you and kill him. This is called a EAV
design and it is totally wrong in an RDBMS. It confuses data and meta
data and makes your most basic queries run several orders of magnitude
slower. But the lack of speed does not matter; you will have no data
integrity so you cannot trust the answers anyway.
You might also want to get any book on data modeling. Then you will
know that there cannot be such a creature is a "type_id" -- the data
element is either a type with a known code or it is an identifier of
some kind of entity.
I am on a borrowed computer in Brazil, so I cannot cut & paste the
painful details; you can Google old postings on EAV, OTLT and MUCK as
bad design decisions frequently made by non-SQL programmers.|||Kevin Burton wrote:
> I have been "noramlizing" some of the tables that we have been using and I
> ran into a problem in one of our stored procedures.
> It used to be (with the old table) that you could do something like:
> SELECT A, B FROM OldTable
> The stored procedure essentially does this.
> Now with the new table(s) it is something like:
> SELECT *
> FROM Table
> LEFT OUTER JOIN Attributes ON Attributes.AttributesId = Table.AttributesId
> There is in the Attributes table an AttibutesTypeId and an AttributesValue.
> So I get 'A' only when AttributesTypeId = 1 and 'B' when AttributesTypeId => 2. The question is how do I return A and B from the stored procedure given
> this new table structure? I am tempted to create a temporary table and use a
> cursor to move row by row through the table, but there must be a more
> efficient way.
> Thank you.
That is what you call "normalization"?!! Go lookup normalization in a
good book is my advice. This is a design problem of your own making I'm
afraid.
--
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
--|||Thank you for your reply. In an effort to make things simpler by removing
detail I probably confused things.
The OldTable had columns that looked like:
Id, A, B, C, D
A, B, C, D are types of attributes for the node of Id. We are adding new
types and in the future even more will be added. So rather than keep adding
columns to the OldTable a new table was thought up like:
Id, AttributeId
The AttributeId column is an index to the attributes of Id. It indexes
another new table where the attributes are stored called Attributes. It has
columns like:
AttributeId, Type, Value
So a human will be able to tell what 'Type' is there is a third table
AttributeTypes that has columns like:
Type, Description
So the OldTable would have a row like:
1000, 'A Attribute', 'B Attribute', 'C Attribute', 'D Attribute'
This would translate to the NewTable having content like:
1000, 10
1000, 20
1000, 30
1000, 40
The Attributes table would have content like:
10, 1, 'A Attribute'
20, 2, 'B Attribute'
30, 3, 'C Attribute'
40, 4, 'D Attribute'
The contents of the "lookup" table (AttributeTypes) would look like:
1, 'A'
2, 'B'
3, 'C'
4, 'D'
So to make the query return the same result set as before I am trying:
SELECT Id,
CASE Attributes,Type
WHEN 1 THEN Attributes.Value
ELSE NULL
END AS A,
CASE Attributes,Type
WHEN 2 THEN Attributes.Value
ELSE NULL
END AS B,
CASE Attributes,Type
WHEN 3 THEN Attributes.Value
ELSE NULL
END AS C,
CASE Attributes,Type
WHEN 4 THEN Attributes.Value
ELSE NULL
END AS D
FROM NewTable
INNER JOIN Attributes ON Attributes.AttributeId = NewTable.AttributeId
ORDER BY Id
The problem is that this returns
1000, 'A Attribute', NULL, NULL, NULL
1000, NULL, 'B Attribute', NULL, NULL
1000, NULL, NULL, 'C Attribute',NULL
1000, NULL, NULL, NULL, 'D Attribute'
So I either need to merge the above (preferred) so that it returns just one
row as before:
1000, 'A Attribute', 'B Attribute', 'C Attribute', 'D Attribute'
Or I need to be prepared to add a column any time a new attribute is
speicfied.
Suggestions?
Thank you.
Kevin
"Rakesh Ajwani" wrote:
> Kevin
> I need a bit more clarification, if you don't mind
> You used to have One table called "Big Daddy" now there are 3 tables I
> presume.
> Table, Attributes and Attributetype
> if that assumption is correct, let us know how the columns(old table) have
> been distributed in the normalized structure(have the static columns A and B
> been converted to AttributeType) , there is probably a way to do it without
> the cursor, but right now it is very hard to understand your problem.
> Also can you give me the query again that you are trying to run from the OLD
> table.
> In the query you have specified , it's confusing when you are say the
> Attribute has AttributeTypeId but you join it to Table using AttributesId
> column, so seems like there is a 3rd table called AttributesType.
> it might be just simpler to post the DDL , you don't mind doing it.
> --
> Rakesh Ajwani
> MCSD, MCSD.NET
>
> "Kevin Burton" wrote:
> > I have been "noramlizing" some of the tables that we have been using and I
> > ran into a problem in one of our stored procedures.
> >
> > It used to be (with the old table) that you could do something like:
> >
> > SELECT A, B FROM OldTable
> >
> > The stored procedure essentially does this.
> >
> > Now with the new table(s) it is something like:
> >
> > SELECT *
> > FROM Table
> > LEFT OUTER JOIN Attributes ON Attributes.AttributesId = Table.AttributesId
> >
> > There is in the Attributes table an AttibutesTypeId and an AttributesValue.
> > So I get 'A' only when AttributesTypeId = 1 and 'B' when AttributesTypeId => > 2. The question is how do I return A and B from the stored procedure given
> > this new table structure? I am tempted to create a temporary table and use a
> > cursor to move row by row through the table, but there must be a more
> > efficient way.
> >
> > Thank you.
> >

Changing table properties

I have 7 tables that I want to remove the checkbox for "Enforce relationship
for replication". These tables are all articles in a publication and I want
them to get down to the merge synchronization laptop subscribers. On a test
system, I unchecked them in EM and the changes took without any errors
(actually I expected to be told I cannot do that on published articles). I
assume that I can just re-create the snapshot and have the laptops
"reinitialize" at their next synch. Am I correct or missing anything.
Thanks.
David
David,
this is fine, although you might be able to just apply a script on the
subscriber to drop the FKs directly (or through sp_addscriptexec) without
doing the reinitialization.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||I tried this in a test environment but when I looked at the database on the
laptop it still had the box checked. Did I miss something?
David
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:etWoZ9JQHHA.1380@.TK2MSFTNGP05.phx.gbl...
> David,
> this is fine, although you might be able to just apply a script on the
> subscriber to drop the FKs directly (or through sp_addscriptexec) without
> doing the reinitialization.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
>
|||David - can you post up the script you were using and I'll take a look.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Paul,
I was trying to do it in Enterprise Manager instead of script. I then went
into the Snapshot Agent and restarted the snapshot agent, which I thought
would pick up the new settings. I then re-initialized the subscription at
the laptop but it didn't pick up the removal of "Enforce relationship for
replication".
Do I need to completely re-create the publication? If I can do it in a
script then what script do I use to run on the laptops and where can I find
the syntax for changing this relationship property? Thank you.
David
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:OZyzv$SQHHA.1200@.TK2MSFTNGP02.phx.gbl...
> David - can you post up the script you were using and I'll take a look.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
>
>
|||Also, do I need to run sp_addscriptexec at both the publisher and
subscriber? I read the BOL and they said you have to be sysadmin to run it
but the laptops are not. Thanks.
David
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:OZyzv$SQHHA.1200@.TK2MSFTNGP02.phx.gbl...
> David - can you post up the script you were using and I'll take a look.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
>
>
|||Hi David - have answered your other thread below.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||David,
the script is in the form:
ALTER TABLE dbo.yourFKtable
DROP CONSTRAINT yourFKtable1
GO
ALTER TABLE dbo.yourFKtable WITH NOCHECK ADD CONSTRAINT
yourFKtable1 FOREIGN KEY
(
PKColumn
) REFERENCES dbo.YourPKTable
(
pkcolumn
) NOT FOR REPLICATION
GO
You'll need to run this at the publisher to change the publisher tables then
add it to the publication using sp_addscriptexec.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Thanks Paul. Will I have to run sp_addscriptexec at the subscribers also or
will that happen when they synch?
David
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:ufa1ml4QHHA.4632@.TK2MSFTNGP04.phx.gbl...
> David,
> the script is in the form:
> ALTER TABLE dbo.yourFKtable
> DROP CONSTRAINT yourFKtable1
> GO
> ALTER TABLE dbo.yourFKtable WITH NOCHECK ADD CONSTRAINT
> yourFKtable1 FOREIGN KEY
> (
> PKColumn
> ) REFERENCES dbo.YourPKTable
> (
> pkcolumn
> ) NOT FOR REPLICATION
> GO
> You'll need to run this at the publisher to change the publisher tables
> then add it to the publication using sp_addscriptexec.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
>
|||David - the merge agent will take care of applying it to the subscribers.
Cheers,
Paul Ibison

Changing table properties

Hi,
I have got approximately 100 tables. Their all attributes are same. But, I w
ant to change their one attribute.
Such as my table;
NAME nvarchar(50)
SNAME nvarchar(50)
SALARY float
I want to change SALARY's property from float to money. It is possible to th
is operation manually. But I need to this operation automaticallay via progr
amming language or via query analizer.
How can I do?Why would you want 100 identical tables? How about combining them into one.
If you have no choice but to support the current design then use a query out
of INFORMATION_SCHEMA.TABLES to generate the script.
SELECT 'ALTER TABLE '+
QUOTENAME(table_schema)+'.'+
QUOTENAME(table_name)+' ALTER COLUMN salary DECIMAL(10,2)'
FROM information_schema.tables
WHERE table_name LIKE ?
Now just cut and paste the results into Query Analyzer.
I recommend you avoid using MONEY for the salary column because MONEY has
some problems with precision in arithmetic operations. Use DECIMAL/NUMERIC
instead.
David Portas
SQL Server MVP
--|||Hi
I would do it slightly differently!!!
SELECT 'ALTER TABLE '+
QUOTENAME(table_schema)+'.'+
QUOTENAME(table_name)+' ALTER COLUMN ' + QUOTENAME(COLUMN_NAME) + '
DECIMAL(10,2)'
FROM information_schema.columns
WHERE COLUMN_NAME = 'Salary'
John
"David Portas" wrote:

> Why would you want 100 identical tables? How about combining them into one
.
> If you have no choice but to support the current design then use a query o
ut
> of INFORMATION_SCHEMA.TABLES to generate the script.
> SELECT 'ALTER TABLE '+
> QUOTENAME(table_schema)+'.'+
> QUOTENAME(table_name)+' ALTER COLUMN salary DECIMAL(10,2)'
> FROM information_schema.tables
> WHERE table_name LIKE ?
> Now just cut and paste the results into Query Analyzer.
> I recommend you avoid using MONEY for the salary column because MONEY has
> some problems with precision in arithmetic operations. Use DECIMAL/NUMERIC
> instead.
> --
> David Portas
> SQL Server MVP
> --
>
>

Changing table properties

Hi,
I have got approximately 100 tables. Their all attributes are same. But, I w
ant to change their one attribute.
Such as my table;
NAME nvarchar(50)
SNAME nvarchar(50)
SALARY float
I want to change SALARY's property from float to money. It is possible to th
is operation manually. But I need to this operation automaticallay via progr
amming language or via query analizer.
How can I do?See http://tinyurl.com/a3f26
"huseyin_akturk" wrote:

> Hi,
> I have got approximately 100 tables. Their all attributes are same.
> But, I want to change their one attribute.
> Such as my table;
> NAME nvarchar(50)
> SNAME nvarchar(50)
> SALARY float
> I want to change SALARY's property from float to money. It is possible
> to this operation manually. But I need to this operation automaticallay
> via programming language or via query analizer.
> How can I do?
>
> --
> huseyin_akturk
> ---
> Posted via http://www.codecomments.com
> ---
>

Changing table owner SQL database

i'm looking for a script to change the ownership of some tables in SQL
database from a certain user to dbo
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!Hi.
You can utilize something as this:
sp_changeobjectowner [ @.objname = ] 'object' , [ @.newowner = ] 'owner'
This it is a stored procedure of SQL Server and only is able used by the use
rs in the roles SysAdmin and db_owner.
Hermilson Tinoco.
****************************************
*************
i'm looking for a script to change the ownership of some tables in SQL
database from a certain user to dbo|||Thanks for your reply.
Via other sources I've got a mail with the following script which
creates a stored procedure that you can execute to change the ownership
of several tables in one run:
CREATE PROC dbo.up_FixObjOwners
AS
SET NOCOUNT ON
DECLARE @.dynsql varchar(1000)
SET @.dynsql = ''
DECLARE @.Obj_Owner sysname
SET @.Obj_Owner = ''
DECLARE @.Obj_Type VARCHAR(30)
SET @.Obj_Type = ''
DECLARE @.Obj_Name sysname
SET @.Obj_Name = ''
DECLARE @.ObjCounter INT
SET @.ObjCounter = 0
DECLARE @.DBO CHAR(3)
SET @.DBO = 'DBO'
-- temp table to hold all objects not owned
-- by DBO
create table #ChangeOwners(
id int identity(1,1),
Obj_Owner sysname,
Obj_Name sysname,
Obj_Type varchar(30))
-- populate it
INSERT #ChangeOwners (Obj_Owner, Obj_Name, Obj_Type)
select
su.name,
so.name,
case
when type = 'u' then 'table'
when type = 'p' then 'sproc'
when type = 'v' then 'view'
end as obj_type
from sysusers su
join sysobjects so
on su.uid = so.uid
where su.name not in ('information_schema', 'dbo')
and so.type in ('p', 'u', 'v')
-- select * from #ChangeOwners
SET @.ObjCounter = @.@.rowcount -- holds the count of rows inserted into
#ChangeOwners
WHILE @.Objcounter > 0
BEGIN
-- construct string for object ownership change
SELECT @.Obj_Name = Obj_Owner + '.' + Obj_Name FROM #ChangeOwners WHERE
id = @.ObjCounter
SELECT @.Obj_Type = Obj_Type FROM #ChangeOwners WHERE id = @.ObjCounter
SET @.dynsql = 'sp_ChangeObjectOwner ''' + @.Obj_Name + ''', ' + @.DBO
--select @.dynsql
print 'changing ownership on ' + @.Obj_Type + ': ' + @.Obj_Name
EXEC(@.dynsql)
SET @.ObjCounter = @.ObjCounter - 1
END
-- ok all done, collect garbage
drop table #ChangeOwners
I hope u can use some other time too.
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!|||Note that changing the object owner (or renaming the proc) will not change
the underlying source text of the procedure. This can cause problems with
subsequent DDL scripting if the original owner was explicitly specified on
the CREATE statement.
Hope this helps.
Dan Guzman
SQL Server MVP
"stevesilent" <stevesilent@.devdex.com> wrote in message
news:OV1wf8J8DHA.2412@.TK2MSFTNGP09.phx.gbl...
> Thanks for your reply.
> Via other sources I've got a mail with the following script which
> creates a stored procedure that you can execute to change the ownership
> of several tables in one run:
> CREATE PROC dbo.up_FixObjOwners
> AS
> SET NOCOUNT ON
> DECLARE @.dynsql varchar(1000)
> SET @.dynsql = ''
> DECLARE @.Obj_Owner sysname
> SET @.Obj_Owner = ''
> DECLARE @.Obj_Type VARCHAR(30)
> SET @.Obj_Type = ''
> DECLARE @.Obj_Name sysname
> SET @.Obj_Name = ''
> DECLARE @.ObjCounter INT
> SET @.ObjCounter = 0
> DECLARE @.DBO CHAR(3)
> SET @.DBO = 'DBO'
> -- temp table to hold all objects not owned
> -- by DBO
> create table #ChangeOwners(
> id int identity(1,1),
> Obj_Owner sysname,
> Obj_Name sysname,
> Obj_Type varchar(30))
> -- populate it
> INSERT #ChangeOwners (Obj_Owner, Obj_Name, Obj_Type)
> select
> su.name,
> so.name,
> case
> when type = 'u' then 'table'
> when type = 'p' then 'sproc'
> when type = 'v' then 'view'
> end as obj_type
> from sysusers su
> join sysobjects so
> on su.uid = so.uid
> where su.name not in ('information_schema', 'dbo')
> and so.type in ('p', 'u', 'v')
> -- select * from #ChangeOwners
> SET @.ObjCounter = @.@.rowcount -- holds the count of rows inserted into
> #ChangeOwners
> WHILE @.Objcounter > 0
> BEGIN
> -- construct string for object ownership change
> SELECT @.Obj_Name = Obj_Owner + '.' + Obj_Name FROM #ChangeOwners WHERE
> id = @.ObjCounter
> SELECT @.Obj_Type = Obj_Type FROM #ChangeOwners WHERE id = @.ObjCounter
> SET @.dynsql = 'sp_ChangeObjectOwner ''' + @.Obj_Name + ''', ' + @.DBO
> --select @.dynsql
> print 'changing ownership on ' + @.Obj_Type + ': ' + @.Obj_Name
> EXEC(@.dynsql)
> SET @.ObjCounter = @.ObjCounter - 1
> END
> -- ok all done, collect garbage
> drop table #ChangeOwners
>
> I hope u can use some other time too.
>
> *** Sent via Developersdex http://www.examnotes.net ***
> Don't just participate in USENET...get rewarded for it!

Changing Table Name to Write data dynamically.

Hello there, folks.
I have 5 tables with exactly same column types, names, and constraints
but with differnt names.(e.g. table1, table2, ..., table5)
I have a stored procedure(sp_example) that writes some calculated data to
"table1".
but I would like to modify the stored procedure to write the similiar data
(but from different sources) to other tables(table2,... table5)
depending on value passed to the stored procedure
(e.g, exec sp_example '2' will write to table2, exec sp_example '3' will
write to table3 and so on)...
I have tried to use a pattern like
==============================
create procedure sp_example @.param
as
declare @.cmd as Varchar(8000)
set @.cmd = @.cmd + 'INSERT INTO ' + getTableName(@.param)
set @.cmd = @.cmd + ' SELECT * From sometable '
Exec(@.cmd)
==============================
But the problem is that stored procedure i am working with is roughly 1000
lines
and having to write "set @.cmd = @.cmd + '...'" seems like an overkill.
So is there any other way to change the "table" name only dynamically?
Thank you in advance.> I have 5 tables with exactly same column types, names, and
constraints
> but with differnt names.(e.g. table1, table2, ..., table5)
Why? Sounds like a design flaw. Use one table and add an extra column
for whatever attribute is represented by the different table names. You
can still create views with the original table names so you shouldn't
even need to change your code.
Failing that, you could consider using a partitioned view. See Books
Online for details. Good design is the right solution rather than messy
Dynamic SQL.
David Portas
SQL Server MVP
--|||> Why? Sounds like a design flaw. Use one table and add an extra column
Yeah, it seems like so but as a programmer, i have to deal with badly
designed table structures...

> Failing that, you could consider using a partitioned view. See Books
> Online for details. Good design is the right solution rather than messy
I don't think partitioned views can be applied to the problem i have after
going through the online book...

Changing table colon's properties

Hi,
I have got approximately 100 tables. Their all attributes are same. But, I
want to change their one attribute.
Such as my table;
NAME nvarchar(50)
SNAME nvarchar(50)
SALARY float
I want to change SALARY's property from float to money. It is possible to th
is operation manually. But I need to this operation automaticallay via
programming language or via query analizer.
How can I do?huseyin_akturk wrote:
> Hi,
> I have got approximately 100 tables. Their all attributes are same.
> But, I want to change their one attribute.
> Such as my table;
> NAME nvarchar(50)
> SNAME nvarchar(50)
> SALARY float
> I want to change SALARY's property from float to money. It is
> possible to th is operation manually. But I need to this operation
> automaticallay via programming language or via query analizer.
> How can I do?
You can add a new column Salary2 Money, Update the table and move all
the float values into the new money type (cast the value and round as
needed), drop the old column, and then rename the new column. Test in
dev before applying to production. You may also want to consider using a
DECIMAL(X) or INT for the salary, especially if you want a fixed number
of decimal places (say 2) or do not require decimal places at all.
The following example moves the salary values directly from one column
to the other.
Create Table dbo.SalaryTestXXX (
MyID INT IDENTITY NOT NULL PRIMARY KEY,
Salary FLOAT NULL )
go
Insert Into dbo.SalaryTestXXX Values (25000)
Insert Into dbo.SalaryTestXXX Values (50000)
Insert Into dbo.SalaryTestXXX Values (75000)
Insert Into dbo.SalaryTestXXX Values (100000)
Select * from dbo.SalaryTestXXX
go
Alter Table dbo.SalaryTestXXX
Add Salary2 Money
go
Select * from dbo.SalaryTestXXX
Update dbo.SalaryTestXXX
Set Salary2 = Salary
Where Salary IS NOT NULL
go
Select * from dbo.SalaryTestXXX
go
Alter Table dbo.SalaryTestXXX
Drop Column Salary
go
Select * from dbo.SalaryTestXXX
Exec sp_rename 'dbo.SalaryTestXXX.Salary2', 'Salary', 'COLUMN'
Select * from dbo.SalaryTestXXX
Drop Table dbo.SalaryTestXXX
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Are you sure you want to change it to MONEY? MONEY data type might not be
exactly what you think it is. You might want to use NUMERIC(18, 2) or
NUMERIC(18, 4) or some other variation of NUMERIC instead of MONEY... MONEY
stores currency display information in the same column as the value, which
can cause headaches.
"huseyin_akturk" <huseyinakturk@.discussions.microsoft.com> wrote in message
news:7C2B0E2B-FEA8-4BA2-8777-CC1D0DED37E3@.microsoft.com...
> Hi,
> I have got approximately 100 tables. Their all attributes are same. But, I
> want to change their one attribute.
> Such as my table;
> NAME nvarchar(50)
> SNAME nvarchar(50)
> SALARY float
> I want to change SALARY's property from float to money. It is possible to
> th
> is operation manually. But I need to this operation automaticallay via
> programming language or via query analizer.
> How can I do?
>|||See if this helps:
use tempdb
go
create table t1 (
salary float
)
go
insert into t1 values(1245.07)
go
select * from t1
go
declare @.sql nvarchar(4000)
declare @.ts sysname
declare @.tn sysname
declare @.cn sysname
declare columns_cursor cursor local fast_forward
for
select
table_schema,
table_name
from
information_schema.columns
where
column_name = 'salary'
and data_type = 'float'
and objectproperty(object_id(quotename(table
_schema) + '.' +
quotename(table_name)), 'IsMSShipped') = 0
open columns_cursor
while 1 = 1
begin
fetch next from columns_cursor into @.ts, @.tn
if @.@.error != 0 or @.@.fetch_status != 0 break
set @.sql = N'alter table ' + quotename(@.ts) + '.' + quotename(@.tn) + N'
alter column salary money'
print @.sql
exec sp_executesql @.sql
end
close columns_cursor
deallocate columns_cursor
go
select * from t1
go
drop table t1
go
AMB
"huseyin_akturk" wrote:

> Hi,
> I have got approximately 100 tables. Their all attributes are same. But, I
> want to change their one attribute.
> Such as my table;
> NAME nvarchar(50)
> SNAME nvarchar(50)
> SALARY float
> I want to change SALARY's property from float to money. It is possible to
th
> is operation manually. But I need to this operation automaticallay via
> programming language or via query analizer.
> How can I do?
>

changing records in tables

Hi,

I was just wondering... Is there any built-in function in MS SQL, which will
allow me to do rows' "capitalisation"?
Lets say that in database.table.name I've got:
"FOO BAR LTD.", which I want to change to "Foo Bar Ltd."
"Foo bar LTD.", which I want to be "Foo Bar Ltd.".
Is there any way of doing this or do I have to read it from database, change
in some script, and then insert it back into the table?

Hope it's all clear ;-)

Thank you,
MartinMartin Lukasik (marcin@.milea.pl.i.hate.this.spam) writes:

Quote:

Originally Posted by

I was just wondering... Is there any built-in function in MS SQL, which
will allow me to do rows' "capitalisation"?
Lets say that in database.table.name I've got:
"FOO BAR LTD.", which I want to change to "Foo Bar Ltd."
"Foo bar LTD.", which I want to be "Foo Bar Ltd.".
Is there any way of doing this or do I have to read it from database,
change in some script, and then insert it back into the table?

No, there is no built in for this. You could write your own scalar
UDF for this.

--
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|||Hi There,

Search for "Camel Case" in Google

With Warm Regards
Jatinder Singh
http://jatindersingh.blogspot.com

Changing ownership on all objects in a DB

Hi,
I have to change ownership of all the tables/storedprocedures/views in a
database.
Can some tell me a good and easy way to do it.
Thnx in advance.I don't know of anything easy.
Take a look at:
select * from information_schema.tables (tables and views)
select * from information_schema.routines (stored procedures)
You could,
open a cursor,
populate it with the names of the tables/views/stored procedures
loop through the cursor
use dynamic SQL to change ownership
Ugly...
Don't forget that you can 2 different objects with the same name and
different owners. This procedure will crash in this case.
Also, if you reference other objects from inside views and stored procedures
using the object owner, this does nothing to solve it.
One easier method to change view/stored procedure owners is to script out
all of them into a text file, do a search and replace on the owner and run
the script in Query Analyser. You could use this method to solde the
"referenced object owner" problem too. If you don't mind loosing all data in
your tables, you could use this method on tables.
For the tables, take a look at the "undocumented" stored procedure
sp_MSforeachtable. Google it for more details.
"dotnettester" <dotnettester@.discussions.microsoft.com> wrote in message
news:8C1ECABA-5302-4D34-93BF-015AF2A6916F@.microsoft.com...
> Hi,
> I have to change ownership of all the tables/storedprocedures/views in a
> database.
> Can some tell me a good and easy way to do it.
> Thnx in advance.|||You might want to use the script given here >>
http://weblogs.asp.net/owscott/arch...1/30/65229.aspx
Hope this helps!
Best Regards
Vadivel
http://vadivel.blogspot.com
"dotnettester" wrote:

> Hi,
> I have to change ownership of all the tables/storedprocedures/views in a
> database.
> Can some tell me a good and easy way to do it.
> Thnx in advance.|||Well, you could start with this script, in Query Analyzer:
DECLARE @.newOwner SYSNAME;
SET @.newOwner = 'new_owner_name'; -- I assume you are changing to dbo?
IF USER_ID(@.newOwner) IS NOT NULL
BEGIN
SELECT 'EXEC sp_changeobjectowner
'''+QUOTENAME(name)+''','''+@.newOwner+''
''
FROM sysobjects
WHERE xtype IN ('P','U','IF','FN','TR','V')
AND uid != USER_ID(@.newOwner);
END
ELSE
RAISERROR('User %s does not exist.', 11, 1, @.newOwner);
This will yield a script in the bottom pane that you can copy and paste to
the top pane, syntax check, prune, and execute.
"dotnettester" <dotnettester@.discussions.microsoft.com> wrote in message
news:8C1ECABA-5302-4D34-93BF-015AF2A6916F@.microsoft.com...
> Hi,
> I have to change ownership of all the tables/storedprocedures/views in a
> database.
> Can some tell me a good and easy way to do it.
> Thnx in advance.

Changing owner on a SQLServer DB ?

We have received a DB from one of our customer.
We want to change the DB and tables (and all objects) owner to sa
How can we do that ?
ThanksYou could use the procedures 'sp_changedbowner' and 'sp_changeobjectowner' to change the owner for the database and objects (tables, views, etc) resp.|||can I do that in 1 commands for all tables (I have more 100 tables !!!) ?|||As far as I know there is no standard procedure that changes the owner of all objects to another.|||Databases are owned by a SQL Server login. Objects within the database are owned by a database user. I strongly suspect that when you change the database owner login to sa, all of the objects are probably owned by dbo so they will appear as though they are owned by the sa login.

-PatP

changing owner of the subscriber

Hello there
Afrer i replicate my database, the tables that i replicated have been
created with the owner of the user i've accessed to the subscriber database,
instead of updating the data on my original tables that has dbo owner.
Is there a way to use replication to update the dbo tables?
' 03-5611606
' 050-7709399
: roy@.atidsm.co.il
Right click on your publication, select properties, click on the articles
tab. Click on the browse button to the right of each of your tables, and in
the general tab, enter dbo for destination table owner.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Roy Goldhammer" <roy@.hotmail.com> wrote in message
news:ezEA$uCHGHA.2460@.TK2MSFTNGP10.phx.gbl...
> Hello there
> Afrer i replicate my database, the tables that i replicated have been
> created with the owner of the user i've accessed to the subscriber
> database, instead of updating the data on my original tables that has dbo
> owner.
> Is there a way to use replication to update the dbo tables?
> --
>
> ' 03-5611606
> ' 050-7709399
> : roy@.atidsm.co.il
>

Changing outer joins to inner join

Hi,
I have a main table that references many tables (something like Orders
table, referencing Customers,Employees...).
As an example, there are many orders that have Null value for their
CustomerID therefore I need an Outer Join from Orders table to retrieve all
rows.
Now a View that joins Customers to Orders cannot be indexed because of the
Outer Join.
I was thinking of adding a row to Customers with value of (#) for
CompanyName column and assigning its ID to all Orphan rows in Orders.
I must:
- Write trigger on Orders table to insert ID of # record for new orphan
orders instead of Null.
- Filter # record when I need a real Inner Join.
- and some other considerations...
Instead an Inner Join between Customers and Orders can return all orders and
this query can be used in indexed view.
The performance of queries from main(Orders) table is the most critical
issue. Is it a good decision for changing such queries to Inner Join?
I appreciated any suggestion.
LeilaDo you really have to use an index view ? I just wanna keep track that even
it is an outer join the indexes will be used if possible and if existing. I
wouldt got the way you are describing although this situation is uncommon
to me, to enter an order where no customer is assigned to it. (?) Ok, but i
wouldt worsen up the situation by building a trigger and doing this kind of
workaround. I would build my best on the possibiliies i have, perhaps there
is a way to tune up our queries ?!
http://www.sql-server-performance.com/tuning_joins.asp
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Leila" <leilas@.hotpop.com> schrieb im Newsbeitrag
news:%23s11KGCSFHA.1348@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I have a main table that references many tables (something like Orders
> table, referencing Customers,Employees...).
> As an example, there are many orders that have Null value for their
> CustomerID therefore I need an Outer Join from Orders table to retrieve
> all
> rows.
> Now a View that joins Customers to Orders cannot be indexed because of the
> Outer Join.
> I was thinking of adding a row to Customers with value of (#) for
> CompanyName column and assigning its ID to all Orphan rows in Orders.
> I must:
> - Write trigger on Orders table to insert ID of # record for new orphan
> orders instead of Null.
> - Filter # record when I need a real Inner Join.
> - and some other considerations...
> Instead an Inner Join between Customers and Orders can return all orders
> and
> this query can be used in indexed view.
> The performance of queries from main(Orders) table is the most critical
> issue. Is it a good decision for changing such queries to Inner Join?
> I appreciated any suggestion.
> Leila
>|||Hi
You can change your outer joins to a union of a query that uses an inner
join plus a join where the where clause eliminates those returned by the
inner join.
If you want to use a default for your order id, make the column not nullable
with the default.
I would have to ask why you have orders without customers?
John
"Leila" <leilas@.hotpop.com> wrote in message
news:%23s11KGCSFHA.1348@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I have a main table that references many tables (something like Orders
> table, referencing Customers,Employees...).
> As an example, there are many orders that have Null value for their
> CustomerID therefore I need an Outer Join from Orders table to retrieve
> all
> rows.
> Now a View that joins Customers to Orders cannot be indexed because of the
> Outer Join.
> I was thinking of adding a row to Customers with value of (#) for
> CompanyName column and assigning its ID to all Orphan rows in Orders.
> I must:
> - Write trigger on Orders table to insert ID of # record for new orphan
> orders instead of Null.
> - Filter # record when I need a real Inner Join.
> - and some other considerations...
> Instead an Inner Join between Customers and Orders can return all orders
> and
> this query can be used in indexed view.
> The performance of queries from main(Orders) table is the most critical
> issue. Is it a good decision for changing such queries to Inner Join?
> I appreciated any suggestion.
> Leila
>|||Thanks Jens,

>I just wanna keep track that even
> it is an outer join the indexes will be used if possible and if existing
- I do agree, but indexes and tuning the queries can help to limited extent.
If they could be ultimate solution for all situations, Indexed Views
wouldn't be invented! What if your query needs a covering index with more
that 16 columns? What if the index length exceeds 900 bytes? You will not be
able to create useful indexes.
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:#0Fi1lCSFHA.3716@.TK2MSFTNGP14.phx.gbl...
> Do you really have to use an index view ? I just wanna keep track that
even
> it is an outer join the indexes will be used if possible and if existing.
I
> wouldt got the way you are describing although this situation is uncommon
> to me, to enter an order where no customer is assigned to it. (?) Ok, but
i
> wouldt worsen up the situation by building a trigger and doing this kind
of
> workaround. I would build my best on the possibiliies i have, perhaps
there
> is a way to tune up our queries ?!
> http://www.sql-server-performance.com/tuning_joins.asp
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>
> "Leila" <leilas@.hotpop.com> schrieb im Newsbeitrag
> news:%23s11KGCSFHA.1348@.TK2MSFTNGP15.phx.gbl...
the
>|||Thanks John,
Using union is great idea but in my query, the main table(orders) is joined
with other parent tables(like customers, employees..).
The same situation can exist: orders with EmployeeID=Null as well.
Please note that orders without customer is just an example, I skipped
describing the real situation (and why orphan rows exist) because tables in
northwind are famous and everybody is familiar with them.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:e3LJqvCSFHA.2932@.TK2MSFTNGP09.phx.gbl...
> Hi
> You can change your outer joins to a union of a query that uses an inner
> join plus a join where the where clause eliminates those returned by the
> inner join.
> If you want to use a default for your order id, make the column not
nullable
> with the default.
> I would have to ask why you have orders without customers?
> John
> "Leila" <leilas@.hotpop.com> wrote in message
> news:%23s11KGCSFHA.1348@.TK2MSFTNGP15.phx.gbl...
the
>|||"Leila" <leilas@.hotpop.com> wrote in message
news:edXR8yCSFHA.2788@.TK2MSFTNGP09.phx.gbl...
> - I do agree, but indexes and tuning the queries can help to limited
> extent.
> If they could be ultimate solution for all situations, Indexed Views
> wouldn't be invented! What if your query needs a covering index with more
> that 16 columns? What if the index length exceeds 900 bytes? You will not
> be
> able to create useful indexes.
Not familiar with your DDL, or exactly how you're relating these tables to
one another, but columns with a lot of NULL values don't tend to make very
good indexes either... Are you really relating these tables to one another
using a CustomerID field that can be NULL? How do you keep track of who to
ship the order to? Or, more importantly, where to send the bill? In a
situation like this, would it make sense to generate all your NULL
customerid orders in a separate 'exception'-type query instead of plopping
them down in the middle of orders that were actually placed by customers?
Just wondering...
Instead of going through all the trigger-writing, etc., why not just UPDATE
all current NULL customerid's with the default value you specified; then
define the column as NON-NULL with a Default?|||Thank Michael,
Please note that orders without customer is just an example, I skipped
describing the real situation (and why orphan rows exist) because tables in
northwind are famous and everybody is familiar with them.
"Michael C#" <xyz@.abcdef.com> wrote in message
news:K%Fae.3416$RP1.1452@.fe10.lga...
> "Leila" <leilas@.hotpop.com> wrote in message
> news:edXR8yCSFHA.2788@.TK2MSFTNGP09.phx.gbl...
more
not
> Not familiar with your DDL, or exactly how you're relating these tables to
> one another, but columns with a lot of NULL values don't tend to make very
> good indexes either... Are you really relating these tables to one
another
> using a CustomerID field that can be NULL? How do you keep track of who
to
> ship the order to? Or, more importantly, where to send the bill? In a
> situation like this, would it make sense to generate all your NULL
> customerid orders in a separate 'exception'-type query instead of plopping
> them down in the middle of orders that were actually placed by customers?
> Just wondering...
> Instead of going through all the trigger-writing, etc., why not just
UPDATE
> all current NULL customerid's with the default value you specified; then
> define the column as NON-NULL with a Default?
>
>

Changing outer joins to inner join

Hi,
I have a main table that references many tables (something like Orders
table, referencing Customers,Employees...).
As an example, there are many orders that have Null value for their
CustomerID therefore I need an Outer Join from Orders table to retrieve all
rows.
Now a View that joins Customers to Orders cannot be indexed because of the
Outer Join.
I was thinking of adding a row to Customers with value of (#) for
CompanyName column and assigning its ID to all Orphan rows in Orders.
I must:
- Write trigger on Orders table to insert ID of # record for new orphan
orders instead of Null.
- Filter # record when I need a real Inner Join.
- and some other considerations...
Instead an Inner Join between Customers and Orders can return all orders and
this query can be used in indexed view.
The performance of queries from main(Orders) table is the most critical
issue. Is it a good decision for changing such queries to Inner Join?
I appreciated any suggestion.
LeilaDo you really have to use an index view ? I just wanna keep track that even
it is an outer join the indexes will be used if possible and if existing. I
wouldt got the way you are describing although this situation is uncommon
to me, to enter an order where no customer is assigned to it. (?) Ok, but i
wouldt worsen up the situation by building a trigger and doing this kind of
workaround. I would build my best on the possibiliies i have, perhaps there
is a way to tune up our queries ?!
http://www.sql-server-performance.com/tuning_joins.asp
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Leila" <leilas@.hotpop.com> schrieb im Newsbeitrag
news:%23s11KGCSFHA.1348@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I have a main table that references many tables (something like Orders
> table, referencing Customers,Employees...).
> As an example, there are many orders that have Null value for their
> CustomerID therefore I need an Outer Join from Orders table to retrieve
> all
> rows.
> Now a View that joins Customers to Orders cannot be indexed because of the
> Outer Join.
> I was thinking of adding a row to Customers with value of (#) for
> CompanyName column and assigning its ID to all Orphan rows in Orders.
> I must:
> - Write trigger on Orders table to insert ID of # record for new orphan
> orders instead of Null.
> - Filter # record when I need a real Inner Join.
> - and some other considerations...
> Instead an Inner Join between Customers and Orders can return all orders
> and
> this query can be used in indexed view.
> The performance of queries from main(Orders) table is the most critical
> issue. Is it a good decision for changing such queries to Inner Join?
> I appreciated any suggestion.
> Leila
>|||Hi
You can change your outer joins to a union of a query that uses an inner
join plus a join where the where clause eliminates those returned by the
inner join.
If you want to use a default for your order id, make the column not nullable
with the default.
I would have to ask why you have orders without customers?
John
"Leila" <leilas@.hotpop.com> wrote in message
news:%23s11KGCSFHA.1348@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I have a main table that references many tables (something like Orders
> table, referencing Customers,Employees...).
> As an example, there are many orders that have Null value for their
> CustomerID therefore I need an Outer Join from Orders table to retrieve
> all
> rows.
> Now a View that joins Customers to Orders cannot be indexed because of the
> Outer Join.
> I was thinking of adding a row to Customers with value of (#) for
> CompanyName column and assigning its ID to all Orphan rows in Orders.
> I must:
> - Write trigger on Orders table to insert ID of # record for new orphan
> orders instead of Null.
> - Filter # record when I need a real Inner Join.
> - and some other considerations...
> Instead an Inner Join between Customers and Orders can return all orders
> and
> this query can be used in indexed view.
> The performance of queries from main(Orders) table is the most critical
> issue. Is it a good decision for changing such queries to Inner Join?
> I appreciated any suggestion.
> Leila
>|||Thanks Jens,

>I just wanna keep track that even
> it is an outer join the indexes will be used if possible and if existing
- I do agree, but indexes and tuning the queries can help to limited extent.
If they could be ultimate solution for all situations, Indexed Views
wouldn't be invented! What if your query needs a covering index with more
that 16 columns? What if the index length exceeds 900 bytes? You will not be
able to create useful indexes.
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:#0Fi1lCSFHA.3716@.TK2MSFTNGP14.phx.gbl...
> Do you really have to use an index view ? I just wanna keep track that
even
> it is an outer join the indexes will be used if possible and if existing.
I
> wouldt got the way you are describing although this situation is uncommon
> to me, to enter an order where no customer is assigned to it. (?) Ok, but
i
> wouldt worsen up the situation by building a trigger and doing this kind
of
> workaround. I would build my best on the possibiliies i have, perhaps
there
> is a way to tune up our queries ?!
> http://www.sql-server-performance.com/tuning_joins.asp
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>
> "Leila" <leilas@.hotpop.com> schrieb im Newsbeitrag
> news:%23s11KGCSFHA.1348@.TK2MSFTNGP15.phx.gbl...
the[vbcol=seagreen]
>|||Thanks John,
Using union is great idea but in my query, the main table(orders) is joined
with other parent tables(like customers, employees..).
The same situation can exist: orders with EmployeeID=Null as well.
Please note that orders without customer is just an example, I skipped
describing the real situation (and why orphan rows exist) because tables in
northwind are famous and everybody is familiar with them.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:e3LJqvCSFHA.2932@.TK2MSFTNGP09.phx.gbl...
> Hi
> You can change your outer joins to a union of a query that uses an inner
> join plus a join where the where clause eliminates those returned by the
> inner join.
> If you want to use a default for your order id, make the column not
nullable
> with the default.
> I would have to ask why you have orders without customers?
> John
> "Leila" <leilas@.hotpop.com> wrote in message
> news:%23s11KGCSFHA.1348@.TK2MSFTNGP15.phx.gbl...
the[vbcol=seagreen]
>|||"Leila" <leilas@.hotpop.com> wrote in message
news:edXR8yCSFHA.2788@.TK2MSFTNGP09.phx.gbl...
> - I do agree, but indexes and tuning the queries can help to limited
> extent.
> If they could be ultimate solution for all situations, Indexed Views
> wouldn't be invented! What if your query needs a covering index with more
> that 16 columns? What if the index length exceeds 900 bytes? You will not
> be
> able to create useful indexes.
Not familiar with your DDL, or exactly how you're relating these tables to
one another, but columns with a lot of NULL values don't tend to make very
good indexes either... Are you really relating these tables to one another
using a CustomerID field that can be NULL? How do you keep track of who to
ship the order to? Or, more importantly, where to send the bill? In a
situation like this, would it make sense to generate all your NULL
customerid orders in a separate 'exception'-type query instead of plopping
them down in the middle of orders that were actually placed by customers?
Just wondering...
Instead of going through all the trigger-writing, etc., why not just UPDATE
all current NULL customerid's with the default value you specified; then
define the column as NON-NULL with a Default?|||Thank Michael,
Please note that orders without customer is just an example, I skipped
describing the real situation (and why orphan rows exist) because tables in
northwind are famous and everybody is familiar with them.
"Michael C#" <xyz@.abcdef.com> wrote in message
news:K%Fae.3416$RP1.1452@.fe10.lga...
> "Leila" <leilas@.hotpop.com> wrote in message
> news:edXR8yCSFHA.2788@.TK2MSFTNGP09.phx.gbl...
more[vbcol=seagreen]
not[vbcol=seagreen]
> Not familiar with your DDL, or exactly how you're relating these tables to
> one another, but columns with a lot of NULL values don't tend to make very
> good indexes either... Are you really relating these tables to one
another
> using a CustomerID field that can be NULL? How do you keep track of who
to
> ship the order to? Or, more importantly, where to send the bill? In a
> situation like this, would it make sense to generate all your NULL
> customerid orders in a separate 'exception'-type query instead of plopping
> them down in the middle of orders that were actually placed by customers?
> Just wondering...
> Instead of going through all the trigger-writing, etc., why not just
UPDATE
> all current NULL customerid's with the default value you specified; then
> define the column as NON-NULL with a Default?
>
>

Changing outer joins to inner join

Hi,
I have a main table that references many tables (something like Orders
table, referencing Customers,Employees...).
As an example, there are many orders that have Null value for their
CustomerID therefore I need an Outer Join from Orders table to retrieve all
rows.
Now a View that joins Customers to Orders cannot be indexed because of the
Outer Join.
I was thinking of adding a row to Customers with value of (#) for
CompanyName column and assigning its ID to all Orphan rows in Orders.
I must:
- Write trigger on Orders table to insert ID of # record for new orphan
orders instead of Null.
- Filter # record when I need a real Inner Join.
- and some other considerations...
Instead an Inner Join between Customers and Orders can return all orders and
this query can be used in indexed view.
The performance of queries from main(Orders) table is the most critical
issue. Is it a good decision for changing such queries to Inner Join?
I appreciated any suggestion.
Leila
Do you really have to use an index view ? I just wanna keep track that even
it is an outer join the indexes will be used if possible and if existing. I
wouldt got the way you are describing although this situation is uncommon
to me, to enter an order where no customer is assigned to it. (?) Ok, but i
wouldt worsen up the situation by building a trigger and doing this kind of
workaround. I would build my best on the possibiliies i have, perhaps there
is a way to tune up our queries ?!
http://www.sql-server-performance.com/tuning_joins.asp
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"Leila" <leilas@.hotpop.com> schrieb im Newsbeitrag
news:%23s11KGCSFHA.1348@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I have a main table that references many tables (something like Orders
> table, referencing Customers,Employees...).
> As an example, there are many orders that have Null value for their
> CustomerID therefore I need an Outer Join from Orders table to retrieve
> all
> rows.
> Now a View that joins Customers to Orders cannot be indexed because of the
> Outer Join.
> I was thinking of adding a row to Customers with value of (#) for
> CompanyName column and assigning its ID to all Orphan rows in Orders.
> I must:
> - Write trigger on Orders table to insert ID of # record for new orphan
> orders instead of Null.
> - Filter # record when I need a real Inner Join.
> - and some other considerations...
> Instead an Inner Join between Customers and Orders can return all orders
> and
> this query can be used in indexed view.
> The performance of queries from main(Orders) table is the most critical
> issue. Is it a good decision for changing such queries to Inner Join?
> I appreciated any suggestion.
> Leila
>
|||Hi
You can change your outer joins to a union of a query that uses an inner
join plus a join where the where clause eliminates those returned by the
inner join.
If you want to use a default for your order id, make the column not nullable
with the default.
I would have to ask why you have orders without customers?
John
"Leila" <leilas@.hotpop.com> wrote in message
news:%23s11KGCSFHA.1348@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I have a main table that references many tables (something like Orders
> table, referencing Customers,Employees...).
> As an example, there are many orders that have Null value for their
> CustomerID therefore I need an Outer Join from Orders table to retrieve
> all
> rows.
> Now a View that joins Customers to Orders cannot be indexed because of the
> Outer Join.
> I was thinking of adding a row to Customers with value of (#) for
> CompanyName column and assigning its ID to all Orphan rows in Orders.
> I must:
> - Write trigger on Orders table to insert ID of # record for new orphan
> orders instead of Null.
> - Filter # record when I need a real Inner Join.
> - and some other considerations...
> Instead an Inner Join between Customers and Orders can return all orders
> and
> this query can be used in indexed view.
> The performance of queries from main(Orders) table is the most critical
> issue. Is it a good decision for changing such queries to Inner Join?
> I appreciated any suggestion.
> Leila
>
|||Thanks Jens,

>I just wanna keep track that even
> it is an outer join the indexes will be used if possible and if existing
- I do agree, but indexes and tuning the queries can help to limited extent.
If they could be ultimate solution for all situations, Indexed Views
wouldn't be invented! What if your query needs a covering index with more
that 16 columns? What if the index length exceeds 900 bytes? You will not be
able to create useful indexes.
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:#0Fi1lCSFHA.3716@.TK2MSFTNGP14.phx.gbl...
> Do you really have to use an index view ? I just wanna keep track that
even
> it is an outer join the indexes will be used if possible and if existing.
I
> wouldt got the way you are describing although this situation is uncommon
> to me, to enter an order where no customer is assigned to it. (?) Ok, but
i
> wouldt worsen up the situation by building a trigger and doing this kind
of
> workaround. I would build my best on the possibiliies i have, perhaps
there[vbcol=seagreen]
> is a way to tune up our queries ?!
> http://www.sql-server-performance.com/tuning_joins.asp
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>
> "Leila" <leilas@.hotpop.com> schrieb im Newsbeitrag
> news:%23s11KGCSFHA.1348@.TK2MSFTNGP15.phx.gbl...
the
>
|||Thanks John,
Using union is great idea but in my query, the main table(orders) is joined
with other parent tables(like customers, employees..).
The same situation can exist: orders with EmployeeID=Null as well.
Please note that orders without customer is just an example, I skipped
describing the real situation (and why orphan rows exist) because tables in
northwind are famous and everybody is familiar with them.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:e3LJqvCSFHA.2932@.TK2MSFTNGP09.phx.gbl...
> Hi
> You can change your outer joins to a union of a query that uses an inner
> join plus a join where the where clause eliminates those returned by the
> inner join.
> If you want to use a default for your order id, make the column not
nullable[vbcol=seagreen]
> with the default.
> I would have to ask why you have orders without customers?
> John
> "Leila" <leilas@.hotpop.com> wrote in message
> news:%23s11KGCSFHA.1348@.TK2MSFTNGP15.phx.gbl...
the
>
|||"Leila" <leilas@.hotpop.com> wrote in message
news:edXR8yCSFHA.2788@.TK2MSFTNGP09.phx.gbl...
> - I do agree, but indexes and tuning the queries can help to limited
> extent.
> If they could be ultimate solution for all situations, Indexed Views
> wouldn't be invented! What if your query needs a covering index with more
> that 16 columns? What if the index length exceeds 900 bytes? You will not
> be
> able to create useful indexes.
Not familiar with your DDL, or exactly how you're relating these tables to
one another, but columns with a lot of NULL values don't tend to make very
good indexes either... Are you really relating these tables to one another
using a CustomerID field that can be NULL? How do you keep track of who to
ship the order to? Or, more importantly, where to send the bill? In a
situation like this, would it make sense to generate all your NULL
customerid orders in a separate 'exception'-type query instead of plopping
them down in the middle of orders that were actually placed by customers?
Just wondering...
Instead of going through all the trigger-writing, etc., why not just UPDATE
all current NULL customerid's with the default value you specified; then
define the column as NON-NULL with a Default?
|||Thank Michael,
Please note that orders without customer is just an example, I skipped
describing the real situation (and why orphan rows exist) because tables in
northwind are famous and everybody is familiar with them.
"Michael C#" <xyz@.abcdef.com> wrote in message
news:K%Fae.3416$RP1.1452@.fe10.lga...[vbcol=seagreen]
> "Leila" <leilas@.hotpop.com> wrote in message
> news:edXR8yCSFHA.2788@.TK2MSFTNGP09.phx.gbl...
more[vbcol=seagreen]
not
> Not familiar with your DDL, or exactly how you're relating these tables to
> one another, but columns with a lot of NULL values don't tend to make very
> good indexes either... Are you really relating these tables to one
another
> using a CustomerID field that can be NULL? How do you keep track of who
to
> ship the order to? Or, more importantly, where to send the bill? In a
> situation like this, would it make sense to generate all your NULL
> customerid orders in a separate 'exception'-type query instead of plopping
> them down in the middle of orders that were actually placed by customers?
> Just wondering...
> Instead of going through all the trigger-writing, etc., why not just
UPDATE
> all current NULL customerid's with the default value you specified; then
> define the column as NON-NULL with a Default?
>
>

Changing outer joins to inner join

Hi,
I have a main table that references many tables (something like Orders
table, referencing Customers,Employees...).
As an example, there are many orders that have Null value for their
CustomerID therefore I need an Outer Join from Orders table to retrieve all
rows.
Now a View that joins Customers to Orders cannot be indexed because of the
Outer Join.
I was thinking of adding a row to Customers with value of (#) for
CompanyName column and assigning its ID to all Orphan rows in Orders.
I must:
- Write trigger on Orders table to insert ID of # record for new orphan
orders instead of Null.
- Filter # record when I need a real Inner Join.
- and some other considerations...
Instead an Inner Join between Customers and Orders can return all orders and
this query can be used in indexed view.
The performance of queries from main(Orders) table is the most critical
issue. Is it a good decision for changing such queries to Inner Join?
I appreciated any suggestion.
LeilaDo you really have to use an index view ? I just wanna keep track that even
it is an outer join the indexes will be used if possible and if existing. I
would´t got the way you are describing although this situation is uncommon
to me, to enter an order where no customer is assigned to it. (?) Ok, but i
would´t worsen up the situation by building a trigger and doing this kind of
workaround. I would build my best on the possibiliies i have, perhaps there
is a way to tune up our queries ?!
http://www.sql-server-performance.com/tuning_joins.asp
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"Leila" <leilas@.hotpop.com> schrieb im Newsbeitrag
news:%23s11KGCSFHA.1348@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I have a main table that references many tables (something like Orders
> table, referencing Customers,Employees...).
> As an example, there are many orders that have Null value for their
> CustomerID therefore I need an Outer Join from Orders table to retrieve
> all
> rows.
> Now a View that joins Customers to Orders cannot be indexed because of the
> Outer Join.
> I was thinking of adding a row to Customers with value of (#) for
> CompanyName column and assigning its ID to all Orphan rows in Orders.
> I must:
> - Write trigger on Orders table to insert ID of # record for new orphan
> orders instead of Null.
> - Filter # record when I need a real Inner Join.
> - and some other considerations...
> Instead an Inner Join between Customers and Orders can return all orders
> and
> this query can be used in indexed view.
> The performance of queries from main(Orders) table is the most critical
> issue. Is it a good decision for changing such queries to Inner Join?
> I appreciated any suggestion.
> Leila
>|||Hi
You can change your outer joins to a union of a query that uses an inner
join plus a join where the where clause eliminates those returned by the
inner join.
If you want to use a default for your order id, make the column not nullable
with the default.
I would have to ask why you have orders without customers?
John
"Leila" <leilas@.hotpop.com> wrote in message
news:%23s11KGCSFHA.1348@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I have a main table that references many tables (something like Orders
> table, referencing Customers,Employees...).
> As an example, there are many orders that have Null value for their
> CustomerID therefore I need an Outer Join from Orders table to retrieve
> all
> rows.
> Now a View that joins Customers to Orders cannot be indexed because of the
> Outer Join.
> I was thinking of adding a row to Customers with value of (#) for
> CompanyName column and assigning its ID to all Orphan rows in Orders.
> I must:
> - Write trigger on Orders table to insert ID of # record for new orphan
> orders instead of Null.
> - Filter # record when I need a real Inner Join.
> - and some other considerations...
> Instead an Inner Join between Customers and Orders can return all orders
> and
> this query can be used in indexed view.
> The performance of queries from main(Orders) table is the most critical
> issue. Is it a good decision for changing such queries to Inner Join?
> I appreciated any suggestion.
> Leila
>|||Thanks Jens,
>I just wanna keep track that even
> it is an outer join the indexes will be used if possible and if existing
- I do agree, but indexes and tuning the queries can help to limited extent.
If they could be ultimate solution for all situations, Indexed Views
wouldn't be invented! What if your query needs a covering index with more
that 16 columns? What if the index length exceeds 900 bytes? You will not be
able to create useful indexes.
"Jens Süßmeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:#0Fi1lCSFHA.3716@.TK2MSFTNGP14.phx.gbl...
> Do you really have to use an index view ? I just wanna keep track that
even
> it is an outer join the indexes will be used if possible and if existing.
I
> would´t got the way you are describing although this situation is uncommon
> to me, to enter an order where no customer is assigned to it. (?) Ok, but
i
> would´t worsen up the situation by building a trigger and doing this kind
of
> workaround. I would build my best on the possibiliies i have, perhaps
there
> is a way to tune up our queries ?!
> http://www.sql-server-performance.com/tuning_joins.asp
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>
> "Leila" <leilas@.hotpop.com> schrieb im Newsbeitrag
> news:%23s11KGCSFHA.1348@.TK2MSFTNGP15.phx.gbl...
> > Hi,
> > I have a main table that references many tables (something like Orders
> > table, referencing Customers,Employees...).
> > As an example, there are many orders that have Null value for their
> > CustomerID therefore I need an Outer Join from Orders table to retrieve
> > all
> > rows.
> > Now a View that joins Customers to Orders cannot be indexed because of
the
> > Outer Join.
> > I was thinking of adding a row to Customers with value of (#) for
> > CompanyName column and assigning its ID to all Orphan rows in Orders.
> > I must:
> > - Write trigger on Orders table to insert ID of # record for new orphan
> > orders instead of Null.
> > - Filter # record when I need a real Inner Join.
> > - and some other considerations...
> >
> > Instead an Inner Join between Customers and Orders can return all orders
> > and
> > this query can be used in indexed view.
> > The performance of queries from main(Orders) table is the most critical
> > issue. Is it a good decision for changing such queries to Inner Join?
> > I appreciated any suggestion.
> > Leila
> >
> >
>|||Thanks John,
Using union is great idea but in my query, the main table(orders) is joined
with other parent tables(like customers, employees..).
The same situation can exist: orders with EmployeeID=Null as well.
Please note that orders without customer is just an example, I skipped
describing the real situation (and why orphan rows exist) because tables in
northwind are famous and everybody is familiar with them.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:e3LJqvCSFHA.2932@.TK2MSFTNGP09.phx.gbl...
> Hi
> You can change your outer joins to a union of a query that uses an inner
> join plus a join where the where clause eliminates those returned by the
> inner join.
> If you want to use a default for your order id, make the column not
nullable
> with the default.
> I would have to ask why you have orders without customers?
> John
> "Leila" <leilas@.hotpop.com> wrote in message
> news:%23s11KGCSFHA.1348@.TK2MSFTNGP15.phx.gbl...
> > Hi,
> > I have a main table that references many tables (something like Orders
> > table, referencing Customers,Employees...).
> > As an example, there are many orders that have Null value for their
> > CustomerID therefore I need an Outer Join from Orders table to retrieve
> > all
> > rows.
> > Now a View that joins Customers to Orders cannot be indexed because of
the
> > Outer Join.
> > I was thinking of adding a row to Customers with value of (#) for
> > CompanyName column and assigning its ID to all Orphan rows in Orders.
> > I must:
> > - Write trigger on Orders table to insert ID of # record for new orphan
> > orders instead of Null.
> > - Filter # record when I need a real Inner Join.
> > - and some other considerations...
> >
> > Instead an Inner Join between Customers and Orders can return all orders
> > and
> > this query can be used in indexed view.
> > The performance of queries from main(Orders) table is the most critical
> > issue. Is it a good decision for changing such queries to Inner Join?
> > I appreciated any suggestion.
> > Leila
> >
> >
>|||"Leila" <leilas@.hotpop.com> wrote in message
news:edXR8yCSFHA.2788@.TK2MSFTNGP09.phx.gbl...
> - I do agree, but indexes and tuning the queries can help to limited
> extent.
> If they could be ultimate solution for all situations, Indexed Views
> wouldn't be invented! What if your query needs a covering index with more
> that 16 columns? What if the index length exceeds 900 bytes? You will not
> be
> able to create useful indexes.
Not familiar with your DDL, or exactly how you're relating these tables to
one another, but columns with a lot of NULL values don't tend to make very
good indexes either... Are you really relating these tables to one another
using a CustomerID field that can be NULL? How do you keep track of who to
ship the order to? Or, more importantly, where to send the bill? In a
situation like this, would it make sense to generate all your NULL
customerid orders in a separate 'exception'-type query instead of plopping
them down in the middle of orders that were actually placed by customers?
Just wondering...
Instead of going through all the trigger-writing, etc., why not just UPDATE
all current NULL customerid's with the default value you specified; then
define the column as NON-NULL with a Default?|||Thank Michael,
Please note that orders without customer is just an example, I skipped
describing the real situation (and why orphan rows exist) because tables in
northwind are famous and everybody is familiar with them.
"Michael C#" <xyz@.abcdef.com> wrote in message
news:K%Fae.3416$RP1.1452@.fe10.lga...
> "Leila" <leilas@.hotpop.com> wrote in message
> news:edXR8yCSFHA.2788@.TK2MSFTNGP09.phx.gbl...
> > - I do agree, but indexes and tuning the queries can help to limited
> > extent.
> > If they could be ultimate solution for all situations, Indexed Views
> > wouldn't be invented! What if your query needs a covering index with
more
> > that 16 columns? What if the index length exceeds 900 bytes? You will
not
> > be
> > able to create useful indexes.
> Not familiar with your DDL, or exactly how you're relating these tables to
> one another, but columns with a lot of NULL values don't tend to make very
> good indexes either... Are you really relating these tables to one
another
> using a CustomerID field that can be NULL? How do you keep track of who
to
> ship the order to? Or, more importantly, where to send the bill? In a
> situation like this, would it make sense to generate all your NULL
> customerid orders in a separate 'exception'-type query instead of plopping
> them down in the middle of orders that were actually placed by customers?
> Just wondering...
> Instead of going through all the trigger-writing, etc., why not just
UPDATE
> all current NULL customerid's with the default value you specified; then
> define the column as NON-NULL with a Default?
>
>

changing order of DTS package

Hi everyone,

I have a DTS package that exports table from SQL Server to an Oracle database. However, I want to change the order in which the tables get exported because of referential integrity. I can find no way in the DTS designer to do that! IS there a way to change this order other than specifying a querY for each and every table!

Thanks,

PankajIf you're using Data Driven Query task in your package, - no. You'll need to replace it with individual table transfer tasks per table, where you can specify the order in which each table is being transfered. It's more tedious to create, but gives you much more control.

Changing object ownership in SQL 6.5?

Help!
I've been using SQL 7.0 & 2K for the last four years but need to
change the ownership of some tables in a legacy database residing on
SQL 6.5 from (user) to dbo. I know you can use sp_ChangeObjectOwner
in 7/2K but what is it's equivalent in 6.5 please?
Regards
ALIThere is no in 6.5. You can of course try to look at the source code of
sp_changeobjectowner and try to do the same on a 6.5 install, but that is
not supported and is risky (there might be some architectural reason why
this ability wasn't introduced until 7.0).
I suggest you re-create the object.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"ALI" <ali.shah@.itnetplc.com> wrote in message
news:8487385c.0311110159.56b09753@.posting.google.com...
> Help!
> I've been using SQL 7.0 & 2K for the last four years but need to
> change the ownership of some tables in a legacy database residing on
> SQL 6.5 from (user) to dbo. I know you can use sp_ChangeObjectOwner
> in 7/2K but what is it's equivalent in 6.5 please?
> Regards
> ALI