Viewing 15 posts - 5,071 through 5,085 (of 7,619 total)
Is it just the id and address, or is there a customer id or other parent entity for the address? If so, the clustering key should be on (...
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".
June 2, 2015 at 1:45 pm
A scalar function will handle that just fine. Particularly if you do it only once, when the data is inserted. You can add a format code / pattern...
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".
June 2, 2015 at 1:42 pm
Dougieson (6/2/2015)
I was thinking of using MIN...
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".
June 2, 2015 at 11:59 am
To evaluate this, we will need considerably more info about the table and its indexes. Please copy the code below into a "New Query..." window in SSMS, change the...
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".
June 2, 2015 at 10:32 am
Maybe something along the lines below. You could save the results into another table to come back and update the main table with the consolidated data.
You could also add:
COUNT(DISTINCT...
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".
June 2, 2015 at 10:16 am
Very surprising that SQL doesn't the index on which it can do almost a full seek.
Do you really need the "PersonID<>0", or could you remove that? I believe 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".
May 27, 2015 at 1:22 pm
Another option to generate code to be run on the other server:
DECLARE @view_template varchar(8000)
SET @view_template = '
CREATE VIEW [$schema$].[$table$]
AS
SELECT * FROM [SourceDatabase].[$schema$].[$table$] UNION ALL
SELECT * FROM [ArchiveDatabase].[$schema$].[$table$]
GO
'
SELECT REPLACE(REPLACE(@view_template,
...
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 27, 2015 at 1:09 pm
Create a replacement values table, and use it to do the data replacement.
CREATE TABLE dbo.data_replacements (
column_name varchar(30) NOT NULL,
current_value varchar(100) NULL,
...
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 22, 2015 at 8:25 am
You'd have to generate dynamic SQL to do that. First, get the highest count as it exists ni your current data. Then modify the above overall structure 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".
May 21, 2015 at 4:18 pm
How about?:
SELECT p.Warehouse,
CASE WHEN SUM(Quantity) >= 10
THEN CAST(SUM(Quantity) AS varchar(30))
...
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 21, 2015 at 3:46 pm
JediSQL (10/31/2014)
In most conditional statements, like WHERE clauses and IF statements, the conditional expression will return TRUE if and only if SQL Server considers the assertion provably true. 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".
May 21, 2015 at 1:20 pm
Here's one quick-and-dirty way to do this for up to 5 instances:
Edit: changed the column names to better match the table defs.
with instanceInfo as
(
select
ins.srvID
,ins.instNetName 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".
May 21, 2015 at 1:01 pm
CORRECTION: Previous code has bug because the tally table does not include a 0.
DECLARE @Sample TABLE(
FileNumb int,
startdate 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 19, 2015 at 3:52 pm
Select FROM the job table and user LEFT OUTER JOINs on the other two tables.
[This sounds possibly like class work or home work, so I'm just giving a general guideline...
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:41 pm
Edit: Was able to post an inline CTE this time; guess they fixed the bug at work that was blocking posting any CTEs ... Hallelujah!
DECLARE @Sample 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".
May 19, 2015 at 3:37 pm
Viewing 15 posts - 5,071 through 5,085 (of 7,619 total)