Viewing 15 posts - 5,206 through 5,220 (of 7,613 total)
Eirikur Eiriksson (4/13/2015)
ScottPletcher (4/13/2015)
Eirikur Eiriksson (4/13/2015)
ScottPletcher (4/13/2015)
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".
April 14, 2015 at 8:09 am
jgale 70688 (4/13/2015)
Both solutions did work. However, the data I'm working with is slightly more complex than my simple example. My main data table is a...
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".
April 13, 2015 at 4:33 pm
Grant Fritchey (4/13/2015)
SELECT a.City,
a.PostalCode
FROM Person.Address AS a
WHERE ...
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".
April 13, 2015 at 4:32 pm
Eirikur Eiriksson (4/13/2015)
ScottPletcher (4/13/2015)
SELECT...
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".
April 13, 2015 at 3:33 pm
Luis Cazares (4/13/2015)
ScottPletcher (4/13/2015)
Go ahead and add DISTINCT to each separate query on which it's applicable:
SELECT DISTINCT omlSalesOrderID
FROM m1_dc.dbo.SalesOrderLines
WHERE omlPartID='finalmile'
UNION
SELECT ompSalesOrderID
FROM m1_dc.dbo.SalesOrders
WHERE ompShippingMethodID='JBFM'
Why? :unsure:
It's...
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".
April 13, 2015 at 2:58 pm
Here's an alternative approach, also using CROSS APPLY, that can take advantage of the (presumed) clustered index on prefix.PREFIX_VALUE; no guarantee, but it might perform better.
SELECT p_cross_apply.PREFIX_VALUE, SUM(d.DATA_VALUE) 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".
April 13, 2015 at 2:54 pm
Go ahead and add DISTINCT to each separate query on which it's applicable:
SELECT DISTINCT omlSalesOrderID
FROM m1_dc.dbo.SalesOrderLines
WHERE omlPartID='finalmile'
UNION
SELECT ompSalesOrderID
FROM m1_dc.dbo.SalesOrders
WHERE ompShippingMethodID='JBFM'
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".
April 13, 2015 at 2:37 pm
In general, since you want to avoid grouping by varchar columns anywhere, as it's not very efficient, delay converting numeric codes until the outer-most query you can.
select DB_NAME(a.database_id) AS [Database...
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".
April 13, 2015 at 2:25 pm
Off the top of my head, I would use a temp table here, something like below. I don't have time to finish all the median values in the SELECT,...
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".
April 9, 2015 at 3:57 pm
Eric Prévost (4/9/2015)
jdbrown239 (4/9/2015)
When the job runs it fails with the error: 'Procedure IndexOptimize, Line 505...
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".
April 9, 2015 at 11:56 am
If the string is within SSMS, you could try adding a backslash to the end of each line to continue the string. For example:
declare @varchar varchar(30)
set @varchar = 'abcdef'
print...
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".
April 9, 2015 at 9:49 am
CELKO (4/8/2015)
I have a column which needs to be 9 digits long.
I gave you a simple LIKE predicate to assure this, as per your request. I thought ...
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".
April 9, 2015 at 9:46 am
CROSS APPLY often works well in these types of lookups:
SELECT *
FROM @vehicle v
CROSS APPLY (
SELECT TOP (1) *
FROM @vehicleclass vc
...
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".
April 9, 2015 at 9:44 am
I think those are also uniqueidentifier columns, so fragmentation is to be expected. Unless you retain lots of backup info in msdb, and I would not recommend that, then...
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".
April 9, 2015 at 9:38 am
I think those are also uniqueidentifier columns, so fragmentation is to be expected. Unless you retain lots of backup info in msdb -- and I would not recommend that...
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".
April 9, 2015 at 9:38 am
Viewing 15 posts - 5,206 through 5,220 (of 7,613 total)