Alternate to a not in query

-- tested schema below --
-- create tables --
create table tbl_test
(serialnumber char(12))
go
create table tbl_test2
(serialnumber char(12),
exportedflag int)
go
--insert data --
insert into tbl_test2 values ('123456789010',0)
insert into tbl_test2 values ('123456789011',0)
insert into tbl_test2 values ('123456789012',0)
insert into tbl_test2 values ('123456789013',0)
insert into tbl_test2 values ('123456789014',0)
insert into tbl_test2 values ('123456789015',0)
insert into tbl_test2 values ('123456789016',0)
insert into tbl_test2 values ('123456789017',0)
insert into tbl_test2 values ('123456789018',0)
insert into tbl_test2 values ('123456789019',0)

insert into tbl_test values ('123456789011')
insert into tbl_test values ('123456789012')
insert into tbl_test values ('123456789013')
insert into tbl_test values ('123456789014')
insert into tbl_test values ('123456789015')

-- query --
Select serialnumber from tbl_test2
where serialnumber
not in (select serialnumber from tbl_test) and
exportedflag=0

This query runs quite fast with only the data above but when both
tables get million plus rows, the query simply bogs down. Is there a
better way to write this query?Select serialnumber
from tbl_test2 a
left joint tbl_test b on a. serialnumber = b.serialnumber
where (b.serialnumber IS NULL)
AND (a.exportedflag=0)|||There is another way to write the query, but it's not better (in fact,
I think it's worse):

Select tbl_test2.serialnumber from tbl_test2
left join tbl_test on tbl_test2.serialnumber=tbl_test.serialnumber
where exportedflag=0 and tbl_test.serialnumber is null

To improve the performance of this query, you should create primary
keys on the tables. Besides the conceptual benefits of a proper design,
this would accomplish (at least) the following things:
- create an index on the serialnumber column
- declare that the serialnumber column does not allow duplicates
- declare that the serialnumber column does not allow nulls
These things will help the Query Optimizer very much to create a better
execution plan.

Razvan|||
Razvan Socol wrote:
> There is another way to write the query, but it's not better (in fact,
> I think it's worse):
> Select tbl_test2.serialnumber from tbl_test2
> left join tbl_test on tbl_test2.serialnumber=tbl_test.serialnumber
> where exportedflag=0 and tbl_test.serialnumber is null

Razvan,

Why worse?

The common wisdom seems to be that it is always more efficient
eliminate nested subqueries, if possible.

My understanding is that the optimizer will internally eliminate the
subquery by doing a left join as above if it can.|||Ira Gladnick (IraGladnick@.yahoo.com) writes:
> Why worse?
> The common wisdom seems to be that it is always more efficient
> eliminate nested subqueries, if possible.

It's worse, becase it does not express the intent of the query equally
well, and therefore can contribute to higher maintenance costs.

> My understanding is that the optimizer will internally eliminate the
> subquery by doing a left join as above if it can.

I don't know if this is the case, but in such case there is even less
reason to rewrite the query in an obscure way.

I would write the query as:

Select serialnumber
from tbl_test2 t2
where not exists (select *
from tbl_test t
where t2.serialnuber = t.serialnumber)
and exportedflag=0

In SQL 6.5 this would typically perform better than NOT IN. But I believe
SQL 2000 will rewrite NOT IN to NOT EXISTS internally, so it is not that
much of an issue for performance. But NOT EXISTS is more general to use
than NOT IN, because you can handle multi-column conditions. Furthermore,
if there are NULL values involved, NOT IN can give you surpriese.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||(kjaggi@.hotmail.com) writes:
> -- query --
> Select serialnumber from tbl_test2
> where serialnumber
> not in (select serialnumber from tbl_test) and
> exportedflag=0
> This query runs quite fast with only the data above but when both
> tables get million plus rows, the query simply bogs down. Is there a
> better way to write this query?

Beside the obvious point from Razvan about indexes, if you are on a multi-
CPU box, you can try this at the end of the query:

OPTION (MAXDOP 1)

this turns off parallelism. I've seen SQL Server use massive parallel
plans for this type of query, when a non-parallel plan have been much
faster.

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

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

Alter table new column and update

Hi

for MS SQL 2000/2005

I am having a table (an old database, not mine) with char value for the column [localisation]

Users
[name] [nvarchar] (100) NOT NULL ,
[localisation] [nvarchar] (100)NULL

Now i have created a table [Localisation]

Localisation
[id_Localisation] [int] NOT NULL,
[localisation] [nvarchar] (100) NOT NULL

I am adding a new column to Users

ALTER TABLE [Users] ADD
[id_Localisation] int NULL

and I want to update the Column [Users].[id_Localisation] before to drop the column [Users].[Localisation]

