Go Back   Access World Forums > Apps and Windows > SQL Server

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 02-20-2014, 09:20 AM   #1
Rx_
Nothing In Moderation
 
Rx_'s Avatar
 
Join Date: Oct 2009
Location: Denver, Colorado
Posts: 2,800
Thanks: 636
Thanked 337 Times in 307 Posts
Rx_ has a spectacular aura about Rx_ has a spectacular aura about Rx_ has a spectacular aura about
Using IIF in query for Linked Tables SQL Server 2012

In SQL Server 2012, the T-SQL languaged added the IIF. The IIF is actually a wrapper that converts to a T-SQL Case statement during runtime.

My SQL Server is 2008R2. The IIF is not part of the T-SQL language.
My MS Access 2010 uses a DSN-Less connection using SQL Server Native Client 11.0 (the latest one for SQL Server 2013).

Does the ODBC (Native Client) translate the IIF in an Access Query into a Case statement for the SQL Server side?

For speed purposes, I am trying to determine if to invest the overhead in re-writing Stored Procedures with Case statements - or if the ODBC is doing the job for me anyway?

__________________
Were you lucky enough to get an answer? Please mark your question as [SOLVED] The original poster can go to Thread Tools to mark it as Solved.

Quotation Thomas Jefferson: "Peace is that brief glorious moment in history when everybody stands around reloading."

There are 2 Kinds of Countries on this Planet
1. Those that use the Metric System
2. Those that had a man walk on the moon

Denver, Colorado - The "Mile High City" - non-metric!
Rx_ is offline   Reply With Quote
Old 02-20-2014, 01:35 PM   #2
Galaxiom
Super Moderator
 
Join Date: Jan 2009
Location: NSW Australia
Posts: 11,573
Thanks: 88
Thanked 1,486 Times in 1,402 Posts
Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold
Re: Using IIF in query for Linked Tables SQL Server 2012

Yes. ODBC does the translation.

A stored procedure on the server will be slightly faster because it has a stored execution plan. However you probably won't notice the difference on a lightly loaded server.

Eliminating the need to build the execution plan is more important for reducing the load on a busy server.
Galaxiom is offline   Reply With Quote
The Following User Says Thank You to Galaxiom For This Useful Post:
Rx_ (02-20-2014)
Old 02-21-2014, 06:21 AM   #3
Rx_
Nothing In Moderation
 
Rx_'s Avatar
 
Join Date: Oct 2009
Location: Denver, Colorado
Posts: 2,800
Thanks: 636
Thanked 337 Times in 307 Posts
Rx_ has a spectacular aura about Rx_ has a spectacular aura about Rx_ has a spectacular aura about
Thumbs up Re: Using IIF in query for Linked Tables SQL Server 2012

Thanks for the confirmation.
It is what I expected, and I incidently won the bet.
If you get to visite the topside, I owe you half the winnings (a cup of coffee).

__________________
Were you lucky enough to get an answer? Please mark your question as [SOLVED] The original poster can go to Thread Tools to mark it as Solved.

Quotation Thomas Jefferson: "Peace is that brief glorious moment in history when everybody stands around reloading."

There are 2 Kinds of Countries on this Planet
1. Those that use the Metric System
2. Those that had a man walk on the moon

Denver, Colorado - The "Mile High City" - non-metric!
Rx_ is offline   Reply With Quote
Reply

Tags
iif , native client 11.0 , odbc , query , t-sql 2008r2

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Access 2010 front end to SQL Server 2012 Operation must use an updateable query SandyShin Queries 4 05-09-2013 10:35 AM
Upsizing to SQL Server 2012 Express - Localdb? Peter Reid SQL Server 2 01-11-2013 06:56 AM
Question Access 2010 on Windows Server 2012 Johnrg General 1 10-23-2012 12:47 PM
MakeTable query for all linked tables (SQL Server) Rx_ Modules & VBA 1 07-31-2012 09:49 AM
[SOLVED] Unmatched Query Error using Linked SQL Server Tables skalb Queries 0 07-25-2004 03:59 PM




All times are GMT -8. The time now is 02:47 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World