2005 Query help

Please help.

On sql 2000 i have a query like this where the columns are primary keys.

Select count(*) from db.dbo.table1

where convert(varchar(3), col1) + convert(varchar(10), col2) not in

(select convert(varchar(3), col1) + convert(varchar(10), col2) from db.dbo.table2)

It completes in 1 second with sql 2000. I have restored the db to sql 2005 and run the same query. The processors peg and it goes to la la land. I have updated statistics and installed SP1. Anyone have ideas? The sql 2005 is even way better than the sql 2000 box. If you have a better way to perform the same task, please let me know.

Thanks!

can you compare execution plans on both servers? That should give a head start.|||

Hey,

I did compare the execution plans. For some reason, the 2005 execution plan has more to it and mentions parallelism. It just does not run the query. The box just maxes out and stays maxed out. Very strange.

I've rebooted the box for the heck of it and it doesn't matter. It doesn't want to run. Thanks for the response.

|||Moving to T-SQL forum. Maybe there's a way to rewrite the query.|||

Try this using EXISTS:

select *
from db.dbo.table1 as table1
where not exists (select *
from db.dbo.table2 as table2
where table1.col1 = table2.col1
and table1.col2 = table2.co2)

This should perform better and give the same results (actually more correct, because the varchar conversions could in some rare cases given invalid values).

As for the long run times, How much data is involved? One of the problems I have run into is a lot of waits during parallel operations when some larger operations go parallel. I had to tune some of my data warehouse queries by setting MAXDOP to 1.

I found this by executing this query:

select der.session_id, der.wait_type, der.wait_time,
der.status as requestStatus,
des.login_name,
cast(db_name(der.database_id) as varchar(30)) as databaseName,
des.program_name,
execText.text as objectText,
case when der.statement_end_offset = -1 then '--see objectText--'
else SUBSTRING(execText.text, der.statement_start_offset/2,
(der.statement_end_offset - der.statement_start_offset)/2)
end AS currentExecutingCommand
from sys.dm_exec_sessions des
join sys.dm_exec_requests as der
on der.session_id = des.session_id
cross apply sys.dm_exec_sql_text(der.sql_handle) as execText
where des.session_id <> @.@.spid --eliminate the current connection

And checking the wait type. Lots of huge CXPACKET waits. Once you get into the wait, watch the results here and post them. You can see where the execution is at by watching the currentExecutingCommand column (it is really cool to watch when you aren't stuck :)

|||

Hey Louis. Thanks for the response!

The exists does work very well. I use that most of the time. I also will put a hyphen between the converts to help avoid getting errors when I do use the "in" style.

I tried running the query you posted on the 2005 box and it complains about '.' near the end of the query saying incorrect syntax. I don't see why though.

It's very strange though how they will be handled so differently between 2000 and 2005.

Thanks again!

|||

The other concern with the IN style is indexing. If you put values in functions or expressions it invalidates use of indexes. But putting seperators that cannot exist in the data will make it "technically" safe.

I took that query verbatim and ran it on my express instance and it worked fine. It is version:

Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86) Oct 14 2005 00:33:37 Copyright (c) 1988-2005 Microsoft Corporation Express Edition on Windows NT 5.1 (Build 2600: Service Pack 2)

I will try it on my 2005 SP1 box and make sure it works there, but that is interesting.

|||

Hey Louis. Thanks for the info.

Thanks too for trying it on your boxes!

One of my tables has 1.4 million records and the other has about 40K.

Thanks

2005 product key

