Viewing 15 posts - 166 through 180 (of 2,171 total)
Can you post the execution plan? Perhaps the index is not covering?
February 11, 2011 at 5:22 pm
Please don't yell!
-- This table is a MUST! Do not remove!
DECLARE@ID TABLE (CtcID INT PRIMARY KEY CLUSTERED)
INSERTdbo.CONTACT
(
CONTACTID,
FIRSTNAME,
MIDDELNAME,
LASTNAME
)
OUTPUTinserted.CONTACTID
INTO@ID
SELECTContactID,
FirstName,
MiddleName,
LastName
FROMAdventureWorks.Person.Contact
WHEREFIRSTNAME LIKE @FIRST
INSERTdbo.EMPLOYEE
(
CONTACTID,
EMPLOYEEID,
NationalIDNumber,
LoginID,
Title
)
SELECTCONTACTID,
EMPLOYEEID,
NationalIDNumber,
LoginID,
Title
FROMAdventureWorks.HumanResources.Employee
INNER JOIN@ID ON ctcID = ContactID
February 10, 2011 at 4:37 am
The answer is quite simple. Your ContactID column in table Contact is not an identity column.
February 10, 2011 at 3:02 am
SELECT[Service],
Product_Entered AS [Product Entered],
CIN,
CLI,
CLI_Match AS [CLI Match],
[Date],
CONVERT(CHAR(5), [Time], 114) AS CallTime
FROMdbo.CliExtract
WHERE(CIN = @cin OR @cin IS NULL)
AND ([Service] = @Service OR @Service IS NULL)
AND [Date] >= COALESCE(@Date, '17530101')
AND [Date] <=...
February 10, 2011 at 3:00 am
DECLARE @Sample VARCHAR(100) = '12345'
;WITH cte(CurrPos, LastPos, String)
AS (
SELECT1 AS CurrPos,
DATALENGTH(@Sample) AS LastPos,
CAST(@Sample AS VARCHAR(MAX)) AS String
UNION ALL
SELECTCurrPos + 1 AS CurrPos,
LastPos,
STUFF(String, 2 * CurrPos, 0, ',') AS String
FROMcte
WHERECurrPos <...
February 8, 2011 at 2:06 am
DECLARE@CurrID INT = (SELECT MIN(PrimaryKeyColumnNameHere) FROM MyDB1.[dbo].[Company]),
@MaxID INT = (SELECT MAX(PrimaryKeyColumnNameHere) FROM MyDB1.[dbo].[Company]),
@Interval INT = 100000
WHILE @CurrID <= @MaxID
BEGIN
DELETEtgt
FROMMyDB1.dbo.Company AS tgt
LEFT JOINMyDB2.dbo.Company AS src ON src.CompanyID = tgt.CompanyID
WHEREtgt.PrimaryKeyColumnNameHere >= @CurrID
AND...
February 8, 2011 at 1:58 am
Here is another approach. Instead of searching for "no duplicate" I keep track of which values that have been used.
SET NOCOUNT ON
CREATE TABLE#Sample
(
ID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
idMvt1 INT...
January 24, 2011 at 4:50 pm
I meant, what is the new timings for all pool sizes; 100, 1000, 10000 and 100000 unique records on a million sample dataset?
January 24, 2011 at 8:22 am
And the other Pool sizes? 100, 1000 and 10000.
And just in case ID doesn't start with 1, changeWHERE id=1;toWHERE id=(select min(id) from mvts);
January 24, 2011 at 8:13 am
Dave Ballantyne (1/23/2011)
Ingenious use. 🙂One word of warning though set MAXDOP 1 , scope_identity can return incorrect results in a parallel query.
And using partitions, right?
January 24, 2011 at 12:19 am
DECLARE@Sample TABLE
(
ID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
idMvt1 int,
idMvt2 int,
idMvt3 int
)
INSERT@Sample
(
idMvt1,
idMvt2,
idMvt3
)
SELECT271, 204, 136 UNION ALL --1 This will always show up because...
January 23, 2011 at 3:39 pm
Change
SELECT @XmlData.modify
to
SET @XmlData.modify
January 23, 2011 at 1:10 pm
Can I throw in a bone too?
--===== Peso's code
;WITH cteSource(ID, SequenceID)
AS (
SELECTu.ID,
ROW_NUMBER() OVER (PARTITION BY u.theValue ORDER BY u.ID) AS SequenceID
FROMdbo.Mvts AS s
UNPIVOT(
theValue
FOR theCol IN (s.idMvt1, s.idMvt2, s.idMvt3)
) AS u
)
SELECTID
FROMcteSource
GROUP...
January 23, 2011 at 10:50 am
How does table A look like?
January 22, 2011 at 12:11 pm
Try this query
SELECTt1.ID,
t1.Postcode,
t1.CityTown,
t1.SuburbDistrict,
t1.StateProvince,
t1.Country_ID,
t1.AddressType_ID,
t1.Postcode_ID
FROM(
SELECTadr.ID,
adr.Postcode,
adr.CityTown,
adr.SuburbDistrict,
adr.StateProvince,
adr.Country_ID,
adr.AddressType_ID,
adr.Postcode_ID
FROMdbo.[Address] AS adr
LEFT JOINdbo.s2_usn AS s2 ON s2.table_id = adr.id
and s2.tablename = 'address'
WHEREadr.Country_ID = 63
AND s2.ID IS NULL
) AS t1
INNER JOIN(
SELECTDISTINCT
u.AddressID
FROM(
SELECTacc.Address_ID AS adr1,
aa.Address_ID AS adr2
FROMAccount AS acc
LEFT...
December 10, 2010 at 7:35 am
Viewing 15 posts - 166 through 180 (of 2,171 total)