something like

UPDATE [Users] SET id_Localisation = (SELECT Localisation.id_Localisation
FROM Localisation FULL OUTER JOIN
Users ON Localisation.Localisation = Users.Localisation)

Users.Localisation can have a NULL value (then no id_localisation return)

but it doesnt work because it returns > 1 row

thank you

how can I do it ?update [Users]
set id_Localisation = t2.id_Localisation
from [Users] t1
inner
join Localisation t2
on t1.Localisation = t2.Localisation|||it works perfectly

thanks a lot

do you thing i have to add a contrainst to this new column ?|||it would be a good idea to declare Users.id_Localisation as a foreign key|||but 5 tables are using this id_Localisation, can i add a FK to each one ?
FK_FK_Users_Localisation
FK_job_Localisation
FK_groups_Localisation
.....

if so

5 times (for each tables)

ALTER TABLE [Users] ADD
id_Localisation int NULL

ALTER TABLE [Users] WITH NOCHECK ADD
CONSTRAINT [FK_Users_Localisation] FOREIGN KEY
(
[id_Localisation]
) REFERENCES [Localisation] (
[id_Localisation]
)

I dont want to apply ON DELETE CASCADE , but to give a Id_localisation = 0 or NULL if a Localisation is deleted, how can i do it
??

thanks again for helping|||I dont want to apply ON DELETE CASCADE, but to give a Id_localisation = 0 or NULL if a Localisation is deleted
You can use ON DELETE SET NULL for that purpose|||but 5 tables are using this id_Localisation, can i add a FK to each one ?yes . ;)|||You can use ON DELETE SET NULL for that purposeunfortunately, not in SQL Server 2000, only in SQL Server 2005|||unfortunately, not in SQL Server 2000, only in SQL Server 2005Ah, right. I checked the wrong manual ;)|||well, i wouldn't exactly call it wrong -- i'm sure it's the right one for SQL Server 2005!!|||thank you

this application must work on 2000 and 2005

alter table and logging

I just did an alter table alter column on a test 7.0 database to change a
non-indexed, non-primary key column of char(2) to a varchar(100). The table
was about a 1G and it logged the whole thing. I guess that's not too
surprising.
Is the only way around this to create a brand new column and then work with
the new column instead?
You are correct. The transaction log must remember the exact actions
taken by all users/roles in a database in order to perform a proper
recovery. You could do a SELECT INTO and make sure SELECT INTO/BULK
COPY is enabled at the database level and that will not be logged.
Shahryar
CLM wrote:

>I just did an alter table alter column on a test 7.0 database to change a
>non-indexed, non-primary key column of char(2) to a varchar(100). The table
>was about a 1G and it logged the whole thing. I guess that's not too
>surprising.
>Is the only way around this to create a brand new column and then work with
>the new column instead?
>
Shahryar G. Hashemi | Sr. DBA Consultant
InfoSpace, Inc.
601 108th Ave NE | Suite 1200 | Bellevue, WA 98004 USA
Mobile +1 206.459.6203 | Office +1 425.201.8853 | Fax +1 425.201.6150
shashem@.infospace.com | www.infospaceinc.com
This e-mail and any attachments may contain confidential information that is legally privileged. The information is solely for the use of the intended recipient(s); any disclosure, copying, distribution, or other use of this information is strictly prohi
bited. If you have received this e-mail in error, please notify the sender by return e-mail and delete this message. Thank you.

alter table and logging

I just did an alter table alter column on a test 7.0 database to change a
non-indexed, non-primary key column of char(2) to a varchar(100). The table
was about a 1G and it logged the whole thing. I guess that's not too
surprising.
Is the only way around this to create a brand new column and then work with
the new column instead?You are correct. The transaction log must remember the exact actions
taken by all users/roles in a database in order to perform a proper
recovery. You could do a SELECT INTO and make sure SELECT INTO/BULK
COPY is enabled at the database level and that will not be logged.
Shahryar
CLM wrote:
>I just did an alter table alter column on a test 7.0 database to change a
>non-indexed, non-primary key column of char(2) to a varchar(100). The table
>was about a 1G and it logged the whole thing. I guess that's not too
>surprising.
>Is the only way around this to create a brand new column and then work with
>the new column instead?
>
Shahryar G. Hashemi | Sr. DBA Consultant
InfoSpace, Inc.
601 108th Ave NE | Suite 1200 | Bellevue, WA 98004 USA
Mobile +1 206.459.6203 | Office +1 425.201.8853 | Fax +1 425.201.6150
shashem@.infospace.com | www.infospaceinc.com
This e-mail and any attachments may contain confidential information that is legally privileged. The information is solely for the use of the intended recipient(s); any disclosure, copying, distribution, or other use of this information is strictly prohibited. If you have received this e-mail in error, please notify the sender by return e-mail and delete this message. Thank you.

alter table and logging

I just did an alter table alter column on a test 7.0 database to change a
non-indexed, non-primary key column of char(2) to a varchar(100). The table
was about a 1G and it logged the whole thing. I guess that's not too
surprising.
Is the only way around this to create a brand new column and then work with
the new column instead?You are correct. The transaction log must remember the exact actions
taken by all users/roles in a database in order to perform a proper
recovery. You could do a SELECT INTO and make sure SELECT INTO/BULK
COPY is enabled at the database level and that will not be logged.
Shahryar
CLM wrote:

>I just did an alter table alter column on a test 7.0 database to change a
>non-indexed, non-primary key column of char(2) to a varchar(100). The tabl
e
>was about a 1G and it logged the whole thing. I guess that's not too
>surprising.
>Is the only way around this to create a brand new column and then work with
>the new column instead?
>
Shahryar G. Hashemi | Sr. DBA Consultant
InfoSpace, Inc.
601 108th Ave NE | Suite 1200 | Bellevue, WA 98004 USA
Mobile +1 206.459.6203 | Office +1 425.201.8853 | Fax +1 425.201.6150
shashem@.infospace.com | www.infospaceinc.com
This e-mail and any attachments may contain confidential information that is
legally privileged. The information is solely for the use of the intended
recipient(s); any disclosure, copying, distribution, or other use of this in
formation is strictly prohi
bited. If you have received this e-mail in error, please notify the sender
by return e-mail and delete this message. Thank you.

Alter Table - Change Column Datatype

Hi,

I want to change the datatype of an existing column from char to
varbinary. When I run the "Alter Table" statement, I get the
following error message -

Disallowed implicit conversion from data type char to data type
varbinary, table 'test.dbo.testalter', column 'col1'. Use the CONVERT
function to run this query.

Can the CONVERT function be used as part of an alter table/alter
column? Is there another way besides renaming the table and creating
a new one?

Thanks,
BruceOn 19 Apr 2004 11:29:46 -0700, Bruce wrote:

>Hi,
>I want to change the datatype of an existing column from char to
>varbinary. When I run the "Alter Table" statement, I get the
>following error message -
>Disallowed implicit conversion from data type char to data type
>varbinary, table 'test.dbo.testalter', column 'col1'. Use the CONVERT
>function to run this query.
>Can the CONVERT function be used as part of an alter table/alter
>column? Is there another way besides renaming the table and creating
>a new one?
>Thanks,
>Bruce

Yes, there is another way: rename not the whole table, but just the
column, then create a new one:

EXEC sp_rename 'test.dbo.testalter.col1' 'col1old', COLUMN
go
ALTER TABLE test.dbo.testalter
ADD col1 varbinary(321) NULL
-- If it has to be NOT NULL, change this to read
-- ADD col1 varbinary(321) NOT NULL DEFAULT 0
go
UPDATE test.dbo.testalter
SET col1 = CAST(col1old AS varbinary(321))
go
ALTER TABLE test.dbo.testalter
DROP COLUMN col1old
go

Best, Hugo
--

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

Alter column with data

I am trying to use T-SQL to alter a column with data already in it from char to varbinary. This is very easy to do in Enterprise Manager, but just for my own knowledge I'm trying to figure out how to do this in T-SQL. I don't mind losing the data (I'm using a temp table to bring the converted data back in), but I want to keep the column in the same place. Here's what I have so far, but I keep getting an implicit conversion error:

UPDATE UserProfile

SET PassID = CAST(PassID AS VARBINARY(128))
GO
ALTER TABLE UserProfile

ALTER COLUMN PassID VARBINARY(128)
GO

In Enterprise Manager, there is an option to preview the code that will be executed. If you check that you will often find to make a change that is disallowed with simple alters and to keep the column order the same, the table is dropped and recreated. What makes the operation difficult, in the general case, is handling foreign key constraints.

Column order should never be relied on in Tables, but I can understand the desire from a documentation point of view.

The general approach for altering a column's datatype is to:

1. Drop any foreign key constraints.

2. Rename the table to a temporary name.

3. Recreate the table with the new definition.

4. Copy the data back -- setting identity insert if necessary.

5. Recreate all foreign key constraints. (4 and 5 can probably be switched.)

6. Drop the original table.

If you don't care about column order, you can rename the old column, add a new column with the different datatype, transfer the data, and drop the original column.

Alter a column to be the table identity column

i have a table
table1
column1 int not null
column2 char not nul
column3 char
i want to script a change for table1 to alter column1 to be the table identity column. not primary.you can not. You must create a new table with an identity column already defined and copy your data from the old to the new, drop your old table and rename your new table|||Just a curious question then why is that i can use the enterprise manager to do it?
the tables in question have data in them which is why i dont want to drop them.|||Just a curious question then why is that i can use the enterprise manager to do it?
the tables in question have data in them which is why i dont want to drop them.

Care to guess what Enterprise Mangler is about to do in the background?|||Care to guess what Enterprise Mangler is about to do in the background?

it is really curious that the enterprise mangler does use to do this operation. I had a whole of tables to convert to identity once and I traced the operation that EM does and there is a lot of weird stuff in their that does not pan out in the QA.

as for dudes question. you need to do a an

INSERT INTO MyNewTable
SELECT ... FROM MyOldTable

before you drop the old table.|||Well since it was the client that messed the tables up by importing them into another db then importing them back without selecting all objects, i showed them how to fix them using the EM and said to get after it. (1242 tables in all which is why i was trying to find a way of scripting it). i did use another clients db to create all the indexes and sent to them to use.
anyways thanks for all the time and help.

all spaces in a CHAR(5) column

i'm going nuts with this, i suppose i will crack it eventually, but i thought i'd ask around here, seems like all the smart SQL Server guys hang out here

(i'm an SQL guy, not an SQL Server guy)

how does one place 5 spaces into a CHAR(5) column?
create table testzeros
( id smallint not null primary key identity
, myfield char(5)
)
insert into testzeros (myfield) values (' 1')
insert into testzeros (myfield) values (' 11')
insert into testzeros (myfield) values (' 111')
insert into testzeros (myfield) values (' 1111')
insert into testzeros (myfield) values ('11111')
insert into testzeros (myfield) values (' ')

select id
, myfield
, len(myfield) as L
from testzerosno matter what i do, id=6 shows up with L=0, just like an empty string

i've even tried inserting 4 spaces and a non-blank character, which enters just fine, just as you would expect, but when i update the value and replace the non-blank character with a blank, all 5 spaces collapse back to an empty string

is there some kind of server setting like SET ALL_SPACE_EQUALS_EMPTY_YOU_IDIOT to OFF or something?FROM BOL:

"Interpretation of an empty string is controlled by the compatibility level, which is set with the sp_dbcmptlevel system stored procedure. If the compatibility level is 65 or lower, SQL Server interprets empty strings as single spaces. If the compatibility level is 70 or 80, SQL Server interprets empty strings as empty strings. For more information, see sp_dbcmptlevel."

I never used this and afterreading the documentation for sp_dbcmptlevel I am not sure it is such a good idea.|||thanks, that at least sounds somewhat related

but we aren't talking about inserting an empty string

i even tried this --

insert into testzeros(myfield) values (space(5))

and this was converted to empty string as well|||aha!! found it!!

i had declared it as NULL

BOL says:If ANSI_PADDING is ON when a char NULL column is created, it behaves the same as a char NOT NULL column: values are right-padded to the size of the column. If ANSI_PADDING is OFF when a char NULL column is created, it behaves like a varchar column with ANSI_PADDING set OFF: trailing blanks are truncated.
i am an idiot

:)|||Idiocy has been copyrighted??|||Idiots don't find the solutions to their own problems. Only an idiot would not know this.

Where that leaves you, I'm not sure. But thanks for posting the solution anyway.|||Idiocy has been copyrighted??no, but that particular phrase is all mine -- and it's a trademark!!

i lied about it being registered, though, and i suppose somebody else will eventually run out and register it -- i guess i'm just an idiot!!|||I did'nt know this off the top of my head. So I must be pretty stupid.

I am not participating in this forum anymore. Logging out.|||Try such. Its perversion IMHO, but one works
create table testzeros
( id smallint not null primary key identity
, myfield varchar(5) <--
)
insert into testzeros (myfield) values (' 1')
insert into testzeros (myfield) values (' 11')
insert into testzeros (myfield) values (' 111')
insert into testzeros (myfield) values (' 1111')
insert into testzeros (myfield) values ('11111')
insert into testzeros (myfield) values (' ')

select id
, myfield
, len(myfield) as L
, len(replace(myfield, ' ', '_')) <-- look this result
from testzeros|||no, but that particular phrase is all mine -- and it's a trademark!!

Ahh. That may explain why we have to go around saying "I R dum".|||"I R Dum" is freely available as SharePhrase. You can use it all you want, but you are not allowed to modify it or sell it.|||I'm thinking that with the mountains of easily verifiable "prior art" on this topic, that it would never survive a trademark registration. I could be wrong, but I think this would be a real pig to try to register. ;)

-PatP|||... the mountains of easily verifiable "prior art" on this topic... tee hee

i'm responsible for creating plenty of piles, that's for sure ;)