Why does ACID start with an A? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Why does ACID start with an A?

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2006-01-30 : 13:50:55

Robert writes "Currently the system I am working on has a data structure encoded in binary, then base64 encoded into text and stored in my SQL server.

What is the best argument I can use to convince my team that this is by far one of the worst things someone could do with SQL server?

I've tried to tell them that a stored procedure can return multiple result sets, and those results loaded into a dataset instead of using this binary object.

I tried to take the middle ground and store the data as XML instead of in its own columns. This sort of works, but I am using SQL Server 2000 and there will be a limit on the amount of data I can store. We're storing what we call "Hours Of Operation" information about a company. I'd like to be able to blow out this hours of operation to include sales, holidays, etc.. I think it would be rather cool to be able to go to our search engine and see who has a sale on what right now. (shh! don't steal my idea and don't let me know if anyone else beat me to it either...)

And, the whole issue is that I can't query my data. I was able to query it using OPENXML when I had the data stored in XML, but it was very slow, and I got this annoying "Invalid Pointer" error when using DTS and my stored procedure :( .. Furthermore it required complex, well more complex than needed, Xpath queries to get at the data I was looking for, and I can't exactly use an index if my data is stored in XML.

What I am being told to do is store the data in the column, in text format. I have two delimiters because I am storing multiple tables in one column. "INSERT HEADACHE INTO BRAIN" Then I have to use an inline function to move the data into a temporary table when I need to transform it to export it to a client. We export in varied formats to different partners and clients, so access to each data element individually is critical.

Lastly, I have considered another option.. triggers.. I argue that triggers are usually not a good thing since they cause side effects that can be very unpredictable. However, I could perhaps use a trigger to call xp_cmdshell, since they won't let me upgrade to sql 2005 for CLR stored procedures, which would write a little exe that loads up the binary object and writes it back to my database tables so I can query it... Our system will probably be queried more than written to so this wouldn't be sooo bad.. "

RobWafle
Starting Member

38 Posts

Posted - 2006-01-30 : 13:54:48

Anyone have any general advice? I know the true topic of this question really goes outside the sql server realm, and into another area.. Perhaps communication and education, or sharing a common methodology...

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-01-30 : 14:08:11

Why don’t you just use SQL Server the way it is meant to be used? Model your data into entities, and then create a physical data model to store the data as relational tables.

That gives you a native storage engine, indexes, and a powerful query language (SQL) that you can use to access the data. It is possible that part of your data is not a good fit for a relational data model, but in most real world applications, that is a small percentage.

CODO ERGO SUM

X002548
Not Just a Number

15586 Posts

Posted - 2006-01-30 : 14:13:27

quote:
Originally posted by AskSQLTeam

Robert writes "Currently the system I am working on has a data structure encoded in binary, then base64 encoded into text and stored in my SQL server.

That has got to be the worst idea I've ever heard.

We're talking about engineers, aren't we.

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2006-01-30 : 14:42:48

quote:
Originally posted by AskSQLTeam

Robert writes "Currently the system I am working on has a data structure encoded in binary, then base64 encoded into text and stored in my SQL server.

What is the best argument I can use to convince my team that this is by far one of the worst things someone could do with SQL server?

Uhm....invite them to lunch with Fabian Pascal?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-01-31 : 00:43:36

>>Uhm....invite them to lunch with Fabian Pascal?

Madhivanan

Failing to plan is Planning to fail

RobWafle
Starting Member

38 Posts

Posted - 2006-01-31 : 10:45:15

Michael,

I agree with you wholeheartedly. That is exactly my plan, I was looking for a nice politically correct way to explain that using their existing method basically screws everything up for me at the SQL server level.

At the moment, A hybrid solution is in place. The front end is still using the crazy binary/base64 encoded object. For my reporting and transformation needs, I run a vb2003 program I wrote which copies the data into tables in sql server, so I can use DTS to export the data from my view.

Good news, I will be able to move to using tables to store the data, so I won't have to run a program to load it and save it!! Woo Hoo! They must have seen the pain in my eyes!

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-01-31 : 11:52:16

quote:
Originally posted by RobWafle
...I was looking for a nice politically correct way to explain that using their existing method basically screws everything up for me at the SQL server level...

I feel your pain. Sometimes, it's best to take the offense, and make them try to defend it: "Encoded in binary, then base64 encoded into text??? That's the stupidist thing I've ever seen in my life! What brain-dead moron came up with that!??" Then apologize when you find out it was your boss.

