Viewing 15 posts - 766 through 780 (of 7,619 total)
As I see it, it's not the ROW_NUMBER itself that causes inefficiency (SQL is remarkably efficient at providing ROW_NUMBER), it's the multiple uses of it and thus multiple scans of...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
July 5, 2022 at 11:37 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...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
July 5, 2022 at 11:21 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 =...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
July 5, 2022 at 11:18 pm
I strongly suspect that the specific proc nor statement is available, since the "standard" dm_* views don't include that data.
If you could post the proc itself, I will look and...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
July 5, 2022 at 11:17 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 =...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
July 5, 2022 at 11:09 pm
I'm trying to think of a more efficient way, but for now this should at least function correctly:
;WITH cte_add_row_num AS (
SELECT *, ROW_NUMBER()...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
July 5, 2022 at 11:07 pm
What are the datatypes for the two columns in Question #1?
smallint and char(3) (and I have added the data types to the original post).
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
July 5, 2022 at 10:35 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...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
July 5, 2022 at 10:25 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...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
July 5, 2022 at 9:55 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)
...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
July 5, 2022 at 9:41 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)
...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
July 5, 2022 at 9:40 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)
...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
July 5, 2022 at 8:56 pm
The table would need to contain, and be keyed on, workerID, year and Course_Code. This should be a unique key if that is your rule (that is, no single worker...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
July 5, 2022 at 7:06 pm
Consider, for example, with design #1, a unique constraint will insure that the same course is not entered twice for the same year.
But for design #2, you'd have to insure...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
July 5, 2022 at 2:02 pm
You should only ever use the first method, period, no matter what your current requirements are.
The second approach would be a maintenance and query nightmare.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
July 5, 2022 at 1:14 pm
Viewing 15 posts - 766 through 780 (of 7,619 total)