July 5, 2022 at 11:13 pm
Jonathan AC Roberts wrote:ok, that's not what I thought you meant. I thought you meant flip all the bits.
This works for 0 and 1:
DECLARE @smallint smallint = 0
select @smallint ^ 1My intention was for:
ABS(column - 1)
Heh... dammit... that was going to be my answer. It basically does the same thing as subtracting 1 from a BIT . It'll play correctly even if the column is a TINYINT because the -1 is an INT by default and that elevates the TINYINT to an INT before the subtraction happens, just like it does for a BIT. BIT works a little differently in that any non-zero answer becomes a 1 and so doesn't require the ABS() function. If you want to bullet proof the code against someone changing the column to something other than an INT, the ABS() on the BIT column will still work, as well.
If I were in an interview, that would be added on to the answer coded answer.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 5, 2022 at 11:18 pm
ScottPletcher wrote:Jonathan AC Roberts wrote:ok, that's not what I thought you meant. I thought you meant flip all the bits.
This works for 0 and 1:
DECLARE @smallint smallint = 0
select @smallint ^ 1My intention was for:
ABS(column - 1)
Heh... dammit... that was going to be my answer. It basically does the same thing as subtracting 1 from a BIT . It'll play correctly even if the column is a TINYINT because the -1 is an INT by default and that elevates the TINYINT to an INT before the subtraction happens, just like it does for a BIT. BIT works a little differently in that any non-zero answer becomes a 1 and so doesn't require the ABS() function. If you want to bullet proof the code against someone changing the column to something other than an INT, the ABS() on the BIT column will still work, as well.
If I were in an interview, that would be added on to the answer coded answer.
Same for bit for you? I too used ~ for bit, since presumably it's a tiny bit more efficient?? Then again, the difference is probably not even measurable.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
July 5, 2022 at 11:21 pm
For (4), some/many of you may be wondering, "Why on earth would you ever have a trigger returning an identity value?"
But, yes, there is actually a good reason for doing that.
Specifically, when you want to COMPRESS a column(s) in a table transparently to other users of the table. Moreover, you want existing code -- or even new code -- to treat the column(s) as [n]varchar(nnnn) or [n]varchar(max) (rather than as varbinary(max)). That is, existing/new code does not care whether a column(s) is(are) compressed or not (thus you could also remove COMPRESSion transparentlly).
To allow multiple INSERTs at once, and get back identities, you'd have to use another mechanism. But if the original code was using SCOPE_IDENTITY(), then it was concerned with only a single identity value anyway, and @@IDENTITY will work.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
July 5, 2022 at 11:24 pm
ScottPletcher wrote:Jonathan AC Roberts wrote:ok, that's not what I thought you meant. I thought you meant flip all the bits.
This works for 0 and 1:
DECLARE @smallint smallint = 0
select @smallint ^ 1My intention was for:
ABS(column - 1)
I think
column ^ 1Should be faster or
1 - columnmore brief
Test it an see. 10 Million rows should be enough. 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
July 5, 2022 at 11:35 pm
Jonathan AC Roberts wrote:ScottPletcher wrote:Jonathan AC Roberts wrote:ok, that's not what I thought you meant. I thought you meant flip all the bits.
This works for 0 and 1:
DECLARE @smallint smallint = 0
select @smallint ^ 1My intention was for:
ABS(column - 1)
I think
column ^ 1Should be faster or
1 - columnmore brief
Test it an see. 10 Million rows should be enough. 😀
And I know just the function to use to test it:
drop table if exists #w
drop table if exists #x
drop table if exists #y
drop table if exists #z
set statistics io,time on
go
select abs(n-1) a
into #w
from dbo.fnTally(1,10000000)
go
select 1-n a
into #x
from dbo.fnTally(1,10000000)
go
select n^1 a
into #y
from dbo.fnTally(1,10000000)
go
select n
into #z
from dbo.fnTally(1,10000000)
I'd say column ^ 1 wins by the smallest whisker.
July 5, 2022 at 11:49 pm
I'm curious to see if anyone takes a shot at (2). Maybe it's TL;DR :smile:.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
July 6, 2022 at 12:01 am
Here's 1, 3, and 4
--(1)
select v.sort_mo, input_str.mo
from (values ('Jan'),('Feb'),('Mar')) input_str(mo)
cross apply (values (parse(concat(input_str.mo, '-01-1900') as date))) v(sort_mo)
order by v.sort_mo;
I was going to test this for performance but then noticed that it's not a complete answer 😮
--Jeff Moden
Change is inevitable... Change for the better is not.
July 6, 2022 at 12:42 am
For Question #1, Scott's method is the fastest. Don't take my word for it, though. Here's the test table...
--===== If the test table exists, drop it just to make reruns in SSMS easier.
DROP TABLE IF EXISTS #TestTable;
GO
--===== CREATE and Populate the test table on the fly.
SELECT RowNum = t.N --Just for the sake of test reference to see what's going on if we need to.
,YYYY = CONVERT(SMALLINT,DATEPART(yy,rd.SomeDate))
,MMM = CONVERT(CHAR(3),DATENAME(mm,rd.SomeDate))
INTO #TestTable
FROM dbo.fnTally(1,1000000)t
CROSS APPLY (VALUES (DATEADD(dd,ABS(CHECKSUM(NEWID())%DATEDIFF(dd,'1980','2040')),'1980')))rd(SomeDate)
;
--===== Let's see what the table looks like
SELECT TOP 1000 *
FROM #TestTable
ORDER BY RowNum
;
GO
Here's the test code. The ONLY things I changed in anyone's code was to put a test wrapper around the code, change the table name to the test table about, change the column names (if needed) to match the test table, and redirect the output to throw away variable to remove display times.
--=====================================================================================================================
PRINT REPLICATE('*',119);
RAISERROR('===== Scott''s Code) =====',0,0) WITH NOWAIT;
DBCC FREEPROCCACHE;
CHECKPOINT;
GO
DECLARE @YYYY SMALLINT
,@MMM CHAR(3)
;
SET STATISTICS TIME ON;
SELECT @YYYY = YYYY
,@MMM = MMM
FROM #TestTable
ORDER BY YYYY, CHARINDEX(MMM,'JanFebMarAprMayJunJulAugSepOctNovDec')
SET STATISTICS TIME OFF;
GO 3
--=====================================================================================================================
PRINT REPLICATE('*',119);
RAISERROR('===== Jonathan''s first answer =====',0,0) WITH NOWAIT;
DBCC FREEPROCCACHE;
CHECKPOINT;
GO
DECLARE @YYYY SMALLINT
,@MMM CHAR(3)
;
SET STATISTICS TIME ON;
SELECT @YYYY = YYYY
,@MMM = MMM
FROM #TestTable t
CROSS APPLY (VALUES ('Jan', 1),('Feb', 2),('Mar', 3),('Apr', 4),('May', 5),('Jun', 6),('Jul', 7),('Aug', 8),('Sep', 9),('Oct', 10),('Nov', 11),('Dec', 12)) M(Name,N)
WHERE M.Name = t.MMM
ORDER BY YYYY, M.N
SET STATISTICS TIME OFF;
GO 3
--=====================================================================================================================
PRINT REPLICATE('*',119);
RAISERROR('===== Jonathan''s second answer =====',0,0) WITH NOWAIT;
DBCC FREEPROCCACHE;
CHECKPOINT;
GO
DECLARE @YYYY SMALLINT
,@MMM CHAR(3)
;
SET STATISTICS TIME ON;
SELECT @YYYY = YYYY
,@MMM = MMM
FROM #TestTable t
ORDER BY t.YYYY, CONVERT(date, '01 ' + t.MMM + ' 2022')
SET STATISTICS TIME OFF;
GO 3
--=====================================================================================================================
PRINT REPLICATE('*',119);
RAISERROR('===== Jonathan''s third answer =====',0,0) WITH NOWAIT;
DBCC FREEPROCCACHE;
CHECKPOINT;
GO
DECLARE @YYYY SMALLINT
,@MMM CHAR(3)
;
SET STATISTICS TIME ON;
SELECT @YYYY = YYYY
,@MMM = MMM
FROM #TestTable t
ORDER BY CONVERT(date, t.MMM + ' 1, ' + CONVERT(varchar, t.YYYY))
SET STATISTICS TIME OFF;
GO 3
... and, here are the results...
***********************************************************************************************************************
===== Scott's Code) =====
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Beginning execution loop
SQL Server Execution Times:
CPU time = 1390 ms, elapsed time = 440 ms.
SQL Server Execution Times:
CPU time = 1468 ms, elapsed time = 417 ms.
SQL Server Execution Times:
CPU time = 1249 ms, elapsed time = 413 ms.
Batch execution completed 3 times.
***********************************************************************************************************************
===== Jonathan's first answer =====
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Beginning execution loop
SQL Server Execution Times:
CPU time = 1407 ms, elapsed time = 444 ms.
SQL Server Execution Times:
CPU time = 1547 ms, elapsed time = 458 ms.
SQL Server Execution Times:
CPU time = 1374 ms, elapsed time = 428 ms.
Batch execution completed 3 times.
***********************************************************************************************************************
===== Jonathan's second answer =====
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Beginning execution loop
SQL Server Execution Times:
CPU time = 1937 ms, elapsed time = 586 ms.
SQL Server Execution Times:
CPU time = 2001 ms, elapsed time = 599 ms.
SQL Server Execution Times:
CPU time = 2062 ms, elapsed time = 623 ms.
Batch execution completed 3 times.
***********************************************************************************************************************
===== Jonathan's third answer =====
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Beginning execution loop
SQL Server Execution Times:
CPU time = 1938 ms, elapsed time = 575 ms.
SQL Server Execution Times:
CPU time = 1891 ms, elapsed time = 575 ms.
SQL Server Execution Times:
CPU time = 1781 ms, elapsed time = 550 ms.
Batch execution completed 3 times.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 6, 2022 at 1:21 am
For Question #1, Scott's method is the fastest. Don't take my word for it, though.
These are the results from my machine
***********************************************************************************************************************
===== Scott's Code) =====
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Beginning execution loop
SQL Server Execution Times:
CPU time = 3858 ms, elapsed time = 19511 ms.
SQL Server Execution Times:
CPU time = 2703 ms, elapsed time = 899 ms.
SQL Server Execution Times:
CPU time = 7829 ms, elapsed time = 27592 ms.
Batch execution completed 3 times.
***********************************************************************************************************************
===== Jonathan's first answer =====
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Beginning execution loop
SQL Server Execution Times:
CPU time = 734 ms, elapsed time = 262 ms.
SQL Server Execution Times:
CPU time = 610 ms, elapsed time = 239 ms.
SQL Server Execution Times:
CPU time = 703 ms, elapsed time = 240 ms.
Batch execution completed 3 times.
***********************************************************************************************************************
===== Jonathan's second answer =====
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Beginning execution loop
SQL Server Execution Times:
CPU time = 2203 ms, elapsed time = 1955 ms.
SQL Server Execution Times:
CPU time = 2000 ms, elapsed time = 714 ms.
SQL Server Execution Times:
CPU time = 1968 ms, elapsed time = 708 ms.
Batch execution completed 3 times.
***********************************************************************************************************************
===== Jonathan's third answer =====
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Beginning execution loop
SQL Server Execution Times:
CPU time = 2064 ms, elapsed time = 1844 ms.
SQL Server Execution Times:
CPU time = 1969 ms, elapsed time = 641 ms.
SQL Server Execution Times:
CPU time = 1780 ms, elapsed time = 637 ms.
Batch execution completed 3 times.
Completion time: 2022-07-06T02:19:29.3203882+01:00
July 6, 2022 at 1:48 am
Steve Collins wrote:Here's 1, 3, and 4
I was going to test this for performance but then noticed that it's not a complete answer 😮
The data types were added. It says: "Sort rows with year column (smallint) and month column (char(3)) == Jan, Feb, Mar, etc. == correctly, without using CASE or other conditional expressions (the simpler the better)."
So it was missing the 'year' column
select cast(year(v.sort_mo) as smallint) year, input_str.mo
from (values ('Jan'),('Feb'),('Mar')) input_str(mo)
cross apply (values (parse(concat(input_str.mo, '-01-1900') as date))) v(sort_mo)
order by v.sort_mo;
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
July 6, 2022 at 2:02 am
Oh my! That's a crazy amount of difference, Jonathan... even from the runs for the same code. What else is running on your machine?
I'm running SQL Server 2017 Developer's Edition
The laptop is an Alienware 17 R5 and has a 6 core/hypterthreaded to 12, 8th Generation I7 CPU and it is NOT "VM'd".
(Intel(R) Core(TM) i7-8750H CPU @ 2.20GHz 2.21 GH)
32GB Ram
2TB NVME SSD
And, I love the performance for your first answer. That's nasty fast and that's why I'm questioning what else is running on your machine.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 6, 2022 at 2:06 am
Jeff Moden wrote:Steve Collins wrote:Here's 1, 3, and 4
I was going to test this for performance but then noticed that it's not a complete answer 😮
The data types were added. It says: "Sort rows with year column (smallint) and month column (char(3)) == Jan, Feb, Mar, etc. == correctly, without using CASE or other conditional expressions (the simpler the better)."
So it was missing the 'year' column
select cast(year(v.sort_mo) as smallint) year, input_str.mo
from (values ('Jan'),('Feb'),('Mar')) input_str(mo)
cross apply (values (parse(concat(input_str.mo, '-01-1900') as date))) v(sort_mo)
order by v.sort_mo;
It's still missing the "etc." part of the months. You only have 3 of the months listed.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 6, 2022 at 2:08 am
Jeff Moden wrote:For Question #1, Scott's method is the fastest. Don't take my word for it, though.
These are the results from my machine
***********************************************************************************************************************
===== Scott's Code) =====
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Beginning execution loop
SQL Server Execution Times:
CPU time = 3858 ms, elapsed time = 19511 ms.
SQL Server Execution Times:
CPU time = 2703 ms, elapsed time = 899 ms.
SQL Server Execution Times:
CPU time = 7829 ms, elapsed time = 27592 ms.
Batch execution completed 3 times.
***********************************************************************************************************************
===== Jonathan's first answer =====
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Beginning execution loop
SQL Server Execution Times:
CPU time = 734 ms, elapsed time = 262 ms.
SQL Server Execution Times:
CPU time = 610 ms, elapsed time = 239 ms.
SQL Server Execution Times:
CPU time = 703 ms, elapsed time = 240 ms.
Batch execution completed 3 times.
***********************************************************************************************************************
===== Jonathan's second answer =====
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Beginning execution loop
SQL Server Execution Times:
CPU time = 2203 ms, elapsed time = 1955 ms.
SQL Server Execution Times:
CPU time = 2000 ms, elapsed time = 714 ms.
SQL Server Execution Times:
CPU time = 1968 ms, elapsed time = 708 ms.
Batch execution completed 3 times.
***********************************************************************************************************************
===== Jonathan's third answer =====
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Beginning execution loop
SQL Server Execution Times:
CPU time = 2064 ms, elapsed time = 1844 ms.
SQL Server Execution Times:
CPU time = 1969 ms, elapsed time = 641 ms.
SQL Server Execution Times:
CPU time = 1780 ms, elapsed time = 637 ms.
Batch execution completed 3 times.
Completion time: 2022-07-06T02:19:29.3203882+01:00
Something's gone wrong there. There's no way CHARINDEX is that much slower than having to convert to a date.
My times are:
***********************************************************************************************************************
===== Scott's Code) =====
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Beginning execution loop
SQL Server Execution Times:
CPU time = 3656 ms, elapsed time = 883 ms.
SQL Server Execution Times:
CPU time = 3502 ms, elapsed time = 898 ms.
SQL Server Execution Times:
CPU time = 3484 ms, elapsed time = 816 ms.
Batch execution completed 3 times.
***********************************************************************************************************************
===== Jonathan's first answer =====
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Beginning execution loop
SQL Server Execution Times:
CPU time = 3422 ms, elapsed time = 909 ms.
SQL Server Execution Times:
CPU time = 3873 ms, elapsed time = 899 ms.
SQL Server Execution Times:
CPU time = 3593 ms, elapsed time = 866 ms.
Batch execution completed 3 times.
***********************************************************************************************************************
===== Jonathan's second answer =====
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Beginning execution loop
SQL Server Execution Times:
CPU time = 4217 ms, elapsed time = 977 ms.
SQL Server Execution Times:
CPU time = 4656 ms, elapsed time = 1045 ms.
SQL Server Execution Times:
CPU time = 4735 ms, elapsed time = 1152 ms.
Batch execution completed 3 times.
***********************************************************************************************************************
===== Jonathan's third answer =====
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Beginning execution loop
SQL Server Execution Times:
CPU time = 4859 ms, elapsed time = 1135 ms.
SQL Server Execution Times:
CPU time = 4250 ms, elapsed time = 951 ms.
SQL Server Execution Times:
CPU time = 4234 ms, elapsed time = 1003 ms.
Batch execution completed 3 times.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
July 6, 2022 at 2:13 am
There's something crazy going on with Jonathan's machine, Scott. Look at the results of your code in his run... it's all over the place.
I'll also say that on both my runs and the runs Scott made, Jonathan's first attempt is second only by a handful of milliseconds.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 6, 2022 at 2:21 am
select cast(year(v.sort_mo) as smallint), input_str.mo
from (values ('Jan'),('Feb'),('Mar'),
('Apr'),('May'),('Jun'),
('Jul'),('Aug'),('Sep'),
('Oct'),('Nov'),('Dec')) input_str(mo)
cross apply (values (parse(concat(input_str.mo, '-01-1900') as date))) v(sort_mo)
order by v.sort_mo;
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
Viewing 15 posts - 16 through 30 (of 39 total)
You must be logged in to reply to this topic. Login to reply