CODO ERGO SUM

X002548
Not Just a Number

15586 Posts

Posted - 2006-01-31 : 12:15:19

quote:
Originally posted by Michael Valentine Jones

quote:
Originally posted by RobWafle
...I was looking for a nice politically correct way to explain that using their existing method basically screws everything up for me at the SQL server level...

I feel your pain. Sometimes, it's best to take the offense, and make them try to defend it: "Encoded in binary, then base64 encoded into text??? That's the stupidist thing I've ever seen in my life! What brain-dead moron came up with that!??" Then apologize when you find out it was your boss.

CODO ERGO SUM

Isn't that what I just said? I've made a career out of it. Sometime good, sometimes bad. I know no other way.

Which, has had it's own positives and negatives.

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

X002548
Not Just a Number

15586 Posts

Posted - 2006-01-31 : 12:16:41

....p.s.

at least I'm consistent

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2006-01-31 : 12:38:01

quote:
Robert writes "Currently the system I am working on has a data structure encoded in binary, then base64 encoded into text and stored in my SQL server.

What in the world are they thinking?
Sounds like.....

Step one - open mouth.
Step two - pick up left foot.
Step three - place left foot in mouth.
Step four - Smack self on back of head.

Jim
Users <> Logic

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-01-31 : 14:53:42

quote:
Originally posted by X002548

quote:
Originally posted by Michael Valentine Jones

quote:
Originally posted by RobWafle
...I was looking for a nice politically correct way to explain that using their existing method basically screws everything up for me at the SQL server level...

I feel your pain. Sometimes, it's best to take the offense, and make them try to defend it: "Encoded in binary, then base64 encoded into text??? That's the stupidist thing I've ever seen in my life! What brain-dead moron came up with that!??" Then apologize when you find out it was your boss.

CODO ERGO SUM

Isn't that what I just said? I've made a career out of it. Sometime good, sometimes bad. I know no other way.

Which, has had it's own positives and negatives.

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Yes, it is about what you said, although my remark may have been slightly more offensive.

I liked the slam that you added about engineers though. You just can't say enough bad things about engineers, especially when they start designing databases.

CODO ERGO SUM

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2006-01-31 : 17:39:51

quote:
Originally posted by Michael Valentine JonesYou just can't say enough bad things about engineers, especially when they start designing databases.

...or talking to you at parties. I usually look around for an accountant to introduce them to...

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-01-31 : 17:48:36

quote:
Originally posted by blindman

quote:
Originally posted by AskSQLTeam

Robert writes "Currently the system I am working on has a data structure encoded in binary, then base64 encoded into text and stored in my SQL server.

What is the best argument I can use to convince my team that this is by far one of the worst things someone could do with SQL server?
Uhm....invite them to lunch with Fabian Pascal?

I wouldn't be surprised to see quotes from this thread show up over at:
http://www.dbdebunk.com/index.html

CODO ERGO SUM

RobWafle
Starting Member

38 Posts

Posted - 2006-02-01 : 12:05:48

ROTFL - Well, Michael.. I think you missed two key words in my request.. "Politically Correct" .. ROTFL

Where is the PC in :

"Encoded in binary, then base64 encoded into text??? That's the stupidist thing I've ever seen in my life! What brain-dead moron came up with that!??"

But the plain truth is that it gets worse! The system uses rows in a table to represent columns too.. e.g. Check out the craziest query I ever wrote.

Here's an idea of what the table looks like:

tbAttribute
AttributeName varchar(100)
AttributeValue varchar(4000)

so, to get a row of data:

CREATE VIEW [dbo].[view_Interchange] AS
SELECT fk9.CompanyPrimaryID
, x.companyProductInfoID as xcompanyProductInfoID
, ISNULL(FK5.attributeValueString,'') as businessName
, ISNULL(BA1.attributeValueString,'') as businessAddressLine1
, ISNULL(BA2.attributeValueString,'') as businessAddressLine2
, ISNULL(BDA.attributeValueString,'') as businessDisplayAddressInListing
, ISNULL(BC.attributeValueString,'') as businessCity
, ST.stateCode as businessState
, ISNULL(BPC.attributeValueString,'') as businessPostalCode
, ISNULL(BURL.attributeValueString,'') as businessURL
, FK1.CompanyID
, FK1.CompanyProductProfileID
, ISNULL(FK7.attributeValueString,'') as businessType
, ISNULL(BPHM.attributeValueString,'') as businessPhoneMain
, ISNULL(BPHFX.attributeValueString,'') as businessPhoneFax
, ISNULL(BPHTF.attributeValueString,'') as businessPhoneTollFree
, ISNULL(BEM.attributeValueString,'') as businessEmail
, ISNULL(BLAT.attributeValue,'') as businessLat
, ISNULL(BLONG.attributeValue,'') as businessLong
, 'http://' + this.host as businessProfileURL

