eeyo
asked on
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?
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?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Nitin Sontakke
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.
ASKER
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?
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?
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.
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.
ASKER
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)
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.
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.
ASKER
Thanks to all. Nice tidbits about adding an index, and no special permissions for temp tables.
ASKER
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
ASKER
Thanks for the sample code, Nitin. I was actually considering using NOT EXISTS, but your code seems slightly simpler.