July 5, 2022 at 7:10 pm
(1) 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).
(2) Table "emp_training" has (emp_id int, training_id int, status char(1)) where status = F(fail)/P(pass). Each employee (emp_id) may pass a class only once but may fail it multiple times (or 0 or 1 time, of course). Write all necessary DDL to create a unique index -- with both Fs and Ps in it -- to enforce only 0/1 P but 0+ F(s). You may assume the table has a unique column "id" with the IDENTITY() property. (This is more advanced than the other qs.)
(3) Flip a bit column from 0 to 1 or vice versa without conditional logic. Flip a numeric not-bit column likewise from a value of 0 to 1 or 1 to 0.
(4) More advanced question: When should you use @@IDENTITY in preference to SCOPE_IDENTITY()?
NOTE: Some people have already answered. If you want to do these yourself first, wait to read more comments below until you have your own answers.
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 7:54 pm
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;
--(3)
declare @b bit=1;
declare @i int=0;
select 1-@b
select 1-@i
--(4)
Never
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
July 5, 2022 at 8:49 pm
1
SELECT t.*
FROM MyTable 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.mon
ORDER BY t.yyyy, M.N
OR
SELECT t.*
FROM MyTable t
ORDER BY t.yyyy, CONVERT(date, '01 ' + t.mon + ' 2022')
2
CREATE UNIQUE INDEX UX_emp_training_1 ON emp_training(emp_id, training_id)
WHERE status = 'P'
3
select ~col1
from MyTable
4 If you want the value of the identity column after a trigger fired
July 5, 2022 at 8:56 pm
1
SELECT t.*
FROM MyTable 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.mon
ORDER BY t.yyyy, M.N
OR
SELECT t.*
FROM MyTable t
ORDER BY t.yyyy, CONVERT(date, '01 ' + t.mon + ' 2022')2
CREATE UNIQUE INDEX UX_emp_training_1 ON emp_training(emp_id, training_id) WHERE status = 'P'3
select ~col1
from MyTable4 If you want the value of the identity column after a trigger fired
(1) Good, workable, but it could be simpler
(2) D'OH, loophole I overlooked, nicely done! Try creating one index with both P and Fs in it.
(3) Correct for bit! Other numerics need another approach.
(4) Excellent! (And note that, unless you have system triggers on the tables, you can control which trigger fires last so you can control which @@IDENTITY value comes last from the trigger(s) fired.)
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 9:29 pm
Jonathan AC Roberts wrote:1
SELECT t.*
FROM MyTable 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.mon
ORDER BY t.yyyy, M.N
OR
SELECT t.*
FROM MyTable t
ORDER BY t.yyyy, CONVERT(date, '01 ' + t.mon + ' 2022')2
CREATE UNIQUE INDEX UX_emp_training_1 ON emp_training(emp_id, training_id) WHERE status = 'P'3
select ~col1
from MyTable4 If you want the value of the identity column after a trigger fired
(1) Good, workable, but it could be simpler
(2) D'OH, loophole I overlooked, nicely done! Try creating one index with both P and Fs in it.
(3) Correct for bit! Other numerics need another approach.
(4) Excellent! (And note that, unless you have system triggers on the tables, you can control which trigger fires last so you can control which @@IDENTITY value comes last from the trigger(s) fired.)
(1)
SELECT t.*
FROM MyTable t
ORDER BY CONVERT(date, t.mon + ' 1, ' + CONVERT(varchar, t.yyyy))
(3) That is the correct logic and result for bitwise "not" when the number is in 2's compliment (e.g. smallint, int, bigint). Does it give the result you expect for tinyint?
July 5, 2022 at 9:40 pm
ScottPletcher wrote:Jonathan AC Roberts wrote:1
SELECT t.*
FROM MyTable 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.mon
ORDER BY t.yyyy, M.N
OR
SELECT t.*
FROM MyTable t
ORDER BY t.yyyy, CONVERT(date, '01 ' + t.mon + ' 2022')2
CREATE UNIQUE INDEX UX_emp_training_1 ON emp_training(emp_id, training_id) WHERE status = 'P'3
select ~col1
from MyTable4 If you want the value of the identity column after a trigger fired
(1) Good, workable, but it could be simpler
(2) D'OH, loophole I overlooked, nicely done! Try creating one index with both P and Fs in it.
(3) Correct for bit! Other numerics need another approach.
(4) Excellent! (And note that, unless you have system triggers on the tables, you can control which trigger fires last so you can control which @@IDENTITY value comes last from the trigger(s) fired.)
(1) I think this is quite simple
SELECT t.*
FROM MyTable t
ORDER BY t.yyyy, CONVERT(date, '01 ' + t.mon + ' 2022')Maybe this is a tiny bit simpler?
SELECT t.*
FROM MyTable t
ORDER BY t.yyyy, CONVERT(date, t.mon + ' 1, 2022')(3) That is the correct logic and result for bitwise "not" when the number is in 2's compliment (e.g. smallint, int, bigint). Does it give the result you expect for tinyint?
(1) or maybe this?:
ORDER BY t.yyyy, CHARINDEX(t.month, 'JanFebMarAprMayJunJulAugSepOctNovDec')
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 9:41 pm
Jonathan AC Roberts wrote:ScottPletcher wrote:Jonathan AC Roberts wrote:1
SELECT t.*
FROM MyTable 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.mon
ORDER BY t.yyyy, M.N
OR
SELECT t.*
FROM MyTable t
ORDER BY t.yyyy, CONVERT(date, '01 ' + t.mon + ' 2022')2
CREATE UNIQUE INDEX UX_emp_training_1 ON emp_training(emp_id, training_id) WHERE status = 'P'3
select ~col1
from MyTable4 If you want the value of the identity column after a trigger fired
(1) Good, workable, but it could be simpler
(2) D'OH, loophole I overlooked, nicely done! Try creating one index with both P and Fs in it.
(3) Correct for bit! Other numerics need another approach.
(4) Excellent! (And note that, unless you have system triggers on the tables, you can control which trigger fires last so you can control which @@IDENTITY value comes last from the trigger(s) fired.)
(1) I think this is quite simple
SELECT t.*
FROM MyTable t
ORDER BY t.yyyy, CONVERT(date, '01 ' + t.mon + ' 2022')Maybe this is a tiny bit simpler?
SELECT t.*
FROM MyTable t
ORDER BY t.yyyy, CONVERT(date, t.mon + ' 1, 2022')(3) That is the correct logic and result for bitwise "not" when the number is in 2's compliment (e.g. smallint, int, bigint). Does it give the result you expect for tinyint?
(1) or maybe this?:
ORDER BY t.yyyy, CHARINDEX(t.month, 'JanFebMarAprMayJunJulAugSepOctNovDec')
(3) Tinyint is probably ok, since it's unsigned, but int definitely does not 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 9:52 pm
ScottPletcher wrote:Jonathan AC Roberts wrote:ScottPletcher wrote:Jonathan AC Roberts wrote:1
SELECT t.*
FROM MyTable 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.mon
ORDER BY t.yyyy, M.N
OR
SELECT t.*
FROM MyTable t
ORDER BY t.yyyy, CONVERT(date, '01 ' + t.mon + ' 2022')2
CREATE UNIQUE INDEX UX_emp_training_1 ON emp_training(emp_id, training_id) WHERE status = 'P'3
select ~col1
from MyTable4 If you want the value of the identity column after a trigger fired
(1) Good, workable, but it could be simpler
(2) D'OH, loophole I overlooked, nicely done! Try creating one index with both P and Fs in it.
(3) Correct for bit! Other numerics need another approach.
(4) Excellent! (And note that, unless you have system triggers on the tables, you can control which trigger fires last so you can control which @@IDENTITY value comes last from the trigger(s) fired.)
(1) I think this is quite simple
SELECT t.*
FROM MyTable t
ORDER BY t.yyyy, CONVERT(date, '01 ' + t.mon + ' 2022')Maybe this is a tiny bit simpler?
SELECT t.*
FROM MyTable t
ORDER BY t.yyyy, CONVERT(date, t.mon + ' 1, 2022')(3) That is the correct logic and result for bitwise "not" when the number is in 2's compliment (e.g. smallint, int, bigint). Does it give the result you expect for tinyint?
(1) or maybe this?:
ORDER BY t.yyyy, CHARINDEX(t.month, 'JanFebMarAprMayJunJulAugSepOctNovDec')
(3) Tinyint is probably ok, since it's unsigned, but int definitely does not work.
(3) It does give the correct result, numerics are stored as Two's complement.
For example, the smallint number -1 is stored in binary as 1111111111111111 so if you flip the bits you get 0000000000000000.
What result would you expect?
Perhaps you could give an example of what you expect e.g. for the smallint number 8192?
July 5, 2022 at 9:55 pm
The expectation, as in the OP, is that 0 becomes 1 and 1 becomes 0. I didn't really mention -1; typically values are 0/1 for no/yes.
DECLARE @int int
DECLARE @smallint smallint
SET @int = 0
SET @int = ~@int
SELECT @int AS int_not_zero
SET @int = 1
SET @int = ~@int
SELECT @int AS int_not_one
SET @smallint = 0
SET @smallint = ~@smallint
SELECT @smallint AS smallint_not_zero
SET @smallint = 1
SET @smallint = ~@smallint
SELECT @smallint AS smallint_not_one
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 10:03 pm
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 ^ 1
July 5, 2022 at 10:25 pm
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 ^ 1
My intention was for:
ABS(column - 1)
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 10:32 pm
What are the datatypes for the two columns in Question #1?
--Jeff Moden
Change is inevitable... Change for the better is not.
July 5, 2022 at 11:03 pm
July 5, 2022 at 11:09 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 ^ 1
Should be faster
I can't imagine you could measure the difference between the two.
^1 would definitely not work with float, so technically it doesn't cover all "not-bit numeric" 😀
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 15 posts - 1 through 15 (of 39 total)
You must be logged in to reply to this topic. Login to reply