Viewing 15 posts - 5,971 through 5,985 (of 7,613 total)
I must be missing something, because with the limited search criteria so far it looks straightforward. I can't tell whether they will pass in a single search id or...
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 10, 2014 at 3:25 pm
If you do many lookups by ManagerID, you'd likely be able to avoid messing with all this by clustering the table differently:
CREATE TABLE Employees (
ID 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 10, 2014 at 3:09 pm
You should also be able to use a CROSS APPLY to a "SELECT TOP (1) ..." to get the most recent row for any given user.
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 10, 2014 at 2:51 pm
I want to be a successful DBA someday and hopefully can write sql scripts without looking or using BOL. 🙂
I've been a SQL Server DBA for 13+ years and I...
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 10, 2014 at 11:33 am
SELECT
COALESCE(t1.Account, t2.Account) AS Account,
MAX(Date) AS Date
FROM (
SELECT Account, MAX(Date) AS Date
FROM dbo.table1
...
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 10, 2014 at 11:30 am
The single biggest factor in overall query performance is getting the best clustered index on each table (hint: it's rarely an identity column). Typically you'd start with a higher...
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 9, 2014 at 10:24 am
I'm virtually certain the tables are clustered improperly. That should be fixed before you do the partitioning. It's even possible you won't need to partition if you put...
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 7, 2014 at 1:05 pm
You can, and definitely should, avoid converting cStaging.cDate if you can.
--if "cDate" is a datetime column:
--instead of this:
--WHERE (CONVERT(DATE, cStaging.cDate) BETWEEN @startdate AND @endDate) AND (CONVERT(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".
July 7, 2014 at 12:40 pm
sqldriver (7/5/2014)
TheSQLGuru (7/1/2014)
create index ix_Split_1 on #Split_1 (NewParameter)Good call on the joining to a split-built derived table and how putting that into a temp table could make things faster. ...
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 6, 2014 at 12:52 am
By far the biggest payback is in correcting clustered indexes if the wrong key column(s) were chosen for it. And that alone often allows very large numbers of other...
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 2, 2014 at 8:32 pm
I also make the doc tables "live". That is, the code that created/initialized/set up a new db should use the "documentation" table to determine whether the table is static...
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 2, 2014 at 8:30 pm
TheSQLGuru (7/2/2014)
ScottPletcher (7/2/2014)
TheSQLGuru (7/1/2014)
Very few things in SQL Server land can/will short-circuit.
I think that's over-stated. Most optimizers will short-circuit when they safely can. Presumably later versions 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 2, 2014 at 8:27 pm
You did review the stats SQL Server provides on index usage and missing indexes before you made your changes, right?
Edit: What indexes you think SQL will need/use and what indexes...
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 2, 2014 at 4:06 pm
TheSQLGuru (7/1/2014)
Very few things in SQL Server land can/will short-circuit.
I think that's over-stated. Most optimizers will short-circuit when they safely can. Presumably later versions of the optimizer...
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 2, 2014 at 4:03 pm
I wouldn't want to put them in a separate schema unless you really had to. Aside from hideous renaming issues (*), you might end up with what seems like...
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 2, 2014 at 3:43 pm
Viewing 15 posts - 5,971 through 5,985 (of 7,613 total)