A T-sql question.

Question

Post reply

A T-sql question.

Viewing 5 posts - 16 through 19 (of 19 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

Jason A. Long (6/4/2015)

ChrisM@Work (6/4/2015)

Jason A. Long (6/4/2015)

Edit... I didn't like the solution I posted...

Solution to what? 😀

It was just a rewrite of the original code Sales.Customer code.

I didn't like it... So I killed it. 😀

Did it look like this?

;WITH FilterTable AS (



-- This could be a #temp table, a table v@riable or even a table-valued parameter:

	SELECT StoreID, AccountNumber, rowguid

	FROM (VALUES

		(934, 'AW00000001', '3F5AE95E-B87D-4AED-95B4-C3797AFCB74F'),

		(928, 'AW00000010', 'CDB6698D-2FF1-4FBA-8F22-60AD1D11DABD'),

		(1252, 'AW00000100', '7D37485D-A4CF-4C08-8D3C-364DEEC7C841'),

		(1186, 'AW00000200', '7C96C878-A038-4B2F-825F-016C922D6407'),

		(386, 'AW00000300', '7256F9B7-5253-4384-B8DE-60B6F0ADF5A6')

	) d (StoreID, AccountNumber, rowguid)

) 

SELECT c.*

FROM Sales.Customer c

INNER JOIN FilterTable f

	ON f.StoreID = c.StoreID

	AND f.AccountNumber = c.AccountNumber

AND f.rowguid = c.rowguid

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Dan121 SSCommitted Points: 1616 More actions · Answer 2

All I am asking is simple questions. Please forget what I am trying to accomplish or what I am trying get.

After running below query (AdventureWorks database). I get 7 columns with 5 rows. My question is, is there any alternative way to this script to get exact same results. Another word, can this query be tuned? or this is the only way to get the result what I get? (plz run the script against AvdentureWork to see the result)

Hope I made clear to everyone.

Thanks everyone for your patience and time.

select * from Sales.Customer where

StoreID = 934 and AccountNumber = 'AW00000001' and rowguid = '3F5AE95E-B87D-4AED-95B4-C3797AFCB74F'

or

StoreID = 928 and AccountNumber = 'AW00000010' and rowguid = 'CDB6698D-2FF1-4FBA-8F22-60AD1D11DABD'

or

StoreID = 1252 and AccountNumber = 'AW00000100' and rowguid = 'E789F6DD-3159-4DDC-81A8-8571C571656E'

or

StoreID = 1186 and AccountNumber = 'AW00000200' and rowguid = '7C96C878-A038-4B2F-825F-016C922D6407'

or

StoreID = 386 and AccountNumber = 'AW00000300' and rowguid = '7256F9B7-5253-4384-B8DE-60B6F0ADF5A6'

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

Dan121 (6/4/2015)

All I am asking is simple questions. Please forget what I am trying to accomplish or what I am trying get.

After running below query (AdventureWorks database). I get 7 columns with 5 rows. My question is, is there any alternative way to this script to get exact same results. Another word, can this query be tuned? or this is the only way to get the result what I get? (plz run the script against AvdentureWork to see the result)

Hope you I made clear to everyone.

Thanks everyone for your patience and time.

select * from Sales.Customer where

StoreID = 934 and AccountNumber = 'AW00000001' and rowguid = '3F5AE95E-B87D-4AED-95B4-C3797AFCB74F'

or

StoreID = 928 and AccountNumber = 'AW00000010' and rowguid = 'CDB6698D-2FF1-4FBA-8F22-60AD1D11DABD'

or

StoreID = 1252 and AccountNumber = 'AW00000100' and rowguid = 'E789F6DD-3159-4DDC-81A8-8571C571656E'

or

StoreID = 1186 and AccountNumber = 'AW00000200' and rowguid = '7C96C878-A038-4B2F-825F-016C922D6407'

or

StoreID = 386 and AccountNumber = 'AW00000300' and rowguid = '7256F9B7-5253-4384-B8DE-60B6F0ADF5A6'

The post above your last post offers a suggestion.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Dan121 SSCommitted Points: 1616 More actions · Answer 4

Dan121

SSCommitted

Points: 1616

June 4, 2015 at 9:04 am

#1803043

Thanks ChrisM@Work and everyone.

Jason A. Long SSC-Insane Points: 23731 More actions · Answer 5

ChrisM@Work (6/4/2015)

Jason A. Long (6/4/2015)

ChrisM@Work (6/4/2015)

Jason A. Long (6/4/2015)

Edit... I didn't like the solution I posted...

Solution to what? 😀

It was just a rewrite of the original code Sales.Customer code.

I didn't like it... So I killed it. 😀

Did it look like this?

;WITH FilterTable AS (
-- This could be a #temp table, a table v@riable or even a table-valued parameter: SELECT StoreID, AccountNumber, rowguid FROM (VALUES (934, 'AW00000001', '3F5AE95E-B87D-4AED-95B4-C3797AFCB74F'), (928, 'AW00000010', 'CDB6698D-2FF1-4FBA-8F22-60AD1D11DABD'), (1252, 'AW00000100', '7D37485D-A4CF-4C08-8D3C-364DEEC7C841'), (1186, 'AW00000200', '7C96C878-A038-4B2F-825F-016C922D6407'), (386, 'AW00000300', '7256F9B7-5253-4384-B8DE-60B6F0ADF5A6') ) d (StoreID, AccountNumber, rowguid) ) SELECT c.* FROM Sales.Customer c INNER JOIN FilterTable f ON f.StoreID = c.StoreID AND f.AccountNumber = c.AccountNumber
AND f.rowguid = c.rowguid

Very similar... CTE had a different name and I use a "WHERE EXISTS" instead if the INNER JOIN...