-- , ISNULL(BLANG.attributeValueString,'') as businessLanguagesSpoken
, ISNULL(BYE.attributeValueString,'') as businessYearEstablished
, ISNULL(BEMER.attributeValueString,'') as businessEmergencyService
, ISNULL(BMO.attributeValueString,'') as businessMinorityOwned
, ISNULL(BFO.attributeValueString,'') as businessFemaleOwned
, ISNULL(BEC.attributeValueString,'0') as businessEmployeeCount
, ISNULL(BLC.attributeValueString,'0') as businessLocationCount
, ISNULL(BTL.attributeValueString,'') as businessTagLine
, ISNULL(BDESC.attributeValueString,'') as businessDescription
, ISNULL(BCOC.attributeValueString,'') as businessChamberOfCommerceMember
, ISNULL(BBBB.attributeValueString,'') as businessBBBMember
, ISNULL(BASOC1.attributeValueString,'') as businessAssociation1
, ISNULL(BASOC2.attributeValueString,'') as businessAssociation2
, ISNULL(BASOC3.attributeValueString,'') as businessAssociation3
, ISNULL(BASOC4.attributeValueString,'') as businessAssociation4
, ISNULL(BAWARDS.attributeValueString,'') as businessAwardsCertifications

, ISNULL(BMILDISC.attributeValueString,'') as businessGovMilDiscount
, ISNULL(BAARPDISC.attributeValueString,'') as businessAARPDiscount
, ISNULL(BAAADISC.attributeValueString,'') as businessAAADiscount
, ISNULL(BRET.attributeValueString,'') as businessReturnPolicy

, replace(ISNULL(BRANDS.attributeValueString,''), char(13) + char(10), '|') as businessBrands
, replace(ISNULL(KEYWORDS.attributeValueString,''), char(13) + char(10), '|') as businessKeywords
, replace(ISNULL(PRODUCTS.attributeValueString,''), char(13) + char(10), '|') as businessProducts
, replace(ISNULL(SERVICES.attributeValueString,''), char(13) + char(10), '|') as businessServices

, dbo.if_NAICSCodes(FK1.CompanyProductProfileID) as businessNAICSCodes
, dbo.if_SICCodes(FK1.CompanyProductProfileID) as businessSICCodes
, dbo.if_HoursOfOperation(FK1.CompanyProductProfileID) as businessHoursOfOperation

-- service area
, CASE FK8.targetAreaTypeID
WHEN 5 THEN 'Nationwide'
WHEN 6 THEN dbo.if_CountyNameFromPostalCodeID(FK8.primarypostalCodeID) + ' County' -- county
WHEN 4 THEN dbo.if_StateNameFromPostalCodeID(FK8.primarypostalCodeID) -- state
WHEN 3 THEN dbo.if_DMAFromPostalCodeID(FK8.primarypostalCodeID) -- dma
WHEN 2 THEN dbo.if_CityNameFromPostalCodeID(FK8.primarypostalCodeID) -- city
WHEN 1 THEN dbo.if_RadiusFromPostalCodeID(FK8.radiuspostalCodeID,FK8.radiusMiles) -- radius Handler
ELSE ''
END as businessAreasServed

, dbo.if_LanguagesSpoken(FK1.CompanyProductProfileID) as businessLanguageSpoken
, dbo.if_PaymentMethodsAccepted(FK1.CompanyProductProfileID) as businessPaymentMethodsAccepted

