Viewing 15 posts - 5,941 through 5,955 (of 7,613 total)
halifaxdal (7/17/2014)
ScottPletcher (7/17/2014)
I suggest using SSIS to import that data. It 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".
July 17, 2014 at 1:58 pm
Lowell (7/17/2014)
if it is plain text, i'd just use bulk insert instead of building an SSIS package;for me, that would be faster than anything else.
I'm thinking you're gonna have to...
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 17, 2014 at 1:33 pm
You really need only a single ROW_NUMBER function:
SELECT
MAX(CASE WHEN row_num % 3 = 0 THEN LotNo ELSE '' END) AS Lot1,
MAX(CASE WHEN...
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 17, 2014 at 1:30 pm
Yep, looks great, thanks!
Here's my scripted recommendations for index changes/rebuilds. I don't have time right now, but can explain more later if/when you have qs. Hope this helps!
------------------------------------------------------------------------------------------------------------------------
--Table:...
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 17, 2014 at 1:17 pm
Since it's from SSMS, it's likely just a standard text file .rpt, rather than a Crystal Reports .rpt.
I suggest using SSIS to import that data. It will of course...
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 17, 2014 at 10:19 am
You could go the redo route if you could afford some brief downtime. Stop replication, delete the data and rebuild indexes, then re-initialize the replication with a fresh snapshot.
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 17, 2014 at 9:08 am
Quite right: if DFUR1.[AsOfDate] is any date/datetime type, you don't want to use ISDATE() on it.
Also, you can simplify the year and month comparisons:
AND
YEAR(MD1.[Inception 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 16, 2014 at 5:09 pm
What percentage of the data does the part to be deleted represent? For example, if the database has 100 years of history, then 2 years is a relative pittance....
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 16, 2014 at 5:00 pm
A table consisting of customers; one row for each customer, and includes that customer's type of house, car, insurance, and type of internet connection.
I think you need to step back...
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 16, 2014 at 4:53 pm
And I realize that an absolute rule is not the intent of what you're doing, but that is what very often happens now. Identity keys have become so much...
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 16, 2014 at 11:12 am
But that's the problem that is over-looked in the all-out desire to achieve a "small clustering key". Often the "always a small clustering key" approach requires creating far more...
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 16, 2014 at 11:08 am
I think that Query1 data got scrambled or something.
Can you use a spreadsheet instead? After you run the queries, in the Results/Output area, left-click in the empty box...
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 16, 2014 at 10:17 am
The Dixie Flatline (7/15/2014)
Statement of fact: No single clustered index can ideally serve the needs of multiple queries searching against different columns of the table.
Another statement of fact:...
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 16, 2014 at 8:29 am
The Dixie Flatline (7/15/2014)
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 15, 2014 at 3:21 pm
You don't need to explicitly convert it. Just get the format to a string of YYYYMMDD, which is always a valid date/datetime format:
SELECT DATEADD(HOUR, @hour, CAST(@date AS varchar(8)))
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 15, 2014 at 1:48 pm
Viewing 15 posts - 5,941 through 5,955 (of 7,613 total)