SQL Server SQL Query Excluding Specific Field Values (Efficienct Code)

Using  VB.NET Winforms application:  Essentially, I would like to generate a list of GUIDS to exclude from a query (anywhere from about 10 to 1000 GUIDS).  I am looking to determine which of these is most efficient.  Assuming that I have read-only access, can run SQL query code (but not create stored procedures).  

Options:
1)  Dynamically create a SQL WHERE clause to exclude the GUIDS (e.g. "WHERE field1 is NOT IN ('GUID1', 'GUID2", etc...)"
2) Create temporary table in SQL, load the excluded GUIDS, then run the final query against standard tables JOINed with this temporary table
3) Other?
eeyoAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Friedhelm Feller-PrzybylSoftware EngineerCommented:
Good morning,

I guess that the GUIDs which you want to exclude will change often?
From my experience I would tend to the temporary table with the GUIDs. Don't forget to add an index though.
Greetings
Friedhelm
0
Nitin SontakkeDeveloperCommented:
Depending on what version of SQL Server you have, you can even opt for creating a table variable. An inline index is allowed as well. Would just second Friedhelm and go with a table with joins and null / not null check.
0
eeyoAuthor Commented:
Thanks for the replies.  The GUIDs would change but not often.  Due to the read only access, my plan was to track this list of "excluded" GUIDs in the VB.Net winforms application, and then generate dynamic SQL code or the temporary table.

Sorry, I don't have access to the particular database at the moment.  Can a temporary table be used/created if the DB user has read-only privileges?

Also, just to confirm, the temporary table will be deleted from the database once that particular connection is closed, right?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Nitin SontakkeDeveloperCommented:
Please mention the version of SQL Server, when you get it, very important.

Our responses are not much around where you will stored GUIDs and dynamic SQL. It is to state that table + join approach is preferred over the in clause.

If you have read only access, you will obviously will not be able to create a temp table. However, you can create a table variable, I believe.

Temporary tables does automatically gets dropped when connection is closed.
0
eeyoAuthor Commented:
I will be connecting to SQL Server 2008 and 2012 databases.  If I need to request slightly elevated user rights from the DBA, do you know what specific rights I should mention to be able to create and use temporary tables? (Sorry, DB administration novice here)
0
Nitin SontakkeDeveloperCommented:
You can use table variables. That's probably the best way.

Unfortunately, I am not DBA either. To the best of my knowledge, db_reader and then db_writer role. While db_writer role will allow to you to perform CRUD (insert, select, update, delete) operations, it would still not allow to you create objects such as temp tables (I think!)

As it turns out you need no special permissions to create a temp table as stated here.
0
eeyoAuthor Commented:
Thanks to all.  Nice tidbits about adding an index, and no special permissions for temp tables.
0
eeyoAuthor Commented:
and go with a table with joins and null / not null check.
Nitin, can you clarify why you mentioned the null / not null check?  I don't usually do that for SQL Joins.
0
Nitin SontakkeDeveloperCommented:
For example...

declare @guidstable table
(
   [someguid] uniqueidentifier not null primary key
)

insert into @guidtable ([someguid]) values 
('ddd'),
('eee')

select *
from [yourtablewithguids] yt
left outer join @guidtable g on yt.[yourguidcolumn] = g.[someguid]
where g.[someguid] is null -- This will give you rows from [yourtablewithguids] excluding guids in @guidtable

Open in new window


Hope it helps!
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Nitin SontakkeDeveloperCommented:
The kind of insert into [tablename] values (),() syntax I have given above you can add maximum 1000 values in one go. You will need to add more 'insert into's for more batches of 1000 values each.
0
eeyoAuthor Commented:
Thanks for the sample code, Nitin.  I was actually considering using NOT EXISTS, but your code seems slightly simpler.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.