-- , FK1.CompanyProductProfileID
-- , FK0.directoryCompanyID
-- , FK9.companyprimaryid as resellerCompanyID
FROM dbo.tbHost this WITH (NOLOCK)
left JOIN dbo.tbCompanyProductProfileHostXref FK0 WITH (NOLOCK) ON this.hostID = FK0.hostID
left JOIN dbo.tbCompanyProductProfile FK1 WITH (NOLOCK) ON FK0.CompanyProductProfileID = FK1.CompanyProductProfileID
LEFT JOIN dbo.tbCompanyProductProfileAttribute FK5 WITH (NOLOCK) ON (FK1.CompanyProductProfileID = FK5.CompanyProductProfileID AND FK5.attributeName = 'businessName')
LEFT JOIN dbo.tbCompanyProductProfileAttribute FK6 WITH (NOLOCK) ON (FK1.CompanyProductProfileID = FK6.CompanyProductProfileID AND FK6.attributeName = 'businessSEOText')
LEFT JOIN dbo.tbCompanyProductProfileAttribute FK7 WITH (NOLOCK) ON (FK1.CompanyProductProfileID = FK7.CompanyProductProfileID AND FK7.attributeName = 'businessType')
LEFT JOIN dbo.tbCompanyProductProfileAttribute BA1 WITH (NOLOCK) ON (FK1.CompanyProductProfileID = BA1.CompanyProductProfileID AND BA1.attributeName = 'businessAddressLine1')
LEFT JOIN dbo.tbCompanyProductProfileAttribute BA2 WITH (NOLOCK) ON (FK1.CompanyProductProfileID = BA2.CompanyProductProfileID AND BA2.attributeName = 'businessAddressLine2')
LEFT JOIN dbo.tbCompanyProductProfileAttribute BC WITH (NOLOCK) ON (FK1.CompanyProductProfileID = BC.CompanyProductProfileID AND BC.attributeName = 'businessCity')
LEFT JOIN dbo.tbCompanyProductProfileAttribute BST WITH (NOLOCK) ON (FK1.CompanyProductProfileID = BST.CompanyProductProfileID AND BST.attributeName = 'businessState')
LEFT JOIN dbo.tbCompanyProductProfileAttribute BPC WITH (NOLOCK) ON (FK1.CompanyProductProfileID = BPC.CompanyProductProfileID AND BPC.attributeName = 'businessPostalCode')
LEFT JOIN dbo.tbCompanyProductProfileAttribute BURL WITH (NOLOCK) ON (FK1.CompanyProductProfileID = BURL.CompanyProductProfileID AND BURL.attributeName = 'businessURL')
LEFT JOIN dbo.tbCompanyProductProfileAttribute BPHM WITH (NOLOCK) ON (FK1.CompanyProductProfileID = BPHM.CompanyProductProfileID AND BPHM.attributeName = 'businessPhoneMain')
LEFT JOIN dbo.tbCompanyProductProfileAttribute BPHFX WITH (NOLOCK) ON (FK1.CompanyProductProfileID = BPHFX.CompanyProductProfileID AND BPHFX.attributeName = 'businessPhoneFax')
LEFT JOIN dbo.tbCompanyProductProfileAttribute BPHTF WITH (NOLOCK) ON (FK1.CompanyProductProfileID = BPHTF.CompanyProductProfileID AND BPHTF.attributeName = 'businessPhoneTollFree')
LEFT JOIN dbo.tbCompanyProductProfileAttribute BEM WITH (NOLOCK) ON (FK1.CompanyProductProfileID = BEM.CompanyProductProfileID AND BEM.attributeName = 'businessEmail')
LEFT JOIN dbo.tbCompanyProductProfileAttribute BDA WITH (NOLOCK) ON (FK1.CompanyProductProfileID = BDA.CompanyProductProfileID AND BDA.attributeName = 'businessDisplayAddressInListing')