I just downloaded sql server 2005 dev edition from my msdn subscription,
however, in the listing of product keys, I dont see any keys for sql server.
Dont I need a product key for this when I install it?
moondaddy@.nospam.nospamNot for Dev edition, no.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"moondaddy" <moondaddy@.nospam.nospam> wrote in message
news:OWi1L2X5FHA.1864@.TK2MSFTNGP12.phx.gbl...
>I just downloaded sql server 2005 dev edition from my msdn subscription,
>however, in the listing of product keys, I dont see any keys for sql
>server. Dont I need a product key for this when I install it?
> --
> moondaddy@.nospam.nospam
>|||Download the DVD edition (sorry). There was a problem with the initial CD
style distribution that incorrectly required product keys; I hope the
problem isn't that you started the download a w ago and only just
finished. The MSDN version of SQL Server 2005 should not require product
keys at all.
"moondaddy" <moondaddy@.nospam.nospam> wrote in message
news:OWi1L2X5FHA.1864@.TK2MSFTNGP12.phx.gbl...
>I just downloaded sql server 2005 dev edition from my msdn subscription,
>however, in the listing of product keys, I dont see any keys for sql
>server. Dont I need a product key for this when I install it?
> --
> moondaddy@.nospam.nospam
>|||moondaddy wrote:
> I just downloaded sql server 2005 dev edition from my msdn
> subscription, however, in the listing of product keys, I dont see any
> keys for sql server. Dont I need a product key for this when I
> install it?
It might have the key built into the download. Some products work that
way to ease installation. Start the install and see.
David Gugick
Quest Software
www.imceda.com
www.quest.com

2005 encryption - symmetric keys

Hi There

We have been playing around with encryption in 2005. I cannot find a BOL topic that discusses dropping encryption objects such as keys.

We do the followign steps:

Create master key with password, then we create a certificate using the master key, we then create a symmetric key using this certificate and encrypt data columns.

But what i find worrying is that you can then drop the symmetric key , there are no warnings that you have objects dependant on this key for decryption.

Once you have dropped the key you cannot decrypt the data anymore?

Also the key defults the expiration date to 1 year.

WHat happens after 1 year when you have encrypted data and an expired key, or someone drops the key ? How can you ever decrypt the data after that ?

You can backup master keys nd certificates but not symmetric keys?

It seems to be that youc an very easily orphan encrypted data by the loss of the symmetric key for whatever reason, is this correct ?

Thanx

In SQL Server 2005 we have support to explicitly encrypt/decrypt data using builtins, but there is no way to mark a column as being used to store encrypted data, therefore there is no way to bound any key to the data encrypted by it. Unfortunately this means that the system doesn’t know if any given symmetric key is being used or not, and they can be accidentally dropped.

As you mentioned, you can create backups for master key and certificates, but in SQL Server 2005 there is no support for backing up individual symmetric keys. To prevent symmetric key loss you can create a database backup or alternatively create your symmetric keys using the KEY_SOURCE and IDENTITY_VALUE syntax. This syntax will allow you to recreate the keys in case they are dropped by mistake, if you don’t want to permanently store the key in the database or if you need to create the same symmetric key in a different database. Example:

CREATE SYMMETRIC KEY key_demo2 WITH ALGORITHM = TRIPLE_DES ENCRYPTION BY CERTIFICATE cert_demo2

go

-- Create a symmetric key with KEY_SOURCE and IDENTITY_VALUE so it can be recreated

CREATE SYMMETRIC KEY key_demo_recreate WITH ALGORITHM = TRIPLE_DES,

KEY_SOURCE = 'Key source - keep this source secret protected!',

IDENTITY_VALUE = 'My Key identity'

ENCRYPTION BY CERTIFICATE cert_demo

go

-- Create a table to store some encrypted data

CREATE TABLE t_secret_data( mySecret varbinary( 100 ) )

go

OPEN SYMMETRIC KEY key_demo_recreate DECRYPTION BY CERTIFICATE cert_demo

go

INSERT INTO t_secret_data values( encryptbykey( key_guid( 'key_demo_recreate' ), 'Secret data 1' ))

INSERT INTO t_secret_data values( encryptbykey( key_guid( 'key_demo_recreate' ), 'Secret data 2' ))

INSERT INTO t_secret_data values( encryptbykey( key_guid( 'key_demo_recreate' ), 'Secret data 3' ))

go

SELECT convert( varchar(100), decryptbykey( mySecret)) FROM t_secret_data

go

CLOSE SYMMETRIC KEY key_demo_recreate

go

-- We can drop the key, either by mistake or on purpose

DROP SYMMETRIC KEY key_demo_recreate

go

-- At this point we cannnot recover the encrypted data.

-- We need to recreate the symmetric key to recover it.

-- notice that the key name is not necessarily the same

