Viewing 15 posts - 1,246 through 1,260 (of 7,620 total)
First, get rid of the functions against table columns in the WHERE clause. Those are always a terrible idea for performance. (Code reformatted but so that it can be read...
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".
September 30, 2021 at 4:44 pm
Jeffrey Williams wrote:You should also not use SELECT to set the variable - use SET instead:
WHY? What do you base that recommendation on?
I remember someone testing and saying that it...
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".
September 30, 2021 at 2:05 am
About the>= vs =, yes, you're correct, it can be = in this example. I didn't mention it, but often we rerun a few days in the back, so we...
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".
September 30, 2021 at 2:03 am
Just an example of concatenation when using EXEC():
DECLARE @sql1 varchar(1000)
DECLARE @sql2 varchar(1000)
SET @sql1 = 'SELECT GETDATE() AS today'
SET @sql2 = 'SELECT COUNT(*) AS object_count FROM sys.objects'
EXEC(@sql1 + ';' + @sql2)
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".
September 29, 2021 at 7:24 pm
Oh, sorry, I didn't look closely enough, I thought it was EXEC() and not EXEC sys.sp_executesql.
For sp_executesql, you must combine code before running the proc.
DECLARE @sql NVARCHAR(4000)
,@SQL1 NVARCHAR(4000);
SELECT @sql =...
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".
September 29, 2021 at 7:22 pm
You left off the parens (and something to separate the new SELECT from the old command, either a space or a semi-colon):
EXEC sp_executesql (@SQL + ';' + @SQL1)
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".
September 29, 2021 at 7:09 pm
To determine proper clustering (PKs) for the tables, we'd need to know more about how the data is most often processed, by statistics type or entityid or timeperiod range.
However, as...
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".
September 29, 2021 at 3:40 pm
Create the temp table before calling the proc. Have a flag that is passed in to "tell" the proc to load its results into the temp table, or you could...
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".
September 28, 2021 at 3:34 am
sql playing dumb on this case
if your software can be set to do other types of joins/cross applies or even to, as you said, generate the isnull (which works...
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".
September 27, 2021 at 3:20 pm
Not one of these are valid date/datetime values:
'Sep 01', '2001-Q3', '2001-Q2', 'Sep 01', '2001-Q3', '2001-Q2'
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".
September 26, 2021 at 10:22 pm
Whoever's teaching your class is not particularly expert at db design. Storing NNN-NNN-NNN is just plain wrong; it's a waste of space to store fixed dashes in a column.
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".
September 26, 2021 at 10:17 pm
Gotta say this, since it's a pet peeve of mine = Using comments in place of accurate names.
Viz, why?:
FROM Tally t --Month count sequence starting at 0
rather than:
FROM Tally month_count...
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".
September 24, 2021 at 7:43 pm
Hi Scott,
like I mentioned earlier, the WHERE clause is added afterwards in my application (so the user can choose to see archived corporations only, active corporations only or both)....
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".
September 24, 2021 at 7:39 pm
Hi Scott, this is oversimplified but it reproduces the problem. The question becomes, what is missing so we do not have to wrap EA.IsArchived in a ISNULL?
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".
September 24, 2021 at 6:32 pm
Yeah, you can test for NULLs in columns and/or GROUPING_ID to determine: which rows are total rows and which rows are detail rows; to exclude total rows you don't want;...
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".
September 24, 2021 at 6:09 pm
Viewing 15 posts - 1,246 through 1,260 (of 7,620 total)