LEFT JOIN dbo.tbCompanyProductProfileAttribute BYE WITH (NOLOCK) ON (FK1.CompanyProductProfileID = BYE.CompanyProductProfileID AND BYE.attributeName = 'businessYearEstablished')
LEFT JOIN dbo.tbCompanyProductProfileAttribute BEMER WITH (NOLOCK) ON (FK1.CompanyProductProfileID = BEMER.CompanyProductProfileID AND BEMER.attributeName = 'businessEmergencyService')
LEFT JOIN dbo.tbCompanyProductProfileAttribute BMO WITH (NOLOCK) ON (FK1.CompanyProductProfileID = BMO.CompanyProductProfileID AND BMO.attributeName = 'businessMinorityOwned')
LEFT JOIN dbo.tbCompanyProductProfileAttribute BFO WITH (NOLOCK) ON (FK1.CompanyProductProfileID = BFO.CompanyProductProfileID AND BFO.attributeName = 'businessFemaleOwned')
LEFT JOIN dbo.tbCompanyProductProfileAttribute BEC WITH (NOLOCK) ON (FK1.CompanyProductProfileID = BEC.CompanyProductProfileID AND BEC.attributeName = 'businessEmployeeCount')
LEFT JOIN dbo.tbCompanyProductProfileAttribute BLC WITH (NOLOCK) ON (FK1.CompanyProductProfileID = BLC.CompanyProductProfileID AND BLC.attributeName = 'businessLocationCount')
LEFT JOIN dbo.tbCompanyProductProfileAttribute BTL WITH (NOLOCK) ON (FK1.CompanyProductProfileID = BTL.CompanyProductProfileID AND BTL.attributeName = 'businessTagLine')
LEFT JOIN dbo.tbCompanyProductProfileAttribute BDESC WITH (NOLOCK) ON (FK1.CompanyProductProfileID = BDESC.CompanyProductProfileID AND BDESC.attributeName = 'businessDescription')
LEFT JOIN dbo.tbCompanyProductProfileAttribute BCOC WITH (NOLOCK) ON (FK1.CompanyProductProfileID = BCOC.CompanyProductProfileID AND BCOC.attributeName = 'businessChamberOfCommerceMember')
LEFT JOIN dbo.tbCompanyProductProfileAttribute BBBB WITH (NOLOCK) ON (FK1.CompanyProductProfileID = BBBB.CompanyProductProfileID AND BBBB.attributeName = 'businessBBBMember')
LEFT JOIN dbo.tbCompanyProductProfileAttribute BASOC1 WITH (NOLOCK) ON (FK1.CompanyProductProfileID = BASOC1.CompanyProductProfileID AND BASOC1.attributeName = 'businessAssociation1')
LEFT JOIN dbo.tbCompanyProductProfileAttribute BASOC2 WITH (NOLOCK) ON (FK1.CompanyProductProfileID = BASOC2.CompanyProductProfileID AND BASOC2.attributeName = 'businessAssociation2')
LEFT JOIN dbo.tbCompanyProductProfileAttribute BASOC3 WITH (NOLOCK) ON (FK1.CompanyProductProfileID = BASOC3.CompanyProductProfileID AND BASOC3.attributeName = 'businessAssociation3')
LEFT JOIN dbo.tbCompanyProductProfileAttribute BASOC4 WITH (NOLOCK) ON (FK1.CompanyProductProfileID = BASOC4.CompanyProductProfileID AND BASOC4.attributeName = 'businessAssociation4')
LEFT JOIN dbo.tbCompanyProductProfileAttribute BAWARDS WITH (NOLOCK) ON (FK1.CompanyProductProfileID = BAWARDS.CompanyProductProfileID AND BAWARDS.attributeName = 'businessAwardsCertifications')

LEFT JOIN dbo.tbCompanyProductProfileAttribute BMILDISC WITH (NOLOCK) ON (FK1.CompanyProductProfileID = BMILDISC.CompanyProductProfileID AND BMILDISC.attributeName = 'businessGovMilDiscount')
LEFT JOIN dbo.tbCompanyProductProfileAttribute BAARPDISC WITH (NOLOCK) ON (FK1.CompanyProductProfileID = BAARPDISC.CompanyProductProfileID AND BAARPDISC.attributeName = 'businessAARPDiscount')
LEFT JOIN dbo.tbCompanyProductProfileAttribute BAAADISC WITH (NOLOCK) ON (FK1.CompanyProductProfileID = BAAADISC.CompanyProductProfileID AND BAAADISC.attributeName = 'businessAAADiscount')
LEFT JOIN dbo.tbCompanyProductProfileAttribute BRET WITH (NOLOCK) ON (FK1.CompanyProductProfileID = BRET.CompanyProductProfileID AND BRET.attributeName = 'businessReturnPolicy')

