Viewing 15 posts - 5,086 through 5,100 (of 7,619 total)
I don't like table overhead when it can be avoided by simple mathematical calcs. Calendar tables have their uses, but to me they can sometimes become the proverbial hammer...
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".
May 19, 2015 at 3:11 pm
You should only rebuild or reorganize an index when it is fragmented enough to be significant. For some tables, this might be every day. For others, only every...
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".
May 19, 2015 at 9:36 am
Presumably some number of UDFs would get updated over time, particularly as new techniques are learned. I believe, for example, that the splitter function has gone thru several iterations,...
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".
May 19, 2015 at 8:28 am
Jason A. Long (5/15/2015)
Just tetested with this...
DECLARE
@BegDate DATE...
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".
May 18, 2015 at 9:56 am
Interesting points. (Although I'd prefer to put the test conditions / "known case" conditions into a table, for the usual reasons of flexibility and maintainability.)
As an aside, the function...
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".
May 18, 2015 at 8:49 am
Jeff Moden (5/15/2015)
Jason A. Long (5/15/2015)
ScottPletcher (5/15/2015)
Never use BETWEEN on dates or datetimes;
Out of curiosity, why do you say this?
I could see it it you were talking about...
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".
May 18, 2015 at 8:26 am
Lynn Pettis (5/15/2015)
Jason A. Long (5/15/2015)
Both good points... Although I'd still put this in the "good idea not to do this" category as opposed to the "never do this" category...
Some...
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".
May 15, 2015 at 4:31 pm
Never use a function on a column if it can be avoided.
Never use between for date/datetimes, use >= and < instead.
SELECT @sql = 'SELECT *
FROM #Temp
WHERE DocDate >=...
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".
May 15, 2015 at 10:51 am
Never use BETWEEN on dates or datetimes; instead, use >= and < the next date/time value.
Also, for literal dates, always use format 'YYYYMMDD', which is 100% unambiguous. ...
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".
May 15, 2015 at 10:25 am
SELECT REPLACE(CONVERT(varchar(11), DATEADD(MONTH, month_adjustment, current_month), 106), ' ', '-') AS column_name
FROM (
SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0) AS current_month
) AS get_current_month
CROSS JOIN (
...
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".
May 15, 2015 at 10:17 am
That's a good point about the minimal logging.
But from at least SQL 2008 on, you can get minimal logging on the insert into the existing empty clustered index as well,...
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".
May 15, 2015 at 9:25 am
That's not surprising once you think through the process. Loading the table and then clustering it requires all the rows to be inserted twice.
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".
May 15, 2015 at 8:31 am
It's faster to create the clustered index first, because if necessary you can always explicitly sort the data being into cluster key order for the insert. Typically SQL will...
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".
May 14, 2015 at 10:24 am
Or much better, when entering literal dates and making date/datetime comparisons:
1) always use format YYYYMMDD, which is 100% under any/all SQL settings
2) use >= and < [the next day] rather...
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".
May 14, 2015 at 10:21 am
Before that you may want to try:
EXEC sp_refreshview
on that view.
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".
May 13, 2015 at 2:45 pm
Viewing 15 posts - 5,086 through 5,100 (of 7,619 total)