CREATE SYMMETRIC KEY key_demo_recreate2 WITH ALGORITHM = TRIPLE_DES,

KEY_SOURCE = 'Key source - keep this source secret protected!',

IDENTITY_VALUE = 'My Key identity'

ENCRYPTION BY CERTIFICATE cert_demo

go

-- Notice that we use the new key name

OPEN SYMMETRIC KEY key_demo_recreate2 DECRYPTION BY CERTIFICATE cert_demo

go

-- Same select statement

-- As the key ring uses the key identifier (key_guid),

-- and not the key name we don't need to change the decryption statement

SELECT convert( varchar(100), decryptbykey( mySecret)) FROM t_secret_data

go

CLOSE SYMMETRIC KEY key_demo_recreate2

go

The certificate expiration date is stored in metadata so it can be used/enforced by any application, but it won’t be used by SQL Server for any of the encryption features. You will be able to encrypt/decrypt data and keys using an expired certificate, but be aware that other SQL Server features such as Service Broker will enforce this expiration date.

I hope this information was able to answer your questions, please feel free to send us follow up questions and additional feedback.

-Raul Garcia

SDE/T

SQL Server Engine

|||

Hi Raul

That does help alot, exactly what i wanted to know did you get this from BOL, i had trouble searching for a BOL topic with this information, if so do youhave the name of the topic for me ?

Thanx

|||

I am part of the test team for this particular feature and I am quite familiar with it; I really didn’t look for this information on BOL; but I think you are right, we don’t have an article describing symmetric keys and potential data loss in case of an accidental DROP SYMMETRIC KEY in detail. I will open a bug for BOL.

In the meantime, I recommend the following resources:

* CREATE SYMMETRIC KEY (Transact-SQL): http://msdn2.microsoft.com/en-us/library/ms188357.aspx

* DROP SYMMETRIC KEY (Transact-SQL) : http://msdn2.microsoft.com/en-us/library/ms182698.aspx

* How to: Create Identical Symmetric Keys on Two Servers: http://msdn2.microsoft.com/en-us/library/ms366281.aspx

* How to: Encrypt a Column of Data : http://msdn2.microsoft.com/en-us/library/ms179331.aspx

* Laurentiu Cristofor's blog : http://blogs.msdn.com/lcris/

* Raul Garcia’s blog: http://blogs.msdn.com/raulga/

Thanks a lot for the feedback, and feel free to send us more questions and feedback. We really appreciate it.

-Raul Garcia

SDE/T

SQL Server Engine

|||

Excellent, thanx a million Raul.

Yes a BOL topic that discussed dependancies of encryption objects, as well as considerations when dropping Keys etc would definately be valuable, thanx for the feedback.

Cheers

2005 breaks sp_rename for constraints?

BooksOnline indicates that sp_rename can still be used to rename constraints such as primary keys, foreign keys, defaults, etc. in SQL Server 2005. However, when I try to do so I get this:

"Either the parameter @.objname is ambiguous or the claimed @.objtype (object) is wrong."

After looking at the code for sp_rename, it looks like this problem might be related to another bit of apparent misbehavior on 2005's part: when I execute "select object_name()" with the Id number of a constraint, it returns the name, but when I execute "select object_id()" with the name of that same constraint, it returns null. Why is that?

Thanks,

Ron

Did you specify the schema ? make sure that Object_id is only valid for the current context, the database you are currently connected with.

HTH, jens Suessmeyer.

http://www.sqlserver2005.de|||

It worked for me on RTM Express Edition. Can you post the code you used/reproduce it? Here is my code:

create table test
(
testId int constraint pktest primary key
)
go
select object_id('pktest')
select object_name(object_id('pktest'))
go
/*
--
613577224

pktest
*/
go
sp_rename 'pktest','newPkTest'
go
select object_id('pktest')
select object_name(object_id('pktest'))

select object_id('newPkTest')
select object_name(object_id('newPktest'))
go
/*

--
NULL

--
NULL

--
613577224

--
newPkTest
*/

|||

Sorry, I thought I had deleted this one. Yes, I had not specified the schema of the constraint. Doing so helped a lot!

Thanks,

Ron