LEFT JOIN dbo.tbCompanyProductProfileAttribute BRANDS WITH (NOLOCK) ON (FK1.CompanyProductProfileID = BRANDS.CompanyProductProfileID AND BRANDS.attributeName = 'brandNamesOffered')
LEFT JOIN dbo.tbCompanyProductProfileAttribute PRODUCTS WITH (NOLOCK) ON (FK1.CompanyProductProfileID = PRODUCTS.CompanyProductProfileID AND PRODUCTS.attributeName = 'businessProducts')
LEFT JOIN dbo.tbCompanyProductProfileAttribute SERVICES WITH (NOLOCK) ON (FK1.CompanyProductProfileID = SERVICES.CompanyProductProfileID AND SERVICES.attributeName = 'businessServices')
LEFT JOIN dbo.tbCompanyProductProfileAttribute KEYWORDS WITH (NOLOCK) ON (FK1.CompanyProductProfileID = KEYWORDS.CompanyProductProfileID AND KEYWORDS.attributeName = 'keywordPhrases')

LEFT JOIN dbo.tbAttribute BLAT WITH (NOLOCK) ON (FK1.CompanyProductProfileID = BLAT.referenceID AND BLAT.attributeName = 'BusinessAddressGeo_Latitude' and BLAT.context = 'tbCompanyProductProfile')
LEFT JOIN dbo.tbAttribute BLONG WITH (NOLOCK) ON (FK1.CompanyProductProfileID = BLONG.referenceID AND BLONG.attributeName = 'BusinessAddressGeo_Longitude' and BLONG.context = 'tbCompanyProductProfile')

join tbCompanyProductInfoProfileXref x on x.CompanyProductProfileID = FK1.CompanyProductProfileID
join view_ProductToCompanyProductInfo v on v.companyProductInfoID = x.companyProductInfoID

JOIN dbo.tbCompanyProductProfileTargetArea FK8 WITH (NOLOCK) ON FK1.CompanyProductProfileID = FK8.CompanyProductProfileID
JOIN dbo.tbCompanyRelationshipXref FK9 WITH (NOLOCK) ON FK1.companyID = FK9.companySecondaryID
LEFT JOIN dbo.tbState ST WITH (NOLOCK) ON BST.attributeValueString = ST.stateID

WHERE this.recordStatus = 1
AND FK1.recordStatus = 1
and FK1.defaultStatus = 0
AND FK9.CompanyPrimaryID not in (6788, 6651, 6711)
and v.ProductID = 17
-- profile has active products --
AND EXISTS( SELECT 1
FROM dbo.tbCompanyProductInfoProfileXref SK1 WITH (NOLOCK)
JOIN dbo.tbCompanyProductInfo SK2 WITH (NOLOCK) ON SK1.companyProductInfoID = SK2.companyProductInfoID
JOIN dbo.tbCompanyProductInfoStatusXref SK3 WITH (NOLOCK) ON (SK2.companyProductINfoID = SK3.companyProductInfoID AND SK3.currentStatus = 1)
WHERE SK2.recordStatus = 1
AND SK3.companyProductInfostatusID NOT IN (17,18)
AND SK1.companyProductProfileID = FK1.companyproductprofileiD
)

I am eagerly awaiting your comments ..

RobWafle
Starting Member

38 Posts

Posted - 2006-02-01 : 12:08:55

FYI, the inline functions you see in the query take the data from:

tables
columns which contain multiple values seperated by CRLF

and present them as |PIPE| delimited in the export file.

"When I grow up I want to be a DBA"

X002548
Not Just a Number

15586 Posts

Posted - 2006-02-01 : 12:54:05

I just threw up

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

RobWafle
Starting Member

38 Posts

Posted - 2006-02-01 : 13:10:24

LOL. Just in case anyone is wondering. I love my job. I really, really do. I appriciate everyone's support.

Rob

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-02-01 : 13:19:02

How big is your database? Do you currently have performance problems?

Tara Kizer
aka tduggan

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-02-01 : 13:47:35

quote:
Originally posted by tkizer

How big is your database? Do you currently have performance problems?

Tara Kizer
aka tduggan

now i can't decide... i this meant as a serious question or as a joke?
is it just me that's wondering that??

quote:
Michael Valentine Jones

I liked the slam that you added about engineers though. You just can't say enough bad things about engineers, especially when they start designing databases.

now i resent that!!

i'm an engineer!

Go with the flow & have fun! Else fight the flow

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-02-01 : 13:52:50

Both were serious questions. If they aren't currently having performance problems, then I'd bet they will soon based upon that query. It would give him a reason to have them change the design.

Tara Kizer
aka tduggan

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources