September 23, 2015 at 8:33 am
Alvin Ramard (9/23/2015)
Luis Cazares (9/23/2015)
Manic Star (9/23/2015)
patricklambin (9/23/2015)
Stewart "Arturius" Campbell (9/22/2015)
Interesting question, thanks SteveAnother of the functions copied from Excel.
I don't think so. These functions have been introduced only to make easier the translation Access towards SQL Server ( the future of Access seems not to be all roses ).
MS Access should have died a long long time ago.
MS Access has an audience which is different from the audience of SQL Server.
I agree with Luis. The problem is not Access. The problem is people/companies using Access for something it was not really intended to be used for.
Agreed. And it is so very, very widespread.
September 23, 2015 at 9:22 am
Really, really simple. Thanks, Steve!
September 23, 2015 at 1:04 pm
Ed Wagner (9/23/2015)
Alvin Ramard (9/23/2015)
Luis Cazares (9/23/2015)
Manic Star (9/23/2015)
patricklambin (9/23/2015)
Stewart "Arturius" Campbell (9/22/2015)
Interesting question, thanks SteveAnother of the functions copied from Excel.
I don't think so. These functions have been introduced only to make easier the translation Access towards SQL Server ( the future of Access seems not to be all roses ).
MS Access should have died a long long time ago.
MS Access has an audience which is different from the audience of SQL Server.
I agree with Luis. The problem is not Access. The problem is people/companies using Access for something it was not really intended to be used for.
Agreed. And it is so very, very widespread.
That doesn't mean its not a widespread repository of potential evil and frustration.
September 23, 2015 at 7:42 pm
Manic Star (9/23/2015)
patricklambin (9/23/2015)
Stewart "Arturius" Campbell (9/22/2015)
Interesting question, thanks SteveAnother of the functions copied from Excel.
I don't think so. These functions have been introduced only to make easier the translation Access towards SQL Server ( the future of Access seems not to be all roses ).
MS Access should have died a long long time ago.
Not until they steal and incorporate the good stuff that Access has. For example, the PIVOT function is ACCESS makes the PIVOT function in SQL Server look absolutely stupid.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 23, 2015 at 9:17 pm
Manic Star (9/23/2015)
patricklambin (9/23/2015)
Stewart "Arturius" Campbell (9/22/2015)
Interesting question, thanks SteveAnother of the functions copied from Excel.
I don't think so. These functions have been introduced only to make easier the translation Access towards SQL Server ( the future of Access seems not to be all roses ).
MS Access should have died a long long time ago.
Haha - yeah, tell that to this guy.
-- Itzik Ben-Gan 2001
September 23, 2015 at 9:34 pm
Ed Wagner (9/23/2015)
Alvin Ramard (9/23/2015)
Luis Cazares (9/23/2015)
Manic Star (9/23/2015)
patricklambin (9/23/2015)
Stewart "Arturius" Campbell (9/22/2015)
Interesting question, thanks SteveAnother of the functions copied from Excel.
I don't think so. These functions have been introduced only to make easier the translation Access towards SQL Server ( the future of Access seems not to be all roses ).
MS Access should have died a long long time ago.
MS Access has an audience which is different from the audience of SQL Server.
I agree with Luis. The problem is not Access. The problem is people/companies using Access for something it was not really intended to be used for.
Agreed. And it is so very, very widespread.
I think that MS Access is an awesome Front-end application when you have a real RDBMS on the back-end. Say you need to put together a GUI for entering and retrieving data. I've used in the past as a shell that consumes parameters and passes them to a SQL Server Stored procedure. Beginning with Access 2010 you can publish an Access front-end in SharePoint. It's pretty easy and I could easily teach someone who is SQL savvy and has no MS access experience how to do this pretty quickly. Not bad for $100 (or however much it costs these days). I'm not talking about complex financial applications that end up in production but when you need a simple, no frills front-end to talk to your database Access is awesome.
The Microsoft Jet Engine DB, on the other hand, is poo.
-- Itzik Ben-Gan 2001
September 23, 2015 at 10:12 pm
I have personally found CHOOSE to be completely worthless. I have never seen an article or example of where CHOOSE was better than the alternatives. It's slow. I just don't get why it exists...
Look at this performance test. I test CHOOSE vs. the same functionality using VALUES, a CTE, a temp table variable and a CASE statement. One would think the temp table variable would be the slowest its not. CHOOSE always loses.
SET NOCOUNT ON;
-- Sample Data
IF OBJECT_ID('tempdb..#X') IS NOT NULL DROP TABLE #X;
GO
CREATE TABLE #X(SomeID int identity primary key, SomeNumber int NOT NULL);
INSERT #X(SomeNumber)
SELECT TOP (1000000) ABS(CHECKSUM(newid())%4)+1
FROM sys.all_columns a, sys.all_columns b;
GO
-- Plan Cache Cleanup
DBCC FREEPROCCACHE WITH NO_INFOMSGS
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS
-- The performance tests
DECLARE @st datetime = getdate(), @x varchar(10);
PRINT '=== Using CHOOSE ===';
SELECT @x = CHOOSE(SomeNumber, 'One','Two','Three','Four')
FROM #X;
PRINT DATEDIFF(MS,@st,getdate());
GO 3
DECLARE @st datetime = getdate(), @x varchar(10);
PRINT '=== Using VALUES ==='
SELECT @x = X2.t --@x = v
FROM #X X
CROSS APPLY (VALUES (1,'One'),(2,'Two'),(3,'Three'),(4,'Four')) X2(n,t)
WHERE X.SomeNumber = X2.n
PRINT DATEDIFF(MS,@st,getdate());
GO 3
DECLARE @st datetime = getdate(), @x varchar(10);
PRINT '=== Using CTE ===';
WITH X2(n,t) AS (SELECT 1,'One' UNION ALL SELECT 2,'Two' UNION ALL
SELECT 3,'Three' UNION ALL SELECT 4, 'Four')
SELECT @x = X2.t
FROM #X X
JOIN X2 ON X.SomeNumber = X2.n
PRINT DATEDIFF(MS,@st,getdate());
GO 3
DECLARE @st datetime = getdate(), @x varchar(10);
PRINT '=== Using temp variable ===';
DECLARE @choose TABLE (n tinyint, t varchar(10));
INSERT @choose VALUES (1,'One'),(2,'Two'),(3,'Three'),(4,'Four');
SELECT @x = X2.t
FROM #X X
JOIN @choose X2 ON X.SomeNumber = X2.n
PRINT DATEDIFF(MS,@st,getdate());
GO 3
DECLARE @st datetime = getdate(), @x varchar(10);
PRINT '=== Using CASE Statement ===';
DECLARE @choose TABLE (n tinyint, t varchar(10));
INSERT @choose VALUES (1,'One'),(2,'Two'),(3,'Three'),(4,'Four');
SELECT @x =
CASE SomeNumber
WHEN 1 THEN 'One' WHEN 2 THEN 'Two' WHEN 3 THEN 'Three' WHEN 4 THEN 'Four'
END
FROM #X X
PRINT DATEDIFF(MS,@st,getdate());
GO 3
Results:
Beginning execution loop
=== Using CHOOSE ===
186
=== Using CHOOSE ===
133
=== Using CHOOSE ===
143
Batch execution completed 3 times.
Beginning execution loop
=== Using VALUES ===
93
=== Using VALUES ===
100
=== Using VALUES ===
96
Batch execution completed 3 times.
Beginning execution loop
=== Using CTE ===
96
=== Using CTE ===
96
=== Using CTE ===
103
Batch execution completed 3 times.
Beginning execution loop
=== Using temp variable ===
103
=== Using temp variable ===
100
=== Using temp variable ===
100
Batch execution completed 3 times.
Beginning execution loop
=== Using CASE Statement ===
103
=== Using CASE Statement ===
106
=== Using CASE Statement ===
106
Batch execution completed 3 times.
-- Itzik Ben-Gan 2001
September 24, 2015 at 12:56 am
Easy one, thanks.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
September 24, 2015 at 1:02 am
Toreador (9/23/2015)
Guessed wrongly that it was zero-based.I've never used this function and have no intention of starting - it does nothing that cannot already be achieved by other means.
Does anyone know why this (and other similar Access-like functions) were introduced?
It can make the code look cleaner.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
September 24, 2015 at 1:04 am
Kristen-173977 (9/23/2015)
Stewart "Arturius" Campbell (9/23/2015)
I believe it was, inter alia, to make the transition from Access-based databases to SQL Server simpler.Hadn't spotted that CHOOSE was added in SQL2012 ...
... nor that IIF had been added too. Never understood why IIF was needed (as a function) rather than adding IF or CASE to the language, I have the most horrific nested IIF statements in Excel, with the occasional AND() and/or OR() functions thrown in too. Would have been much easier to write a proper, structured, logic statement instead of nested Logic Functions 🙁
I like IIF for replacing very simple CASE statements. Looks a bit better in my opinion and it's shorter.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
September 29, 2015 at 12:45 am
Never used Choose before, thanx.
Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant
September 29, 2015 at 6:11 am
Koen Verbeeck (9/24/2015)
I like IIF for replacing very simple CASE statements. Looks a bit better in my opinion and it's shorter.
I've always considered IIF (in Excel etc.) to be a bit Micky Mouse and that using a function for a logic test offended my programmers sense of "IF THEN ELSE" ... but just reading your comment perhaps I should recast that. ISNULL is doing something similar - choosing a parameter based on the logical outcome of a test - so perhaps I have been unfair to IIF() in the past 🙂
Viewing 12 posts - 16 through 26 (of 26 total)
You must be logged in to reply to this topic. Login to reply