Viewing 15 posts - 5,086 through 5,100 (of 7,613 total)
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
DOH, just saw this:
User_Login_test is a VIEW
So obviously clustering would not apply to it, but to the underlying tables.
It's still fundamentally true that identity is usually NOT the best clustering...
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:36 pm
herladygeekedness (5/13/2015)
Jeff Moden (5/9/2015)
ScottPletcher (5/8/2015)
Who comes up with such ridiculous, useless qs? And why on earth would a dba want to waste value time and brain space memorizing such trivialities??
Heh.......
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:31 pm
1) The LEFT OUTER JOIN is functioning like an INNER JOIN because of the WHERE condition on a column from that table. For better performance, move the WHERE condition...
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 1:55 pm
If you mean cast it to a time / datetime, you can't safely do that, because the run_duration in sysjobhistory can meet or exceed 24 hours.
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 1:39 pm
The names should not be in that table, only the IDs.
Also, you need some type of amount code so that amounts for that visit can be distinguished from one another.
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 10:49 am
Indexing is an interesting q.
In theory I guess best would be an index on:
( dealer, article ) include ( price ) or even
( dealer, article, price )
but 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".
May 13, 2015 at 10:47 am
Viewing 15 posts - 5,086 through 5,100 (of 7,613 total)